Persistent memory architecture in agents using DBMS

A language model has no memory. Each request is answered from the tokens in front of it, and when the context window closes, everything in it is gone. An agent — a model wrapped in a loop that runs over hours or months — has to supply that memory from outside. It writes down what happened, and later it reads some of it back.

Stated that way, the problem is immediately familiar. Records are inserted. They are indexed so they can be found again. A query selects a subset under a budget. Concurrent writers must not overwrite each other. A crash must not leave the store asserting something that was never true. This is the problem a database management system exists to solve, and it has been studied for fifty years[1][3].

Agent frameworks have largely arrived at a different answer. Memory is usually a file of JSON, or a vector index holding one embedding per remembered utterance, retrieved by cosine similarity. Neither has a schema, a query language, a transaction, or a recovery protocol. The question this paper asks is direct: what does an agent lose by storing its memory outside a DBMS, and which of the DBMS's properties actually matter?

We answer it by measurement rather than argument. We build one corpus of agent memories with known ground truth, implement 10 storage architectures over it, and score them on the same 313 queries — then subject the same three families to a crash, to concurrent writers, and to a fact that changes after it has been remembered 3.69 times.

The word covers three different things, and conflating them is the first source of confusion. The division follows Tulving's[12] and is now standard in agent architectures[13]:

• episodic — Something that happened, tied to a time and a session.
• semantic — A standing fact about the world, time-independent.
• procedural — An ordered way of doing something.

In the relational model these are not three stores. They are one relation with a discriminator: a disjoint, total specialization on kind. That is a design decision with consequences we return to in §4, and it is the first place where having a data model at all changes what the system can do — a query can ask for procedural memory only, and the engine can use an index to answer it.

What an agent actually writes is narrower than “everything it saw”. In our corpus a memory is a short natural-language restatement of one fact, produced during one turn of one session. It is roughly 25.4 tokens. It carries a provenance (shell_output and similar), a confidence, a position in time, and — critically — it may later be contradicted by something the agent learns afterwards.

Two architectures dominate, and we implement both rather than describe them.

The file store. Memories are appended to a JSON or JSONL file and recall reads the whole thing, scoring by keyword overlap. It has no index, so recall is linear in the corpus; it has no transaction, so a crash mid-write is whatever the filesystem left behind; and it has no concurrency control, so two writers race. We implement the careful variant (append-only) and, for the durability experiment, the common one (rewrite the whole document on every change).

The vector index. Each memory is embedded once and recall is a top-k nearest-neighbour search in that space[9]. This is the architecture most often described as “giving the agent memory”, and it is genuinely good at one thing: finding a memory that means the same as the query while sharing none of its words. We implement it twice — once with no filtering at all, and once with metadata post-filtering, because every production vector store offers the latter and comparing against the former alone would be a straw man.

Against these we put the same logical schema in two database engines: SQLite 3.53.0 in process[7], and PostgreSQL 18.3[6] with the pgvector[10] extension. Each family is measured with progressively more indexing, so the paper can attribute a result to an access method rather than to a product.

Table 2 is the paper's argument in one grid, and everything after it is the measurement of what those columns are worth.

Before anything can be measured, the memory has to have a shape. We give it one using the entity-relationship model[2], then normalise it.

4.1 Entities and relationships

Seven entities. The one that matters is MEMORY, and it is a weak entity: a memory has no identity apart from the session that produced it, so its natural key is (session_id, turn_no) with turn_no as the partial key. This is not a modelling nicety. A vector store treats each memory as a free-floating document with a global identity, and every question of the form “what did I learn in that session” becomes unanswerable as a direct consequence.

Three features of the diagram carry weight later:

The identifying relationship records is drawn double because SESSION supplies part of MEMORY's key. Participation is total on both ends: a memory without a session cannot exist.

The recursive relationship supersedes runs from MEMORY to itself. It records that a later memory corrects an earlier one, and it is the structure that distinguishes what the agent currently believes from what it has ever written down. No similarity function can recover it, because a superseded memory matches a query at least as well as its replacement — usually better, since the replacement carries the word “correction”.

