Start with EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Look for Sequential Scans on large tables, high row estimate errors, and nested loop joins with large outer sets. These are your first targets.

Indexing basics

  • B-tree indexes cover equality and range queries — the default and correct choice for most cases.
  • Partial indexes (WHERE status = 'active') are dramatically smaller and faster for filtered queries.
  • Composite indexes: column order matters. Put the equality column first, range column second.
  • GIN indexes for full-text search and JSONB containment queries.

Key config settings

  • shared_buffers — set to 25% of RAM.
  • work_mem — per sort/hash operation. Increase if you see spill to disk in EXPLAIN.
  • effective_cache_size — set to 75% of RAM. This is a planner hint, not an allocation.
  • autovacuum — leave on. Tune autovacuum_vacuum_scale_factor downward for high-churn tables.

Connection pooling

PostgreSQL forks a process per connection. Above ~200 direct connections, performance degrades. Use PgBouncer in transaction mode to pool connections.