Skip to content

Query Optimization

Performance rules and patterns for PostgreSQL access in dichit-backend. The service targets < 200 ms API responses, so most request latency is DB latency.

Golden rules (from AGENTS.md)

  1. Prefer one efficient query over multiple round trips.
  2. If Prisma needs several queries for one result, consider a single raw PostgreSQL query in the repository layer.
  3. Raw SQL stays in repository/infrastructure code, is parameterized and typed, and never interpolates untrusted input.
  4. Select only required columns.
  5. Use UnitOfWork for multi-statement writes.
  6. Paginate list endpoints with deterministic ordering.

N+1 is the #1 offender

flowchart TB
    Q1["SELECT cycles"] --> Q2["SELECT invoices × N"]
    Q2 --> Q3["SELECT transactions × N²"]

Fix: fetch with include/select in one query, or a single raw SQL join in the repository.

flowchart LR
    Q["SELECT c + invoices + transactions (1 query)"] --> R["Result mapped to DTO"]

Choosing Prisma vs raw SQL

Situation Preferred
Simple CRUD, obvious relations Prisma (findMany, include, select)
Dashboard rollups / aggregations One raw SQL aggregation
Deep joins with authz filters Raw SQL with composable WHERE
Many writes in a transaction unitOfWork
Paginated feed + count Single query with window functions if safe

Raw results map into explicit DTOs/entities — never leak raw rows to routes.

Pagination pattern

// deterministic ordering: stable sort key + unique tiebreaker
orderBy: [{ createdAt: 'desc' }, { id: 'asc' }];

Keep ordering stable under inserts/deletes (composite key) — see api/rest template and indexing.md.

UnitOfWork

Writes spanning multiple DB operations use unitOfWork so they commit/roll back together. Use cases orchestrate; repositories expose narrow operations; the transaction boundary is explicit at the use-case level.

Instrumentation

  • LOG_PRISMA_SLOW_QUERY_MS=200 to surface slow queries in dev (see debugging).
  • Sentry traces capture DB spans in production (see infrastructure/monitoring.md).
  • EXPLAIN ANALYZE any query suspected of being hot.

Best practices checklist

  • No N+1 in the hot path
  • Column pruning on reads
  • Index matches WHERE/ORDER BY
  • Pagination with deterministic ordering
  • Raw SQL parameterized + typed
  • Heavy work offloaded to queues (see event-driven)