pgvector Index Management Embedding Pipeline Optimization

Scheduling REINDEX INDEX CONCURRENTLY Without Downtime

This page shows how to run REINDEX INDEX CONCURRENTLY on bloated HNSW and IVFFlat indexes on a schedule, inside a maintenance window, so a vector index is rebuilt in place without ever blocking reads or writes. It scopes the problem narrowly: the preflight checks (free space and lock blockers) that decide whether a rebuild is even safe to start, the session timeouts that keep a stuck rebuild from wedging the table, the pg_cron or external automation that fires it, and the cleanup that detects and drops the INVALID index a failed run leaves behind.

Up: Zero-Downtime Index Operations for pgvector

REINDEX INDEX CONCURRENTLY is the only way to shrink a bloated pgvector index without a read-blocking rebuild: it constructs a fresh copy of the index alongside the live one, holding a ShareUpdateExclusiveLock that permits concurrent SELECT, INSERT, UPDATE, and DELETE for almost the entire operation, then swaps the two in a brief exclusive moment. The catch is that it is neither free nor transactional — it needs roughly twice the index’s disk footprint while both copies exist, it aborts if it cannot take its lock, and an interrupted run leaves a half-built INVALID index consuming space and doing nothing. Scheduling it safely means wrapping the bare command in a preflight, a set of session guards, and a cleanup pass, all fired from an automation layer during a low-traffic window.

Prerequisites

  • PostgreSQL 15+ with the pgvector extension 0.5+ installed. REINDEX INDEX CONCURRENTLY for a single index is fully supported from PG12 onward; PG15+ is assumed for the monitoring views used here.
  • A named HNSW or IVFFlat index you already suspect is bloated. Confirm the bloat first rather than reindexing blindly — measure it with detecting HNSW index bloat with pgstattuple.
  • Free disk equal to at least the current index size, ideally 2x, on the tablespace holding the index. HNSW indexes are large; a 10M-row, 1536-dimension index can run to tens of gigabytes.
  • Privileges to run REINDEX on the table (table owner or superuser), and — if automating in-database — the pg_cron extension installed and scheduled on the target database.
  • A maintenance window where write volume is low. A concurrent reindex on a hot, high-churn table takes longer and is more likely to be blocked, so pair it with an understanding of asynchronous index build strategies.
The scheduled concurrent reindex lifecycle with preflight and cleanup A left-to-right sequence of five phase boxes. Phase one, preflight, checks that free space is at least twice the index size and that no session holds a conflicting lock in pg_locks. Phase two sets statement_timeout and idle_in_transaction_session_timeout for the session. Phase three runs REINDEX INDEX CONCURRENTLY. Phase four monitors pg_stat_progress_create_index while the rebuild proceeds. Phase five is a cleanup pass that finds any index with indisvalid false and drops it with DROP INDEX CONCURRENTLY. An arrow loops from the REINDEX phase down to the cleanup phase labelled "on failure or interrupt". Scheduled concurrent reindex — preflight, run, clean up PREFLIGHT Space ≥ 2x, no lock blockers GUARD Set session timeouts REBUILD REINDEX INDEX CONCURRENTLY OBSERVE Watch progress view CLEANUP Drop INVALID index on failure or interrupt → INVALID index must be dropped
The five phases of a scheduled concurrent reindex. A failed or interrupted rebuild jumps straight to the cleanup phase.

Step-by-step

1. Preflight: confirm free space

REINDEX INDEX CONCURRENTLY builds a complete second copy of the index before dropping the old one, so the tablespace must hold both simultaneously. Abort the run rather than fill the disk. Measure the current index and compare against free space on its tablespace.

SQL
SELECT
    pg_size_pretty(pg_relation_size('doc_chunks_embedding_hnsw'))  AS index_size,
    pg_relation_size('doc_chunks_embedding_hnsw')                  AS index_bytes;

Have the scheduler compare index_bytes * 2 against available space on the mount and skip the run (logging a warning) if the headroom is insufficient. Never let a reindex be the thing that runs the volume out of space.

2. Preflight: check for lock blockers

The rebuild must acquire a ShareUpdateExclusiveLock on the table. That lock conflicts with VACUUM, ALTER TABLE, another concurrent index build, and any manual LOCK. A long-running transaction already holding a conflicting lock will make the reindex wait — or, with a lock_timeout, fail fast. Inspect current activity before firing:

SQL
SELECT
    a.pid,
    a.state,
    a.wait_event_type,
    now() - a.xact_start AS xact_age,
    l.mode,
    l.granted
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE l.relation = 'doc_chunks'::regclass
  AND l.mode IN ('ShareUpdateExclusiveLock', 'ExclusiveLock', 'AccessExclusiveLock')
ORDER BY xact_age DESC;

If a long-lived transaction is holding a conflicting mode, defer the run. A concurrent reindex also cannot proceed while an old snapshot from a long transaction is open, because it must wait for those transactions to drain twice during its build phases.

3. Set session timeouts before running

A concurrent reindex that hangs — usually behind a lock or a runaway autovacuum — should abort itself, not sit indefinitely holding its own lock and blocking DDL. Set a statement_timeout sized to your window, an idle_in_transaction_session_timeout to kill a wedged session, and a short lock_timeout so the rebuild fails fast if it cannot take its lock instead of queueing behind a blocker.

SQL
SET statement_timeout = '90min';
SET idle_in_transaction_session_timeout = '5min';
SET lock_timeout = '10s';

Run REINDEX outside an explicit transaction block — REINDEX INDEX CONCURRENTLY cannot run inside a BEGIN ... COMMIT, so issue it as a standalone statement on the session.

