Shard co-location

Placing rows that will be queried together on the same node, by giving their tables the same distribution column. database-sharding explains how one table is split; co-location is what makes several sharded tables usable together, and it is the reason a sharded database can still offer joins, foreign keys and multi-statement transactions instead of only single-table lookups.

The rule

From citus, where the mechanism is explicit in the API:

"Distributed tables that have the same distribution column can be co-located to enable high performance distributed joins and foreign keys between distributed tables."

Tables are co-located by default when their distribution columns share a type, and explicitly with create_distributed_table('devices', 'device_id', colocate_with := 'events'). The effect is positional: device_id = 42 hashes to the same shard number in both tables, so both rows live on the same worker.

What it buys

Once two tables are co-located, an operation that stays inside one distribution-column value becomes a local operation on one node:

• Joins — events JOIN devices USING (device_id) needs no data movement; each worker joins its own shards.
• Foreign keys between distributed tables — a constraint can only be enforced where both rows are. Co-location is the precondition, not an optimization.
• Distributed transactions, INSERT..SELECT, and stored procedures — citus names all three as scaling on co-location.

The negative form is the useful one: anything co-location does not cover falls back to scatter-gather. A join on a non-distribution column, an aggregate across all tenants, a foreign key to a table sharded differently — these are the queries whose cost the proxy or coordinator pays in its own memory. Co-location is how a sharded schema keeps that set small.

The escape hatch for small tables

Co-location only helps when both tables have a sensible distribution column. For lookup and dimension tables that don't, citus uses reference tables: replicate the whole table to every node, so a join against it is always local no matter what the other side is sharded by. Replication instead of partitioning, chosen by table size — the same trade every distributed store makes somewhere.

Why it decides the data model, not just the deployment

The distribution column has to be a column that already appears in the queries you care about — a tenant id, a customer id, a device id. That is why multi-tenant SaaS is the workload sharding stories always reach for: tenancy supplies a column that naturally partitions almost every query, so almost everything co-locates. Workloads with no such column get the scatter path for most of their traffic.

This is the same one-way decision cross-shard-queries records from the vitess side — the shard key is chosen once and constrains every query afterwards. Co-location is that decision applied across a whole schema rather than one table, which raises the stakes: change the distribution column of one table and you break its co-location with every table that shared it.

Related

database-sharding · cross-shard-queries · citus · vitess · planetscale-768-servers · operational-databases · synthesis