PostgreSQL Performance Tuning: From Slow to Sub-Second

PostgreSQL can serve queries in under 1ms — or over 60 seconds. The difference comes down to indexing, configuration, query design, and maintenance. Most performance issues fall into five categories: missing indexes, misconfigured memory, unoptimized queries, connection exhaustion, and table bloat from deferred vacuuming.

This guide covers the highest-impact PostgreSQL optimizations in the order you should apply them. Start with EXPLAIN ANALYZE to find the problem, fix indexes, tune configuration, add connection pooling, and set up proper maintenance.

Step 1: Diagnose with EXPLAIN ANALYZE

Never guess at performance — measure it. EXPLAIN (ANALYZE, BUFFERS) shows you exactly what the query planner decided to do, how long each step took, and how much data was read from disk versus cache.

-- Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for real execution stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.name, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY o.order_id, c.name
ORDER BY total DESC
LIMIT 100;

Reading EXPLAIN Output

Indicator	Problem	Fix
`Seq Scan` on large table (>10K rows)	Missing index on filter/join column	Create index on WHERE/JOIN columns
`Nested Loop` with high row counts	Poor join strategy for large datasets	Check statistics, increase `work_mem`, consider hash join
`Sort` with `external merge Disk`	Not enough memory for in-memory sort	Increase `work_mem` for this query
High `Buffers: shared read`	Data not in shared_buffers cache	Increase `shared_buffers` or the table is too large
`Rows Removed by Filter: 999000`	Index exists but not selective enough	Create a more selective composite or partial index
`HashAggregate` with disk batches	work_mem too small for aggregation	Increase `work_mem`
`Planning Time: 50ms`	Complex query with many joins	Simplify query or increase `from_collapse_limit`

Finding Slow Queries with pg_stat_statements

-- Enable the extension (requires shared_preload_libraries restart)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_seconds,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with the most cache misses (reading from disk)
SELECT
    LEFT(query, 100) AS query_preview,
    shared_blks_read AS disk_reads,
    shared_blks_hit AS cache_hits,
    ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;

Step 2: Indexing Strategy

Indexes are the highest-impact optimization. A single well-placed index can turn a 30-second query into a 5ms query.

-- B-tree index for equality and range queries (most common)
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite index (column order matters — put equality columns first, then range)
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

-- Partial index (only index rows you actually query — smaller, faster)
CREATE INDEX idx_orders_active
ON orders (customer_id, order_date)
WHERE status = 'active';

-- Covering index (avoids table lookup — all queried columns in the index)
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date)
INCLUDE (total_amount, status);

-- GIN index for full-text search (inverted index)
CREATE INDEX idx_products_search
ON products USING gin(to_tsvector('english', name || ' ' || description));

-- Expression index (index computed values)
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

-- BRIN index (block range index — for naturally ordered data like timestamps)
CREATE INDEX idx_events_created ON events USING brin(created_at);
-- Much smaller than B-tree for append-only tables with correlated columns

Index Selection Rules

Query Pattern	Index Type	Example
`WHERE status = 'active'`	B-tree (equality)	`CREATE INDEX ON orders (status)`
`WHERE date BETWEEN x AND y`	B-tree (range)	`CREATE INDEX ON orders (date)`
`WHERE status = 'x' AND date > y`	Composite B-tree	`CREATE INDEX ON orders (status, date)`
`WHERE text ILIKE '%search%'`	GIN (trigram)	`CREATE INDEX USING gin (col gin_trgm_ops)`
`WHERE jsonb_col @> '{"key": "val"}'`	GIN (JSONB)	`CREATE INDEX USING gin (jsonb_col)`
`ORDER BY created_at` (append-only)	BRIN	`CREATE INDEX USING brin (created_at)`

Index Health Check

-- Find unused indexes (wasting disk and slowing writes)
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find tables with heavy sequential scans (likely missing indexes)
SELECT
    schemaname || '.' || relname AS table,
    seq_scan,
    seq_tup_read,
    idx_scan,
    CASE WHEN idx_scan > 0
         THEN ROUND(seq_scan::numeric / idx_scan, 2)
         ELSE seq_scan END AS seq_to_idx_ratio,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND pg_relation_size(relid) > 10485760  -- > 10 MB
ORDER BY seq_tup_read DESC;

