---
title: "PostgreSQL Vacuum Internals: Fixing Write-Pattern Bloat in Mobile Backends"
published: true
description: "Tune PostgreSQL autovacuum for high-write mobile backends with per-table overrides, bloat detection queries, and visibility map leverage for index-only scans."
tags: postgresql, performance, mobile, architecture
canonical_url: https://mvpfactory.co/blog/postgresql-vacuum-internals-mobile-backends
---
## What We Are Building
By the end of this tutorial you will have a working autovacuum tuning strategy for high-write PostgreSQL tables — the kind your mobile backend actually generates. We will instrument bloat detection, apply per-table overrides, and learn to read visibility map coverage so you can catch performance degradation before your query planner starts making bad choices.
---
## Prerequisites
- PostgreSQL 13+ (storage parameters and visibility map columns available)
- `pgstattuple` extension (`CREATE EXTENSION pgstattuple;`)
- At least one high-write table you suspect is bloating (session state, event stream, notification queue)
---
## The Problem: Mobile Backends Break Vacuum's Defaults
Default PostgreSQL autovacuum settings were designed for balanced read/write workloads — not the upsert storms, session tables, and event streams mobile backends generate.
A delivery service backend sustains location pings every 2–5 seconds per active user, session state upserts, notification event queues, and order lifecycle transitions. A 500K-row session table receiving 2,000 writes per minute generates over 100K dead tuples before autovacuum's default 20% threshold even triggers.
PostgreSQL's MVCC model never overwrites rows in place. Every `UPDATE` creates a new row version; the old version becomes a dead tuple. Two settings do the most damage:
- `autovacuum_vacuum_scale_factor = 0.2` — vacuum fires when 20% of rows are dead
- `autovacuum_vacuum_cost_delay = 2ms` — sleep between I/O cost units to limit disk impact
Here is the pattern I use in every project to reason about which tables are at risk:
| Table type | Default trigger | Recommended override |
|---|---|---|
| Event stream (millions of rows) | 20% = 2M dead tuples | `scale_factor = 0.01`, `cost_delay = 0` |
| Session state (high-churn) | 20% = 100K dead tuples | `scale_factor = 0.05`, `threshold = 100` |
| Notification queue | 20% of queue depth | `scale_factor = 0.01`, `cost_delay = 0` |
| Reference/lookup tables | Default is fine | Leave defaults alone |
---
## Step 1: Apply Per-Table Autovacuum Overrides
Stop applying global autovacuum settings uniformly. PostgreSQL supports per-table storage parameters — use them:
sql
ALTER TABLE user_events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 0,
autovacuum_vacuum_threshold = 500,
autovacuum_analyze_scale_factor = 0.005
);
ALTER TABLE user_sessions SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_cost_delay = 0,
autovacuum_vacuum_threshold = 100
);
Setting `cost_delay = 0` disables the I/O throttle for these tables. Accept the disk pressure — the alternative is bloat that stalls your entire query path.
---
## Step 2: Detect Bloat Accurately
Here is the gotcha that will save you hours: `pg_stat_user_tables` reports `n_dead_tup` as an estimate updated by autovacuum — it lags reality badly under sustained write load. Use this query instead:
sql
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
n_dead_tup,
n_live_tup,
ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC
LIMIT 20;
For accurate measurement, install `pgstattuple` and run:
sql
SELECT * FROM pgstattuple('public.user_events');
The `dead_tuple_percent` field reads actual page data, not stats estimates. Alert on this number, not the estimate. Schedule a job that fires when it exceeds 5% on any high-churn table.
---
## Step 3: Monitor Visibility Map Coverage
Most teams treat vacuum as a space-reclamation job. The docs do not make this obvious, but vacuum is also what makes index-only scans work. When vacuum confirms all tuples on a page are visible to all transactions, it marks that page in the visibility map. Once marked, PostgreSQL can satisfy index lookups without touching the heap.
On NVMe storage, index-only scans run in roughly 0.1–0.3ms per fetch versus 1–3ms for heap fetches — a 5–10x latency gap that compounds under concurrent read load. On high-write tables that never get properly vacuumed, the visibility map stays perpetually dirty and you pay heap I/O costs on every query you thought you'd already optimized.
Monitor it:
sql
SELECT
pg_stat_user_tables.schemaname,
pg_stat_user_tables.tablename,
pg_class.all_visible,
pg_class.relpages,
(pg_class.all_visible::float / NULLIF(pg_class.relpages, 0) * 100)::int AS pct_visible
FROM pg_class
JOIN pg_stat_user_tables
ON pg_stat_user_tables.tablename = pg_class.relname
AND pg_stat_user_tables.schemaname = (
SELECT nspname FROM pg_namespace WHERE oid = pg_class.relnamespace
)
WHERE pg_class.relkind = 'r' AND pg_class.relpages > 100
ORDER BY pct_visible ASC;
Tables below 70% visible are candidates for `VACUUM ANALYZE` and tighter autovacuum configuration. Keep this above 90% on any table where your query planner depends on index-only access paths.
---
## Gotchas
**Gotcha 1 — Global settings will not save your hot tables.** Any table receiving more than 1,000 writes per minute needs its own `ALTER TABLE ... SET (...)` override. Global autovacuum will always be too slow for your worst offenders.
**Gotcha 2 — `n_dead_tup` is a lie under load.** I have watched engineers tune autovacuum based on `pg_stat_user_tables` while bloat was quietly compounding. Install `pgstattuple` before you think you need it.
**Gotcha 3 — A table at 40% visibility is silently expensive.** You will not see an error. You will just pay full heap I/O on every index scan and wonder why your "optimized" queries are not performing. This one hides for months.
**Gotcha 4 — `cost_delay = 0` is intentionally aggressive.** It is correct for high-churn tables. Do not set it globally.
---
## Conclusion
Here is the minimal checklist to get this working in production:
1. Override autovacuum per-table for any relation receiving more than 1,000 writes per minute — `scale_factor = 0.01`, `cost_delay = 0`
2. Replace `pg_stat_user_tables` estimates with `pgstattuple` for real bloat numbers; alert on `dead_tuple_percent > 5%`
3. Run the visibility map query weekly; anything below 70% needs `VACUUM ANALYZE` and tighter thresholds until it holds above 90%
I usually run the bloat detection queries during long debugging sessions — the kind where you're three hours into `pg_stat` pages and realize you haven't moved. That's when [HealthyDesk](https://play.google.com/store/apps/details?id=com.healthydesk) earns its keep with a desk stretch nudge. Focus is good; circulation matters too.
**Relevant docs:**
- [PostgreSQL autovacuum storage parameters](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html)
- [pgstattuple extension](https://www.postgresql.org/docs/current/pgstattuple.html)
- [Visibility map internals](https://www.postgresql.org/docs/current/storage-vm.html)
Top comments (0)