I’ve spent the last few years building AI backends that handle millions of JSON documents daily. Embeddings, agent decision logs, LLM outputs, prompt histories, structured state machines. All of it lives in PostgreSQL. And I’ve learned that treating JSON like a second-class citizen is a fast way to watch your queries crawl.
PostgreSQL’s JSONB type is genuinely powerful. But power without strategy becomes a performance liability. This article walks through the operators, indexing patterns, and tuning techniques I’ve used to keep JSON queries snappy in production AI systems.
Why JSONB, Not JSON
First, a foundation. PostgreSQL has two JSON types: JSON (text-based, slower) and JSONB (binary, faster). For any production AI work, use JSONB. It’s stored in a parsed binary format, which means queries run faster and you can index them efficiently. The trade-off is a tiny bit of storage overhead during insertion. Worth it.
-- Good: JSONB for production
CREATE TABLE agent_states (
id BIGSERIAL PRIMARY KEY,
agent_id UUID NOT NULL,
state JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Alternative: JSON for text-based storage
CREATE TABLE agent_states (
id BIGSERIAL PRIMARY KEY,
state JSON NOT NULL
);Core JSONB Operators and When to Use Them
PostgreSQL gives you several operators for querying JSONB. Knowing which one to reach for is the difference between a 10ms query and a 10-second query.
The ->> Operator (Text Extraction)
This extracts a value as text. Use it when you need to filter or compare JSON fields as strings or convert to other types.
-- Get the model name from an LLM response
SELECT state->>'model' AS model_name
FROM agent_states
WHERE agent_id = 'abc-123';
-- Filter by a nested string value
SELECT id, state
FROM agent_states
WHERE state->>'status' = 'completed';The -> Operator (JSON Extraction)
This returns the value as JSONB, not text. Chain it for nested access.
-- Get a nested object (returns JSONB)
SELECT state->'metadata' AS metadata
FROM agent_states;
-- Chain for deeper nesting
SELECT state->'metadata'->'tags' AS tags
FROM agent_states;The @> Operator (Contains)
This is the workhorse for AI workflows. It checks if the left JSONB contains the right JSONB structure. Indexable with GIN, which I’ll cover later.
-- Find all agent states with a specific decision
SELECT id, state
FROM agent_states
WHERE state @> '{"decision": "escalate"}'::jsonb;
-- Find embeddings tagged with a specific category
SELECT id, state
FROM embeddings
WHERE state @> '{"tags": ["production"]}'::jsonb;The @? Operator (Path Exists)
Checks if a path exists in the JSONB document, using JSONPath syntax. Useful for optional fields or conditional logic.
-- Find states that have a 'retry_count' field
SELECT id, state
FROM agent_states
WHERE state @? '$.retry_count';
-- Check nested existence
SELECT id, state
FROM agent_states
WHERE state @? '$.metadata.error_code';The jsonb_path_query Function
This is for more complex extractions. Use it when you need to pull multiple values or apply filters within the JSON structure itself.
-- Extract all tags from a nested array
SELECT id, jsonb_path_query(state, '$.tags[*]') AS tag
FROM embeddings;
-- Find embeddings where any score exceeds a threshold
SELECT id, state
FROM embeddings
WHERE jsonb_path_query(state, '$.scores[*]') @> '0.95'::jsonb;Indexing Strategies for Production
This is where most engineers stumble. You can’t just throw an index on a JSONB column and call it a day. The type of index matters, and so does what you’re actually querying.
GIN Indexes (General Inverted Index)
Use GIN for @>, @?, and most JSON containment queries. It’s the default and the most versatile.
-- Create a GIN index on the entire JSONB column
CREATE INDEX idx_agent_states_state_gin ON agent_states USING GIN (state);
-- For large JSONB documents, index specific paths
CREATE INDEX idx_agent_states_decision ON agent_states USING GIN ((state->'decision'));
-- Index with jsonb_path_ops for faster containment checks
CREATE INDEX idx_embeddings_tags ON embeddings USING GIN (state jsonb_path_ops);GIN indexes are large and slower to build, but they make queries fast. They’re ideal when you have frequent reads and can afford the write overhead.
GIST Indexes (Generalized Search Tree)
GIST is smaller and faster to build than GIN, but slower on queries. Use it when you have write-heavy workloads and can tolerate slightly slower reads.
-- Create a GIST index for containment queries
CREATE INDEX idx_agent_states_state_gist ON agent_states USING GIST (state);B-Tree Indexes on Extracted Values
If you’re filtering by the same JSON field repeatedly, extract it and index the result. This is faster than indexing the entire JSONB.
-- Add a generated column for the status
ALTER TABLE agent_states
ADD COLUMN status TEXT GENERATED ALWAYS AS (state->>'status') STORED;
-- Index the generated column with B-Tree
CREATE INDEX idx_agent_states_status ON agent_states (status);Practical Patterns for AI Applications
Storing Vector Embeddings
Embeddings are just arrays of floats. JSONB handles them fine, though for very large-scale similarity search you’d use pgvector. But for moderate scale and multi-modal embeddings with metadata, JSONB works.
-- Table for embeddings with metadata
CREATE TABLE embeddings (
id BIGSERIAL PRIMARY KEY,
document_id UUID NOT NULL,
data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- data structure:
-- {"embedding": [0.12, -0.45, 0.67, ...], "model": "text-embedding-3-small", "source": "doc"}
CREATE INDEX idx_embeddings_model ON embeddings USING GIN (data);
-- Query embeddings by model
SELECT id, data->'embedding' AS vec
FROM embeddings
WHERE data->>'model' = 'text-embedding-3-small'
LIMIT 1000;Storing Agent Decision Logs
Agent workflows generate a lot of state. JSONB is perfect for storing the entire decision tree, tool calls, and reasoning steps.
-- Table for agent execution logs
CREATE TABLE agent_logs (
id BIGSERIAL PRIMARY KEY,
agent_id UUID NOT NULL,
run_id UUID NOT NULL,
log JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- log structure:
-- {"step": 1, "action": "tool_call", "tool": "search", "input": {...}, "output": {...}, "reasoning": "..."}
CREATE INDEX idx_agent_logs_agent_id ON agent_logs (agent_id);
CREATE INDEX idx_agent_logs_action ON agent_logs USING GIN (log jsonb_path_ops);
-- Find all runs where a specific tool was called
SELECT run_id, log
FROM agent_logs
WHERE log @> '{"action": "tool_call", "tool": "search"}'::jsonb
ORDER BY created_at DESC;Storing Multi-Model LLM Responses
Different LLMs return different response structures. JSONB lets you store them all in one table without schema migration.
-- Table for LLM responses
CREATE TABLE llm_responses (
id BIGSERIAL PRIMARY KEY,
prompt_id UUID NOT NULL,
model TEXT NOT NULL,
response JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- responses vary by model:
-- OpenAI: {"model": "gpt-4", "choices": [{"message": {"content": "..."}}], "usage": {...}}
-- Anthropic: {"model": "claude-3", "content": [{"type": "text", "text": "..."}], "usage": {...}}
CREATE INDEX idx_llm_responses_model ON llm_responses (model);
CREATE INDEX idx_llm_responses_response ON llm_responses USING GIN (response);
-- Query responses by model and check for specific fields
SELECT id, response
FROM llm_responses
WHERE model = 'gpt-4'
AND response @? '$.choices[*].message.content'
LIMIT 100;Query Optimization Techniques
Use EXPLAIN ANALYZE
Before you assume a query is slow, profile it. EXPLAIN ANALYZE shows you exactly where the time is spent.
EXPLAIN ANALYZE
SELECT id, state
FROM agent_states
WHERE state @> '{"status": "pending"}'::jsonb
LIMIT 100;Look for sequential scans on large tables. If you see one and you have an index, the planner might not be using it. Check your index definition and query.
Avoid Casting in WHERE Clauses
This is a common mistake. If you extract a JSON field and cast it in the WHERE clause, the index won’t help.
-- Slow: casting in WHERE prevents index use
SELECT id, state
FROM agent_states
WHERE (state->>'retry_count')::INT > 5;
-- Better: use a generated column with B-Tree index
ALTER TABLE agent_states
ADD COLUMN retry_count INT GENERATED ALWAYS AS ((state->>'retry_count')::INT) STORED;
CREATE INDEX idx_agent_states_retry_count ON agent_states (retry_count);
SELECT id, state
FROM agent_states
WHERE retry_count > 5;Combine Indexes with Partial Indexes
If you always filter by a specific condition (like status = ‘active’), create a partial index to reduce size and improve performance.
-- Partial GIN index for active agents only
CREATE INDEX idx_agent_states_active ON agent_states USING GIN (state)
WHERE state->>'status' = 'active';Monitor Query Performance Over Time
Set up pg_stat_statements to track slow queries in production.
-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Find the slowest JSONB queries
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE query LIKE '%@>%' OR query LIKE '%jsonb%'
ORDER BY mean_exec_time DESC
LIMIT 10;Common Anti-Patterns
Here’s what I’ve seen slow down production systems:
- Indexing the entire JSONB column when you only query specific paths. Use targeted indexes instead.
- Using JSON instead of JSONB. JSONB is the clear choice for all production scenarios.
- Storing deeply nested structures (5+ levels) and querying at the bottom. Flatten where possible.
- Querying without indexes and relying on sequential scans. Profile first, then index.
- Creating too many indexes on the same column. GIN indexes are expensive. One per table is usually enough.
- Ignoring ANALYZE after bulk inserts. The planner needs stats to choose the right plan.
Benchmarking in Your Environment
Every system is different. Before deploying, benchmark your specific queries and data patterns.
-- Create test data
INSERT INTO agent_states (agent_id, state)
SELECT
gen_random_uuid(),
jsonb_build_object(
'status', (ARRAY['pending', 'running', 'completed'])[floor(random() * 3) + 1],
'decision', (ARRAY['escalate', 'retry', 'resolve'])[floor(random() * 3) + 1],
'retry_count', floor(random() * 10),
'metadata', jsonb_build_object('tags', ARRAY['prod', 'test'])
)
FROM generate_series(1, 1000000);
ANALYZE agent_states;
-- Benchmark without index
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM agent_states
WHERE state @> '{"status": "completed"}'::jsonb;
-- Create index
CREATE INDEX idx_agent_states_state_gin ON agent_states USING GIN (state);
-- Benchmark with index
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM agent_states
WHERE state @> '{"status": "completed"}'::jsonb;You should see a dramatic difference in the plan. Without the index, you’ll see “Aggregate” and “Seq Scan”. With the index, you’ll see “Aggregate” and “Bitmap Index Scan” or “Index Scan”.
When to Step Beyond JSONB
JSONB is powerful, but it’s not the answer to every problem. If you’re doing large-scale vector similarity search, use pgvector. If you’re storing massive semi-structured datasets and need complex analytics, consider a data lake. But for most AI workflows where you’re storing state, logs, and structured outputs alongside relational data, JSONB in PostgreSQL is the right choice.
It keeps your schema simple, your queries flexible, and your operational complexity low. I’ve built systems handling millions of JSON documents per day this way, and it scales cleanly.
Start with JSONB and GIN indexes. Profile with EXPLAIN ANALYZE. Extract hot paths to generated columns if needed. Monitor with pg_stat_statements. That foundation will carry you through most production scenarios.
Should I use JSON or JSONB?
Always use JSONB in production. It’s stored in a parsed binary format, making queries faster and indexing possible. JSON is text-based and slower. JSONB is the clear choice for production AI workflows.
What index should I use for JSONB queries?
GIN indexes are the default and most versatile for @>, @?, and containment queries. They’re large and slower to build but make queries fast. GIST indexes are smaller and faster to build but slower on queries. Use GIN for read-heavy workloads, GIST for write-heavy workloads.
Why is my JSONB query slow even with an index?
Check with EXPLAIN ANALYZE. Common causes are casting in WHERE clauses (which prevents index use), querying without an index on the specific path, or the planner not having up-to-date statistics. Run ANALYZE after bulk inserts to update stats.
Can I use JSONB for vector embeddings at scale?
JSONB works fine for moderate-scale embeddings with metadata. For large-scale vector similarity search (millions of queries), use pgvector instead. For multi-modal embeddings with rich metadata, JSONB is often the better choice.
How do I monitor JSONB query performance in production?
Enable pg_stat_statements and query it regularly. Look for queries with high mean_exec_time or max_exec_time. Use EXPLAIN ANALYZE to profile specific queries. Set up alerts on slow queries to catch performance regressions early.
