PostgreSQL Vacuum Internals for High-Write Mobile Backends

--- 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 earns its keep with a desk stretch nudge. Focus is good; circulation matters too. Relevant docs: - PostgreSQL autovacuum storage parameters - pgstattuple extension - Visibility map internals Top comments (0)