Performance (indexes, lean queries, pagination)

Indexes: why filters get slow without one

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)');
  }
}

Lean queries: select only what is needed

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) } });

Pagination correctness

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.

EXPLAIN mindset (without memorizing planner trivia)

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_at

Keep in mind

• Index what is filtered, sorted, or joined on, not everything.
• Fetch only the columns and relations the response needs, and batch relation loads instead of looping.
• Always give pagination a deterministic order with a tiebreaker.
• Verify indexes with EXPLAIN on realistic data — do not trust the migration alone.