The specialization on kind is disjoint and total: every memory is exactly one of episodic, semantic or procedural.

4.2 Normalisation

The file baselines store a self-contained record per memory: session metadata, agent model, entity list and the restated fact all inline. That is the unnormalised form, and walking it up to BCNF names exactly what each file architecture is giving up.

The functional dependencies that survive in the final schema are the ones a key determines and nothing else:

5.1 Ground truth that is not circular

A retrieval benchmark is only as good as its labels, and labels chosen by looking at results are worthless. We invert the usual order. First we build a world of entities — services, incidents, configuration records, decisions, people. From it we derive 2,500 facts, each a subject-predicate-object triple with a validity interval. Each fact is then rendered into one or more memories: short natural-language restatements an agent would plausibly have written, in different surface forms, scattered across 333 sessions.

Relevance is therefore definitional rather than judged: the memories relevant to a query about a fact are exactly the memories rendered from that fact, intersected with the query's structural predicate. Nothing is scored by eye. The generator is seeded, so the corpus reproduces byte for byte.

Two deliberate contaminants make the benchmark hard. Distractors (1,218 memories, 15% of the corpus) mention a record's identifier while asserting nothing about it — the agent-memory equivalent of “checked X, unrelated”. And supersessions (844 memories) revise a fact after it was first recorded, so the best-matching text and the currently true statement are different rows.

5.2 Query classes

The 313 queries are partitioned into 8 classes chosen to stress different machinery. The column that matters is the last one: whether the query can be answered by a top-k similarity search alone, with no predicate, no join and no aggregate.

5.3 Validating the corpus before using it

A benchmark that claims “lexical queries share identifiers and semantic ones do not” should demonstrate it rather than assert it, because if the property fails, every downstream comparison measures nothing. Table 5 reports what the corpus actually has.

The design holds where it needs to. Lexical queries share the target's identifier but sit at cosine 0.277, barely above the 0.2655 random-pair baseline — they name the record without describing it. Semantic queries invert exactly that: cosine 0.738 with an identifier overlap of 0.00. A retriever that wins one and loses the other is telling us about its access method, which is the point.

5.4 Instrumentation

One harness, tools/capture.mjs, drives every measurement into a single JSON document. Engines return an ordered list of memory ids and nothing else; precision, recall, MRR and nDCG are computed centrally, so no engine can flatter itself. Latency is the median of three timed runs after a warm-up. Embeddings are computed once and shared, so a retrieval difference can never be an embedding difference. Storage is read from the engines' own accounting — dbstat for SQLite, pg_class for Postgres — not from file sizes.

Both engines run without a server: SQLite through Node's built-in node:sqlite, and PostgreSQL through PGlite[11], a genuine Postgres 18.3 build compiled to WebAssembly. The whole study therefore reproduces from npm install, at the cost discussed in §10.

Aggregated over all 313 queries at k=10, the ordering is already informative — and not the ordering the current literature would predict.

Aggregate scores hide the finding. Broken down by query class, the arms do not merely differ in degree — the ordering inverts, and each architecture has classes on which it is essentially unable to answer.

Three things happen in that grid.

Pure similarity collapses on structural questions. Averaged over the 5 classes requiring a predicate, join or aggregate, Vector reaches 0.057 nDCG. Its provenance score — “everything I recorded during session S” — is 0.000, because the question has no similarity content whatsoever. The answer is defined by a foreign key.

The same index inside a DBMS recovers most of it. PG HNSW uses the identical embeddings and an HNSW graph, and averages 0.446 on those classes — reaching 1.000 on provenance, because the planner applies the predicate and the vector index is simply not consulted. What changed is not the retrieval; it is that a query language existed to state the constraint.

Adding a vector index can make things worse. On currency — “what is the current position on X” — the B-tree arms score 1.000, because superseded_by IS NULL is exactly the right answer. The hybrid arms score 0.621: rank fusion re-ranks away from a filter that was already correct. Hybrid retrieval is not uniformly better, and treating it as a default costs accuracy on precisely the questions a predicate settles.

