PostgreSQL Tutorial: Check B-tree index to rebuild

Summary: in this tutorial, you will learn how to check B-tree index to rebuild in PostgreSQL.

Table of Contents

If an index page is full, and an INSERT needs to fit another entry, PostgreSQL can split the page. But if an UPDATE or DELETE removes an index entry, a subsequent VACUUM will leave the page somewhat bloated. PostgreSQL reclaims index pages that become completely empty, but it makes no effort to consolidate sparsely populated pages.

Assessing if you should rebuild a B-tree index

To measure how your B-tree index is doing, use the pgstattuple extension. The function pgstatindex() gives you information about a B-tree index:

• avg_leaf_density is the average percentage to which index pages are filled with entries, or 100 minus the index bloat.
• leaf_fragmentation is the percentage of index pages that are physically after the logically next page on the same index level (the right sibling). It is a measure for how often a range scan has to “jump backward”.

If avg_leaf_density is very low, and particularly if it keeps shrinking, rebuilding the index is indicated. If a high value of leaf_fragmentation is a reason to rebuild the index largely depends on whether you perform larger index range scans or not. I’d say that avg_leaf_density is the more important measure of the two.

Note that pgstatindex() scans the entire index, so it will definitely consume system resources. If you build it into your monitoring system, don’t run that probe every ten minutes. Once a week is probably enough, preferably during times of reduced load on the database.

Check a B-tree index

Let’s create a table with a million rows, and disable autovacuum on it so that we can demonstrate the effects of VACUUM.

CREATE TABLE large_table (id bigint) 
WITH (autovacuum_enabled = off);

INSERT INTO large_table (id)
  SELECT * FROM generate_series(1, 1000000);

CREATE INDEX large_idx ON large_table (id);

CREATE EXTENSION pgstattuple;

SELECT tree_level, deleted_pages, avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('large_idx');
 tree_level | deleted_pages | avg_leaf_density | leaf_fragmentation 
------------+---------------+------------------+--------------------
          2 |             0 |            90.01 |                  0

You can see that the index is in perfect shape. Note that the index depth is only three (two plus the root node), even though the table is not exactly tiny. Let’s delete a third of the rows:

DELETE FROM large_table WHERE id % 3 = 0;

SELECT tree_level, deleted_pages, avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('large_idx');
 tree_level | deleted_pages | avg_leaf_density | leaf_fragmentation
------------+---------------+------------------+--------------------
          2 |             0 |            90.01 |                  0

Nothing has changed, because the index entries are still in place. VACUUM will change that:

VACUUM large_table;

SELECT tree_level, deleted_pages, avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('large_idx');
 tree_level | deleted_pages | avg_leaf_density | leaf_fragmentation 
------------+---------------+------------------+--------------------
          2 |             0 |            60.09 |                  0

Now the bloat has increased as expected. No index page has become empty and could be deleted. leaf_fragmentation didn’t change, because the index pages are still in the original order. If PostgreSQL were to delete and reuse some empty pages, we would see a change there.

Conclusion

No matter what your previous experience with other relational database systems may be, resist the temptation to automatically rebuild indexes regularly. Before you rebuild an index, use pgstatindex() to test if that makes sense. You will find that usually, there is no need for a rebuild.

See more

PostgreSQL Monitoring