Should I Rebuild My PostgreSQL Index? When and How to REINDEX

Few maintenance questions generate as much confusion in the PostgreSQL community as this one: should I rebuild my PostgreSQL index, and if so, how often? Database administrators arriving from other systems frequently ask, "How can I automatically rebuild my indexes regularly?" or "When should I rebuild my indexes in PostgreSQL?" More often than not, these questions point to a solution in search of a problem. The REINDEX command exists for good reasons, and there are legitimate scenarios where rebuilding an index is exactly the right call, but scheduling blanket, periodic rebuilds is rarely one of them. In this guide, we explain when it genuinely makes sense to rebuild my PostgreSQL index, how index bloat and fragmentation actually affect performance when you rebuild my PostgreSQL index, and how to gather the hard data you need to make an evidence-based decision rather than acting on a hunch.  

Why the urge to rebuild my PostgreSQL index is often misguided

The instinct to rebuild indexes on a fixed schedule usually travels with administrators migrating from database platforms where periodic index reorganization was a routine part of housekeeping. PostgreSQL, however, is architected differently. Its multiversion concurrency control model, its autovacuum subsystem, and the way B-tree indexes manage page space mean that a healthy index generally maintains its performance without human intervention. So before you decide to rebuild my PostgreSQL index on a schedule, remember that when someone insists on a recurring job to rebuild every index in the database, they are frequently trying to cure a symptom they have never actually measured. Before you schedule anything, it is worth understanding what an index is, how it can degrade, and whether that degradation is meaningful enough to justify the cost of a rebuild. The decision to rebuild my PostgreSQL index is not free. Rebuilding an index consumes CPU, generates significant write-ahead log traffic, temporarily doubles the storage footprint of the index, and can hold locks that interfere with your workload. If you cannot articulate the specific problem you are solving, the safest and most professional default is to leave the index alone and let PostgreSQL do its job. If you would like expert help deciding whether to rebuild my PostgreSQL index in your environment, our PostgreSQL consulting and support team can review your indexing strategy in depth.

Does the decision to rebuild a PostgreSQL index depend on the index type?

Yes, and considerably so. PostgreSQL offers several index access methods, and their internal implementations differ enough that the reasoning behind a rebuild changes from one to the next. B-tree, hash, and BRIN indexes, for example, store and organize data in fundamentally different ways, so the efficiency considerations that apply to one do not automatically transfer to another. This article focuses primarily on the standard B-tree index, which is by far the most widely used. Most of what follows also applies to the other tree-shaped access methods, namely GIN, GiST, and SP-GiST, and we will call out the differences where they matter. Hash and BRIN indexes behave differently enough that the "should I rebuild" conversation takes a separate shape for them.

How can a tree-like index deteriorate?

Many people carry a vague mental picture of "defragmentation" when they think about rebuilding a data structure for better efficiency. To decide sensibly whether to rebuild a PostgreSQL index, we need a more precise understanding of how a tree-shaped index is laid out and where its performance can actually erode. Like everything else in PostgreSQL, the unit of storage for an index is the 8 kB page. In every tree-shaped index, a search begins at the root page, which divides the search space into segments. In a B-tree index, where entries are kept in sorted order, the search looks for the largest entry smaller than your value and the smallest entry larger than it; the gap between them points to the correct page on the next level down. This procedure repeats until the search reaches a leaf page, at which point PostgreSQL follows the item pointers to the corresponding rows in the table.

The importance of densely packed index pages

The performance of this search procedure is inversely proportional to the depth of the index, that is, the number of levels it contains. Fewer levels mean fewer page accesses per lookup, so a shallow index is a fast index. The depth shrinks as the fan-out grows, where fan-out is the number of entries that fit on a single index page. This is the crucial insight for anyone asking whether they should rebuild a PostgreSQL index: performance is driven by how densely the index pages are packed, because dense pages hold more entries, which raises the fan-out, which lowers the depth. A rebuild is worthwhile only insofar as it meaningfully increases that density, and, as we will see, that condition is met far less often than intuition suggests. Consider the arithmetic. A B-tree index whose pages are, on average, only half full needs roughly twice as many leaf pages as a perfectly packed one. Yet because each additional level multiplies the fan-out, even a substantial amount of wasted space on the leaves may not add a single level to the tree. An index that would be three levels deep when perfectly packed very often remains three levels deep even when it carries a great deal of empty space. That is precisely why rebuilding to reclaim space frequently produces no measurable improvement in lookup latency.

The small impact of bloat on scanning for a single table row

