EXPLAIN ANALYZE: Your Starting Point

Never optimize blindly. Run EXPLAIN ANALYZE before every query: it shows the actual execution plan, row estimates, and time spent at each step. Look for sequential scans on large tables (should be index scans), nested loops with high row counts, and sort operations on unindexed columns. The query planner is smart — but it needs the right indexes and statistics.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at > '2026-01-01'
GROUP BY u.name
ORDER BY COUNT(o.id) DESC;

Indexing Strategies

Create B-tree indexes on columns used in WHERE, JOIN, and ORDER BY. Composite indexes serve multiple queries: CREATE INDEX idx_orders_user_date ON orders(user_id, created_at). Use partial indexes for filtered queries: CREATE INDEX idx_active_users ON users(email) WHERE active = true. GIN indexes speed up full-text search and JSONB queries. Don't over-index — each index adds write overhead.

Solving N+1 Query Problems

ORMs like Prisma, Drizzle, and ActiveRecord default to lazy loading — fetching 100 users then making 100 separate queries for their orders. Fix this with eager loading (include/join), batch loading (DataLoader pattern), or raw SQL with JOINs. Monitor query counts in development — if a page generates 50+ queries, you have an N+1 problem.

Connection Pooling

Each PostgreSQL connection uses 5-10MB of RAM. If 100 serverless functions each open a connection, your database runs out of memory. PgBouncer or Supavisor sit between your app and Postgres, reusing a pool of connections. Configure pool_mode = transaction for serverless workloads. This is critical for serverless architectures.

Configuration Tuning

PostgreSQL's defaults are conservative. For a server with 16GB RAM: set shared_buffers to 4GB (25% of RAM), effective_cache_size to 12GB (75%), work_mem to 64MB (for complex sorts), and maintenance_work_mem to 512MB (for VACUUM and index builds). Use PGTune for automated recommendations.

VACUUM and Maintenance

PostgreSQL uses MVCC — old row versions accumulate and need cleanup. Autovacuum handles this automatically, but heavy-write tables may need manual tuning: lower autovacuum_vacuum_scale_factor and increase autovacuum_max_workers. Run ANALYZE after bulk data changes to update query planner statistics.

Monitoring and Profiling

Enable pg_stat_statements to track the slowest and most frequent queries. Monitor pg_stat_user_tables for sequential scan counts (high = missing index). Use pg_stat_activity to find long-running queries and idle connections. Tools like pganalyze and Datadog provide dashboards and alerts for production databases.

Conclusion

PostgreSQL performance tuning follows a clear hierarchy: fix slow queries first (EXPLAIN ANALYZE), add strategic indexes, solve N+1 problems, implement connection pooling, then tune configuration. Combined with Redis caching for hot data, these optimizations can handle 10-100x more traffic on the same hardware.

PostgreSQL Performance Tuning: Queries, Indexes, and Beyond

Key takeaways