So there I was, staring at a VPS that looked perfectly fine from the outside—CPU cruising, RAM not pegged, disk I/O modest—and yet the app felt sluggish. Not a full meltdown, just a kind of weary sigh on every request. If you’ve ever had that “something’s off but I can’t prove it” gut feeling, you know the vibe. I tailed logs, poked at queries, and then the lightbulb went on: table bloat. Autovacuum was on, sure, but it wasn’t tuned for this little server’s reality. And the worst part? I’d seen this movie before. Autovacuum wasn’t lazy; it was just trying to be polite and wound up being late. On a VPS, that can quietly snowball into bloat, random IO, and a slow dance nobody asked for.
In this post, I want to walk you through how I think about PostgreSQL autovacuum tuning and bloat control on a VPS, the practical knobs that actually matter, and how to wield pg_repack when you’ve already got a mess on your hands. We’ll chat about what bloat really is (beyond the scary name), how to measure it without spreadsheets taking over your life, the handful of settings that usually move the needle, and a safe workflow to repack tables without making your app hold its breath. We’ll keep it friendly, but we’ll get into the good stuff.
Why Bloat Happens and Why Your VPS Feels It First
Here’s the thing about PostgreSQL: it’s wonderfully honest. When you update or delete a row, the old version doesn’t just vanish—it sticks around until VACUUM comes by and cleans it up. That’s a feature, not a bug, because Postgres uses multiversion concurrency control (MVCC) to keep readers happy while writers do their thing. But if autovacuum isn’t firing as often as it should, or if it’s politely tiptoeing around your workload, those dead rows pile up like uncollected recycling. That’s bloat.
On a big iron database server with more IOPS than sense, you can sometimes coast for a while. On a VPS—especially the kind we love for small apps and startups—disks are shared, I/O is precious, and RAM is the tightest bottleneck. Bloat translates into larger tables and indexes, more pages to scan, extra cache misses, and a general sense of heaviness. Think of it like carrying a backpack stuffed with last year’s receipts; you can still walk, but each step feels a tiny bit harder.
Autovacuum is the background helper that keeps things tidy without you thinking about it. The defaults are conservative because Postgres doesn’t want to surprise you with sudden I/O storms. That’s fine for a lot of workloads, but if your app updates the same rows frequently, or if you’ve got large, hot tables, you’ll probably need to nudge those defaults so they fit your reality. A VPS appreciates those nudges even more than a big box does.
Reading the Room: How to Spot Bloat and Lazy Autovacuum
I’ve learned to start with a quiet look around. Before touching a single setting, ask: how is autovacuum behaving today? Is it running at all? Is it finishing? Where is the pressure? You don’t need a full observability platform—just a couple of views Postgres gives you and a sensible logging setup.
First, I like to surface basic signals. You can peek at dead tuples per table:
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
If you see the same tables floating to the top with high n_dead_tup and no recent autovacuum, that’s a clue. I also love checking pg_stat_progress_vacuum in a second session when autovacuum is on the move, just to see who’s being worked on and how far along it is. It gives you a feel for whether autovacuum is actually keeping up or just nibbling at the edges. If you want to get into the weeds, the VACUUM progress reporting docs are a great compass.
Second, turn on helpful logging. You don’t need to go wild—just enable logs for autovacuum that runs long enough to be interesting:
log_autovacuum_min_duration = '5s'
With that, the server will log autovacuum jobs that took longer than five seconds. Pick a value that keeps noise out while still capturing “real work.” I usually ship those logs to a central place so I can search and graph them. If that sounds like your kind of tidy, I wrote about setting up centralised logging with Grafana Loki + Promtail—a perfect companion when you want to slice through Postgres logs without SSH gymnastics.
Third, remember indexes bloat too. You can feel it when index scans get slower for no apparent reason. If your table does heavy updates, the index leaf pages can grow with pointers to dead tuples. Autovacuum helps, but once an index has sprawled, you often need a rebuild or a repack to reclaim space.
The Practical Autovacuum Settings That Usually Move the Needle
Let’s talk knobs. There are many, but only a handful usually change the plot on a VPS. We’ll keep each one anchored in how it feels at runtime.
First up: when autovacuum decides to act. The scale and threshold settings control this:
# Fire more predictably on modest tables too
autovacuum_vacuum_scale_factor = 0.1 # default is 0.2; 10% change triggers sooner
autovacuum_vacuum_threshold = 50 # base tuples before scale factor applies
# Analyze more often to keep planner statistics fresh
autovacuum_analyze_scale_factor = 0.05 # default is 0.1
autovacuum_analyze_threshold = 50
I like smaller scale factors on a VPS, especially for hot tables that aren’t massive. It means autovacuum wakes up more often to do smaller, cheaper rounds of maintenance. You can always tune per-table later, but shifting the baseline helps. On very large tables, pure percentages can be a trap—ten percent of a huge table is still, well, huge. That’s where per-table settings shine.
Second: how fast autovacuum moves. The cost settings are your pacing tool:
# Let VACUUM move with purpose, but not bulldoze
vacuum_cost_limit = 2000
vacuum_cost_delay = '2ms' # small delay smooths IO; increase if IO stalls
Out of the box, autovacuum can be a bit too polite. On a VPS with SSDs, a higher vacuum_cost_limit often helps autovacuum finish sooner, which ironically reduces overall pressure. If you see spiky I/O or latency wobbles during vacuum, lengthen vacuum_cost_delay a touch. Picture it like steady breathing during a jog rather than sprinting and stopping.
Third: worker power. How many jobs can run, and how much memory do they get?
autovacuum_max_workers = 3 # or 4 on slightly larger VPS
maintenance_work_mem = '512MB' # for vacuum/reindex; match VPS size and workload
autovacuum_naptime = '15s' # how often to wake and check for work
On a small VPS, more workers isn’t always better. Three is a sweet spot for many apps—enough to work in parallel but not enough to cause a thundering herd. I bump maintenance_work_mem as long as the server can afford it; it helps VACUUM and index maintenance move briskly. And a shorter autovacuum_naptime keeps scheduling responsive without turning the server into a jittery hummingbird.
Fourth: freezing old tuples. If you’ve ever been sideswiped by long freeze vacuums, you know they can be noisy. These settings help keep them orderly:
autovacuum_freeze_max_age = 800000000
autovacuum_multixact_freeze_max_age= 160000000
vacuum_freeze_table_age = 200000000
vacuum_freeze_min_age = 50000000
Think of freezing as the seasonal deep clean. You want it to happen before it’s urgent, but not so often it interrupts daily life. I keep these near defaults and rely on the earlier triggers to keep tables fresh. If your workload sits idle for long stretches, glance at these numbers so you don’t get a surprise “house cleaning” during peak traffic.
And finally: visibility. If autovacuum seems sleepy, tell PostgreSQL to say more:
log_autovacuum_min_duration = '5s'
log_checkpoints = on
log_temp_files = '4MB'
This isn’t about drowning in logs; it’s about catching patterns. If you see constant checkpoint pressure while autovacuum runs, consider bumping up max_wal_size a bit. If temp files explode during analyze or queries, you might tweak work_mem carefully (per-session, ideally).
By the way, for the curious: the upstream details behind the curtain are in the PostgreSQL autovacuum documentation. It’s not bedtime reading, but it’s the map you wish you had when you’re lost.
Per‑Table Tuning: Where the Real Wins Hide
Global settings get you in the ballpark. But the tables doing the most damage to your I/O bill often need special care. The most common pattern I see is an “updates galore” table—think sessions, carts, or anything that gets nudged on almost every request.
For those, scale factors are too blunt. I’d rather use small fixed thresholds so autovacuum fires predictably:
ALTER TABLE public.sessions
SET (autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 50,
autovacuum_analyze_scale_factor= 0.02,
autovacuum_analyze_threshold = 50);
If the table is still ballooning, check the fillfactor. Lowering it on a hot update table leaves room on each page for updated rows to stay put, enabling HOT (heap-only) updates more often. It’s like leaving space in a suitcase so you don’t have to unpack everything to add one shirt.
ALTER TABLE public.sessions SET (fillfactor = 80);
VACUUM FULL public.sessions; -- blocking; do this in a quiet window
I only use VACUUM FULL when the table isn’t too big or the maintenance window is generous because it’s blocking. In most cases where downtime is precious, I go straight to pg_repack instead (we’ll get there). The point is: per-table fillfactor plus tighter autovacuum triggers often cuts bloat growth at the source.
One more sneaky source of trouble is unused or overly broad indexes. They bloat just as happily as tables, but they don’t help your queries. Before repacking, I’ll sometimes disable an obviously unused index in staging and measure the impact. In production, I’m careful: I prefer marking it invalid and seeing if anything yelps, or rolling out the drop during a calm moment. You’d be amazed how often a single unnecessary index doubles the maintenance cost on a busy table.
When the Mess Has Already Happened: pg_repack, Without the Drama
Alright, let’s say bloat has gotten out of hand. Autovacuum’s doing its best but can’t claw back space because pages are already overstuffed. That’s when I reach for pg_repack. If you haven’t met it yet, it’s an external utility that rebuilds tables and indexes online, using triggers to keep a shadow copy in sync and then swapping it in. The result: reclaimed space with minimal locks.
Before you touch anything, step zero: backups and safety checks. I know it’s boring, but I sleep better after a fresh backup and a quick restore test on another machine. If you want a friendly template for that mindset, here’s how I think through a no‑drama DR plan. Even with pg_repack’s smooth approach, there’s always a chance the final swap needs a short lock; be ready.
Installing pg_repack is straightforward on Debian/Ubuntu or RPM-based distros. Package names vary by PostgreSQL version, and sometimes you’ll compile from source. The official pg_repack project page has clear steps.
There are two big caveats I always call out to clients: first, your table should have a primary key or at least a unique not-null index that pg_repack can use. Otherwise it might require a full table lock. Second, watch your disk and WAL budget. Repacking a large table creates a parallel copy and churns WAL, which is fine if you’ve planned for it and unpleasant if you haven’t.
My usual flow looks like this. I start in a quiet period, confirm space, and keep eyes on replication lag if there’s a standby. Then I run a spot repack for the worst offender:
pg_repack
-h 127.0.0.1 -p 5432 -U postgres
-d mydb -t public.sessions
--wait-timeout=600 --no-order
The –no-order flag cuts some overhead when you don’t care about ordering. If everything behaves, I’ll consider cleaning up related indexes too. Many times it’s better to repack the whole database during a maintenance window:
pg_repack -h 127.0.0.1 -U postgres -d mydb -a --wait-timeout=600
That does all tables and indexes it can, skipping ones that aren’t safe. When you’ve got a read replica, watch lag and maybe throttle using –jobs or spacing runs apart. Slower and steady beats fast and scary on a VPS.
One of my clients once had a “mystery” 70 GB database. The tables added up to barely half that. We measured, repacked the hot tables first, then the rest, and came out at 38 GB without changing a single row of data. Queries sped up simply because the filesystem had less to drag around. That’s what I mean by bloat being sneaky: it steals milliseconds in a thousand little ways.
If you’re running Postgres in containers, quick note: I prefer running pg_repack from a dedicated utility container or from the host with network access, rather than baking it into the database image. Same logic I use when I talk about how I ship safer containers: keep the database container lean, grant only what’s needed, and run maintenance from controlled tooling with clear permissions.
A Day‑to‑Day Maintenance Rhythm That Actually Sticks
Autovacuum tuning is not a once-and-done switch flip. It’s a conversation with your workload. Here’s how I keep it manageable without turning it into a full-time job.
Step one, set a monthly audit ritual. I don’t mean a spreadsheet; I mean 20 minutes with coffee. Check your top tables by dead tuples, look at autovacuum logs for long runners, and glance at index sizes for the usual suspects. If anything looks weird, I dig in right away or schedule a repack during the next low traffic window.
Step two, stop bloat at the source. That means a healthy fillfactor on the hottest tables and the courage to remove an index that isn’t earning its keep. It also means keeping statistics fresh. If you’ve got lopsided distributions (like a handful of “hot” tenants or products), raising default_statistics_target a bit or setting it per table can help the planner understand reality.
Step three, monitor without obsessing. Centralised logs are enough for many teams. I ship autovacuum and error logs to Loki and set a few gentle alerts: “autovacuum jobs over 2 minutes,” “repack finished,” that sort of thing. If you want a quick start, I laid out a friendly path to VPS log management with Loki + Promtail. It makes charting autovacuum runs oddly satisfying.
Step four, plan for the inevitable “oh no” moment. Even with perfect tuning, a migration, a feature launch, or a surprise traffic spike can tilt a table into bloat territory. Having a written runbook helps—what you’ll repack first, what queries might be paused, who needs a heads-up. I treat it like a mini version of a DR playbook, just surgical and specific. If you haven’t written one before, I shared my approach to disaster recovery runbooks that actually work and the mindset carries over nicely.
And finally, prefer online operations where possible. In the MySQL world, I love using online migration tools to keep changes flowing with minimal disruption. The same principle applies here with pg_repack. If that idea resonates, I wrote about zero‑downtime MySQL migrations—different database, same philosophy: do the heavy lifting quietly in the background, then switch over.
Putting It All Together: A Simple Recipe You Can Trust
If we were sitting together with your VPS right now, I’d do three things. First, I’d turn on lightweight autovacuum logging and look at the last week of activity. Second, I’d identify the two or three tables that generate the most dead tuples, check their indexes and fillfactor, and set per-table autovacuum thresholds that fire early and often. Third, I’d schedule a repack for the worst offender and watch disk, WAL, and replication lag while it runs. That’s it. You don’t need a 40‑point plan to feel the difference.
Two weeks later, I’d check in again. Are autovacuum runs shorter? Did index scans get snappier? Is cache hit ratio steadier? If yes, we keep going. If not, I’ll weave in another pass: maybe increase maintenance_work_mem, trim an index, or lower a scale factor a smidge. The goal is not perfection; the goal is a boring database. Boring is beautiful.
If you want to nerd out a bit more (and who among us doesn’t), stash a link to the autovacuum configuration page and the pg_repack project docs. They’re the authoritative sources behind many of the tips here.
Wrap‑up: A Friendly Nudge Toward Happier Queries
Ever had that moment when your app just feels heavier than it should? Nine times out of ten, it’s not some exotic bug—it’s everyday maintenance lagging behind. On a VPS, a little tuning goes a long way. Let autovacuum wake up a touch earlier. Give it just enough muscle to finish its rounds without stomping on I/O. Teach your hottest tables to leave breathing room with fillfactor. And when bloat has already moved in, repack with a clear plan and a backup you’ve actually tested, not just assumed.
If this nudged you to tweak a setting, peek at a log, or schedule a quiet repack, great—that’s a win. And if you’re juggling containers along the way, keep your database image clean and your maintenance tools sharp and separate; it’ll save you from a pile of “why is this in prod?” questions later. Most of all, aim for a database that’s as boring as possible in the best way: predictable, quiet, and fast enough that you forget it’s there.
Hope this was helpful! If you’ve got a fun bloat story or a setting you swear by, I’m all ears. See you in the next post, and here’s to fewer dead tuples and happier queries.
