Database track: free-recall synthesis
Free-recall prompts spanning the database track — schema, indexes, plans, MVCC, pooling, migrations, sharding. Answer from memory first, then reveal and compare.
Retrieval beats re-reading. For each prompt, reconstruct a full answer from memory before you open the model answer — these span the whole track, so the effort of joining concepts is exactly what makes them stick. By the time you finish, you will know exactly which part of the track you own and which you are still guessing at.
Reconstruct the track’s spine without looking back: when to bend normalization, how the index cost model picks a scan, why row-estimate errors cascade, what snapshot isolation does and does not protect, the pool-sizing math, and how a shard key co-locates or scatters work.
- 01When is it correct to bend the relational rules — denormalize, store JSONB, or even disable foreign keys?
- 02How does the planner decide between an index scan and a sequential scan, and why does the leading column of a composite index matter so much?
- 03Why do row-estimate errors cascade into catastrophically bad plans, and how do you stabilize a plan under traffic?
- 04What does PostgreSQL's MVCC snapshot isolation protect against, what does it not, and how does a long transaction cause table bloat?
- 05Give the pool-sizing reasoning for PgBouncer in front of Postgres: why are backends heavy, how big should the server-side pool be, and what is the transaction-mode prepared-statement trap?
- 06How do you choose a shard key, why does a low-cardinality key create a hot shard, and what is the safe order of operations before sharding at all?
If you could rebuild each answer from memory, you hold the track’s spine end to end: normalize by default and bend only for a named, owned tradeoff; let the index’s leading column match the access pattern; treat row estimates as the planner’s load-bearing input; keep transactions short so MVCC can reclaim; multiplex heavy backends through a small, measured pool; sequence breaking changes as expand-contract; and shard last, on a high-cardinality co-locating key, only after every single-node lever is spent. Now when you see a slow query, a ballooning table, or a connection storm, you know which layer to reach for first — and what evidence to collect before declaring a fix.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.