d. Indexes & EXPLAIN

Since lab 1 the catalog listing — upcoming events in a city, soonest first — has been the slow endpoint. Lab 5 hid it behind a cache. Before reaching for more infrastructure, ask the database how it runs the query. Often the cheapest scaling decision of all is an index.

1. Read the plan

The query generated by findUpcomingInCity (page + count) is equivalent to:

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.*, v.*
FROM catalog.events e JOIN catalog.venues v ON v.id = e.venue_id
WHERE v.city = 'Recife' AND e.starts_at >= now()
ORDER BY e.starts_at, e.id
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM catalog.events e JOIN catalog.venues v ON v.id = e.venue_id
WHERE v.city = 'Recife' AND e.starts_at >= now();

docker compose exec db psql -U boxoffice

Before any index, the plan looks like this (from a test run for this lab, abridged; your numbers will differ):

EXPLAIN ANALYZE — before

Limit  (actual time=35.620..36.762 rows=20)
  ->  Gather Merge
        ->  Sort  (Sort Key: e.starts_at, e.id; Sort Method: top-N heapsort)
              ->  Hash Join  (Hash Cond: (e.venue_id = v.id))
                    ->  Parallel Seq Scan on events e  (rows=100000 loops=2)
                    ->  Hash  ->  Seq Scan on venues v  (Filter: city = 'Recife'; rows=5)
Execution Time: 36.802 ms          -- and 13.9 ms more for the count(*)

How to read a plan:

Node	Meaning
`Seq Scan`	reads every row of the table
`Index Scan` / `Index Only Scan`	walks a B-tree; Only means it never touches the table
`Hash Join`, `Nested Loop`, `Merge Join`	three ways to join; nested loop is great when the outer side is small and the inner side has an index
`Sort`	sorts in memory (or on disk: `external merge`) — avoidable when an index already delivers the order
`actual time=a..b rows=n`	time to the first and to the last row, and real row count; compare with the estimated `rows` to spot bad statistics

The problem is visible: 200,000 rows are read and sorted to return 20.

2. Add the indexes

Add a new migration to catalog — never edit V1/V2:

services/catalog/src/main/resources/db/migration/V3__index_events.sql
-- Lab 6: indexes for the listing "upcoming events in a city, soonest first".
-- Walk events in starts_at order and stop after one page, instead of sorting 20,000 rows:
CREATE INDEX events_starts_at_idx ON catalog.events (starts_at, id);
-- Count/filter the events of a city's venues without reading the whole table:
CREATE INDEX events_venue_starts_at_idx ON catalog.events (venue_id, starts_at);
CREATE INDEX venues_city_idx ON catalog.venues (city);

docker compose up --build -d catalog-1 catalog-2 catalog-3      # Flyway applies V3 on startup
docker compose logs catalog-1 catalog-2 catalog-3 | grep "V3\|version \"3"   # one replica applies it

Run the two EXPLAIN ANALYZE again:

EXPLAIN ANALYZE — after

Limit  (actual time=0.064..0.442 rows=20)
  ->  Nested Loop
        ->  Index Scan using events_starts_at_idx on events e  (rows=189)
        ->  Index Scan using venues_pkey on venues v  (loops=189)
Execution Time: 0.620 ms

Aggregate  (actual time=4.006..4.006 rows=1)
  ->  Nested Loop
        ->  Seq Scan on venues v  (rows=5)
        ->  Index Only Scan using events_venue_starts_at_idx on events e  (rows=4000 loops=5)
Execution Time: 4.028 ms

With events_starts_at_idx, PostgreSQL walks the events already in date order and stops as soon as it has 20 from Recife (it read 189 index entries instead of 200,000 rows): the page query drops from ~37 ms to well under 1 ms. The count still visits every event of the city, but through the smaller (venue_id, starts_at) index, without touching the table (Index Only Scan): ~14 ms → ~4 ms.

Indexes are not free

Every index is updated on every insert and update of the table, takes disk and memory, and can make the planner choose badly when statistics are stale. On a write-heavy table (seats during the sale!) each extra index slows the critical path. Index for the queries you measured, not for every column. On a large production table, use CREATE INDEX CONCURRENTLY (outside a transaction) to avoid blocking writes while it builds.

3. Measure it

Turn the cache off so that every request reaches PostgreSQL, and repeat the lab 2/3 sweep through the edge:

CACHE_ENABLED=false docker compose up -d catalog-1 catalog-2 catalog-3 && sleep 30
BASE_URL=http://localhost OUT=results/lab6-index bash scripts/sweep.sh 300 600 900 1200 1500
uv run scripts/summarize.py results/lab6-index/rate-*.json

Compare with the no-cache numbers of lab 3. Then turn the cache back on and decide, with numbers, what the listing really needed.

Think first: the count query still reads 20,000 index entries per request. What would you do about it?

Options, from cheapest: cache the count separately with a longer TTL (it changes slowly); return an estimate ("20,000+"); drop it and return only hasNext (a Slice instead of a Page); or maintain a counter per city updated on writes. Each trades exactness for cost — a product decision as much as a technical one.

In the interview

When a query is slow, the order is: measure (which query, how often), explain (what plan), fix the access path (index, rewrite, denormalize), and only then add infrastructure (cache, replica, shard). Interviewers like to hear that you would not cache a query you have not explained.

Next: 4. Failover.