October 2, 20265 min read

Adding pgvector Search Without Serving Stale Embeddings

Use Ptah and a real local model to add vector search, catch source changes, test retrieval, and refuse a cutover whose evidence is out of date.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A help center already stores its articles in PostgreSQL. Adding a vector column is a schema change. Making search use vectors for the current text is a data problem: an editor can change an article after its embedding was built.

We tested that boundary with ten short help articles and a real local embedding model. During the run, we revised a refund policy, removed an old article, and added an account-recovery article. Then we changed the refund policy again after verification and tried to activate the earlier result.

The run used Ptah 0.11.4, PostgreSQL 18.6, pgvector 0.8.6, and Ollama 0.34.2 serving all-minilm:22m. The model returned 384-dimensional vectors. The supporting files contain the source data, model identity, full specification, queries, and recorded results.

Keep schema deployment and vector construction separate

The starting database had app.articles: an ID, title, body, and revision counter. The first Ptah migration created that table. We inserted the existing articles before applying the next migration:

CREATE EXTENSION IF NOT EXISTS vector;

ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum

This enabled pgvector without replacing the articles. The later inference commands own the embedding column, generation metadata, change capture, and vector index. Their completion is separate from the SQL migration history.

Our specification joins each article’s title and body, uses its revision to recognize changes, and selects outbox consistency. Its target is app.articles.embedding_v1, with vector storage, cosine distance, and an HNSW index. The revision must increase when the application changes an input.

Check the actual provider before sending article text

ptah inference probe --spec spec.yaml --format json
ptah inference plan --spec spec.yaml --db-url "$DB_URL"

The probe checked the endpoint using fixed test strings. It measured one 384-value vector per input and a complete batch response. A second specification deliberately declared three dimensions; the probe returned 1 and reported the mismatch before any database row was sent.

The plan counted ten source rows and named the title and body fields that would leave PostgreSQL. In this lab they went to Ollama over loopback in the isolated test environment. endpoint_class: local is a declaration; it does not prove where an arbitrary endpoint runs or what it retains.

We recorded the downloaded model digest in model.revision and in the model inventory. Ptah includes that declaration in the generation identity. It does not independently attest that an Ollama tag still serves those bytes. Keep the model runtime and its inventory under the same release control as the specification.

Backfill while source changes remain visible

ptah inference prepare --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1
ptah inference backfill --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1 --batch-rows 4

Preparation installed the outbox and recorded the starting boundary. Backfill embedded all ten initial articles. We then applied changes.sql: one update, one deletion, and one insertion.

ptah inference catchup --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1 --batch-rows 4
ptah inference index --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1
ptah inference verify --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1

Catch-up reported:

caught up to transaction 804: 3 changed rows, 1 tombstoned
  - pruned 3 processed event(s) from the outbox

The database had ten current source rows and ten current vectors. Verification passed, and the catalog showed a valid HNSW index using vector_cosine_ops over vector(384). Trying to index immediately after backfill, before catch-up, had returned 2 because that step skipped the required lifecycle phase.

These commands run once and exit. The outbox captures subsequent changes, but an application that keeps accepting edits also needs an ongoing catch-up schedule. A completed backfill does not maintain search indefinitely.

Test what search returns

Fresh vectors can still return poor answers. We fixed six synthetic questions and their expected article IDs before evaluating the model. Each case required its answer among the first three results.

ptah inference evaluate --spec spec.yaml --db-url "$DB_URL" \
  --corpus corpus.yaml --min-recall 1 --min-exact-agreement 1

All six expected answers ranked first. The evaluation reported:

  - recall 1.000, MRR 1.000, NDCG 1.000 over 6 cases
  - the index agrees with an exhaustive search on 1.000 of results, over 6 cases

We repeated the evaluation with sequential scans discouraged and hnsw.ef_search=40; an accompanying EXPLAIN ANALYZE confirmed use of the HNSW index. The same checks passed.

This verifies wiring and a small, fixed set of expectations. It does not estimate retrieval quality on real support traffic or measure index performance. The query settings and full report are in the supporting files.

Refuse approval when the source moves

An initial cutover without approval returned 2 and printed a plan digest. Before approving it, we changed the refund policy again. Supplying that old digest produced this refusal:

cutover refused:
  - 1 source changes are unprocessed
  - the approval is bound to plan b13f1bba2428 and this plan is b0ce72430348
  - verification did not pass and nothing was accepted

A new verification returned 1 and identified article 2 as stale. We ran catch-up, verification, and the same retrieval evaluation again. Each passed. Then we requested the current plan and approved its digest:

ptah inference cutover --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1
ptah inference cutover --spec spec.yaml --db-url "$DB_URL" --run-id help-center-v1 \
  --approve "$PLAN_DIGEST" --approver "help-center-release"

PLAN_DIGEST is the value from that environment’s freshly reviewed plan. The first command intentionally refuses without it. Our approved command returned zero and recorded the active generation.

We embedded a refund question through the same model and executed that query after cutover. The refund article was its first result. In a separate transaction, we changed that article again without rebuilding its vector. Search omitted it until we rolled the transaction back. This avoids serving a stale embedding by temporarily excluding an edited article; catch-up must restore its availability.

This was the first generation, so there was no previous generation to roll back to. Retrieval evaluation was a separate release check; a passing cutover does not certify the quality of future queries.

Reproduce the result and retain the limits

On another empty database, we replayed both SQL migrations and the complete inference sequence with the same source changes. The final column definitions, indexes, triggers, trigger functions, and pgvector version matched the first database. Every current article had a 384-dimensional vector and the expected source revision. The retrieval checks and direct search result matched too.

The PostgreSQL catalog comparison is the replay check for this example. The SQL and HCL export round trips still report trigger differences; see the recorded limits before using those exports as desired state.

The useful deployment boundary was still demonstrated: a source write made the earlier approval unusable, catch-up repaired the stale vector, and an explicitly approved current generation became available to the tested query.

Put Ptah to work

Example files

← All articles