Home › Blog › Businesses › Saas › Backfill Embeddings for 1M Rows Without Killing Postgres
SaaS technology
Businesses SaaS July 12, 2026 • 11 min read

Backfill Embeddings for 1M Rows Without Killing Postgres

For: A backend engineer at a 20–80-person B2B SaaS company who just added pgvector to their Postgres instance and needs to generate embeddings for an existing products or documents table that already has 800K–2M rows — without locking the table, spiking OpenAI costs, or explaining a weekend outage to their CTO

Treat the backfill as a resumable work queue, not a batch job. Add an embedding column plus a claimed_at lease column, have workers atomically claim small batches with FOR UPDATE SKIP LOCKED, call the embedding API, and write results back one row at a time. This gives you crash-safety, horizontal parallelism, and short-lived locks — which is what keeps a live Postgres instance healthy while you churn through a million rows.

The rest of this post is the runnable version of that idea. It assumes you already added pgvector and are staring at a products or documents table with 800K–2M rows and live write traffic.

Why the obvious approaches break

Before the tutorial, the three failure modes worth naming:

The lease pattern below solves all three with about 40 lines of SQL and 60 lines of Python.

Prerequisites

Step 1: Add the embedding and lease columns

Adding nullable columns in Postgres 11+ is metadata-only — no table rewrite, no long lock.

ALTER TABLE documents
  ADD COLUMN embedding vector(1536),
  ADD COLUMN embedding_model text,
  ADD COLUMN embedded_at timestamptz,
  ADD COLUMN claimed_at timestamptz;

CREATE INDEX CONCURRENTLY documents_embed_todo_idx
  ON documents (id)
  WHERE embedding IS NULL;

Two things worth noting. First, the partial index on WHERE embedding IS NULL is what makes claiming rows fast — it shrinks as the backfill progresses. Second, we're not building the HNSW vector index yet. Do that after the backfill; building it now means every insert pays the cost twice.

Expected output:

ALTER TABLE
CREATE INDEX

Step 2: Write the claim query

This is the heart of the pattern. One SQL statement claims a batch and marks it — atomically, without holding locks after commit.

WITH claimed AS (
  SELECT id
  FROM documents
  WHERE embedding IS NULL
    AND (claimed_at IS NULL OR claimed_at < now() - interval '10 minutes')
  ORDER BY id
  LIMIT 100
  FOR UPDATE SKIP LOCKED
)
UPDATE documents d
SET claimed_at = now()
FROM claimed
WHERE d.id = claimed.id
RETURNING d.id, d.content;

What each piece does:

Step 3: The worker loop

import os, time, psycopg
from openai import OpenAI
from tenacity import retry, wait_exponential, stop_after_attempt

client = OpenAI()
MODEL = "text-embedding-3-small"
BATCH = 100

CLAIM_SQL = """
WITH claimed AS (
  SELECT id FROM documents
  WHERE embedding IS NULL
    AND (claimed_at IS NULL OR claimed_at < now() - interval '10 minutes')
  ORDER BY id LIMIT %s
  FOR UPDATE SKIP LOCKED
)
UPDATE documents d SET claimed_at = now()
FROM claimed WHERE d.id = claimed.id
RETURNING d.id, d.content;
"""

WRITE_SQL = """
UPDATE documents
SET embedding = %s::vector,
    embedding_model = %s,
    embedded_at = now(),
    claimed_at = NULL
WHERE id = %s;
"""

@retry(wait=wait_exponential(min=1, max=60), stop=stop_after_attempt(6))
def embed(texts):
    resp = client.embeddings.create(model=MODEL, input=texts)
    return [d.embedding for d in resp.data]

def run():
    conn = psycopg.connect(os.environ["DATABASE_URL"], autocommit=False)
    while True:
        with conn.cursor() as cur:
            cur.execute(CLAIM_SQL, (BATCH,))
            rows = cur.fetchall()
        conn.commit()

        if not rows:
            print("nothing to do, sleeping")
            time.sleep(30)
            continue

        ids, texts = zip(*[(r[0], (r[1] or "")[:8000]) for r in rows])
        vectors = embed(list(texts))

        with conn.cursor() as cur:
            for id_, vec in zip(ids, vectors):
                cur.execute(WRITE_SQL, (vec, MODEL, id_))
        conn.commit()
        print(f"embedded {len(ids)} rows")

if __name__ == "__main__":
    run()

Notes on what's deliberately not in there:

Step 4: Run it and watch progress

Start one worker first to confirm the pipeline works:

$ python worker.py
embedded 100 rows
embedded 100 rows
embedded 100 rows

In a separate psql session, watch progress:

SELECT
  count(*) FILTER (WHERE embedding IS NOT NULL) AS done,
  count(*) FILTER (WHERE embedding IS NULL) AS todo,
  count(*) FILTER (WHERE claimed_at IS NOT NULL AND embedding IS NULL) AS in_flight
FROM documents;

Expected output:

 done  |  todo  | in_flight
-------+--------+-----------
 12400 | 987600 |       200

Once one worker is stable, scale out. Two to four workers is usually the sweet spot before you hit either OpenAI rate limits or your Postgres connection pool.

Step 5: Rate-limit and cost controls

OpenAI's text-embedding-3-small has generous limits, but at four workers × 100 rows/sec you'll notice. Two practical controls:

Also: pick the smallest model that meets your recall target. text-embedding-3-small at 1536 dims is fine for most product-search and doc-retrieval use cases. Only jump to -large if you've measured a real recall gap.

Step 6: Handle new rows going forward

