Postgres in depth · Chapter 48 of 65
Designing indexes
By the end of this chapter you can design the index a query needs: the column order of a composite index, a partial index, an index on an expression, a covering index for index-only scans, and build it on a live table without blocking writes. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Inside Postgres
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
LCO's event log grows by thousands of rows an hour, and every page of the site asks it something different: a student's latest activity, a lesson's completions, Sana's error dashboard, Arjun's watch-time report. Kabir's answer is "index every column". Priya says no: every index slows every write, and an index on the wrong columns is never used. Chapter 47 showed how one B-tree works; this chapter is about choosing which B-tree.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
composite.sql: the right column order
Each lesson page shows its 10 latest completions: the id, student_id and happened_at of the lesson_events rows with lesson_id 42 and event_type 'complete', newest first, 10 rows (the query is in the starter file).
Create one index on lesson_events so that Postgres finds those rows directly and reads them already in order. Afterwards the plan of that query must use your index, with both columns compared with = in its Index Cond, and must have no Sort step.
Careful: in a composite index the column order decides what it can do; the Check builds lesson_events with only its primary key.
- Task 2
partial.sql: an index for one rare kind of row
Sana's dashboard shows the 50 latest error events: the id, student_id and happened_at of the lesson_events rows whose event_type is 'error', newest first (the query is in the starter file). Only 1 event in 100 is an error.
Create an index that holds only the error events, in the order the dashboard reads them. Afterwards the plan must use your index, the index must have a WHERE clause about the error events, and the plan must have no Sort step.
Careful: a partial index is used only when the query's own condition implies the index's condition.
- Task 3
expression.sql: a case-insensitive login
The Check builds learner_logins, 100,000 rows whose e-mails are stored as people typed them, in mixed case. The login looks a user up by comparing the lower-case form of email with a lower-case address (the query is in the starter file).
Create an index this query can use. Afterwards its plan must have an Index Cond on lower(email).
Careful: an index on email itself does not help, because the query compares a different value: the lower-case one.
- Task 4 · interview question
Interview: covering.sql, an index-only scan, built live
Arjun's report adds up watched_seconds over the lesson_events rows of lesson 42 (the query is in the starter file). Create an index that answers it without reading the table at all, and build it without blocking the writes lesson_events takes all day.
Afterwards, once the table is vacuumed, the plan of that query must be an Index Only Scan, and no invalid index may be left behind.
Careful: that kind of build cannot run inside a transaction, so the file must hold that one statement and nothing else.
What you will learn
- Composite indexes: why column order matters, equality before range.
- Partial indexes: indexing only the rows a query wants.
- Expression indexes: indexing lower(email), not email.
- Covering indexes and index-only scans: INCLUDE, and the visibility map.
- The cost of an index, and CREATE INDEX CONCURRENTLY.
More in Inside Postgres
Build "Designing indexes" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.