Embedding Backfill Pipeline

Running the embedding-backfill worker with FOR UPDATE SKIP LOCKED.

Source: docs/operations/embedding-backfill-runbook.md

Embedding Backfill Runbook

Tool: tools/embedding_backfill.py Audience: Substrate operators running backfill jobs on sandbox-02 Related spec: SPEC-063 (artifact embeddings)

Overview

The backfill tool scans artifact_embeddings for rows where the embedding column is NULL or contains a P1 mock vector (embedding_model = 'mock-hash@p1'), then re-embeds the corresponding artifact title using the configured provider.

It uses FOR UPDATE SKIP LOCKED so concurrent operator runs or scheduled workers never double-process the same row. Each batch is committed independently, so a mid-run failure leaves completed rows intact.

When to run

Trigger Example
Provider upgrade (P1 mock → real OpenAI) After setting SCIDEX_EMBEDDING_PROVIDER=openai
Stale embedding refresh --since 2026-01-01 to re-embed rows older than a date
New artifact type backfill --artifact-type dataset after enabling a new type
Post-restore validation --dry-run --report to check coverage

Prerequisites

# Verify the tool is importable
cd /home/ubuntu/scidex-substrate
PYTHONPATH=src python tools/embedding_backfill.py --help

For real provider embeddings:

# OpenAI provider
export SCIDEX_EMBEDDING_PROVIDER=openai
export SCIDEX_EMBEDDING_MODEL=text-embedding-3-small
export OPENAI_API_KEY=sk-...

Standard workflow

Step 1 — Check current coverage

Always start with a dry assessment:

SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --report

Example output:

=== Embedding coverage report ===
Generated:        2026-05-18T10:00:00+00:00
Total artifacts:  48230
  Real vectors:   0      (0.0%)
  Mock vectors:   48000
  Null vectors:   230
  No row:         0
  Needs backfill: 48230

By artifact type:
  article                         real=     0  mock= 45000  null=   200  no_row=     0
  dataset                         real=     0  mock=  3000  null=    30  no_row=     0

Step 2 — Dry run to confirm scope

SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --dry-run --limit 500

Dry-run output shows how many rows would be processed without making any writes:

[dry-run] Backfill complete:
  Processed:  500
  Succeeded:  0
  Skipped:    47730
  Failed:     0

Step 3 — Run the backfill

Mock provider (P1, default):

SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --limit 5000

Real OpenAI provider (P2):

SCIDEX_EMBEDDING_PROVIDER=openai \
SCIDEX_EMBEDDING_MODEL=text-embedding-3-small \
OPENAI_API_KEY=sk-... \
SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --limit 5000

Successful output:

Backfill complete:
  Processed:  5000
  Succeeded:  4997
  Skipped:    0
  Failed:     3
  First error: article:abc123 v1: <provider error>

Step 4 — Verify coverage after

SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --report

Repeat steps 3–4 until Needs backfill: 0 or the desired coverage is reached.

CLI reference

usage: embedding_backfill.py [-h] [--dsn DSN] [--dry-run]
                              [--artifact-type TYPE] [--limit LIMIT]
                              [--since DATE] [--report] [--json]
                              [--batch-size N] [-v]
