阅读中文版本

Measure PostgreSQL Index Bloat

Use pgstattuple and statistics to estimate index bloat and decide whether a REINDEX is worth the lock cost.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('public.idx_events_created');

SELECT pg_size_pretty(pg_relation_size('idx_events_created')) AS index_size,
       pg_size_pretty(pg_relation_size('events')) AS table_size;

What this recipe does

Quantify index bloat with pgstattuple before scheduling a REINDEX.

Indexes seem much larger than the data they cover, but no one can say how much space is actually wasted.

Prerequisites

• The pgstattuple extension installed
• Superuser or monitoring role
• A candidate index

Procedure

1. Install the extension and sample the index.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('public.idx_events_created');

Expected result: The result reports leaf density and free space, giving a bloat estimate.

2. Compare index size with the table it supports.

SELECT pg_size_pretty(pg_relation_size('idx_events_created')) AS index_size,
       pg_size_pretty(pg_relation_size('events')) AS table_size;

Expected result: An index close to the table size for a low-cardinality predicate signals bloat.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the measured bloat ratio justifies a REINDEX and available space covers the concurrent rebuild.

Troubleshooting

pgstattuple takes a long time — Use pgstatindex for a faster estimate or run it during low traffic.

Bloat is real but REINDEX locks — Consider CONCURRENTLY or partition-wise rebuilds to avoid long locks.

Limitations

• Estimates can be noisy on actively written indexes.
• REINDEX CONCURRENTLY needs extra disk and time.
• Does not cover other bloat sources such as table bloat.