Database Performance

Most backend latency is database time. Read query plans (EXPLAIN ANALYZE), add indexes that match filters and sort order (composite, covering, partial), eliminate N+1 patterns with joins or batched IN queries, fetch only needed columns, use keyset pagination, keep transactions short, and size connection pools (plus PgBouncer/ProxySQL) to what the database can handle. Move heavy analytics to replicas or warehouses.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
-- Seq Scan on orders (actual time=0.03..812.4 rows=20) Filter rows removed: 2,400,000   ← problem

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC) INCLUDE (total_cents);
-- Index Only Scan ... (actual time=0.05..0.21 rows=20)                                  ← fixed

Common pitfalls

• Indexes on every column (slower writes, unused indexes).
• ORM lazy loading in loops.
• More app instances × pool size than the database allows.

Key points

• EXPLAIN ANALYZE before and after
• Composite/covering indexes match queries
• Fix N+1, bound pools

Further reading: PostgreSQL: Using EXPLAIN