PostgreSQL: Maintenance, Autovacuum and Monitoring

This note was written for PostgreSQL 13–18.
On older versions some columns of system views may differ. When in doubt, check the documentation for your version.
PostgreSQL 19 is still in beta/RC (release expected in October 2026). Where it changes something relevant, it is marked as “PG19 (beta)”.

Legend used in the explanations:

🟢 normal
🟡 worth a look
🔴 a problem that requires action

Contents#

1. How autovacuum works (briefly)
2. Current autovacuum settings
3. What autovacuum is doing right now
4. Dead rows and vacuum history per table
5. Transaction ID wraparound
6. What prevents vacuum from removing rows
7. Table and index sizes
8. Bloat
9. Indexes: unused, duplicate, invalid
10. General database health
11. Locks
12. Heavy queries (pg_stat_statements)
13. Replication and WAL
14. Checkpoints and I/O
15. What to monitor and alert thresholds
16. Configuration recommendations
17. Routine maintenance
18. Partitioning
19. Managed PostgreSQL in the clouds

1. How autovacuum works (briefly)#

PostgreSQL does not overwrite a row on UPDATE or DELETE. The old row version stays on disk as a dead tuple until VACUUM removes it. This is called MVCC.

Autovacuum does three things:

1. VACUUM — marks the space of dead rows as free for reuse (disk space is not returned to the OS, but the table stops growing).
2. ANALYZE — collects data distribution statistics the planner uses to choose query plans. Stale statistics = bad plans = slow queries.
3. Freezing — protects against transaction counter exhaustion (wraparound). Without it the database will stop.

Autovacuum runs on a table when the number of dead rows exceeds a threshold:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × row_count

With default values (50 + 0.2 × rows), a 100-million-row table waits for 20 million dead rows. This is the main reason defaults are unsuitable for large tables.

Since PG18 the result is capped: threshold = min(autovacuum_vacuum_max_threshold, ...), default 100 million dead rows. It only helps tables with more than 500 million rows, so the per-table tuning below is still needed.

Vacuum cannot remove a dead row if any transaction in the cluster might theoretically still see it. That is why long transactions are enemy number one.

2. Current autovacuum settings#

SELECT name, setting, unit, source, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
   OR name IN ('maintenance_work_mem', 'vacuum_cost_limit', 'vacuum_cost_delay',
               'log_autovacuum_min_duration', 'track_counts', 'track_io_timing')
ORDER BY name;

Column meanings:

What to look at:

Per-table settings (override global ones):

SELECT n.nspname AS schema, c.relname AS table_name, c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.reloptions IS NOT NULL
ORDER BY 1, 2;

If you see autovacuum_enabled=false — 🔴 someone disabled autovacuum on the table. This is almost always a mistake.

3. What autovacuum is doing right now#

SELECT pid,
       now() - xact_start AS running_for,
       wait_event_type, wait_event,
       query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

Columns:

An empty result means no worker is running right now. That is normal if no tables exceed their threshold (see section 4).

Vacuum progress by phase:

SELECT p.pid,
       p.relid::regclass AS table_name,
       p.phase,
       p.heap_blks_total,
       p.heap_blks_scanned,
       round(p.heap_blks_scanned * 100.0 / nullif(p.heap_blks_total, 0), 1) AS scanned_pct,
       p.heap_blks_vacuumed,
       p.index_vacuum_count,
       p.num_dead_tuples
FROM pg_stat_progress_vacuum p;

Columns:

In PG17, num_dead_tuples was renamed to num_dead_item_ids, max_dead_tuples to max_dead_tuple_bytes, and dead_tuple_bytes, indexes_total, indexes_processed were added. PG18 adds delay_time (time spent sleeping on cost delay, requires track_cost_delay_timing = on). PG19 (beta) adds started_by and mode.

4. Dead rows and vacuum history per table#

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       n_mod_since_analyze,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       vacuum_count, autovacuum_count,
       analyze_count, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

Columns:

Since PG18 the same view also has total_vacuum_time, total_autovacuum_time, total_analyze_time, total_autoanalyze_time (milliseconds). Sort by total_autovacuum_time DESC to find the tables that eat most of the workers’ time.

:::warning Important n_live_tup and n_dead_tup are estimates, not exact. For diagnostics that is sufficient. :::

Tables that have already exceeded the autovacuum threshold but have not been processed yet:

SELECT s.schemaname, s.relname,
       s.n_dead_tup,
       round(current_setting('autovacuum_vacuum_threshold')::int
           + current_setting('autovacuum_vacuum_scale_factor')::numeric * c.reltuples) AS threshold,
       s.last_autovacuum,
       now() - s.last_autovacuum AS since_last_autovacuum
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
WHERE s.n_dead_tup > current_setting('autovacuum_vacuum_threshold')::int
                   + current_setting('autovacuum_vacuum_scale_factor')::numeric * c.reltuples
ORDER BY s.n_dead_tup DESC;

How to read the result:

• Empty list — 🟢 autovacuum is keeping up.
• A few tables, since_last_autovacuum is small — 🟢 they are simply queued, workers are busy. Normal.
• Many tables or since_last_autovacuum of hours/days — 🟡 too few workers, cost_limit too low, or vacuum is being blocked (section 6).
• A table with millions of n_dead_tup, autovacuum ran recently, yet dead rows did not decrease — 🔴 vacuum cannot remove them. Definitely check section 6.

5. Transaction ID wraparound#

