Asynchronous Index Build Strategies for pgvector

Up: HNSW & IVFFlat Index Creation & Tuning

Production vector search pipelines run under strict latency and throughput budgets, and a naive CREATE INDEX breaks them. A synchronous build takes an AccessExclusiveLock on the table, so every embedding write blocks for the full duration of the build — minutes on a few million rows, hours on a billion-vector corpus. During that window the ingestion stream backs up, Kafka consumer lag climbs, Python ETL workers time out, and the application connection pool saturates with stalled writers. Asynchronous index construction decouples graph materialization from the live write path so ingestion never stops: the index builds in the background against a snapshot while reads and writes continue, then the finished structure is atomically swapped into service. Getting this right means aligning PostgreSQL’s concurrency primitives, pgvector’s single-connection build model, and your orchestration layer so that a build failure degrades gracefully instead of taking the table offline.

Architectural Divergence & Trade-offs #

Three build strategies dominate production pgvector deployments, and they differ mainly in how much of the table they lock and for how long. Choosing between them is a function of write volume during the build window, whether you have a replica to spare, and how strict your recall-continuity requirement is.

Blocking in-place build (CREATE INDEX). The simplest path and the only one appropriate for an offline maintenance window. It holds AccessExclusiveLock for the entire build, blocking all reads and writes. It is the fastest wall-clock option because there is no catch-up phase and no MVCC bookkeeping, and it produces the tightest graph. Use it only when you can guarantee zero traffic — a cold load before cutover, or a scheduled outage.

Concurrent in-place build (CREATE INDEX CONCURRENTLY). The workhorse for live tables. It avoids AccessExclusiveLock, instead holding ShareUpdateExclusiveLock on the table for the duration and AccessShareLock on the index. Reads and writes proceed throughout. The cost is a two-phase build: an initial pass that scans the table snapshot and builds the base structure, then a catch-up pass that reconciles rows written during phase one. Concurrent builds are slower, generate more WAL, and — critically — can leave an invalid index behind if they fail, which must be cleaned up manually. The ShareUpdateExclusiveLock also conflicts with VACUUM FULL, ALTER TABLE, and other schema migrations, so it cannot run alongside DDL.

Shadow-table or replica-staged build with swap. The zero-downtime pattern for very large tables or continuous high-volume ingestion. The index is built (blocking, for speed) on a replica or on a shadow copy of the table that receives a dual-write stream, then promoted into service. The swap itself is a fast metadata operation — a RENAME, an ATTACH PARTITION, or a view repoint — that takes a brief AccessExclusiveLock measured in milliseconds. This is the most operationally complex option but the only one that keeps both write throughput and query recall continuous during a full rebuild or a parameter change.

The strategy you pick interacts with the algorithm. HNSW parallelizes across max_parallel_maintenance_workers because layer construction distributes cleanly, so a concurrent in-place build scales reasonably. IVFFlat’s k-means centroid pass is single-threaded and becomes a hard serial bottleneck regardless of worker count, which pushes large IVFFlat rebuilds toward the shadow-table pattern where the blocking build runs uncontended. If you have not yet fixed the algorithm, settle that first — the trade-offs are laid out in HNSW vs IVFFlat algorithm selection, and the construction parameters that drive build time are covered in optimizing m and ef_construction parameters.

Parameter Space & Diagnostic Workflow #

Asynchronous builds are memory- and I/O-bound, and the defaults are tuned for small B-tree indexes, not multi-gigabyte vector graphs. Set these at the session level on the build connection — never globally, or you will starve normal query traffic.

-- Run on the dedicated build/maintenance connection only
SET maintenance_work_mem = '16GB';            -- graph/centroid working set in RAM
SET max_parallel_maintenance_workers = 8;     -- HNSW parallel build workers
SET parallel_tuple_cost = 0.1;                -- bias planner toward parallel scan
SET parallel_setup_cost = 1000;
SET statement_timeout = 0;                     -- disable per-statement timeout for this build
SET idle_in_transaction_session_timeout = 0;

