Managing VACUUM on Heroku Postgres

Last updated April 24, 2026

Postgres uses a mechanism called Multi Version Concurrency Control (MVCC) to track changes in your database. As a side effect, some rows become “dead” and are no longer visible to any running transaction. DELETE operations, UPDATE operations, and transactions that you must roll back all generate dead rows.

“Bloat” is the dead rows that take up space on disk. Your database needs periodic maintenance to clean out these dead rows and reduce bloat. This maintenance is essentially a form of garbage collection. Typically, this maintenance happens automatically, but you can understand the details and tune the maintenance settings as needed.

Vacuuming a Database

VACUUM is the built-in mechanism for managing dead tuples cleanup. You can run VACUUM as a regular command, but Postgres also runs the VACUUM process automatically in the background as a maintenance task, periodically cleaning out old data. The autovacuum process performs its maintenance based on a set of configuration parameters.

The default Heroku autovacuum configuration is enough for many apps, but in some situations, you must make some changes or occasionally take manual action.

Determining Bloat

To check whether you must vacuum, check bloat with the heroku pg:bloat command:

$ heroku pg:bloat DATABASE_URL --app example-app
 type  | schemaname |    object_name          | bloat |   waste
-------+------------+-------------------------+-------+-----------
 table | public     | users                   |   1.0 | 109 MB
 table | public     | logs                    |   1.0 | 47 MB
 index | public     | queue_classic_jobs_pkey |   3.1 | 25 MB
 table | public     | reviews                 |   2.2 | 16 MB
 table | public     | queue_classic_jobs      |  32.5 | 1512 kB
...

The bloat column shows the bloat factor, which is the fraction of the original table that exists as bloat. Because it’s a ratio, there are no units. The waste column shows the total bloat in bytes in each table and index in the system.

A large bloat factor on a table or index can lead to poor performance for some queries, as Postgres plans them without considering the bloat.

The threshold for excessive bloat varies according to your query patterns and the size of the table. Generally, results with a bloat factor over 10 are worth looking into, especially on tables over 100 MB.

To check on vacuuming in your database, use the heroku pg:vacuum-stats command:

$ heroku pg:vacuum-stats DATABASE_URL --app example-app
 schema |         table      | last_vacuum | last_autovacuum  |    rowcount    | dead_rowcount  | autovacuum_threshold | expect_autovacuum
--------+--------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
 public | queue_classic_jobs |             | 2026-04-20 16:54 |         82,617 |         36,056 |         16,573       | yes
 public | logs               |             | 2026-04-20 16:27 |              1 |             18 |             50       |
 public | reviews            |             | 2026-04-20 01:36 |             87 |              0 |             67       |
 public | users              |             | 2026-04-20 16:28 |              0 |             23 |             50       |
...

The results report when each table was last vacuumed, and whether that was through a manual action or the autovacuum background worker. The results also show the threshold number of dead rows that triggers an autovacuum for that particular table, and whether you can expect an autovacuum to occur.

VACUUM Variants

Postgres provides two main variants of VACUUM with different trade-offs between thoroughness and performance impact.

Regular VACUUM marks dead row space as available for reuse without blocking other operations. The autovacuum process uses this variant automatically. You can also run VACUUM manually on specific tables instead of the complete database with VACUUM table_name;.

VACUUM FULL provides a more exhaustive cleanup that physically reclaims disk space rather than just marking it as reusable. However, VACUUM FULL is a heavyweight database operation that rewrites the entire table and blocks all other operations on it, including SELECT queries. Configure autovacuum’s settings to be aggressive enough to prevent needing VACUUM FULL.

For tables that track only transient data, such as a work queue, you can delete all the table data and clean up bloat with the TRUNCATE command. This command deletes all the data in the table in a batch operation and reclaims disk space immediately. For very bloated tables, TRUNCATE is faster than combining DELETE and VACUUM FULL.

Automatic Vacuuming with autovacuum

Autovacuum automatically runs VACUUM on tables when they accumulate enough dead rows or inserts. You can tune when the autovacuum triggers and how aggressively it runs to prevent bloat from building up.

Configuring Autovacuum Thresholds

Control when autovacuum runs on a table by adjusting its threshold settings. On Heroku Postgres, you must adjust these settings on a per-table basis.

Setting Description Default
autovacuum_vacuum_threshold Specifies the minimum number of updated or deleted rows (tuples) required to trigger a VACUUM in any one table. 50 tuples
autovacuum_vacuum_scale_factor Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger autovacuum. 0.2 (20% of the table size)
autovacuum_vacuum_insert_threshold Specifies the minimum number of inserted tuples needed to trigger a VACUUM in any one table. If you set the value as -1, autovacuum doesn’t trigger a VACUUM operation on any tables based on the number of inserts. 1000 tuples
autovacuum_vacuum_insert_scale_factor Specifies a fraction of the unfrozen pages in the table to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger autovacuum. 0.2 (20% of unfrozen pages in the table)

Based on these settings, Postgres triggers autovacuum when the database exceeds either the dead row threshold or the insert threshold.

Together, the settings make up the actual threshold and insert threshold according to the following formulas:

vacuum threshold = autovacuum_vacuum_threshold +
    autovacuum_vacuum_scale_factor * number of rows

