Postgres + pgvector → RedDB: a migration playbook with wall-clock numbers

The most common RedDB migration we see is from Postgres with the pgvector extension bolted on. The shape is familiar: a primary OLTP schema, an embeddings table with an HNSW or IVFFlat index, a RAG pipeline that does a vector search joined back to source rows, and an ops team that has started to notice that every schema change to the embeddings table now takes a maintenance window. This post is the playbook we hand to teams making that move.

Numbers below are from a representative test corpus we ran against ourselves before publishing: 12.4M rows in the source table, 1536-dim OpenAI-style embeddings, hot working set ~30 GB. Your numbers will differ — what matters is the ratio between the phases, which is stable across the migrations we have shipped. Treat the absolute milliseconds and minutes as a sanity check, not a contract.

The shape of the source

Almost every Postgres + pgvector workload we have migrated has this skeleton:

CREATE TABLE documents (
  id           bigserial PRIMARY KEY,
  tenant_id    text NOT NULL,
  uri          text NOT NULL,
  body         text NOT NULL,
  metadata     jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
  id           bigserial PRIMARY KEY,
  document_id  bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  chunk_idx    int NOT NULL,
  body         text NOT NULL,
  embedding    vector(1536),
  model        text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

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

The hot query is a tenant-scoped top-k followed by a join back to the source document:

SELECT c.id, c.document_id, c.body, d.uri,
       1 - (c.embedding <=> $1) AS score
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE c.tenant_id = $2
ORDER BY c.embedding <=> $1
LIMIT 20;

If your workload looks meaningfully different from this — for example, you do not have a tenant column, or you store embeddings in a sibling service — most of what follows still applies, but adapt the dual-write step to your topology.

Phase 0 — pre-flight (1 day)

Before touching either database, write down four numbers. We have not yet seen a migration where these were all known up front, and we have seen migrations stall when they were not.

1. Embedding cost to rebuild from scratch. Rows × tokens-per-row × $/million-tokens. If this number is more than ~10% of one month of your inference budget, plan to copy embeddings, not regenerate them.
2. Read QPS, p50/p95/p99 latency. From the actual production load balancer, not from a benchmark. The cutover budget is set by your p99.
3. Write QPS, including the burstiest hour. Dual-write must survive this without falling behind.
4. Total bytes on disk for chunks.embedding (SELECT pg_size_pretty(pg_total_relation_size('chunks_embedding_hnsw'))). This sets the wall-clock for the bulk copy.

For our test corpus those numbers were: ~$1,800 to re-embed, 340 read QPS at p95 = 22 ms, write peak 90 QPS, embedding index = 41 GB. We copied embeddings rather than regenerating them. Almost every team should.

Phase 1 — schema in RedDB (1 hour)

The translation is almost mechanical because RedDB speaks the same SQL dialect for table DDL. Two real differences matter:

CREATE TABLE documents (
  id           bigserial PRIMARY KEY,
  tenant_id    text NOT NULL,
  uri          text NOT NULL,
  body         text NOT NULL,
  metadata     jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
  id           bigserial PRIMARY KEY,
  document_id  bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  tenant_id    text NOT NULL,          -- denormalised, see below
  chunk_idx    int NOT NULL,
  body         text NOT NULL,
  embedding    vector(1536),
  model        text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX chunks_tenant_idx ON chunks (tenant_id);

CREATE INDEX chunks_embedding_ann
  ON chunks USING vector (embedding cosine)
  WITH (lists = 2048, ef_search = 64);

The two differences:

• tenant_id is denormalised onto chunks. In pgvector you can filter by tenant via a join, but the planner often picks the vector index first and applies the filter as a post-condition, which is slow when the tenant is selective. Denormalising the column lets RedDB push the predicate down into the ANN search.
• The vector index is RedDB’s native ANN, not HNSW-via-extension. The WITH parameters look similar (lists, ef_search) but they mean different things — see the RAG-without-a-second-database post for the long-form on tuning. Default to the values above for 1536-dim, ~10M-vector workloads; revisit only if recall is off.

Build the schema, run smoke writes, and confirm the ANN index plan with EXPLAIN. Do not run the migration until you can see the ANN index getting picked.

Phase 2 — dual-write (3–5 days, calendar)

The dual-write window is the lever you pay rent on for the entire migration. Keep it short.

We dual-write at the application layer, not in the database, for two reasons: (a) you keep one transactional boundary per database, which means rollback is just “stop writing to RedDB”, and (b) you can shed RedDB writes if it falls behind without disturbing Postgres.

// Pseudocode — adapt to your data access layer.
async function writeChunk(input: ChunkInput): Promise<void> {
  const id = await pg.insertChunkReturningId(input)        // source of truth

  // Best-effort secondary write. Failures go to a retry queue,
  // not back to the user.
  void reddbWriteQueue.enqueue({ id, input })
}

Two non-obvious requirements:

1. The retry queue must preserve order per document_id. Out-of-order writes will leave you with a chunk in RedDB whose chunk_idx is ahead of a chunk that has not landed yet, and your top-k will return holes. Partition the queue by document_id (or by id, if you never update chunks).
2. Backfill from Postgres LSN, not from created_at. A created_at-based backfill misses any row that was inserted between your initial SELECT max(created_at) and the dual-write going live. Use pg_export_snapshot() to anchor the bulk copy and start the live writer at the snapshot’s LSN.

For the 12M-row test corpus, dual-write ran for four calendar days. Lag stayed under 90 seconds at the worst (the daily reindex run on the source) and under 4 seconds at p95.

Phase 3 — bulk copy with embedding preservation (8–14 hours of wall-clock for ~10M rows)

This is the longest single step. The naïve approach — SELECT * FROM chunks driving INSERT INTO chunks over the wire — will work but takes about 4× longer than necessary and pegs both databases. Do the boring thing instead.

# 1. Snapshot Postgres to a COPY-formatted dump with embeddings as bytea.
psql "$PG_URL" -c "\
  COPY (
    SELECT id, document_id, tenant_id, chunk_idx, body,
           embedding::text AS embedding_text, model, created_at
    FROM chunks
    WHERE created_at <= '${SNAPSHOT_TS}'
  ) TO STDOUT WITH (FORMAT csv, HEADER true)" \
  | zstd -3 -T0 > chunks-${SNAPSHOT_TS}.csv.zst

# 2. Stream into RedDB. Use COPY, not INSERT.
zstd -d -c chunks-${SNAPSHOT_TS}.csv.zst \
  | psql "$RDB_URL" -c "\
    COPY chunks (id, document_id, tenant_id, chunk_idx, body,
                 embedding, model, created_at)
    FROM STDIN WITH (FORMAT csv, HEADER true)"

