Migrations on a Table With 50 Million Vectors

A migration on a table of fifty million vectors is not a bigger version of a migration on a small one; it is a different activity. The index build takes hours, the backfill generates more WAL than your replicas can consume, and the ALTER TABLE that ran in 40 ms in staging takes an ACCESS EXCLUSIVE lock in production and queues every query behind it. Each of those has a specific remedy.

Why the usual migration advice fails here

Three properties of vector tables break the assumptions behind ordinary migration tooling.

• The rows are enormous. At 1536 dimensions a row is six kilobytes, so fifty million rows is over 300 GB before indexes. Any operation that rewrites the table — most ALTER TABLE … TYPE forms, VACUUM FULL, CLUSTER — writes 300 GB of WAL and needs 300 GB of free disk.
• The index build is hours, not minutes. From the scaling law in choosing and tuning a pgvector index, fifty million rows at 1536 dimensions is a multi-hour build even with parallel workers. Your migration tool’s default statement timeout is measured in seconds.
• Backfilling means calling an external API. If the new column holds embeddings, the backfill rate is bounded by an embedding provider’s throughput, not by your database. That makes it a job with retries and a resumable cursor, not a SQL statement.

The lock that takes your site down

ALTER TABLE takes an ACCESS EXCLUSIVE lock. On an idle table that is instantaneous even for a large table, because since Postgres 11 adding a column with a non-volatile default is a catalogue change only. The danger is not the duration of the lock; it is the queue behind it.

If a long-running SELECT holds a lock the ALTER needs, the ALTER waits — and every query that arrives afterwards waits behind the ALTER, because lock requests are queued in order. One slow analytics query plus one instant migration equals a full outage for as long as the analytics query runs. Always:

SET lock_timeout = '3s';

ALTER TABLE chunk_embeddings ADD COLUMN embedding_v2 vector(1024);

-- If it cannot get the lock within 3 seconds it fails with
--   ERROR: canceling statement due to lock timeout
-- and nothing has queued. Retry in a loop; it will succeed
-- the moment there is a gap.

Three seconds, then retry, is infinitely preferable to an unbounded wait. Set lock_timeout in the migration itself rather than globally, and confirm your migration tool is not wrapping everything in one transaction — a single transaction holding a lock across twelve statements is the same problem with more steps.

Building the index online

SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 8;
SET statement_timeout = 0;            -- or the build is killed mid-way

