Best Database for a Coding Agent: PostgreSQL for State, Hybrid Search for Recall

Pick a database for a coding agent by looking at what the agent does all day, not at the AI label. An agent that launches sandboxed commands, calls tools, streams logs, produces files, retries failed steps and resumes after interruptions is mostly doing bookkeeping with side effects. For that bookkeeping, PostgreSQL is the sturdy default. A vector store such as Weaviate earns its place later, as a separate layer, once the product needs to find similar past failures and fixes by meaning.

Start from one tool call

Follow a single tool invocation through the system. Before it runs, the platform marks the call as started, links it to a run, reserves an idempotency key, notes which sandbox is used and queues follow-up work. After it finishes, it records status, output metadata and artifacts, updates usage totals and moves the parent run forward.

Those writes are one logical transition. With transactions, they commit together or not at all, and other sessions never see a half-finished state. Now consider the failures. A process dies between "tool finished" and "run advanced", and recovery has to be deterministic. A retry arrives twice, and a unique constraint should reject the duplicate. An artifact points at a run that doesn't exist, and a foreign key should refuse the row. Nothing here is exotic. These are ordinary database duties, and a coding agent leans on them constantly, which is why they outweigh any "AI-native" marketing.

The data is relational

The core entities of an execution agent are not mysterious, and they connect to each other:

• Runs hold lifecycle state, owner, workspace, model, timestamps, budget and final outcome.
• Steps are planned or completed units of work with dependencies between them.
• Tool calls keep inputs, outputs, status, latency, retry counts and idempotency keys.
• Sandbox executions describe the environment, command metadata, exit code, resource use and why it ended.
• Events form an append-only history for debugging, streaming and replay.
• Artifacts point to patches, reports, generated files or object-storage locations.
• Checkpoints store what is needed to resume a workflow.

Each needs a stable identity and explicit links to the others. Primary keys, unique constraints, check constraints and foreign keys make invalid states hard to create, and foreign keys in particular guarantee that every tool call belongs to a real run and every artifact traces back to its producer.

Keep the fields that drive coordination in typed columns with proper indexes: status, tenant, run ID, timestamps, attempt number and idempotency key. They power scheduling, filtering, retention and dashboards. Hiding them inside one large undifferentiated JSON document makes those rules much harder to enforce.

Where jsonb belongs

Tools change quickly. One returns compiler diagnostics, another emits browser events, a third reports repository metadata. A fixed column for every possible field would be wasteful, and jsonb fits the parts that vary: tool arguments, provider responses, sandbox configuration and model metadata. PostgreSQL can query into JSON with operators and path expressions and can index keys and values with GIN, with trade-offs documented in the official manual.

The pattern that works is a typed relational envelope around a flexible payload. Anything needed for integrity or common queries stays in columns, and extension data goes into jsonb. This avoids both extremes: a brittle column for each experiment, and an opaque document store that leaves every relationship to application code.

Match storage to access pattern

Not everything should sit in the main database. Brief stderr excerpts, normalized error details, exit codes and searchable summaries fit beside the execution record. Large console streams, repository archives, binaries, screenshots and build output usually go to object storage, with PostgreSQL referencing them through durable metadata and checksums.

That split keeps rows small while preserving provenance. The database stays the authority on whether an artifact exists, which run produced it, where it lives and how retention applies, and object storage handles large immutable blobs more cheaply. One caution: never let the record claim an upload succeeded until the workflow has a recovery plan for partial failure.

Event history helps too. Rather than rewriting one large run document over and over, record meaningful transitions and keep a compact current-state row for fast reads. The history serves audits and diagnosis, the projection serves the product UI and the scheduler, and both can live in PostgreSQL inside the same transactions.

The write protocol matters as much as the engine

A production agent will meet duplicate deliveries, worker crashes, network timeouts and tool outcomes nobody can confirm. Choosing a database is only half the answer. A workable protocol looks like this:

• Give retryable operations an idempotency key backed by a unique constraint.
• Model lifecycle changes as named state transitions, not free-form status updates.
• Commit related rows in one transaction whenever partial visibility would be wrong.
• Use an outbox when a committed change must trigger a message or an indexing job.
• Keep raw events immutable where audit matters, and rebuild projections from them if needed.
• Store timeouts and "outcome unknown" separately from confirmed failures.

