PostgreSQL Bloat, Index Waste, and Cloud Cost

Bloat and unused indexes are usually filed under “performance hygiene.” On a cloud database they are also a line on the bill: storage you pay for and never use, writes amplified across indexes nobody reads, and I/O spent scanning dead space. The fixes are well understood and mostly low-risk — the hard part is seeing the problem.

Situation

Cloud Postgres bills separate storage, I/O, and compute into visible line items, but nothing on the invoice tells a team that half a table’s storage is dead tuples, or that six indexes have not served a single query in a month. Bloat and unused indexes are database hygiene problems that were always true; on a cloud bill they become cost problems too.

The Problem

PostgreSQL’s MVCC model creates dead tuples on every update and delete. Autovacuum reclaims them for reuse, but under heavy churn — or with mistuned autovacuum — dead space accumulates faster than it’s reclaimed. Tables and indexes grow beyond the live data they hold. Separately, indexes added years ago for queries that no longer run keep costing write overhead and storage. Neither shows up as a “cost” problem until you go looking:

• Storage on cloud Postgres (and Aurora) is billed on what’s allocated/used; bloat inflates it permanently — Aurora storage doesn’t even shrink.
• Write amplification: every INSERT/UPDATE maintains every index on the table. Unused indexes tax every write with zero read benefit.
• I/O: bloated tables mean more pages scanned for the same rows — more I/O, which on Aurora is a direct charge and everywhere is latency.

These are small per-row and large in aggregate — the classic shape of a cost that hides until measured. The question that matters: is anyone actually measuring bloat and index usage, or is the bill just absorbing it?

Finding the Waste

Bloat and unused indexes share the same root causes, and the same read-only diagnostic path finds both.

flowchart TD
    A[High-churn tables] --> B[Dead tuples outpace autovacuum]
    C[Long-running transactions] --> B
    B --> D[Table and index bloat]
    E[Indexes from retired queries] --> F[Zero-scan indexes]
    F --> G[Write overhead with no read benefit]
    D --> H[Review checklist]
    G --> H
    H --> I[Prioritized remediation]

Technical root causes worth checking before anything else:

• High-churn tables (queues, counters, soft-deletes) outpacing autovacuum defaults.
• Long-running transactions holding back the xmin horizon so vacuum can’t reclaim.
• Indexes created for one-off queries, dashboards, or ORMs and never removed.
• Duplicate or redundant indexes (e.g. an index that’s a prefix of another).

Review checklist (read-only):

• Which tables and indexes have the highest estimated bloat?
• Is autovacuum keeping up, or are dead tuples climbing on hot tables?
• Are there long-running transactions blocking vacuum?
• Which indexes have zero or near-zero scans in pg_stat_user_indexes?
• Any duplicate/redundant indexes?
• What’s the storage trend, and how much is reclaimable?

The companion DB Cost & Reliability Toolkit ships read-only index_bloat_review.sql and related checks for exactly this.

In Practice

(Illustrative — the pattern these reviews repeatedly surface, not a specific client.)

• Four high-churn tables carried significant estimated bloat; tuning autovacuum (lower scale factors, more workers) plus a maintenance-window repack reclaimed storage and cut scan I/O.
• Six indexes showed zero scans over a 30-day window while adding write overhead; dropping them (after confirming no rare/seasonal use) reduced write amplification and storage.

A note on safety: finding all of this is read-only. Applying it ranges from zero-risk (drop an index with zero scans) to needs-a-window (repack a large table). Sequence accordingly and validate in staging.

Where It Breaks

Wrong move	What happens in production	Better approach
Drop an index after a short scan window	A seasonal or rare-but-critical query breaks	Confirm zero scans across a full 30+ day window before dropping
VACUUM FULL on a live hot table	Table is locked for the duration; outage risk	Prefer pg_repack for an online reclaim, or schedule a maintenance window
Tune autovacuum globally	Aggressive settings on cold tables waste cycles; hot tables still lag	Tune per-table (autovacuum_vacuum_scale_factor) based on churn
Treat bloat as a one-time cleanup	Bloat and unused indexes silently return within months	Add bloat and index-usage checks to a recurring (monthly) hygiene routine

What to Do Next

• Problem: Table and index bloat, plus indexes nobody queries, quietly inflate cloud storage, I/O, and write cost without ever appearing as a line item.
• Solution: Measure bloat and index-scan counts with read-only queries, tune autovacuum per hot table, reclaim bloat with pg_repack before considering VACUUM FULL, and drop confirmed-unused indexes after a long-enough observation window.
• Proof: Storage growth flattens or reverses, write latency on high-churn tables improves, and the top sequential-scan queries convert to index scans after remediation.
• Action: This week, run bloat estimation and pull pg_stat_user_indexes scan counts over the longest window you have, and flag anything with zero scans for a 30-day confirmation before dropping it.

---

Want a senior engineer to find and quantify this in your database? AKS runs a Database Cost & Reliability Review that includes bloat and index analysis with the math behind each opportunity. Start free with the 30-Point Checklist, or see a worked example in the Acme SaaS sample report.