When you look up a single row through a unique index, the query descends from the root to a leaf and follows one item pointer to the table. The number of pages touched equals the depth of the tree plus one heap access. Because the depth is so resistant to change, moderate bloat has an almost negligible effect on this kind of point lookup. Whether the index is tightly packed or carries 50 percent empty space, the traversal still visits the same handful of pages. For workloads dominated by primary-key lookups and other single-row access patterns, the case to rebuild my PostgreSQL index to fight bloat is exceptionally weak. This is one of the most common misunderstandings we encounter when clients ask us whether they should rebuild my PostgreSQL index during production tuning engagements. Teams observe a large index-to-table size ratio, assume it is hurting them, and schedule a rebuild, only to find that query latency is unchanged afterward. The metric they measured was real, but it was not the metric that governs their actual workload.

Index range scans are more affected by bloat and fragmentation

Range scans are where fragmentation begins to matter. When a query reads a contiguous range of index entries, PostgreSQL walks along the leaf level following the sibling pointers that connect one leaf page to the next. If the index is densely packed and its leaf pages sit close together on disk, this walk is efficient and largely sequential. If the index is heavily bloated, the same range is spread across many more leaf pages, so the scan reads more blocks to return the same rows. Fragmentation compounds the problem: when leaf pages are scattered physically rather than laid out in logical order, the storage system does more random I/O instead of fast sequential reads. So if you have a workload that leans heavily on large index range scans and you are weighing whether to rebuild my PostgreSQL index, and you can demonstrate that the index has grown badly bloated over time, you have a far stronger argument to rebuild my PostgreSQL index than someone whose workload is all point lookups. The key phrase remains "you can demonstrate." The decision should rest on measurement, not on a general uneasiness about index size.

How index bloat happens, and when it makes a rebuild attractive

To understand bloat, contrast an index with a heap table. In a table, PostgreSQL can place a new row on almost any page that has room, so free space is used opportunistically. In a tree-shaped index, an entry must live on a specific page determined by its key value. When an index page is full and an INSERT needs to add another entry there, PostgreSQL splits the page into two. When an UPDATE or DELETE removes an index entry, a later VACUUM cleans up the dead pointer, but the page is left partly empty. PostgreSQL will reclaim an index page that becomes completely empty, but it makes no effort to merge two sparsely populated neighboring pages back into one. This is why a certain amount of bloat is not merely normal but expected. It is entirely ordinary for a B-tree index to carry more bloat than its underlying table, which is why you should not rush to rebuild my PostgreSQL index. The default fillfactor of 90 deliberately leaves a B-tree index about 10 percent empty from the moment it is built, precisely so that future inserts have somewhere to go without triggering constant page splits. In practice, it is not unusual for the bloat in a B-tree index to climb toward 70 percent and stabilize there. That figure alone is not a reason to rebuild the index. What genuinely warrants attention is a different pattern: bloat that keeps climbing steadily over time and never levels off. Here is the crucial nuance. If an index sits at 60 percent bloat and you rebuild it, it may perform marginally better for a short while, but it will typically drift right back toward its previous steady state as the workload continues. You will have spent real resources for a temporary and largely cosmetic gain. The decision to rebuild my PostgreSQL index pays off when the index has reached a genuinely anomalous level of bloat that it will not otherwise recover from, for instance after a bulk deletion that removed most of the rows, or when a monitoring trend shows relentless, unbounded growth rather than a plateau.

How to rebuild my PostgreSQL index: assessing a B-tree index first

The professional way to decide whether to rebuild my PostgreSQL index is to measure it rather than guess. PostgreSQL ships with the pgstattuple extension, which provides functions that report the physical characteristics of tables and indexes, including how densely their pages are packed. For B-tree indexes specifically, the pgstatindex() function gives you exactly the numbers you need to reason about bloat and leaf-page density. Start by installing the extension in the database you want to inspect.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Using pgstatindex() before you rebuild my PostgreSQL index

Once the extension is installed and you are ready to rebuild my PostgreSQL index, you can call pgstatindex() on any B-tree index and read off its structure. Suppose you have an index named orders_customer_id_idx that you suspect has grown bloated. Query it like this.

SELECT * FROM pgstatindex('orders_customer_id_idx');

The function returns a single row with several columns. The most important for our purposes are tree_level, which tells you the depth of the B-tree; avg_leaf_density, which is the average percentage fill of the leaf pages; and leaf_fragmentation, which estimates how much the physical ordering of the leaf pages diverges from their logical ordering. A high avg_leaf_density, for example 85 to 90 percent, indicates a healthy, tightly packed index for which a rebuild would accomplish nothing. A low density combined with high fragmentation on an index that serves heavy range-scan traffic is the signal that you may want to rebuild my PostgreSQL index.

