PostgreSQL Performance Degraded After Growth

Problem Summary

PostgreSQL is exceptionally capable under load, but it requires proactive maintenance and correct configuration to stay fast as data volumes grow. The default settings are optimised for a server with 256MB of RAM running in the year 2005 they are not sensible defaults for a production database handling millions of rows. Performance degradation after growth almost always involves some combination of: sequential scans on large tables due to missing indexes, physical table bloat from accumulated dead tuples that autovacuum has not cleaned, query planner statistics that are stale and cause incorrect execution plans, and connection pool exhaustion when application instances multiply. Each of these is diagnosable using PostgreSQL's built-in statistics views and the EXPLAIN ANALYZE command.

Symptoms

• Queries that previously completed in 50ms now take 2-5 seconds with the same parameters
• pg_stat_activity shows many queries in 'idle in transaction' or long-running states
• Sequential scans (Seq Scan) appearing in EXPLAIN ANALYZE output on tables with millions of rows
• PostgreSQL CPU above 80% during normal business hours
• Connection refused errors in application logs: 'too many connections'
• Table sizes growing faster than expected relative to row count, indicating bloat
• autovacuum_count barely incrementing despite high write/delete volume
• Query plans changing unexpectedly after data volume crosses a threshold

Business Impact

PostgreSQL performance degradation compounds quickly. Slow queries hold database connections open longer, which exhausts the connection pool, which causes application timeouts, which causes user-facing errors. A slow query that holds a lock on a high-write table can block all writes to that table, causing application-level failures across multiple features simultaneously. At scale, a single missing index on a hot table can be the direct cause of a site-wide outage.

Root Cause

PostgreSQL performance problems at growth have well-known root causes: missing indexes on columns used in WHERE, JOIN ON, and ORDER BY clauses on high-volume tables; physical bloat from rows deleted or updated without sufficient vacuum cycles; stale planner statistics causing the query planner to underestimate row counts and choose sequential scans over index scans; connection pool exhaustion from too many application instances sharing too few database connections; and queries running on the primary that should be routed to a read replica.

Diagnosis

Step 1: Enable pg_stat_statements and Find Slow Queries

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for all SQL statements. It is the single most useful tool for diagnosing query-level performance problems. If it is not enabled, enable it now on AWS RDS it is available as a shared_preload_library.

-- Enable pg_stat_statements (requires restart if adding to shared_preload_libraries)
-- Add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- Then in psql:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 20 slowest queries by mean execution time
SELECT
  LEFT(query, 120) AS query_snippet,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND(total_exec_time::numeric / 1000, 1) AS total_seconds,
  ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
  ROUND(rows::numeric / calls, 1) AS avg_rows_returned
FROM pg_stat_statements
WHERE calls > 5
  AND mean_exec_time > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Reset stats to start fresh after making changes
SELECT pg_stat_statements_reset();

Step 2: Run EXPLAIN ANALYZE on Slow Queries

For each slow query identified in pg_stat_statements, run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) with representative parameters. The output tells you exactly what the query planner is doing: which indexes it is using (or not using), how many rows it estimated versus actually retrieved, and where the time is being spent. A large discrepancy between estimated rows and actual rows is a sign of stale statistics.

-- Basic EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC;

-- What to look for in output:
-- "Seq Scan" on large tables = missing index
-- "Rows Removed by Filter: 50000" = index exists but is not selective enough
-- estimated rows=5 vs actual rows=48291 = stale statistics, run ANALYZE
-- "Buffers: shared hit=0 read=50000" = data not cached, consider work_mem increase
-- cost=0.00..99999.00 on nested loop = N+1 at SQL level

-- Update statistics for a specific table
ANALYZE orders;

-- Update statistics for the entire database
ANALYZE VERBOSE;

Step 3: Find and Create Missing Indexes

PostgreSQL tracks index usage statistics. Any index with zero scans is potentially unnecessary (wasting write overhead). Any column that appears frequently in query WHERE clauses but has no index is a candidate for creation. Creating indexes concurrently avoids locking the table.

-- Find unused indexes (candidates for removal)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey'  -- keep primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find tables with high sequential scan rates (candidates for new indexes)
SELECT
  tablename,
  seq_scan,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 50000
ORDER BY seq_scan DESC;

-- Create an index WITHOUT locking the table (CONCURRENT)
CREATE INDEX CONCURRENTLY idx_orders_user_id_status
  ON orders (user_id, status)
  WHERE status IN ('pending', 'processing');  -- partial index for common filter

-- Create a composite index for a common query pattern
CREATE INDEX CONCURRENTLY idx_orders_created_at_status
  ON orders (created_at DESC, status)
  INCLUDE (id, user_id, total_amount);  -- covering index avoids table heap fetch

Step 4: Check and Fix Table Bloat

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which means deleted and updated rows are not immediately removed from storage. The VACUUM process cleans these dead tuples. If autovacuum is not keeping up with your write volume, dead tuples accumulate and table bloat occurs: tables consume far more disk space than the live rows justify, and sequential scans read more pages than necessary.

-- Check table bloat and dead tuple accumulation
SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manually vacuum and analyze a bloated table
VACUUM (VERBOSE, ANALYZE) orders;

-- Check autovacuum configuration for a specific table
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

-- Tune autovacuum for a high-write table (run per-table)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum when 1% of rows are dead (default 0.2)
  autovacuum_analyze_scale_factor = 0.005, -- analyze when 0.5% of rows change
  autovacuum_vacuum_cost_delay = 2         -- reduce IO throttling for this table
);

Step 5: Fix Connection Pool Exhaustion with PgBouncer

