5 exercises on advanced PostgreSQL index vocabulary.
0 / 5 completed
1 / 5
What is a partial index in PostgreSQL?
Partial index:CREATE INDEX ON orders(status) WHERE status = 'pending' indexes only pending orders. It is smaller, faster to update, and optimized for queries that match the same predicate.
2 / 5
What type of queries does a GIN index accelerate?
GIN (Generalized Inverted Index): stores a mapping from element values to the rows that contain them. Ideal for @> (containment), @@ (full-text), or ? (key exists) operators on JSONB and arrays.
3 / 5
When would you choose a BRIN index over a B-tree?
BRIN (Block Range Index): stores min/max values per range of disk pages. On an append-mostly table ordered by timestamp, BRIN is tiny and fast because it can skip entire page ranges that fall outside the query range.
4 / 5
What is an index bloat problem?
Index bloat: PostgreSQL's MVCC keeps old row versions; the index points to them until VACUUM reclaims them. Heavy write workloads without regular vacuuming cause bloat, degrading scan performance.
5 / 5
What does CREATE INDEX CONCURRENTLY accomplish?
CONCURRENTLY: regular CREATE INDEX locks writes on the table. The CONCURRENTLY option builds the index in multiple passes with only brief shared locks, safe for production with live traffic.