SQL optimization#

Two goals:

  • reduce roundtrips between the caller (e.g. the BE) and the DB
  • optimize the queries

Measure first#

Don’t guess, find what is actually slow: slow query log, pg_stat_statements, EXPLAIN ANALYZE (runs the query and shows real timings, unlike plain EXPLAIN).

Reading#

  • Ensure indexes on the columns used in WHERE, JOIN and ORDER BY.
    • Indexes are not free: they slow down writes and take space. Index what your queries use, not everything.
    • Composite indexes: column order matters, put equality filters before range filters.
    • Covering indexes: include the selected columns so the DB doesn’t need to read the table.
  • Check how you wrote the query: select only the columns you need, avoid functions on indexed columns.
  • Avoid N+1 (classic with ORMs): fetch related rows for the whole batch at once with eager loading or WHERE id IN (...).
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at) INCLUDE (total);

EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE customer_id = 42 AND created_at > '2026-01-01';

Reading many rows#

  • Use pagination: never load the whole table at once.
  • Load in batches (e.g. 500 rows per call), process them, then write the results in batch (see below).

Prefer keyset pagination over OFFSET, it stays fast on big tables:

SELECT id, customer_id, total
FROM orders
WHERE id > :last_id
ORDER BY id
LIMIT 500;

Writing#

Process the data and keep the results in the caller’s memory, then write them with a single insert many instead of one insert per row.

INSERT INTO invoices (order_id, amount)
VALUES (1, 10.0), (2, 25.5), (3, 7.2) /* ... up to the batch size */;
  • One transaction per batch: one commit instead of one per row, and the batch is atomic.
  • Upsert / bulk update when rows may already exist:
INSERT INTO invoices (order_id, amount)
VALUES (1, 10.0), (2, 25.5)
ON CONFLICT (order_id) DO UPDATE SET amount = EXCLUDED.amount;

UPDATE invoices i
SET amount = v.amount
FROM (VALUES (1, 10.0), (2, 25.5)) AS v(order_id, amount)
WHERE i.order_id = v.order_id;
  • Big imports: use the native bulk loader (COPY in Postgres, LOAD DATA in MySQL), much faster than INSERT.

Batch size#

500 is a good default, but it’s a tradeoff: very large batches hit parameter limits (Postgres ~65k bind parameters per statement), hold locks longer and use more memory. Measure.

Other#

  • Connection pooling: opening connections is expensive, reuse them.
  • Push work to the DB when it fits: if the processing can be expressed in SQL, a set-based UPDATE ... WHERE or INSERT ... SELECT beats read → process → write, with zero data roundtrips.
INSERT INTO invoices (order_id, amount)
SELECT id, total * 1.22 FROM orders WHERE status = 'closed';

Blueprint#

loop:
  rows = read next batch (500, keyset pagination, indexed, no N+1)
  if rows empty: break
  results = process(rows)        # in memory
  begin
    insert/upsert many results   # one roundtrip
  commit