Three things we wish we had known the first time we did this:

• COPY is the only thing that matters for throughput. On the test corpus, COPY-based bulk load ran at ~280k rows/min. The same data via INSERT batched 1000 at a time ran at ~38k rows/min. Same disk, same network.
• Build the ANN index after the load, not before. Building during load triples wall-clock and produces a worse index. We use CREATE INDEX post-load, which took 42 minutes on the test corpus.
• zstd -3 is the right compression level for a network-bound copy. Higher levels save little additional bandwidth but cost real CPU. Lower levels saturate the disk.

For documents, the same approach — COPY out, compress, COPY in — runs in about a quarter of the chunk time because the rows are smaller and the index footprint is trivial.

Phase 4 — query translation (1–2 days)

Most queries port unchanged. The patterns that need attention:

Vector search with tenant filter

In pgvector the idiomatic version is:

SELECT c.id, c.document_id, c.body,
       1 - (c.embedding <=> $1) AS score
FROM chunks c
WHERE c.tenant_id = $2
ORDER BY c.embedding <=> $1
LIMIT 20;

In RedDB, with tenant_id denormalised onto chunks, the literal translation works and the plan changes:

SELECT id, document_id, body,
       1 - (embedding <=> $1) AS score
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 20;

The join back to documents is usually cheaper as a second round-trip than as a SQL JOIN, because RedDB’s planner cannot push the JOIN below the ANN scan and you end up materialising the full top-k before the join. On the test corpus, splitting the query dropped p95 from 31 ms to 19 ms. If your wrapper library makes the round-trip awkward, leave the JOIN in — the absolute difference is small.

Hybrid (vector + lexical) search

If you used pgvector’s <=> together with tsvector @@, the translation is almost identical, but in RedDB the planner will pick a parallel scan strategy if you wrap the two scores in a CTE:

WITH
  vec AS (
    SELECT id, 1 - (embedding <=> $1) AS vscore
    FROM chunks
    WHERE tenant_id = $2
    ORDER BY embedding <=> $1
    LIMIT 200
  ),
  lex AS (
    SELECT id, ts_rank(search_v, plainto_tsquery('english', $3)) AS lscore
    FROM chunks
    WHERE tenant_id = $2 AND search_v @@ plainto_tsquery('english', $3)
    LIMIT 200
  )
