丁久

Posted on May 17 • Originally published at dingjiu1989-hue.github.io

Index Maintenance: Bloat, Rebuild, Reindex, and Fillfactor Tuning

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Index Maintenance: Bloat, Rebuild, Reindex, and Fillfactor Tuning

Indexes degrade over time. PostgreSQL's MVCC architecture creates dead index entries that waste space and slow down scans. Regular maintenance keeps indexes healthy and query performance predictable.

Index Bloat

Index bloat occurs when dead tuples leave empty space in index pages. Unlike tables, PostgreSQL does not automatically reuse index page space aggressively.

Measuring Bloat

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Quick bloat estimation using pg_stat_user_indexes

SELECT

indexrelname AS index_name,

relname AS table_name,

idx_scan,

idx_tup_read,

idx_tup_fetch,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM pg_stat_user_indexes

WHERE schemaname = 'public'

ORDER BY pg_relation_size(indexrelid) DESC;

For detailed bloat estimation, the pgstattuple extension provides accurate measurements:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_user_id');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- version, tree_level, index_size, root_block_no, internal_pages, leaf_pages,

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- empty_pages, deleted_pages, avg_leaf_density, leaf_fragmentation

Key metrics:

avg_leaf_density: Below 50% indicates significant bloat.
leaf_fragmentation: High fragmentation slows sequential index scans.
empty_pages + deleted_pages: Pages that are allocated but useless.

External Tools

pg_repack rebuilds indexes without locks:

Rebuild all indexes on a table without blocking writes

pg_repack -h localhost -d mydb --table orders -o idx_orders_user_id

REINDEX

REINDEX rebuilds an index from scratch, eliminating bloat and restoring optimal structure:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Rebuild a single index

REINDEX INDEX idx_orders_user_id;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Rebuild all indexes on a table

REINDEX TABLE orders;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Rebuild all indexes in a schema

REINDEX SCHEMA public;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Rebuild all indexes in a database (offline)

REINDEX DATABASE mydb;

CONCURRENTLY

REINDEX by default takes an ACCESS EXCLUSIVE lock, blocking reads and writes. The CONCURRENTLY option avoids this:

REINDEX INDEX CONCURRENTLY idx_orders_user_id;

Concurrent reindexing creates a new index, starts a new transaction, and drops the old index when complete. It uses more resources (CPU, I/O, temporary disk space) but does not block queries.

\\\\\\\\\\\\

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Index Maintenance: Bloat, Rebuild, Reindex, and Fillfactor Tuning

Index Maintenance: Bloat, Rebuild, Reindex, and Fillfactor Tuning