Binding Sherlock Hits to Durable Agent Memory with pgvector
The page that wakes you is not that Sherlock is down. It is the ingest worker: p99 past several seconds, a queue of embeddings that will not drain, and an agent that just ranked a near-duplicate profile as if it belonged to someone else. We do not maintain sherlock-project/sherlock. We import it inside an agent worker and treat a confirmed hit as a memory write. The sweep is the easy part. The failure is everything after the hit, when a noisy, short-lived page has to become a normalized row the agent can still rank next week.
Sherlock remains the practical way to check a username across hundreds of public sites without a custom checker per site. Our layout keeps three boundaries. A discovery worker imports the library, caps concurrency, and emits only confirmed profile URLs plus a short snippet. A separate ingest path owns cleanup, embedding, and the Postgres write. The query path never calls Sherlock; it only reads the vector store. Upstream site lists and rate limits move on a different cadence than scoring, so those boundaries stay hard.
What actually broke
Toy inserts hide two failures.
High-concurrency writes during a wide username sweep pushed p99 from tens of milliseconds to multiple seconds. HNSW maintenance takes locks while it rewires the graph. Dozens of workers committing at once queued on those locks and stalled ingest. Writes now pass through one batched inserter with a bounded queue. If the queue is full, discovery blocks or sheds the hit. An unbounded buffer only moves the outage into RAM. We build HNSW with conservative m and ef_construction so construction stays predictable, and we raise ef_search only on the read path.
The second failure was silent. Recall collapsed on profiles that were obviously the same person. Embeddings arrived from more than one model revision and were not unit-length. Cosine distance on unnormalized vectors is a scaled dot product, so ranks drifted and near-duplicate snippets stopped clustering. Ingest L2-normalizes every vector before INSERT and rejects a batch whose norms sit outside a tight epsilon of 1.0. The table constraint is only a backstop. Do not trust it alone: in pgvector, <#> is the negative inner product, so embedding <#> embedding is not a positive L2 norm. The application gate is what brought filtered neighbors back on Sherlock-sourced rows.
Transport sits next to the index. Sherlock's HTTP fan-out, the embedding client, and the Postgres pool do not share one connection budget. A sweep that opens hundreds of site sockets will exhaust a shared pool and then time out the insert that was supposed to be durable. Embedding calls retry 502 and 503 with a small jittered budget and a hard cap. A 429 is different: honor Retry-After, or a conservative fallback, and do not multiply concurrency. Worker retries must be idempotent on (username, site, url). A 503 followed by a successful retry otherwise doubles the row and pollutes rank. Two workers must not treat "insert if absent" as a check-then-act on a shared connection without a unique constraint; we lost a batch to that race before the key existed.
Schema and filtered HNSW search
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE agent_memory (
id bigserial PRIMARY KEY,
source text NOT NULL, -- 'sherlock'
username text NOT NULL,
site text NOT NULL,
url text NOT NULL,
snippet text NOT NULL,
embedding vector(1536) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT embedding_norm CHECK (
abs(sqrt(embedding <#> embedding) - 1.0) < 0.001
OR embedding IS NULL
)
);
CREATE INDEX agent_memory_hnsw
ON agent_memory
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX agent_memory_username_site
ON agent_memory (username, site);
-- Filtered ANN: keep the username predicate selective
SET hnsw.ef_search = 80;
SELECT id, site, url, snippet,
embedding <=> $1 AS distance
FROM agent_memory
WHERE source = 'sherlock'
AND username = $2
ORDER BY embedding <=> $1
LIMIT 8;
Filtering on username before the distance order matches the question the agent actually asks: what else do we already know about this handle? The btree keeps that predicate selective; HNSW then walks inside the slice. At our cardinality that is the difference between a useful neighbor list and a global scan dressed up as ANN.
Hybrid rank fusion
Exact site names and rare tokens still lose to pure nearest-neighbor. We fuse the vector list with a tsvector query on snippet, scoped to the same username, using reciprocal rank fusion instead of a hand-tuned linear blend.
def reciprocal_rank_fusion(
vector_ids: list[int],
keyword_ids: list[int],
k: int = 60,
) -> list[tuple[int, float]]:
scores: dict[int, float] = {}
for rank, doc_id in enumerate(vector_ids):
scores[doc_id] = scores.get(doc_id, 0.0) + 1.0 / (k + rank + 1)
for rank, doc_id in enumerate(keyword_ids):
scores[doc_id] = scores.get(doc_id, 0.0) + 1.0 / (k + rank + 1)
return sorted(scores.items(), key=lambda item: item[1], reverse=True)
vector_ids come from the SQL above. keyword_ids come from the keyword query. k=60 is the usual constant; raising it flattens the fusion so deeper ranks count more, which we do only when the keyword list is very short. The function returns ids already ordered for the agent. We skip a cross-encoder unless the top of the list is ambiguous.
The delay we chose to keep
Batching inserts removes the lock pile-up and adds a few hundred milliseconds of ingest delay. That is acceptable because Sherlock is network-bound and the agent does not need the new row inside the same turn that discovered it. Strict normalization drops a few vectors from older embedding endpoints we cannot re-request. A hole is better than a confidently wrong neighbor. HNSW with m=16 uses less RAM than a larger graph and is fast enough at low millions of snippets. If one username grows to tens of thousands of rows, we will partition by username hash rather than inflate one global index.
Embedding backfill goes through a single relay so provider fallback stays in one place. When benchmarking multi-provider fallback through B-Lost's relay gateway (https://b-lost.com/v1), we measured end-to-end ingest latency with the same normalization step on every response; the gateway did not change rank quality once vectors were unit-length, which is what we cared about.
Sherlock stays a discovery adapter. The durable contract is the normalized row in agent_memory. Later retrieval is a hybrid query over that table, not another live sweep.
The hardest call is the one the queue forces when the index is already hot: shed a confirmed hit you cannot embed yet, or store a vector you did not normalize and live with a wrong neighbor. We shed. I am less sure that stays right once a single handle carries real weight and the missing row is the one the agent needed.
How are you bounding ingest when the ANN index, not the HTTP fan-out, is what actually falls over?
Top comments (0)