Postgres as Autonomous Memory: Crash Recovery for Vector Indexing

Every autonomous agent eventually hits the same wall: the model is fast, the orchestration is clever, but memory—the thing that makes it autonomous—is a fragile append-only log that dies the moment the process does. We've all been there: a nightly LoRA fine-tune job kicks off, embeddings start flowing, and then the box blinks. The index is half-built, the checkpoint is stale, and the agent wakes up with amnesia.

This is not a story about a specific incident. It's a pattern. Teams building agent swarms on top of vector databases routinely discover that the indexing pipeline is the least reliable component in the stack. And the fix isn't a better vector store—it's treating Postgres as the source of truth for memory, with a crash-recovery protocol that assumes failure is normal.

Why Postgres, Not a Vector Database

Vector databases are great at one thing: similarity search. They're terrible at being a system of record. They don't do transactions across metadata and vectors. They don't have crash recovery that plays well with external side effects. They're append-only logs with a search index bolted on.

Postgres, on the other hand, has been doing durability for decades. With the pgvector extension, you get ACID transactions, point-in-time recovery, and a mature ecosystem. The trick is to treat the vector index as a derived structure, not the source of truth. The source of truth is the document table, with its content, metadata, and processing state.

A common pattern in the RiNET stack—which runs three Hetzner servers, WireGuard mesh, Qwen on vLLM, BGE-M3 for embeddings, and Postgres+Qdrant+Neo4j—is to use Postgres for the operational log and Qdrant for the search index. But even then, Postgres is the one that holds the state machine. If Qdrant dies, you rebuild from Postgres. If Postgres dies, you've lost the plot.

The Crash-Prone Pipeline

Here's the typical flow for building a memory store:

1. Ingest: Raw text arrives from a source (logs, documents, chat history).
2. Chunk: Split into semantic pieces.
3. Embed: Run BGE-M3 or similar to get vectors.
4. Index: Write vectors to a vector store, and metadata to Postgres.
5. Link: Build graph edges in Neo4j for relationships.

Each step can fail. The embedding model might timeout. The vector store might reject a batch. The network partition might split your cluster. And if you're doing this in a single monolithic script, a crash mid-way leaves you with partial state: some chunks embedded, others not; some vectors in Qdrant, others missing; and no way to know what's complete.

The naive fix is to re-run the whole pipeline. But that's expensive—re-embedding millions of chunks costs compute and time. And it's dangerous: you might duplicate vectors, creating false similarities in the index.

Checkpointing with Postgres

The answer is to make every step idempotent and checkpointed in Postgres. You maintain a documents table and a chunks table, each with a status field. The pipeline processes chunks in batches, and after each batch, it updates the status in Postgres in the same transaction as any other state change.

