PostgreSQL is the world's most popular open-source relational database — but default settings rarely fit production workloads. This guide covers the highest-impact optimizations.
📈 Step 1: Use EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.name;Look for Seq Scan on large tables, high cost values, and missing indexes flagged by the planner.
🗂️ Step 2: Add the Right Indexes
- B-tree — equality and range queries (default)
- GIN — full-text search and JSONB
- BRIN — huge time-series tables
- Partial indexes — index only rows matching a WHERE clause
-- Composite + partial index for active users
CREATE INDEX idx_orders_recent
ON orders (user_id, created_at DESC)
WHERE status = 'active';🔌 Step 3: Connection Pooling with pgBouncer
Postgres connections are expensive (~10MB each). For apps with many short-lived connections (serverless, Next.js), use pgBouncer in transaction mode to reuse connections — often 10x throughput improvement.
⚙️ Step 4: Tune postgresql.conf
| Setting | Recommendation |
|---|---|
| shared_buffers | 25% of RAM |
| effective_cache_size | 75% of RAM |
| work_mem | RAM / max_connections / 4 |
| random_page_cost | 1.1 (SSD) |