PostgreSQL Performance Tuning: 10 Techniques That Actually Work

Why Most Slow Queries Are Avoidable

In my experience, 80% of slow PostgreSQL queries come from three things: missing indexes on join/filter columns, N+1 query patterns from ORMs, and SELECT * on wide tables. The remaining 20% require deeper investigation — but fixing these three first almost always reaches acceptable performance without schema changes.

“

“Premature optimization is the root of all evil. But measuring first and optimizing second is just engineering.”

— Martin Fowler

The 10 Techniques

Ordered by impact-to-effort ratio. Technique 1 frequently solves 70% of the problem by itself.

1. 1Add composite indexes for your most-queried column combinations
2. 2Run EXPLAIN (ANALYZE, BUFFERS) before writing any optimization — measure first
3. 3Replace SELECT * with explicit column lists to reduce I/O and memory
4. 4Eliminate N+1 queries with JOINs or batch fetching (IN clause with IDs)
5. 5Use partial indexes to index only rows you actually query
6. 6Enable connection pooling with PgBouncer to reduce per-connection overhead
7. 7Set work_mem appropriately for complex sorts and hash joins
8. 8Run VACUUM ANALYZE after bulk inserts to update planner statistics
9. 9Partition large tables by date range for time-series data
10. 10Cache frequently-read, rarely-written data in Redis to bypass the database entirely

Using EXPLAIN ANALYZE

Before and after every optimization, run EXPLAIN (ANALYZE, BUFFERS). The key signals: 'Seq Scan' means a full table scan (usually bad), 'Index Scan' means the planner is using your index.

Before: Sequential Scan (8.2 seconds)

1-- Slow query: no index on user_id + status combination
2EXPLAIN (ANALYZE, BUFFERS)
3SELECT id, amount, created_at
4FROM orders
5WHERE user_id = 12345 AND status = 'completed'
6ORDER BY created_at DESC
7LIMIT 20;
8
9-- Result:
10-- Seq Scan on orders  (actual time=8198.23..8199.01 rows=20)
11-- Filter: ((user_id = 12345) AND (status = 'completed'))
12-- Rows Removed by Filter: 1,847,293

After: Composite Partial Index (0.8ms)

1-- Composite partial index: covers the filter, sort, and WHERE clause
2CREATE INDEX CONCURRENTLY idx_orders_user_completed
3ON orders (user_id, created_at DESC)
4WHERE status = 'completed';
5
6-- Index Scan using idx_orders_user_completed
7-- (actual time=0.72..0.81 rows=20)

Choosing the Right Index Type

For Go applications, the pgoptimize library (in the related tools section) wraps EXPLAIN ANALYZE output parsing and can automatically suggest missing indexes based on your query patterns.