PostgreSQL Performance Tuning: The Enterprise Database Optimisation Handbook

PostgreSQL powers some of the world's largest and most critical applications. Instagram, Shopify, and countless enterprise applications rely on it. But an untuned PostgreSQL installation will struggle under load that a properly configured one handles effortlessly.

Indexing: The Highest-Impact Optimisation

Most database performance problems trace to missing or incorrect indexes. Before tuning anything else, ensure:

B-tree indexes for equality and range queries on high-cardinality columns used in WHERE, JOIN, and ORDER BY clauses.

Partial indexes for queries that filter on a condition: CREATE INDEX ON orders (user_id) WHERE status = 'active'. Dramatically smaller index, faster queries.

Composite indexes match the column order of your most frequent multi-column filters. The leftmost prefix rule applies.

GIN indexes for full-text search, JSONB operators (@>), and array containment (@>).

Use pg_stat_user_indexes to identify unused indexes (they slow down writes for no benefit) and pg_stat_statements to identify the slowest queries.

Query Optimisation

EXPLAIN ANALYSE is your most powerful tool. Read the execution plan before and after any optimisation.

Avoid SELECT * – fetch only the columns you need. Prevents index-only scans from being blocked by fetching unneeded columns.

Use CTEs judiciously – In PostgreSQL 12+, CTEs are inlined by default (not materialised). For very large result sets used multiple times, force materialisation with WITH foo AS MATERIALIZED (...).

Batch inserts – Use COPY or multi-row INSERT for bulk data loading. Individual INSERT statements within a loop are orders of magnitude slower.

Connection Pooling

PostgreSQL's connection overhead is significant—each connection consumes ~10MB of memory. Use PgBouncer in transaction pooling mode to support thousands of application connections through a small pool of database connections.

Configuration Tuning

Key parameters to adjust from defaults:

• shared_buffers: 25% of RAM
• effective_cache_size: 75% of RAM
• work_mem: (RAM * 0.25) / max_connections
• checkpoint_completion_target: 0.9
• wal_buffers: 64MB

Always validate changes under realistic load before applying to production.