Once the backfill catches up, you need to keep new inserts embedded. Two options:

  1. Let the same workers keep running. The claim query already finds any row where embedding IS NULL. New inserts just get picked up on the next poll. Cheapest option.
  2. Trigger + NOTIFY. An AFTER INSERT trigger fires pg_notify('embed_todo', NEW.id::text), and workers LISTEN for lower latency. Worth it only if you need sub-minute freshness.

For most B2B SaaS use cases — semantic search over a product catalog, doc retrieval for a support bot — option 1 with a 30-second poll is genuinely fine.

Step 7: Build the vector index — after backfill

Now build the index. Do this after the backfill completes, and do it concurrently.

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

HNSW build on a million rows is not free — it's CPU-heavy and takes a while. But CONCURRENTLY means no write lock. Run it during a quieter period and monitor with pg_stat_progress_create_index.

Once built, drop the partial todo index — it's no longer useful:

DROP INDEX documents_embed_todo_idx;

Step 8: Verify and clean up

-- sanity check: no orphans
SELECT count(*) FROM documents WHERE embedding IS NULL;

-- sanity check: no stuck leases
SELECT count(*) FROM documents
WHERE claimed_at IS NOT NULL AND embedding IS NULL;

-- optional: drop the lease column if you don't need re-embedding later
ALTER TABLE documents DROP COLUMN claimed_at;

Keep embedding_model and embedded_at. You'll thank yourself when you switch models next year and need to know which rows to re-embed.

Common errors and how to fix them

"could not obtain lock on row" or workers stalling

You forgot SKIP LOCKED, or you're running the claim inside a longer transaction. The claim query must commit immediately. Don't wrap it with the embedding API call in a single transaction.

OpenAI 429 rate limit errors even with retries

Reduce worker count first, then batch size. Also check whether you're hitting the tokens-per-minute limit rather than requests-per-minute — long documents blow through TPM fast. Truncate more aggressively or chunk longer content.

Rows keep getting re-claimed by the same worker

Your claimed_at lease is shorter than your actual embedding call. Bump the interval in the claim query from 10 minutes to 30, or make the API call timeout shorter than the lease.

Postgres CPU spiking during backfill

Usually the partial index isn't being used. Run EXPLAIN on the claim query — if you see a Seq Scan, run ANALYZE documents. If the planner still ignores it, force it by narrowing the WHERE clause with a range: AND id BETWEEN %s AND %s, sharded per worker.

Dimension mismatch on insert

ERROR: expected 1536 dimensions, not 3072. You changed models mid-backfill. The vector(1536) column type is fixed. Either recreate the column at the new dimension or stick with one model per column.

The embedding IS NULL count isn't decreasing

Check that the write path is actually committing. A common bug: workers claim rows, embed them, but the UPDATE loop hits an exception on one row and rolls back all of them. Commit per-row or wrap each write in its own savepoint.

What this pattern is bad at

Honest tradeoffs:

For teams working through the messier version of this — legacy schemas, mixed content types, or an existing production system where you can't just add columns freely — the same lease pattern still works, but the migration story around it takes more care. That's the kind of thing our team walks through as part of legacy modernization engagements, and we've applied variants of this queue pattern in production systems from accounting SaaS to logistics marketplaces.

Frequently Asked Questions

How long does it take to backfill embeddings for 1 million rows?

It depends almost entirely on your embedding provider's rate limits, not on Postgres. With text-embedding-3-small and 2–4 parallel workers, most teams finish a million rows well within a day. Postgres itself is nowhere near the bottleneck at this scale — the API is.

Should I use pgvector or a dedicated vector database like Pinecone or Weaviate?

If your data already lives in Postgres and your query patterns join vectors with relational filters (tenant_id, category, date ranges), stay in Postgres with pgvector. You avoid a second system and get transactional consistency for free. Move to a dedicated vector DB only when you cross tens of millions of vectors or need specialized index types pgvector doesn't offer yet.

Can I run the backfill safely while my application is serving live traffic?

Yes — that's the entire point of this pattern. Each UPDATE touches one row and commits immediately, so writers are never blocked for more than milliseconds. The main risk is CPU load from the embedding column updates and any concurrent index builds; monitor pg_stat_activity and cap worker count based on what your instance can absorb.

What happens if I need to re-embed everything with a new model later?

Add a new column (embedding_v2 vector(...)) and repeat the same backfill pattern, keyed on WHERE embedding_v2 IS NULL. Keep the old column live until the new one is fully populated and validated, then swap application reads over. The embedding_model column pays off here — you always know which rows are on which version.

How do I estimate the cost of embedding my entire table?

Sample 1000 representative rows, count their tokens with tiktoken, multiply out to your row count, then apply your provider's per-token price. For a more thorough assessment tailored to your data shape and infrastructure, contact CodeNicely for a personalized assessment.

Keep reading

SaaS

Microservices vs. Modular Monolith: Pick the Right Architecture

Your monolith is slow to deploy and teams are stepping on each other. Before you split into microservices, here's how to tell if your real problem is coupling, pipeline design, or actual scale — and which architecture solves which.

 SaaS

AI Feature Flags Cheatsheet: What to Gate, Rollback, and Monitor

Standard feature flags gate users. AI features need a second layer that gates model behavior — version, prompt, embedding schema, output quality. Here's the reference cheatsheet.

 SaaS

Celery vs. Temporal: Pick the Right Workflow Engine for AI Jobs

Celery loses in-flight state when a worker dies mid-chain — and no amount of logging fixes that. Here's an honest head-to-head with Temporal for teams running multi-step LLM and document-parsing pipelines in production.

Found this useful? CodeNicely publishes engineering and product playbooks weekly. Browse the archive or tell us what you're building.