liveBook · Manning

Jsonb

As explained in Designing AI Systems

JSONB is PostgreSQL's binary JSON data type for storing structured JSON values with database indexing and querying support.

JSONB is PostgreSQL’s binary JSON type for storing nested, variable-shaped data inside a relational schema. It fits message records whose tool calls contain IDs, function names, and arguments without requiring separate tables. section 4.4.1

Choose JSONB when the application must preserve structured JSON and may later search or filter its contents. Inserts are slightly slower than JSON, but queries are significantly faster, making JSONB suitable for tool-call data that may become operationally important. section 4.4.1

A production message schema uses JSONB alongside conventional relational columns:

  • Keep message identity and ordering in a SERIAL primary key rather than depending on timestamps. section 4.4.1
  • Store the nested tool_calls structure in JSONB when additional tables would add unnecessary complexity. section 4.4.1
  • Add indexes or JSONB queries later when operations require finding messages that invoked particular tools. section 4.4.1

Tool calls in a message record

A message can retain a nested tool-call payload in its tool_calls column while session_id, role, content, and tool_call_id remain queryable relational fields. Deleting the parent session also removes its messages through the foreign key’s CASCADE behavior. section 4.4.1

How Designing AI Systems covers it

How deep it goes
Explains the JSONB-versus-JSON performance trade-off and schema placement, but stops short of showing query syntax or index definitions.
Examples
Uses a messages table whose tool_calls column stores IDs, function names, and arguments as nested JSONB data.
What it assumes
Assumes familiarity with PostgreSQL tables and JSON-shaped tool-call data, while defining JSONB’s storage role and trade-offs.
What you can do after
Designs a PostgreSQL message schema that stores searchable nested tool-call data without additional tables.
How it connects
Connects JSONB with PostgreSQL, relational schemas, tool calls, indexing, foreign keys, and cascading deletes.

Also explained in

Compare how 3 books explain jsonb →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Designing AI Systems. The section links point to the parts of the book it draws on.