Flag Description
--dsn DSN Postgres DSN (default: $SCIDEX_DSN or postgresql:///scidex_v2)
--dry-run Show what would be processed; no DB writes
--artifact-type TYPE Only backfill this artifact type (e.g. article, dataset)
--limit N Max rows to process per run
--since DATE Only backfill rows with computed_at older than DATE (YYYY-MM-DD or YYYY-MM-DDTHH:MM:SS); also includes NULL embeddings
--report Print coverage stats and exit (read-only)
--json Output JSON instead of human-readable text
--batch-size N Rows per DB transaction (default: 200)
-v / --verbose Enable debug logging

Exit codes:

  • 0 — success (or dry-run / report with no failures)

  • 1 — one or more rows failed to embed, or bad CLI args

Environment variables

Variable Description
SCIDEX_DSN Default Postgres DSN used when --dsn is not given
SCIDEX_EMBEDDING_PROVIDER mock (default, P1) or openai (P2)
SCIDEX_EMBEDDING_MODEL Model identifier stored in embedding_model column
OPENAI_API_KEY Required when SCIDEX_EMBEDDING_PROVIDER=openai

Batch strategy and concurrency

The tool uses FOR UPDATE SKIP LOCKED on artifact_embeddings rows within each batch transaction. This means:

  • Multiple operator sessions or cron runs can run concurrently without double-embedding or deadlocking.

  • Rows locked by one worker are transparently skipped by others.

  • If a worker crashes mid-batch, the transaction rolls back and those rows become available to the next run.

Default batch size is 200 rows per transaction. Increase --batch-size for bulk runs; decrease for rate-limited providers.

Common patterns

Full backfill by type (production upgrade P1 → P2)

for TYPE in article dataset figure model signal; do
  echo "=== Backfilling $TYPE ==="
  SCIDEX_EMBEDDING_PROVIDER=openai \
  OPENAI_API_KEY=sk-... \
  SCIDEX_DSN=postgresql:///scidex_v2 \
    python tools/embedding_backfill.py \
      --artifact-type "$TYPE" \
      --batch-size 100 \
      --verbose
done

Re-embed rows older than a specific date

SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --since 2026-03-01 --dry-run

JSON output for scripting / monitoring

SCIDEX_DSN=postgresql:///scidex_v2 \
  python tools/embedding_backfill.py --report --json | jq .needs_backfill

Scheduled cron (via systemd or crontab)

# /etc/cron.d/embedding-backfill
# Run nightly at 02:00, limit 10000 rows per run
0 2 * * * ubuntu \
  SCIDEX_DSN=postgresql:///scidex_v2 \
  SCIDEX_EMBEDDING_PROVIDER=openai \
  SCIDEX_EMBEDDING_MODEL=text-embedding-3-small \
  OPENAI_API_KEY_FILE=/etc/scidex-substrate/openai-key \
    python /home/ubuntu/scidex-substrate/tools/embedding_backfill.py \
      --limit 10000 \
      --json >> /var/log/scidex/embedding-backfill.log 2>&1

Troubleshooting

“relation artifact_embeddings does not exist”

pgvector is not installed or migration 0226 hasn’t run. Run the schema reconciliation tool first:

python tools/ops/reconcile_prod_v2_schema.py --apply

“openai package required for provider=openai”

pip install openai

“OPENAI_API_KEY env var must be set”

Export the key or source from a secrets file before running.

“unsupported embedding provider: ‘cohere’”

Only mock and openai are supported. Contributions for additional providers should follow the embed_text dispatch pattern in tools/embedding_backfill.py.

High failure rate

Check --verbose output for the first error. Common causes:

  • Provider API quota exceeded (reduce --batch-size or add a rate limiter)

  • Title column is empty for many artifacts (mock provider handles this; real providers get <empty>)

  • Network connectivity to provider endpoint

Rows not being picked up

If --report shows candidates but the run processes 0 rows, another process may be holding locks. Use:

SELECT pid, query, state, wait_event
FROM pg_stat_activity
WHERE query LIKE '%artifact_embeddings%';

The FOR UPDATE SKIP LOCKED strategy means these rows will be available again once the other process releases its transaction.

Database impact

Each batch transaction executes:

  1. One SELECT ... FOR UPDATE OF ae SKIP LOCKED to lock the batch.

  2. One UPDATE artifact_embeddings SET embedding = ... per row.

  3. Implicit COMMIT at the end of the async with conn.transaction() block.

The ivfflat cosine index (artifact_embeddings_cosine_idx) is not updated during UPDATE — pgvector updates the index incrementally. For very large backfills (>1M rows), consider running REINDEX INDEX CONCURRENTLY artifact_embeddings_cosine_idx after the backfill completes to improve search accuracy.