-- Check index bloat (fragmented indexes waste space and slow scans)
SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 20;

Step 3: Configuration Tuning

Default PostgreSQL configuration is designed for a machine with 512MB RAM and minimal concurrency. Production servers require significant tuning.

# postgresql.conf — for a 16 GB RAM, 8-core SSD server

# Memory
shared_buffers = 4GB              # 25% of RAM (PostgreSQL's page cache)
effective_cache_size = 12GB       # 75% of RAM (total OS + PG cache estimate)
work_mem = 64MB                   # Per-sort/hash operation (careful: per-connection!)
maintenance_work_mem = 1GB        # For VACUUM, CREATE INDEX (ok to be large)

# WAL (Write-Ahead Log)
wal_buffers = 64MB                # Default is too small for write-heavy workloads
checkpoint_completion_target = 0.9 # Spread checkpoints over 90% of interval
max_wal_size = 4GB                # Larger WAL reduces checkpoint frequency
min_wal_size = 1GB

# Query Planner
random_page_cost = 1.1            # SSD storage (default 4.0 assumes spinning disk!)
effective_io_concurrency = 200    # SSD can handle concurrent IO
default_statistics_target = 200   # More accurate query plans (default 100, max 10000)

# Connections
max_connections = 200             # Don't go higher — use PgBouncer to multiplex

# Parallel Queries (PostgreSQL 10+)
max_parallel_workers_per_gather = 4    # Parallel workers per query
max_parallel_workers = 8               # Total parallel workers
max_parallel_maintenance_workers = 2   # For VACUUM, CREATE INDEX
parallel_tuple_cost = 0.01             # Lower to encourage parallelism

Configuration by Server Size

Setting	8 GB RAM	16 GB RAM	64 GB RAM	128 GB RAM
`shared_buffers`	2 GB	4 GB	16 GB	32 GB
`effective_cache_size`	6 GB	12 GB	48 GB	96 GB
`work_mem`	32 MB	64 MB	128 MB	256 MB
`maintenance_work_mem`	512 MB	1 GB	2 GB	4 GB
`max_wal_size`	2 GB	4 GB	8 GB	16 GB

Step 4: Connection Pooling

PostgreSQL forks a new process per connection (~10MB RAM each). At 200+ connections, PostgreSQL spends more time managing connections than running queries. PgBouncer solves this by multiplexing thousands of application connections into a small pool of database connections.

# PgBouncer configuration (pgbouncer.ini)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction         # Best for web apps (release on transaction end)
default_pool_size = 20          # Actual connections to PostgreSQL
max_client_conn = 1000          # Max application connections to PgBouncer
min_pool_size = 5               # Keep 5 connections warm
reserve_pool_size = 5           # Emergency overflow pool

Pool Mode Comparison

Mode	When to Use	Limitation
`session`	Legacy apps using session features (LISTEN/NOTIFY)	No multiplexing benefit
`transaction`	Web apps, APIs (most common)	Cannot use session-level features
`statement`	Read-only workloads, simple SELECT queries	No multi-statement transactions

Step 5: VACUUM and Maintenance

PostgreSQL’s MVCC (Multi-Version Concurrency Control) creates dead rows on every UPDATE and DELETE. VACUUM reclaims this space. Without it, tables bloat, queries slow down, and eventually you hit transaction wraparound.

-- Check which tables need vacuuming most urgently
SELECT
    schemaname || '.' || relname AS table,
    n_dead_tup,
    n_live_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Tune autovacuum for high-write tables (more aggressive than defaults)
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,          -- Start vacuuming after 1K dead tuples
    autovacuum_vacuum_scale_factor = 0.05,        -- Plus 5% of table (default 20% is too lazy)
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.02
);

-- Monitor autovacuum workers
SELECT datname, usename, query, state, wait_event
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

Transaction Wraparound Prevention

-- Check proximity to transaction ID wraparound (CRITICAL alert)
SELECT datname,
    age(datfrozenxid) AS xid_age,
    ROUND(age(datfrozenxid)::numeric / 2147483647 * 100, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- Alert if pct_to_wraparound > 50%
-- Emergency if > 75% — autovacuum cannot keep up

Performance Checklist

:::note[Source] This guide is derived from operational intelligence at Garnet Grid Consulting. For database optimization, visit garnetgrid.com. :::