How Bitlox Fixed PostgreSQL’s Hidden Vacuum Overhead and Prevented Bloat Backlash

PostgreSQL's autovacuum is often treated as a set-and-forget background process. But for Bitlox, a fintech platform processing millions of transactions daily, it became a silent performance killer. Queries that once ran in milliseconds started taking seconds; disk usage climbed despite no increase in active data; and the operations team faced a growing sense of dread as table bloat crept in. This article walks through how Bitlox uncovered the hidden overhead of PostgreSQL's vacuum mechanism, implemented targeted fixes, and established a sustainable bloat prevention strategy. The lessons apply to any team running PostgreSQL at scale — especially those with write-heavy workloads.

Recognizing the Hidden Vacuum Overhead

Bitlox's first clue came from a routine monitoring dashboard. CPU usage on the database server was consistently 20–30% higher than expected, even during low-traffic periods. The I/O wait times on the primary disk were also elevated. Initial investigations pointed to autovacuum workers consuming resources, but the default settings had worked well for months. What changed?

The MVCC Bloat Mechanism

PostgreSQL uses Multiversion Concurrency Control (MVCC) to allow concurrent reads and writes without locking. Every UPDATE or DELETE creates a new row version, leaving the old one as a "dead tuple" until vacuum reclaims the space. If vacuum falls behind, dead tuples accumulate, causing table bloat and degrading index efficiency. Bitlox's transaction volume had grown faster than the autovacuum could keep up, leading to a backlog of dead tuples that consumed both disk and memory.

Signs of Bloat Backlash

Beyond CPU and I/O, Bitlox noticed three specific symptoms: (1) Sequential scans on tables that previously used index-only scans, because the visibility map was outdated; (2) Increasing autovacuum worker counts, sometimes hitting the maximum and causing new workers to queue; (3) Query plans that suddenly included “Rows Removed by Filter” values far above expected, indicating that the planner was overestimating live tuple counts due to bloat. These signs pointed to a vacuum system that was working harder but achieving less.

Bitlox also observed that the autovacuum workers were spending more time scanning entire tables rather than focusing on modified pages, a symptom of the “all-visible” flag not being set on many pages. This created a feedback loop: more bloat led to longer vacuum cycles, which in turn delayed the next vacuum, allowing more bloat to accumulate.

Diagnosing the Root Causes

Once the team recognized the symptoms, they systematically investigated the underlying causes. The goal was to understand why the default autovacuum configuration was no longer sufficient.

Configuration Gaps

The default settings in PostgreSQL 13 (which Bitlox was running at the time) include autovacuum_vacuum_scale_factor = 0.2 and autovacuum_vacuum_threshold = 50. For a large table with millions of rows, the scale factor means vacuum triggers only after 20% of rows change — which can translate to hundreds of thousands of dead tuples accumulating before a vacuum cycle starts. For Bitlox's high-traffic tables, this interval was too long.

Work Contention

Bitlox had multiple autovacuum workers (default: 3) but they often competed for I/O with the primary workload. The vacuum cost delay settings (vacuum_cost_limit = 200, vacuum_cost_delay = 20ms) were meant to throttle vacuum, but in practice, the workers would sometimes pause too long, extending the vacuum duration and allowing more dead tuples to accumulate in the meantime.

Index Bloat Amplification

Table bloat was only part of the problem. Indexes on heavily updated columns also grew, because each UPDATE on an indexed column inserts a new index entry. Bitlox found that their largest index had grown to 1.5× the size of the table itself, causing index scans to be slower and memory consumption higher.

To confirm the diagnosis, the team used pg_stat_user_tables to track n_dead_tup and last_autovacuum, and pgstattuple to estimate bloat ratios. They discovered that several core tables had bloat ratios exceeding 40%, meaning nearly half the table space was occupied by dead tuples.

Designing the Fix Strategy

Bitlox's approach was not to disable autovacuum or run aggressive manual vacuums, but to tune the system to match their workload patterns. The strategy involved three layers: per-table tuning, resource management, and monitoring enhancements.

Per-Table Vacuum Tuning

The team set custom autovacuum parameters for their most active tables. For example, on the transactions table, they changed autovacuum_vacuum_scale_factor to 0.01 and autovacuum_vacuum_threshold to 1000, so that vacuum triggers after only 1% of rows change (or 1000 dead tuples, whichever is reached first). This increased vacuum frequency but dramatically reduced the backlog of dead tuples. They also set autovacuum_vacuum_cost_limit higher (e.g., 1000) on these tables to allow faster vacuuming, while keeping the default for less critical tables.

Managing Autovacuum Workers

To prevent worker contention, Bitlox increased the number of autovacuum workers from 3 to 6, but also adjusted the vacuum cost delay to 10ms (from 20ms) and the cost limit to 800 (from 200) for the database cluster. This allowed more workers to run concurrently without overwhelming the I/O subsystem. They also set autovacuum_naptime to 30 seconds (from the default 1 minute) so that the workers would check for work more frequently.

Index Maintenance

For the bloated indexes, Bitlox scheduled periodic REINDEX CONCURRENTLY operations during low-traffic windows. They also experimented with partial indexes and index-only scan optimizations to reduce the number of index entries per update.

The team also added a monitoring view that combined pg_stat_all_tables with pg_class to calculate bloat ratios in real time, and set up alerts when bloat exceeded 20% on any table larger than 1 GB.

Implementation and Initial Results

