PostgreSQL in Practice #4 Advanced Index Strategy: Partial, Composite, Covering — and GIN, BRIN

Basics chapter 4 set the B-tree ground rules (leading columns, no column transforms, selectivity), and last chapter built the routine that finds slow queries. This chapter is the strategies you reach for when that routine brings back a query that a basic index can’t fix.

Partial indexes: the condition goes into the index #

Put a WHERE into the index definition and only matching rows are indexed.

-- index only unprocessed orders: if they're 1% of the table, the index is 1% of the size
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

The classic home is a table that is “large overall, but queries only ever look at a slice.” An orders table may hold hundreds of millions of rows while the application repeatedly looks only at unprocessed ones. A partial index is small and therefore fast, and when rows outside the indexed slice (completed orders) get updated, this index doesn’t need touching — so the write tax shrinks too. The one thing to remember: only queries whose condition implies the index’s condition (WHERE status = 'pending') can use it. The same construction gives you WHERE deleted_at IS NULL (only the live rows of a soft-delete table) and partial UNIQUE indexes (uniqueness among active rows only) — both production staples.

Covering indexes: never visiting the table #

In basics chapter 5’s table, the fastest scan was the Index Only Scan — answering from the index without touching the table. The requirement: every column the query needs must be inside the index. A covering index carries columns needed for the result but not for searching, via INCLUDE.

-- serve SELECT email, name FROM users WHERE email = ? from the index alone
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name);

Two cautions. First, the index grows by the INCLUDE columns and updates to those columns now touch the index, so reserve this for measured hot queries. Second, whether the Index Only Scan actually happens must be verified with EXPLAIN. Under MVCC, the visibility map — which records “is this row visible to all transactions” — can go stale, forcing table visits anyway; the thing that refreshes that map is next chapter’s VACUUM. Index strategy and VACUUM connect right here.

Beyond B-tree: GIN, GiST, BRIN #

GIN already appeared with JSONB in basics chapter 7. The extra one worth knowing in practice is BRIN: an extremely small index that stores only min/max per block range. For tables that only ever append in time order — the timestamp column of logs and events — it backs range searches at a few hundredths of a B-tree’s size. When a B-tree over hundreds of millions of time-series rows feels too heavy, this is the name to remember.

Cleanup: finding unused indexes #

Basics chapter 4 concluded that indexes are a write tax, so strategy includes cleanup. The evidence comes from the statistics views.

SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 — never read since statistics collection began — marks the candidates. Two cautions: UNIQUE indexes exist for the constraint, not for lookups, so exclude them (the NOT indisunique above), and an index might serve a rare month-end batch, so judge over a sufficiently long statistics window. With replication, read load may live on the standby — keep that in mind too (practice #8).

Finally, bloat. On heavily updated tables, indexes swell with the residue of MVCC’s dead tuples, until the same work takes more reads. Rebuild without stopping the service using REINDEX INDEX CONCURRENTLY idx_name; (the same family as practice #1’s CREATE INDEX CONCURRENTLY). Why bloat arises and how to keep it suppressed day to day — that main body is next chapter’s VACUUM.

Summary #

• Partial indexes are the answer for big tables where queries only see a slice: small, fast, and they even cut the write tax.
• Covering indexes (INCLUDE) aim for the Index Only Scan. Whether it lands depends on EXPLAIN and the visibility map (VACUUM).
• Remember the non-B-tree options by their homes: documents and arrays are GIN, overlap and proximity are GiST, huge time series are BRIN.
• Find unused indexes via idx_scan in pg_stat_user_indexes. Only UNIQUE indexes and rare batch users need care.
• Bloated indexes get REINDEX CONCURRENTLY. The mechanics and prevention of bloat are next chapter’s topic.