Interview · concept drills
Say one mechanism out loud
34 drills. Answer first, then reveal. This is rehearsal, not the engine course.
Three different doors
- Lessons, how the engine works (see it move, lab proof). The only engine course.
- Concept drills, you are here. Out-loud interview practice on one idea at a time.
- Deep Internals pathway, a study order through Lessons, not a second catalog.
Free drills open all the way, prompt, docs, source, real run. Other drills keep the question open; Pro unlocks the rest. Need the full mechanism first? Start on Lessons.
Showing 34 of 34
Autovacuum thresholds: the dead-tuple trigger formula and what drives it
Explain exactly when autovacuum decides to vacuum a table. What is the threshold formula, which counters feed it, and why can a high-churn table still bloat even though autovacuum is enabled?
Preview Pro concept →B-tree deduplication & posting-list tuples
What is B-tree deduplication, what is a posting-list tuple, and how would you prove an index is using it, and what are the cases where it does NOT apply?
Open free concept →B-tree on-disk anatomy: the metapage, the levels, and how a range scan moves
Walk me through what a PostgreSQL B-tree index actually looks like on disk. What is in the metapage, how are the levels arranged, how does a range scan move across leaves, and what is the 'high key' for?
Preview Pro concept →B-tree page splits: why an ascending key packs tighter than a random one
Two tables, same 300000 rows, same single-column index. One is loaded on an ascending key, the other on a random key. The random one's index is noticeably bigger on disk. Walk me through why -- get into how B-tree page splits actually choose where to split.
Preview Pro concept →BufferAccessStrategy ring buffers: why big seq scans/VACUUM/COPY don't trash shared_buffers
Why doesn't a large sequential scan (or VACUUM, or COPY) evict your entire shared_buffers cache? Explain BufferAccessStrategy ring buffers, when they kick in, how big they are, and how you'd prove it.
Preview Pro concept →Clock-sweep buffer eviction, usage_count & the BAS_BULKREAD ring
How does PostgreSQL decide which shared buffer to evict when the cache is full, what is usage_count, and why doesn't one big sequential scan flush the whole buffer cache?
Preview Pro concept →The commit log (clog/pg_xact): where a transaction's outcome is recorded
When a transaction commits, where exactly is 'this xid committed' recorded, and how does another transaction later find out whether some xid committed or aborted? How big is that per-transaction record, how many transactions fit on one page, and what happens to that information once the xid is frozen?
Preview Pro concept →Connection scaling and GetSnapshotData (the PG14 dense-array snapshot rewrite)
Production wisdom says 'idle-in-transaction connections destroy snapshot performance for the whole cluster' and 'thousands of idle connections make GetSnapshotData O(N) and crush you'. On a modern PostgreSQL (14+), which half of that is still true and which half is folklore? Walk me through what GetSnapshotData actually scans, what makes a snapshot cheap to take, and prove where the real wall is when you scale connections.
Preview Pro concept →ctid and line pointers: why a row's physical address isn't a stable id
What does a row's ctid actually point at, is the second number a byte offset into the page? Walk me through what happens to a row's ctid when you UPDATE it, how an index entry still finds the row afterward, and explain why ctid is a dangerous thing to store as a permanent row identifier.
Preview Pro concept →Extended statistics (CREATE STATISTICS: dependencies, ndistinct, MCV)
Two columns in a table are strongly correlated (think city and postal_code, or status and stage). A query filters on both with AND, the row estimate is off by 100x, and the planner picks a terrible join. Explain WHY the default planner gets this wrong, then walk me through the three kinds of extended statistics, which symptom each one fixes, what the planner actually does differently with each, and why even a 'perfect' dependency doesn't make the estimate exactly right.
Preview Pro concept →The Free Space Map: how INSERT finds a page with room
I delete a few hundred thousand rows from a table and the table file on disk doesn't shrink -- fine, I know VACUUM doesn't truncate. But I also notice new INSERTs keep appending to the END of the table instead of reusing the space I just freed, until I run VACUUM. Walk me through the mechanism. What is the Free Space Map and exactly when does it learn about freed space?
Preview Pro concept →Generic vs custom plans for prepared statements
Explain custom vs generic plans for a prepared statement: when does PostgreSQL switch, how is the decision made, how do you observe which plan was used, and what is the failure mode on skewed data?
Preview Pro concept →HashAggregate spill to disk and hash_mem_multiplier (work_mem * multiplier ceiling)
How does HashAggregate decide it has run out of memory, and what is hash_mem_multiplier? Walk me through what 'Batches' and 'Disk Usage' mean in an EXPLAIN ANALYZE HashAggregate node, and why a GROUP BY that was fine in PostgreSQL 11 can behave very differently in 13+.
Preview Pro concept →Hint bits: why the first read after a write dirties pages
Right after a big COPY/INSERT load that has already committed, the very FIRST SELECT over those rows is slow and -- surprisingly -- generates a lot of dirty pages and WAL-ish write activity, even though it's a read-only query that changes no data. Run it a second time and it's fast and clean. Explain what's happening at the tuple level.
Preview Pro concept →HOT updates & page pruning
What is a HOT (heap-only tuple) update, when does Postgres choose it, and how does opportunistic page pruning reclaim dead row versions without VACUUM?
Preview Pro concept →Index LP_DEAD pruning (btree kill_prior_tuple)
After you DELETE or UPDATE a lot of rows but BEFORE autovacuum runs, what removes the now-dead entries from a B-tree index, and what actually triggers it? Walk me through how a plain SELECT can shrink an index's logical garbage with no VACUUM, why that same read sometimes does nothing at all, and how this keeps a heavily-churned index from bloating.
Preview Pro concept →Isolation anomalies and Serializable Snapshot Isolation (write skew, SSI)
Explain the difference between PostgreSQL's REPEATABLE READ and SERIALIZABLE isolation levels. Both give you a stable snapshot, so what can still go wrong under REPEATABLE READ? Demonstrate the write-skew anomaly, then explain HOW SERIALIZABLE (SSI) detects and prevents it, what it locks, what a 'dangerous structure' / pivot is, and what error the loser gets. Why is SERIALIZABLE not just 'take more locks'?
Preview Pro concept →Planner join selection: nested loop vs hash vs merge, the cost crossover, and disable_cost
PostgreSQL has three join executors, nested loop, hash join, and merge join. Walk me through how the planner chooses between them, what the cost crossover looks like, and what it means when EXPLAIN shows a join cost like 10000000006.12.
Preview Pro concept →Heavyweight lock manager (lock modes, conflict table, fast-path, deadlock detector)
Walk me through PostgreSQL's heavyweight lock manager. What are the table-level lock modes and how does the planner/executor decide two operations conflict? Then explain the 'fast path', what it is, which locks use it and which can't, and why it exists. Finally, how does PostgreSQL detect a deadlock, how does it choose the victim, and what does the waiter actually wait ON when two UPDATEs deadlock?
Open free concept →Multixacts (shared row locks)
What is a multixact (MultiXactId), when does PostgreSQL create one, and how does it change the meaning of a tuple's t_xmax? Why do multixacts have their own wraparound horizon separate from transaction IDs?
Preview Pro concept →MVCC snapshot visibility
How does PostgreSQL's MVCC decide whether one transaction sees another's committed change? Walk through xmin/xmax, the snapshot (xmin:xmax:xip), and why a REPEATABLE READ reader can keep seeing the old value after a concurrent commit.
Open free concept →Parallel query: the Gather node, and planned vs launched workers
Explain how a parallel sequential scan actually runs. Where do the workers come from, who decides how many, what is the difference between 'Workers Planned' and 'Workers Launched', and what does the leader do while the workers run?
Preview Pro concept →Partition pruning (plan-time, init-time, exec-time)
A query against a table partitioned by month only touches one month, yet someone tells you 'partition pruning happens at plan time.' Push past that: name the THREE distinct moments PostgreSQL can prune a partition, show how each one looks different in EXPLAIN, and explain which one a parameterized/prepared query depends on and why a partition can still appear in the plan but never run. Then tell me the one thing that silently turns all of it off.
Preview Pro concept →How to read EXPLAIN plans like a pro
Walk me through how you read an EXPLAIN plan. Given a slow query in production, how do you find the problem in the plan and prove your fix worked?
Open free concept →Why sequences leave gaps: caching, WAL log-ahead, and rollback
Our primary-key sequence has gaps -- it jumps from 1 to 101, and after a crash it skipped ~30 values, and a rolled-back transaction still 'used up' an id. The customer thinks we're losing data. Walk me through why a PostgreSQL sequence is allowed to have gaps and where each kind of gap actually comes from.
Preview Pro concept →Subtransaction SLRU overflow (the 64-subxid cliff)
A backend can cache only 64 active subtransaction XIDs. What exactly happens on the 65th, why does it silently slow down every OTHER session's visibility checks across the whole cluster (not just the backend that overflowed), and how would you prove and detect it in production? Where do subtransactions get created without anyone writing SAVEPOINT?
Preview Pro concept →TOAST (The Oversized-Attribute Storage Technique): compression, out-of-line storage, chunking
A heap tuple has to fit on an 8KB page, yet you can store a 1MB text or jsonb value in a column. Explain how. Walk me through what the 'toaster' does when a row is too big, the order of operations, the difference between the four storage strategies (PLAIN/MAIN/EXTERNAL/EXTENDED), where the bytes actually end up, and how a value is reassembled on read. Then tell me the practical performance traps TOAST creates.
Preview Pro concept →Tuple freezing & transaction-ID wraparound
What does VACUUM FREEZE actually change on a tuple, and how does freezing prevent transaction-ID wraparound from making old rows disappear? Does it overwrite t_xmin?
Preview Pro concept →How PostgreSQL sorts: quicksort, external merge, and top-N heapsort
EXPLAIN ANALYZE shows three different 'Sort Method' lines depending on the query: 'quicksort Memory', 'external merge Disk', and 'top-N heapsort Memory'. Walk me through what each one means, what makes PostgreSQL choose between them, and where work_mem comes into it.
Preview Pro concept →Visibility map & index-only scans
What is the visibility map, what is the 'Heap Fetches' line in an Index Only Scan, and why can a long-running transaction silently turn a fast index-only scan back into one that visits the heap?
Preview Pro concept →WAL full-page images (FPI) and checkpoints
What is a WAL full-page image, when exactly does PostgreSQL write one, why does it exist, and how would you prove and measure it?
Preview Pro concept →WAL records: what an UPDATE writes, and the WAL-before-data rule
Walk me through what PostgreSQL writes to the WAL when I run a single UPDATE. What is a full-page image, why does the *first* write to a page after a checkpoint cost so much more WAL than the next one, and what is the durability rule that ties WAL flushing to data-page writes?
Open free concept →ANALYZE from source: sampling rows into pg_statistic
Walk through what ANALYZE does in the backend. Does it read every page of a 50TB table? Where do the numbers the planner uses actually live, and which source functions build versus consume them?
Preview Pro concept →Executor PlanState tree and ExecProcNode pull
After the planner returns a Plan, what does the executor build, how do tuples move, and how do EXPLAIN ANALYZE’s loops and startup time fall out of that design?
Preview Pro concept →