All quizzesHard
Index Internals & Bloat — Series 2
Preview — 3 of 10 questions
What does PostgreSQL 13's B-tree index deduplication feature do?
AFor non-unique indexes, when many rows share the same key value (e.g., a low-cardinality status column), PostgreSQL can store one copy of the key value with a compressed list of pointers to all matching table rows, instead of repeating the full key value for every entry — reducing index size
BIt removes duplicate rows from the underlying table automatically
CIt merges two indexes that have identical definitions into one automatically
DIt only applies to GIN indexes, not B-tree
A table has an index CREATE INDEX ON orders (user_id) INCLUDE (status). An UPDATE changes only status (not user_id). Does this update qualify for a HOT (Heap-Only Tuple) update?
AYes — INCLUDE columns are never considered indexed columns for HOT eligibility purposes, only key columns matter
BNo — HOT requires that none of the columns referenced by any index (key columns AND INCLUDE columns) are modified; since status is an INCLUDE column of this index, updating it disqualifies the update from being HOT, and the index entry must be updated
CIt depends on whether the new status value happens to be alphabetically before the old one
DHOT only applies to primary key indexes, so this question is moot regardless of INCLUDE
What controls how many worker processes PostgreSQL can use when building an index with CREATE INDEX?
AIndex builds are always single-threaded and cannot be parallelized
Bmax_worker_processes alone, with no dedicated maintenance-specific limit
Cmax_parallel_maintenance_workers, a separate setting specifically for maintenance operations like CREATE INDEX and VACUUM
Dmax_parallel_workers_per_gather, the same setting used for parallel query execution
Sign up free to play
Answer all 10 questions (7 more), see explanations for every answer, and track your score.