Databases
Database track: multiple-choice synthesis
Crux Cross-track synthesis MCQs: connect schema, indexes, plans, MVCC, pooling, migrations, and sharding into the one decision tree that takes a product from MVP to a sharded cluster.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole track. Each is a moment in one product’s life — from a single table to a billion-row cluster — where two database concepts collide and you have to pick the lever that fixes the right one.
Goal
Confirm you can chain the track together: model the schema, choose the index the planner will actually use, read why a plan went wrong, pick an isolation level, size the pool, sequence a zero-downtime migration, and decide if sharding is even the answer.
Quiz
Completed
A query filters orders by tenant and date and you add a composite index. Both plans below run on the same table; only the index column order differs. Why does the planner skip the index in plan A? ```sql -- query SELECT * FROM orders WHERE tenant_id = 42 AND created_at >= '2026-01-01'; -- A: CREATE INDEX ON orders (created_at, tenant_id); -> Seq Scan -- B: CREATE INDEX ON orders (tenant_id, created_at); -> Index Scan ```
Heads-up Both indexes cover the same two columns and are near-identical in size. Selection is driven by whether the leading column matches the query's access pattern, not raw index size.
Heads-up It does — that is exactly what index B is for. Equality on the leading column then a range on the next is the canonical composite-index win.
Heads-up WHERE-clause text order is irrelevant. What matters is matching equality columns first, then the range column — the leading-column rule, not source order.
Quiz
Completed
A nightly batch runs one transaction that stays open for three hours. Over the same window an OLTP table that is heavily UPDATEd grows from 8 GB to 40 GB on disk even though the live row count barely changes. What links the long transaction to the bloat?
Heads-up The live row count barely changes, so this is not insert growth. It is dead-tuple accumulation: updates create new versions and the old ones can't be vacuumed while the long snapshot is held.
Heads-up UPDATE creates a new tuple version per changed row, not a full-table rewrite. The bloat comes from those dead versions being unreclaimable, not from rewriting.
Heads-up A stricter isolation level holds snapshots at least as long, if not longer. The fix is to not hold one transaction open for hours — not to tighten isolation.
Quiz
Completed
A reporting query was fast for months, then suddenly picked a nested-loop join over a hash join and went from 200 ms to 40 s after a data-import spike. EXPLAIN ANALYZE shows the planner expected 12 rows from one branch but got 2.1 million. Root cause and first durable fix?
Heads-up A nested loop is optimal when one side really is tiny. The defect is not the join algorithm — it is the row estimate that fed the join choice. Fix the statistics, not the join.
Heads-up Postgres has no hint syntax, and pinning one plan masks the real problem. The misestimate will distort other queries too; refreshing statistics fixes the input the planner reads.
Heads-up work_mem affects whether a chosen hash spills to disk, but the planner never chose the hash join here. The trigger is the cardinality misestimate — a statistics problem.
Quiz
Completed
You move the app behind PgBouncer in transaction mode to survive a connection storm. Immediately the app errors that prepared statements do not exist, and a SET search_path you ran at connect time no longer sticks. Why?
Heads-up It corrupts nothing. The behaviour is by design: transaction mode multiplexes many clients over few server connections, so anything assuming a stable session connection breaks.
Heads-up Pool size controls concurrency, not statement lifetime. The statements vanish because the client doesn't keep the same server connection between transactions in transaction mode.
Heads-up Session mode preserves session state but pins one server connection per client for the whole session, defeating the multiplexing that solved the connection storm. It is a real tradeoff, not a free fix.
Quiz
Completed
You must rename a hot column from `email` to `email_address` on a 500M-row table with zero downtime while old and new app code run side by side during a rolling deploy. Which sequence is correct?
Heads-up RENAME is fast on the catalog, but it is a breaking change: the instant it commits, old app code referencing the old name errors. With two versions live during a rolling deploy you need expand-contract, not an atomic rename.
Heads-up The requirement is zero downtime with versions coexisting. A window plus big-bang deploy violates both constraints; expand-contract is what lets the two code versions overlap.
Heads-up A NOT NULL plus volatile default forces a full table rewrite under an ACCESS EXCLUSIVE lock, freezing the table and queueing every waiting query — the lock-queue failure mode. Backfill in batches instead.
Quiz
Completed
A multi-tenant SaaS hits write limits on a single Postgres. The team proposes hash-sharding on a `country` column. Three tenants in one country drive 70% of traffic. What goes wrong, and what is the better shard key?
Heads-up A uniform hash spreads keys evenly, but load is per-key, not per-distinct-value. A low-cardinality, skewed key like country concentrates real traffic into one shard regardless of hash uniformity.
Heads-up Random per-row sharding does spread writes, but it scatters each tenant's rows across every shard, turning every tenant query and join into a cross-shard fan-out. Co-location by tenant_id is the point.
Heads-up Sharding is the last resort after schema, indexing, plan tuning, pooling, and read-replica options are exhausted — it adds cross-shard joins, rebalancing, and operational cost. Confirm the single-node ceiling is truly hit first.
Recap
The track is one decision tree applied to a growing product: model the schema for integrity first, add the index whose leading column matches the access pattern, read the plan to catch row-estimate errors before they cascade, keep transactions short so MVCC can vacuum, size and mode the pool to survive connection storms, decompose every breaking change into expand-contract phases, and reach for sharding only after a high-cardinality, co-locating shard key is chosen and every cheaper lever is spent. Each failure mode resolves back to one question: which layer is the bottleneck, and what is the cheapest lever that fixes it?