4. Run the concurrent reindex

SQL
REINDEX INDEX CONCURRENTLY doc_chunks_embedding_hnsw;

The command rebuilds the index in place under its original name. Reads and writes continue throughout; only the final swap takes a brief stronger lock. For IVFFlat the rebuild also re-runs k-means on current data, which is exactly why periodic reindexing recovers recall on tables whose distribution has drifted since the original build.

5. Monitor progress

A large HNSW rebuild can run for many minutes. Watch pg_stat_progress_create_index from a second session to see which phase it is in and how far along the current phase is.

SQL
SELECT
    p.phase,
    p.blocks_done,
    p.blocks_total,
    round(100.0 * p.blocks_done / NULLIF(p.blocks_total, 0), 1) AS pct,
    p.tuples_done,
    p.tuples_total
FROM pg_stat_progress_create_index p
JOIN pg_class c ON c.oid = p.index_relid
WHERE c.relname LIKE 'doc_chunks_embedding%';

6. Clean up any INVALID index left by a failed run

If the reindex is interrupted (timeout, cancel, crash), it can leave behind a transient index whose pg_index.indisvalid is false. This dead index consumes disk and is never used by the planner. Detect it and remove it with DROP INDEX CONCURRENTLY, which itself takes only a ShareUpdateExclusiveLock and does not block traffic.

SQL
SELECT
    c.relname AS invalid_index,
    t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE i.indisvalid = false
  AND t.relname = 'doc_chunks';

-- for each invalid index reported above:
DROP INDEX CONCURRENTLY doc_chunks_embedding_hnsw_ccnew;

Make this cleanup pass part of the scheduled job itself so a failed night self-heals before the next run.

7. Automate it on a schedule

With pg_cron you can fire the reindex from inside the database. Give it its own dedicated window and keep the cadence conservative — HNSW indexes rarely need reindexing more than weekly.

SQL
-- weekly, Sunday 03:15, in-database via pg_cron
SELECT cron.schedule(
    'reindex-doc-chunks-hnsw',
    '15 3 * * 0',
    $$REINDEX INDEX CONCURRENTLY doc_chunks_embedding_hnsw$$
);

For preflight logic, timeouts, and cleanup that are awkward to express in one pg_cron statement, drive the whole sequence from an external scheduler (a cron job or orchestrator running psql) that connects, runs the preflight queries, sets the session GUCs, executes the reindex, and runs the INVALID-index cleanup as steps in one script.

Parameter reference

Parameter Type Default Production recommendation Notes
statement_timeout GUC (session) 0 (off) Size to the window, e.g. 90min Aborts a rebuild that overruns the maintenance window instead of bleeding into peak traffic.
idle_in_transaction_session_timeout GUC (session) 0 (off) 5min Kills a wedged session so it stops holding locks; a stuck reindex session otherwise blocks later DDL.
lock_timeout GUC (session) 0 (off) 10s Makes the rebuild fail fast when a conflicting lock is held rather than queueing behind it.
maintenance_work_mem GUC (session) 64MB 1GB–2GB for large HNSW builds Larger values speed the build; raise per-session, not globally, so only the reindex pays the memory.
max_parallel_maintenance_workers GUC (session) 2 2–4 Parallel workers for the build phase; bounded by max_parallel_workers.
Free space headroom operational — >= 2x current index size Both index copies coexist during the rebuild; the preflight must enforce this.
Reindex cadence schedule — Weekly or on a bloat threshold Reindex on measured bloat, not on a fixed clock, to avoid needless rebuilds.

Verification

After a run, confirm the index is valid, ready, and no smaller-than-expected. A healthy reindex leaves exactly one index under the original name with indisvalid = true and indisready = true.

SQL
SELECT
    c.relname,
    i.indisvalid,
    i.indisready,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'doc_chunks'::regclass
  AND c.relname LIKE 'doc_chunks_embedding%';

If the post-reindex size is materially smaller than before, the rebuild reclaimed real bloat. If indisvalid is false for any row, step 6’s cleanup has not run — drop that index and re-run. A quick EXPLAIN of a representative similarity query confirms the planner still chooses the rebuilt index.

Troubleshooting

  • Reindex hangs and never completes. A long-running transaction is holding a conflicting lock, or an old snapshot is keeping the build from draining. Diagnose with the pg_locks join from step 2 and SELECT * FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start; fix by terminating or waiting out the blocking transaction, and rely on the lock_timeout from step 3 so future runs fail fast instead of hanging.
  • could not create unique index or an _ccnew index lingers. The rebuild was interrupted and left an INVALID index. Diagnose with the indisvalid = false query in step 6; fix with DROP INDEX CONCURRENTLY on the reported name, then re-run the reindex.
  • Disk fills mid-rebuild. The preflight was skipped or the 2x estimate was too low. Diagnose by comparing pg_relation_size of the index against free space on its tablespace; fix by dropping any leftover INVALID index to reclaim space and enforcing the step 1 space check before every run.
  • Reindex overruns the maintenance window into peak traffic. No statement_timeout was set, or maintenance_work_mem was too small so the build was slow. Diagnose from the job’s start and end timestamps and the progress view; fix by setting a window-sized statement_timeout and raising maintenance_work_mem for the session.
  • Recall does not improve after reindexing an IVFFlat index. The list count no longer fits the data volume, so a rebuild alone cannot help. Diagnose by comparing row count against lists; fix by rebuilding with a re-tuned list count rather than reindexing under the old parameter — the sizing math is in asynchronous index build strategies.