PostgreSQL Performance: The Basics That Matter

Key Insights

• Missing indexes are the #1 cause of slow queries — use EXPLAIN ANALYZE to identify sequential scans on large tables
• Connection pooling is essential; PostgreSQL forks a process per connection and exhausts resources quickly without it
• Set shared_buffers to 25% of available RAM and ensure autovacuum is keeping up with dead tuple accumulation

Most PostgreSQL performance problems come down to a handful of common issues. Before reaching for complex solutions, check these fundamentals.

Missing Indexes

The single most common cause of slow queries. Use EXPLAIN ANALYZE to identify sequential scans on large tables:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

If you see Seq Scan on a table with more than a few thousand rows, you likely need an index.

Connection Pooling

PostgreSQL forks a new process per connection. Without pooling, you’ll exhaust system resources quickly under load. Use PgBouncer or your framework’s built-in pooler.

Vacuum and Analyze

PostgreSQL’s MVCC model means dead tuples accumulate. Autovacuum handles this, but check that it’s keeping up:

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Shared Buffers

The default shared_buffers of 128MB is too low for production. Set it to 25% of available RAM as a starting point.

These four items resolve the majority of PostgreSQL performance complaints.