Postgres patterns that survived real traffic

Before you tune queries

Measure first. Enable logging for slow queries, identify the top few by total time, and fix those. Most “mysterious” slowness is missing indexes on foreign keys or filters you use in every list view.

Quick checklist

• Turn on log_min_duration_statement in staging before prod.
• Capture EXPLAIN (ANALYZE, BUFFERS) for the slow paths you care about.

-- Example: index for a common list query
CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

Connection pooling

Opening a new connection per request does not scale. Pool at the app layer or use a managed pooler. Watch max connections versus instance size; the database should not become a connection museum.

Caching with discipline

Cache at the edge for public reads, Redis for hot keys with explicit TTLs, and avoid caching aggregates that are expensive to invalidate. Document what happens when the cache is cold.

Migrations in production

Prefer additive changes, backfill in batches, and only then drop old columns. Treat schema changes as product releases: reversible steps and a rollback story.