The most critical check. The transaction counter (XID) is 32-bit and circular. If the age of the oldest unfrozen transaction approaches ~2.1 billion, PostgreSQL stops accepting writes to avoid data loss.

Per database:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS mxid_age,
       round(age(datfrozenxid) * 100.0 / 2147483647, 2) AS pct_to_wraparound,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM pg_database
ORDER BY xid_age DESC;

Columns:

Thresholds:

Per table (to find which table drags the database age):

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

The database age equals the age of its oldest table. If one table stands out, it is usually either a huge table that vacuum takes long to process, a table with autovacuum disabled, or a table someone holds a lock on. relkind = 't' are TOAST tables (storing large values), they need freezing too.

Manual freezing of a table:

VACUUM (FREEZE, VERBOSE) schema.table_name;

6. What prevents vacuum from removing rows#

If n_dead_tup does not decrease after autovacuum or xid_age keeps growing, something is keeping old rows “visible”. Four typical causes.

6.1. Long transactions and idle in transaction#

SELECT pid, usename, application_name, client_addr, state,
       now() - xact_start AS xact_duration,
       now() - state_change AS in_state_for,
       age(backend_xmin) AS xmin_age,
       left(query, 100) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
   OR state = 'idle in transaction'
ORDER BY xact_start NULLS LAST
LIMIT 20;

Columns:

Terminate a problematic session:

SELECT pg_cancel_backend(pid);     -- soft: cancel the query
SELECT pg_terminate_backend(pid);  -- hard: drop the connection

6.2. Replication slots#

SELECT slot_name, slot_type, active, database,
       age(xmin) AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       wal_status,
       inactive_since, invalidation_reason   -- PG17+, remove on older versions
FROM pg_replication_slots;

Columns:

Two safety nets: max_slot_wal_keep_size (PG13+) caps WAL retained by slots, and idle_replication_slot_timeout (PG18+) invalidates slots with no consumer. Both sacrifice the slot to protect the disk.

Drop an abandoned slot (make sure it is really not needed):

SELECT pg_drop_replication_slot('slot_name');

6.3. Prepared transactions (two-phase commit)#

SELECT gid, prepared, owner, database,
       now() - prepared AS age_of_prepared,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

Normally the table is empty. If there are rows with prepared older than a few minutes — 🔴 forgotten transactions from an XA/JTA manager. They hold locks and block vacuum forever. If definitely not needed: ROLLBACK PREPARED 'gid';

6.4. Queries on replicas with hot_standby_feedback#

If a replica has hot_standby_feedback = on, long queries on the replica send their xmin to the primary and block vacuum there. Check with the query from 6.1 on the replica. Trade-off: without feedback, long queries on the replica will get canceling statement due to conflict with recovery errors.

7. Table and index sizes#

SELECT c.oid::regclass AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size,
       pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid) - pg_indexes_size(c.oid)) AS toast_size,
       c.reltuples::bigint AS est_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

Columns:

Whole database size:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

8. Bloat#

Bloat is space in a table or index occupied by dead rows or emptiness. Vacuum marks it free, but the file does not shrink. A table with 1 GB of live data can occupy 10 GB.

An accurate estimate needs the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Table (fast approximate estimate, does not read the whole table):

SELECT * FROM pgstattuple_approx('public.my_table');

Exact (slow, reads the whole table): SELECT * FROM pgstattuple('public.my_table'); — same columns without the approx_ prefix.

Index:

SELECT * FROM pgstatindex('public.my_index');

Without the extension you can use ready-made estimation queries (search for “postgres bloat estimation query”, e.g. from the ioguix/pgsql-bloat-estimation repository). They are inexact (±20–30%) but do not require privileges to create extensions.

How to remove bloat:

9. Indexes: unused, duplicate, invalid#

9.1. Unused indexes#

Every index slows down INSERT/UPDATE/DELETE and takes space. Redundant ones should be dropped.

SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       s.idx_scan,
       s.last_idx_scan,                       -- PG16+, remove on older versions
       s.idx_tup_read,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       pg_get_indexdef(s.indexrelid) AS definition
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Before dropping, always check:

1. When statistics were reset: SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); If the reset was a week ago and a report runs monthly, the index may still be needed.
2. Statistics on replicas are kept separately. An index unused on the primary may be used by reports on a replica. Run the query on every replica.
3. Unique indexes and PKs are deliberately excluded from the query: they enforce integrity even when not used for lookups.

Drop without locking: DROP INDEX CONCURRENTLY index_name;

9.2. Duplicate indexes#

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS total_size,
       array_agg(idx::regclass) AS indexes,
       (array_agg(idx::regclass))[1]::regclass::text AS keep_one_of
FROM (
    SELECT indexrelid AS idx,
           (indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n'
            || coalesce(indexprs::text, '') || E'\n' || coalesce(indpred::text, '')) AS key
    FROM pg_index
) sub
GROUP BY key
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

Shows groups of indexes with identical definitions (same columns, same order). Keep one, drop the rest.

9.3. Invalid indexes#

SELECT c.oid::regclass AS index_name, i.indrelid::regclass AS table_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

An invalid index appears when CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY failed midway. 🔴 It takes space and is updated on writes, but is not used for reads. Drop it (DROP INDEX) and recreate.

10. General database health#

10.1. Per-database statistics#

SELECT datname,
       numbackends,
       round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       xact_commit, xact_rollback,
       round(xact_rollback * 100.0 / nullif(xact_commit + xact_rollback, 0), 2) AS rollback_pct,
       deadlocks,
       conflicts,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_size,
       stats_reset
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

