PostgreSQL Table and Index Bloat

Why this problem matters

MVCC updates and unsuitable fill behavior can contribute to unnecessary table and index growth. A large object is not automatically bloated because the data itself may have grown.

How to diagnose it

Compare table, index, and total relation sizes with row and change trends. Size functions show allocated space but do not precisely classify reusable free space.

The query lists allocated sizes for the largest tables and materialized views. The result is not a bloat percentage or reclaimable-space calculation by itself.

SELECT c.oid::regclass AS relation,
       pg_relation_size(c.oid) AS table_bytes,
       pg_indexes_size(c.oid) AS index_bytes,
       pg_total_relation_size(c.oid) AS total_bytes
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY total_bytes DESC
LIMIT 25;

A safe solution approach

First verify whether growth comes from real data, dead versions, or index design. If rebuilding is needed, assess lock, disk, and WAL impact in a separate maintenance plan.

Why continuous monitoring matters

moon helps keep object-size trends continuously visible with low overhead. Slack, PagerDuty, or webhook alerts can surface unusual growth early.