Topic
Definition
API performance practices reduce response latency and resource use by bounding query result size, selecting only needed data, and ensuring the database can locate filtered or sorted rows without scanning every row.
In simpler words
Fast endpoints ask the database for only what they need, in a way the database can find quickly.
This connects directly to the challenge topics ahead. Recognize the patterns here so they are easy to diagnose in the challenges.
Definition
A database index is an ordered structure over one or more columns that lets the query planner locate matching rows without scanning the entire table, at the cost of extra storage and slightly slower writes.
In simpler words
An index is like a sorted lookup table for one column, so Postgres does not have to read every row to find matches.
Without an index on a status column, filtering by status forces a full table scan, which is fine at a hundred rows and painful at half a million.
Index columns that are actually filtered, sorted, or joined on, such as status, foreign keys, or a created-at column used for a feed. Indexing every column wastes write performance for no read benefit.
A composite index over status and created-at can serve open tickets sorted by newest in one lookup, faster than two single-column indexes combined.
export class AddTicketStatusIndex implements MigrationInterface {
async up(qr: QueryRunner) {
await qr.query('CREATE INDEX idx_tickets_status ON tickets(status)');
}
}Indexes ship as reviewed migrations, exactly like any other schema change.
Definition
A lean query returns only the columns and relations a response actually requires, avoiding unnecessary payload size, serialization cost, and N+1 relation-loading patterns.
In simpler words
Ask the database for exactly the fields that will be used, not the whole row plus every relation just in case.
Fetching every column of a wide entity, such as a long description and audit history, for a list view wastes bandwidth when the list only shows a title and a status.
The N+1 pattern happens when a loop fetches a relation per row instead of loading it once for the whole page, such as looking up an assignee inside a map instead of one join or one batched query.
Relations should be loaded deliberately, using a join or a single follow-up query keyed by ids, rather than implicitly inside a loop.
// N+1 — one query per ticket
for (const t of tickets) {
t.assignee = await usersRepo.findOne({ where: { id: t.assigneeId } });
}
// Batched — one extra query total
const ids = tickets.map(t => t.assigneeId);
const users = await usersRepo.find({ where: { id: In(ids) } });The batched version scales with one extra query regardless of list size.
Definition
Offset-based pagination retrieves a page by skipping a fixed number of ordered rows, which can duplicate or omit rows if the underlying data set changes between page requests.
In simpler words
Skip and take pagination can show a row twice or skip one entirely if rows are added or removed while a user is paging through.
Skip and take pagination is simple and fine for stable, low-write lists, but under concurrent inserts or deletes it can shift which rows land on which page.
Always pair pagination with a stable, deterministic order, including a tiebreaker such as id. Without one, the same page can return rows in a different order across requests.
Cursor-based pagination, ordering by a stable key and asking for rows after a given id or timestamp, avoids the shifting-page problem at the cost of losing arbitrary jump-to-page access.
Definition
EXPLAIN (and EXPLAIN ANALYZE) shows how Postgres plans to find rows — sequential scan versus index scan — so you can verify an index is actually used before celebrating a migration.
In simpler words
You do not need to become a DBA; you need to recognize Seq Scan on a hot filtered list as a smell.
After adding an index migration, run EXPLAIN on the list query with realistic filters. Prefer Index Scan / Bitmap Index Scan over Seq Scan for selective filters on large tables.
ANALYZE updates statistics; a brand-new index on an empty table can look fine until production volume arrives — test with representative data sizes when you can.
Over-indexing slows writes. Index the access paths your API actually uses (status, assignee_id, created_at sorts), not every column.
EXPLAIN ANALYZE
SELECT * FROM tickets WHERE status = 'open' ORDER BY created_at DESC LIMIT 20;
-- Smell: Seq Scan on tickets (large table + selective filter)
-- Better: Index Scan using idx_tickets_status_created_atIf the plan ignores your new index, check column order, predicates, and whether the query matches the index definition.
Test
At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥ 80% (enforced by the API) to save progress.
Checking your session…
10 questions · concept 3 · syntax 2 · practical 3 · logic 2