PostgreSQL VACUUM and ANALYZE: maintain tables and check statistics

PostgreSQL VACUUM makes space occupied by obsolete row versions available for reuse. ANALYZE updates the statistics the query planner uses to estimate results. You can run them separately or combine them with VACUUM (ANALYZE). They solve different maintenance needs, so check the evidence for each.

This guide follows a 10,000-row test table through updates, deletes, analysis, vacuuming, and new inserts. The SQL ran on PostgreSQL 18.6 in a disposable local database. We measured row counts, stored column statistics, maintenance counters, and heap size. These observations show what the commands did in this fixture; they are not a production performance benchmark.

Understand what needs maintenance

PostgreSQL keeps row versions so transactions can see a consistent view of data. An update or delete can leave an older version behind. A version that another transaction may still need cannot simply be removed. The transaction isolation documentation explains those visibility rules.

Think of three separate questions: how much reusable space the table contains, how accurately the planner understands its data, and how much disk space its files occupy. One number cannot answer all three. A large file after successful vacuuming is not, by itself, evidence of failure.

Vacuum also maintains visibility information and freezes old transaction IDs to prevent wraparound. Low dead-row counts do not remove those maintenance needs.

Start from a workload symptom rather than a fixed maintenance recipe. Our pg_stat_statements guide helps identify expensive queries. After checking statistics, use EXPLAIN ANALYZE to investigate a selected query. This article concentrates on maintenance and its observable effects.

Set up a disposable database

Connect directly with psql to a new test database that you own. Keep autocommit on, do not wrap the exercise in BEGIN, and stop on errors. Confirm the database before running any setup or cleanup:

SELECT current_database(), current_user, version();
SET statement_timeout = '15s';
SET lock_timeout = '2s';
SHOW autovacuum;

The two timeouts limit this lab’s statements and lock waits; they are not recommended production values. Keep the same connection for the walkthrough. If you use a graphical SQL client, check whether it starts transactions automatically.

Stop if the schema vacuum_lab already exists. Create this fixture once:

CREATE SCHEMA vacuum_lab;
CREATE TABLE vacuum_lab.jobs (
    id integer PRIMARY KEY,
    state text NOT NULL,
    payload text NOT NULL
) WITH (autovacuum_enabled = false);

INSERT INTO vacuum_lab.jobs
SELECT i,
       CASE WHEN i % 5 = 0 THEN 'done' ELSE 'queued' END,
       repeat('x', 200)
FROM generate_series(1, 10000) AS g(i);

ANALYZE vacuum_lab.jobs;

The table contains 8,000 queued jobs and 2,000 done jobs. Every payload has the same width. Automatic maintenance is paused only for this disposable table so it cannot hide the effect of our manual steps. The server-wide autovacuum setting stays on. The cleanup restores the table’s default before removing it.

Do not carry that table setting into an application migration. PostgreSQL’s autovacuum settings describe automatic vacuuming and analysis; leave that protection enabled for normal workloads. A manual exercise is not a replacement for it.

Change the rows, then inspect the statistics

UPDATE vacuum_lab.jobs SET state = 'done' WHERE id <= 4000;
DELETE FROM vacuum_lab.jobs WHERE id % 2 = 0;

SELECT state, count(*)
FROM vacuum_lab.jobs
GROUP BY state ORDER BY state;

The update affects 4,000 rows; the delete removes the 5,000 even-numbered IDs. The remaining rows are 2,600 done and 2,400 queued. That is 52% done and 48% queued, a substantial change from the original distribution.

Now inspect the statistics for the state column:

SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'vacuum_lab'
  AND tablename = 'jobs'
  AND attname = 'state';

Before another ANALYZE, our query still returned {queued,done} with frequencies {0.8,0.2}. Those values describe the previous analysis, not a live count of the changed table. In pg_stats, the values and frequencies correspond by array position.

ANALYZE vacuum_lab.jobs;

Run the same statistics query again. Our result became {done,queued} and {0.52,0.48}, matching this small fixture. The ordering changed because done is now the more common value. Reading the frequency array without its value array would reverse the interpretation.

The separate ANALYZE command collects planner statistics; it is not the ANALYZE option that executes a query in EXPLAIN. Large-table statistics are generally sampled, so do not expect exact frequencies or identical estimates after every run. Accurate statistics support planning decisions but do not guarantee a particular plan or latency.

Record maintenance activity and heap size