10.2. Connections#

SELECT state, count(*),
       max(now() - state_change) AS max_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

SELECT current_setting('max_connections')::int AS max_connections,
       count(*) AS current_connections,
       round(count(*) * 100.0 / current_setting('max_connections')::int, 1) AS used_pct
FROM pg_stat_activity;

10.3. Who is doing what right now (top active queries)#

SELECT pid, usename, application_name, state,
       now() - query_start AS running_for,
       wait_event_type, wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
ORDER BY query_start
LIMIT 20;

wait_event_type: Lock — waiting for a lock (section 11), IO — disk, LWLock — internal locks (often BufferMapping/WALWrite under overload), Client — waiting for the client, NULL while active — actually running on CPU.

11. Locks#

Who is blocking whom:

SELECT a.pid AS blocked_pid,
       a.usename AS blocked_user,
       now() - a.query_start AS blocked_for,
       left(a.query, 80) AS blocked_query,
       b.pid AS blocking_pid,
       b.usename AS blocking_user,
       b.state AS blocking_state,
       now() - b.xact_start AS blocking_xact_age,
       left(b.query, 80) AS blocking_query
FROM pg_stat_activity a
JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS bp(pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.pid
ORDER BY a.query_start;

Empty result — 🟢 nobody is waiting on anybody.

Lock details by object (when you need to understand which table is held by whom):

SELECT l.pid, l.locktype, l.mode, l.granted,
       l.relation::regclass AS relation,
       a.state, left(a.query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IS NOT NULL
  AND l.pid <> pg_backend_pid()
ORDER BY l.granted, l.pid;

granted = false — the lock is being waited for, mode — level: AccessShareLock (reads, harmless), RowExclusiveLock (writes), AccessExclusiveLock (DDL, VACUUM FULL, TRUNCATE — blocks everything, even reads).

Useful parameters: log_lock_waits = on (logs waits longer than deadlock_timeout, default 1s), lock_timeout for DDL migrations (so ALTER TABLE does not queue up and block everyone behind it).

12. Heavy queries (pg_stat_statements)#

The extension collects statistics for all queries. Requires shared_preload_libraries = 'pg_stat_statements' (restart) and CREATE EXTENSION pg_stat_statements; in the database.

Top by total time (what loads the server the most):

SELECT left(query, 100) AS query,
       calls,
       round(total_exec_time::numeric / 1000, 1) AS total_sec,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows,
       round(shared_blks_hit * 100.0 / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

In PG12 and older the columns are named total_time, mean_time, etc. (without _exec).

Top by call count: ORDER BY calls DESC. Top by mean time: ORDER BY mean_exec_time DESC (with calls > 100 to filter out one-offs). Top by I/O: ORDER BY shared_blks_read DESC.

Reset statistics after optimizations: SELECT pg_stat_statements_reset();

13. Replication and WAL#

On the primary — replica status:

SELECT client_addr, application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS sent_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

On the replica — lag:

SELECT pg_is_in_recovery() AS is_replica,
       pg_last_wal_receive_lsn() AS received,
       pg_last_wal_replay_lsn() AS replayed,
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

replay_delay — how long ago the last applied transaction occurred. If the primary is idle with no writes, the value grows even though there is no real lag.

WAL on disk:

SELECT count(*) AS wal_files,
       pg_size_pretty(sum(size)) AS wal_size
FROM pg_ls_waldir();

If wal_size is much larger than max_wal_size — 🔴 WAL is accumulating. Causes: an inactive replication slot (section 6.2), a failing archive_command (check pg_stat_archiver), a lagging replica.

SELECT archived_count, failed_count,
       last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;

failed_count growing, last_failed_time recent — 🔴 WAL archiving is broken, WAL is not removed, the disk will fill up.

14. Checkpoints and I/O#

-- PG up to 16
SELECT checkpoints_timed, checkpoints_req,
       round(checkpoints_req * 100.0 / nullif(checkpoints_timed + checkpoints_req, 0), 1) AS req_pct,
       round(checkpoint_write_time / 1000) AS write_time_sec,
       buffers_checkpoint, buffers_clean, buffers_backend,
       maxwritten_clean,
       stats_reset
FROM pg_stat_bgwriter;

-- PG 17+
SELECT num_timed, num_requested,
       round(num_requested * 100.0 / nullif(num_timed + num_requested, 0), 1) AS req_pct,
       round(write_time / 1000) AS write_time_sec,
       buffers_written, stats_reset
FROM pg_stat_checkpointer;

In PG17+ buffers_clean and maxwritten_clean stay in pg_stat_bgwriter. buffers_backend was removed, the same information lives in pg_stat_io (PG16+): rows with backend_type = 'client backend', context = 'normal', column writes. PG18 adds num_done (completed checkpoints, num_timed/num_requested also count skipped ones) and slru_written.

Enable log_checkpoints = on (default since PG15): the logs will show the duration and volume of each checkpoint.

I/O per table (where most disk reads happen):

SELECT schemaname, relname,
       heap_blks_read, heap_blks_hit,
       round(heap_blks_hit * 100.0 / nullif(heap_blks_hit + heap_blks_read, 0), 1) AS heap_hit_pct,
       idx_blks_read, idx_blks_hit,
       round(idx_blks_hit * 100.0 / nullif(idx_blks_hit + idx_blks_read, 0), 1) AS idx_hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read + idx_blks_read DESC
LIMIT 20;

*_blks_read — reads from disk, *_blks_hit — from cache. Tables with a low hit_pct and high blks_read are candidates for query or index optimization.

Seq scan vs index scan:

SELECT schemaname, relname,
       seq_scan, seq_tup_read,
       idx_scan,
       n_live_tup,
       CASE WHEN seq_scan > 0 THEN seq_tup_read / seq_scan END AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

seq_scan — full table scans. Many seq_scans on a large table (n_live_tup in the millions) with a high avg_rows_per_seq_scan — 🟡 an index is missing. For small tables a seq scan is normal and faster than an index.

15. What to monitor and alert thresholds#

A minimal set of metrics for any installation. Thresholds are indicative and should be tuned for your system.

Tools:

• Prometheus + postgres_exporter + Grafana — the self-hosted standard. Ready-made dashboards exist.
• pgwatch2, pganalyze, Datadog, New Relic — turnkey solutions with a UI.
• pg_stat_statements — mandatory regardless of the tool.
• auto_explain — logs query plans for queries longer than auto_explain.log_min_duration. Indispensable for catching slow queries that look average in pg_stat_statements.
• pgBadger — log analyzer. Generates an HTML report: top queries, errors, locks, checkpoints, autovacuum. Requires a proper log_line_prefix and log_min_duration_statement.

Logging settings needed for proper monitoring:

log_min_duration_statement = 1000   # queries longer than 1 s (tune)
log_lock_waits = on
log_checkpoints = on
log_autovacuum_min_duration = 1000
log_temp_files = 0                  # or a threshold in kB
log_connections = on                # if you need to see who and from where (PG18+: a list, e.g. 'receipt,authentication,authorization')
log_line_prefix = '%m [%p] %q%u@%d %a %h '
track_io_timing = on                # I/O time in EXPLAIN and pg_stat_statements

16. Configuration recommendations#

Autovacuum#

1. Never disable it — neither globally nor per table. If autovacuum is in the way, make it more aggressive so runs are more frequent and smaller, rather than turning it off.

2. Lower the scale factor for large tables individually:

   ALTER TABLE big_table SET (
     autovacuum_vacuum_scale_factor = 0.01,     -- 1% instead of 20%
     autovacuum_analyze_scale_factor = 0.02,
     autovacuum_vacuum_cost_limit = 2000        -- optional, per-table limit
   );
   

   Rule of thumb:

   • tables up to 1 M rows — defaults are fine
   • 1–10 M — 0.05, above 10 M — 0.01–0.02
   • above 100 M — 0.005 or a fixed autovacuum_vacuum_threshold in the tens/hundreds of thousands with scale_factor = 0.

3. Give it speed. For modern disks (SSD, NVMe, cloud volumes):

   • autovacuum_vacuum_cost_limit = 1000 (starting value, 2000–3000 for powerful systems)
   • autovacuum_vacuum_cost_delay = 2ms
   • autovacuum_max_workers = 4–6 (and raise cost_limit accordingly, since it is shared among workers). Up to PG17 this parameter is static — requires a restart. Since PG18 it can be raised on reload up to autovacuum_worker_slots (default 16, that one is static).

4. Give it memory: autovacuum_work_mem = 512MB–1GB (before PG17 vacuum uses at most 1 GB, so more is pointless). maintenance_work_mem = 1GB for manual VACUUM, CREATE INDEX, REINDEX.

5. Log it: log_autovacuum_min_duration = 1000. PG19 (beta) splits it: log_autovacuum_min_duration covers only vacuum, analyze goes to the new log_autoanalyze_min_duration.

6. Insert-only tables (logs, events): since PG13 these are handled by autovacuum_vacuum_insert_scale_factor (default 0.2). Do not disable — it is needed for the visibility map (fast index-only scans) and freezing.

7. Large tables and wraparound: for tables of hundreds of GB, lower autovacuum_freeze_max_age for them (e.g. 100 M) so that freezing happens more often in smaller portions, instead of one multi-hour anti-wraparound vacuum at the worst possible moment.

Transactions and connections#

• idle_in_transaction_session_timeout = '5min' (or less for OLTP). The most common reason autovacuum runs yet bloat grows.
• statement_timeout for the application role (e.g. 30s–60s): ALTER ROLE app_user SET statement_timeout = '60s';. A separate value, or none, for reporting/analytics roles.
• lock_timeout = '5s' for sessions running DDL migrations, so ALTER TABLE does not queue in the lock chain and halt all traffic.
• A connection pooler (PgBouncer, pgcat, or your framework’s built-in one) instead of a large max_connections. Every PostgreSQL connection is a separate process with its own memory.

Memory#

• shared_buffers = 25% of RAM (starting point, above 8–16 GB rarely helps, since the OS cache does the rest).
• effective_cache_size = 50–75% of RAM (does not allocate memory, just tells the planner how much data may be cached).
• work_mem — carefully: it is allocated per sort/hash operation in every query, not per connection. For OLTP 16–64 MB. For analytics it can be higher, but better set via SET work_mem in a specific session or for a specific role.

WAL and checkpoints#

• max_wal_size = 4–16GB (the 1 GB default is too low for busy systems and causes frequent requested checkpoints).
• checkpoint_timeout = 15min, checkpoint_completion_target = 0.9 (default since PG14).
• wal_compression = on — less WAL at the cost of a little CPU.

What not to do in production#

• VACUUM FULL and CLUSTER during business hours — exclusive lock for the whole duration. Use pg_repack.
• REINDEX without CONCURRENTLY (PG12+) — same thing.
• CREATE INDEX without CONCURRENTLY on a large table.
• ALTER TABLE ... ADD COLUMN ... DEFAULT with a volatile default (e.g. now()), or changing a column type — rewrites the table. Since PG11 a constant default is added instantly.
• Resetting statistics (pg_stat_reset()) without need — autovacuum loses its knowledge of dead rows until the next ANALYZE.

17. Routine maintenance#

Partitioning for very large tables with time-based data (logs, events, transactions): old partitions are removed instantly via DROP TABLE/DETACH PARTITION, instead of a bulk DELETE that leaves vacuum with lots of work and leaves bloat behind. Autovacuum processes each partition separately and in parallel. Details in section 18.

18. Partitioning#

Partitioning splits one logical table into several physical tables (partitions) by a key. For maintenance it matters because vacuum, analyze, freezing, bloat and retention are then handled per partition instead of per one huge table.

18.1. When it helps and when it does not#

What partitioning does not give: a global unique constraint on a column outside the partition key, and faster point lookups by primary key (pruning only saves the planner work, the index on the partition is what makes the lookup fast).

18.2. Choosing the key and the type#

Rules:

• The key must be a column of every PRIMARY KEY and UNIQUE constraint on the table. A table with PRIMARY KEY (id) partitioned by created_at needs PRIMARY KEY (id, created_at). Plan the application for that.
• Most queries must include the partition key in WHERE, otherwise pruning does not happen. Check with EXPLAIN: only the needed partitions should appear in the plan, and Subplans Removed: N shows runtime pruning for parameterized queries.
• Foreign keys to a partitioned table work since PG12, foreign keys from a partitioned table since PG11.
• Partition granularity: monthly is the default choice, weekly for tables growing by tens of GB per month, daily only for very high volume. Target partitions of a few GB to a few tens of GB. Hundreds of partitions are fine, thousands slow down planning and need a larger max_locks_per_transaction (every partition touched by a query takes a lock, at 64 by default a query over 100+ partitions in a long transaction can fail with out of shared memory).

18.3. Autovacuum and statistics on partitioned tables#

• Autovacuum works on partitions, each with its own threshold (section 1). Since thresholds are proportional to partition size, defaults work much better than on one huge table. Per-partition ALTER TABLE partition SET (...) is still possible for the hot current partition.

• 🔴 Autovacuum never analyzes the parent. The partitioned table itself has no rows, but the planner uses its statistics for joins and aggregates over the whole table. Nobody collects them automatically, up to and including PG18. Schedule it manually (pg_cron, section 17):

  ANALYZE parent_table;         -- also re-analyzes every partition, can be slow
  ANALYZE ONLY parent_table;    -- PG18+: parent statistics only, fast
  

  Check when the parent was last analyzed:

  SELECT c.oid::regclass AS parent, s.last_analyze, s.last_autoanalyze
  FROM pg_class c
  LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
  WHERE c.relkind = 'p'
  ORDER BY 1;
  

  last_autoanalyze is always NULL here. 🟡 last_analyze older than a week on an actively growing table — plans for cross-partition queries are based on stale numbers.

• Freezing (section 5) is also per partition. Old partitions reach relfrozenxid once and then cost nothing. The section 5 per-table query lists partitions individually (relkind = 'r'), the parent has no relfrozenxid of its own.

• pg_stat_user_tables and all queries in section 4 show partitions as separate rows. Aggregate by parent when you need a per-table picture:

  SELECT i.inhparent::regclass AS parent,
         count(*) AS partitions,
         pg_size_pretty(sum(pg_total_relation_size(i.inhrelid))) AS total_size,
         sum(s.n_live_tup) AS live_rows,
         sum(s.n_dead_tup) AS dead_rows,
         min(s.last_autovacuum) AS oldest_autovacuum
  FROM pg_inherits i
  JOIN pg_class p ON p.oid = i.inhparent AND p.relkind = 'p'
  LEFT JOIN pg_stat_user_tables s ON s.relid = i.inhrelid
  GROUP BY 1
  ORDER BY sum(pg_total_relation_size(i.inhrelid)) DESC;
  

18.4. Partition inventory#

SELECT i.inhparent::regclass AS parent,
       c.oid::regclass AS partition,
       pg_get_expr(c.relpartbound, c.oid) AS bounds,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
       s.n_live_tup, s.n_dead_tup,
       s.last_autovacuum, s.last_autoanalyze
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
JOIN pg_class p ON p.oid = i.inhparent AND p.relkind = 'p'
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
ORDER BY 1, 2;

Default partition:

SELECT pt.partrelid::regclass AS parent,
       pt.partdefid::regclass AS default_partition,
       s.n_live_tup
FROM pg_partitioned_table pt
LEFT JOIN pg_stat_user_tables s ON s.relid = pt.partdefid
WHERE pt.partdefid <> 0;

A default partition catches rows that match no partition. It is a safety net, not a place to keep data: 🟡 n_live_tup > 0 means a partition was not created in time, and rows now have to be moved by hand. A non-empty default partition also makes ATTACH PARTITION scan it fully under lock to verify there are no conflicting rows. Either do not create one and let inserts fail loudly, or alert on its row count.

18.5. Creating, attaching and dropping partitions#

• Create partitions ahead of time, at least a few periods in advance. Use pg_partman (partman.create_parent(), partman.run_maintenance_proc() scheduled via pg_cron, premake and retention settings), or your own job. An alert for “no partition exists for next month” is cheaper than a 3 a.m. outage from failed inserts.

• Retention: DROP TABLE partition or ALTER TABLE parent DETACH PARTITION partition and archive it. Both are instant and leave no bloat. DETACH ... CONCURRENTLY (PG14+) takes only a SHARE UPDATE EXCLUSIVE lock, without CONCURRENTLY it is ACCESS EXCLUSIVE on the parent and blocks everything for the duration.

• Attaching a table with existing data: add a CHECK constraint matching the future bounds first, then attach. Otherwise ATTACH PARTITION scans the whole table under ACCESS EXCLUSIVE lock to validate it.

  ALTER TABLE events_2026_09 ADD CONSTRAINT tmp_bounds
    CHECK (event_time >= '2026-09-01' AND event_time < '2026-10-01');
  ALTER TABLE events ATTACH PARTITION events_2026_09
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
  ALTER TABLE events_2026_09 DROP CONSTRAINT tmp_bounds;
  

• Converting an existing large table cannot be done in place. Create the new partitioned table, copy data in batches (or ATTACH the old table as one big partition and let new data flow into fresh partitions), swap names in one transaction. pg_partman has partition_data_proc() for batch moves. For zero-downtime migrations of very large tables use logical replication into the new structure.

18.6. Indexes on partitioned tables#

• An index created on the parent is automatically created on every partition, including future ones. This is the normal way.

• 🔴 CREATE INDEX CONCURRENTLY is not supported on a partitioned table. On a live system do it in three steps so the parent index does not take SHARE locks on all partitions at once:

  CREATE INDEX events_user_idx ON ONLY events (user_id);              -- parent only, marked invalid
  CREATE INDEX CONCURRENTLY events_2026_09_user_idx ON events_2026_09 (user_id);  -- repeat per partition
  ALTER INDEX events_user_idx ATTACH PARTITION events_2026_09_user_idx;           -- repeat per partition
  

  The parent index becomes valid once every partition has an attached index. The section 9.3 query shows it as invalid until then.

• REINDEX TABLE CONCURRENTLY parent (PG14+) rebuilds partition indexes one by one. pg_repack works per partition. Section 8 bloat checks apply per partition, and for time-based data bloat is usually only in the current partition.

• The duplicate-index query in section 9.2 lists partition indexes too. Partition indexes inherited from one parent index are not duplicates, compare by parent.

18.7. Planner settings#

• enable_partition_pruning = on (default). Never turn it off.
• enable_partitionwise_join and enable_partitionwise_aggregate are off by default because they cost planning time. Turn them on for analytics workloads that join or aggregate two tables partitioned the same way, keep them off on pure OLTP.
• Partitioned tables are a reason to keep an eye on max_locks_per_transaction (section 18.2) and on planning time in EXPLAIN ANALYZE for queries without the partition key.

18.8. Version notes#

19. Managed PostgreSQL in the clouds#

A managed service takes the OS, backups, minor updates and failover off your hands. Parameter tuning, schema, queries, and making sure autovacuum keeps up stay with you. Everything in sections 2–18 applies to every provider below, with the differences listed here.

19.1. Common to all providers#

Rules that hold everywhere:

• No superuser, no SSH. CREATE EXTENSION, pg_repack, VACUUM on other owners’ tables all have to run as the provider’s admin role or the table owner (or a role with MAINTAIN, PG17+). Section 2’s pg_settings query is the only way to confirm what was really applied.
• Static parameters need a restart and the provider decides how that restart happens (maintenance window, failover, immediate). shared_buffers, max_connections, autovacuum_max_workers (up to PG17), shared_preload_libraries and all *_preload-style switches are static everywhere.
• Replication slots are the number one cause of a full disk on every provider: a CDC connector was stopped, the slot stayed. Section 6.2 queries and the provider’s slot lag metric are mandatory once logical replication is on.
• Major upgrades use pg_upgrade under the hood. Up to PG17 planner statistics are lost, run ANALYZE on every database afterwards. PG18+ carries basic statistics over, providers still recommend an ANALYZE pass.

Extensions used in this note (all providers, checked against the official lists in October 2026):

The pg_repack client version must match the server extension version, on every provider. pgBadger, PgBouncer, pgcat, postgres_exporter, pgwatch2 and pganalyze are not extensions, they run outside the database and work with any provider that lets you export logs or connect.

19.2. AWS RDS for PostgreSQL#

What is not available#

• No superuser. rds_superuser has most privileges, but no access to the file system, pg_hba.conf, shared_preload_libraries directly, or COPY ... TO/FROM server-side files.
• No SSH/OS access. Logs are only available via the RDS console, API, or CloudWatch Logs.
• Not all extensions. List of available ones: SELECT * FROM pg_available_extensions;. Several extensions (pg_cron, pg_stat_statements, pgaudit, pg_hint_plan) must be added to shared_preload_libraries in the parameter group, followed by an instance reboot.
• VACUUM/ANALYZE on other owners’ tables. Only the table owner (or a role with the MAINTAIN privilege in PG17+) can run them. rds_superuser is not automatically the owner of everything. To maintain tables with different owners, grant rds_superuser membership in the owner roles: GRANT owner_role TO rds_admin_user;

Parameter groups#

• All postgresql.conf parameters are changed through a DB parameter group. The default group cannot be modified — create your own and attach it to the instance.
• Parameters are either dynamic (applied immediately) or static (require an instance reboot, the console shows pending-reboot). autovacuum_max_workers, shared_buffers, shared_preload_libraries, max_connections are static.
• Values can be set as formulas based on instance size, e.g. {DBInstanceClassMemory/32768}. This is how RDS itself sets shared_buffers (≈25% of RAM) and max_connections.
• Verify what was applied: the same pg_settings query (section 2). The source column shows configuration file for parameters from the parameter group.

RDS autovacuum defaults#

RDS changes several PostgreSQL defaults:

The recommendations from section 16 apply fully: lower scale_factor for large tables via ALTER TABLE, raise autovacuum_vacuum_cost_limit to 1000–2000 in the parameter group, autovacuum_work_mem to 512MB–1GB.

Monitoring: CloudWatch#

RDS publishes metrics to CloudWatch automatically. Key ones for maintenance:

Enable Enhanced Monitoring (OS metrics at up to 1 s granularity, shows processes including autovacuum workers) and Performance Insights (DB load by wait events and top queries, 7 days of history free). Performance Insights effectively replaces manual pg_stat_statements analysis, but the extension itself is still worth having for details.

Logs#

• Export PostgreSQL logs to CloudWatch Logs (instance settings → Log exports). There you can create Metric Filters: alerts on PANIC, canceling autovacuum task, too many connections, remaining connection slots.
• Logging parameters (section 15) are set via the parameter group. log_line_prefix in RDS is fixed and cannot be changed (it already includes time, pid, user, database, app name).
• For pgBadger, download logs via the console/CLI (aws rds download-db-log-file-portion) or from CloudWatch Logs.

Maintenance#

• pg_repack is available as an extension: CREATE EXTENSION pg_repack;, the pg_repack client runs from your machine with the -k flag (since there is no superuser). The client version must match the extension version on the server.
• pgstattuple is available.
• pg_cron is available (PG12.5+): you can schedule VACUUM/ANALYZE/REINDEX CONCURRENTLY inside the database. Requires pg_cron in shared_preload_libraries and the cron.database_name parameter.
• pg_partman is available (PG12.5+): CREATE EXTENSION pg_partman; and schedule CALL partman.run_maintenance_proc(); with pg_cron (section 18). It creates future partitions and drops or detaches old ones by retention.
• Maintenance window — a weekly window in which AWS applies minor updates (if auto minor version upgrade is enabled) and changes that require a reboot. Choose the least busy time. For Multi-AZ, updates go through a failover (tens of seconds of downtime).
• Major upgrades via console/CLI use pg_upgrade. Up to PG17 planner statistics are not carried over — after the upgrade, ANALYZE all databases (RDS does not do this automatically). With a PG18+ target, pg_upgrade preserves basic statistics, but AWS still recommends running ANALYZE afterwards. Before upgrading: commit or roll back prepared transactions, handle logical replication slots (on source versions before 17 they must be dropped or the upgrade fails, on 17+ slots on the primary are retained, slots on read replicas are not), check for invalid indexes, take a snapshot.
• Snapshots are volume-level backups. Complement them with logical backups (pg_dump) of critical tables if selective restore is needed. Enable automated backups with a 7–35 day retention — this gives you PITR (point-in-time recovery).
• Storage autoscaling — worth enabling as a safeguard against storage-full, but not instead of monitoring: volume growth happens at most once every 6 hours and does not save you from rapid WAL growth due to an abandoned slot.
• Logical replication / CDC (Debezium, DMS) requires rds.logical_replication = 1 (static parameter, reboot). After that, slots are the most common cause of a full disk on RDS: the connector was stopped, the slot remained. Monitoring OldestReplicationSlotLag is mandatory.
• Read replicas have hot_standby_feedback set to off by default — long reports on the replica may be cancelled (conflict with recovery). If you enable it, long queries on the replica will block vacuum on the primary (section 6.4). Compromise: max_standby_streaming_delay on the replica.

19.3. Amazon Aurora PostgreSQL#

• Autovacuum and wraparound work the same way, all queries from sections 2–6 apply.
• Storage is shared across all instances in the cluster and grows automatically, there is no FreeStorageSpace, use VolumeBytesUsed instead. Vacuum likewise does not return space, but pg_repack/VACUUM FULL do reduce VolumeBytesUsed.
• There is no WAL archiving or TransactionLogsDiskUsage in the usual sense, use AuroraReplicaLag for replicas.
• There are no checkpoints in the classic sense (section 14 does not apply).
• shared_buffers defaults to ≈75% of RAM, since there is no separate OS cache.

19.4. Google Cloud SQL for PostgreSQL#

Roles and parameters#

• cloudsqlsuperuser is the admin role, the default postgres user is a member. Only members can create extensions.
• Flags are set per instance: gcloud sql instances patch INSTANCE --database-flags=a=1,b=2. 🔴 The list replaces all previously set flags, always pass the full set (Terraform database_flags behaves the same). Flags not in the documented list are not supported.
• There is no shared_preload_libraries flag. Preload extensions are switched on with cloudsql.enable_pg_cron, cloudsql.enable_auto_explain, cloudsql.enable_pg_hint_plan, cloudsql.enable_pgaudit, cloudsql.enable_pglogical, cloudsql.enable_pg_squeeze. All of them restart the instance. pg_stat_statements is preloaded, just CREATE EXTENSION.
• Logical replication / CDC: cloudsql.logical_decoding = on (restart).
• log_line_prefix is configurable (default %m [%p]: [%l-1] db=%d,user=%u), log_autovacuum_min_duration defaults to 0, so every autovacuum run is already in the log. max_connections default depends on the memory of the largest instance in the replication chain.

Monitoring: Cloud Monitoring#

Metric names are under cloudsql.googleapis.com/:

Query Insights is the built-in pg_stat_statements UI with wait events and plans. System Insights gives the OS view. Logs land in Cloud Logging automatically (postgres.log), build log-based alerts there for PANIC, canceling autovacuum task, remaining connection slots. For pgBadger pull logs with gcloud logging read and convert, the format is not a plain file.

Maintenance#

• pg_cron needs cloudsql.enable_pg_cron = on, jobs run as background workers only (no libpq mode), size max_worker_processes accordingly.
• pg_partman ships without its background worker, schedule partman.run_maintenance_proc() with pg_cron.
• pg_repack needs the cloudsqlsuperuser to be granted the table owner role for the duration (GRANT owner TO postgres, repack, REVOKE). Long runs may drop with SSL SYSCALL error: EOF detected, shorten TCP keepalives on the client.
• Automatic storage increase can be enabled, storage never shrinks back. It does not save you from an abandoned slot filling the disk faster than it can grow.
• Read replicas: hot_standby_feedback is a regular flag, same trade-off as section 6.4.
• Major upgrades are in-place (pg_upgrade), take a backup first, run ANALYZE after.

19.5. Google AlloyDB for PostgreSQL#

• Same model as Cloud SQL: alloydbsuperuser admin role, database flags, preload extensions via alloydb.enable_pg_cron, alloydb.enable_pgaudit, alloydb.enable_auto_explain, alloydb.enable_pg_hint_plan, alloydb.enable_pglogical (restart), logical decoding via alloydb.logical_decoding.
• Adaptive autovacuum (enable_google_adaptive_autovacuum, on by default): AlloyDB adjusts cost limit, delay, worker count and vacuum memory to the current workload, throttles XID consumption when wraparound approaches, and logs warnings about blockers (long transactions, orphan prepared transactions, orphan slots) to the postgres log. Standard autovacuum flags still work and are taken into account. Sections 2–6 apply unchanged, but the first thing to check when autovacuum looks odd is whether someone turned the flag off.
• shared_buffers defaults to 80% of RAM (the storage layer, not the OS cache, serves the rest), max_connections to 1000, log_autovacuum_min_duration to 0. Changing shared_buffers, max_connections, autovacuum_max_workers, autovacuum_worker_slots or autovacuum_freeze_max_age restarts the instance.
• Storage is cluster-wide and grows automatically, watch the cluster storage metrics in Cloud Monitoring rather than a disk-free number. Vacuum does not return space, pg_repack does.
• Read pools instead of read replicas: max_connections must be set on read pool instances before the primary and be greater or equal.
• Query Insights, System Insights and Cloud Logging work the same as on Cloud SQL.

19.6. Azure Database for PostgreSQL Flexible Server#

Roles and parameters#

• azure_pg_admin is the admin role. pg_repack runs only from it (otherwise permission denied for schema repack), pg_cron jobs run as the scheduling user, which should hold azure_pg_admin.
• Server parameters are set in the portal, CLI or Terraform. Extensions must be allowlisted in azure.extensions before CREATE EXTENSION, modules (auto_explain, pgaudit, pg_cron, pg_hint_plan, pg_partman_bgw, pglogical, wal2json) additionally go into shared_preload_libraries (restart). pg_stat_statements is already preloaded, it still needs the allowlist and CREATE EXTENSION.
• pgstattuple cannot read pg_toast on PG11–13. On PG14–15 grant pg_read_all_data to azure_pg_admin, on PG16+ it is granted automatically.
• maintenance_work_mem can go up to 2 GB. The role pg_signal_autovacuum_worker (PG15+ on Azure) lets a non-admin terminate an autovacuum worker that blocks a DDL.
• Logical replication: wal_level = logical (restart). Built-in PgBouncer: pgbouncer.enabled = on, port 6432, not on the Burstable tier.
• Query Store (pg_qs.query_capture_mode = top) replaces manual pg_stat_statements reading and adds wait sampling (pgms_wait_sampling.query_capture_mode = on). Azure Advisor raises recommendations for high bloat ratio and for XID age above 1 B.

Monitoring: Azure Monitor metrics#

Enhanced and autovacuum metrics are off by default, both switches are dynamic. The portal also has two troubleshooting guides, “Autovacuum monitoring” and “Autovacuum blockers and wraparound”, that run the section 4–6 checks for you.

Logs#

• Diagnostic settings stream log categories to Log Analytics, Storage or Event Hub. Useful beyond the server log: PostgreSQLFlexTableStats (per-table autovacuum stats every 30 min), PostgreSQLFlexDatabaseXacts (XID and multixact age per database with thresholds), PostgreSQLFlexSessions (snapshot of pg_stat_activity every 5 min).
• Server logs (download as files, 1–7 days retention) are what you feed to pgBadger.

Maintenance#

• Storage autogrow is available, storage never shrinks. Same caveat as other providers about slots.
• In-place major version upgrade exists but refuses servers with some extensions (timescaledb, postgres_fdw, dblink, anon, age, orafce on some versions). Drop them, upgrade, recreate. Run ANALYZE after.
• Maintenance window is weekly and configurable, HA servers upgrade through a failover.