Shard design doesn't start from your tables
Shard keys should come from your access patterns, not your tables: co-locating orders with customers by customer_id avoids cross-shard joins in sharded Postgres.
Extract
Previously, in The lifecycle of a sharded Postgres query , the router had to join customers and orders itself, because each table was sharded by its own ID. The fix was to shard orders by customer_id , hashed and mapped the same way as customers . In Neki, that fix is called a "shard group." Sharding splits a table's rows across multiple Postgres clusters, called shards. Each shard holds one subset of the table. For the best performance, you want most of your queries to touch a single shard. Then the Neki router does not have to fan out to multiple shards to collect and aggregate results. To do this, you place all related rows on the same shard, so that joins and transactions stay local. Neki gives you powerful options to do this with shard groups . A shard group is the unit of co-location in Neki. When two tables belong to the same shard group and hash their shard keys the same way, rows with the same key value always land on the same shard. Shard groups are worth thinking through when you migrate to Neki, because they decide which queries stay on one shard. But keep in mind, they aren't permanent. The Reshard workflow allows you to change a table's shard key or shard count later, which you'll likely need to do as your queries evolve. In this post, we walk through how to design them, extending the storefront example from the earlier post. Let's start with a data topology snippet that fully describes the sharding scheme of the storefront. You may want to refer to our earlier post, What is a data topology? Co-locating related tables { " shard_indexes " : { " by_customer " : { " type " : "xxhash" , " columns " : [ "customer_id" ] } }, " shard_groups " : [ { " uid " : "customer_data" , " default_shard_index " : "by_customer" , " key_ranges " : [ { " shard_uid " : "store-1" , " end " : "80" }, { " shard_uid " : "store-2" , " start " : "80" } ] } ], " databases " : { " store " : { " default_shard_group " : "customer_data" , " schemas " : { " public " : { " tables " : { " customers " : { " primary_indexes " : [{ " columns " : [ "id" ] }] } } } } } } } The customer_data shard group has two key elements. First, it defines a default shard index. by_customer says that tables in this shard group have a customer_id column. Neki takes the value in that column and hashes it. xxhash maps a number, in this case a specific customer_id , into a 64-bit keyspace. Second, the shard group's key_ranges split that keyspace across physical Postgres …
Explained
What happened
PlanetScale explained how to design shards in Neki, its sharded Postgres, using a unit called a shard group.
- A shard group defines a shard index (for example
xxhashofcustomer_id) and how the 64-bit hash space is split into key ranges across Postgres clusters. - Tables in the same group hash the same key the same way, so a customer and all their orders, items and addresses land on one shard. The
customerstable inherits the index but hashes its ownid. - Small lookup tables like
countriesandcurrenciesbecome reference tables: every shard gets a full copy, and the router sends each write to all copies. - Tables with a different natural key, like the product catalog by SKU, go in a second shard group that can reuse the same clusters or get its own. Shard keys can be SQL expressions, such as
lower(sku)orleft(order_id, 4). - Besides
xxhash, Neki supportsrangeandmoduloindexes. Modulo is discouraged because changing the modulus moves almost every row. - Queries without the shard key, like
WHERE id = 84213on orders, would fan out to every shard. A global secondary index fixes this: a lookup table, itself sharded by order id, that maps the id to itscustomer_id.
Why it works
A query or transaction that stays on one shard behaves like normal Postgres. One that touches two shards does not: Neki commits each shard on its own, with no atomic commit and no shared snapshot. So the goal is to place rows that are read and written together on the same shard. That depends on access patterns, not on how tables are drawn in the schema.
The bigger pattern
This is co-location by entity, the same idea as Vitess keyspaces, Citus distributed and reference tables, and Spanner interleaved tables. The tenant or customer becomes the unit of locality. Global secondary indexes trade an extra write and possible lag for avoiding scatter-gather reads. None of it is new, but the post is a clear checklist for applying it.
What it means in practice
- Before sharding, list your top queries and transactions and find the key most of them filter on. That is your shard key, even if it is not the primary key of every table.
- Add the shard key column to child tables early (for example
customer_idonorder_items), even in a single Postgres today. It makes later sharding much easier. - Replicate small, read-heavy lookup tables to every shard instead of joining across shards.
- Prefer hash ranges over modulo so you can split shards without moving everything.
- Any flow that must be atomic across two customers needs a different design, such as a saga or an outbox, because cross-shard commits are not atomic.