A Vector Index Migration Locked Our RAG Ingestion Pipeline for 52 Minutes

09:14 UTC. The ingestion queue depth graph in Grafana goes from a flat line near zero to a straight diagonal climbing off the top of the chart. Nothing is erroring. Nothing is crashing. Documents are just going in and never coming back out the other side.

We'd shipped a migration eleven minutes earlier to replace an ivfflat index with hnsw on our embeddings table, chasing better recall on similarity search. The migration itself was three lines of SQL. What it actually did was hold an exclusive lock on a 38-million-row table for the better part of an hour, and quietly explained a "why is search sometimes wrong" ticket we'd been ignoring for two weeks.

the setup

We run a document-ingestion pipeline that chunks incoming PDFs and support tickets, embeds each chunk with a 1536-dimension model, and writes the vectors into a Postgres 16 table with pgvector. Similarity search at query time runs against an index on the embedding column. We'd launched on ivfflat eight months earlier because it was the only index type pgvector shipped with at the time. Recall had been degrading as the table grew past 30 million rows. ivfflat's list-based clustering gets worse as data scales past what the list count was tuned for, and nobody had gone back to retune it.

hnsw was the fix: better recall at scale, no list-count tuning to babysit. The migration script one of our backend engineers wrote was correct pgvector syntax and, on its own, looked uncontroversial.

-- Ran clean against a 400k-row staging copy in 40 seconds.
-- Against the 38M-row production table, it held an exclusive lock for 52 minutes.

DROP INDEX embeddings_ivfflat_idx;

CREATE INDEX embeddings_hnsw_idx ON embeddings
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Staging has 400,000 rows, a fraction of production's 38 million. The build finished before anyone had time to watch it. Production was a different table entirely, and nobody had reasoned about what that difference in row count would do to build time, or to everything trying to write to the table while the build ran.

the scramble

First theory in the incident channel: the embedding model API was rate-limiting us. We'd hit quota issues with the provider before, and a sudden queue backup looked like the same shape. Someone pulled the provider dashboard.

API calls were succeeding, fast, in the 200-400ms range they always ran at. The embeddings were coming back fine. Whatever was stuck, it wasn't upstream of Postgres.

Second theory: the ingestion workers had deadlocked against each other, we'd seen worker-pool contention before under bursty load. We checked the worker logs.

[worker-3] embedding computed for chunk a91f... (312ms)
[worker-3] INSERT INTO embeddings ... (no error, no response)
[worker-3] INSERT INTO embeddings ... (still waiting, 41200ms elapsed)
[worker-7] INSERT INTO embeddings ... (still waiting, 38900ms elapsed)

Not a deadlock, a wait. Every worker had computed its embedding successfully and was sitting on a plain INSERT that Postgres simply hadn't executed yet. Workers weren't stuck on each other. They were all stuck on Postgres.

the hunt

That pointed at locks. We ran the standard blocking-query check against the primary.

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_locks blocked
JOIN pg_locks blocking
  ON blocking.locktype = blocked.locktype
 AND blocking.relation = blocked.relation
 AND blocking.pid != blocked.pid
JOIN pg_stat_activity blocking_stat ON blocking_stat.pid = blocking.pid
WHERE NOT blocked.granted;

 blocked_pid | blocking_pid |                blocking_query
-------------+--------------+-----------------------------------------------
       28114 |        27310 | CREATE INDEX embeddings_hnsw_idx ON embeddings...
       28119 |        27310 | CREATE INDEX embeddings_hnsw_idx ON embeddings...
       28122 |        27310 | CREATE INDEX embeddings_hnsw_idx ON embeddings...

Every stuck worker traced back to the same blocking query: the index build from eleven minutes earlier, still running. We'd assumed CREATE INDEX was a read-only operation that just took a while in the background. It isn't. Without the CONCURRENTLY keyword, CREATE INDEX takes a SHARE lock on the table for the full duration of the build. A SHARE lock still permits reads, but it blocks every INSERT, UPDATE, and DELETE until the build finishes. Query-time similarity search kept working the entire incident, users saw no errors there. Every new document trying to land in the table just queued behind a build with no defined end time.

We checked how long the build had left.

SELECT phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

        phase          | blocks_done | blocks_total | pct
------------------------+-------------+--------------+------
 building index: loading tuples in tree | 890241 | 2140880 | 41.6

41.6% done, eleven minutes in. hnsw builds are memory- and CPU-bound in a way ivfflat mostly isn't, the graph construction has to hold working state proportional to m and ef_construction in memory as it inserts each vector, and our maintenance_work_mem was still set to the 512MB default. The build was spilling to disk repeatedly instead of holding its working set in memory. That spilling is the specific reason 38 million rows took nearly an hour instead of the roughly proportional 63 minutes a naive extrapolation from the staging run would have predicted.

the find

Root cause: CREATE INDEX without CONCURRENTLY takes a table-level SHARE lock for its entire runtime, and nobody on the team had internalized that hnsw build time scales far worse than linearly with row count once maintenance_work_mem is too small to hold the graph's working set. The migration passed review because it read as syntactically correct pgvector, and it passed staging because staging's 400k rows never came close to triggering the memory-pressure path. Production ended up taking 78x longer than that 63-minute guess, all of it from spilling nobody saw coming.

The two-week-old "search sometimes wrong" ticket turned out to be related but separate: with ivfflat, recall degrading under an under-tuned list count had been silently returning near-miss results instead of the true nearest neighbors for weeks. Not this incident's cause, but the reason the team had scheduled the hnsw migration in the first place, under time pressure, without a deeper look at build cost.

the fix

We killed the build, confirmed the lock released, and reran it properly.

-- CONCURRENTLY: no exclusive/share lock, writes proceed throughout the build.
-- Trade-off: roughly 2x slower, and cannot run inside a transaction block.
SET maintenance_work_mem = '4GB';

CREATE INDEX CONCURRENTLY embeddings_hnsw_idx ON embeddings
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Only drop the old index after the new one reports as valid
-- (a CONCURRENTLY build can fail and leave an invalid index behind silently)
DROP INDEX CONCURRENTLY embeddings_ivfflat_idx;

CREATE INDEX CONCURRENTLY builds the index without blocking writers, at the cost of roughly double the build time and a requirement to run outside a transaction block, so it can't be wrapped in the all-or-nothing safety a normal migration gets. We ran it manually, watched pg_stat_progress_create_index until it hit 100%, then confirmed the index wasn't left in an invalid state before dropping the old one. Raising maintenance_work_mem to 4GB for the session cut the rebuild from a projected two hours down to 71 minutes, all of it now overlapping with live traffic instead of blocking it.

We also added a migration-review check: any CREATE INDEX against a table over one million rows now requires CONCURRENTLY explicitly, or a sign-off comment explaining why a blocking build is acceptable for that specific case. It's a grep in CI, not a tool, and it would have caught this migration before it ever reached staging.

the aftermath

No data was lost, every queued chunk eventually wrote through once the lock released, but ingestion latency for that window went from a normal 2-3 seconds per document to over 40 minutes for anything queued early in the incident. We had to manually re-trigger freshness checks for a handful of downstream consumers that had timed out waiting.

We've since run three more index changes on that table, all with CONCURRENTLY and maintenance_work_mem set deliberately beforehand. All three ran fully overlapped with live ingestion. Nobody in the incident channel had to watch a queue depth graph climb off the chart again.