A cross-client memory store for coding agents

Every coding agent keeps its own session log in its own format on the machine where it ran, prunes it on its own schedule, and searches it with its own built-in tool. Three clients on two machines is three histories, three search tools, and a retention window that quietly deletes the thing you wanted last month. This is the architecture of the store that consolidates them, and what seeding 640k messages into it taught.

Provenance. Measured 2026-08-09 and 2026-08-10 on a self-hosted deployment: Postgres 18 with pgvector, PostgREST, and a CPU embedding worker, all in one Compose stack on a 2-core/2GB container allocation, ingesting from one workstation. Corpus at time of writing: 642,496 messages across three agent clients plus a work-ledger and an agent memory file.

TL;DR

Session logs are append-only files, so ingest is a byte-offset or line-count checkpoint per file, not a diff. Re-reading a 30MB log every five minutes to find four new lines is the thing to avoid.
One Postgres does both retrieval modes. Full-text search answers “where did I write this exact string”, vector search answers “where did I discuss this idea”, and they are different questions with different failure modes.
Filtering a vector search needs its own design. The index applies the filter after it collects candidates, so a selective filter returns short, and the workaround for that scans the table when the filter is broad: 9,625 ms against 18 ms for the index path on the same query.
Measure recall against exact search before trusting an approximate index, and pin the query vectors when you do. An unpinned OFFSET sample here reported 100% recall for something that measures around 92%.
Embedding 640k rows on CPU is a throughput problem with one lever that works (parallel workers on disjoint row batches) and one that inverts (more intra-op threads per call).
A background worker started from a framework lifecycle hook that the framework silently skipped ran for weeks doing nothing while its health endpoint reported healthy. Heartbeat-or-it-did-not-happen.
The same worker later held one uncommitted transaction open across its idle sleeps, which blocked every DDL statement and then every query behind it. A loop that touches a database has to commit on the path where it finds nothing to do.
Expose retrieval as ordinary HTTP. Any client that can make a request can use it - protocol adapters are a thin layer on top, not the architecture.

Topology

What lives in it

Source	Shape	Checkpoint	Why it is here
Agent session logs	jsonl, one line per message, append-only	byte offset	The bulk of the corpus and the only copy past local retention
Prompt history file	jsonl, one line per prompt	line count	A client that logs prompts but not full sessions still has recoverable intent
Account export	zip or json, one array of conversations	one-shot import, idempotent	Web-client history that has no local log at all
Work ledger	sqlite, one row per session summary	max id	The “what did I do on project X” question, already summarized
Agent memories	one json file	whole-file	Small, changes rarely, cheap to re-read

The unifying key is session_key = <source>:<host>:<external_id>, and messages are keyed (session_key, ordinal) where ordinal is the line index. That choice is what makes incremental ingest idempotent: re-reading a file from an earlier offset re-derives the same keys, and the upsert merges rather than duplicating.

Incremental ingest

The ingester is a CLI on a five-minute timer, not a daemon. Per source file it stores (size, mtime, offset, line count) in an ingest_state table, reads from the stored offset, and writes back the new one. A file whose size and mtime match the checkpoint is skipped without opening it.

Batched writes go through PostgREST with Prefer: resolution=merge-duplicates, which turns the insert into an upsert on the primary key.¹ Two consequences worth knowing before adopting the pattern:

Every object in one batch must have the same keys. PostgREST rejects a heterogeneous batch with PGRST102 All object keys must match.² A source that only sets optional columns when non-null will produce exactly that batch. Pad the missing keys with null in the client before posting.
Order matters across tables. Messages carry a foreign key to sessions, so the session rows have to land first. Discovered the way these things are usually discovered: a foreign-key violation on the very first run of a new source.

Because the timer reads the live log file, a session’s messages are searchable minutes after they are written - closing the client is not what triggers ingest, and nothing is lost if the machine is off for a day.

Two retrieval modes, one database

Full-text search runs in Postgres against a generated tsvector column with a GIN index, exposed as a function through PostgREST. Vector search runs against a pgvector column with an HNSW index.³ They answer different questions:

Mode	Good at	Bad at	Cost
Full-text	Exact identifiers, error strings, file paths, flag names	Paraphrase, concepts, “the thing about locking”	Index maintained inline on write
Vector	Concepts, paraphrase, “what was that idea about X”	Exact tokens, rare identifiers, anything the embedding model never saw	Backfill cost + index maintenance per row

Keep the query syntax honest per mode. Postgres websearch_to_tsquery accepts quoted phrases, OR, and -negation, but not the FTS5-style AND/NOT an agent might have learned from a different tool; multi-word queries are joined with OR before they reach it so a two-word search does not silently require both.⁴

Vector search needs the query embedded with the same model as the corpus, so it belongs behind a small service rather than in each client. That service is also where the embedding model lives, which keeps the model out of every consumer.

