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

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.