Re:PlanetScale Insights: Advanced query monitoring
by derekperkins on Friday May 27, 2022 @10:47AM
(#31530271)
That's a different problem also solvable with Vitess. For those smaller tables, you can define them as a reference table, then have the same rows on every shard, so you can continue to have FKs to all of those.
https://vitess.io/docs/13.0/user-guides/vschema-guide/advanc...
When you're choosing your sharding keys, you want to design it where the bulk of your operations happen on a single shard, often a tenant/customer id. That guarantees that all customer data lands on a single shard, with FKs across every table you want.
We're running on ~40 shards across 6 keyspaces, and there are very few cases where we can't use FKs.
This is a pretty major limitation. Part of the reason to use Vitess is to scale out. It is often very valuable to have a small number of root elements in a star schema and to have foreign keys which ladder up to them.