Acts 1–3 in depth: schema, indexes, and planner statistics

A team adds a TEXT column for email instead of CITEXT on Day 0. Six months and 100M rows later, a case-sensitivity bug requires a full-table type change. The original schema decision — made in five seconds — costs three days and a maintenance window. Schema is the only act where the cost of being wrong is irreversible.

Act 1 — Schema: the one irreversible decision

A wrong type (TEXT vs CITEXT for email) becomes a multi-day migration at 100M rows. A wrong key choice (composite natural key vs surrogate BIGSERIAL) makes every join expensive forever. The relational model rewards strictness up front:

• NOT NULL by default — nullable columns invite application-level null checks that should be database constraints.
• FK constraints unless you have measured throughput pressure — referential integrity prevents orphaned child rows that produce wrong query results at scale.
• CHECK constraints for business invariants — a direct SQL update can create invalid state without them.
• Surrogate keys for external references — id BIGSERIAL insulates from email changes; retrofitting surrogate keys at 1B rows requires a full table rewrite.

Schema mistakes and their escape hatches:

• A composite natural key (user_id, organization_id, email) as the PK means every join carries three columns; every FK replicates three columns. Fix: surrogate id is cheaper, but retrofitting requires a migration that rewrites the table.
• A missing FK on a child table means no referential integrity; the data decays over time. Fix: add the FK retroactively — but first fix orphaned child rows.
• A missing CHECK constraint on a domain-specific column (status must be one of ACTIVE, PENDING, ARCHIVED) means the application is the only guard. Fix: add the constraint, quarantine invalid existing rows.

Bend the rules only with evidence — disable FK on the highest-write child tables if the FK trigger cost exceeds the write budget, never as a stylistic preference.

Act 2 — Indexes: pay back cost only on the right columns

A B-tree on email resolves WHERE email = ? in O(log N) page reads. A B-tree on (org_id, created_at DESC) resolves “the 50 latest events for an org” in two page reads. A GIN index on prefs jsonb makes WHERE prefs @> '{"theme":"dark"}' a 2 ms lookup instead of a 4 s seq-scan.

The forgotten cost: every insert and update writes to every index. The leading-column rule is the most-violated principle in the wild.

Index selection failures:

• A B-tree index on a low-cardinality column (gender with 2 values, status with 5 values) is almost always a waste — the planner chooses seq-scan and the index eats write throughput.
• An index on (a, b) does not help queries that filter only by b. A (user_id, created_at) index built for “events for a user” does not help “recent events by anyone.” A separate (created_at, user_id) index satisfies both and the planner picks it based on the query.
• JSONB indexes (GIN or GIST) are powerful but expensive on writes — 100K inserts/second can halve write throughput if the JSONB column is indexed naively.
• Partial indexes solve the write-throughput problem for dimension tables: CREATE INDEX ... WHERE status != 'ARCHIVED' covers the 5% of active rows at a fraction of the cost.

The cost of being wrong here is permanent: legacy apps inherit the bad column order, and any attempt to drop the index for remediation risks query regressions in undocumented code paths.

Act 3 — Statistics: the planner picks plans from histograms

Postgres samples the table during ANALYZE and builds a histogram per column. The planner uses histograms to estimate row counts; the cost model ranks plans. When statistics are stale, estimates are wrong.

A 1000× off estimate produces a 1000× slower plan: the planner picks a nested loop expecting 10 rows; the real cardinality is 10000 rows; the loop scans 10M index entries instead of one hash join over a 10K-row build side.

Defence:

• autovacuum_analyze_scale_factor = 0.05 (or smaller, 0.01) on busy tables — at the default 0.1, a table that grows by 1M rows per day has stale stats within hours.
• CREATE STATISTICS (dependencies) ON user_id, created_at FROM events teaches the planner column correlations (new users tend to appear in recent data; the planner assumes independence by default).
• pg_stat_statements to find the offending plans by total execution time.
• auto_explain.log_min_duration = '500ms' for tail-latency triage.

The trap at scale: a single bad estimate cascades. The planner caches plan IDs (pg_stat_statements.plan_id); a human has to find and force a replan by dropping and re-creating the query’s prepared statement, or by running DISCARD PLANS.