Index Internals & Bloat — Series 2

Preview — 3 of 10 questions

What does PostgreSQL 13's B-tree index deduplication feature do?

javascript

Before: [status='PENDING', tid=1], [status='PENDING', tid=2], [status='PENDING', tid=3], ...
After:  [status='PENDING', tids=[1,2,3,...]]  ← one key, many TIDs (posting list)

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?

javascript

CREATE INDEX idx_orders_user ON orders (user_id) INCLUDE (status);

UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
-- status is an INCLUDE column of idx_orders_user, so this update
-- is NOT HOT-eligible — the index entry must be updated too

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?

javascript

SHOW max_parallel_maintenance_workers;  -- default: 2

SET maintenance_work_mem = '1GB';       -- more memory helps parallel sort too
CREATE INDEX idx_big_table_col ON big_table (col);  -- can use up to max_parallel_maintenance_workers

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

Answer all 10 questions (7 more), see explanations for every answer, and track your score.