SELECT n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relid = 'vacuum_lab.jobs'::regclass;

SELECT pg_relation_size('vacuum_lab.jobs') AS heap_bytes;

The first query shows estimated live and dead row counts and the most recent manual and automatic maintenance timestamps. Our fixture reported 5,000 live rows and 9,000 dead rows before vacuuming. The second query reported 3,481,600 bytes.

These metrics have different meanings. Table activity statistics are estimates and may lag; they are not an exact bloat measurement. A null timestamp is not a permanent history of the table. Statistics can be reset, and repeated reads inside a transaction can use cached values.

pg_relation_size measures the main relation fork by default. Our heap byte count excludes separate indexes, TOAST data, and auxiliary forks. Do not describe it as the total footprint of the table or database.

Vacuum the table and compare the results

VACUUM (ANALYZE, VERBOSE, TRUNCATE FALSE) vacuum_lab.jobs;

This runs ordinary VACUUM, includes analysis, and prints diagnostic output. TRUNCATE FALSE disables removal of empty pages at the file’s end so the demonstration isolates reuse inside the heap. It also avoids the exclusive lock needed for that truncation step. See the VACUUM options before adapting it.

Repeat the maintenance and size queries. In our run, the manual vacuum counter increased, the dead-row estimate fell to zero, and the heap stayed at 3,481,600 bytes. A separate comparison of every live row, ordered by ID, confirmed that vacuuming changed none of their values.

The unchanged file size was expected. Now insert 5,000 new rows of the same width:

INSERT INTO vacuum_lab.jobs
SELECT i, 'queued', repeat('x', 200)
FROM generate_series(10001, 15000) AS g(i);

SELECT count(*) AS live_rows,
       pg_relation_size('vacuum_lab.jobs') AS heap_bytes
FROM vacuum_lab.jobs;

The table returned to 10,000 live rows while the heap remained 3,481,600 bytes. The new rows fit in the available space after vacuuming. This is direct evidence of reuse in this fixture, not a promise that another workload, row width, index, or storage configuration will behave identically. The original heap was 2,490,368 bytes before the update/delete sequence; vacuuming did not shrink it back to that starting size.

Check transactions, locks, and permissions

Our negative test confirmed that VACUUM fails inside an explicit transaction block. If your client reports that error, finish the transaction appropriately and run maintenance outside it. Review warnings as well as command completion: PostgreSQL 18 requires the relevant maintenance privilege, and tables without sufficient permission can be skipped.

Ordinary vacuuming can coexist with normal reads and writes, but it still consumes resources and can conflict with other maintenance or schema changes. PostgreSQL’s lock documentation distinguishes those cases. Old snapshots can retain row versions, so repeatedly issuing VACUUM does not remove the need to investigate a long-running transaction.

VACUUM FULL rewrites the table, requires extra working space, and blocks other table use with an exclusive lock. It is not the next routine step simply because the heap file stayed large. The routine vacuuming guide explains that tradeoff. We did not run VACUUM FULL or test production lock behavior in this exercise.

Watch a running vacuum

For a longer operation, run this from a second connection to the same database:

SELECT pid, relid::regclass AS table_name, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum
WHERE datname = current_database();

pg_stat_progress_vacuum describes the current phase and heap-block progress of ordinary vacuuming. It does not show VACUUM FULL, which uses the cluster progress view. Do not treat the scanned-block ratio as a percentage of all remaining work; index and cleanup phases have their own work.

Our small vacuum had already finished when we queried the view, so it returned no rows. That validates the query, not live progress sampling. An empty result after completion is expected and is not evidence that vacuuming never ran.

Clean up and apply the observation carefully

ALTER TABLE vacuum_lab.jobs RESET (autovacuum_enabled);
DROP TABLE vacuum_lab.jobs;
DROP SCHEMA vacuum_lab;

Run this only in the disposable database. It restores the table option, drops the named table, and removes the now-empty schema without CASCADE. End the lab connection when finished.

The walkthrough passed 15 checks on PostgreSQL 18.6, including changed column statistics, preserved live data, space reuse, transaction rejection, and cleanup. The private server was stopped afterward. No production throughput, concurrent workload, cloud deployment, or long-snapshot behavior was tested.

For a real table, compare maintenance history, data changes, transaction activity, and query plans before changing settings. Keep autovacuum enabled and make a targeted change only when those observations justify it. Record the result using the same measures so you can tell whether the intended problem changed.