# Database Sharding: The Choices We Wish We'd Made Earlier URL: https://www.devopsness.com/blog/database-sharding-choices-we-wish-we-made-earlier Author: Kiril Urbonas Published: 2026-06-14 Updated: 2026-10-31 Categories: Infrastructure Tags: Database, Sharding, Postgres, Scaling, Distributed Systems Summary: Sharding is more than "split the table": the shard key choice cascades through queries, joins, rebalancing, and operations. The decisions that pay off and the ones we redid. --- # Database Sharding: The Choices We Wish We'd Made Earlier Pick the shard key your hottest queries already filter on, which for multi-tenant SaaS is almost always `tenant_id`, and make four other decisions before you migrate: globally unique IDs, a plan for whale tenants, routing outside the application, and cross-shard query tooling. Retrofitting any of them after sharding is where our pain came from. > [!VERIFIED] > PostgreSQL 18 (docs for 18.6), Citus 13.0, against the PostgreSQL logical replication row filter and declarative partitioning docs and the Citus data modeling guide. Checked October 2026. We sharded our primary Postgres database after two years of telling ourselves we never would. Sharding worked: the system is faster and scales further, and it is operationally more complex. Every sharding choice is a constraint on every future query, so this is the list we wish we'd had. ## Shard last, not first The forcing functions for us were storage growth on one large table, write throughput with shrinking headroom on a single primary, and maintenance (VACUUM, migrations, restore tests) that grew with data size until a migration no longer fit a window. Before sharding, we exhausted the cheaper levers: declarative partitioning, archiving cold data, read replicas, materialized views for expensive aggregates. Those bought us about a year. If your pain is reads, [routing reads to replicas](/blog/postgres-read-replicas-routing-reads) solves it without sharding. If your pain is one append-heavy table, [declarative partitioning for time-series data](/blog/time-series-postgres-declarative-partitioning) keeps you on one node much longer. Sharding adds write and storage scale. It does not make reads cheaper. ## The shard key is the decision you can't undo The key decides which rows live together, which queries are single-shard (fast) or fan-out (slow), and how rebalancing works. Our candidates were `user_id`, `tenant_id`, `created_at`, and a hash of a compound key. We picked `tenant_id`. The dominant query is "everything for one tenant," so most queries became single-shard. Per-tenant operations (export, GDPR deletion, tenant maintenance) align with shard boundaries. This matches the Citus guidance, which says to distribute multi-tenant tables by a common tenant column so rows with the same value land on the same node and joins stay local: ``` -- citus: co-locate tenant tables, replicate small shared tables SELECT create_distributed_table('orders', 'tenant_id'); SELECT create_distributed_table('order_items', 'tenant_id', colocate_with => 'orders'); SELECT create_reference_table('currencies'); ``` Two risks we accepted came true: whale tenants skewed shard sizes, and cross-tenant analytics needed fan-out. A time-based key would have been worse: every write lands on the newest shard, which becomes the permanent hotspot. ## The four things we'd do on day one **Plan for whales.** Our biggest tenant ended up alone on a shard that ran hot while others idled. We retrofitted a `tenant_id` plus sub-bucket key for the largest tenants, splitting their rows by a deterministic hash. Retrofitting was painful. Reserving the bucket column from day one is nearly free. **Use globally unique IDs.** Per-shard auto-increment IDs collide the moment rows move between shards or get merged in analytics. Use UUIDv7 or a Snowflake-style scheme for anything that can cross a shard boundary. PostgreSQL 18 ships a built-in `uuidv7()` function, so there's no extension to install. **Keep routing out of the application.** Our first shard map lived in app code, so every routing change needed a deploy. We moved it to a proxy layer. The map itself is simple; this is roughly what ours does: ``` # shard_router.py import hashlib SHARD_COUNT = 16 OVERRIDES = {"tenant-acme": [12, 13, 14, 15]} # whale tenants span buckets def shard_for(tenant_id: str, row_key: str = "") -> int: buckets = OVERRIDES.get(tenant_id) if buckets: h = int(hashlib.sha256(row_key.encode()).hexdigest(), 16) return buckets[h % len(buckets)] h = int(hashlib.sha256(tenant_id.encode()).hexdigest(), 16) return h % SHARD_COUNT ``` Note the trap in that sketch: `h % SHARD_COUNT` means changing the shard count remaps almost every tenant. That's fine only if moves are explicit (see below) and the map is stored, not recomputed. **Build cross-shard tooling before cutover.** We built cross-shard query routing three months after sharding. We needed it in week two. ## Cross-shard queries are the tax Single-shard queries are easy. The fan-out remainder is where complexity lives. A `LIMIT 10` across shards fetches the top 10 from each, merges, and takes the global top 10. A `count(*)` sums per-shard counts. A join between a sharded table and a small shared table works if the small table is replicated everywhere, which is what Citus reference tables do. Transactions are harder. Atomic writes across shards need two-phase commit, sagas with idempotent steps, or an honest decision to accept eventual consistency. We keep related data on one shard so this is rare, and use sagas for the rest. Track the cross-shard query rate; we alert when it creeps past roughly 10-15% of queries, because growth there means the shard key no longer fits the workload. ## Moving tenants between shards We use explicit, range-style moves rather than consistent hashing, because with 16 shards an auditable map beats an elegant one. Consistent hashing pays off at hundreds of shards. Each move uses logical replication with a row filter (PostgreSQL 15+), which our [logical replication upgrade guide](/blog/postgres-logical-replication-zero-downtime-upgrades) covers in depth: ``` -- on the source shard ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_tenant_id_id_key; CREATE PUBLICATION move_t42 FOR TABLE orders WHERE (tenant_id = 42), order_items WHERE (tenant_id = 42); -- on the target shard CREATE SUBSCRIPTION move_t42 CONNECTION 'host=shard-03 dbname=app user=replicator' PUBLICATION move_t42; ``` Then wait for catch-up, pause writes for that tenant for a few seconds, flip the routing entry, resume, and delete the old rows. The replica identity line is not optional: the PostgreSQL docs say that if a publication publishes `UPDATE` or `DELETE`, the row filter may only use columns covered by the replica identity. ## Errors and failure modes we hit - `ERROR: cannot update table "orders"` with `DETAIL: Column used in the publication WHERE expression is not part of the replica identity.` The filter column isn't in the replica identity. Use a unique index that includes `tenant_id`. - Duplicate key violations after a move: the target shard's sequence was behind the copied IDs. Globally unique IDs make this disappear. - A new shard missing columns: we added a shard and forgot to apply the latest migration, so writes failed on it. CI now checks that every shard's schema matches before any rebalance. - A routing bug ten minutes into our first production move. We rolled back, then built a shadow-shard environment where every routing change runs first. ## Where this breaks None of this helps a read-heavy workload, data under a few hundred GB that fits comfortably on one well-tuned node, or a single hot table you could partition or archive instead. Tenant sharding fails when no natural tenant exists (a social graph, a global marketplace), when most queries span tenants, or when one tenant is larger than any node you can buy. Application changes are bigger than they look: our ORM assumed one connection, and rewriting query patterns took about twice our estimate. ## Questions people ask ### How do I choose a shard key? Pick the column your most frequent and latency-sensitive queries already filter by, so they hit one shard. For multi-tenant SaaS that's usually `tenant_id`. Check three things: values spread load reasonably evenly, related tables share the key so joins stay local, and no single value outgrows a shard. Avoid time-based keys for write-heavy tables, because the newest shard takes every insert. ### When should you shard a Postgres database? After partitioning, archiving, read replicas, query tuning and vertical scaling have run out. Real triggers are write throughput a single primary can't sustain, data growth past what one instance handles comfortably, and maintenance operations that no longer fit their windows. If the bottleneck is reads, add replicas; sharding adds write and storage capacity, not read capacity. ### What is the difference between partitioning and sharding? Partitioning splits one table into pieces inside a single database server, and PostgreSQL's planner prunes partitions it doesn't need. Sharding spreads data across separate servers, each holding a subset. Partitioning is transparent to the application and keeps transactions and joins normal. Sharding adds network hops, cross-shard queries and distributed transactions, which is why it should come second. ## What to read next The shard key is permanent in practice, so pick it carefully; everything else is tooling you can build over time. - [Postgres logical replication for zero-downtime upgrades](/blog/postgres-logical-replication-zero-downtime-upgrades), the mechanism we use for shard moves - [Postgres connection pooling with PgBouncer in front of RDS](/blog/postgres-connection-pooling-pgbouncer), the connection layer that sharding stresses - [pg_stat_statements for Postgres query analysis](/blog/postgres-pg-stat-statements-query-analysis), which tells you which queries are cross-shard - [Postgres autovacuum tuning from production stalls](/blog/postgres-autovacuum-tuning-from-production-stalls), discipline that matters more per shard ## Sources - [PostgreSQL documentation: logical replication row filters](https://www.postgresql.org/docs/current/logical-replication-row-filter.html) - [PostgreSQL documentation: table partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html) - [Citus documentation: choosing a distribution column](https://docs.citusdata.com/en/stable/sharding/data_modeling.html)