All quizzesHard
Index Internals & Bloat — Series 3
Preview — 3 of 10 questions
After a large DELETE removes most rows referenced by a B-tree index, VACUUM runs. Does the index's on-disk file shrink immediately to reflect the freed space?
ANo — VACUUM marks the now-empty index pages as reusable (recording them so future insertions can reclaim that space) rather than shrinking the file; the file's size on disk generally stays the same unless a plain VACUUM happens to be able to truncate trailing all-empty pages, or a VACUUM FULL/REINDEX fully rebuilds the structure
BYes — VACUUM always truncates the index file down to exactly the space still in use
CVACUUM deletes the index entirely and it must be recreated manually
DIndex files never change size for any reason after creation
An index scan that used to take 2ms now takes 40ms after the table grew far beyond available RAM, with the same query plan (Index Scan) chosen both times. What changed?
AWhen the index's frequently-accessed pages fit in shared_buffers (or the OS page cache), lookups are served from memory; once the working set of pages needed no longer fits, more of those page reads become actual disk I/O, and that I/O latency — not the index's logical structure — dominates the time, even though the plan and the number of logical page reads may be similar
BPostgreSQL automatically switches B-tree indexes to a less efficient format above a certain size threshold
CThe index itself became a different, slower data structure once the table grew large enough
DThis can only happen if autovacuum has stopped running
A large, heavily-bloated index needs to be compacted, but the table can't tolerate the long exclusive lock a plain REINDEX would hold. What are the standard approaches?
AREINDEX CONCURRENTLY (builds a fresh index alongside the old one with only brief locks at the start/end, then swaps it in), or the third-party pg_repack extension (which similarly rebuilds a table/index online and swaps it in) — both avoid holding a single long-lived exclusive lock for the whole rebuild, unlike plain REINDEX
BThere is no way to avoid locking; bloat can only be fixed during scheduled downtime
CRunning VACUUM repeatedly in a loop until the index shrinks on its own
DManually editing the index's binary file while the database is offline
Sign up free to play
Answer all 10 questions (7 more), see explanations for every answer, and track your score.