SELECT
    c.relname AS index_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    stat.avg_leaf_density,
    stat.leaf_fragmentation
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
JOIN pg_am am ON am.oid = c.relam
CROSS JOIN LATERAL pgstatindex(c.oid) AS stat
WHERE am.amname = 'btree'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20;

With this data in front of you, the decision to rebuild my PostgreSQL index stops being a matter of superstition and becomes a matter of evidence. You are looking for the combination of low leaf density, high fragmentation, a workload that actually performs range scans, and, ideally, a monitoring history showing that the situation is deteriorating rather than holding steady.

How to rebuild a PostgreSQL index safely with REINDEX

When your measurements genuinely justify the decision to rebuild my PostgreSQL index, the tool to rebuild my PostgreSQL index is the PostgreSQL REINDEX command. In its simplest form, you rebuild my PostgreSQL index for a single index by name. This rewrites the index from scratch, producing a fresh, tightly packed structure.

REINDEX INDEX orders_customer_id_idx;

The catch is that a plain REINDEX takes a lock that blocks writes to the table, and in many cases reads as well, for the duration of the rebuild. On a busy production system, that is often unacceptable. This is where REINDEX INDEX CONCURRENTLY comes in. It builds a replacement index alongside the original, then swaps them in with only a brief lock, so your application keeps running throughout.

REINDEX INDEX CONCURRENTLY orders_customer_id_idx;

When you rebuild my PostgreSQL index online, the concurrent variant is slower overall and needs enough disk space to hold both the old and new copies of the index at once, but for online systems it is almost always the right choice. You can also reindex an entire table or an entire schema, and the same concurrency option applies, though the larger the scope, the more carefully you should schedule the operation.

REINDEX TABLE CONCURRENTLY orders;

Should I rebuild my PostgreSQL index for other index types?

When you rebuild my PostgreSQL index, the reasoning above is tuned to B-tree indexes, but the same measurement-first discipline applies to the other tree-shaped access methods. GIN indexes, widely used for full-text search and JSONB querying, have their own pending-list mechanism and can benefit from a rebuild in specific circumstances, but again only when you can point to a concrete problem. GiST and SP-GiST indexes follow the general tree logic, so densely packed pages and shallow depth remain the goals. Hash indexes and BRIN indexes sit outside the tree family altogether. When you consider whether to rebuild my PostgreSQL index of a non-standard type, BRIN indexes in particular are so compact that bloat is rarely a meaningful concern; a summarize operation is usually more relevant than a full rebuild. In every case, the same question applies: what measurable problem is the rebuild meant to solve?

Should you automate regular index rebuilds in PostgreSQL?

We can now answer the two questions we began with about whether to rebuild my PostgreSQL index directly and with confidence. Should you automatically rebuild your indexes on a regular schedule? In the overwhelming majority of cases, no. Blanket periodic rebuilds burn CPU, flood the write-ahead log, consume I/O bandwidth, and risk locking issues, all in exchange for benefits that are usually temporary or entirely imaginary. A healthy autovacuum configuration does far more for index health than any cron job that mechanically reindexes everything each night. When should you rebuild your indexes in PostgreSQL? When measurement tells you to. The right pattern for anyone wondering when to rebuild my PostgreSQL index is monitoring, not scheduling. When you monitor rather than blindly rebuild my PostgreSQL index, you track index size and density over time, watch for indexes whose bloat grows without bound rather than settling at a plateau, pay special attention to indexes that serve heavy range-scan workloads, and rebuild after events such as massive bulk deletions that leave an index anomalously sparse. If you must automate anything, automate the measurement and alerting, then let a human confirm that a specific index has crossed a threshold that actually correlates with a workload it serves. That is the difference between maintenance driven by evidence and maintenance driven by anxiety.

Key takeaways on rebuilding a PostgreSQL index

" Index depth, not raw size, governs lookup performance, and depth is remarkably stable even under considerable bloat. Point lookups barely notice bloat, while large range scans are the workloads that truly suffer from fragmentation. A steady level of bloat, even as high as 70 percent, is normal and self-correcting through reuse; only unbounded, ever-increasing bloat or an anomalous spike after bulk deletion justifies intervention. Use the pgstattuple extension and pgstatindex() to gather real numbers, and when a rebuild is warranted, reach for REINDEX ... CONCURRENTLY to avoid disrupting production. Above all, replace the reflex to schedule regular rebuilds with disciplined monitoring, so that every rebuild you perform is one you can justify with data.