Zero-Downtime Index Operations for pgvector

The parameters that most affect an HNSW or IVFFlat index — m, ef_construction, and lists — are immutable once the index exists. There is no ALTER INDEX ... SET (m = ...); changing any of them means building a whole new index. On a table small enough to lock for a few seconds that is a non-event, but a production vector table with tens of millions of rows cannot afford the ACCESS EXCLUSIVE lock that a plain CREATE INDEX or REINDEX takes, and an HNSW build can run for minutes to hours. Blocking every read and write for that long is an outage. The tools that make the rebuild online — REINDEX INDEX CONCURRENTLY, CREATE INDEX CONCURRENTLY followed by DROP INDEX CONCURRENTLY, and building on a replica before promotion — each trade lock duration for disk, time, and operational complexity, and each has a failure mode (an INVALID leftover index) that must be handled explicitly. This page compares the approaches, gives the lock preflight that keeps them from stalling, and walks the rolling-parameter-change procedure end to end. It sits under Monitoring, Observability & Operations.

Up: Monitoring, Observability & Operations

Architectural Divergence & Trade-offs #

Three strategies rebuild a vector index without taking the table offline. They differ in how long they hold a lock, how much extra disk they consume, and how a failure leaves the system.

REINDEX INDEX CONCURRENTLY rebuilds the index in place under its existing name. Internally PostgreSQL builds a new index, waits for concurrent transactions, then swaps the definitions and drops the old one — all under a ShareUpdateExclusiveLock that permits reads and writes throughout. It briefly needs a stronger lock at the very start and end of the operation to register and swap the index, but never blocks DML for the duration of the build. Its cost is disk: both the old and new copies coexist until the swap, so you need roughly twice the index size free. It is the least error-prone path when the index name must be preserved, for example because application code or a partition template references it.

Build-new-then-swap does the same work explicitly: CREATE INDEX CONCURRENTLY items_embedding_hnsw_new ..., verify it, then DROP INDEX CONCURRENTLY items_embedding_hnsw, optionally renaming the new one into the old name with a fast ALTER INDEX ... RENAME. This is the strategy that lets you change the shape parameters — you specify a new m and ef_construction on the fresh index — which REINDEX cannot do because it rebuilds with the original definition. It is also the one that supports a validation gate: the new index exists alongside the old, so you can EXPLAIN against it and compare recall before dropping the incumbent. Same ~2x disk cost, slightly more moving parts.

Build on a replica, then promote or roll avoids putting the build load on the primary at all. You build the new index on a physical or logical replica, verify it there, and then either promote that replica to primary or roll the change across the fleet node by node. This is the choice when the primary lacks the disk headroom or I/O budget for a concurrent build, or when the build is long enough that you would rather not run it on the node serving traffic. It is the most operationally involved and depends on your replication topology.

All concurrent builds cost more total time than their blocking equivalents — PostgreSQL does extra table passes and waits for concurrent transactions — so this is throughput traded for availability. The build itself is subject to the same timeout and resource constraints as any large index creation, covered in asynchronous index build strategies.

Parameter Space & Diagnostic Workflow #

The knobs that matter here are not index parameters but session and lock settings that decide whether a concurrent build completes or stalls.

The preflight is a lock check, because a concurrent operation waits for every transaction that could see the old index and will hang indefinitely behind a long-running one. Before starting, find sessions that would block the build’s ShareUpdateExclusiveLock:

SELECT a.pid,
       a.state,
       a.wait_event_type,
       now() - a.xact_start          AS xact_age,
       left(a.query, 80)             AS query
FROM   pg_stat_activity a
JOIN   pg_locks l ON l.pid = a.pid
JOIN   pg_class c ON c.oid = l.relation
WHERE  c.relname = 'items'
  AND  l.mode LIKE '%ShareUpdateExclusive%'
ORDER  BY xact_age DESC NULLS LAST;

Any long-lived transaction here — an idle-in-transaction session, an open cursor, a stuck autovacuum — will stall the build’s wait phase. Set a bounded lock_timeout on the build session so it fails fast rather than queueing, and enforce idle_in_transaction_session_timeout server-wide so other sessions cannot hold the build hostage. Set statement_timeout = 0 in the build session specifically, since the build legitimately runs longer than any normal query.

