PostgreSQL JSONB for Agent Data: Flexible Storage for Heterogeneous Agent Outputs

Why JSONB Is Essential for Agent Systems

AI agent systems produce heterogeneous data. A weather tool returns temperature and humidity. A search tool returns ranked results with snippets. A database tool returns rows with different column sets. Trying to force all of these into rigid relational columns creates an explosion of nullable columns or an unmaintainable table-per-tool design.

PostgreSQL JSONB solves this. It stores JSON as a decomposed binary format that supports indexing, partial updates, and rich query operators. You get the flexibility of a document store with the transactional guarantees and query power of PostgreSQL.

Storing Agent Outputs in JSONB

Here is a practical schema for an agent system that stores tool results in JSONB:

CREATE TABLE tool_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES conversations(id),
    tool_name TEXT NOT NULL,
    input_args JSONB NOT NULL DEFAULT '{}',
    output_data JSONB,
    error_detail JSONB,
    execution_ms INTEGER,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Each tool writes its own structure into output_data. A weather tool might store {"temp_f": 72, "humidity": 45, "condition": "sunny"} while a search tool stores {"results": [{"title": "...", "url": "...", "snippet": "..."}], "total_hits": 1250}.

Querying JSONB Data

PostgreSQL provides operators for navigating JSONB structures. The most important ones for agent data:

-- Extract a text value from a JSONB column
SELECT output_data->>'condition' AS weather_condition
FROM tool_executions
WHERE tool_name = 'get_weather';

-- Filter by a nested JSONB value
SELECT *
FROM tool_executions
WHERE tool_name = 'search'
  AND (output_data->>'total_hits')::int > 100;

-- Check if a key exists
SELECT *
FROM tool_executions
WHERE output_data ? 'error_code';

-- Query nested arrays using jsonb_array_elements
SELECT
    te.id,
    result->>'title' AS result_title,
    result->>'url' AS result_url
FROM tool_executions te,
     jsonb_array_elements(te.output_data->'results') AS result
WHERE te.tool_name = 'search';

The ->> operator returns text, while -> returns JSONB. This distinction matters when comparing values or casting types.

Indexing JSONB for Performance

Without indexes, every JSONB query requires a full table scan. PostgreSQL offers two JSONB index types:

GIN Index — supports containment and existence operators:

-- General-purpose GIN index on the entire JSONB column
CREATE INDEX idx_tool_exec_output_gin
    ON tool_executions USING gin(output_data);

-- Now these queries use the index:
SELECT * FROM tool_executions
WHERE output_data @> '{"condition": "sunny"}';

SELECT * FROM tool_executions
WHERE output_data ? 'error_code';

Expression Index — for frequently queried specific keys:

-- Index a specific extracted value
CREATE INDEX idx_tool_exec_total_hits
    ON tool_executions ((output_data->>'total_hits'));

-- This query now uses a B-tree scan:
SELECT * FROM tool_executions
WHERE output_data->>'total_hits' = '1250';

Use GIN indexes when you query many different keys. Use expression indexes when you repeatedly filter on the same key. For large tables, the jsonb_path_ops GIN operator class is smaller and faster for containment queries:

CREATE INDEX idx_tool_exec_output_pathops
    ON tool_executions USING gin(output_data jsonb_path_ops);

Partial Updates with jsonb_set

Updating a single key inside a JSONB column without rewriting the entire document:

import asyncpg

async def mark_output_reviewed(pool, execution_id: str):
    await pool.execute(
        """
        UPDATE tool_executions
        SET output_data = jsonb_set(
            output_data,
            '{reviewed}',
            'true'::jsonb
        )
        WHERE id = $1
        """,
        execution_id,
    )

The jsonb_set function takes the column, a path array, and the new value. It returns a new JSONB document with just that key changed. For deeply nested updates:

UPDATE tool_executions
SET output_data = jsonb_set(
    output_data,
    '{results,0,processed}',
    'true'::jsonb
)
WHERE id = $1;

When Not to Use JSONB

JSONB is not a replacement for proper relational columns. If you filter, sort, or join on a value in every query, it belongs in its own column. A good rule: start with JSONB for new, evolving data structures. Once a field stabilizes and appears in WHERE clauses frequently, promote it to a dedicated column.

Also avoid storing large arrays (thousands of elements) in a single JSONB cell. PostgreSQL rewrites the entire JSONB value on any update, so large documents cause write amplification.

FAQ

What is the difference between JSON and JSONB in PostgreSQL?

JSON stores the raw text exactly as inserted, preserving whitespace and duplicate keys. JSONB parses the input into a binary format, removing duplicates and whitespace. JSONB is almost always the correct choice because it supports indexing, is faster to query, and uses less storage after the initial parse cost.

How do I validate the structure of JSONB data?

PostgreSQL does not enforce JSONB schemas natively. Use CHECK constraints for simple validation: CHECK (output_data ? 'status') ensures a key exists. For complex validation, enforce structure at the application layer with Pydantic models or Zod schemas before inserting, and use database triggers for critical invariants.

Does JSONB work well with ORMs like SQLAlchemy or Prisma?

Yes. SQLAlchemy maps JSONB to Python dictionaries natively via sqlalchemy.dialects.postgresql.JSONB. Prisma supports JSONB through its Json type, allowing you to read and write JSON objects directly. Both ORMs generate correct queries for JSONB operators.

---

#PostgreSQL #JSONB #Database #AIAgents #DataStorage #AgenticAI #LearnAI #AIEngineering