Offensive Security Researcher & Quantum Cryptography Analyst

How to Use the REINDEX Command in PostgreSQL

Abdul Wahab Junaid
How to Use the REINDEX Command in PostgreSQL

Indexes are what make PostgreSQL fast at finding specific rows without scanning an entire table, but they aren’t immune to problems of their own. Over time, indexes can become bloated, corrupted, or simply stale in ways that hurt performance rather than help it. The REINDEX command is PostgreSQL’s built-in fix for exactly that: it rebuilds an index from scratch, throwing away the old, potentially bloated or corrupted structure and replacing it with a clean one.

In this article, I’ll go through what REINDEX does, its full syntax and options, real examples for different scenarios, and the operational considerations you need to be aware of before running it — especially on production systems.

What Is the REINDEX Command?

REINDEX rebuilds one or more indexes, replacing the old index data with a freshly constructed version built from the current table data. The new index is functionally identical to what the old one represented, but physically it’s brand new: no bloat, no leftover dead entries, and no corruption carried over from whatever caused the problem in the first place.

Indexes typically need rebuilding in a few common scenarios:

Basic Syntax

REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;

Let’s go through each target type.

REINDEX INDEX

Rebuilds a single, specific index.

REINDEX INDEX orders_customer_id_idx;

REINDEX TABLE

Rebuilds every index belonging to a specific table.

REINDEX TABLE orders;

REINDEX SCHEMA

Rebuilds every index in every table within a given schema.

REINDEX SCHEMA public;

REINDEX DATABASE

Rebuilds every index in the entire current database.

REINDEX DATABASE mydb;

REINDEX SYSTEM

Rebuilds only the indexes on PostgreSQL’s internal system catalogs for the current database — useful if catalog-level indexes have become bloated or corrupted, without touching your own application tables.

REINDEX SYSTEM mydb;

Key Options

CONCURRENTLY

This is the single most important option to know about, because it changes the locking behavior dramatically.

REINDEX INDEX CONCURRENTLY orders_customer_id_idx;

By default, REINDEX takes an exclusive lock on the table, blocking both reads and writes for the duration of the rebuild. On a large, busy production table, that can mean real downtime. CONCURRENTLY avoids this by building the new index alongside the old one without blocking normal reads and writes, then swapping it in once complete. It takes longer overall and uses more disk space temporarily (since old and new indexes coexist briefly), but it avoids the hard outage window.

A few caveats with CONCURRENTLY:

VERBOSE

Prints detailed progress information as each index is rebuilt.

REINDEX (VERBOSE) TABLE orders;

TABLESPACE

Lets you rebuild the index into a different tablespace than where it currently lives.

REINDEX (TABLESPACE fast_ssd) INDEX orders_customer_id_idx;

Note: TABLESPACE can’t be combined with CONCURRENTLY in some PostgreSQL versions for certain target types, so check compatibility for your specific version if you need both.

Practical Examples

Example 1: Rebuilding a Single Bloated Index

REINDEX INDEX orders_customer_id_idx;

This is the most surgical option — use it when you’ve identified one specific index as problematic (via bloat monitoring queries, for example) and don’t want to touch the rest of the table’s indexes.

Example 2: Fixing an Invalid Index Left Behind by a Failed Concurrent Build

If CREATE INDEX CONCURRENTLY fails partway through, PostgreSQL leaves an invalid index behind rather than automatically cleaning it up. You can spot these with:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indisvalid = false;

Then rebuild it:

REINDEX INDEX CONCURRENTLY orders_customer_id_idx;

If it still fails, you may need to drop it and recreate it from scratch instead.

Example 3: Rebuilding All Indexes on a Table Without Downtime

REINDEX TABLE CONCURRENTLY orders;

Use this after a period of heavy update/delete churn on a table, when several of its indexes have likely bloated together, and you can’t afford an exclusive lock window.

Example 4: Rebuilding Everything in a Schema After a Bulk Data Migration

REINDEX SCHEMA CONCURRENTLY app_schema;

Handy after a large data migration or bulk load/delete cycle that’s likely bloated multiple indexes across many tables at once.

Example 5: Rebuilding After a Collation Library Upgrade

Operating system collation library updates (glibc version changes, for instance) can silently make text indexes inconsistent with actual sort order, leading to subtle query correctness bugs, not just performance issues. After such an upgrade, rebuilding affected indexes is a recommended precaution:

REINDEX DATABASE CONCURRENTLY mydb;

Example 6: Checking Index Bloat Before Deciding to Reindex

While not part of REINDEX itself, checking for bloat first helps you decide whether it’s actually necessary:

SELECT
    schemaname,
    relname,
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

This won’t directly tell you bloat percentage (that typically requires a more specialized query or extension like pgstattuple), but it gives you a starting point for identifying your largest indexes, which are the ones most worth investigating further.

Common Use Cases

Troubleshooting Common Issues

REINDEX Is Blocking Application Traffic

This is expected with the default (non-concurrent) mode, which takes an exclusive lock. If you’re running this on a production table during business hours, switch to REINDEX ... CONCURRENTLY instead, understanding the trade-off of a longer overall runtime.

“cannot run inside a transaction block” Error

REINDEX CONCURRENTLY cannot be run inside an explicit BEGIN ... COMMIT block, and cannot be run inside a function that wraps it in an implicit transaction. Run it as a standalone statement.

Invalid Index Left Behind After a Failed Concurrent Reindex

Check for it and drop it manually if the rebuild doesn’t succeed on retry:

SELECT indexrelid::regclass FROM pg_index WHERE indisvalid = false;

DROP INDEX CONCURRENTLY invalid_index_name;

Then recreate it, either via CREATE INDEX CONCURRENTLY or another REINDEX attempt.

Reindexing Isn’t Reducing Disk Usage as Expected

Remember that REINDEX rebuilds indexes, not tables. If your table itself (not just its indexes) is heavily bloated, you’d want VACUUM FULL or tools like pg_repack to address table-level bloat — REINDEX alone won’t touch that.

Running Out of Disk Space During a Concurrent Reindex

Because CONCURRENTLY builds a new index alongside the old one before swapping, you temporarily need enough free space for both. Check available space before starting on especially large indexes:

SELECT pg_size_pretty(pg_relation_size('orders_customer_id_idx'));

Best Practices

  1. Default to CONCURRENTLY in production unless you have a confirmed maintenance window where downtime is acceptable — the extra runtime is almost always worth avoiding an outage.
  2. Monitor index bloat proactively rather than waiting for performance complaints; tools like pgstattuple or third-party bloat-estimation queries can help you catch this early.
  3. Check for invalid indexes periodically, especially if your team uses CREATE INDEX CONCURRENTLY regularly, since failed builds silently leave debris behind if not cleaned up.
  4. Reindex after major collation-affecting OS upgrades, since this is a correctness issue, not just a performance one.
  5. Don’t reindex everything reflexively. Target specific bloated or problematic indexes when you can identify them, rather than running blanket REINDEX DATABASE operations that take much longer than necessary.
  6. Combine with ANALYZE after significant reindexing work, to make sure the query planner’s statistics are fresh and consistent with the newly rebuilt structures.
  7. Automate a bloat-check + reindex routine for consistently high-churn tables, rather than relying on someone noticing degraded performance manually.

REINDEX Versus Rebuilding Manually With CREATE INDEX

It’s worth knowing that REINDEX isn’t strictly the only way to rebuild an index. You can accomplish essentially the same outcome manually:

CREATE INDEX CONCURRENTLY orders_customer_id_idx_new ON orders(customer_id);
DROP INDEX CONCURRENTLY orders_customer_id_idx;
ALTER INDEX orders_customer_id_idx_new RENAME TO orders_customer_id_idx;

This manual approach has one meaningful advantage over REINDEX INDEX CONCURRENTLY: the old index remains fully valid and usable right up until the moment you drop it, giving you a natural rollback point if something goes wrong with the new index build. It’s also useful when you want to change the index’s definition slightly (a different column order, an added INCLUDE clause, or a different operator class) rather than rebuilding an identical copy, which is something REINDEX alone can’t do since it always recreates the index with its existing definition.

For a simple like-for-like rebuild, though, REINDEX ... CONCURRENTLY is more convenient, since it’s a single command rather than a three-step manual dance.

Measuring Index Bloat More Precisely

Earlier, this guide mentioned checking index sizes as a rough starting point, but for a more precise bloat estimate, many teams install the pgstattuple extension, which can directly report on wasted space within an index:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('orders_customer_id_idx');

This returns detailed statistics including avg_leaf_density, which gives you a much more concrete picture of how bloated an index actually is compared to simply looking at its total size, which can be misleading on its own since larger tables naturally have larger indexes regardless of bloat.

Automating Reindex Maintenance

For teams managing many tables with high write churn, it’s common to build a small scheduled job (via cron, a task scheduler, or a tool like pg_cron running inside PostgreSQL itself) that identifies bloated indexes and reindexes them automatically during low-traffic windows. A simplified version of this pattern might look like:

-- Using pg_cron, scheduled for 3 AM daily
SELECT cron.schedule(
    'nightly-reindex-check',
    '0 3 * * *',
    $$REINDEX INDEX CONCURRENTLY orders_customer_id_idx$$
);

Before adopting a fully automated approach like this, it’s worth combining it with the bloat-checking queries mentioned earlier so you’re not reindexing healthy indexes unnecessarily — reindexing an index that doesn’t need it wastes resources without any real benefit, even with CONCURRENTLY minimizing the locking impact.

Reindexing System Catalogs Safely

REINDEX SYSTEM deserves a special mention because it behaves a bit differently than the other target types — it always operates non-concurrently on catalog indexes, since PostgreSQL doesn’t currently support concurrent reindexing of system catalogs the same way it does for regular tables. This means it briefly locks catalog access, though this is typically much faster than reindexing a large user table, since catalog tables are usually small relative to application data. Still, it’s worth running this during a quieter period rather than assuming it’s always instant.

Monitoring Reindex Progress in Real Time

For long-running reindex operations on large tables, PostgreSQL exposes a progress-reporting view that lets you watch exactly what phase the operation is in, rather than wondering whether it’s stuck:

SELECT
    pid,
    datname,
    relid::regclass,
    phase,
    blocks_total,
    blocks_done,
    round(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS percent_done
FROM pg_stat_progress_create_index;

Interestingly, REINDEX operations show up in pg_stat_progress_create_index (not a separately named view), since internally a reindex is implemented as building a new index, similar to CREATE INDEX. The phase column tells you exactly where the operation currently stands — for example, building index: scanning table, building index: sorting live tuples, or building index: loading tuples in tree — which is genuinely useful when trying to estimate how much longer a large reindex has left to run.

Reindexing Different Index Types

It’s worth knowing that REINDEX works uniformly across all of PostgreSQL’s index types — B-tree (the default and most common), GIN (often used for full-text search and JSONB containment queries), GiST (used for geometric data and some full-text scenarios), BRIN (block range indexes, common for very large, naturally ordered tables like time-series data), and hash indexes. The command syntax doesn’t change based on index type, but the time and resource cost can vary significantly — GIN indexes in particular are often notably slower to rebuild than an equivalent B-tree index on the same amount of data, due to their more complex internal structure.

REINDEX INDEX CONCURRENTLY orders_search_gin_idx;

If you maintain several GIN indexes for full-text search on a heavily updated table, it’s worth budgeting extra time for reindex operations on those specifically compared to simpler B-tree indexes elsewhere in the same schema.

Combining REINDEX With a Broader Maintenance Routine

In practice, REINDEX rarely happens in complete isolation — it’s usually one piece of a broader maintenance pass alongside VACUUM and ANALYZE. A reasonable maintenance script for a table that’s shown signs of significant bloat might look like:

VACUUM (VERBOSE, ANALYZE) orders;
REINDEX TABLE CONCURRENTLY orders;
ANALYZE orders;

Running VACUUM first cleans up dead tuples in the table itself, which can sometimes reduce how much work the subsequent reindex actually needs to do. Following the reindex with a fresh ANALYZE ensures the planner’s statistics are current relative to both the cleaned-up table and freshly rebuilt indexes, closing the loop on a genuinely thorough maintenance pass rather than addressing just one symptom of bloat in isolation.

When to Reach for REINDEX Versus Other Maintenance Commands

It’s easy to conflate REINDEX, VACUUM, and CLUSTER since they all touch physical storage in one way or another, so it’s worth being clear about when each is actually the right tool. VACUUM cleans up dead tuples within existing structures without rebuilding them. CLUSTER physically reorders an entire table according to an index. REINDEX rebuilds an index’s internal structure without touching the table itself at all. If query performance has degraded and you’re not sure which applies, checking dead tuple counts (pointing toward VACUUM), index bloat specifically (pointing toward REINDEX), or poor physical correlation with a frequently-scanned index (pointing toward CLUSTER) helps narrow down which command actually addresses the root cause rather than reaching for all three reflexively.

Wrapping Up

REINDEX is one of those maintenance commands you might not touch for months, and then suddenly need urgently when an index gets corrupted or bloated beyond what autovacuum can handle. Understanding the difference between the default locking behavior and CONCURRENTLY is the single most important thing to get right — especially in production, where an unplanned exclusive lock on a busy table can cause real user-facing downtime.

Get comfortable identifying bloated or invalid indexes proactively, default to CONCURRENTLY unless you’ve got a real maintenance window, and treat REINDEX as a normal part of ongoing database hygiene rather than an emergency-only tool.

Exit mobile version