PostgreSQL Internals
How PostgreSQL actually works underneath the SQL — heap pages and tuple headers, the planner's cost model, XIDs and snapshots, VACUUM and freezing, the buffer manager, WAL and replication — remembered with spaced repetition.
- flashcards
- 112
- flashcards
- per day
- ~10 min
- per day
- level
- Advanced
- level
- modules
- 7
- modules
What this track is for
You can run PostgreSQL for years treating it as a black box — until a table bloats while autovacuum is running, a replica falls hours behind, or a checkpoint stalls writes. This track opens the box. It follows a row from the heap page and line pointer it lives in, through the xmin, xmax and snapshot that decide who can see it, to the VACUUM and freeze passes that eventually clean it up.
The second half follows the bytes instead of the rows: how the buffer manager picks a page to evict, why a dirty page cannot leave memory before its WAL is flushed, what a checkpoint actually guarantees, and how the same WAL stream drives point-in-time recovery, timelines and streaming and logical replication. The planner gets its own module — the cost model, the statistics behind row estimates, and how the three join algorithms are chosen.
It is the third PostgreSQL tier, after PostgreSQL in Practice and PostgreSQL Advanced. The cards target PostgreSQL 18, including its new asynchronous I/O; cards whose answer depends on a version say so in the question. 7 modules, every card citing the official documentation or the PostgreSQL source tree.
7 modules, seed to bloom
Each module is a set of flashcards — 112 in total. Answer, review, and watch your knowledge grow from seed to full bloom.
Cluster Layout, Processes & Pages
Where PostgreSQL keeps data on disk, how a heap page is laid out, and the process-per-connection model
16 cardsPlanner & Executor
Cost model, planner statistics, join algorithms, parallel query and plan caching
16 cardsMVCC Internals: XIDs, Clog & Snapshots
Transaction IDs, tuple headers, commit status, snapshots and row-level locks
16 cardsVACUUM, Freezing & HOT Pruning
Dead-tuple cleanup, the visibility map, freezing and wraparound, autovacuum triggers
16 cardsBuffer Manager & I/O
Shared buffers, pins and locks, clock-sweep eviction, ring buffers and PostgreSQL 18 asynchronous I/O
16 cardsWAL, Checkpoints & Crash Recovery
Write-ahead logging, LSNs, full-page images, checkpoints and redo
16 cardsBackup, PITR & Replication
Base backups, point-in-time recovery, timelines, streaming and logical replication
16 cardsSample questions
A taste of the real flashcards. Pick an answer, then reveal the explanation.
What do the xmin and xmax fields in a heap tuple header record?
- AThe ID of the transaction that inserted the version, and the ID of the one that deleted or locked it
- BThe oldest and newest snapshot that may still see the version, as set when the row was last vacuumed
- CThe lowest and highest transaction ID stored on the page, used to decide when it can be frozen
- DThe ID of the session that created the version, and the ID of the session that last read it
Why can a forgotten replication slot stop VACUUM from removing dead tuples?
- AIts xmin records the oldest transaction it needs — VACUUM cannot remove tuples deleted by any later one
- BIt retains the WAL it still needs — VACUUM keeps every tuple whose change is in that unconsumed WAL
- CIt locks each table it replicates — VACUUM skips any table that a replication slot is holding open
- DIt counts as an active subscriber — autovacuum skips tables still published to a connected subscriber
Which replacement algorithm does PostgreSQL use to pick a victim buffer?
- AClock sweep — a hand cycles through buffers, decrementing usage counts until it finds an unpinned zero
- BStrict LRU — a linked list moves each touched buffer to the head, and the tail buffer is the victim
- C2Q — separate queues track pages seen once and pages seen repeatedly, and the first queue is evicted
- DFIFO — buffers are recycled in the order they were loaded, whatever their usage since being loaded
Where does crash recovery start replaying WAL?
- AAt the REDO location of the last checkpoint, which can precede the checkpoint record itself
- BAt the start of the oldest segment in pg_wal, replaying every record found there in order
- CAt the last commit record in pg_wal, replaying backward to undo uncommitted changes
- DAt the LSN of the last base backup, replaying forward through the archive to the crash point
Learn it once, keep it for good
Answer a question
Each card is one practical concept with multiple options. Pick what you think is right.
Get the full answer
See the correct option plus a clear explanation, and a link to deeper docs when one is available.
Review at the right time
A spaced-repetition engine (SM-2 or FSRS) resurfaces each card just before you would forget it.
Why internals are worth learning
Bloat you can explain
Once you know what holds back the cleanup horizon — an old snapshot, a forgotten slot, a prepared transaction — "VACUUM is not removing rows" becomes a five-minute diagnosis.
Plans you can predict
Row estimates come from pg_class and pg_stats. Knowing exactly how turns a surprising plan into a statistics question with an answer.
Durability you can reason about
Write-ahead logging, full-page images and checkpoints explain what synchronous_commit = off really risks, and why recovery is always safe to replay.
Replication without surprises
Timelines, slots and standby conflicts are where most replication incidents start. Each one has a mechanism you can learn ahead of time.
Common questions
Which PostgreSQL version do the cards target? +
PostgreSQL 18. Most of the internals are stable across releases; where an answer depends on a version — asynchronous I/O and io_method in 18, incremental backup in 17, Memoize in 14 — the question says so, and cards about a PostgreSQL 18 default link to the version 18 documentation.
Do I need to read the PostgreSQL source code? +
No. Every card stands on its own and links to the page that backs it — usually the official documentation, and for a few buffer manager and WAL details that the docs do not cover, a README or header file in the PostgreSQL source tree.
Should I do the other PostgreSQL tracks first? +
It helps. PostgreSQL in Practice covers indexes, EXPLAIN and MVCC from the user's side, and this track assumes that vocabulary. PostgreSQL Advanced is about design judgment and can be taken in either order.
Is it free? +
Yes, completely free. No registration or credit card is required, and all your progress is stored locally in your browser.
Ready to look inside PostgreSQL?
Plant your first seed today. Ten minutes a day turns the black box into a system you can reason about.