Autovacuum Tuning for the SMB: A Field Guide

Why SMBs Should Avoid Global Autovacuum Tweaks

In the world of PostgreSQL, the autovacuum daemon is your best friend—until it isn’t. For small-to-medium businesses (SMBs) running lean infrastructure, the temptation to "tune" autovacuum globally by editing `postgresql.conf` is high. However, changing global parameters like `autovacuum_vacuum_scale_factor` or `autovacuum_vacuum_cost_limit` often leads to unintended consequences, such as excessive I/O pressure or bloated tables that never get cleaned.

The golden rule for SMB database management is simple: keep your global defaults for 95% of your tables and apply surgical, table-level tuning only where it’s actually needed.

The 3 Tables Worth Tuning Manually

While most tables in a logistics or SaaS platform behave predictably, three specific types of tables almost always require custom autovacuum settings to maintain performance.

First, identify your **High-Volume Transaction Logs**. These tables see constant inserts and deletes. Because they grow rapidly, the default autovacuum trigger (usually 20% of the table size) is too slow, leading to massive bloat. You should lower the `autovacuum_vacuum_scale_factor` to 0.01 or 0.05 for these tables to ensure they are cleaned frequently.

Second, look at your **Frequently Updated Metadata Tables**. If you have a small configuration table that is updated hundreds of times per minute, the default settings might not trigger a vacuum often enough, causing index bloat. For these, use a lower scale factor combined with a lower `autovacuum_vacuum_cost_limit` to ensure the vacuum process runs more aggressively without spiking CPU usage.

Third, target your **Large "Cold" Tables**. These are tables that hold historical shipment data or archived logs. They don’t change often, but when they do, a default autovacuum might take hours to finish. For these, consider disabling autovacuum entirely and scheduling a manual `VACUUM ANALYZE` during off-peak hours using a cron job. This prevents the daemon from fighting for resources during your peak logistics operating windows.

How to Apply Table-Level Settings

You don’t need to restart your database to apply these changes. PostgreSQL allows you to override global settings on a per-table basis using the `ALTER TABLE` command. For example:

ALTER TABLE shipment_logs SET (autovacuum_vacuum_scale_factor = 0.01);

This command tells PostgreSQL to prioritize this specific table while leaving the rest of your database configuration untouched. This approach keeps your maintenance overhead low and your performance predictable.

When to Leave Defaults Alone

For the vast majority of your schema—user profiles, static lookups, and standard business entities—the default autovacuum settings are perfectly balanced. Over-tuning these tables often results in "vacuum thrashing," where the database spends more time cleaning itself than serving your actual application queries. If a table isn't showing signs of bloat or performance degradation, leave it alone.

Managing database health is a balancing act, especially as your logistics operations scale. If you are looking for deeper visibility into your database performance and automated insights to keep your infrastructure running smoothly, Querk provides the monitoring tools necessary to optimize your logistics stack. Learn more at https://querk.io.