Database track: design and tune a production schema

Design, load, and tune a production-shaped multi-tenant PostgreSQL schema for an order/ledger system, then carry it through one breaking migration and a documented shard-or-not decision — proving every step with EXPLAIN ANALYZE output and before/after numbers, not assertions.

• Model the schema: tenants, users, orders, order_items, and a payments ledger, with primary keys, foreign keys, and CHECK constraints. Pick surrogate vs natural keys per table and justify each. Identify one place you deliberately denormalize or use JSONB, and name the integrity guarantee you trade away and who now owns it.
• Load realistic volume: at least 50M orders skewed across tenants (a few whale tenants, a long tail), so statistics and plans behave like production rather than a toy dataset. Record row counts per table.
• Index for the real access pattern: write the 4-5 highest-traffic queries first, then add indexes whose leading column matches each predicate. Include at least one composite, one partial, and one covering or expression index. For each, show EXPLAIN ANALYZE before and after and state why the planner now uses it.
• Force and read a bad plan: create a row-estimate error (skewed or correlated columns, or stale statistics) that makes the planner pick a nested loop where a hash join wins. Capture the EXPLAIN ANALYZE with the estimated-vs-actual row gap, then fix it via ANALYZE / statistics target / CREATE STATISTICS and show the plan flip.
• Choose isolation deliberately: identify one operation in the ledger that is vulnerable to a concurrency anomaly (lost update or write skew). Demonstrate the anomaly under READ COMMITTED with two concurrent sessions, then show the correct fix — explicit locking, a constraint, or SERIALIZABLE with retry — and explain the tradeoff you chose.
• Size the connection pool: state your app's real concurrency, compute a server-side pool size with the pooling math, put PgBouncer in transaction mode in front of Postgres, and demonstrate it surviving a connection storm that would exhaust direct connections. Note any prepared-statement / session-state change the mode forced.
• Plan and execute a zero-downtime breaking change: decompose one schema change (e.g. split a column, change a type, or add a NOT NULL on the 50M-row table) into expand-contract phases with batched backfill, showing that old and new app code can run side by side and that no phase takes a long table-level lock.
• Make the shard-or-not call: measure the single-node write ceiling under sustained load, document which cheaper levers (indexing, pooling, read replicas, partitioning) you would exhaust first, then either declaratively partition the largest table or write a concrete Citus shard plan — naming the shard key, why it is high-cardinality and co-locating, and how you would avoid a hot shard.

• A schema DDL file plus a one-paragraph rationale per non-obvious modeling choice (key type, each constraint, the one denormalization/JSONB tradeoff and its new owner).
• For every index: paired EXPLAIN ANALYZE output (before/after) showing the scan change and actual time, not estimated cost alone.
• The forced bad plan captured with its estimated-vs-actual row gap, and the post-fix plan showing the join flip and the lower runtime.
• A reproducible two-session demo of the concurrency anomaly and its fix, with the isolation-or-locking decision and its tradeoff written down.
• Pool-sizing math written out, plus evidence (logs or metrics) that PgBouncer transaction mode absorbed the connection storm that direct connections could not.
• The migration runbook with each expand-contract phase, the batched-backfill approach, and proof (lock_timeout settings + pg_locks observation) that no phase queued behind a long table lock.
• A shard decision memo: the measured single-node ceiling, the lever-exhaustion order, the chosen shard key with co-location reasoning, and the hot-shard mitigation.