Introduction
Indexes are one of the main reasons PostgreSQL can find rows without scanning an entire table. A well-designed index can make a large difference to query performance, especially when applications repeatedly search, join, sort, or filter on the same columns.
But an index can become less useful over time.
The problem is not always that the index itself is broken. PostgreSQL indexes can become inefficient because of table growth, heavy update and delete activity, index bloat, poor index design, changing query patterns, or outdated statistics.
When a query becomes slower, rebuilding an index may be one possible solution. It should not, however, be the first command you run.
Before rebuilding anything, you need to determine whether the index is actually the problem.
This article explains how to identify inefficient PostgreSQL indexes, when rebuilding makes sense, which rebuilding options are available, and how to perform the operation safely on a production database.
Why PostgreSQL Indexes Can Become Inefficient
PostgreSQL uses indexes to reduce the amount of data that needs to be examined.
For example:
SELECT *
FROM orders
WHERE customer_id = 1250;
If an appropriate index exists:
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
PostgreSQL can potentially locate the required rows through the index instead of scanning the entire orders table.
Over time, however, the physical structure of an index can become less efficient.
Common causes include:
- Frequent
UPDATE operations - Frequent
DELETE operations - Large amounts of inserted data
- Changing data distribution
- Table and index growth
- Dead tuples
- Index bloat
- Poorly chosen indexes
- Indexes that are no longer useful
- Queries that have changed over time
- Insufficient maintenance
- Long-running transactions preventing cleanup
An important distinction is that not every slow index needs to be rebuilt.
A query can be slow because the planner chooses the wrong plan, statistics are stale, the table is poorly indexed, or the query itself has become more expensive.
Start by Checking the Indexes
Before rebuilding anything, list the indexes on the affected table.
For example:
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY indexname;
This gives you a basic inventory.
You should understand:
- Which columns are indexed
- Whether the index is unique
- Whether it is a partial index
- Whether it uses an expression
- Whether it is a primary key or constraint index
- Whether similar indexes already exist
For a production database, this inventory is important because blindly rebuilding indexes can waste maintenance time and create unnecessary database activity.
Check Whether the Index Is Actually Being Used
An index existing on a table does not mean PostgreSQL is using it.
The first step is to inspect the execution plan.
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1250;
You might see:
Index Scan using idx_orders_customer_id on orders
That indicates the planner selected the index.
You might instead see:
Seq Scan on orders
That does not automatically mean the index is inefficient.
PostgreSQL may correctly choose a sequential scan when a large percentage of the table needs to be read.
For example, if a query returns 70 percent of a table, using an index can involve many random page accesses and may be more expensive than reading the table sequentially.
Use EXPLAIN ANALYZE to Measure the Real Behavior
When it is safe to execute the query, use:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 1250;
This provides actual execution information.
For example:
Index Scan using idx_orders_customer_id on orders
(cost=0.43..250.00 rows=100)
(actual time=0.050..12.800 rows=8500)
Buffers: shared hit=2400
This tells you much more than the fact that an index was selected.
Pay attention to:
- Actual execution time
- Estimated rows
- Actual rows
- Shared buffer hits
- Shared buffer reads
- Rows returned
- Scan type
A large difference between estimated and actual rows can indicate a statistics problem rather than an index problem.
Check Index Size
Large indexes deserve closer investigation.
You can inspect index sizes with:
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
This helps identify indexes consuming significant disk space.
Large size alone is not evidence of a problem.
A large table can legitimately have a large index.
The useful question is whether the index size is reasonable compared with the amount of useful data it represents.
Check Index Usage Statistics
PostgreSQL provides index usage statistics through pg_stat_user_indexes.
For example:
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
An index with a high idx_scan count is being used frequently.
An index with very low usage may deserve review.
For example:
index_name idx_scan
------------------------- --------
idx_orders_customer_id 850000
idx_orders_status 420000
idx_orders_old_reference 3
The last index may be a candidate for investigation.
But be careful with usage statistics.
A low usage count does not automatically mean an index should be removed. Some indexes support infrequent but critical operations.
For example, an index used only during an end-of-month financial process may still be important.
Understand Index Bloat
Index bloat occurs when the physical index structure contains space that is not being used efficiently.
Heavy updates and deletes can contribute to this problem.
Consider a table where rows are repeatedly updated:
UPDATE
UPDATE
DELETE
INSERT
UPDATE
DELETE
Over time, the table and its indexes can contain unused or reusable space.
PostgreSQL's normal maintenance processes help manage dead tuples, but they do not mean every index remains perfectly compact.
This is where rebuilding may become useful.
However, measuring bloat is more useful than guessing.
Check Dead Tuples in the Table
Before rebuilding an index, check the related table:
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
A large number of dead tuples can indicate that maintenance activity is important.
For example:
n_live_tup 50000000
n_dead_tup 12000000
This should prompt further investigation.
Do not immediately conclude that the index needs rebuilding.
First check whether autovacuum is keeping up and whether long-running transactions are preventing cleanup.
VACUUM and REINDEX Solve Different Problems
This distinction is important.
VACUUM primarily handles dead tuples and makes reusable space available to PostgreSQL.
REINDEX rebuilds an index.
They are not interchangeable.
For example:
VACUUM ANALYZE orders;
can help refresh statistics and reclaim reusable table space.
While:
REINDEX INDEX idx_orders_customer_id;
rebuilds the specified index.
If the underlying problem is stale statistics, rebuilding the index may accomplish nothing.
If the problem is index corruption or significant index bloat, rebuilding may be appropriate.
Rebuild a Single Index
The simplest form is:
REINDEX INDEX idx_orders_customer_id;
This is useful when you have identified one specific index that needs rebuilding.
It is generally preferable to rebuilding every index on a table when only one index has a problem.
You can also rebuild all indexes for a table:
REINDEX TABLE orders;
This can be much more expensive because every index on the table is rebuilt.
Use it only when there is a clear reason.
Use REINDEX CONCURRENTLY for Production Workloads
One of the most important considerations for production systems is locking.
A regular:
REINDEX INDEX idx_orders_customer_id;
can require a lock that interferes with concurrent activity on the affected table or index.
For systems where availability matters, PostgreSQL provides:
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
This is designed to reduce blocking of normal table activity.
It does not mean the operation is free.
Concurrent index rebuilding can still consume CPU, memory, disk I/O, and time.
It also has additional operational considerations compared with a normal REINDEX.
For a busy production database, the important question is not simply:
"Can I rebuild this index?"
It is:
"Can I rebuild this index without creating a larger production problem?"
Compare REINDEX and REINDEX CONCURRENTLY
| Operation | Blocking | Speed | Production Impact | Typical Use |
|---|
| REINDEX INDEX | Higher locking impact | Usually faster | Higher | Maintenance window |
| REINDEX TABLE | Higher locking impact | Usually faster | Higher | Rebuild all table indexes |
| REINDEX INDEX CONCURRENTLY | Lower blocking | Usually slower | Lower | Busy production systems |
| REINDEX TABLE CONCURRENTLY | Lower blocking | Usually slower | Lower | Rebuild table indexes with availability requirements |
The correct choice depends on workload, maintenance windows, database size, and availability requirements.
Check the Index Before and After Rebuilding
Do not stop after running REINDEX.
Capture useful information before the operation.
For example:
SELECT
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders';
After rebuilding, collect the same information.
Then compare:
- Index size
- Query execution time
- Buffer usage
- Query plan
- Index usage
- Disk activity
A smaller index is useful evidence, but the real test is whether important queries actually improved.
Check Query Plans After Rebuilding
Run the affected query again:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 1250;
Compare the result with the baseline.
You might see that the plan remains:
Index Scan using idx_orders_customer_id on orders
but execution time falls because the index structure is more efficient.
You might also find that nothing changed.
That result is important.
If rebuilding the index does not change query performance, the problem may not have been index bloat.
Do Not Rebuild an Index Just Because It Is Large
Consider these two indexes:
idx_customers_id 2 GB
idx_orders_customer 8 GB
The second index is larger, but that does not make it inefficient.
The size may simply reflect the amount of indexed data.
A better investigation asks:
- Is the index used?
- How often is it used?
- Are queries slower?
- Is the index bloated?
- Are there dead tuples?
- Is the planner choosing another path?
- Is the index definition appropriate?
- Has the data distribution changed?
Only then should rebuilding become a candidate.
Check for Duplicate or Overlapping Indexes
Sometimes the problem is not that an index is inefficient. The database has too many indexes.
For example:
CREATE INDEX idx_orders_customer
ON orders(customer_id);
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
The second index may already support some queries that use customer_id.
That does not mean the first index is always redundant, but it deserves investigation.
Too many indexes have costs:
- Additional disk usage
- More write overhead
- More maintenance work
- More vacuum activity
- Longer insert and update operations
Before rebuilding an index, check whether the index itself is still necessary.
Check Composite Indexes Carefully
Column order matters in a multi-column index.
For example:
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
This index is useful for queries that filter by customer_id and may also help with queries that filter only on the leading column.
But it is not equivalent to:
CREATE INDEX idx_orders_status_customer
ON orders(status, customer_id);
If query patterns change, an existing index can become less useful even though the index is physically healthy.
In that situation, rebuilding the existing index will not solve the underlying problem.
The index definition may need to be redesigned.
Partial Indexes Can Be a Better Solution
Suppose an application frequently queries only active orders:
SELECT *
FROM orders
WHERE status = 'ACTIVE';
Instead of indexing every row:
CREATE INDEX idx_orders_status
ON orders(status);
a partial index may be appropriate:
CREATE INDEX idx_orders_active
ON orders(customer_id)
WHERE status = 'ACTIVE';
The smaller index can be more efficient for the specific workload.
Again, this is an index design problem, not necessarily an index rebuilding problem.
Check Long-Running Transactions
Long-running transactions can interfere with cleanup.
For example:
SELECT
pid,
usename,
state,
xact_start,
query_start,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
A transaction that has remained open for a long time can prevent PostgreSQL from removing row versions that are still potentially visible to that transaction.
This can contribute to table and index growth.
If you repeatedly need to rebuild indexes because they grow quickly, investigate the workload causing the growth.
A rebuild may only hide the underlying problem temporarily.
Check Autovacuum Configuration
Autovacuum plays an important role in keeping PostgreSQL tables healthy.
You can inspect table-level settings with:
SELECT
relname,
reloptions
FROM pg_class
WHERE relname = 'orders';
You can also inspect global settings:
SHOW autovacuum;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_analyze_threshold;
For high-write tables, default thresholds may not be appropriate.
Instead of repeatedly rebuilding indexes, it may be better to tune vacuum behavior for the workload.
Rebuilding an Index Is Not the Same as Recreating It
There are several ways to change an index.
For example:
DROP INDEX idx_orders_customer_id;
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
This removes the index before creating it again.
That approach can create a period where the index does not exist and can create unnecessary risk.
REINDEX is designed specifically for rebuilding an existing index.
When the goal is simply to rebuild an index, prefer the operation intended for that purpose rather than manually dropping and recreating it.
What About REINDEX DATABASE?
PostgreSQL also supports:
REINDEX DATABASE my_database;
This rebuilds indexes in the database.
This is a much broader operation than rebuilding one problematic index.
Do not use it as a generic performance maintenance command.
If only one index has been identified as problematic, rebuild that index.
Broad maintenance operations should have a specific reason and a controlled maintenance plan.
Check for Index Corruption
One reason to rebuild an index is suspected corruption.
If PostgreSQL reports an index-related error, investigate the exact error before deciding what to do.
A corruption problem is different from normal bloat.
Do not treat every slow query as an index corruption problem.
For suspected corruption, review:
- PostgreSQL logs
- Database errors
- Index definitions
- Recent hardware or storage issues
- Recovery events
- Replication behavior
- Backup and restore results
A rebuild may resolve some index-level problems, but the underlying infrastructure issue still needs investigation.
Production-Safe Rebuild Workflow
For a production database, use a controlled process.
Step 1: Identify the Slow Query
Start with application monitoring or pg_stat_statements.
Step 2: Inspect the Execution Plan
Use:
EXPLAIN (ANALYZE, BUFFERS)
when execution is safe.
Step 3: Identify the Index
Determine which index is being used or should be used.
Step 4: Check Index Usage
Review:
pg_stat_user_indexes
Step 5: Check Table Maintenance
Review:
pg_stat_user_tables
Look at dead tuples, vacuum activity, and analyze activity.
Step 6: Check the Index Size
Determine whether the index is unusually large relative to the table and workload.
Step 7: Decide Whether Rebuilding Is Justified
Do not rebuild solely because the index is old or large.
Step 8: Choose the Rebuild Method
For a maintenance window:
REINDEX INDEX idx_orders_customer_id;
For a busy production environment:
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
Step 9: Monitor the Operation
Watch:
- CPU
- Disk I/O
- Database connections
- Query latency
- Lock activity
- Replication impact
- Available disk space
Step 10: Validate the Result
Run the affected query again and compare its execution plan and runtime.
Common Mistakes
Rebuilding Every Index
A blanket REINDEX can create unnecessary work.
Start with the affected index.
Assuming Large Means Bad
Large indexes can be perfectly healthy.
Size is only one piece of evidence.
Ignoring Statistics
Bad row estimates can cause poor plans even when the index is healthy.
Run ANALYZE when appropriate.
Ignoring Query Changes
A query that used to benefit from an index may no longer need it after application changes.
Review the SQL before rebuilding the index.
Rebuilding During Peak Traffic
Index rebuilding can consume significant resources.
Schedule it carefully or use concurrent rebuilding when appropriate.
Forgetting Disk Space
Concurrent index rebuilding can temporarily require additional disk space.
Check available storage before starting.
Rebuilding Repeatedly
If an index becomes inefficient again soon after rebuilding, investigate why.
Repeated rebuilds may indicate:
- High write activity
- Autovacuum problems
- Long-running transactions
- Poor workload design
- Excessive indexing
- Data churn
The repeated rebuild is a symptom, not necessarily the solution.
Advantages of Rebuilding PostgreSQL Indexes
When rebuilding is actually justified, it can provide several benefits:
- Removes inefficient index structure
- Can reduce index size
- Can improve index scan efficiency
- Can address certain index corruption problems
- Provides a clean index structure
- Can restore performance when index bloat is the underlying issue
The biggest benefit comes from rebuilding a specific index for a demonstrated reason.
Disadvantages and Trade-Offs
Index rebuilding also has costs:
- CPU usage
- Disk I/O
- Additional disk space requirements
- Locking considerations
- Longer maintenance windows
- Increased replication workload
- Possible impact on application latency
- Operational complexity for large indexes
Concurrent rebuilding reduces blocking but generally takes longer and still consumes database resources.
A Practical Index Maintenance Checklist
Before rebuilding an index, check:
- Is the query actually slow?
- Is the index involved in the query plan?
- Is the execution plan appropriate?
- Are estimated and actual rows close?
- Is the table statistics current?
- Is the index heavily used?
- Is the index unusually large?
- Is there evidence of index bloat?
- Are there many dead tuples?
- Is autovacuum keeping up?
- Are long-running transactions preventing cleanup?
- Is the index definition still appropriate?
- Are there overlapping indexes?
- Is there enough disk space?
- Is the database under heavy production load?
- Would
REINDEX CONCURRENTLY be safer? - Will the rebuild be monitored?
- Will query performance be measured afterward?
Final Thoughts
Rebuilding a PostgreSQL index can solve a real performance problem, but it should not become a routine response to every slow query.
Start with evidence.
Look at the execution plan, query statistics, index usage, table statistics, dead tuples, index size, and workload. Determine whether the problem is actually related to the physical index structure.
If the index needs to be rebuilt, choose the narrowest operation that solves the problem. Rebuilding one index is usually preferable to rebuilding every index on a table, and REINDEX CONCURRENTLY can be useful when reducing blocking is important.
Most importantly, measure the result.
A successful index rebuild is not defined by the command completing successfully. It is defined by the application query performing better afterward without creating a new operational problem.
If the same index repeatedly needs rebuilding, stop treating the rebuild as the solution and investigate the workload, autovacuum behavior, transaction patterns, and index design that are causing the problem in the first place.
Join the conversation! Your thoughts help the community grow.