Most PostgreSQL performance problems have the same handful of root causes. Sequential scans on large tables. N+1 query patterns from ORMs. Connection pool exhaustion under load. Autovacuum falling behind on high-write tables. Once you know how to read query plans and where to look, you can diagnose most slowdowns in under an hour.
Key Takeaways
- pg_stat_statements is the most important extension for finding the slowest database queries by cumulative run time.
- EXPLAIN ANALYZE executes the query to show actual vs estimated rows and loops, making it easy to spot missing indexes.
- N+1 query patterns generated by ORMs are the leading cause of high-frequency, low-latency database bloat under load.
- PgBouncer in transaction mode multiplexes thousands of client connections into a tiny set of database connection processes.
- Autovacuum scale factors must be lowered on tables with high write/update volume to prevent dead tuple bloat.
Prerequisites
- PostgreSQL 14+ (most features here apply to pg 12+ as well)
- Superuser or
pg_monitorrole for system views pg_stat_statementsextension available (ships with PostgreSQL, just needs enabling)
How Do You Find the Slowest Queries?
Before diving into individual queries, find the worst offenders across the whole database.
Enable pg_stat_statements in postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'Reload PostgreSQL, then create the extension:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;Now query the slowest queries by total time:
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;total_exec_time shows cumulative time — a query that runs a million times and takes 0.1ms each contributes 100,000ms total. mean_exec_time shows per-call cost. Both matter: high total means it’s consuming database capacity; high mean means individual calls are slow.
Reset stats after tuning to measure improvements from a clean baseline:
SELECT pg_stat_statements_reset();How Do You Read and Interpret EXPLAIN ANALYZE?
EXPLAIN shows what PostgreSQL plans to do. EXPLAIN ANALYZE runs the query and shows what it actually did.
EXPLAIN ANALYZE
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;What to look for:
Seq Scan on a large table — PostgreSQL read every row. Unless the table is tiny or the query returns most rows anyway, this usually means a missing index.
Seq Scan on orders (cost=0.00..18340.00 rows=240 width=120)
(actual time=0.03..412.50 rows=240 loops=1)
Filter: ((status)::text = 'pending'::text)
Rows Removed by Filter: 847620847,620 rows scanned to return 240. Add a partial index: CREATE INDEX ON orders(created_at DESC) WHERE status = 'pending'.
Hash Join vs Loop Join — Hash Join is generally good for larger data sets. Loop Join with an inner Seq Scan is the dangerous combination:
Loop Join (cost=0.00..98234.00 rows=240 width=240)
-> Seq Scan on orders (actual rows=240 loops=1)
-> Seq Scan on users (actual rows=1 loops=240)loops=240 on the inner Seq Scan means PostgreSQL scanned the entire users table 240 times. If you see loops=N on an inner node with N > 10, that node is your bottleneck.
Actual vs estimated rows — the rows=X in the cost estimate vs rows=Y in the actual. Large discrepancies mean stale statistics. Fix with ANALYZE tablename.
Buffers — add BUFFERS to see memory vs disk reads:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...High Buffers: read=N means data from disk. High Buffers: hit=N means from shared buffer cache. If you’re hitting disk heavily for a frequently-accessed table, consider increasing shared_buffers.
What Are the Most Common Slow Query Patterns in Apps?
1. Missing index on a filtered or joined column. The fix is usually CREATE INDEX. Composite indexes when filtering on multiple columns — put equality columns before range columns.
2. N+1 queries from ORMs. This is the most common performance problem in apps using Sequelize, SQLAlchemy, ActiveRecord, or Prisma. Loading 100 orders triggers 101 queries:
-- Query 1: get 100 orders
SELECT * FROM orders LIMIT 100;
-- Queries 2–101: get user for each order (generated by ORM)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
-- ...×100The fix is eager loading: JOIN in SQL, include in Prisma, joinedload in SQLAlchemy. One query instead of 101. Detect N+1 in pg_stat_statements: look for queries with extremely high calls counts and low mean_exec_time.
3. SELECT * in performance-sensitive paths. Name only the columns you use. Partial data is faster to transfer and easier for PostgreSQL to cache.
4. Missing LIMIT on potentially large result sets. A query that returns 10,000 rows when the UI shows 20 wastes everything. Use LIMIT + OFFSET or, better, keyset pagination for large datasets.
Why and How Should You Set Up PgBouncer Connection Pooling?
PostgreSQL creates an OS process for each connection. Under high concurrency, the overhead of process creation and context switching becomes significant. PgBouncer maintains a smaller pool of actual database connections and queues application requests.
sudo apt install pgbouncer -yMinimal /etc/pgbouncer/pgbouncer.ini:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
server_idle_timeout = 600pool_mode = transaction — the connection is returned to the pool after each transaction, not each session. This is the most efficient mode. Caveat: you can’t use session-level features (advisory locks, SET commands that persist across transactions, some prepared statement patterns) with transaction mode.
Your application now connects to PgBouncer on port 6432 instead of PostgreSQL directly on 5432. PgBouncer multiplexes up to 1,000 client connections through 20 actual database connections.
Which Autovacuum Settings Actually Matter for High-Write Tables?
PostgreSQL’s MVCC model keeps dead row versions after updates and deletes until autovacuum reclaims them. Default settings work for typical workloads but fall behind on high-write tables.
Check autovacuum activity:
SELECT schemaname, tablename,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;Dead percentage above 10–20% on a frequently-queried table means autovacuum is behind. Symptoms: bloated tables, stale statistics, growing pg_relation_size.
Tune autovacuum for a specific high-write table:
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 2
);For large tables (millions of rows), the default autovacuum_vacuum_scale_factor = 0.2 means autovacuum doesn’t trigger until 20% of rows are dead. On a 1M-row table, that’s 200,000 dead rows before vacuum kicks in. Setting scale_factor = 0.01 makes it trigger at 10,000 dead rows instead.
FAQ
How do I know if my server is CPU-bound or I/O-bound? Check EXPLAIN (ANALYZE, BUFFERS). High disk reads means I/O-bound — increase shared_buffers (typically 25% of RAM) or add faster storage. High execution time with cache hits means CPU-bound — look at parallelism or query optimization. On the OS level, iostat -x 1 and top show whether the bottleneck is disk or CPU.
When should I use EXPLAIN vs EXPLAIN ANALYZE? Use EXPLAIN for non-destructive planning. Use EXPLAIN ANALYZE for all performance investigations — you need actual row counts and timing, not estimates.
Is it safe to run ANALYZE on a production table? Yes. ANALYZE takes only a brief share lock, doesn’t block reads or writes, and completes quickly (it samples, not a full scan). VACUUM is also safe in its default form. VACUUM FULL takes an exclusive lock — avoid it on production during business hours.
For more foundation, the Database Indexing Explained covers B-trees and composite indexes in detail.
Related Articles
Deepen your understanding with these curated continuations.
Database Indexing: B-trees, Composite Indexes & Performance
Learn how database indexes work, from B-trees to partial indexes. Understand composite index order and how to use EXPLAIN ANALYZE for better performance.
PostgreSQL JSONB: Query Patterns & Performance Optimization
Master PostgreSQL JSONB — operators, indexing, querying deep data, aggregation, and performance tuning. Real-world patterns for modern applications.
How to Debug a Slow SQL Query in PostgreSQL
Step-by-step: find slow queries with pg_stat_statements, read EXPLAIN ANALYZE output, identify missing indexes, fix N+1 queries, and diagnose lock contention.