The aggregate class makes the point without any ranking at all. Asked how many times a fact was recorded, 7 of the 10 arms return the exact cardinality — every arm with a SELECT COUNT(*) … GROUP BY. The file arms return none, and cannot: a top-k list truncated at 10 is the wrong shape of answer to a counting question.

The lexical column deserves its own explanation, because a score of 0.002 invites the reader to assume a bug. It is not a bug, and the mechanism is worth seeing.

In 100% of probed queries, the memory the embedding ranked first was a distractor — a record that mentions the identifier while explicitly disclaiming it. The memory that actually answered the question sat at a median rank of 271 out of 8,282.

The reason is structural. “What do we know about ADR-297?” is a question about a lookup, and the distractor is a sentence about a lookup. They share their grammatical shape, which is most of what survives mean-pooling into 384 dimensions. The identifier itself is a handful of subword pieces averaged in with everything else, and it carries almost no weight. An inverted index has the opposite bias: a rare term is the most informative thing in the query, which is why the same corpus yields 0.790 nDCG for BM25.

The obvious objection to §6.2 is that production vector stores support metadata filters. They do — but as a post-filter over an already-ranked candidate list, and that is a different operation from a predicate the planner may apply first.

With no overfetch, asking for 10 results returns 4.89 on average: 48.9% of the requested slots are filled, because the filter can only remove candidates, never introduce them. Raising the overfetch factor helps, but slowly and at a price — at ×128 the search touches 15.5% of the corpus to reach recall 0.225, which is to say it is becoming the sequential scan the index was adopted to avoid.

The claim that a DBMS “uses an index” is checkable. Both engines report their plans, and the harness captures one per query class per engine. Two are worth reading side by side.

The corpus is 784.4 KB of text. What the architectures cost to store it varies by more than an order of magnitude, and the reason is entirely the embeddings.

SQLite · FTS5 inverted index holds the corpus in 497.5 bytes per memory; Postgres · GIN + HNSW hybrid needs 4177.1, a factor of 8.4. For an agent whose memory grows monotonically, that ratio is the difference between a store that fits on the machine and one that does not — and §6.2 shows the vectors buying quality on one class out of 8.

Retrieval quality is the interesting half of the problem; not losing the memory is the necessary half. We killed each writer with an uncatchable signal partway through sustained writes, then reopened the store and asked what survived. The clock starts only after the store holds a durable baseline, so this measures a crash during writing rather than during initialisation.

The honest result is that append-only files are fine. Across 20 kills the JSONL store never lost a parseable line: appending is close to atomic at these sizes, and the failure mode is a missing tail rather than a corrupt file.

The common file pattern is not fine. Holding memory as one JSON document and rewriting it on every change means the file is briefly neither the old state nor the new one — and in 3 of 20 trials the crash landed inside that window and left a document that no longer parses. The loss is not the last record. It is all 17,043 of them, because the store is a single value.

Both DBMS arms recovered to a committed boundary in every trial, with SQLite's integrity_check returning ok and Postgres replaying its write-ahead log[4] on open. This is not a surprising result; it is a fifty-year-old result[5]. It is included because the architecture that gets it wrong is the one currently in widest use.

Agents increasingly run as fleets sharing one memory. We ran 8 concurrent writers performing 25 increments each on the same record, in-process for every family so the result isolates the concurrency-control mechanism rather than the deployment model.

The file store lost 175 of 200 updates — 87.5%. It is the textbook lost-update anomaly[3], and it arrives here for the textbook reason: read the document, modify a field, write the document back, and whoever writes last erases everyone else. Both DBMS arms lost nothing, expressing the same edit as a single statement whose atomicity the engine guarantees.

This is where the database-theory chapter stops being theoretical. In our corpus a fact is restated across 3.69 memories on average and up to 15. When the fact changes, a normalised schema updates one row in FACT. A denormalised store must find and rewrite every restatement.

An agent does not rewrite every restatement. It rewrites what it retrieved — at most 10 rows. Across 120 probed facts a top-10 repair reached 1.53 of 4.2 restatements, leaving 63.5% still asserting the old value.

