Vector search alone kept missing things, so I gave Postgres three ways to find it

I built a searchable record of everything I have worked on: four months of session transcripts, 548 files, 105,336 records, about 4 million tokens of work. It sits in Postgres 17 with pgvector and pg_trgm, embedded locally through Ollama with nomic-embed-text at 768 dimensions. No API, no per-query cost.

The retrieval took four rewrites. Each failure taught me something specific.

One query language cannot find both meaning and exact strings

Vector search is good at "that thing about deliverability" and bad at ep-noisy-hall-aw73xifb. Full-text search is the reverse. Running one and hoping is how you get a search box people stop trusting.

Postgres will run both in a single statement, so the final version runs three lanes and fuses them with reciprocal rank fusion:

1. Strict AND via websearch_to_tsquery — every term must be present
2. Prefix OR via to_tsquery with :* terms joined by | — partial words, any of them
3. Vector cosine distance over halfvec chunks, HNSW indexed

RRF scores each result as 1/(k + rank) per lane with k = 60, weighted 1.4 / 0.7 / 1.0. A document that ranks moderately in two lanes beats one that ranks first in a single lane, which is exactly the behaviour you want.

The prefix-OR lane exists because of a specific failure. websearch_to_tsquery has AND semantics: every word must appear. Searching my own history for proofpoint blocklist deliverability returned nothing, in a corpus where I had spent a week on precisely that. All three words never landed in the same episode.

Long documents win by default unless you stop them

The first ranked results were all my longest sessions. ts_rank rewards term frequency, and a 100,000-character transcript contains everything at least once.

The fix is two normalization flags on ts_rank_cd:

ts_rank_cd(fts, query, 2|32)

2 divides the rank by document length. 32 maps it to rank/(rank+1), which compresses the top end so one enormous document cannot dominate. Both, together, or long transcripts bury the short precise ones.

Confidence scores you cannot defend are worse than none

I shipped a version that labelled results strong, likely and weak, computed as a ratio to the top score. It looked authoritative. It was meaningless: RRF scores cluster tightly, so forty results came back labelled "strong" for a query with two real hits.

I replaced the banding with match provenance. Every result now says which lane found it: exact, word, or meaning. That is a fact about the query rather than a guess about relevance, and it tells you whether to trust the hit.

Three ingestion bugs, all silent

The glob. ~/.claude/projects/*/*.jsonl finds 45 files. The same glob made recursive finds 570. Subagent transcripts sit a level deeper than their parent session, and they are the overwhelming majority: more than 90% of my history was invisible, and nothing errored.

The unique key. Subagent transcripts share their parent's sessionId. Keying on (session_id, seq) meant each subagent silently overwrote its parent. Switching to (source_file, seq, is_sidechain) recovered 500 episodes and 12,821 tool calls that had been quietly discarded on every run.

The chunk cap. Twelve chunks of 1,600 characters per episode left 81% of the largest episode unindexed. It is now 40 chunks of 1,200 with 150 of overlap, with a retry at half size when an embedding call fails.

Redact on the way in

Transcripts contain API keys, connection strings and tokens. Nine regex families catch them at load time and replace each with a stable placeholder derived from a hash of the secret, so the same key always renders as <NEON_PW_a3f1>. Stable matters: you can still see that two sessions used the same credential without the credential being present.

That pass took 11,931 hits down to zero on a leak scan.

What it cost

A full load takes about 9 seconds. Embedding takes about 29 minutes on the laptop, once. Queries return in 60 to 90ms against 2,047 episodes.

If you are building something similar, spend your tuning time on the ranking flags and the ingestion edge cases rather than the embedding model. My retrieval improved far more from 2|32 and a recursive glob than it ever did from anything I changed about the vectors.