Sample questions
Pg Mvcc VacuumDifficulty 1
Given: CREATE TABLE t(id int, v int); INSERT INTO t VALUES (1,10); UPDATE t SET v = 20 WHERE id = 1; What actually happens to the original row version on disk when the UPDATE runs?
- aA new row version is inserted and the original is marked as a dead tuple.✓
- bThe original row bytes are overwritten with the new value in place.
- cThe original row is deleted immediately and its disk space is freed.
- dThe change is only applied in the WAL and never in the table.
Explanation:PostgreSQL's MVCC never overwrites a heap row in place. An UPDATE inserts a brand-new row version and marks the old version's xmax, turning it into a dead tuple that stays in the table until a later VACUUM reclaims its space.
Pg Mvcc VacuumDifficulty 1
Real output from a fresh table right after two inserts:
xmin | xmax | ctid | id
-------+------+-------+----
12260 | 0 | (0,1) | 1
12260 | 0 | (0,2) | 2
What does
xmax = 0 indicate for these rows?
- aThe rows were deleted but the deleting transaction has not committed yet.
- bThe rows have never been deleted or updated by any transaction.✓
- cThe rows are corrupted and need a REINDEX.
- dThe table has no primary key defined.
Explanation:xmax stores the id of the transaction that deleted or updated away a row version. A value of 0 means it was never set, so the row is still the current, live version — it has not been touched by any DELETE or UPDATE.
Pg Mvcc VacuumDifficulty 2
Real, verified before/after from the same row:
Before: xmin=12260, ctid=(0,1). After UPDATE accounts SET balance = balance + 50 WHERE id = 1;: xmin=12261, ctid=(0,3). Why did ctid change even though only the balance column was touched?
- actid is a random value PostgreSQL reassigns on every SELECT for security.
- bctid only changes when a column referenced by a foreign key is updated.
- cctid is the row's physical address; UPDATE always writes it to a new location.✓
- dctid is derived from the primary key value, so it changes whenever id-related columns update.
Explanation:ctid is the physical (page, offset) address of a row version, not a stable logical identifier. Since UPDATE always creates a brand-new tuple rather than editing in place, that new tuple lands at a new physical location, so its ctid necessarily differs from the old one, regardless of which column changed.
Pg Mvcc VacuumDifficulty 2
In PostgreSQL, what is generally meant by "table bloat"?
- aThe extra space PostgreSQL reserves upfront when a table is created.
- bThe compressed size difference between TOAST and inline storage.
- cThe memory used by shared_buffers to cache a table's pages.
- dDisk space from dead tuples and page space VACUUM hasn't reclaimed yet.✓
Explanation:Bloat is the accumulation of dead tuples (from UPDATEs and DELETEs) and leftover free space inside a table's pages that has not yet been reclaimed or reused, making the table's on-disk size larger than the amount of live data it holds would otherwise require.
Pg Mvcc VacuumDifficulty 2
What is the key practical difference between VACUUM and VACUUM FULL regarding disk usage?
- aVACUUM reuses freed space inside the file; VACUUM FULL rewrites the table and shrinks the file.✓
- bVACUUM only works on indexes, while VACUUM FULL only works on the table heap.
- cVACUUM requires a full table rewrite while VACUUM FULL just updates statistics.
- dVACUUM FULL is a synonym for VACUUM; the FULL keyword only changes log verbosity.
Explanation:Plain VACUUM frees dead tuples' space for reuse by future inserts/updates within the same table file, but it typically leaves the file's size on disk unchanged. VACUUM FULL instead builds a fresh, compact copy of the table, so it can actually shrink the on-disk file and return space to the OS.
Pg Mvcc VacuumDifficulty 3
You run VACUUM FULL accounts; on a busy production table. While it's running, another session runs a plain SELECT * FROM accounts;. A real pg_locks check during a VACUUM FULL on a similar table showed mode = AccessExclusiveLock, granted = t. What happens to the concurrent SELECT?
- aThe SELECT runs immediately, because VACUUM FULL only takes a lock on the table's indexes, not the heap.
- bThe SELECT blocks and waits, since VACUUM FULL holds an ACCESS EXCLUSIVE lock.✓
- cThe SELECT runs immediately using the old data, because MVCC lets readers bypass any lock held by VACUUM FULL.
- dThe SELECT fails immediately with an error instead of waiting.
Explanation:AccessExclusiveLock is the strongest lock mode in PostgreSQL and conflicts with every other lock mode, including the AccessShareLock a plain SELECT needs. So while VACUUM FULL is rewriting the table, any concurrent SELECT simply waits until the lock is released.