Those rows do not sit inertly on disk. They match the same queries they always did, so the agent retrieves them, reads them as confident statements of fact, and acts on them. An update anomaly in an agent's memory does not present as a data-quality metric. It presents as an agent that contradicts itself.

Retrieval is not free at the point of use. Everything returned is pasted into a context window and paid for per token, so the right question is not “which arm scores highest” but “which arm scores highest per token spent”.

Every architecture was rebuilt and re-measured at 609, 1,995, 8,282, 33,197 memories. The unindexed arms degrade linearly, as they must; the indexed arms do not.

The ingest side carries the opposite lesson. Building an inverted index is cheap; building an HNSW graph is not, and neither is writing 384-dimensional vectors through a query protocol. That cost is paid once per memory rather than once per recall, which is the right trade for a store written far less often than it is read — but it is not free, and for a memory that is written on every turn it is the dominant term.

One result deserves separating from the measurements, because it is about what the architecture makes expressible rather than what it makes fast.

Hybrid retrieval is normally application code: run the keyword search, run the vector search, merge the two ranked lists, apply the filters, return the top k. In the Postgres arm none of that code exists. The entire strategy — both indexes, the structural predicate, reciprocal rank fusion[8] and the truncation — is one statement the planner optimises as a unit.

This matters for a reason beyond elegance. Retrieval strategy is the part of an agent that changes most often — a new filter, a different weighting, a recency term. Expressed as a query it is data the engine re-plans against current statistics. Expressed as application code it is a merge loop that has to be rewritten, re-tested, and kept consistent with whatever the store is doing.

9.1 A vector index is not a memory system

The strongest reading of our results is not that vector search is bad. On paraphrase it is the best tool available, and it is the only arm that finds a memory sharing no words with the query. The error is one of scope: a vector index is one access method, and agent memory needs several.

61.7% of our workload is decided by a predicate, a join or an aggregate. Those are not exotic queries — they are “what did we decide before the migration”, “what came out of that session”, “what is still true”, “how many times did this come up”. A system whose only operation is top-k similarity cannot express them, and the failure is silent: it returns ten plausible memories and no indication that the question was not the one it answered.

9.2 What a DBMS actually contributes

Our results separate into two kinds, and they are worth keeping apart.

The retrieval results are contingent. They depend on our corpus, our embedding model and our query mix; a different domain would move them. A reader is entitled to discount them.

The integrity results are not contingent in the same way. A store with no transaction will lose concurrent updates; a store that rewrites a single document has no commit boundary to recover to; a store that repeats a fact in 3.69 places cannot correct it atomically. These follow from the architecture, not from the workload. They are also the results that current practice most consistently ignores.

9.3 The recommendation is smaller than “use Postgres”

SQLite · FTS5 + vectors scored highest overall at 0.726. We would not recommend it as a default. SQLite · FTS5 inverted index reached 0.723 — 0.5% lower — at 0.44 ms median latency, 8.4× less storage, no embedding model and no server. For a single agent on one machine that is the better engineering.

The case for the server arm is the case for concurrency, for a real planner, and for expressing retrieval as a query rather than as code. The case for the vectors is narrower still: they earn their 8.4× storage on paraphrase recall and, on our corpus, nowhere else.

The general lesson is the one the normalisation ladder in §4.2 already states. Agent memory has a schema whether or not anyone writes it down. Writing it down is what makes the questions answerable.

The corpus is synthetic. This is the most important caveat. Real agent transcripts are private, and non-circular relevance labels require knowing which fact each memory restates — which means generating the memories from the facts. We accept the trade and mitigate it by validating the corpus before using it (§5.3, Table 5) rather than assuming its properties. Absolute scores should not be read as predictions for any deployed system; the comparison between arms on identical data is what we claim.