maintenance_work_mem governs how much of the graph or how many centroids stay resident in RAM before pgvector spills to temporary files. Under-provisioning it forces temp-file creation, which inflates WAL, lengthens the build window, and can silently degrade graph quality. max_parallel_maintenance_workers only helps HNSW; it must stay well below your core count so index workers do not context-switch against application queries. The table below gives the values that matter for a build.

Before starting, confirm the build will not collide with a running maintenance operation and that memory headroom exists. These diagnostics answer both:

-- Conflicting locks that would block or be blocked by a concurrent build
SELECT pid, mode, relation::regclass AS table, granted
FROM pg_locks
WHERE relation = 'document_chunks'::regclass
ORDER BY granted;

-- Is anything holding ShareUpdateExclusive (VACUUM, ANALYZE, another CIC)?
SELECT pid, state, wait_event_type, query
FROM pg_stat_activity
WHERE query ILIKE '%document_chunks%' AND state <> 'idle';

Step-by-Step Implementation #

The safe pattern is a two-phase workflow: load into an unindexed (or shadow) table, then build the index off the write path and swap it in. The steps below implement the concurrent in-place variant, with the shadow-table swap called out where it diverges.

Step 1 — Stage the data and quiet the table. For a fresh load, bulk-insert with COPY before any index exists so writes are not paying index-maintenance cost. Batch-chunking and ingestion throughput are covered in batch chunking strategies for embeddings.

