perf(index): add an ANN index so vector search stops scanning every embedding

[waterbro-8]

Summary

SPEC.md:537 lists embeddings_* (embedding) — pgvector HNSW as part of the
index contract. No ANN index DDL exists anywhere in the repository, so every
vector query is an exact sequential scan of the whole embedding table.

This was raised while auditing the data/index plane and appears to be untracked,
checked two ways: search/issues?q=repo:bytefolk/mem hnsw OR ivfflat OR "approximate nearest" returns total_count: 0 with
incomplete_results: false, and a local regex for the same three terms over all
172 open+closed issue and PR titles and bodies returns zero hits.

Source reproduction

On main @ 7a194f1eba4167d54bd46cf84cdbe86e00532319:

• server/internal/db/migrations/0001_init.sql:109-110 — the only
  embeddings_text index is idx_embeddings_text_file (file_id), with the
  comment HNSW index will be added by worker once we settle on a model dimension.
• The stated blocker no longer holds. Dimensions are pinned in code:
  server/internal/search/search.go:62-64 and
  server/internal/indexer/indexer.go:112-116 declare
  textEmbeddingSchemaDim = 768, visualEmbeddingSchemaDim = 512,
  faceEmbeddingSchemaDim = 512, and vector(768) / vector(512) are fixed
  column types.
• server/internal/db/migrations/0019_versioned_index_generations.sql:267
  defers again for the versioned table (Future ANN indexes must be route/dimension-specific expression or partition …).
• Full scans: server/internal/search/search.go:666,674,705,711,
  server/internal/relator/relator.go:205,211,272,278.
• server/internal/face/face.go:14 acknowledges the ceiling in a comment:
  thousands of faces. For larger corpora swap in pgvector HNSW + offline …

No new dependency is required: docker-compose.yml:9 and
docker-compose.test.yml:7 already run pgvector/pgvector:pg16 (test compose
pins it by digest), and HNSW has shipped in pgvector since 0.5.0.

Scope boundary

In scope: one migration adding ANN indexes plus the verification that queries
actually use them. Out of scope: changing distance function, model, chunking,
retrieval ranking, or the generation lifecycle (that is a separate report).

Deliberately not decided here: whether index_generation_vectors gets an index
in the same change. It is empty today, so building an index on it proves nothing
about the planner; it may be better to add it when the generation executor
exists.

Acceptance

• embeddings_text, embeddings_visual, and embeddings_face have ANN
  indexes with the operators their queries use (cosine, <=>).
• A regression check proves the planner takes them: EXPLAIN for the
  text and visual query shapes shows an index scan rather than a seq scan on
  a populated corpus, recorded in the PR validation ledger.
• Building the index on a populated table does not break ingest: the
  migration is verified against a corpus containing all three dimensions and
  the failure mode of an inconsistent-dimension table is stated.
• SPEC.md:537 and the two deferral comments no longer contradict the DDL.
• Recall measured before and after is reported, or the issue link of the
  harness that will measure it is recorded if recall cannot be measured yet.

Evidence level

E2 — source-level. The absence of ANN DDL and the exact-scan queries were
verified by reading every .sql, .go, and .py file in the tree; the planner
behavior and the recall effect were not measured, and no database of
meaningful size was run against.

Proposed triage

Applied on filing, per docs/maintainers/triage.md and the precedent in #135:
type:feature, area:server, evidence:e2-source, status:needs-triage.
Left to a maintainer: priority, and any change to the set below.

This is the limiting factor only once a workspace holds tens of thousands of
vectors, and the current corpus sizes in this repository's own fixtures are far
below that.

Raised from the data/index audit on 2026-09-08.

[xiaocui-big]

PR submitted: #213

What the PR does

1. New migration 0024_add_hnsw_ann_indexes.sql — adds HNSW indexes on all three embedding tables:

   • idx_embeddings_text_embedding_hnsw on embeddings_text (768-d, vector_cosine_ops)
   • idx_embeddings_visual_embedding_hnsw on embeddings_visual (512-d, vector_cosine_ops)
   • idx_embeddings_face_embedding_hnsw on embeddings_face (512-d, vector_cosine_ops)
   • Uses CREATE INDEX CONCURRENTLY (with goose NO TRANSACTION) to avoid blocking writes on populated tables

2. 0001_init.sql — removed the stale deferral comment about HNSW waiting on model dimensions

3. 0019_versioned_index_generations.sql — scoped the ANN guidance to index_generation_vectors explicitly; noted that legacy tables now have HNSW indexes via migration 0024

Not in scope (per the issue's boundary)

• index_generation_vectors — deliberately not indexed (empty table, no generation executor yet)
• Distance function / model / chunking / ranking changes
• Recall measurement (the benchmarks/recall harness exists but needs a populated corpus)

Still needs

• EXPLAIN verification on a populated database to confirm the planner picks up the indexes
• Recall before/after measurement once a test corpus is available

[waterbro-8]

Open implementation PR: #213 (perf(migrations): add HNSW ANN indexes for embedding tables). main still has the deferral comment on 0001_init.sql and no HNSW DDL.

I am not opening a duplicate. Review/CI on #213 is the remaining path. No merge or close.

[waterbro-8]

Implementation of the remaining acceptance (0025 HNSW + text continuation/fallback + planner/semantics tests): #219

#213 was the duplicate 0024 attempt and stays closed. #197 is the earlier HOLD on the DISTINCT ON planner; 219 rewrites the shipping text path instead of leaving that HOLD in place.