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.

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.

Why it matters financially

  • 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.

Technical root causes

  • 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.

Example findings

(Illustrative.)

  • 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.

Actions to take

  1. Measure before touching anything. Run bloat estimation and pg_stat_user_indexes scan counts. Capture a 30-day window so you don’t drop a seasonal index.
  2. Tune autovacuum on hot tables — per-table autovacuum_vacuum_scale_factor, more workers, faster cost limits — before resorting to rewrites.
  3. Reclaim bloat safely. Prefer pg_repack (online) over a blocking VACUUM FULL/REINDEX; schedule maintenance windows for the rest.
  4. Drop unused indexes carefully — confirm zero scans across a long-enough window, and check for constraint-backing indexes before dropping.
  5. Hunt long-running transactions that hold back vacuum; they’re often the real root cause.
  6. Make it recurring. Add bloat and unused-index checks to a monthly hygiene routine and alert on storage runway.

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.

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.