CREATE TABLE document_chunks (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   bigint NOT NULL,
    embedding   vector(1536) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- Bulk load happens here via COPY / batched INSERT, no vector index yet.

Step 2 — Kick off the concurrent build on a dedicated connection. Apply the session parameters from above, then issue the build. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so autocommit must be on.

SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 8;

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

Step 3 — Drive and supervise the build from Python. Wrap the DDL in an idempotent runner on its own connection (never a pooled application connection). Set the connection to autocommit so CONCURRENTLY is legal, and poll progress from a second connection.

import psycopg

DSN = "postgresql://builder@db-primary/vectors"

BUILD_SQL = """
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_chunks_hnsw
    ON document_chunks USING hnsw (embedding vector_cosine_ops)
    WITH (m = 32, ef_construction = 128)
"""

def run_build() -> None:
    # autocommit=True is required: CONCURRENTLY cannot run in a transaction block
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("SET maintenance_work_mem = '16GB'")
        conn.execute("SET max_parallel_maintenance_workers = 8")
        conn.execute("SET statement_timeout = 0")
        conn.execute(BUILD_SQL)   # blocks this connection until the build finishes

if __name__ == "__main__":
    run_build()

For asyncio-based pipelines that must not block the event loop on the multi-hour build, dispatch the runner to a worker rather than awaiting it inline — the offloading pattern is described in async processing with Python asyncio.

Step 4 (shadow variant) — Build off-table and swap. For a rebuild that must preserve query recall throughout, build on a shadow table fed by a dual-write stream, then promote it with a fast rename inside a short transaction:

BEGIN;
ALTER TABLE document_chunks       RENAME TO document_chunks_old;
ALTER TABLE document_chunks_shadow RENAME TO document_chunks;
COMMIT;   -- brief AccessExclusiveLock, milliseconds
DROP TABLE document_chunks_old;

Step 5 — Containerize the job with hard resource bounds. Run the build as a Kubernetes Job (not a long-lived deployment) with explicit requests/limits so a heavy materialization does not trigger node eviction. Reference lifecycle and swap implementations live in the pgvector repository.

Validation & Recall Testing #

A build reporting 100% is not a build you can trust with traffic. Two things must be verified before cutover: that the planner actually uses the new index, and that recall against a ground-truth set meets your SLO.

Confirm index usage with EXPLAIN ANALYZE — a silent sequential scan is the most common post-build failure:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM document_chunks
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;
-- Want: "Index Scan using idx_chunks_hnsw"
-- Red flag: "Seq Scan on document_chunks" → operator mismatch or invalid index

The operator in ORDER BY must match the index’s operator class — a vector_cosine_ops index only serves the <=> operator. A mismatch drops silently to a sequential scan.

Measure recall by comparing the approximate result set against an exact brute-force scan on a sampled query set. Run the exact query with the index disabled so it is forced to compute true nearest neighbors:

import psycopg

def recall_at_k(conn, query_vec: str, k: int = 10) -> float:
    with conn.cursor() as cur:
        cur.execute("SET LOCAL enable_indexscan = off; SET LOCAL enable_seqscan = on")
        cur.execute(
            "SELECT id FROM document_chunks ORDER BY embedding <=> %s LIMIT %s",
            (query_vec, k),
        )
        truth = {r[0] for r in cur.fetchall()}

    with conn.cursor() as cur:
        cur.execute("SET LOCAL hnsw.ef_search = 100")
        cur.execute(
            "SELECT id FROM document_chunks ORDER BY embedding <=> %s LIMIT %s",
            (query_vec, k),
        )
        approx = {r[0] for r in cur.fetchall()}

    return len(truth & approx) / k

Average recall_at_k across a few hundred held-out query vectors; if it sits below target, raise ef_search first (a query-time lever) before considering a rebuild with a higher ef_construction.

Failure Modes & Gotchas #

• Invalid index after a failed concurrent build. When CREATE INDEX CONCURRENTLY fails — timeout, disconnect, deadlock — PostgreSQL leaves an invalid index in the catalog that consumes disk and write-amplification cost but serves no queries. Find and drop it before retrying: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; then DROP INDEX CONCURRENTLY <name>;. Full triage of timeout, disconnect, and worker-starvation causes is in resolving pgvector index build timeout errors.
• Pooler kills a valid build. PgBouncer’s server_idle_timeout, an idle-in-transaction limit, or a cloud load balancer’s idle timeout can sever a build that is legitimately running for hours. Route builds through a direct connection to the primary, bypassing the transaction pooler entirely, and set idle_in_transaction_session_timeout = 0 on the session.
• Temp-file spill from undersized maintenance_work_mem. If the graph working set exceeds maintenance_work_mem, pgvector spills to disk, WAL balloons, and the build slows by an order of magnitude. Watch pg_stat_database.temp_bytes climb during the build as the signal.
• Checkpoint storms. A large concurrent build generates enough WAL to trigger back-to-back checkpoints if max_wal_size is small, throttling every other query on the instance. Raise max_wal_size for the build window.
• Silent sequential-scan fallback. An operator-class mismatch, a still-invalid index, or a planner that estimates the seq scan cheaper leaves queries running full scans at full recall but catastrophic latency. Categorizing these post-build states is covered in index validation error categorization.

Monitoring & Alerting Hooks #

Track a build in flight with pg_stat_progress_create_index, which exposes phase, blocks scanned, and tuples processed:

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,
    a.wait_event_type
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a USING (pid);

After cutover, confirm the index is actually serving reads and is not accumulating bloat. A idx_scan that stays flat means traffic never moved off sequential scans:

SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'document_chunks';

For a Prometheus-compatible exporter, surface build progress and index scan rate as gauges so alerts can fire on a stalled build (pct unchanged for N minutes) or a cold index (idx_scan flat after cutover):

-- postgres_exporter custom query: build progress + index adoption
SELECT
    coalesce((SELECT round(100.0 * blocks_done / NULLIF(blocks_total,0), 1)
              FROM pg_stat_progress_create_index LIMIT 1), 100) AS build_pct,
    (SELECT idx_scan FROM pg_stat_user_indexes
     WHERE indexrelname = 'idx_chunks_hnsw') AS index_scans;

Pair these with checkpoint-pressure signals from pg_stat_bgwriter (checkpoints_req climbing during the build indicates max_wal_size is too low) and temp-file growth from pg_stat_database.temp_bytes.

FAQ #

Related #

• HNSW vs IVFFlat algorithm selection — pick the algorithm before choosing a build strategy
• Optimizing m and ef_construction parameters — the construction knobs that drive build time
• Index validation error categorization — classify invalid and degraded post-build states
• Resolving pgvector index build timeout errors — step-by-step recovery for stalled builds
• pgvector storage overhead analysis — sizing maintenance_work_mem against the graph working set

Up: HNSW & IVFFlat Index Creation & Tuning