What are PostgreSQL Vacuum and Analyze Processes?

PostgreSQL VACUUM and ANALYZE are two important maintenance processes that ensure the optimal performance of your database. VACUUM cleans up obsolete row versions and makes their storage space reusable, while ANALYZE collects statistics that the Query Planner needs for optimal query strategies. Autovacuum automatically starts both processes in the background by default. However, they can also be executed manually to specifically improve database performance.

What is the PostgreSQL VACUUM process and why is it important?

The PostgreSQL VACUUM process removes obsolete row versions and makes their storage space reusable for new data. PostgreSQL uses Multi-Version Concurrency Control (MVCC): Deleted or modified rows are not immediately physically removed but remain as old row versions in the file as long as a running transaction could still see them. Only after that are they considered “dead tuples” that VACUUM can clean up.

VACUUM performs four central tasks:

• It removes dead tuples from tables and indexes and enters the freed space into the Free Space Map.
• It updates the Visibility Map, which enables more efficient Index-Only Scans.
• It freezes old rows (Freezing), thus preventing a Transaction ID Wraparound.
• It updates the estimated row count of the table.

A normal VACUUM typically does not return storage space to the operating system – only completely empty pages at the end of the table are truncated. Without regular VACUUM, your tables would still continuously grow (“bloat”) because the space of dead tuples would not be reused.

PostgreSQL distinguishes between automatic Autovacuum, which runs in the background, and manual VACUUM, which you can execute specifically. The Autovacuum system monitors your tables and starts VACUUM as soon as the number of changed or deleted rows exceeds a threshold – by default 50 rows plus 20% of the table (autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor). Since PostgreSQL 13, pure inserts also trigger a VACUUM. Autovacuum also starts ANALYZE according to the same principle, by default from 50 rows plus 10% changed rows.

How does the PostgreSQL ANALYZE process work?

The ANALYZE process collects statistical information about the data distribution in your tables and stores it in the system catalog pg_statistic. These statistics are crucial for the Query Planner to create optimal execution plans for your SQL queries.

For each column, ANALYZE collects, among other things, the proportion of NULL values, the number of distinct values, the most common values with their frequency, histograms for data distribution, and the correlation between physical storage order and value order. ANALYZE does not collect dependencies between multiple columns by default – for this, there are extended statistics (CREATE STATISTICS). With this information, the Query Planner estimates the number of result rows and, based on that, chooses, for example, between Index Scan and Sequential Scan, between join methods and the join order.

The process uses samples to generate meaningful statistics quickly, even for large tables: ANALYZE reads 300 × default_statistics_target rows, so with the default value of 100, it reads 30,000 rows per table. You can increase this value globally or per ALTER TABLE … ALTER COLUMN … SET STATISTICS for individual columns. Higher values lead to more accurate statistics but prolong ANALYZE and the planning time of queries.

When should you manually run VACUUM and ANALYZE?

Manual VACUUM and ANALYZE operations are useful after large data changes or in case of performance problems. Although Autovacuum runs automatically, there are situations where manual interventions significantly improve performance.

Execute a manual VACUUM after mass deletions or updates affecting large parts of a table, or after loading large amounts of data. The fact that a table does not shrink after deletion is normal: VACUUM makes the space reusable but does not return it to the operating system. Only VACUUM FULL, which completely rewrites the table, achieves this. Caution: VACUUM FULL exclusively locks the table for the entire duration, even for read access, and temporarily requires additional storage space the size of the new table. In live operation, a tool like pg_repack is usually the better choice.

You should manually start ANALYZE after importing new data, after structural changes to tables, after a restore with pg_restore, and – before PostgreSQL 18 – after an upgrade with pg_upgrade. Partitioned tables also require a manual ANALYZE: Autovacuum only analyzes the individual partitions, not the parent table.

Normal VACUUM and ANALYZE do not block read or write access but consume I/O and CPU. Therefore, ideally plan larger maintenance work during periods of low database activity. Typical commands are:

-- Tabelle aufräumen und Statistiken aktualisieren
VACUUM (VERBOSE, ANALYZE) bestellungen;

-- Nur Statistiken aktualisieren
ANALYZE bestellungen;

-- Statistikziel für eine einzelne Spalte erhöhen
ALTER TABLE bestellungen ALTER COLUMN kunden_id SET STATISTICS 500;
ANALYZE bestellungen;

For entire databases or clusters, the command-line tool vacuumdb is suitable, which can process multiple tables in parallel:

vacuumdb --all --analyze-in-stages --jobs=4

Which parameters influence VACUUM and ANALYZE performance?

Various PostgreSQL parameters control the efficiency and resource utilization of VACUUM and ANALYZE processes. Correct configuration can significantly reduce maintenance time and minimize the impact on ongoing operations.

Important VACUUM parameters include: maintenance_work_mem determines the available working memory for maintenance operations, autovacuum_work_mem sets it specifically for Autovacuum. Note that each Autovacuum worker can occupy this memory. Since PostgreSQL 17, VACUUM manages the list of dead tuples much more compactly, and the previous upper limit of 1 GB is no longer applicable. With vacuum_cost_delay and vacuum_cost_limit, VACUUM can be throttled to limit the I/O load. The default value of vacuum_cost_delay is 0, so a manual VACUUM runs unthrottled. For Autovacuum, autovacuum_vacuum_cost_delay (default: 2 ms) and autovacuum_vacuum_cost_limit apply.

For Autovacuum, the following are also relevant: autovacuum_max_workers (default: 3) limits the number of parallel workers, autovacuum_naptime determines how often Autovacuum checks the databases. Especially for large tables, a scale factor of 20% is too slow: For 100 million rows, VACUUM only starts after 20 million changed rows. Since PostgreSQL 18, autovacuum_vacuum_max_threshold limits this value. Additionally, you can adjust the thresholds for individual tables:

ALTER TABLE bestellungen SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

For ANALYZE, default_statistics_target is particularly relevant, supplemented by column-specific statistics targets and the thresholds autovacuum_analyze_threshold and autovacuum_analyze_scale_factor. random_page_cost, however, does not influence ANALYZE itself, but the planner’s cost estimation. For SSD storage, a value around 1.1 is usually recommended instead of the default value of 4. You should adjust these parameters based on your hardware and workload.

What prevents VACUUM from removing dead tuples?

VACUUM may only remove an old row version when it is no longer visible to any transaction. The oldest active transaction therefore determines how far VACUUM can clean up. If a transaction runs for hours somewhere, all dead tuples created thereafter remain in the entire database. This applies even if Autovacuum runs regularly. In practice, this is the most common cause of heavily bloated tables.

Typical causes include:

• Long-running queries and transactions, such as reports, batch jobs, or backups with pg_dump
• Sessions in “idle in transaction” state: An application opens a transaction and never closes it, for example, after an error or due to a misconfigured connection pool
• Orphaned replication slots: A logical replication slot whose recipient no longer exists permanently holds old row versions and WAL files
• Forgotten Prepared Transactions (two-phase commit)
• hot_standby_feedback on replicas: Long queries on a standby server then block VACUUM on the primary server

You can find the oldest open transactions with this query:

SELECT pid, usename, state,
       now() - xact_start AS dauer,
       age(backend_xmin) AS xmin_alter,
       left(query, 60) AS abfrage
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

You can check replication slots and prepared transactions as follows:

SELECT slot_name, slot_type, active,
       age(xmin) AS xmin_alter,
       age(catalog_xmin) AS catalog_xmin_alter
FROM pg_replication_slots;

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts;

VACUUM VERBOSE also indicates the problem: If the number of rows under “dead but not yet removable” is high, VACUUM is slowed down.

You can prevent this with these parameters:

• idle_in_transaction_session_timeout terminates sessions that remain idle in an open transaction for too long.
• transaction_timeout limits the total duration of a transaction from PostgreSQL 17.
• max_slot_wal_keep_size and, from PostgreSQL 18, idle_replication_slot_timeout prevent orphaned replication slots from permanently blocking storage space and VACUUM.

How do you monitor VACUUM and ANALYZE?

Autovacuum works unobtrusively in the background, and therefore problems often only become apparent when queries slow down or storage space runs out. A few key metrics in monitoring show negative developments early on.

Dead tuples and last maintenance runs: The view pg_stat_user_tables shows the estimated number of dead tuples per table. You can also see when VACUUM and ANALYZE last ran and how many rows have changed since the last ANALYZE:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS tot_prozent,
       last_autovacuum, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A persistently high proportion of dead tuples or a long-past last_autovacuum for a heavily used table indicates one of two problems: The thresholds are too high for the table, or VACUUM is being slowed down.

Running processes: How far a running VACUUM has progressed is shown by pg_stat_progress_vacuum. For ANALYZE, since PostgreSQL 13, there is the view pg_stat_progress_analyze:

SELECT pid, relid::regclass AS tabelle, phase,
       heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

Logging: With log_autovacuum_min_duration, PostgreSQL writes every Autovacuum run that lasts longer than the specified value to the log. Since PostgreSQL 15, the default value is ten minutes. With a value of, for example, one second, you get a much more accurate picture of which tables are processed how often and for how long.

Transaction ID Wraparound: PostgreSQL assigns 32-bit transaction IDs. After about two billion transactions, old rows must be frozen, otherwise the database will no longer accept write transactions to protect against data loss. If a table’s age exceeds autovacuum_freeze_max_age (default: 200 million), Autovacuum starts a forced VACUUM. This happens even if Autovacuum is disabled for the table. If the value continues to rise, monitoring should trigger an alarm:

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

You can find the affected tables within a database with:

SELECT c.oid::regclass AS tabelle, age(c.relfrozenxid) AS xid_alter
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY xid_alter DESC
LIMIT 10;

These metrics can be integrated into common monitoring solutions, such as Prometheus with postgres_exporter, pgwatch, or check_postgres. Useful alarms are:

• Transactions that are open longer than a defined time
• inactive replication slots
• a transaction ID age significantly exceeding autovacuum_freeze_max_age
• tables with a persistently high proportion of dead tuples

How credativ® helps optimize PostgreSQL VACUUM and ANALYZE

We support you in the optimal configuration and monitoring of your PostgreSQL VACUUM and ANALYZE processes. Our 24/7 support team consists of experienced PostgreSQL specialists who continuously optimize your database performance.

Our services include:

• Performance analysis and optimization of VACUUM/ANALYZE parameters
• Monitoring and alerting for maintenance processes
• Individual consulting for your specific requirements
• Proactive maintenance planning and automation
• 24/7 technical support in critical situations

Contact us for a free initial consultation on your PostgreSQL performance optimization. Our experts from the PostgreSQL Competence Center will work with you to develop a customized maintenance strategy that sustainably improves your database performance.

About the author

Peter Dreuw

Head of Sales & Marketing

about the person

Peter Dreuw has been working for credativ GmbH since 2016 and has been a team lead since 2017. Since 2021, he has been part of Instaclustr’s management team as VP Services. Following the acquisition by NetApp, his new role became “Senior Manager Open Source Professional Services”. As part of the spin-off, he became a member of the executive management as an authorized signatory. His responsibilities include leading sales and marketing. He has been a Linux user from the very beginning and has been running Linux systems since kernel 0.97. Despite extensive experience in operations, he is a passionate software developer and is also well versed in hardware-near systems.

View posts