SELECT c.id, c.body,
       coalesce(v.vscore, 0) * 0.7 + coalesce(l.lscore, 0) * 0.3 AS score
FROM chunks c
LEFT JOIN vec v ON v.id = c.id
LEFT JOIN lex l ON l.id = c.id
WHERE v.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY score DESC
LIMIT 20;

The LIMIT 200 per branch is the lever for recall vs. latency. We start at 200 and tune down only if a recall regression test passes.

ANALYZE after the copy

The most common “RedDB is slow” support ticket on a fresh migration is “we forgot to ANALYZE.” Run it once after the bulk copy and once after the ANN index build. It takes single-digit minutes on a 12M-row table.

Phase 5 — shadow reads (2–4 days)

Do not flip the read traffic on a Tuesday morning. Shadow-read first.

async function search(query: SearchInput): Promise<SearchResult[]> {
  const primary = await pg.search(query)

  if (sampler.shouldShadow()) {
    void compareInBackground(query, primary)   // never blocks the response
  }

  return primary
}

The shadow compare logs three numbers per request: (a) recall@k between Postgres and RedDB top-k, (b) p99 latency difference, (c) any query that errored on one but not the other. We aim for recall ≥ 0.97 against the Postgres baseline before flipping reads. Our test corpus took 36 hours of shadow traffic to converge there — most of the gap was queries with very small tenants, which is where ANN parameters matter most.

Phase 6 — cutover (15 minutes)

A short, well-instrumented cutover with a one-command rollback. Nothing else.

1. Set READS_FROM=reddb in the feature flag service (5s).
2. Watch p95/p99 search latency and error rate for 10 minutes.
3. If anything looks wrong, set READS_FROM=postgres. You are back where you were because dual-write is still running.
4. After 24 hours of clean metrics, stop writing to Postgres. Keep it as a hot read replica for one more week.
5. After the week, decommission.

On the test corpus the cutover itself was uneventful: 12 minutes from flag flip to “we’re going to lunch.” The interesting work was Phases 2–5; the cutover is just the moment you collect on it.

Rollback plan, explicitly

Rollback is a property of the playbook, not a separate document. The phases above were designed so that at each step you can stop and either back out or hold.

Phase ends	If it goes wrong, you …
Phase 1 (schema)	Drop the RedDB tables. No traffic, no data, no consequence.
Phase 2 (dual-write)	Disable the secondary writer. Postgres is the source of truth and untouched.
Phase 3 (bulk copy)	Truncate the RedDB tables and restart the copy. The snapshot LSN is your idempotency.
Phase 4 (queries)	Keep the old query layer behind the same flag. Roll back at the application boundary.
Phase 5 (shadow read)	Stop shadowing. No user-visible effect.
Phase 6 (cutover)	Flip the read flag back. Dual-write is still running, so no data is lost.

The only step that is hard to reverse is Phase 6 after you have stopped writing to Postgres. That is why “keep writing to Postgres for 24 hours after cutover” is in the playbook with full caps and an exclamation point in our internal version.

The three places teams trip

If you take three things from this post:

1. Anchor the backfill on an LSN snapshot, not a wall-clock timestamp. Otherwise you will find phantom missing rows weeks later.
2. Build the ANN index after the bulk load. Doing it during load is the single biggest preventable wall-clock cost of the migration.
3. Keep dual-write running for 24 hours after the read cutover. Rollback without it is a restore-from-backup; with it, it is a flag flip.

Everything else is mechanical.

What we measured, in one table

Phase	Test-corpus wall-clock	What gates it
0 — pre-flight	~1 day	Stakeholder sign-off on the four numbers
1 — schema	~1 hour	DDL only
2 — dual-write live	4 days calendar	Queue lag stays bounded
3 — bulk copy + index build	11h 24m total	Disk + network
4 — query translation	1.5 days	Engineering effort
5 — shadow read	~36 hours	Recall convergence
6 — cutover	12 minutes	Flag flip + watch

If your corpus is meaningfully bigger than 12M rows or your embeddings are wider than 1536 dims, scale Phase 3 roughly linearly with bytes and budget a longer Phase 5. The other phases are largely independent of corpus size.

If you are in the middle of a migration that does not look like this, send us the shape — there is a good chance we have a variation of the playbook for it.