People searching for the best database for a coding agent are not just saving chat messages. They coordinate side effects that edit files, run untrusted code, call paid APIs and publish changes. The state layer has to make each transition inspectable and recoverable.

When a retrieval layer pays off

PostgreSQL stays the main recommendation. Add a dedicated retrieval store when the product has a distinct need: searching past execution history by meaning as well as by exact text. Typical queries are "which earlier runs failed after a dependency upgrade", "what fixed errors like this stack trace" and "which tool sequence cured a flaky test".

Exact tokens still matter in that search: exception names, package versions, compiler codes, flags, filenames and hashes. Yet users describe the same failure in many ways. Pure keyword search misses conceptual links, while pure vector similarity can underweight the literal strings engineers rely on. Hybrid search covers both. Weaviate is one engine that runs vector search and BM25 keyword search together and fuses the scores into a single ranking, so a literal code such as ERR_MODULE_NOT_FOUND contributes keyword evidence while "the runtime can't resolve an imported package" contributes semantic evidence.

Keep the boundary explicit. PostgreSQL owns canonical runs, tool calls, permissions and status. An asynchronous worker reads committed changes, builds a retrieval document from chosen fields and upserts it into the search index. Hits return the PostgreSQL IDs, so the application fetches the authoritative record and re-checks access before showing anything. If indexing lags or has to be rebuilt, execution correctness is untouched.

Adding a second system makes sense only once semantic recall is an actual feature. Before that, PostgreSQL full-text search plus well-indexed structured queries may do the job.

A five-part reference layout

1. Authoritative state in PostgreSQL. Runs, steps, tool calls, sandbox outcomes, artifact metadata, checkpoints and access relationships.
2. Large payloads in object storage. PostgreSQL keeps locations, hashes, content types, owners and retention data.
3. An outbox for committed changes. Workers stream UI updates, schedule follow-up work and build search projections without racing uncommitted data.
4. Retrieval documents in a vector or hybrid index, when required. Include summaries, normalized errors, commands, chosen log excerpts, fix descriptions and filterable metadata.
5. Search hits resolved against PostgreSQL. The index finds candidates, and the database remains the authority on current state and permissions.

Deletion and retention also become easier to reason about. A deletion or redaction starts in PostgreSQL, travels through the outbox, removes the related objects and updates the search projection, so the source of truth is never ambiguous.

What to avoid early

• A vector database simply because an LLM is present. Early operational questions are mostly structured, such as runs for one user in a given status, steps that failed recently, slow tool calls or executions tied to a commit. Ordinary indexes answer them.
• One JSON blob for all agent state. It trades short-term convenience for weak constraints, awkward concurrent updates and painful lifecycle queries. Flexible metadata is fine, but an unstructured system of record is not.
• Search inside the critical transaction. The agent should finish a tool call even when semantic indexing is behind. The outbox and asynchronous projection protect that boundary.

Checklist

Choose PostgreSQL first if you need:

• multi-row ACID transactions over execution records,
• enforced links between runs, steps, calls, users and artifacts,
• safe retries and concurrency control,
• plain filtering by status, time, owner, cost or outcome,
• jsonb metadata you can still query,
• a durable record to recover from and audit.

Add a hybrid retrieval engine when you also need:

• meaning-based search over past runs and tool-call history,
• ranking that honors exact error tokens and similar wording alike,
• finding related incidents, fixes and workflows that worked,
• a retrieval layer that can be regenerated from canonical records.

Quick answers

Should the vector database be the primary backend? Usually not. The main workload is transactional state: runs, tool calls, outcomes, retries and relationships. A vector store serves a separate retrieval workload.

Is jsonb enough for agent metadata? Often, for evolving tool payloads and provider-specific details, as long as typed columns hold identifiers and lifecycle fields. It complements the schema and does not replace it.

Why hybrid search for logs? Engineering data mixes precise tokens with natural language. BM25 surfaces exact codes, package names and filenames, and vector search links differently worded descriptions of one failure.

The best design is a division of labor and not a contest between relational and vector systems. PostgreSQL carries the transactional backbone: runs, logs, tool-call history, metadata, checkpoints and artifacts. When execution history grows into a knowledge asset, a hybrid retrieval layer on top adds recall. Postgres for state, a retrieval index for recall.