The application team called it a backfill. The database saw a sustained write-heavy migration across millions of rows, large vector values, extra indexes, WAL growth, replica lag, dead tuples, and autovacuum falling behind. Embedding backfills are not background chores. They are production database events.

Short Version

An embedding backfill should be operated like a controlled data migration. Batch size, commit frequency, worker concurrency, index timing, autovacuum behavior, WAL retention, checkpoint pressure, replica lag, and rollback strategy all matter.

The safest pattern is usually: write new embeddings into a separate nullable column or table, process in bounded idempotent batches, throttle based on database signals, build or rebuild vector indexes after the bulk load when appropriate, validate retrieval with a fixed query set, then switch reads deliberately.

Do not let an AI feature team run an unbounded embedding job against production Postgres. The failure mode looks like database instability, not model instability.

Situation

Embedding backfills usually arrive after a prototype already exists. The application team has chunks, a model, and a table with nullable embeddings; the database team inherits the job of making the bulk write safe.

The Problem

Embedding pipelines are often designed from the application outward:

  1. Select rows without embeddings.
  2. Send text to an embedding model.
  3. Update the row.
  4. Repeat until done.

That loop hides the database cost. Every update creates WAL. Every update creates dead tuples under MVCC. Indexes must be maintained. Replicas must receive and apply changes. Long transactions can block vacuum. Large batches can create bursty checkpoints. Failed jobs can leave partial state. Rollbacks can be more expensive than the work being rolled back.

The embedding vector itself can be large enough to change the storage profile of the table. If the same table also serves OLTP reads, the backfill competes with normal application traffic.

Core Technical Explanation

PostgreSQL updates do not overwrite rows in place in the simple mental model. Under MVCC, an update creates a new row version and leaves old versions for vacuum to reclaim. For embedding backfills, that means a table with millions of existing chunks can churn heavily when a nullable embedding column is populated.

The pressure points are connected:

flowchart TD
    Worker[embedding workers] --> Batch[batch updates]
    Batch --> WAL[WAL growth]
    Batch --> Dead[dead tuples]
    Batch --> Index[index maintenance]
    WAL --> Replica[replica lag]
    WAL --> Checkpoint[checkpoint pressure]
    Dead --> Vacuum[autovacuum work]
    Index --> Bloat[index bloat]
    Vacuum --> Health[database health]

Batch size controls transaction duration and burst size. Too small, and overhead dominates. Too large, and locks, WAL bursts, rollback time, and replica lag become harder to control.

Worker concurrency controls write pressure. More workers may increase embedding throughput while degrading database latency. The correct throttle is not “as fast as the model provider allows.” It is “as fast as Postgres can absorb while keeping OLTP healthy.”

Index timing controls maintenance cost. Updating rows while a vector index already exists maintains that index row by row during every update — the same general PostgreSQL principle that applies to any index during a bulk load, not something specific to vector indexes. For a first backfill on a table with no existing vector index, loading embeddings first and building the HNSW or IVFFlat index afterward avoids that per-row maintenance cost entirely. This is a general bulk-load pattern; confirm it still applies if the backfill is instead adding rows to a table that already has a live index in production use, since dropping and rebuilding that index has its own availability tradeoff.

In Practice

Use an explicit job table instead of scanning the base table repeatedly:

CREATE TABLE embedding_backfill_jobs (
  chunk_id bigint PRIMARY KEY,
  status text NOT NULL DEFAULT 'pending',
  attempts int NOT NULL DEFAULT 0,
  locked_at timestamptz,
  completed_at timestamptz,
  error text
);

Workers claim a bounded number of jobs, generate embeddings, update the target rows, and mark jobs complete in short transactions. Keep job claiming idempotent. A worker crash should not create duplicate chunks or leave unknown state.

Prefer a separate embeddings table when the source table is hot:

CREATE TABLE chunk_embeddings (
  chunk_id bigint PRIMARY KEY,
  embedding_model text NOT NULL,
  content_hash text NOT NULL,
  embedding vector(1536) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

This avoids rewriting a wide source row just to attach a vector. It also makes model-version migrations easier because a new model can be loaded side by side before reads switch.

Throttle on database metrics. If replica lag exceeds the threshold, pause. If autovacuum falls behind, pause. If checkpoint or WAL pressure rises, reduce batch size. If OLTP latency degrades, stop the backfill before users discover it.

Where It Breaks

Failure modeProduction symptomMitigation
Batch too largeLong transactions and painful rollbackUse small bounded commits
Too many workersOLTP latency and replica lag riseCentral throttle and worker limits
Backfill updates hot tableTable and index bloat grow quicklySeparate embedding table or staged writes
Index exists before bulk loadWrite amplification during every updateConsider load first, index after
Autovacuum cannot keep upDead tuples and storage growthTune table autovacuum and pause jobs
No rollback planHalf-migrated model version leaks into searchVersion reads and switch by feature flag
Provider retry stormDatabase receives duplicate writesIdempotent jobs and content hashes

Security and Tenancy Notes

Backfill workers need the minimum database privileges required to read source text and write embeddings. Do not run them with broad application owner credentials if the corpus contains tenant or regulated data.

Tenant boundaries still matter in background jobs. A job queue that accidentally mixes tenant-scoped source records with a shared output table can create retrieval exposure later. Store tenant ID and source document ID with the embedding row, and validate negative retrieval cases before enabling the new vectors.

If embeddings are generated by an external provider, data-processing and retention rules need review — this is a contractual and compliance question for whichever team owns the data-processing agreement with that provider, not a database configuration setting.

Cost Notes

Backfill cost has four parts:

  • Embedding generation cost.
  • Worker compute cost.
  • Database write, WAL, storage, and backup growth.
  • Larger or longer-running indexes and replicas.

The surprise is backup and replica cost. A massive backfill can increase storage and WAL retention, and the new vectors may be captured in backups immediately. If the model migration is later rolled back, the database may still carry the storage and cleanup cost.

Observability Notes

Track:

  • Rows processed per minute.
  • Jobs pending, running, failed, and retried.
  • Average and p95 transaction duration.
  • WAL generation rate.
  • Replica lag.
  • Autovacuum activity and dead tuples on target tables.
  • Table and index size growth.
  • OLTP query latency during the backfill.
  • Vector query correctness before and after read cutover.

Add a kill switch. Backfills need operational controls, not only dashboards.

Backup, Restore, and DR Notes

A backfill can cross backup boundaries. If a restore lands in the middle of the migration, the system must know whether to resume, roll forward, or discard partial embeddings.

Store model version, content hash, and backfill batch ID. Make the read path select only completed model versions. During restore, resume from the job table and recompute rows whose content hash no longer matches.

For DR, confirm the target environment can run the same embedding jobs or has a restored copy of the completed vectors. Without that, the database RTO and retrieval RTO diverge.

Decision Checklist

  • Is the embedding stored in the source table or a separate table?
  • What is the maximum transaction size and rollback time?
  • How many workers can run before OLTP latency moves?
  • What metrics pause or slow the backfill?
  • Are vector indexes built before or after the bulk load?
  • Are model versions isolated in the read path?
  • Can the job resume after crash, failover, or restore?
  • Is autovacuum tuned for the target table?
  • Has replica lag been tested under backfill load?
  • Is there a feature flag for read cutover?

What to Do Next

Problem: Embedding backfills get designed from the application outward — select rows, embed, update, repeat — hiding the database cost of WAL growth, dead tuples, index maintenance, and replica lag until production feels it.

Solution: Operate the backfill like a controlled migration: bounded idempotent batches via a job table, a separate embeddings table when the source is hot, index builds after the bulk load, and throttling driven by replica lag, autovacuum, and OLTP latency signals.

Proof: The backfill can be paused mid-run without data loss or duplicate writes, and OLTP query latency stays within its normal band while the backfill is running.

Action: This week, confirm the backfill job has a kill switch and is throttled on at least replica lag and autovacuum activity, not just embedding-provider rate limits.

Sources to Verify