AI
LLM interaction logs are a natural JSONB use case because different request types genuinely have different shapes — a simple completion, a tool-calling turn, and a multi-step agent trace don't fit the same rigid table structure. But "the payload varies" doesn't mean "give up on schema entirely" — the fields you'll actually filter, group by, and index on should still be real columns, with JSONB reserved for the part that's genuinely variable.
Structuring the hybrid schema
1. Put frequently-queried fields in real columns. Timestamp, model name, user/session ID, token counts, and cost are things you'll filter and aggregate on constantly — indexed native columns handle this far better than reaching into a JSON blob for every query.
2. Put the variable request/response payload in one JSONB column. The actual prompt, response text, and any tool-call arguments differ in shape between a plain completion and a multi-tool agent turn — JSONB accommodates that without a table-per-request-type explosion.
3. Add a GIN index on the JSONB column if you need to query inside it. If you occasionally need to search within the payload (e.g., find all logs where a specific tool was called), a GIN index makes that practical instead of a full-table scan.
CREATE TABLE llm_logs (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
model TEXT NOT NULL,
session_id TEXT,
input_tokens INTEGER,
output_tokens INTEGER,
cost_usd NUMERIC(10, 6),
-- Variable-shape payload: prompt, response, tool calls
payload JSONB NOT NULL
);
CREATE INDEX idx_llm_logs_model_time ON llm_logs (model, created_at);
CREATE INDEX idx_llm_logs_payload_gin ON llm_logs USING GIN (payload);| Approach | Query Performance | Flexibility |
|---|---|---|
| All fields in JSONB, no real columns | Poor for common filters/aggregations without expression indexes | Maximum, but hard to query efficiently |
| Rigid columns for everything | Fast, but breaks or needs migration when a new request shape appears | None — every new field needs a schema change |
| Hybrid: real columns + one JSONB payload column | Fast for common queries, GIN-indexable for payload search | High — payload shape can vary freely per request type |
The hybrid pattern isn't a compromise so much as matching each field to what it actually needs: structure and speed for the handful of fields every query touches, and flexibility for the payload that genuinely differs by request type. Revisit which fields belong in real columns as your query patterns solidify — a field that starts inside JSONB can graduate to its own column once you know you'll filter on it often.
Practical Challenge
Create the table above, insert a few rows with different payload shapes (a plain completion vs. one with tool calls), then write a query using the GIN index to find all logs where the payload contains a specific tool name.
Concept Check
Sources & Further Reading
- PostgreSQL Documentation: JSON Types — the official reference on JSONB, including the recommendation to keep even flexible documents somewhat structured.
- AWS Database Blog: PostgreSQL as a JSON database — advanced patterns including GIN indexing and hybrid schema design.