Database Performance
Learning Objectives
- You can inspect a query plan and distinguish estimates from observed execution.
- You can propose an index for a measured access pattern and explain its costs.
- You can identify N+1 access as application-generated repeated database work.
Imagine a course catalog showing the 25 newest courses in one department. The response is small, but how much work did the database do to find and order those courses? Start with the actual query, its data distribution, and its execution plan.
A database index is an additional structure that organizes selected column values so the database can find or order matching rows with less scanning. It costs storage and work when data changes. An index helps some access patterns, but may not be selected for a small table or a query that reads most rows.
The worked SQL examples use this course catalog. We will compare two plans and reduce its repeated database calls. The index-migration exercise uses a separate public listing schema and supplies its tables; apply the same reasoning to that read’s filter and ordering.
Query plans with EXPLAIN
For a course catalog with courses(id, department, title, created_at), examine:
EXPLAINSELECT id, title, created_atFROM coursesWHERE department = 'CS'ORDER BY created_at DESC, id DESCLIMIT 25;The query planner chooses the steps for finding, joining and ordering rows. Its execution plan describes those steps. EXPLAIN reports that plan and its estimates. EXPLAIN (ANALYZE, BUFFERS) also executes the statement and reports observations. The PostgreSQL EXPLAIN documentation explains these distinctions.
Do not casually run EXPLAIN ANALYZE on a destructive statement in a valuable database: it executes the work. Use an isolated synthetic environment if you run an execution-plan experiment.
Scans, sorts and index access
Look for how rows are found, filtered, ordered, and limited. Compare estimated and actual row counts when analyzing execution. For a tiny table, a sequential scan can be cheaper than using an index.
This example plan shows a 100,000-row fixture with 10,000 matching courses. Costs and timing are omitted to focus on the work:
Limit actual rows=25, loops=1 Sort actual rows=25, loops=1 Sort Key: created_at DESC, id DESC Seq Scan on courses actual rows=10000, loops=1 Filter: department = 'CS' Rows Removed by Filter: 90000 Buffers: shared hit=800Read upward from the scan: it examines the table, retains 10,000 matching rows,
and passes them to a sort that finds the first 25. LIMIT 25 bounds the returned
rows but does not by itself mean only 25 rows were examined. loops=1 means each
node ran once here; repeated execution matters when interpreting a join’s work.
Plain EXPLAIN would supply estimated counts rather than these observations.
Compare estimates with actual counts when both are available. If they differ
greatly, investigate the data distribution and the planner’s statistics: an
estimate predicts work, while an actual count describes this execution.
The buffer counters describe accesses to blocks of table or index data. shared hit counts accesses satisfied from PostgreSQL’s cache; read counts blocks read into its buffers. These are block-access counts, which can revisit the same block, rather than numbers of returned rows. A parent node includes its children’s counters, so do not sum every level of the tree.
A candidate index for this particular filter and ordering is:
CREATE INDEX courses_department_created_idON courses (department, created_at DESC, id DESC);The leading department groups the equality match; the remaining keys provide the requested order within that group. If the planner chooses it, an abbreviated plan might instead look like:
Limit actual rows=25, loops=1 Index Scan on courses actual rows=25, loops=1 Index Cond: department = 'CS' Buffers: shared hit=12The index supplies the requested order, so the separate sort has disappeared. This illustrative run also visits fewer buffers to obtain the same rows. These observations show which work the plan avoids; repeated timing measurements are still needed to assess latency. The index also occupies storage and must be maintained on writes.
This proposed index matches the demonstrated filter and ordering. Compare plans on representative data: three rows may produce a different choice from a large fixture. PostgreSQL also uses statistics that summarize table contents; refresh them with ANALYZE after preparing a substantially changed experimental dataset.
Read the plans in the next question to separate evidence of reduced work from a claim about request speed.
Query-plan evidence
0 / 5 points
These abbreviated PostgreSQL plans illustrate observations from the same 100,000-row synthetic course fixture. Both return the same 25 rows ordered by created_at DESC, id DESC. Timing is omitted because this question concerns observed work.
Before:Limit: actual rows=25 Sort: actual rows=25 Seq Scan: actual rows=10000 Filter: department = CS Rows Removed by Filter: 90000 Buffers: shared hit=917
After the index on (department, created_at DESC, id DESC):Limit: actual rows=25 Index Scan: actual rows=25 Index Cond: department = CS Buffers: shared hit=3 read=3What does this evidence support?
Partial indexes
A partial index includes only rows matching its predicate, the condition in its WHERE clause. If a frequently used query always selects CS courses, an alternative is:
CREATE INDEX courses_cs_created_idON courses (created_at DESC, id DESC)WHERE department = 'CS';The index holds the requested order for that subset. It cannot serve the same purpose for another department. To use it for this read, PostgreSQL must be able to establish that the query needs only rows satisfying the index predicate. The query must still return the same result; adding a partial index does not justify filtering out additional rows. PostgreSQL still chooses whether to use it.
For a public listing read, many earlier synthetic records are private drafts. A public-row index can avoid examining them to find the first public records. This is the access pattern used in the next exercise: check both the subset the index contains and the ordering the query needs.
Index migrations
To adopt an index in a persistent application, add a new migration for that application’s actual table and access pattern. Use an isolated environment to rehearse applying and reversing the migration, checking that the query result and ordering stay the same.
For the earlier course example, reversing the added index uses:
DROP INDEX courses_cs_created_id;This removes that index while retaining the table and its rows. In a dbmate
migration, pair the index’s CREATE INDEX in -- migrate:up with its DROP INDEX
in -- migrate:down, using the same index name.
The index exercise describes the existing schema and public read. Add one migration that can be applied and reversed without changing data or earlier indexes. Use its PostgreSQL checks to verify the required rows and ordering.
Public listing index
0 / 20 points
Edit only database-migrations/006_public_listing_index.sql.
The existing read joins each listing to its seller and category, projects its public fields and ends with:
WHERE l.status <> 'draft'ORDER BY l.idLIMIT 100l is the query’s alias for listings. Its id is an integer primary key, and its status is draft, active or closed. The existing schema and public read stay unchanged; edit only the new migration shown in the editor.
In the -- migrate:up section, add an index with id as its first index key and exactly the public rows as its indexed subset. Choose the index name. In the -- migrate:down section, remove only that index.
Applying, reversing and reapplying this migration must preserve earlier indexes, all listing data and the existing public result. Both active and closed listings are public; an ID range is not a visibility rule.
Choose Submit solution. Index semantics are worth 15 points; reversible application without changed state is worth 5. Submit the migration file for the supplied database checks.
The migration changes how the database can find public listings while preserving the returned rows. Reversing it removes only the added index, leaving the data and earlier indexes in place.
N+1 queries and database round trips
An index can reduce the work inside a query while the application still makes too many queries. Consider loading courses, then fetching an instructor for each course in a loop:
one query for courses+ one query per course for its instructorEven quick instructor queries can add up to a slow page when each needs another round trip. A join may return the same needed data with fewer requests. Count the calls made by the application as well as examining the work inside each one.
Figure 1 contrasts that loop with a joined read. The loop’s number of requests grows with the number of courses; the joined read uses one request for the collection. A course query followed by one batched instructor query is another bounded alternative. Either approach must preserve the promised rows and public fields.
Confirm repeated queries before changing the implementation. A joined public read model may already avoid this pattern; retain its explicit public fields from Part 3.
Count the catalog’s database calls in the next trace. Decide whether the cost comes from work inside a query or from the application’s repeated requests.
Catalog request trace
0 / 5 points
A hypothetical catalog endpoint loads 25 courses with one query, then sends one instructor query for each course. The trace shows that the individual queries are quick, but the request spends most of its time waiting for the successive database calls.
Which next step addresses the repeated-work pattern?
Optional relationships and parameterized reads
A join must preserve the operation’s meaning. The catalog includes courses even
when no instructor is assigned. An inner join would discard those courses; a
LEFT JOIN retains them and returns NULL for the missing instructor’s fields.
For one course, we can read its ID and optional instructor name as follows.
Use the same sql tagged template as in Part 3. The next exercise supplies this
function, so no connection setup is needed:
interface CourseInstructorView { id: number; instructorName: string | null;}
const rows = await sql<CourseInstructorView[]>` SELECT c.id, i.name AS "instructorName" FROM courses AS c LEFT JOIN instructors AS i ON i.id = c.instructor_id WHERE c.id = ${courseId}`;Select only the public fields. The quoted alias preserves the camel-case
property instructorName; a missing instructor becomes instructorName: null.
Courses without an assigned instructor remain in the result.
The tagged template sends ${courseId} as a parameter separately from the SQL
text. Use this syntax for values, including strings containing quotes. The row
type documents the selected result, while the database still determines the
actual rows.
Now apply these ideas to the department’s ordered collection. A joined query or a collection query plus one batched relationship query can replace repeated course/instructor reads without losing optional relationships.
Course catalog read
0 / 50 points
Complete loadCourses(sql, department) in catalog-read.ts.
The supplied sql function uses the same tagged-template syntax as Part 3: sql<CourseView[]> followed by a template literal. Embedded ${value} expressions become SQL parameters. Use this function for every database call; no connection setup is needed.
The tables are:
courses( id integer, department text, title text, created_at timestamptz, instructor_id integer nullable)instructors( id integer, name text, private_email text)Return up to 25 courses in the requested department, ordered by created_at DESC, then id DESC. Each result contains exactly these fields:
type CourseView = { id: number; title: string; instructorName: string | null;};A course without an instructor must remain in the result with instructorName: null.
Pass the requested department as a SQL parameter, including when it contains quotes. Make at most two database calls regardless of the number of returned courses.
For a small example, consider these course rows:
id |
department |
title |
created_at |
instructor_id |
|---|---|---|---|---|
| 41 | CS | Computer Networks | 2026-10-02T09:00:00Z | 7 |
| 42 | CS | Programming Seminar | 2026-10-03T09:00:00Z | null |
| 43 | MATH | Linear Algebra | 2026-10-04T09:00:00Z | 7 |
Instructor 7 has name Risto and a private email address. For department CS, return:
[ { "id": 42, "title": "Programming Seminar", "instructorName": null }, { "id": 41, "title": "Computer Networks", "instructorName": "Risto" }]The unassigned course stays first because it is newer. The MATH course and private email are excluded. A department with no courses returns [].
One joined query or two queries that fetch the courses and their instructors in batches can obtain this result. A course query followed by a separate query for each of these two courses would make three calls.
The chapter’s single-course example shows the same parameter interface; apply it here to the department’s ordered collection.
Choose Submit solution. Bounded calls are worth 20 points and require the correct course IDs when matching courses exist; public results, ordering and parameters are worth 30. No particular SQL spelling or join strategy is required.
The catalog read now uses a bounded number of database calls while retaining courses without an instructor and selecting only public fields.
Checking the joined result
Changing a query can alter ordering, omit resources without an optional relationship, or duplicate rows through a join. Reuse the existing result/order checks; add a case only when the selected change affects behavior they do not cover. A query that returns fewer rows because it lost records may appear faster while being wrong.
A join is only one step of a read. The WHERE clause filters the joined result and retains rows only when its condition is true. Selected fields do not change which rows qualify. For optional relationships, account for missing (NULL) values when testing a column from the related table.
Read an optional-relationship query
0 / 5 points
A course may have no instructor. This query is intended to keep those courses. Consider a course in the requested department whose instructor_id is NULL:
const courses = await sql` SELECT c.id, c.title, i.name AS "instructorName" FROM courses AS c LEFT JOIN instructors AS i ON i.id = c.instructor_id WHERE c.department = ${department} AND i.id IS NOT NULL ORDER BY c.created_at DESC, c.id DESC LIMIT 25`;What happens to that course?
Check Your Understanding
- How does
EXPLAINdiffer fromEXPLAIN ANALYZE? - Why can a sequential scan be a sensible choice?
- For a query filtering
departmentand ordering bycreated_at DESC, id DESC, why might an index on(department, created_at DESC, id DESC)help? - Why is N+1 partly an application-structure problem?
- Which correctness cases can a join-based optimization accidentally change?