The distractors are adversarial by construction. 15% of the corpus mentions an identifier while asserting nothing about it, and those records share the grammatical shape of a lookup question. That design is why dense retrieval scores as low as it does on the lexical class. We think the pattern is realistic — agents write “checked X, unrelated” constantly — but a corpus without it would narrow the gap, and the 0.002 figure should be read as the behaviour under adversarial-but-plausible distractors rather than a universal constant.

One embedding model, one dimensionality. All similarity results use Xenova/all-MiniLM-L6-v2 at 384 dimensions. A larger model, or one trained with identifier-aware objectives, would score better on the lexical class. The structural classes would not move at all, because their failure is representational rather than a matter of embedding quality.

PGlite is Postgres in WebAssembly. The SQL semantics, planner, MVCC and WAL are genuine PostgreSQL 18.3, which is what our correctness claims rest on. The absolute latencies are not those of a native server: WASM is slower and single-threaded, so the Postgres arms are penalised on timing relative to a real deployment. Where we compare engines on latency we say so; the structural results do not depend on it.

Concurrency was measured in-process. Running the file store across processes and PGlite in one would have confounded concurrency control with the deployment model. The lost-update result therefore isolates the mechanism, and says nothing about throughput under real multi-process contention.

Recency happens to substitute for currency. The file arm scores well on the currency class, but not because it models supersession — it breaks ties by recency, and in our corpus the correction is always the newest memory. That coincidence is a property of the generator. It would fail the moment a superseded memory were touched again, and the arm has no way to express the constraint that would make it robust.

Single machine, single run. All measurements come from one 16-core machine on win32/x64. Latency medians are over three timed runs per query; they are not a substitute for a benchmarking harness with isolation and repetition across machines.

The database side of this paper is textbook and deliberately so. The relational model[1], the entity-relationship model[2], normalisation and the transaction[3] are settled results; our contribution is applying them to a workload that has grown up without them, and measuring what their absence costs. Recovery follows ARIES[5]; the ranking baseline is BM25[14]; fusion uses reciprocal rank fusion with the original constant rather than a tuned one[8], since a tuned constant would let the hybrid arm win by fitting our corpus.

On the agent side, retrieval-augmented generation[15] established the pattern of fetching text into a context window, and the systems that followed — MemGPT[16] with its paged memory hierarchy, and the generative-agent architecture[13] with its retrieval scored on recency, importance and relevance — both treat memory as a storage problem. Neither is evaluated as a database: we are not aware of prior work measuring agent memory for durability under crash, for lost updates under concurrent writers, or for the update anomaly that follows from storing an unnormalised fact.

Approximate nearest-neighbour search over HNSW[9] and its integration into a relational engine through pgvector[10] are what make the hybrid arm possible at all; the observation that filtered vector search interacts badly with post-filtering is known in that literature, and §6.4 quantifies it for this workload.

An agent's memory is a database, and building it without one costs more than performance. Across 10 architectures on 8,282 memories and 313 labelled queries we find that 61.7% of a realistic recall workload cannot be expressed as similarity search at all; that dense retrieval is near-random on the identifier queries agents ask most (0.002 against 0.790 nDCG for an inverted index); that eight concurrent writers cost a file store 87.5% of its updates while costing both DBMS arms nothing; and that a fact restated 3.69 times cannot be corrected by retrieval, leaving 63.5% of its restatements contradicting the agent's current belief.

None of the database results are new. Codd[1], Chen[2] and Gray[3] settled them decades ago. What is new is the setting: a class of system that writes records, indexes them, queries them under a budget and must survive a crash — and that has largely been built as though none of that work had happened. The useful contribution of this paper is not a new architecture. It is a measurement of how much the old one is still worth.

Everything regenerates from one command. No database server, no API key and no network access at measurement time — the embedding model and both engines are local.

npm run capture alone rebuilds the dataset every figure reads. It runs the 10 architectures over the main corpus, the post-filter sweep, the scaling sweep at 4 sizes, the crash trials, the lost-update test and the anomaly probe, and writes data/capture.json. Total runtime is dominated by the Postgres arms.

Generated from engines/schema.mjs — the same declaration the ER diagram in Figure 2 renders from, so the diagram and the executed schema cannot disagree.