Databases
Sharding: multiple-choice review
Crux Multiple-choice synthesis across the sharding unit — shard-key choice, partitioning vs sharding, co-location, hot shards, cross-shard transactions, and online resharding.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each mirrors a decision you make at the whiteboard or mid-incident — not a definition to recite, but a tradeoff to weigh when one Postgres is no longer enough.
Goal
Confirm you can connect shard-key choice, the partitioning-vs-sharding distinction, co-location, the hot-shard failure mode, cross-shard transactions, and online resharding — the synthesis the seven lessons built toward.
Quiz
Completed
A B2B SaaS shards by tenant_id. The top 5 tenants generate 60% of traffic. Pure hash sharding is proposed because it 'distributes evenly'. What is the flaw, and the senior fix?
Heads-up Hash routes by tenant_id, so all of one tenant's rows resolve to a single shard. A 60%-of-traffic tenant pins one shard near saturation while the rest sit idle.
Heads-up Range sharding gives control but does not fix skew: each dominant tenant still falls into one range's shard, which becomes hot. It also adds a hot tail for new signups.
Heads-up Time-range sharding creates a hot tail (all new writes hit the latest shard) and fails selectivity — queries carry tenant_id, not a time range, so they would fan out to every shard.
Quiz
Completed
A team adds `PARTITION BY RANGE (created_at)` to their largest table and reports they have 'sharded the database'. A month later write throughput is unchanged. Why?
Heads-up Cache warming does not change the structural fact: every partition lives on the same primary. There is no extra write capacity to warm into.
Heads-up An index speeds lookups but adds no write capacity. Partitioning never multiplies throughput because it never adds machines — that is what sharding does.
Heads-up They are different: partitioning is single-instance pruning and retention; sharding is multi-instance throughput scaling. Conflating them is exactly this team's mistake.
Quiz
Completed
On a tenant_id-sharded Citus cluster, an engineer adds `audit_log` distributed by `id` instead of `tenant_id`, then runs `SELECT … FROM orders o JOIN audit_log a ON a.order_id = o.id WHERE o.tenant_id = 42`. What happens?
Heads-up The filter narrows orders to one shard, but audit_log's matching rows are scattered across all shards by id. Citus cannot push the join to one worker, so it fans out.
Heads-up Citus accepts any declared distribution key. The cost is paid at query time as a fan-out join, not rejected at insert time.
Heads-up Citus never re-distributes a table on its own — it uses the declared key. Aligning the keys (co-location) is a deliberate schema decision the engineer must make.
Quiz
Completed
A skew alert fires: one shard is at 94% CPU, the other 31 average 18%, and pg_stat_statements attributes 62% of that shard's query time to one tenant. What is the correct immediate action?
Heads-up The rebalancer moves whole shards between workers; it does not split the dominant tenant off the shard they share. The hot tenant stays bundled and keeps saturating its worker.
Heads-up More workers let the rebalancer move shards, but the hot tenant's shard moves as one unit and still saturates whatever worker holds it. You must isolate the tenant, not add hardware.
Heads-up That gives momentary relief but moves no data; the tenant's next request returns to the same hot shard. It treats the symptom, not the skew.
Quiz
Completed
A refund transaction updates `orders` (tenant-scoped) and a global `ledger` table sharded by `account_id`, so the two rows live on different shards. Citus runs this with two-phase commit. What is the failure mode to monitor?
Heads-up 2PC's whole purpose is atomicity across shards: both commit or neither does. The real risk is a coordinator crash mid-protocol leaving prepared transactions stuck, not a lost update.
Heads-up Postgres does not auto-resolve prepared transactions. A stuck one holds locks (and can block VACUUM) until an operator issues COMMIT PREPARED or ROLLBACK PREPARED. It needs monitoring and a runbook.
Heads-up The characteristic 2PC hazard here is the in-doubt prepared transaction after a coordinator crash. The durable design fix is co-locating tables so the transaction is single-shard and avoids 2PC entirely.
Quiz
Completed
To add capacity a team runs Citus 11.1+ online rebalancing. Mid-move the coordinator crashes and a teammate panics that the moving shard is corrupted. How do you read this, and what mechanism makes the answer true?
Heads-up Logical replication is transactional; the source shard is untouched and the target only has partial data. Restarting the rebalancer resumes safely — no restore needed.
Heads-up Online rebalancing keeps the cluster serving traffic; the one in-flight move is suspended while all other shards are unaffected. It is not a cluster-wide read-only event.
Heads-up Resharding uses logical replication, not physical — physical copy would drag every tenant's pages, not just the shard being moved. Logical replication copies exactly the moving rows and lets the move resume cleanly.
Recap
The through-line of the unit is one decision chain: pick a shard key that is selective, uniform, stable, and present at routing time; partition for pruning and retention but shard for throughput; co-locate every tenant-scoped table so 99% of joins stay single-node; expect hot shards on power-law tenants and answer them with tenant isolation, not rebalancing; keep transactions single-shard to dodge 2PC’s in-doubt risk; and lean on logical-replication-based online resharding — while remembering that a shard-key change is a months-long decision lived with for years.