DEV Community

Cover image for Understanding and Monitoring Index and Table Bloat in PostgreSQL
özkan pakdil
özkan pakdil

Posted on Originally published at ozkanpakdil.github.io on

Understanding and Monitoring Index and Table Bloat in PostgreSQL

PostgreSQL 🐘 is a robust database, but its MVCC (Multi-Version Concurrency Control) design can sometimes lead to a subtle issue: table and index bloat. If left unchecked, bloat wastes storage, increases I/O, and can slow queries down.

In this post, I’ll walk through:

  • What bloat is and why it happens
  • Different ways to measure it
  • How to keep an eye on it without killing your database
  • A few habits to reduce or fix it

What is bloat? 🧹

  • Table bloat : Every update creates a new row version, and the old one becomes dead. Those dead tuples sit there until vacuum cleans them.
  • Index bloat : Even when dead tuples are gone from the table, their index entries may stick around until an index vacuum or reindex.

Why care?

  • Bigger indexes mean slower lookups. Bigger indexes = slower index scans (more pages to read).
  • Larger tables mean more I/O. Bigger tables = more disk I/O and less cache efficiency.
  • Wasted pages reduce cache efficiency. More bloat = more work for autovacuum.

A little is fine. A lot can hurt.

Ways to measure bloat 📐

1. ioguix scripts (detailed but heavy)

The ioguixpgsql-bloat-estimationrepo has two well-known queries:

They’re pretty accurate, but on a big database they can be very slow and eating too much resource.

2. Maxim Boguk’s formula (fast heuristic)

Described in pg_index_pilot. It’s not exact, but it’s fast enough to use in monitoring.

Formula:

bloat_indicator = index_size / reltuples
Enter fullscreen mode Exit fullscreen mode

Here’s SQL you can run right away (no extensions needed):

SELECT 
    i.schemaname,
    c.relname AS table_name,
    i.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    c.reltuples::bigint AS row_estimate,
    CASE 
        WHEN c.reltuples > 0 
        THEN pg_relation_size(i.indexrelid) / c.reltuples 
        ELSE NULL 
    END AS bytes_per_row
FROM pg_stat_all_indexes i
JOIN pg_class c ON c.oid = i.relid
ORDER BY bytes_per_row DESC NULLS LAST
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode
  • bytes_per_row acts as the Boguk bloat indicator.
  • The higher it is compared to the average row size, the more bloated the index might be.
  • This is not exact, but it’s fast and lightweight.

Boguk’s bloat indicator is not measured in bytes or per cents. It is to be used in relative scenario: first, we measure the “ideal” value – the value of freshly built index. And then, we observe how the value changes over time – if it significantly bigger than the “ideal” one, it is time to reindex.

3. pgstattuple (precise but slower)

For deeper inspection, the pgstattuple extension can scan indexes or tables directly:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('my_index');
Enter fullscreen mode Exit fullscreen mode

It’s more precise, but scans every page, requires superuser and scans the whole object (can be slow).

Monitoring bloat over time 📊

A couple of lightweight checks go a long way:

  • Watch pg_stat_all_tables.n_dead_tup for dead tuples.
  • Track index sizes with pg_relation_size().
  • Use the Boguk formula as a baseline trend.
  • Occasionally validate with pgstattuple.

There are tools too:

Preventing and fixing bloat 🛠️

From experience, a mix of these helps:

  • Autovacuum tuning : Lower thresholds on busy tables so cleanup kicks in earlier.
  • Fillfactor : For frequently updated tables, setting a lower fillfactor (say 80–90%) leaves room for HOT updates.
  • Reindexing : Use REINDEX CONCURRENTLY (Postgres ≥ 12) to shrink indexes without downtime.
  • Partitioning : Breaking a giant write-heavy table into partitions can keep bloat local.
  • Drop unused indexes : If pg_stat_user_indexes.idx_scan = 0 for months, it’s a candidate for removal. Can easily win 40%.

How much does it matter? (🚦 rule of thumb)

  • <20–30% bloat → probably fine, not urgent.
  • > 50% bloat on large tables/indexes → worth attention, because performance will likely degrade.

The key is to watch trends, not just snapshots. A sudden growth spike is often more telling than a single measurement.

Final thoughts

  • Use Boguk’s formula for fast monitoring.
  • Use pgstattuple or ioguix scripts for precise checks.
  • Prevent bloat with autovacuum tuning , reindexing , and dropping unused indexes.

References 📚

Top comments (0)