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.

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.

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

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.

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.