Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Indexing & query optimisation

Index the access pattern, not the column: B-tree, hash, composite, covering, partial and expression indexes, what each one adds to every write, and how to read the plan that says whether it worked.

Three seconds for twenty bookmarks

One line repeats all day in the slow-query log of a read-it-later app: duration: 3412 ms statement: SELECT id, title, url FROM bookmarks WHERE user_id = 8812 ORDER BY created_at DESC LIMIT 20. It is the home screen, the twenty links one user saved most recently. The table holds 50 million rows and no index beyond its primary key, so the database answers with a sequential scan: it reads every row, keeps the few thousand that belong to user 8812, sorts them and returns twenty. The query never changed. The table grew.

An index turns a design that reads a table into one that reads an answer, and the rule for choosing one fits in a line: index the access pattern, not the column. Start from the queries the system runs (on a Grid, every arrow into the database is one) and give each hot query a path that touches only the rows it returns. A column nobody filters, joins or sorts on needs no index.

A sorted copy of one column

The first fix is a B-tree index on user_id: a separate structure that keeps the column's values sorted, each pointing at its row, in a shallow tree of pages. Finding user 8812 is a walk from the root to a leaf, three or four page reads on a table this size. Because it is sorted, the same structure serves equality, ranges (created_at > ?) and ORDER BY without a separate sort. It is the default. A hash index answers equality only, with no order, and in a relational store is rarely worth choosing over a B-tree.

With the B-tree the query drops to 40 ms, and the plan shows why it is not 1. User 8812 has 6,000 bookmarks: the database fetches all 6,000 rows, sorts them by created_at, and throws away 5,980.

Column order does the work

A composite index puts several columns in one B-tree, sorted by the first, then by the second within each value of the first. On (user_id, created_at), user 8812's bookmarks sit together, already in date order, so the database starts at the newest, reads twenty and stops.

The order decides which queries it can serve. This index serves WHERE user_id = ? and WHERE user_id = ? AND created_at > ?, but not WHERE created_at > ? alone, because one day's bookmarks are scattered across millions of users' runs. This is the leftmost-prefix rule: an index serves a query that constrains its columns from the left. (MySQL 8.0 and PostgreSQL 18 can sometimes skip-scan past a leading column with few distinct values; with millions of users that does not help.) Columns compared by equality go first, then the one you range over or sort by.

Its next page is a separate problem: a deep OFFSET gets slower even with this index, and Pagination: offset, keyset & cursors covers the fix.

Skipping the table

Twenty index entries still mean twenty trips to the table for id, title and url. A covering index carries every column the query needs, so the engine can skip the table entirely: in PostgreSQL 11 and later, CREATE INDEX ON bookmarks (user_id, created_at) INCLUDE (id, title, url) (MySQL has no INCLUDE; add the columns to the key). The plan says Index Only Scan. PostgreSQL still visits the table for pages changed since the last vacuum, so the trips almost vanish on rows written once and read many times.

An index for the rows you ask about

Each saved link also needs a readable copy of the article, fetched by a worker that takes the oldest first, WHERE fetch_status = 'pending' ORDER BY created_at: a few hundred rows out of 50 million. An index on fetch_status would hold all 50 million entries, nearly all saying done. A partial index holds only the rows matching its own condition, CREATE INDEX ON bookmarks (created_at) WHERE fetch_status = 'pending', so it stays small however long the history grows, and the planner uses it for queries whose WHERE implies that condition. (MySQL has no partial indexes.)

Sign-in shows the opposite trap: an index that exists and goes unused. WHERE lower(email) = ? cannot use a plain index on email, which is sorted by the stored value, Ana@Example.com, not the computed one. The fix is an expression index on lower(email). When the function has a range equivalent, rewriting the predicate to compare the bare column works too: WHERE created_at::date = ? becomes the range created_at >= ? AND created_at < ?.

Every index is a write

An index is a second copy of part of the table, kept in step with it. Every INSERT adds an entry to each index that covers the row, an UPDATE of an indexed column replaces its entries, and deleted rows leave entries to clean up. In PostgreSQL even an UPDATE that changes no indexed column adds entries to every index, unless the new row version fits on the same page (a HOT update). Five indexes on a hot table means up to six writes for every inserted row, and six structures competing for the same memory.

Bookmarks can afford three: a user saves a few links a day and opens the list constantly. On a write-heavy path (events, telemetry, an order log) the right number is the fewest its constraints and hot reads need, often just the primary key, with other reads served from a rollup (High-write ingestion). Some reads get no index at all: the weekly report reads everything anyway, so it scans a replica, away from the writes (Replication & read replicas covers the lag).

One index per hot access pattern

The primary carries the covering index (user_id, created_at) INCLUDE (id, title, url) for the home list and a partial index on pending fetches; the weekly report scans a replica, away from the writes.

Asking the planner what it did

Whether an index is used is the planner's decision, and EXPLAIN ANALYZE shows it: it runs the query and prints the plan with real row counts and timings. It really runs it, so wrap an UPDATE or DELETE in a transaction and roll it back. Read from the innermost node outward and look for four things:

  • Seq Scan on a large table where you expected an index. Sometimes that is right: when a query returns a large share of the table, such as fetch_status = 'done' for 49 million rows, reading it in order beats jumping through an index.
  • Estimated rows against actual rows off by more than 10×: the planner chose from a bad estimate. Stale statistics are the first suspect, and ANALYZE refreshes them; if the gap survives it, look for columns whose values are correlated.
  • Rows Removed by Filter in the hundreds of thousands under an index scan: the index reaches rows the query then discards, often because it lacks a filtered column or has the columns in the wrong order.
  • A Sort or a Hash step that spilled to disk (Sort Method: external merge, or a hash with more than one batch): the query needed more memory or, for a Sort, an index that delivers the rows in order.

When a join across three tables is the slow part, the answer is sometimes a stored copy of the result rather than an index; Normalisation & denormalisation weighs that trade. And read a plan on production-sized data: on a development database with 2,000 rows a sequential scan is the right plan, and the index you just built looks useless.

Index every arrow in Bookmark Manager

Open the Bookmark Manager challenge and focus on one thing: on each read edge into PostgreSQL, write the index that serves it. The per-user list wants the composite covering index; the worker fetching readable copies wants a partial index on pending ones. Keyword search is the exception: LIKE '%kafka%' has no leftmost prefix for a B-tree to use (a PostgreSQL trigram index can serve it), and keyword search usually goes to a search engine such as Elasticsearch, which the brief allows (Full-text search). Price Tracker & Alerts is the partial index again, on the alerts still armed.

Practice this