PostgreSQL Performance Tuning: From Slow Queries to Sub-Millisecond

PostgreSQL is fast by default, but “default” doesn’t cut it at production scale. Here’s our systematic approach to PostgreSQL performance tuning, from identifying slow queries to achieving sub-millisecond response times.

Step 1: Enable Query Logging

Before optimizing anything, you need to know what’s slow. Enable log_min_duration_statement = 100 to log queries taking longer than 100ms. Run this for 24-48 hours during normal traffic to build a baseline.

Use pg_stat_statements for aggregate analysis. It tracks execution count, total time, mean time, and rows returned for every query. Sort by total execution time to find your worst offenders.

Step 2: Understand EXPLAIN ANALYZE

Every query optimization starts with EXPLAIN ANALYZE. Look for: Sequential Scans on large tables (should be Index Scans), Nested Loops with high row estimates, Sort operations on large datasets, and Hash Joins where one side is much smaller than expected.

The most common issue: missing indexes or indexes that PostgreSQL chooses not to use. Check the query plan, identify the bottleneck operation, and address it specifically.

Step 3: Index Strategy

Composite indexes for common queries: If you frequently filter by status and created_at, create a composite index on both columns. Order matters — put the equality column first, range column second.

Partial indexes for filtered queries: If you frequently query active records, create CREATE INDEX ON orders (created_at) WHERE status = 'active'. The index is smaller and faster because it excludes inactive records.

Covering indexes: Include columns in the index that the query needs, so PostgreSQL can satisfy the query from the index alone without accessing the table.

GIN indexes for JSON and full-text: If you query JSONB columns or use PostgreSQL’s full-text search, GIN indexes are essential. They’re slower to update but dramatically faster for reads.

Step 4: Connection Pooling

PostgreSQL forks a process per connection. At 100+ connections, context switching degrades performance. Use PgBouncer in transaction pooling mode to multiplex thousands of application connections onto 20-50 database connections.

Step 5: Configuration Tuning

Key settings to adjust: shared_buffers (25% of RAM), effective_cache_size (75% of RAM), work_mem (based on max connections and available memory), maintenance_work_mem (for index creation and VACUUM), and max_connections (keep low, use PgBouncer).

Step 6: Vacuum and Analyze

Autovacuum is essential but sometimes needs tuning. For tables with frequent updates, reduce autovacuum thresholds to trigger more frequent cleanup. Stale statistics cause bad query plans — ensure autovacuum runs regularly on your most-queried tables.

Measuring Success

Track these metrics over time: query p50/p95/p99 latency, queries per second, cache hit ratio (target >99%), index usage ratio, and dead tuple count. Use pgHero or Grafana dashboards for visualization.