Filtering a vector search

A WHERE clause and a vector index do not compose the way a WHERE clause and a B-tree do. HNSW collects ef_search candidates from the whole index first and the filter runs against those candidates, so a filter selective enough to reject most of them returns short: restricted to the smallest source in this corpus (3,703 of 644,030 rows), a LIMIT 10 search returned 2 rows.³

Materializing the filtered subset and scanning it exactly fixes that, and was the only path here for a day. It also takes the index away from a broad filter, where the subset is most of the table:

Filtered search, LIMIT 10	Latency	Rows
Broad filter (593k of 644k rows), materialized subset	9,625 ms	10
Broad filter, plain index scan	18 ms	10
Selective filter (3,703 rows), materialized subset	21 ms	10
Selective filter, plain index scan	74 ms	2
Selective filter, index scan with iterative scan enabled	1,618 ms	10

The resolution probes the index and falls back to the exact subset only when the probe returns fewer rows than were asked for, which is the signature of the post-filter discard. End to end that endpoint went from 8.4s to 0.21s.

Measuring the recall this costs needs care, and the first attempt here got it wrong in a way worth copying the fix for. Drawing a query vector with OFFSET n LIMIT 1 and no ORDER BY returns a different row per run, so two runs of the same “offset 5000” probe disagreed - 10 of 10 against exact one time, 6 of 10 the next - and a single such draw had already been written up as “identical to exact”. Pinning the sample by primary key and widening it to 20 vectors gives a figure that holds still, top-10 against exact search:

Index	Recall	Worst single vector
Full precision	183/200 (91.5%)	0/10
Half precision	185/200 (92.5%)	0/10

Approximate means approximate: the index was never returning exact results, and the honest number for both is around 92%, not 100%. Both share the same worst vector, consistent with duplicate embeddings and tied distances rather than a graph failure.

Two adjacent behaviours, a day between them:

A similarity threshold belongs after the query rather than in the WHERE clause. With the threshold in SQL, a query that legitimately has few results above it looks the same as a recall failure, so every such query takes the slow fallback.
HNSW returns at most ef_search rows whatever the LIMIT says. At the default 40, a LIMIT 50 came back with 40 rows, tripped the fallback, and measured 13.8s; deriving ef_search from the requested row count returned it to 0.19s.

What the index costs

Component of the messages table	Size
Table including TOAST	2,814 MB
HNSW index	1,041 MB
Vector column	945 MB
Full-text GIN index	387 MB
Stored message content	1,133 MB

Vectors dominate: the column and its index together are 1,986 MB against 1,133 MB of message content. The index earns that. A top-10 search takes 2.8 ms warm and 26 ms cold, and with index scans disabled the same query took 2,535 ms - then 3,005 ms on a second run, because table plus TOAST is 2,814 MB against a 2GB container allocation, so the exact scan re-reads roughly 1.6GB from disk every time and never warms.

The index is also the half of that pair worth shrinking, because it is what gets traversed on every search. pgvector can index the cast rather than the column - hnsw ((embedding::halfvec(384)) halfvec_cosine_ops) - which leaves the stored vectors at full precision and the write path untouched. Here that took the messages index from 1,043 MB to 597 MB and the database from 4,415 MB to 3,946 MB, at the recall shown above: 185/200 against 183/200, so no quality was traded for the 43%. Three things to know before copying it:

An expression index is only used when the query repeats the cast verbatim. embedding <=> $1::vector against a halfvec index silently becomes a sequential scan, which is the 2,535 ms path. Create the new index and drop the old one in separate steps with the application deploy in between.
A parallel index build asks for dynamic shared memory - 265 MB here - and Docker gives a container 64 MB of /dev/shm by default. It fails with could not resize shared memory segment ... No space left on device, which reads like a full disk and is not one. Building serially with max_parallel_maintenance_workers = 0 works around it (9m29s for 644k rows, spilling past a 256 MB maintenance_work_mem after 180,983 tuples); raising shm_size to 1 GB fixed it properly and the same build then took 5m35s with two workers. Note shm_size is a ceiling rather than a reservation, and the pages used are charged to the container’s memory limit: writing 300 MB into /dev/shm moved this container from 86 MiB to 402.6 MiB, and back to 100 MiB when the file was deleted.
A freshly built index is cold, and cold is seconds: the first limit=50 search after the swap took 5.37s before settling to 0.047-0.18s. The warm numbers in this doc are not a claim about the first query after a deploy.

Embedding throughput on CPU

Seeding was 640k rows through a 384-dimension CPU model.⁵ The measured scaling matrix, one configuration change at a time:

