Performance, VACUUM, and Monitoring

Introduction

Slow queries and bloated tables hurt every app. This chapter goes deeper on EXPLAIN ANALYZE, slow query logging, the pg_stat_statements extension, ANALYZE statistics, VACUUM and autovacuum, connection pooling, and essential postgresql.conf knobs. Compare with MySQL query performance—PostgreSQL requires vacuum understanding.

Prerequisites

• Indexes and EXPLAIN ANALYZE
• Isolation and Locking (MVCC and bloat context)

EXPLAIN ANALYZE in Practice

Find slow user lookup:

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT id, email FROM users WHERE email = 'ada@example.com';

Before index—often Seq Scan:

Seq Scan on users ...
  Filter: (email = 'ada@example.com'::text)
  Rows Removed by Filter: ...

Add index and re-run:

CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM users WHERE email = 'ada@example.com';

Look for Index Scan or Index Only Scan and lower actual time.

Join plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.title, u.display_name
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at >= '2026-01-01';

Watch for:

Code explanation:

• estimated rows far from actual rows → run ANALYZE
• BUFFERS shows cache hits vs disk reads

Slow Query Logging

In postgresql.conf (or Docker ALTER SYSTEM):

log_min_duration_statement = 500
log_line_prefix = '%m [%p] %u@%d '

Log statements slower than 500 ms. Reload:

SELECT pg_reload_conf();

Session-only (dev):

SET log_min_duration_statement = 0;

Find log location:

SHOW log_directory;
SHOW data_directory;

Docker:

docker logs postgres 2>&1 | tail -50

pg_stat_statements (Concept)

Extension aggregating query stats—popular in production:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
SELECT
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Requires shared_preload_libraries = 'pg_stat_statements' and restart on some installs—check docs for your version.

Reset stats after deploy:

SELECT pg_stat_statements_reset();

ANALYZE Statistics

Planner uses row count and value distribution estimates:

ANALYZE posts;
ANALYZE users;

Check last analyze:

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Autovacuum runs ANALYZE automatically when enough rows change—manual ANALYZE after large bulk loads.

VACUUM and Dead Tuple Bloat

UPDATE/DELETE leave dead row versions (MVCC). VACUUM marks space reusable:

VACUUM posts;
VACUUM ANALYZE posts;

Verbose bloat check (concept):

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC;

Code explanation:

• High n_dead_tup → table bigger than needed, scans slower
• Long open transactions block vacuum—Isolation and Locking

VACUUM FULL rewrites entire table—exclusive lock, downtime—last resort:

-- VACUUM FULL posts;  -- blocks writes; use pg_repack or maintenance window instead

Autovacuum

Background worker reclaims dead tuples and updates stats:

SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;

Tune per table if needed (advanced):

ALTER TABLE posts SET (
  autovacuum_vacuum_scale_factor = 0.05
);

Optimization Checklist

Bad pattern:

SELECT * FROM posts WHERE date_part('year', published_at) = 2026;

Better:

SELECT id, title FROM posts
WHERE published_at >= '2026-01-01' AND published_at < '2027-01-01';

Connection and Memory Settings (Concept)

Check:

SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;

Too many connections error → use PgBouncer (pooler) in front of Postgres—common in production; apps open a pool, PgBouncer multiplexes to fewer DB connections.

Monitoring Views

Active queries:

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

Database size:

SELECT pg_size_pretty(pg_database_size(current_database()));

Table sizes:

SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Cache hit ratio (rough health):

SELECT
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS heap_hit_ratio
FROM pg_statio_user_tables;

Low ratio on hot tables → consider more RAM or better indexes.

Bottleneck Workflow

1. User reports slow page
2. Find SQL in app logs or pg_stat_statements
3. EXPLAIN ANALYZE on staging copy
4. Add/fix index or rewrite query; ANALYZE
5. Check dead tuples and autovacuum if bloat suspected
6. Re-measure; confirm slow log quiet

FAQ

Seq Scan on small table—bad?

No—optimizer correctly reads whole table when cheaper than index.

Index did not help?

Wrong column order, stale stats, or function on column without expression index.

How often to VACUUM manually?

Rarely if autovacuum healthy—after massive DELETE/UPDATE, run VACUUM ANALYZE.

VACUUM FULL vs REINDEX?

VACUUM FULL compacts heap; REINDEX rebuilds indexes—different bloat sources.

shared_buffers on laptop?

Defaults are modest—Docker dev rarely needs tuning; focus on indexes and query shape first.

Redis instead of tuning Postgres?

Redis caches hot reads—Postgres remains source of truth; fix slow SQL before caching bad queries.