Stage 7 · Master
Phase 18 — Performance Engineering
Database Indexing
Read real EXPLAIN (ANALYZE, BUFFERS) plans, order composite index columns by predicate shape, and choose partial, covering, and GiST indexes for the queries the HOA platform actually runs.
Start From a Slow Endpoint, Not a Guess
Organization has been in production for six months. Support opens a ticket: the resident directory screen — GET /v1/flats/{flatID}/occupants, which lists everyone currently occupying a flat — takes 900ms on the largest tenant's dashboard and 4ms on every small one. That asymmetry is the first diagnostic fact: this is not a code-path bug, it is a data-shape problem, and the occupancies table for the largest organization has crossed 6 million rows while most organizations have a few hundred. Guessing at an index before reading the plan wastes a migration and a deploy; the fix has to start from what PostgreSQL actually decided to do.
The repository method behind the endpoint is unremarkable Go: it opens a transaction, runs one query, and scans rows into a slice. The query is the entire investigation surface.
SELECT id, resident_id, kind, valid_during
FROM occupancies
WHERE organization_id = $1
AND flat_id = $2
AND valid_during @> now();"Who occupies this flat right now" is a range-containment predicate, not a simple equality lookup — that distinction drives every decision in this lesson.
Seq Scan on occupancies (cost=0.00..184213.55 rows=1 width=64) (actual time=812.209..899.041 rows=3 loops=1)
Filter: ((organization_id = '3fce...') AND (flat_id = 'a921...') AND (valid_during @> now()))
Rows Removed by Filter: 6142887
Buffers: shared hit=482 read=91820
Planning Time: 0.183 ms
Execution Time: 899.089 msPostgreSQL read 6.1 million rows to return 3. The planner's own estimate — rows=1 — shows it expected a cheap, selective scan; the 91,820 buffer reads it actually performed are the true cost the planner had no index to avoid.
cost=0.00..184213.55 is the planner's own unit-less estimate; rows=3 next to actual time=899.041 is what really happened. A large gap between estimated and actual rows means either stale statistics or, as here, no index the planner could have used regardless of statistics.
Order Composite Index Columns by Predicate Shape, Not Alphabet
organization_id and flat_id are both equality predicates; valid_during @> now() is a containment test a plain B-tree cannot accelerate at all — B-tree only orders scalar values, and a range type's containment operator is not a </= comparison it can index natively. The first fix is not the containment predicate; it is making sure the two equality columns are not forcing a scan of the whole table before containment is ever evaluated.
CREATE INDEX CONCURRENTLY occupancies_org_flat_lookup
ON occupancies (organization_id, flat_id);
CONCURRENTLY avoids the ACCESS EXCLUSIVE lock a plain CREATE INDEX takes — required because occupancies is written on every move-in and move-out, all day, on the largest tenant's schedule too.
DROP INDEX CONCURRENTLY occupancies_org_flat_lookup;
DROP INDEX CONCURRENTLY is supported the same way CREATE INDEX CONCURRENTLY is — it avoids the same ACCESS EXCLUSIVE lock on the way back down.
This index narrows the seq scan to an index scan over the two equality columns, but valid_during @> now() is still evaluated as a row-by-row filter after the index narrows the candidate set. For a flat with a long occupancy history — five years of move-ins and move-outs — that filter still walks every historical row for that one flat before finding the one with an open-ended or currently-containing range.
GiST Indexes Answer Containment Directly
btree_gist was already enabled in Part 5 for the exclusion constraint that prevents overlapping tenant occupancies. The same extension makes valid_during indexable for containment, not just for exclusion — a GiST index stores the range's bounding structure and can answer "which rows contain this instant" without a linear filter.
golang-migrate's pgx/v5 driver sends each migration file's statements to Postgres together over the simple query protocol, and Postgres implicitly wraps a multi-statement message in one transaction. CREATE INDEX CONCURRENTLY refuses to run inside a transaction block, so a second CONCURRENTLY index cannot share 00007's file — it needs its own migration, 00008, even though both indexes land on the same occupancies table in the same lesson.
CREATE INDEX CONCURRENTLY occupancies_current_gist
ON occupancies USING gist (organization_id, flat_id, valid_during)
WHERE upper_inf(valid_during) OR valid_during @> now();
gist_int4_ops and range containment can share a multicolumn GiST index when the leading columns use the btree_gist opclasses; PostgreSQL builds one composite GiST structure covering equality and range together.
DROP INDEX CONCURRENTLY occupancies_current_gist;
Only this migration's own index is dropped here; 00007's occupancies_org_flat_lookup has its own down file and its own lifecycle.
This index only helps queries whose predicate PostgreSQL can prove is a subset of upper_inf(valid_during) OR valid_during @> now(). A query written as valid_during @> now() alone still qualifies since it implies that disjunction, but valid_during && tstzrange(now(), now(), '[]') does not match syntactically and the planner will silently fall back to the non-partial index or a seq scan.
Shrink the Index to the Rows That Matter
The partial predicate above is also a size decision. Roughly 94% of rows in the largest tenant's occupancies table are historical — residents who moved out years ago. A partial index that only indexes rows where the occupancy is still open or currently active is a fraction of the table's size, fits comfortably in shared_buffers, and never needs to be touched by an INSERT for a new historical record that closes an old range, because closing a range makes upper_inf false and the row leaves the partial index entirely on the next update.
| Index | Definition | Approximate size | Serves |
|---|---|---|---|
| occupancies_org_flat_lookup | btree (organization_id, flat_id) | Full table, ~340 MB | Any lookup scoped to one flat, regardless of time |
| occupancies_current_gist | gist (org, flat, valid_during) WHERE upper_inf OR contains now() | ~20 MB (current occupancies only) | "Who lives here right now" — the directory screen's exact question |
A covering index closes a different gap: even with the GiST index above, PostgreSQL still visits the heap to fetch resident_id and kind, because a GiST index over valid_during cannot INCLUDE non-key payload columns the way a B-tree can. For the equality-only lookup index, adding an INCLUDE clause lets an index-only scan satisfy queries that never touch valid_during at all — the count endpoint on the same screen, for example.
CREATE INDEX CONCURRENTLY occupancies_org_flat_count
ON occupancies (organization_id, flat_id)
INCLUDE (kind);
-- Enables an Index Only Scan for:
SELECT kind, count(*)
FROM occupancies
WHERE organization_id = $1 AND flat_id = $2
GROUP BY kind;INCLUDE columns ride along in the index leaf pages without becoming part of the index's sort key — they widen the index slightly but let this specific query skip the heap entirely, provided the visibility map marks the relevant pages all-visible.
Every Index Is a Write-Path Tax
None of this is free. occupancies now carries three new indexes on top of its primary key and the existing exclusion constraint's GiST index. Every INSERT and every UPDATE that closes a valid_during range now maintains five index structures instead of two. Before shipping, the honest question is whether the read the platform just made 45x faster is hot enough to justify a write-path cost paid on every move-in and move-out, forever.
- Redundant indexes: a leading-column index is a strict subset of a wider index that starts with the same columns — organization_id alone would have been redundant next to occupancies_org_flat_lookup and was never created.
- Write amplification: five index updates per occupancies write, up from two, is measured in the write-path benchmark in Lesson 4, not assumed.
- Bloat: partial and GiST indexes still bloat under heavy UPDATE churn; autovacuum settings for occupancies were tuned in Part 7 and are re-verified, not re-created, here.
- CONCURRENTLY doubles build time and can fail partway, leaving an INVALID index that must be dropped and retried — never trust a CONCURRENTLY build without checking pg_index.indisvalid afterward.
PostgreSQL tracks every index's usage in pg_stat_user_indexes.idx_scan. An index created from a hunch and never verified against that counter after a week of real traffic is a write-path tax with no matching read benefit — remove it.
Verify the Plan Changed, Not Just That the Migration Ran
Bitmap Heap Scan on occupancies (cost=8.31..16.42 rows=1 width=64) (actual time=0.041..0.043 rows=3 loops=1)
Recheck Cond: ((organization_id = '3fce...') AND (flat_id = 'a921...'))
Heap Blocks: exact=1
-> Bitmap Index Scan on occupancies_current_gist (cost=0.00..8.31 rows=1 width=0) (actual time=0.031..0.031 rows=3 loops=1)
Index Cond: ((organization_id = '3fce...') AND (flat_id = 'a921...'))
Planning Time: 0.211 ms
Execution Time: 0.079 ms899ms to 0.079ms — an ~11,000x improvement on the exact query support reported, verified against real EXPLAIN output rather than assumed from the migration alone.
The verification is not done until idx_scan actually increments under real traffic. A plan captured immediately after a migration can reflect a warm cache that flatters the new index; the number that matters is whether pg_stat_user_indexes.idx_scan for occupancies_current_gist is climbing an hour after deploy, and whether occupancies_org_flat_lookup still shows scans from the count endpoint that does not use the GiST index.
Applied exercise
Index the visitor admission log without guessing
The gate app's admission screen runs SELECT * FROM access_events WHERE organization_id = $1 AND flat_id = $2 ORDER BY occurred_at DESC LIMIT 20 and support reports it is slow only for buildings with heavy visitor traffic.
- Capture EXPLAIN (ANALYZE, BUFFERS) for the query against a copy of production data before changing anything.
- Identify whether the bottleneck is a missing index, a wrong column order, or a sort that cannot use an index at all.
- Write the CREATE INDEX CONCURRENTLY migration, including its Down migration.
- Re-run EXPLAIN ANALYZE and record the before/after cost, rows, and actual time.
- Query pg_stat_user_indexes an hour after applying it under simulated traffic to confirm the index is actually used.
Deliverable
A migration file with matching up/down, a before/after EXPLAIN comparison, and a pg_stat_user_indexes reading proving real usage — not just a plan that looks better in isolation.
Completion checks
- The index column order places organization_id and flat_id before occurred_at, matching the query's equality-then-sort shape.
- The migration uses CREATE INDEX CONCURRENTLY and the Down migration drops it the same way.
- idx_scan for the new index is greater than zero after the traffic simulation, not just present in the catalog.
Why did occupancies_org_flat_lookup alone not fix the directory screen's latency, even though it eliminated the sequential scan?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.