CREATE TABLE documents (
    id UUID PRIMARY KEY,
    content TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending', -- pending, chunked, embedded, indexed
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
    id UUID PRIMARY KEY,
    doc_id UUID NOT NULL REFERENCES documents(id),
    content TEXT NOT NULL,
    embedding vector(1024),
    status TEXT NOT NULL DEFAULT 'pending', -- pending, embedded, indexed
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Now, the pipeline for each chunk is:

1. Read a batch of chunks with status pending.
2. For each chunk, call the embedding model.
3. Upsert the vector into Qdrant (or Postgres with pgvector).
4. Update the chunk's status to indexed in Postgres.

But here's the catch: if the process crashes after step 3 but before step 4, you'll have a vector in Qdrant that Postgres thinks is pending. On recovery, you'll re-embed and re-index it, creating a duplicate.

Idempotency Keys

The solution is to use an idempotency key. Each chunk has a unique key (its UUID), and the vector store should support upsert by that key. In Qdrant, you can use the point ID. In pgvector, you can use a unique constraint on the chunk ID.

# Pseudocode for idempotent indexing
for chunk in get_pending_chunks(batch_size=100):
    vector = embed(chunk.content)
    # Upsert with chunk.id as the point ID
    qdrant.upsert(collection="memory", points=[{
        "id": chunk.id,
        "vector": vector,
        "payload": {"doc_id": chunk.doc_id, "content": chunk.content}
    }])
    # Mark as indexed in Postgres
    update_chunk_status(chunk.id, "indexed")

If the crash happens after the upsert but before the status update, the recovery process will see the chunk as pending and re-run it. But since the upsert is idempotent (same point ID, same vector), it simply overwrites—no duplicate.

If the crash happens before the upsert, the chunk is still pending, and you re-run it. No harm done.

The key is that the status update in Postgres is the commit point. If the status is indexed, the vector is guaranteed to be in the store. If it's not, you can safely re-run.

Recovery Protocol

Now, the recovery protocol is straightforward:

1. On startup, scan for documents with status processing or chunks with status embedding.
2. Reset those statuses to pending (since the process died mid-flight, the state is indeterminate).
3. Re-run the pipeline from the beginning of the pending set.

-- Reset stale states after a crash
UPDATE chunks SET status = 'pending'
WHERE status IN ('embedding', 'indexing')
  AND updated_at < now() - interval '10 minutes';

But you need to be careful: if a chunk's status is indexed, but the vector store was rolled back (e.g., Qdrant lost data), you'll have a false positive. To handle that, you need a reconciliation step: periodically compare the set of indexed chunk IDs in Postgres with the set of point IDs in the vector store, and re-index any that are missing.

This is a classic outbox pattern. Postgres is the outbox, and the vector store is the downstream system. The outbox table records intended actions, and a dispatcher ensures they happen.

The Emotional Rollercoaster of Memory

Why does this matter for autonomous agents? Because memory is not just a lookup table. It's a narrative. An agent that forgets its past conversations is not autonomous—it's a goldfish. And if the indexing pipeline crashes, the agent doesn't just lose a few vectors; it loses the thread of its own history.

We've seen agents that, after a crash, start re-ingesting the same data, creating duplicate memories that confuse the retrieval. Or worse, they ingest partial memories, leading to hallucinations about what happened before.

The fix is to make memory durable and recoverable. Postgres gives you that, but only if you design the pipeline with crash recovery in mind.

Practical Implementation with RiNET Stack

In the RiNET stack, we run three Hetzner servers connected via WireGuard. Postgres runs on one node, Qdrant on another, and the agent orchestration (with Qwen served via vLLM) on the third. The nightly LoRA fine-tune job runs on the vLLM node, and it also triggers memory re-indexing.

Here's a typical flow:

1. The agent ingests new data (e.g., conversation logs) into Postgres.
2. A worker picks up new documents, chunks them, embeds with BGE-M3, and writes to Qdrant.
3. The worker updates chunk statuses in Postgres.
4. If the worker crashes (e.g., OOM, network partition), the recovery script resets stale statuses and re-runs.

We use a simple state machine with status fields and updated_at timestamps. The recovery script is a cron job that runs every 5 minutes, looking for chunks that have been processing for more than 10 minutes.

# recovery.sh
psql $DATABASE_URL -c "UPDATE chunks SET status='pending' WHERE status='processing' AND updated_at < now() - interval '10 minutes';"
# Then trigger the worker to pick up pending chunks

Lessons for Your Own Stack

You don't need to copy our exact setup. But the principles apply:

1. Postgres is the source of truth. Don't let the vector store become the system of record. It's a derived index.
2. Idempotency is non-negotiable. Every write to a downstream system must be safe to repeat.
3. Status fields are your friend. Track the state of each chunk, and reset stale states on recovery.
4. Reconcile periodically. Compare Postgres with the vector store to catch drift.

If you're building an autonomous agent, you're building a system that will crash. The question is not if, but when. And when it does, your memory should survive.

Postgres has been the backbone of data durability for decades. It's time to use it for what it's good at—being the reliable memory of your autonomous systems.

Further Reading

• pgvector documentation
• Outbox pattern
• Qdrant docs