Topic

Challenge: missing index / slow list

Definition

A missing index causes the query planner to perform a sequential scan over an entire table to satisfy a filter or sort, producing latency that grows roughly linearly with table size instead of staying flat.

In simpler words

Without the right index, a filtered list gets slower and slower as the table grows, even though the code never changed.

This reuses the performance judgment from earlier in this week: recognizing the missing-index smell on a slow, filtered, frequently used endpoint.

Open official example (opens in new tab)

After this you can

Recognize a filter or sort column with no supporting index
Explain why it was fast in development with a small table hides this bug
Pick the correct migration-based fix over an application workaround

The classic slow-list smell

Definition

A list endpoint that filters or sorts by a column with no index will scale acceptably in development, where tables are small, and degrade sharply in production, where tables are large, because sequential scan cost grows with row count.

In simpler words

The exact same query gets dramatically slower purely because the table grew. The code did not change; the data did.

A ticket list filtered by status and ordered by a created timestamp, with no index on either column, forces Postgres to read and sort every row on every request.

This bug is easy to miss locally with a few dozen seed rows and only shows up as a production incident once the table holds hundreds of thousands of rows.

Keep in mind

Fast in development with tiny seed data proves nothing about production scale.
Index columns used in filters and sorts on frequently hit endpoints.
Ship index changes as reviewed migrations, like any other schema change.

Challenge lab

Bug report

GET /tickets?status=open gets slower every week as the tickets table grows; it was instant in development.

What is broken

The tickets table has no index on status, so every filtered list performs a full sequential scan that gets more expensive as rows accumulate.

Broken snippet

// TicketsService
async list(status: string) {
  return this.ticketsRepo.find({ where: { status } }); // no index on status
}
// tickets table: only a primary key index on id

Pass criteria

Filtering by status uses an index instead of a full table scan
The fix ships as a reviewed migration, not an application-code workaround
Existing data and behavior are unaffected; only the query plan and performance change

Checking your session…

Choose the correct fix

Add a migration that creates an index on the status column, and consider a composite index with the created timestamp if both are used together.Cache the full ticket list in a module-level array inside the Nest service on first request.Increase the database connection pool size to handle more concurrent slow queries.Fetch all tickets unfiltered and filter by status in the React app instead.

Test

Check your understanding

At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥80% (enforced by the API) to save progress.

Checking your session…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. How should the missing-index challenge be fixed?

Concept

Add the index at runtime the first time the endpoint is hitAdd a reviewed migration indexing the filtered/sorted columns of the slow listEnable synchronize so TypeORM creates the index automaticallyAdd @Index to the entity without generating a migration

2. Why not cache the full table in memory?

Syntax

Node arrays cannot hold more than a few thousand rowsStale data, memory blowups, and multi-instance inconsistencyIn-memory data is lost only when the disk fills upThe cache would bypass the JwtAuthGuard on reads

3. Why not just enlarge the pool?

Practical

The pool size is fixed by TypeORM and cannot changeExtra connections each rebuild the query plan from scratchA bigger pool forces sequential scans to run twiceParallel slow scans still hammer disks/CPU

4. Why not filter in the frontend after download-all?

Logic

The browser cannot evaluate a WHERE clause over JSONClient-side filters cannot be paginated afterwardTransfers and scans remain hugeEach page load re-downloads the whole table uncached

5. What should the migration contain?

Concept

An ALTER TABLE adding a generated column for the filterA VACUUM ANALYZE to refresh the planner statisticsA CLUSTER command to physically reorder the rowsCREATE INDEX (or equivalent) for the hot filter/sort columns

6. This list endpoint gets slower as the table grows. What is the correct fix?

Practicalintermediate

async list(status: string) {
  return this.ticketsRepo.find({ where: { status } });
}
// tickets has only a primary-key index on id

Add a reviewed migration creating an index on status (composite with created_at if it is also sorted)Cache the result in a module-level array after the first requestRaise the database connection pool size for the endpointFetch every row and filter by status in the browser

7. Should you edit an old applied migration to add the index?

Syntax

Yes — re-run migration:run after editing the old fileYes — revert it, edit the up() method, then re-applyYes — append the CREATE INDEX inside the existing up()No — add a new migration

8. What tool helps confirm the fix?

Logic

pg_indexes to confirm the index was createdEXPLAIN/ANALYZE or measured list latencyThe TypeORM query log showing the SQL textThe migration:show command listing applied migrations

9. Do indexes replace pagination?

Concept

No — use both for large listsYes — an index returns only the matching rows anywayYes — a covering index makes LIMIT unnecessaryYes — indexed queries already bound the payload size

10. Which fix papers over sequential scans?

Practical

Increasing the DB pool size so more slow scans can runAdding an index on the filtered columnBatching related lookups to remove an N+1Returning results one page at a time

11. The plan shows a Seq Scan on a large table. What fixes it?

Conceptadvanced

EXPLAIN ANALYZE
SELECT * FROM tickets WHERE status = 'open';

Run ANALYZE to refresh the planner statisticsCreate an index on the status columnIncrease work_mem so the scan fits in memoryAdd LIMIT so the scan stops early

12. Which index best serves this query?

Syntaxadvanced

SELECT * FROM tickets WHERE status = 'open' ORDER BY created_at DESC LIMIT 20;

A composite index on (status, created_at)A composite index on (created_at, status)Separate single-column indexes on status and created_atAn index on the id primary key

13. Where should this statement live?

Practicalintermediate

await q.query('CREATE INDEX idx_tickets_status ON tickets(status)');

In a reviewed migration, not created ad hoc at runtimeIn an @Index decorator with synchronize enabledIn the module’s onModuleInit lifecycle hookIn the seed script after inserting the rows

14. Why is this a poor fix for a slow filtered list?

Logicadvanced

let allTickets = await repo.find(); // cached in a module variable

It goes stale, blows up memory, and is inconsistent across instancesModule variables are garbage-collected between requestsIt bypasses the connection pool, exhausting socketsThe array cannot be filtered without an index

15. How do you confirm the index helped?

Conceptintermediate

-- after adding the index
EXPLAIN ANALYZE SELECT * FROM tickets WHERE status = 'open';

Confirm pg_indexes now lists the new indexCheck that the migration ran without an errorVerify the endpoint returns the same rows as beforeRe-run EXPLAIN ANALYZE and compare the plan and latency

Checking your session…