Practical guide: rebuild PostgreSQL indexes in production using CREATE INDEX CONCURRENTLY and partial indexes (safe step‑by‑step)

By Kevin Marcondes · Published on Oct 28, 2026 · 10 min read

This practical guide shows when to apply each technique and how to prepare the environment, run operations safely, and validate results when rebuilding PostgreSQL indexes in production using CREATE INDEX CONCURRENTLY and partial indexes. You’ll find prerequisites, defensive steps, monitoring, rollback instructions and notes about UNIQUE/PK constraints and replicas — all aimed at reducing locks and avoiding plan regressions on active tables.

When to rebuild an index and signs it’s worthwhile

Rebuilding indexes can recover lost performance due to bloat, fix inadequate definitions and reduce I/O. In production, the challenge is doing that with minimal impact.

Look for these signs before acting: degrading queries, more sequential scans than expected, indexes larger than the table itself, and increasing VACUUM time.

If you suspect corruption or broken statistics, prioritize integrity before swapping indexes. See the dedicated recovery guide at recover PostgreSQL indexes and statistics.

Changes in filtering patterns over time create underused indexes. A well‑designed partial index can reduce bloat and improve selectivity. Coordinate schema changes and deploys with your migrations and rollbacks in PostgreSQL.

To keep the intent of this document clear: this practical guide to rebuilding PostgreSQL indexes in production using CREATE INDEX CONCURRENTLY and partial indexes is what you’ll apply here.

• Queries changed and the current index no longer covers the main filters.
• Index grew excessively after many updates/deletes (bloat).
• Planner avoids the index due to low selectivity.
• Need to make a populated column UNIQUE.

How CREATE INDEX CONCURRENTLY reduces impact (and when not to use it)

CREATE INDEX CONCURRENTLY (CIC) avoids long read/write locks. It scans the table multiple times to capture concurrent changes and only takes brief locks in the final phase.

Important limitations: CIC is slower than a regular CREATE INDEX, requires extra disk space and cannot run inside a transaction block. Do not use it in very short maintenance windows without margin, since duration varies with load.

For existing constraints (PK/UNIQUE) or when you need to reindex without changing names, consider REINDEX CONCURRENTLY. For mixed heavy reporting workloads, combine CIC with practices from PostgreSQL hardening and tuning for ERPs.

• Avoids prolonged blocking of SELECT/INSERT/UPDATE/DELETE.
• Consumes more I/O and time due to multiple scans.
• Requires extra space roughly equal to the target index size.
• Failures can leave an invalid index that must be removed.

Pre‑run checklist: safety, space and concurrency

Before creating a concurrent index, validate autovacuum health, free disk space and long sessions. CIC cannot run inside a TRANSACTION and can generate lots of WAL, increasing replica lag.

Set reasonable time limits for your session, schedule a swap window and ensure no conflicting migrations are pending. See also how to organize migrations and rollbacks in PostgreSQL.

If your database experiences connection spikes, align timeouts and the maintenance window to avoid impact during the rebuild; refer to the guidance on too many connections in PostgreSQL.

Use the queries below as a checklist. Replace table/column names for your case.

• Confirm free space for the new index and extra WAL.
• Check for long sessions and potential locks.
• Search for duplicates before creating a UNIQUE index.
• Adjust maintenance_work_mem to speed up index build.

-- Ajustes temporários de tempo (opcional)
SET lock_timeout = '3s';
SET statement_timeout = '1h';

-- 1) Tamanho da tabela e de índices
SELECT 
  pg_size_pretty(pg_relation_size('public.my_table')) AS table_size,
  pg_size_pretty(pg_indexes_size('public.my_table'))  AS indexes_size;

-- 2) Sessões e transações longas
SELECT pid, usename, xact_start, now()-xact_start AS age, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY age DESC;

-- 3) Lag de replicação (se houver standbys)
SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 4) Procure duplicados antes de UNIQUE
-- Substitua col1, col2 pelas colunas almejadas
SELECT col1, col2, COUNT(*)
FROM public.my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1
LIMIT 50;

Step‑by‑step: rebuild an existing index with imperceptible downtime

General strategy: create a new index with CREATE INDEX CONCURRENTLY, validate it, drop the old one and optionally rename the new index to keep naming conventions. On very active tables, favor off‑peak hours.

Suggested flow: create the new index, check validity, drop the old index with DROP INDEX CONCURRENTLY and rename. Avoid IF NOT EXISTS with ambiguous definitions; prefer explicit names and prior review.

After swapping, monitor usage of the new index and keep the old one for a short period only if you need a quick revert. If concurrent jobs cause locks, review your cron jobs according to the guide on locks and deadlocks in PostgreSQL.

Reinforcing the goal: this practical guide to rebuilding PostgreSQL indexes in production using CREATE INDEX CONCURRENTLY and partial indexes helps reduce locks while keeping predictability.

• Name the new index with a _new suffix for clarity.
• Validate indisvalid before swapping.
• DROP INDEX CONCURRENTLY the old index to avoid blocking.
• Rename the new index if the original name is referenced by queries or tools.

-- 1) Criar o novo índice (definição equivalente ao antigo)
CREATE INDEX CONCURRENTLY idx_my_table_created_at_new
  ON public.my_table (created_at);

-- 2) Verificar que o índice ficou válido
SELECT i.relname, idx.indisvalid
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
WHERE i.relname = 'idx_my_table_created_at_new';

-- 3) Remover o antigo de forma não bloqueante
DROP INDEX CONCURRENTLY IF EXISTS idx_my_table_created_at;