Bitlox rolled out the changes incrementally over two weeks, starting with non-critical tables and then moving to production tables during a maintenance window.

Step-by-Step Rollout

1. On a staging environment, they applied the per-table settings and monitored the vacuum worker activity for 48 hours. 2. They verified that the number of dead tuples decreased and that query plans improved. 3. In production, they first adjusted the global autovacuum settings (worker count and cost delay) during a low-traffic period. 4. After observing no negative impact, they applied the per-table settings for the most active tables one by one, monitoring I/O and query latency after each change.

Measurable Improvements

Within one week, the average number of dead tuples on the core transactions table dropped from 800,000 to under 10,000. CPU usage on the database server decreased by 15%, and I/O wait times fell by 40%. Query latency for the most common reporting queries improved by 30%. The team also noticed that autovacuum workers were completing their cycles faster, reducing the chance of overlap with peak traffic.

However, the team also observed that the increased vacuum frequency led to slightly higher write amplification on the primary disk (about 5% more WAL generation), which they considered an acceptable trade-off for the bloat reduction.

Risks, Pitfalls, and Mitigations

While Bitlox's fix was successful, the journey highlighted several risks that other teams should anticipate.

Over-Vacuuming Pitfall

Setting vacuum thresholds too aggressively (e.g., scale_factor = 0.001) can cause vacuum to run almost continuously on large tables, consuming CPU and I/O without much benefit. Bitlox found that a scale_factor of 0.01 was a good balance for their workload; for very large tables (over 100 GB), they used a fixed threshold of 50,000 dead tuples instead of a percentage.

Worker Starvation

Increasing the number of autovacuum workers can lead to competition for shared resources, especially if the database has many small tables that each require a worker. Bitlox mitigated this by setting autovacuum_max_workers to 6 (from 3) and using the cost delay mechanism to throttle workers when I/O pressure was high.

Concurrent Index Rebuild Risks

REINDEX CONCURRENTLY can cause index bloat during the rebuild itself, as it creates a new index while keeping the old one. Bitlox scheduled these operations during maintenance windows and monitored disk space closely. They also ensured that autovacuum was active during the rebuild to clean up any dead tuples generated.

Another pitfall was forgetting to adjust the autovacuum settings after schema changes (e.g., adding a new large table). Bitlox added a checklist in their change management process to review autovacuum parameters for any new table expected to have high write volume.

Mini-FAQ: Common Questions About Vacuum Tuning

Based on Bitlox's experience and broader industry practices, here are answers to questions that often arise when tuning PostgreSQL vacuum.

How often should I run VACUUM on a busy table?

There is no one-size-fits-all answer, but a good starting point is to aim for vacuum to complete at least once per hour on tables with high update/delete rates. Use pg_stat_user_tables to track the time since last autovacuum and the number of dead tuples. If dead tuples exceed 10% of live tuples between vacuum cycles, consider lowering the scale factor or threshold.

Should I use VACUUM FULL or pg_repack?

VACUUM FULL locks the table and can cause downtime, so it's generally not suitable for production. pg_repack is a popular extension that rebuilds tables without blocking writes, but it requires careful planning and extra disk space. Bitlox used pg_repack for their largest bloated table during a maintenance window, and it reduced the table size by 60% without significant downtime.

What is the role of the visibility map?

The visibility map tracks which pages contain only tuples visible to all transactions. When the visibility map is up to date, index-only scans can skip heap fetches, improving performance. Vacuum updates the visibility map as it scans pages. If vacuum is not running frequently enough, the visibility map becomes outdated, and index-only scans degrade. Bitlox's tuning improved visibility map freshness, which contributed to the query performance gains.

Can I disable autovacuum entirely and run manual vacuums?

Disabling autovacuum is strongly discouraged for any production database. Manual vacuum scheduling is error-prone and can lead to catastrophic bloat if a scheduled job fails. Bitlox considered this option but decided that tuning autovacuum was safer and more reliable.

Synthesis and Next Actions

Bitlox's experience demonstrates that PostgreSQL's autovacuum is not a black box — it can and should be tuned to match workload patterns. The key takeaway is that proactive monitoring and incremental configuration changes can prevent bloat from becoming a crisis.

Immediate Steps for Your Database

If you suspect vacuum overhead or bloat in your PostgreSQL instance, start with these actions:

1. Run a bloat estimation query (using pgstattuple or a community script) to identify the most affected tables. 2. Check pg_stat_user_tables for n_dead_tup and last_autovacuum on those tables. 3. For tables with more than 20% dead tuples or where last_autovacuum is more than 24 hours ago, consider lowering the autovacuum scale factor to 0.05 or 0.01. 4. Increase autovacuum_max_workers if you have many tables and see workers queuing. 5. Set up alerts for bloat ratio and vacuum age. 6. Schedule regular REINDEX CONCURRENTLY for heavily updated indexes, but monitor disk space.

Long-Term Strategy

Bitlox now reviews autovacuum settings quarterly as part of capacity planning. They also use a custom dashboard that shows vacuum worker activity, dead tuple counts, and bloat trends over time. The team has adopted a policy of “tune before you upgrade” — before any major PostgreSQL version upgrade, they review vacuum configuration to ensure it aligns with the new version's defaults and features.

By treating vacuum as a first-class operational concern rather than a background noise, Bitlox not only fixed their immediate performance problem but also built a more resilient database infrastructure. The same approach can help any team keep PostgreSQL running smoothly under growing write loads.