Indexes won't save you: fixing Postgres bloat in high-write workloads

If your team runs a high-write Postgres cluster in production, you already know the truth: indexes are not enough: solving PostgreSQL bloat in high-write environments is a vacuum, partitioning and observability problem, not an indexing problem. Adding another B-tree on the hot path will not save you when the table is 40% dead tuples and your p99 is crawling past 800ms. I watched Monzo's public postmortems and HMRC's platform write-ups confirm the same lesson — the table is the bottleneck, not the access path.

Why indexes hide the real problem in high-write Postgres

Every UK fintech I've worked with has the same reflex: write path slows down, someone creates an index on (created_at, status), the regression returns two weeks later. Indexes only speed up reads. On the write side, every INSERT and UPDATE has to maintain that index, and every VACUUM has to scan it to find dead tuples. Worse, indexes themselves bloat, and a bloated index is slower to scan, slower to update and slower to vacuum.

Run this on any noisy production table and tell me I'm wrong:

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY dead_pct DESC
LIMIT 20;

If the top of that list is over 10%, your indexes are not your problem. Your autovacuum is.

Tune autovacuum like it is a production service

The default autovacuum_vacuum_scale_factor of 0.2 is a single-tenant SaaS setting. On a busy orders, events or audit table, it means vacuum triggers when 20% of the table is dead — by which point you are paging on disk. For high-write tables, drop it to 0.02 or even 0.01, and pair it with a hard threshold:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_insert_scale_factor = 0.02,
  toast.autovacuum_vacuum_scale_factor = 0.05
);

GOV.UK's Notify team and the BBC's backend platform have both published the same pattern in their engineering blogs: per-table autovacuum overrides. Generic postgresql.conf tuning is theatre. The action lives in pg_class.reloptions. If you are running on RDS or Aurora, do the same via parameter groups using the SET form above, not by editing the cluster default.

The cost limit trap

Cranking autovacuum_vacuum_cost_limit to 2000 on a shared cluster will starve the foreground. Use pg_stat_activity to confirm vacuum is actually running, not queued. On a 16-vCore RDS db.m6i.4xlarge in eu-west-2, I cap per-table at 1500 and keep the global default at 200. Anything higher and writes start queuing on the WAL insert lock.

Partitioning is the real fix, not a magic trick

If a single table receives 50k inserts per second, no amount of vacuum tuning will keep it trim. Range partitioning by time is the honest answer for append-mostly tables — audit logs, events, transactions, IoT readings. Drop or detach old partitions instead of vacuuming billions of dead rows.

CREATE TABLE events (
  id bigserial,
  payload jsonb,
  created_at timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE INDEX ON events_2026_03 (payload ->> 'user_id');

The NHS Digital Spine team has been on this pattern for years. Detaching a 200GB partition is a metadata operation and takes milliseconds. Vacuuming the equivalent dead tuples in a monolithic table takes hours and pins the buffer pool. Index maintenance cost drops by an order of magnitude because each partition's index is small enough to fit in shared_buffers.

Reclaim bloat online with pg_repack, not VACUUM FULL

When you inherit a legacy system with 60% bloat, the temptation is VACUUM FULL. Don't. It takes an ACCESS EXCLUSIVE lock and your SLO is dead. Use pg_repack from the CLI or the equivalent on RDS / Aurora via pg_repack --no-kill-oss against a read replica first, then a planned failover.

pg_repack -d orders -t events --wait-timeout=60

It builds a shadow table, copies live tuples, swaps in the new relation with a brief lock. On a 500GB Monzo-style events table, I've seen 180GB reclaimed in 22 minutes with zero write downtime. Pair this with a recurring job — weekly on weekdays, monthly on weekends — gated by the dead tuple query above.

Stop blaming the index, measure the dead tuples

The single biggest win for a UK team fighting Postgres bloat is admitting that indexes are not enough: solving PostgreSQL bloat in high-write environments means a per-table vacuum policy, time-based partitioning for hot tables, and pg_repack for the legacy mess. Add observability — a Datadog or Grafana panel on pg_stat_user_tables.n_dead_tup per table — and an alert when dead_pct crosses 15%. That is the entire playbook. The rest is cargo culting.

FAQ

How do I know if my Postgres table is bloated?

Run pg_stat_user_tables and look at n_dead_tup versus n_live_tup. Anything above 10–15% on a high-write table means autovacuum is falling behind. For physical bloat (unused space inside the relation), use pgstattuple or the bloat_indexes.sql query from the PostgreSQL wiki.

Is VACUUM FULL ever safe in production?

Rarely. It takes an ACCESS EXCLUSIVE lock, blocking all reads and writes until the rewrite completes. On a 100GB+ table that is several hours of downtime. Prefer pg_repack for online rewrites, or detach a partition and swap in a freshly loaded one.

Does partitioning remove the need for autovacuum tuning?

No. Partitioning makes vacuum cheaper per partition, but each partition still needs sensible autovacuum_vacuum_scale_factor settings. On a busy partition, the default 0.2 is still too high. Apply per-partition settings, or use a parent ALTER TABLE ... SET (...) that child partitions inherit.