PostgreSQL creates a new OS process per connection. At 200+ concurrent connections, connection overhead alone consumes significant memory. PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL, multiplexing many application connections into a smaller number of actual database connections. Transaction-mode pooling is appropriate for most web applications.

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1

# Transaction-mode pooling: connection returned to pool after each transaction
# This allows many app connections to share few DB connections
pool_mode = transaction

# Maximum database connections PgBouncer will open to PostgreSQL
max_client_conn = 1000        # clients can connect up to this limit
default_pool_size = 25        # actual PostgreSQL connections per db/user pair
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Logging
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

# Timeouts
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 0

Production Failure Scenarios

Scenario 1: Autovacuum Blocked by Long-Running Transaction

A reporting query runs inside a transaction and takes 2 hours to complete. During this time, autovacuum cannot clean any tables that have rows modified after the transaction started (due to MVCC snapshot requirements). Dead tuples accumulate across all tables. Table bloat causes sequential scans to read far more pages than necessary. Queries that took 50ms now take 5 seconds. Fix: set statement_timeout and idle_in_transaction_session_timeout in postgresql.conf to prevent runaway transactions from blocking autovacuum. Route long-running reports to a read replica.

-- Find long-running transactions blocking autovacuum
SELECT
  pid,
  now() - pg_stat_activity.xact_start AS duration,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > INTERVAL '5 minutes'
ORDER BY xact_start;

-- Set session-level timeouts (add to postgresql.conf for global effect)
SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '60s';

-- Terminate a blocking query (use with caution)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE now() - xact_start > INTERVAL '1 hour'
  AND state = 'idle in transaction';

Scenario 2: Query Planner Chooses Sequential Scan Despite Index

An index exists on orders.status but PostgreSQL performs a sequential scan anyway. This happens when statistics are stale and the planner estimates only 100 rows match status='pending' when the actual count is 500,000 at that selectivity, the planner correctly chooses a sequential scan. Running ANALYZE updates the statistics and the planner switches to an index scan. Fix: run ANALYZE on the table, or tune autovacuum_analyze_scale_factor to trigger more frequent statistics updates.

Scenario 3: Index on Wrong Column Order in Composite Index

A composite index exists as (status, created_at) but the most common query pattern filters by created_at first and status second. PostgreSQL can only use the leftmost prefix of a composite index efficiently. The index is effectively unused for this query pattern. Fix: create a new index with the correct column order (created_at, status) and drop the old one after confirming the new index is being used.

Scenario 4: write_ahead_log Settings Causing I/O Saturation

Default PostgreSQL checkpoint settings cause checkpoint writes to burst every 5 minutes, saturating disk I/O and causing query latency spikes every 5 minutes. Fix: increase checkpoint_completion_target to 0.9 (spread writes over 90% of the checkpoint interval), increase max_wal_size to reduce checkpoint frequency, and use SSD storage for the WAL directory.

Scenario 5: Foreign Keys Without Indexes Causing Lock Contention

A foreign key constraint exists on orders.user_id referencing users.id, but there is no index on orders.user_id. When a user record is deleted or updated, PostgreSQL must scan the entire orders table to check for referencing rows. This full-table scan acquires a share lock that blocks concurrent writes. Fix: add an index on every foreign key column in every table.

Prevention Checklist

• Enable pg_stat_statements at cluster creation and review top queries by mean_exec_time weekly
• Add indexes on all foreign key columns at migration creation time never create a foreign key without an index
• Set autovacuum_vacuum_scale_factor = 0.01 and autovacuum_analyze_scale_factor = 0.005 on high-write tables
• Set statement_timeout = '30s' and idle_in_transaction_session_timeout = '60s' in postgresql.conf
• Deploy PgBouncer in transaction mode between application and PostgreSQL when running more than 3 application instances
• Run EXPLAIN (ANALYZE, BUFFERS) on every new query before deploying to production
• Set checkpoint_completion_target = 0.9 and max_wal_size = 2GB to smooth checkpoint I/O
• Monitor table bloat weekly and alert when dead_pct exceeds 20% for tables over 1GB
• Use CREATE INDEX CONCURRENTLY for all index creation to avoid table locks
• Route reporting and analytics queries to a read replica never run them on the primary

Resolution Summary

Start with pg_stat_statements to find the slowest queries by mean execution time. Run EXPLAIN (ANALYZE, BUFFERS) on each. For queries with sequential scans on large tables, create the appropriate index concurrently. For queries with plan estimation errors, run ANALYZE on the affected tables. For connection exhaustion, deploy PgBouncer. For table bloat, tune per-table autovacuum settings and run VACUUM ANALYZE manually on the worst offenders. These five steps resolve the majority of PostgreSQL performance problems in production.

Lessons Learned

• pg_stat_statements is the single most useful PostgreSQL performance tool enable it from day one
• EXPLAIN ANALYZE with the BUFFERS option reveals cache efficiency, not just query plan structure
• Every foreign key must have a corresponding index this is not optional at production scale
• Autovacuum default settings are tuned for small databases; production tables need explicit per-table configuration
• Long-running transactions block autovacuum silently set idle_in_transaction_session_timeout to prevent this
• Stale query planner statistics are the most common cause of unexpected sequential scans on indexed columns

Conclusion

PostgreSQL performance degradation at scale is almost always diagnosable using the tools PostgreSQL ships with. The sequence is: find slow queries with pg_stat_statements, understand execution plans with EXPLAIN ANALYZE, fix the root cause (missing index, stale statistics, table bloat, or connection exhaustion), and verify the fix with a second EXPLAIN ANALYZE. Pair this with correct autovacuum tuning, per-table vacuum settings for high-write tables, and PgBouncer for connection multiplexing, and PostgreSQL will serve you well into hundreds of millions of rows without a rewrite.