vacuum insert threshold = autovacuum_vacuum_insert_threshold +
    autovacuum_vacuum_insert_scale_factor * number of inserts

Connect to your database to adjust these per-table settings:

$ heroku pg:psql -a example-app
=> ALTER TABLE users SET (autovacuum_vacuum_threshold = 30);
ALTER TABLE
=> ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.15);
ALTER TABLE
=> ALTER TABLE users SET (autovacuum_vacuum_insert_threshold = 500);
ALTER TABLE
=> ALTER TABLE users SET (autovacuum_vacuum_insert_scale_factor = 0.15);
ALTER TABLE

On large tables, decrease the scale factor to allow vacuum to start progressing earlier. For small tables, increase the threshold, though increasing is typically not necessary.

Configuring Autovacuum Cost Settings

Autovacuum has a built-in cost-based rate-limiting mechanism to avoid overwhelming the system with VACUUM activity. In busy databases, this mechanism can prevent autovacuum from progressing quickly enough, leading to excessive bloat.

Adjust the vacuum cost settings at the database level to make autovacuum more aggressive.

Setting Description Default
autovacuum_vacuum_cost_delay Specifies the cost delay value that the database uses in autovacuum operations. If you specify the value as -1, the database uses the regular vacuum_cost_delay. If you specify this value without units, it’s taken as milliseconds. 2 milliseconds
vacuum_cost_delay Specifies the amount of time that the process sleeps when the database exceeds the cost limit. If you specify this value without units, it’s taken as milliseconds. 0 (disables the cost-based vacuum delay feature)
autovacuum_vacuum_cost_limit Specifies the cost limit value that the database uses in automatic VACUUM operations. If you specify default value, the database uses the regular vacuum_cost_limit. -1
vacuum_cost_limit Specifies the accumulated cost that causes the vacuuming process to sleep for vacuum_cost_delay. 200
vacuum_cost_page_dirty Specifies the estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. 20
vacuum_cost_page_miss Specifies the estimated cost for vacuuming a buffer that the database must read from disk. It represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk, and scan its content. 2
vacuum_cost_page_hit Specifies the estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table, and scan the content of the page. 1

Connect to your database to check and adjust these settings:

$ heroku pg:psql -a example-app
=> select current_database();
 current_database
------------------
 dd5ir2j6frrtr0
(1 row)

=> SHOW autovacuum_vacuum_cost_limit;
 autovacuum_vacuum_cost_limit
------------------------------
 -1
(1 row)

=> SHOW autovacuum_vacuum_cost_delay;
 autovacuum_vacuum_cost_delay
------------------------------
 20ms
(1 row)


=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_limit = 300;
ALTER DATABASE
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_page_dirty = 25;
ALTER DATABASE
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_page_miss = 7;
ALTER DATABASE
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_page_hit = 0;
ALTER DATABASE

The cost limit determines how much “cost”, in terms of I/O operations, autovacuum can accrue before it must take a break. The cost delay determines how long that break is in milliseconds. You can configure these cost-specific settings for both autovacuum and manual vacuum, or independently. Increasing the cost limit or adjusting the vacuum_cost_page_* parameters can help the autovacuum progress more efficiently.

Manual Vacuuming

Besides relying on the automatic vacuum background processes running on your database, you can run VACUUM manually when you need more control over timing or want to ensure cleanup happens at specific times.

If your database has a periodic workload, use a simple worker process to “manually” run a VACUUM, and trigger it with a tool like Heroku Scheduler during off-peak hours. You can also use VACUUM FULL if locking isn’t an issue.

You can also configure manual VACUUM to use a cost-based back-off mechanism like autovacuum. In Heroku Postgres, the default is off where the vacuum_cost_delay is set to 0. Increase this value on a per-table basis if manual VACUUM impacts your regular workload.

To run VACUUM manually, open a connection to your database and run the VACUUM command with any of its options.

$ heroku pg:psql -a example-app
=> VACUUM;
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_db_role_setting" --- only superuser can vacuum it
VACUUM

The warnings you see are expected and you can ignore them.

VACUUM runs for all the tables and materialized views in your database. You can run VACUUM on a particular table with VACUUM <table_name>;:

$ heroku pg:psql -a example-app
=> VACUUM users;
VACUUM

VACUUM Options

VACUUM supports several options that modify its behavior. Its most commonly used options are:

Option Description
VERBOSE Provides detailed progress information, including statistics on dead rows removed and disk space reclaimed.
ANALYZE Updates table statistics that the query planner uses. This option helps Postgres select optimal query plans.
FULL Performs a more aggressive cleanup that compacts the table, but requires an exclusive lock that blocks all operations.

You can combine these options. For example, VACUUM (VERBOSE, ANALYZE) table_name cleans the table, updates statistics, and shows detailed progress information.

$ heroku pg:psql
=> VACUUM (VERBOSE, ANALYZE) books;
INFO:  vacuuming "d9fm5ro65cprs0.public.books"
INFO:  finished vacuuming "d9fm5ro65cprs0.public.books": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 28174, which was 0 XIDs old when operation ended
new relfrozenxid: 28174, which is 48 XIDs ahead of previous value
…
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 6 hits, 0 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  analyzing "public.books"
INFO:  "books": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM

With carefully managed autovacuum settings, manual vacuuming is rarely necessary, but understanding these options helps when you must intervene.