Autovacuum and the cost of maintenance you are not doing

Quick answer

Under-tuned autovacuum leaves dead tuples on disk, and because managed database storage autoscales up and never down, that bloat becomes a permanent charge. A 1 TB PostgreSQL database with 35 percent bloat is carrying about 350 GB of dead space at $0.115 per GB-month, roughly $40 per month or $483 a year, plus the knock-on cost of a larger buffer pool requirement and slower scans. The two settings that matter most are autovacuum_vacuum_cost_limit (default 200, often worth raising to 1000 to 2000 on modern storage) and per-table autovacuum_vacuum_scale_factor (default 0.2, worth lowering to 0.01 to 0.05 on large high-churn tables). Vacuum itself consumes I/O and CPU, so the cost is real on both sides.

PostgreSQL's MVCC design means an UPDATE does not modify a row, it writes a new version and marks the old one dead. DELETE marks rows dead without reclaiming space. Autovacuum reclaims that space for reuse. When autovacuum cannot keep up, dead tuples accumulate, the table grows, and on a managed service that growth is one-directional. This is a cost problem dressed as a performance topic.

What bloat costs

Live data	Bloat	Allocated	gp3 monthly	Waste
500 GB	10%	550 GB	$63.25	$5.75
500 GB	35%	675 GB	$77.63	$20.13
1 TB	35%	1,350 GB	$155.25	$40.25
1 TB	80%	1,800 GB	$207.00	$92.00
4 TB	40%	5,600 GB	$644.00	$184.00

The direct storage waste looks modest. The indirect cost is larger. Bloated tables and indexes occupy buffer pool space, so a database with 35 percent bloat needs roughly 35 percent more memory to hold the same working set. On the r6g family that is the difference between db.r6g.xlarge at $378 and db.r6g.2xlarge at $756. Sequential scans read the dead space too, so scan times rise proportionally, which on a reporting workload translates directly into CPU and I/O you are paying for.

There is also a failure mode with a much larger price. If autovacuum falls far enough behind on transaction ID wraparound prevention, PostgreSQL will eventually refuse writes to protect data integrity. That is a full outage, and the recovery is a single-threaded vacuum that can take many hours on a large table. The cost of that incident dwarfs every storage figure in the table above.

Why the defaults fail on large tables

Autovacuum triggers a table vacuum when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the table's row count. The defaults are 50 and 0.2, meaning a table needs 20 percent dead rows before vacuum starts.

Table rows	Dead rows before vacuum (default 0.2)	At scale_factor 0.02
100,000	20,050	2,050
10,000,000	2,000,050	200,050
500,000,000	100,000,050	10,000,050

On a 500 million row table, the default lets 100 million dead rows accumulate before autovacuum even starts, and by then the vacuum is a large, slow operation that may not finish before the next threshold is crossed. Setting autovacuum_vacuum_scale_factor to 0.02 on that table, via ALTER TABLE storage parameters rather than globally, makes vacuum run more often on much smaller amounts of work.

The throttle that stops vacuum from finishing

The other default that hurts is autovacuum_vacuum_cost_limit, which is 200 by default. Autovacuum accrues cost points for pages it touches and sleeps when it exceeds the limit within a cost delay window. That throttle was calibrated for spinning disks. On gp3 with 3,000 IOPS, a limit of 200 means autovacuum is deliberately using a fraction of the I/O available to it.

Raising autovacuum_vacuum_cost_limit to 1000 or 2000, and raising autovacuum_max_workers from the default 3 on a large instance, typically lets vacuum keep pace on workloads where it was permanently behind. The cost is I/O and CPU consumed by the vacuum itself, which is real but almost always cheaper than the storage and memory the bloat would otherwise demand.

Reclaiming existing bloat

Method	Locks	Reclaims to OS?	Extra space needed
Regular VACUUM	None blocking	Only trailing pages	None
VACUUM FULL	ACCESS EXCLUSIVE	Yes	Full table copy
pg_repack	Brief exclusive at swap	Yes	Full table copy
REINDEX CONCURRENTLY	None blocking	Yes, for indexes	Index copy

Remember that reclaiming space inside PostgreSQL does not shrink the managed volume. Freed space becomes reusable by the database but the allocated storage stays where autoscaling left it. Actually reducing the bill requires a dump and restore or a replica promotion after the bloat is removed. So the order is: fix vacuum, reclaim the bloat, then migrate to a smaller allocation and capture the saving.

What to monitor

Track n_dead_tup and last_autovacuum from pg_stat_user_tables for your twenty largest tables, alert when a table exceeds 15 percent dead tuples, and watch age(relfrozenxid) against autovacuum_freeze_max_age to catch wraparound risk long before it becomes an outage. Those three checks catch nearly everything.

Price allocated storage from Terraform so bloat-driven growth shows up as a reviewed cost rather than a silent ratchet. Compare storage configurations against the resource catalog, and see indexing cost and performance for the related index side.

FAQ

How much does PostgreSQL bloat cost?

A 1 TB database at 35 percent bloat carries about 350 GB of dead space, roughly $40 per month at gp3's $0.115 per GB-month. The larger indirect cost is memory: bloated tables and indexes occupy buffer pool space, so 35 percent bloat needs roughly 35 percent more RAM to hold the same working set, which can mean db.r6g.2xlarge at $756 instead of db.r6g.xlarge at $378.

Why does autovacuum fall behind on large tables?

The default autovacuum_vacuum_scale_factor of 0.2 means a table needs 20 percent dead rows before vacuum starts. On a 500 million row table that is 100 million dead rows accumulating first, after which the vacuum is a large slow operation that may not finish before the next threshold is crossed. Setting the scale factor to 0.02 per table makes vacuum run more often on far less work.

What is autovacuum_vacuum_cost_limit and why raise it?

It throttles autovacuum by accruing cost points for pages touched and sleeping when the limit is exceeded. The default of 200 was calibrated for spinning disks, so on gp3 with 3,000 IOPS autovacuum deliberately uses a fraction of available I/O. Raising it to 1000 or 2000, along with autovacuum_max_workers above the default 3 on large instances, usually lets vacuum keep pace.

Does vacuuming reduce my storage bill?

Not directly. Reclaimed space becomes reusable inside PostgreSQL, but the managed volume's allocated storage stays where autoscaling left it and cannot be reduced in place. Capturing the saving requires a dump and restore or a replica promotion into a smaller allocation after the bloat is removed. The correct order is fix vacuum, reclaim bloat, then migrate to a smaller volume.

What is the worst case if autovacuum fails?

Transaction ID wraparound protection. If autovacuum falls far enough behind on freezing, PostgreSQL refuses writes to protect data integrity, which is a full outage. Recovery requires a vacuum that can run for many hours on a large table. Monitoring age(relfrozenxid) against autovacuum_freeze_max_age catches this long before it becomes an incident whose cost dwarfs any storage figure.

How does C3X help with maintenance-driven cost?

C3X prices allocated storage and instance memory from Terraform, so the growth that bloat drives appears as a reviewed cost change rather than a silent ratchet on the invoice. Since managed storage cannot be shrunk in place, the cheap moment to notice a database heading from 1 TB to 1.8 TB of allocation is in the pull request, not on the following month's bill.

What to do next

Catch storage growth before it becomes permanent. C3X reads your Terraform and prices your resources against a live catalog. Start with the quickstart.