Common Performance Problems

Most PostgreSQL performance problems come from a short list of mistakes. Fix these and you solve 80% of slow-database issues before you ever touch configuration tuning.

Missing Indexes

The single biggest cause of slow queries. Without an index, PostgreSQL does a sequential scan — reading every row in the table.

Detecting Missing Indexes

-- Tables with high sequential scan counts relative to index scans
SELECT schemaname, relname,
       seq_scan, idx_scan,
       seq_tup_read, idx_tup_fetch,
       n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

A table with millions of rows and high seq_scan but low idx_scan almost certainly needs an index on the columns you filter by.

Detecting Slow Queries

-- Enable pg_stat_statements (add to shared_preload_libraries, restart required)
-- Then find the slowest queries by total time
SELECT query,
       calls,
       total_exec_time / 1000 AS total_seconds,
       mean_exec_time AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Adding the Right Index

-- Simple B-tree index for equality and range queries
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

-- Composite index for multi-column filters
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at);

-- Partial index for common filters on a subset
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at) WHERE status = 'pending';

Always use CONCURRENTLY in production to avoid locking the table.

Over-Indexing

Every index slows down writes. An INSERT must update every index on the table. An UPDATE that changes an indexed column must update those indexes. Indexes also consume disk space and memory.

Signs of Over-Indexing

-- Find unused indexes
SELECT schemaname, relname, indexrelname,
       idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

If an index has zero scans since the last statistics reset, it is dead weight.

Fix

-- Reset statistics to get a fresh count (do this, then wait a few weeks)
SELECT pg_stat_reset();

-- After confirming an index is unused, drop it
DROP INDEX CONCURRENTLY idx_that_nobody_uses;

A good rule: every index should justify its existence. Audit indexes quarterly.

Lock Contention

Long-running transactions hold locks. Other transactions wait. Throughput collapses.

The Problem

-- Transaction A starts and forgets to commit
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... developer goes to lunch

-- Transaction B is blocked, waiting for the lock
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Hangs until Transaction A commits or rolls back

Detecting Lock Contention

-- Find blocked queries and what's blocking them
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks bk ON bk.relation = bl.relation
  AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid
WHERE NOT bl.granted;

Fix

Keep transactions short. Do your processing outside the transaction.
Set statement_timeout and idle_in_transaction_session_timeout.

-- Kill transactions idle in a transaction for more than 5 minutes
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Connection Exhaustion

PostgreSQL forks a new process per connection. Each process uses memory. The default max_connections is 100. Applications that open connections without pooling will hit this limit.

The Symptoms

FATAL: too many connections for role "myapp"
FATAL: remaining connection slots are reserved for non-replication superuser connections

Fix: Connection Pooling

Use PgBouncer or a built-in connection pool in your application framework.

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
max_client_conn = 1000
default_pool_size = 25
pool_mode = transaction

With transaction-mode pooling, a connection is assigned only for the duration of a transaction, then returned to the pool. 25 server connections can serve hundreds of application connections.

Monitoring Connections

-- Current connection count by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Current connection count by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count(*) DESC;

PostgreSQL uses MVCC (Multi-Version Concurrency Control). When you UPDATE a row, the old version stays in the table until VACUUM removes it. Without regular vacuuming, tables bloat — they grow far larger than their live data.

Detecting Bloat

-- Check dead tuple count
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       CASE WHEN n_live_tup > 0
            THEN round(n_dead_tup::numeric / n_live_tup * 100, 1)
            ELSE 0
       END AS dead_pct,
       last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Fix

-- Manual vacuum
VACUUM VERBOSE orders;

-- Reclaim space to disk (locks the table — use off-peak)
VACUUM FULL orders;

-- Check autovacuum is keeping up
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

For high-write tables, tune autovacuum to run more aggressively:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

The 80/20 of PostgreSQL Performance

Most performance work comes down to this checklist:

Find the slow queries (pg_stat_statements).
Add missing indexes (check EXPLAIN ANALYZE for Seq Scans on large tables).
Remove unused indexes (check pg_stat_user_indexes for zero scans).
Fix N+1 queries (use JOINs or batch fetching).
Use connection pooling (PgBouncer in transaction mode).
Keep transactions short (set idle_in_transaction_session_timeout).
Let VACUUM do its job (tune autovacuum for high-write tables).

Everything else — configuration tuning, partitioning, replication — comes after you have handled these fundamentals.

Common Pitfalls

Adding indexes without checking for existing ones that cover the same columns. A composite index on (a, b) already covers queries that filter only on (a).
Blaming the database when the problem is N+1 queries in application code. Check your ORM's query log.
Setting max_connections to 1000 instead of using connection pooling. Each connection costs ~10MB of memory. 1000 connections = 10GB just for connections.
Running VACUUM FULL during peak hours. It takes an ACCESS EXCLUSIVE lock on the table. Use plain VACUUM or pg_repack instead.
Ignoring autovacuum warnings in the logs. If autovacuum can't keep up, dead tuples accumulate and queries slow down.
Assuming more RAM fixes everything. If your queries are doing full table scans, more shared_buffers just caches the wrong thing faster.

Key Takeaways

N+1 queries, missing indexes, and table bloat cause the majority of performance problems.
pg_stat_statements, pg_stat_user_tables, and pg_stat_user_indexes are your primary diagnostic tools.
Connection pooling is not optional for any production deployment.
Keep transactions short and set timeouts for idle transactions.
Audit indexes regularly — unused indexes are a write penalty with no read benefit.
Fix the fundamentals before reaching for advanced solutions.