Configuration	Rows/min
2 CPU, 1 worker, batch 8	64
4 CPU, 1 worker, batch 16	128
8 CPU, 1 worker, batch 32	106
8 CPU, 4 workers x batch 8, 2 intra-op threads	453
8 CPU, 6 workers x batch 8, 2 intra-op threads	589

Intra-op thread scaling inverted past four: the 8-CPU single-worker row is slower than the 4-CPU one. What scaled was worker threads on disjoint row batches - each worker claims its own rows with SELECT ... FOR UPDATE SKIP LOCKED, so six workers never fight over the same batch.⁶ The inference runtime releases the interpreter lock during the forward pass, so the threads genuinely overlap.

Two things to know before copying the numbers. The rate is content-dependent - the same configuration ran at 938 rows/min through a stretch of short tool results and 589 through prose - so measure your own corpus rather than extrapolating from a row above. And at six workers neither side was saturated (the worker container at roughly 3 of 8 cores, Postgres at about 1 of 2), which means the ceiling was coordination, not capacity: adding workers past that point buys progressively less.

The backfill query itself becomes the bottleneck as the queue drains. WHERE embedding IS NULL ORDER BY ts DESC LIMIT n has no index that matches it, and pg_stat_statements put it at a 1.19s mean over 75,199 calls. A partial index - ON messages (ts DESC) WHERE embedding IS NULL - makes that query proportional to what is left rather than to the table, and empties itself when the queue does.

The silent worker

The backfill worker was started from a framework startup hook. When an MCP surface was added later, the app set a custom lifespan context - and a custom lifespan makes the framework skip the startup hooks entirely. No error, no log line, no failed health check: the process was serving search requests while the thread that was supposed to be embedding had never been created. It sat at 2.6% embedded for weeks.

What found it was py-spy dump from a sidecar container sharing the PID namespace, which listed two Python threads where there should have been three. What prevents the next one is a heartbeat the health endpoint reads:

WORKER_STATE = {"last_loop": 0.0, "rows_embedded": 0, "errors": 0}
# ... in the loop: WORKER_STATE["last_loop"] = time.time()

@app.get("/health")
def health(response: Response):
    age = time.time() - WORKER_STATE["last_loop"] if WORKER_STATE["last_loop"] else None
    ok = age is not None and age < 180
    if not ok:
        response.status_code = 503
    return {"ok": ok, "worker": {"last_loop_age_s": age}}

A container healthcheck against that endpoint turns “the worker is wedged” into unhealthy in docker ps, and the same counters go out as Prometheus metrics. The rule generalizes past this stack: any background loop inside a serving process needs a liveness signal that the serving half exposes, because “the port answers” is not evidence that the loop runs.

The loop that held a lock forever

The same worker had a second defect, and it took the database down rather than quietly doing nothing. Each pass claimed a batch per table with SELECT ... FOR UPDATE SKIP LOCKED, which opens a transaction and takes a row-share lock on the table. The early-exit path for “nothing to embed” returned without committing:

rows = cur.fetchall()
if not rows:
    conn.commit()   # <- this line. without it the transaction, and its
    continue        #    table lock, stay open across every idle sleep

While the backfill queue had work this was invisible, because the write path committed every pass. Once the queue drained - the steady state a backfill is trying to reach - the worker held that lock indefinitely. The next ALTER TABLE queued behind it, and a queued ACCESS EXCLUSIVE request blocks every later query on the table, so a routine deploy took the whole database out. Diagnosis is one query:

SELECT pid, state, now() - xact_start AS xact_age,
       pg_blocking_pids(pid), wait_event
  FROM pg_stat_activity WHERE datname = current_database();

which showed a 22-minute idle in transaction connection, the DDL waiting 21 minutes on it, and twenty-odd ordinary queries stacked behind the DDL.

Two settings look like they should prevent this. idle_in_transaction_session_timeout was set to five minutes throughout and never fired, because it measures continuous idle time since the last state change while the worker returns to active every sleep interval - the transaction is long, the idle spells are not. transaction_timeout would catch it and is the wrong instrument at server scope: pg_dump holds a long transaction by design, so the nightly backup becomes the casualty. Fix the loop, not the server.

Access model

Reads are open on the LAN and tailnet; writes always require a bearer token; from outside, both do. That asymmetry is deliberate - the ingester is the only writer and it holds the token, while every agent client on the network can search without one. It also means the retrieval tools in each client need no credential, which removes the whole class of “token in a config file, token in a process argv, token in a cache key” problems that a credentialed read path would have created. This store had exactly that problem via a protocol shim before native tools replaced it.

Client integration: HTTP first, protocol second

The store speaks plain HTTP, which every client can already do. A protocol adapter such as MCP is worth adding for clients that expect it,⁷ but it is a layer on the same endpoints, not a prerequisite - and it carries real cost: process spawn per session, a handshake before the first tool call, and one more place for a credential to live.