A concurrent build has one dangerous property: it does not cancel on client disconnect the way an ordinary statement does — and if it is interrupted (a cancel, a timeout, a crash), it leaves behind an INVALID index that still consumes disk and, worse, is not used by the planner but is maintained on writes. Detect these leftovers:

SELECT c.relname                    AS index_name,
       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
JOIN   pg_class t   ON t.oid = i.indrelid
WHERE  t.relname = 'items'
  AND  i.indisvalid = false;

Clean up an indisvalid = false index with a concurrent drop so the cleanup itself does not block traffic:

DROP INDEX CONCURRENTLY IF EXISTS items_embedding_hnsw_new;

Step-by-Step Implementation #

This is the rolling parameter change: replacing an HNSW index that was built with m = 16 with one at m = 32 and a higher ef_construction, with no read/write outage and a validation gate before the old index is retired.

Step 1 — Prepare the build session. Disable the statement timeout for this session only and set a bounded lock timeout so the initial lock acquisition fails fast instead of queueing behind DDL.

SET statement_timeout = 0;
SET lock_timeout = '5s';
SET maintenance_work_mem = '2GB';   -- as high as the host allows

Step 2 — Run the lock preflight. Confirm no long transaction holds a ShareUpdateExclusiveLock on items using the pg_stat_activity/pg_locks query above. Do not proceed until it is clear; a blocker will stall the build in its wait phase.

Step 3 — Build the new index concurrently with the new parameters. The CONCURRENTLY keyword keeps reads and writes flowing; the new m and ef_construction are what REINDEX could not have changed.

CREATE INDEX CONCURRENTLY items_embedding_hnsw_new
    ON items
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 32, ef_construction = 128);

The relationship between these values, build time, and recall is worked through in optimizing m and ef_construction parameters — raising m and ef_construction improves recall but lengthens the build and grows the index, which is precisely the disk-and-time trade this online procedure is designed to absorb.

Step 4 — Confirm the new index built valid. A concurrent build that hit trouble reports indisvalid = false; only a fully valid index should replace the incumbent.

SELECT c.relname, i.indisvalid, i.indisready
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
WHERE  c.relname = 'items_embedding_hnsw_new';

Step 5 — Validate before the swap. With both indexes present, force the planner onto the new one and check the plan and latency (see the next section). This is the gate the build-new-then-swap approach exists to provide.

Step 6 — Drop the old index concurrently, then rename. Retire the incumbent without a blocking lock, then rename the new index into the canonical name with a fast catalog-only ALTER.

DROP INDEX CONCURRENTLY items_embedding_hnsw;
ALTER INDEX items_embedding_hnsw_new RENAME TO items_embedding_hnsw;

The RENAME takes a brief ACCESS EXCLUSIVE lock but is a metadata operation that completes in milliseconds, so it does not constitute downtime. At no point were writes or reads blocked for the duration of the build. If a build times out or is cancelled at any step, remove the partial index with DROP INDEX CONCURRENTLY IF EXISTS items_embedding_hnsw_new before retrying — the timeout-specific recovery path is detailed in resolving pgvector index build timeout errors.

Validation & Recall Testing #

While the build runs, watch it through pg_stat_progress_create_index. A build whose blocks_done stops advancing is blocked, not slow.

SELECT p.phase,
       p.blocks_done,
       p.blocks_total,
       round(100.0 * p.blocks_done
             / nullif(p.blocks_total, 0), 1) AS pct_done,
       p.current_locker_pid
FROM   pg_stat_progress_create_index p
JOIN   pg_class c ON c.oid = p.relid
WHERE  c.relname = 'items';

A non-null current_locker_pid with a stalled pct_done means the build is waiting on the transaction with that PID — go cancel or wait it out. Once the new index is valid, validate that the planner will actually use it and that recall did not regress. Force the choice by dropping the old index’s visibility to the planner in a throwaway session, or simply compare plans against each named index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM   items
ORDER  BY embedding <=> '[0.01, 0.02, ...]'::vector
LIMIT  10;

Confirm the plan reads Index Scan using items_embedding_hnsw_new and that latency and buffer counts are at least as good as the old index delivered. Whether the higher m actually bought recall is a measurement, not an assumption — compare top-k results against an exact ground truth in Python before you drop the incumbent:

import asyncpg, asyncio

