Routine Maintenance: VACUUM, ANALYZE, pg_dump, and Monitoring

Why Routine Maintenance Matters

PostgreSQL does not silently clean up after itself the way some database engines do. Two core maintenance operations — VACUUM and ANALYZE — must run regularly, either manually or through the built-in autovacuum daemon. Understanding what each one does, how to tune it, and how to monitor the results is a foundational DBA skill that appears on the EDB PostgreSQL CE and Oracle DBA CA exams alike.

Dead Tuples and VACUUM

When you UPDATE or DELETE a row in PostgreSQL, the old row version is not immediately removed. It is marked as dead and left in place until VACUUM reclaims the space. These dead tuples waste disk space, bloat indexes, and slow sequential scans.

`VACUUM` alone reclaims dead tuple space and marks pages as reusable. `VACUUM VERBOSE` prints a detailed report showing how many dead tuples were removed per page, which is useful when you are diagnosing bloat. `VACUUM ANALYZE` does both the cleanup pass and a statistics update in one command — usually the better choice for routine maintenance.

The system view pg_stat_user_tables tracks the column `n_dead_tup` (dead tuple count) per table. Check it before and after a VACUUM run to confirm the operation actually reclaimed tuples.

Autovacuum Tuning

The autovacuum daemon fires automatically when dead tuples exceed a threshold:

> threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

For a large table, the default scale factor of 0.2 (20 % of rows) means autovacuum may wait until millions of dead tuples accumulate. Lower the scale factor for high-churn tables. You can override server-wide settings at the individual table level with `ALTER TABLE`:

```sql

ALTER TABLE orders

SET (autovacuum_vacuum_scale_factor = 0.05,

autovacuum_vacuum_cost_delay = 2);

```

`autovacuum_vacuum_cost_delay` (in milliseconds) throttles how aggressively autovacuum uses I/O. A lower value lets it run faster; a higher value protects OLTP workloads from I/O contention.

ANALYZE and the Query Planner

`ANALYZE` collects column-level statistics — value distributions, most common values, null fractions — and stores them in `pg_statistic`. The query planner uses these statistics to choose join strategies and index paths. Stale statistics after a large bulk load are a common cause of suddenly poor query plans. Running `VACUUM ANALYZE` after any significant data change is good practice.

Logical Backups: pg_dump and pg_restore

`pg_dump` exports a single database into a self-contained backup. It is a logical backup — it captures SQL-level objects (tables, indexes, sequences, constraints), not raw disk blocks. Three commonly used output formats are:

| Format flag | Description | Restore tool |

|---|---|---|

| (none / plain) | Plain SQL script | psql |

| `-Fc` (custom) | Compressed, selective restore | pg_restore |

| `-Fd` (directory) | One file per object, parallelizable | pg_restore |

Custom format is the most flexible for production use: it supports parallel restore (`-j` flag), selective object restore, and reordering. Directory format adds the benefit of parallel dump (`-j` on pg_dump as well).

To back up and then restore:

```bash

pg_dump -Fc -d inventory_db -f inventory_db.dump

createdb inventory_restore

pg_restore -Fc -d inventory_restore inventory_db.dump

```

Always verify restores. Use `\dt`, `\di`, or count objects in `information_schema.tables` to confirm row and object counts match.

Server Health Monitoring with System Views

PostgreSQL exposes rich runtime statistics through the `pg_stat_*` family of views. Four views every DBA should know:

• pg_stat_activity — one row per server process; columns include `state` (active, idle, idle in transaction), `wait_event_type`, `query`, and `query_start`. Sessions stuck in `idle in transaction` hold locks and prevent autovacuum from cleaning those rows.
• pg_stat_bgwriter — tracks checkpoint frequency, buffers written, and clean/dirty ratios. High `buffers_backend` values suggest checkpoints are not keeping up.
• pg_stat_database — per-database aggregates: commits, rollbacks, cache hit ratio (`blks_hit / (blks_hit + blks_read)`), conflicts, and deadlocks.
• pg_locks — shows currently held and awaited locks. Join with `pg_stat_activity` on `pid` to tie lock waits back to specific queries.

To catch slow queries, enable `log_min_duration_statement` in `postgresql.conf` (for example, set it to `1000` for queries longer than 1 second). This is PostgreSQL's equivalent of MySQL's slow-query log; note that the parameter is `log_min_duration_statement`, not `long_query_time` — that term belongs to MySQL.

Safety and Data Integrity Notes

• Never run `VACUUM FULL` on a production table during business hours without a maintenance window. `VACUUM FULL` rewrites the entire table and holds an `ACCESS EXCLUSIVE` lock, blocking all reads and writes.
• Always test a backup by performing a full restore to a separate database before you depend on it. An untested backup is not a backup.
• Keep backup files on storage separate from the PostgreSQL data directory. A disk failure that takes the data directory also takes any backups stored on the same volume.
• When working on a shared lab server, confirm with your instructor before running `pgbench` load tests — high connection counts affect every user on that instance.