For a client with a native extension mechanism, wiring the same REST calls directly is fewer moving parts and a faster session start. The tool surface is small enough to be worth doing twice:

Tool	Backed by
Message search	PostgREST function over the FTS index
Semantic search	Embedding service, cosine similarity over HNSW
Ledger search	PostgREST function over summaries
Memory search	PostgREST table filter
Session list	PostgREST table filter, ordered by start time

Reading the numbers

The corpus is 642k messages and roughly 2.3GB in the messages table. That is small for Postgres and large for a laptop-class container. Once the embeddings landed the same table measured 4,395 MB of a 4,412 MB database, and 1,986 MB of that is the vector column plus its index - a vector store’s footprint tracks its index and dimensionality, not the text it was derived from. The constraining cost is the embedding backfill: one pass proportional to corpus size, then a trickle of a few hundred rows per sync. Sizing the compute for the steady state and temporarily scaling it up for the initial seed is the right shape - this deployment ran 6 workers on 8 cores for the seed and scales back to 1 worker on 2 cores afterwards.

Decision guide

Evidence

Claim	How it was checked	Status
Embedding throughput matrix	Timed row-count deltas over 90-180s windows per configuration, one change at a time	Measured
Intra-op thread scaling inverts past 4	8-CPU single-worker run measured slower than the 4-CPU one (106 vs 128 rows/min)	Measured
Backfill select at 1.19s mean	`pg_stat_statements` after 75,199 calls	Measured
Rate is content-dependent	Same configuration measured at 589 and 938 rows/min in different stretches of the same corpus	Measured
Silent worker, wrong thread count	`py-spy dump` from a sidecar in the same PID namespace showed 2 threads, not 3	Measured
Heterogeneous batch rejected by PostgREST	`PGRST102` on a live sync until the client padded missing keys	Measured
Filtered-search latency table	`EXPLAIN (ANALYZE, BUFFERS)` per variant against the live table, one query vector, top-10	Measured
Index path loses no recall under a broad filter	Its top-10 diffed against the exact top-10 at `ef_search` 40 and 100; search endpoint output diffed before and after the change	Measured
HNSW index earns its 1,041 MB	Top-10 timed with the index and with index scans disabled, cold and warm	Measured
`ef_search` caps rows below `LIMIT`	`LIMIT 50` returned 40 rows at `ef_search` 40 and 50 rows at 100	Measured
Partial index fixes the drain-time select	Reasoned from the query plan shape; not built, because the queue drained first	Design only
Half-precision index is 597 MB against 1,043 MB	Built both, compared `pg_relation_size`; database went 4,415 MB -> 3,946 MB after dropping the full-precision one	Measured
Recall 183/200 full, 185/200 half	20 query vectors pinned by primary key, top-10 diffed against exact search (`enable_indexscan = off`)	Measured
Earlier “identical to exact” recall claim	Withdrawn - the query vector was drawn with `OFFSET` and no `ORDER BY`, so each run sampled a different row	Corrected
Parallel index build fails on 64 MB `/dev/shm`	Reproduced on the live build; succeeded serially with `max_parallel_maintenance_workers = 0`, then in parallel at 5m35s after raising `shm_size` to 1 GB	Measured
`/dev/shm` pages count against the container memory limit	Wrote 300 MB into `/dev/shm` and watched the container go from 86 MiB to 402.6 MiB, then back to 100 MiB on delete	Measured
Uncommitted worker transaction blocks all DDL	`pg_blocking_pids` traced a stalled `REINDEX` to a 22-minute `idle in transaction` connection with 20+ queries queued behind it; after committing on the empty path the worker’s connections idle with no open transaction	Measured
`idle_in_transaction_session_timeout` does not catch it	Setting was 5 min for the whole 22-minute transaction; it measures continuous idle, not transaction age	Measured
Vector search quality vs FTS	No side-by-side relevance evaluation was run	Not tested

References

PostgREST, “Tables and Views,” PostgREST Documentation. https://docs.postgrest.org/en/latest/references/api/tables_views.html ↩
PostgREST, “Errors,” PostgREST Documentation. https://docs.postgrest.org/en/latest/references/errors.html ↩
pgvector contributors, “pgvector,” GitHub. https://github.com/pgvector/pgvector ↩ ↩²
PostgreSQL, “Controlling text search,” PostgreSQL Documentation. https://www.postgresql.org/docs/current/textsearch-controls.html ↩
Qdrant, “FastEmbed,” FastEmbed Documentation. https://qdrant.github.io/fastembed/ ↩
PostgreSQL, “SELECT,” PostgreSQL Documentation. https://www.postgresql.org/docs/current/sql-select.html ↩
Model Context Protocol, “Tools,” MCP Documentation. https://modelcontextprotocol.io/docs/concepts/tools ↩