async def recall_at_k(pool, probes, k=10):
    async with pool.acquire() as conn:
        await conn.execute("SET LOCAL hnsw.ef_search = 100")
        hits = 0
        for vec in probes:
            approx = await conn.fetch(
                """SELECT id FROM items
                   ORDER BY embedding <=> $1::vector LIMIT $2""",
                vec, k)
            exact = await conn.fetch(
                """SELECT id FROM items
                   ORDER BY embedding <=> $1::vector LIMIT $2""",
                vec, k)  # run with SET enable_indexscan = off for true exact
            hits += len(set(r["id"] for r in approx)
                        & set(r["id"] for r in exact))
        return hits / (len(probes) * k)

Systematizing this comparison so a regression blocks a deploy is the subject of recall regression testing in CI; here it is the manual gate before the irreversible DROP.

Failure Modes & Gotchas #

• The INVALID leftover index. A cancelled or timed-out concurrent build leaves an index with indisvalid = false. The planner ignores it, but writes still maintain it, so it silently costs write throughput and disk. Always check for indisvalid = false after any interrupted build and clean up with DROP INDEX CONCURRENTLY.
• Concurrent builds do not cancel on client disconnect. Unlike a normal query, killing your psql session does not necessarily stop the build cleanly — and a statement_timeout firing mid-build aborts it into exactly the INVALID state above. Run the build session with statement_timeout = 0 and manage its lifetime deliberately.
• Disk blowup from coexisting copies. Both the old and new index exist simultaneously until the swap or drop, so a rebuild transiently needs ~2x the index size in free space. On a large HNSW index that can be tens of gigabytes; verify headroom before starting or the build fails partway and leaves an invalid stub.
• WAL pressure and replica lag. A concurrent build writes the entire new index through WAL, which can flood replication and inflate lag on standbys. Throttle by scheduling off-peak, and monitor replica lag during the build so a read replica serving search does not fall dangerously behind.
• Long transactions stalling the wait phases. Concurrent operations have wait phases that block until all older transactions finish. A single long-running or idle-in-transaction session extends the build indefinitely. Enforce idle_in_transaction_session_timeout and run the lock preflight first.
• REINDEX CONCURRENTLY cannot change shape. It rebuilds with the original m/ef_construction/lists, so it fixes bloat but never re-tunes. If the goal is a parameter change, you must use the create-new-then-swap path.

Monitoring & Alerting Hooks #

Instrument two things during any online rebuild: the build’s forward progress and the lock waits that could stall it. Both are cheap pg_stat reads suitable for a scrape loop; publishing them as metrics is covered in pg_stat monitoring for vector indexes.

Export build progress as a gauge so a stalled build pages instead of hanging silently:

SELECT c.relname                              AS table_name,
       p.phase,
       round(100.0 * p.blocks_done
             / nullif(p.blocks_total, 0), 1)  AS pct_done,
       p.current_locker_pid IS NOT NULL       AS is_blocked
FROM   pg_stat_progress_create_index p
JOIN   pg_class c ON c.oid = p.relid;
-- alert: is_blocked = true for > 60s, or pct_done unchanged for > 5m

Alert on lock waits against the table under rebuild, since a blocker is the usual reason a concurrent operation appears frozen:

SELECT count(*) FILTER (WHERE NOT l.granted)          AS waiters,
       max(EXTRACT(epoch FROM now() - a.query_start)) AS longest_wait_s
FROM   pg_locks l
JOIN   pg_stat_activity a ON a.pid = l.pid
JOIN   pg_class c ON c.oid = l.relation
WHERE  c.relname = 'items';
-- alert: waiters > 0 AND longest_wait_s > 30

Finally, sweep for orphaned invalid indexes on a schedule so a failed build does not quietly accumulate write overhead:

SELECT c.relname, 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.indisvalid = false;
-- alert: any row → an interrupted concurrent build needs cleanup

FAQ #

Related #

• pg_stat monitoring for vector indexes — the counter and progress views this procedure watches
• Recall regression testing in CI — automating the pre-swap recall gate
• Asynchronous index build strategies — running the long concurrent build off the request path
• Resolving pgvector index build timeout errors — recovering from an aborted build and its invalid index
• Optimizing m and ef_construction parameters — choosing the new values a rolling change applies
• Up: Monitoring, Observability & Operations