The seven acts: from CREATE TABLE to Citus

Day 0 at a SaaS startup. The PM wants “search users by email.” One engineer, one Postgres, one table. Three years later: 1 billion rows, six Citus shards, and a runbook the size of a textbook. Between those two points are seven moments when the database broke and the team learned.

The seven pieces of this chapter map one-to-one onto seven acts of product growth. Each act has a trigger, a symptom, and a lever. Skip a lever and the next act costs more to resolve.

Act 1 — Day 0, schema design. “users(email, name, org_id).” Should email be UNIQUE? Should org_id be a foreign key? Should prefs be a side table or JSONB? Decisions: email is UNIQUE NOT NULL CITEXT. org_id is BIGINT REFERENCES orgs(id) ON DELETE CASCADE. prefs is JSONB with a GIN index when search demands it. A surrogate id BIGSERIAL insulates from email changes. Bend the rules only with measured throughput pressure, never as a default.

Act 2 — Week 1, 10K rows, the first slow query. Email-search endpoint p95 climbs from 30 ms to 800 ms. The planner does a sequential scan. CREATE INDEX users_email_idx ON users(email). The leading-column rule of B-tree means WHERE email = ? resolves with two page reads. p95 drops to 4 ms.

Act 3 — Month 1, 100K rows, the planner lies. Half the requests are fast, half take 600 ms. EXPLAIN ANALYZE shows the planner sometimes picks a seq-scan despite the index. Row estimate is off by 30×: stale statistics. ANALYZE users; rebuilds the histograms. The planner picks plans from statistics, not from data; statistics maintenance is the operational discipline.

Act 4 — Month 6, the silent bloat. A nightly report runner holds one transaction open for four hours. VACUUM cannot reclaim dead tuples below the xmin horizon. The users table swells from 200 MB to 80 GB. Fix: kill the long transaction, set idle_in_transaction_session_timeout = 60s, run pg_repack to reclaim disk. Permanent fix: replicate to a read replica for reporting.

Act 5 — Year 1, 1M users + 50 app pods, the connection storm. Each pod opens its own Postgres backend on cold-start. At pod-rollout the cluster sees 1000 concurrent backends; the kernel scheduler thrashes; 4 ms queries take 4 s. PgBouncer in transaction-mode: 100 server-side backends, 10000 client connections multiplexed. Sizing rule: pool_size = active_concurrent_transactions × safety_factor, not max_app_workers.

Act 6 — Year 2, the migration that froze prod. Multi-tenancy day. ALTER TABLE users ADD COLUMN tenant_id BIGINT NOT NULL DEFAULT 0 takes an AccessExclusiveLock and triggers a full table rewrite. Prod freezes for eight minutes. The expand-contract recipe: add nullable column, backfill in batches, add CHECK ... NOT VALID, VALIDATE CONSTRAINT, SET NOT NULL, drop the check.

Act 7 — Year 3, 1B rows, the hot shard. Citus is rolled out, shard key is tenant_id. Tenant Acme accounts for 40% of all queries. Acme’s shard saturates while the others idle. Fix: co-location of related tables on the same shard key; online resharding with citus_rebalance_table_shards.

Why the order matters.

A database is like a growing city. The schema is zoning. Indexes are the street map. Execution plans are the traffic dispatcher. MVCC is multiple lanes so cars pass without colliding. The connection pool is the parking garage. Migrations are construction crews who must not close every road at once. Sharding is annexing new districts when one city block can’t hold the traffic. A skipped lesson means the city keeps growing, but the wrong layer is overloaded.

Each act unlocks the next only if you nailed the previous one. Skip Act 1 (schema) and Acts 2–7 fight a losing battle against inefficient joins. Skip Act 2 (indexing) and Act 3 planning decisions are made blind. Skip Act 3 (stats) and Act 4 vacuum is the only lever left. Skip Act 4 (bloat) and Act 5 pool threads see table scans get slower. Skip Act 5 (pooling) and Act 6 migrations are starved by connection storms. Skip Act 6 (lock safety) and Act 7 sharding is impossible. The order is a constraint imposed by physics and Postgres internals.