# Senior PostgreSQL Backend Playbook

Compressed reference for the senior-only PostgreSQL extension. Pair with [cheat-sheet.md](./cheat-sheet.md), [GLOSSARY.md](./GLOSSARY.md), and [repo-postgres-map.md](./repo-postgres-map.md).

## The senior shift
Junior/intermediate PostgreSQL asks: “Can you write SQL and name the main internals?”
Senior PostgreSQL asks: “Can you explain the planner’s choices, the concurrency risks, the storage costs, and the operational trade-offs in a real backend system?”

## The six senior lenses
1. **Query shape first** — the planner can only use an index strategy that matches the predicates, joins, sort order, and selectivity of the real query.
2. **Row-count thinking** — most bad plans start with bad cardinality estimates.
3. **Write-path costs** — updates, soft deletes, and indexes all create storage and maintenance consequences.
4. **Safe schema change thinking** — migrations are correctness and locking events, not just DDL files.
5. **Debugging from evidence** — trace → SQL → EXPLAIN ANALYZE → root cause → targeted fix.
6. **Measure before tuning** — no folklore fixes before profiles, plans, and table behavior support them.

## Backend review checklist
- Does the query shape match the available indexes, or are functions / mismatched types blocking index use?
- Are row-count estimates obviously wrong relative to actual data shape?
- Are soft deletes and update churn creating bloat or autovacuum pressure?
- Could this migration or DDL operation block writes or reads longer than expected?
- Is the transaction boundary protecting correctness without creating unnecessary lock contention?
- Is tenant filtering enforced by RLS rather than only by query convention?
- Do we know whether the bottleneck is planner choice, missing index, too many heap fetches, or too much data being requested?

## Interview answer shapes
### Why is an index present but still not used?
Because the planner thinks scanning is cheaper. That can happen when the query is not selective, stats are stale, the SQL shape prevents index use, the predicate does not match the index order, or a function/type mismatch hides the indexable form.

### What is the planner really doing?
It estimates row counts and costs for candidate plans, then picks the cheapest one according to its model. Good plans depend on good statistics and a query shape the planner can reason about.

### Why do senior engineers care about bloat?
Because MVCC leaves old row versions behind, and write-heavy tables can pay in storage, cache efficiency, vacuum work, and slower scans if cleanup can’t keep up.

### What makes a schema change safe?
Understanding lock impact, migration shape, backfill strategy, and whether the change can be rolled out without long blocking or inconsistent app behavior.

### What is a good slow-query workflow?
Identify the real slow SQL, run EXPLAIN ANALYZE, compare estimates to actuals, find the expensive node, then decide whether the fix belongs in the query, the index strategy, statistics, or table design.