CREATE INDEX CONCURRENTLY chunk_embeddings_v2_hnsw
  ON chunk_embeddings USING hnsw (embedding_v2 vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

CONCURRENTLY makes two passes over the table without blocking writes, at the price of taking roughly twice as long and of being unable to run inside a transaction block. Most migration frameworks wrap migrations in transactions by default, which produces ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Every framework has a way to opt out; find it before you need it.

The failure mode to know: if a concurrent build fails or is cancelled, it leaves an invalid index behind — a full-size index that costs storage, is maintained on every write, and is never used by a query. It does not announce itself.

SELECT c.relname, pg_size_pretty(pg_relation_size(c.oid))
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

-- Drop it before retrying, or you will have two.
DROP INDEX CONCURRENTLY chunk_embeddings_v2_hnsw;

Watch progress rather than guessing, and watch replication lag at the same time — an index build generates WAL steadily and a replica that falls far behind is a restore problem waiting to happen:

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

SELECT client_addr,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Backfilling 50 million rows

Never UPDATE the whole table in one statement. Postgres MVCC writes a new row version for every update, so a single statement over fifty million six-kilobyte rows doubles the table on disk, holds one transaction open for hours, prevents VACUUM from reclaiming anything the whole time, and cannot be resumed if it fails at 90 per cent.

Batch on the primary key with a cursor you persist, so the job is resumable by construction:

-- Progress table: survives a restart of the worker.
CREATE TABLE backfill_state (
  job         text PRIMARY KEY,
  last_id     bigint NOT NULL DEFAULT 0,
  updated_at  timestamptz NOT NULL DEFAULT now()
);
INSERT INTO backfill_state (job) VALUES ('embedding_v2')
ON CONFLICT DO NOTHING;

-- One batch. Runs in its own transaction, commits, releases.
WITH cur AS (
  SELECT last_id FROM backfill_state WHERE job = 'embedding_v2' FOR UPDATE
),
batch AS (
  SELECT id FROM chunk_embeddings, cur
  WHERE id > cur.last_id AND embedding_v2 IS NULL
  ORDER BY id LIMIT 1000
),
upd AS (
  UPDATE chunk_embeddings c
  SET embedding_v2 = $1::vector(1024)      -- one per row, from the API call
  FROM batch b WHERE c.id = b.id
  RETURNING c.id
)
UPDATE backfill_state
SET last_id = (SELECT max(id) FROM upd), updated_at = now()
WHERE job = 'embedding_v2';

1. Size the batch by duration, not by row count. Aim for each batch to commit in under a second. A thousand rows is a starting point; measure and adjust.
2. Sleep between batches, proportionally to replication lag. Read pg_stat_replication at the top of each loop and pause if lag exceeds a threshold. A backfill that saturates WAL shipping turns into a replica outage.
3. Watch dead tuples and let autovacuum keep up. SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'chunk_embeddings'. If it climbs monotonically, you are outrunning vacuum and the table is bloating; slow down.
4. Build the index after the backfill, not before. A backfill into an indexed column pays graph maintenance on every one of fifty million updates. Backfill into an unindexed column, then build once.

Expand and contract, step by step

The pattern that makes each step individually reversible. Nothing here changes the meaning of an existing column, which is the property that makes rollback possible at all.

1. Expand. Add the new nullable column. Catalogue-only, instant, and invisible to the running application. Reversible by DROP COLUMN.
2. Dual-write. Deploy application code that writes both the old and the new column, and still reads only the old. Reversible by deploying the previous version.
3. Backfill. The batched job above, for rows written before the dual-write deploy. Reversible: it only fills a column nobody reads.
4. Build the index concurrently. Still nobody reads it. Reversible by DROP INDEX CONCURRENTLY.
5. Verify before switching. Not “the backfill finished” but “the new column answers the same questions”.

   -- No nulls left?
   SELECT count(*) FROM chunk_embeddings WHERE embedding_v2 IS NULL;
   
   -- Do the two indexes agree on a sample of real queries?
   -- Overlap of top-10 between old and new, over 200 probes:
   SELECT round(avg(overlap), 3) FROM (
     SELECT (SELECT count(*) FROM (
               SELECT id FROM chunk_embeddings ORDER BY embedding    <=> p.v LIMIT 10
               INTERSECT
               SELECT id FROM chunk_embeddings ORDER BY embedding_v2 <=> p.v2 LIMIT 10
             ) x) / 10.0 AS overlap
     FROM probes p
   ) s;

   An overlap well below 1.0 is expected when the model changed — that is the point of changing it — but it should be explained by the model change and not by a truncated backfill.
6. Switch reads. One deploy. This is the only irreversible-feeling step and it is reversible by deploying the previous version, because the old column and its index still exist.
7. Contract, after a waiting period. Stop dual-writing, then drop the old index, then the old column. A week is a reasonable gap. Dropping the column is the first genuinely irreversible action in the whole sequence, and it should feel like it.

The rollback that actually works

The reason to lay it out that way is that “roll back the migration” is not a thing you can do to a 300 GB table in an incident. ALTER TABLE … TYPE back to the old type is another full rewrite and another multi-hour index build; restoring from a backup loses every write since it was taken.

So the rollback plan is the sequence itself: at every step before the contract phase, reverting is either a deploy of the previous application version or a DROP of something nothing reads. That is what “a rollback that works” means for a table this size — not an inverse migration, but an ordering in which no step destroys the thing you would need to go back to.

Two things to have ready before you start, because you cannot arrange them during the incident: a restore of a recent backup into a scratch instance, verified to actually restore (backups and restore for AI data covers the drill), and a way to disable the feature that reads the new column without a deploy. A feature flag that changes which column is read turns your worst case from a rollback into a toggle.

The last thing to check before starting, and the one that ends migrations early: free disk. A concurrent index build needs space for the new index while the old one still exists, a backfill needs space for the dead row versions until vacuum reclaims them, and WAL accumulates if a replication slot is lagging. Three demands arriving at once on a volume sized for steady state is how a migration becomes an outage that has nothing to do with the schema.

-- Before you start: what exists, and what you are about to add.
SELECT pg_size_pretty(pg_total_relation_size('chunk_embeddings')) AS table_total,
       pg_size_pretty(pg_relation_size('chunk_embeddings_hnsw'))  AS old_index,
       pg_size_pretty(pg_database_size(current_database()))       AS database;

-- WAL that cannot be recycled because a slot is holding it. A slot
-- belonging to a replica or a change-data-capture consumer that has
-- stopped will fill the disk on its own, quietly, over days.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY 3 DESC;

Budget for the new index at the size derived in storing embeddings, plus the same again for churn during the backfill, and confirm the headroom exists before the first statement rather than at seventy per cent through a four-hour build.