-- 4) (Opcional) Padronizar o nome
ALTER INDEX IF EXISTS idx_my_table_created_at_new
  RENAME TO idx_my_table_created_at;

UNIQUE and PK: safe swap with minimal locks

To make a populated column UNIQUE without blocking, create the unique index CONCURRENTLY and then attach it as a constraint. Adding/removing constraints requires a quick table lock, so plan the window accordingly.

If there’s an existing problematic PK/UNIQUE, REINDEX CONCURRENTLY may be more straightforward. When you need to change the key columns, create a new UNIQUE CONCURRENTLY and switch the constraint to use that index.

Remove duplicates before attempting UNIQUE; if duplicates exist the command fails and leaves an invalid index that must be dropped.

• Create the UNIQUE index CONCURRENTLY first.
• Swap the constraint to use the new index.
• Expect a brief lock when altering constraints.
• Deduplicate beforehand to avoid failures.

-- 1) Criar índice único de forma concorrente
CREATE UNIQUE INDEX CONCURRENTLY idx_my_table_email_uq_new
  ON public.my_table (email);

-- 2) Trocar a constraint para usar o novo índice (lock breve)
ALTER TABLE public.my_table
  DROP CONSTRAINT IF EXISTS my_table_email_key,
  ADD CONSTRAINT my_table_email_key
    UNIQUE USING INDEX idx_my_table_email_uq_new;

-- Observação: se a constraint antiga existia, o DROP pode remover
-- o índice antigo automaticamente. Limpeza manual só se necessário.

Partial indexes: when they shine and how to design them

Partial indexes cover a subset of rows defined by a WHERE predicate. They excel when most queries filter by a specific state, reducing size and bloat.

Practical rules: the index condition should reflect the most common filter. The planner will use the index only if the query predicate implies the index predicate. Prefer simple expressions and immutable functions.

Validate real filter patterns in your workload. A poorly chosen partial index can become dead weight. For intense reporting scenarios, align index design with query tuning and hardware considerations — see PostgreSQL hardening and tuning for ERPs.

• Use when a few states are queried frequently.
• Keep predicates simple, targeting direct columns.
• Avoid volatile functions in the predicate.
• Ensure queries repeat the filter exactly.

-- Exemplo: foco em pedidos ativos e não deletados
CREATE INDEX CONCURRENTLY idx_orders_active_created_at
  ON public.orders (created_at)
  WHERE status = 'active' AND deleted_at IS NULL;

-- Padrão de consulta que aproveita o índice
EXPLAIN ANALYZE
SELECT id
FROM public.orders
WHERE status = 'active' AND deleted_at IS NULL
  AND created_at >= now() - interval '7 days';

Monitoring, validation and rollback without surprises

Monitor progress with the progress view and validate usage after the swap. If a command fails, an invalid index can remain; remove it with DROP INDEX CONCURRENTLY.

Confirm the new index is being used with pg_stat_user_indexes and EXPLAIN. If workload changes, recheck statistics and plans.

On systems with replicas, watch lag during the operation; index swaps generate significant WAL. If you detect severe anomalies, review consistency and stats as described in the recover PostgreSQL indexes and statistics guide.

In short, this practical guide to rebuilding PostgreSQL indexes in production using CREATE INDEX CONCURRENTLY and partial indexes emphasizes auditable and reversible steps.

• Follow pg_stat_progress_create_index.
• Check idx_scan after deployment.
• Remove leftover invalid indexes.
• Monitor replica lag during the window.

-- Progresso da criação do índice
SELECT pid, phase, relid::regclass AS table,
       index_relid::regclass AS index, tuples_total, tuples_done
FROM pg_stat_progress_create_index;

-- Uso de índices após a troca
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'my_table'
ORDER BY idx_scan DESC;

-- Gerar comandos para limpar índices inválidos
SELECT 'DROP INDEX CONCURRENTLY ' || quote_ident(relname) || ';' AS drop_cmd
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid AND relkind = 'i';

Frequently asked questions

Can I run CREATE INDEX CONCURRENTLY inside a transaction?

No. CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Open a dedicated session for the operation. If your migration tool auto‑starts transactions, disable that step for this operation.

How much extra space do I need to rebuild an index?

It depends on the target index size and data churn during the build. Plan for at least the size of the new index plus extra WAL. On very active tables the requirement can grow until completion.

What if duplicates exist when creating a UNIQUE CONCURRENTLY index?

The command fails and leaves an invalid index. You must identify and remove or consolidate duplicates before retrying. Use a GROUP BY with HAVING COUNT(*) > 1 on the target columns to find occurrences.

Will a partial index work if the query WHERE is not identical?

The planner uses a partial index when the query predicate implies the index predicate. If the filter does not logically match the index predicate, the index may be ignored. Keep filters consistent between queries and the index.

When should I choose REINDEX CONCURRENTLY instead of CREATE INDEX CONCURRENTLY?

When you need to reindex an existing constraint without changing columns, REINDEX CONCURRENTLY is often simpler and preserves names. If you need to change the definition or create a partial index, build a new index CONCURRENTLY and swap it in.

See also

• Practical guide: active‑passive failover between two VPS with Keepalived, HAProxy & lsyncd for synchronized uploads
• Guide: technical checklist to prepare a Debian VPS with Docker Compose for PHP apps in production
• Practical guide to VPS sizing and PHP‑FPM tuning for Magento/OpenMage stores with seasonal peaks: from sizing to load testing
• PostgreSQL backup and recovery with WAL shipping and basebackup on disk‑limited VPS: low local disk footprint