Lesson 17 advanced

Maintenance and Monitoring

Keep Orbit healthy: understand dead rows and what VACUUM and autovacuum do, tune autovacuum for busy tables, avoid transaction ID wraparound, find slow queries with pg_stat_statements, watch sessions, sizes and cache hit rates, log what matters, set sensible memory and checkpoint settings, and pool connections with PgBouncer.

A Postgres server left alone runs well for a surprisingly long time, but not forever. Tables accumulate dead rows, queries slow down as data grows, and connections pile up. This lesson covers the routine care that keeps Orbit fast, and the views and settings that tell you when something is wrong.

Dead rows and VACUUM

Remember MVCC from lesson 12: an UPDATE writes a new row version and leaves the old one behind, and a DELETE only marks a row as expired. Those old versions, called dead tuples, still take space until something removes them. Create some on purpose:

UPDATE notes SET body = body WHERE user_id = 3;
UPDATE 66667

Every one of Chen’s notes now exists twice on disk: the live version and a dead one. (The triggers from lesson 14 also fired once per row, which is why the statement takes a few seconds.) See the dead rows in the statistics:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'notes';

VACUUM finds dead tuples that no running transaction can see anymore and marks their space as reusable:

VACUUM (VERBOSE, ANALYZE) notes;

What VACUUM does, and does not do:

  • It makes space reusable for new rows in the same table. It does not usually shrink the file on disk.
  • It updates the visibility map, which makes index-only scans possible (lesson 11).
  • It freezes old rows, which prevents transaction ID wraparound (below).
  • It does not block reads or writes.

VACUUM FULL rewrites the table compactly and returns space to the operating system, but it locks the table completely for the whole time. On a busy table, use the pg_repack extension instead, which does the same online.

Autovacuum

You rarely run VACUUM by hand, because the autovacuum launcher does it for you. It vacuums a table when its dead tuples exceed a threshold:

vacuum when n_dead_tup > autovacuum_vacuum_threshold (50)
                         + autovacuum_vacuum_scale_factor (0.2) × table rows

That means 20% of a table must be dead before autovacuum acts. For a 100-million-row table, that is 20 million dead rows, far too many. PostgreSQL 18 adds autovacuum_vacuum_max_threshold (100 million by default) as a cap, but busy tables still deserve their own settings:

ALTER TABLE notes SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Warning: Never turn autovacuum off. Tables bloat, queries slow down and, eventually, the server stops accepting writes to protect itself from wraparound.

The most common reason autovacuum cannot clean up is a long-running transaction (often idle in transaction): dead rows newer than its snapshot must be kept. Lesson 12’s idle_in_transaction_session_timeout helps here too.

Transaction ID wraparound

Every write transaction gets a 32-bit ID. After about two billion transactions the counter would wrap around and make old rows look like they are from the future. Vacuum prevents this by freezing old rows. Watch how close each database is:

SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;

Autovacuum starts aggressive freezing at 200 million (autovacuum_freeze_max_age). Alert if the age passes, say, 1 billion: it means vacuum is blocked or cannot keep up.

ANALYZE and statistics

The planner chooses plans from statistics about each column: the most common values, a histogram and the number of distinct values. Autovacuum refreshes them as data changes (autovacuum_analyze_scale_factor), and you should run ANALYZE after bulk loads. For columns with skewed data, collect more detail:

ALTER TABLE notes ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE notes;

Finding slow queries with pg_stat_statements

The single most useful monitoring tool is the pg_stat_statements extension. It records every query shape with its call count, total time, rows and I/O. Enable it once as a superuser (sudo -u postgres psql); loading the library needs a restart:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- restart the server, then in each database you want to inspect:
CREATE EXTENSION pg_stat_statements;

The queries that cost the most in total are usually the ones worth fixing first:

SELECT calls,
       round(total_exec_time) AS total_ms,
       round(mean_exec_time, 2) AS mean_ms,
       rows,
       left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

A query that takes 5 ms but runs a million times a day matters more than a 2-second report that runs once. Take the top entries, run them with EXPLAIN (ANALYZE, BUFFERS) and apply lesson 11.

Watching the server

QuestionWhere to look
Who is connected, running what?pg_stat_activity
What is waiting on locks?pg_stat_activity.wait_event_type, pg_blocking_pids()
Which queries cost the most?pg_stat_statements
Which tables are scanned sequentially?pg_stat_user_tables.seq_scan
Are indexes used?pg_stat_user_indexes.idx_scan
How big are tables and indexes?pg_total_relation_size(), \dt+, \di+
Cache hit rate, deadlocks, temp filespg_stat_database
I/O by backend typepg_stat_io
Replication lagpg_stat_replication (lesson 18)
Progress of VACUUM, index builds, COPYpg_stat_progress_* views

The ten largest tables, with indexes and TOAST data included:

SELECT relid::regclass AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

The share of reads served from memory:

SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;

For a typical web workload this stays above 99%. In production, export these numbers continuously with postgres_exporter into Prometheus and Grafana, and analyse logs with pgBadger.

Logging what matters

A few settings make the server log far more useful. ALTER SYSTEM changes server-wide settings, so run it as a superuser (or a role granted ALTER SYSTEM privilege on those parameters):

ALTER SYSTEM SET log_min_duration_statement = '500ms';  -- log slow statements
ALTER SYSTEM SET log_lock_waits = on;                   -- waits longer than deadlock_timeout
ALTER SYSTEM SET log_autovacuum_min_duration = '10s';   -- slow autovacuum runs
ALTER SYSTEM SET log_temp_files = '10MB';               -- sorts spilling to disk
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';  -- time, pid, user@database
SELECT pg_reload_conf();

ALTER SYSTEM writes to postgresql.auto.conf, which overrides postgresql.conf. pg_reload_conf() applies settings that do not need a restart; SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart; lists the ones that do.

Memory and checkpoint settings

The defaults are tiny, chosen so Postgres starts on any machine. Reasonable starting points for a dedicated server:

SettingStarting pointWhat it does
shared_buffers25% of RAMPostgres’s own page cache (restart needed)
effective_cache_size50 to 75% of RAMPlanner hint: how much the OS can cache
work_mem16 to 64 MBMemory per sort or hash step, per query, per connection
maintenance_work_mem512 MB to 2 GBMemory for VACUUM, CREATE INDEX
max_wal_size4 to 16 GBWAL allowed between checkpoints; larger means fewer forced checkpoints
random_page_cost1.1 on SSDMakes the planner more willing to use indexes on fast storage

Be careful with work_mem: a complex query can use it several times, and every connection can run such a query. Raise it per session or per role for heavy reports instead of globally.

Connection pooling

Each connection is a process using several megabytes of memory (lesson 1). Hundreds of idle application connections waste memory, and thousands make the server slow. A pooler such as PgBouncer keeps a small set of server connections and hands them to many clients:

[databases]
orbit = host=127.0.0.1 port=5432 dbname=orbit

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

The application connects to port 6432 instead of 5432. In transaction mode a server connection is borrowed only for the length of a transaction, which is the most efficient, but session state (SET, advisory locks, LISTEN) does not survive between transactions. Use SET LOCAL inside transactions, as lesson 15 did for row-level security.

A maintenance checklist

How oftenCheck
ContinuouslyDisk space, replication lag, connections, errors in logs, backup success
DailyTop queries in pg_stat_statements, long or idle transactions
WeeklyTables with many dead tuples, unused and invalid indexes, slow autovacuum runs
MonthlyTransaction ID age, table and index growth, restore test (lesson 16)
QuarterlyApply the latest minor release

Quick reference

TaskSQL / command
Clean up a tableVACUUM (VERBOSE, ANALYZE) t;
Shrink a table onlinepg_repack (extension)
Per-table autovacuumALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.02);
Dead tuplespg_stat_user_tables.n_dead_tup
Wraparound ageage(datfrozenxid) in pg_database
Top queriespg_stat_statements ORDER BY total_exec_time DESC
Sizespg_total_relation_size(...), \dt+
Change a settingALTER SYSTEM SET ...; SELECT pg_reload_conf();
Pending restartspg_settings WHERE pending_restart
Pool connectionsPgBouncer, pool_mode = transaction

Exercise

A table’s file stays 40 GB after deleting half its rows and running VACUUM. Why?

  1. VACUUM did not run because autovacuum is on
  2. Deleted rows are kept for point-in-time recovery
  3. VACUUM makes the space reusable inside the table but does not normally return it to the operating system
  4. The table needs to be analyzed first
Show answer

Answer: C. Freed space is reused by future inserts and updates. To shrink the file, rewrite the table with VACUUM FULL (exclusive lock) or pg_repack (online).

Exercise

Autovacuum runs on a busy table but n_dead_tup keeps growing. What is the most common cause?

  1. A long-running or idle-in-transaction session holds an old snapshot, so dead rows cannot be removed yet
  2. The table has too many indexes
  3. shared_buffers is too small
  4. The table uses jsonb columns
Show answer

Answer: A. Rows that an open snapshot might still need cannot be cleaned up. Find old transactions in pg_stat_activity and set idle_in_transaction_session_timeout.

Exercise

Which query from pg_stat_statements is usually the best one to optimise first?

  1. The one with the highest maximum execution time
  2. The one with the longest SQL text
  3. The one most recently executed
  4. The one with the highest total execution time
Show answer

Answer: D. Total time combines how slow a query is with how often it runs. A fast query called millions of times often costs more than a slow query called once.

Key takeaways

  • Updates and deletes leave dead tuples; VACUUM makes their space reusable and autovacuum runs it for you. Never disable it.
  • Tune autovacuum per table for big, busy tables, and keep transactions short so cleanup can happen.
  • Watch transaction ID age to stay far from wraparound.
  • pg_stat_statements shows which queries cost the most; fix by total time.
  • Log slow statements and lock waits, size memory settings for your hardware, and pool connections with PgBouncer.

FAQ

Should I schedule VACUUM FULL every night?

No. It locks tables and rewrites them for little lasting gain, because normal tables reach a steady state where freed space is reused. Tune autovacuum instead, and use pg_repack for the rare table that really needs shrinking.

How many connections should max_connections allow?

Fewer than you think. A few hundred at most for most servers, with a pooler in front. More connections than CPU cores actively running queries just adds contention.

What does “checkpoints are occurring too frequently” in the log mean?

WAL fills max_wal_size before checkpoint_timeout is reached, so checkpoints are forced. Raise max_wal_size so checkpoints happen on the timer, which spreads I/O more evenly.

Is a 95% cache hit ratio bad?

For analytics workloads that scan large tables, it can be normal. For an OLTP application like Orbit it suggests the working set no longer fits in memory: look at shared_buffers, RAM and which queries read the most blocks.

Do I need pg_stat_statements on managed services?

Most managed Postgres services enable it by default or with one setting. It is the first thing to check when a database gets slower.

↑ ↓ navigate Enter open Esc close