JSONB Storage & Expression Indexing

Mental model

PostgreSQL provides two JSON types: json (stores raw unparsed text preserving whitespace and duplicate keys) and jsonb (decomposed binary format enabling fast index lookup and field extraction).

Theory

jsonb parses JSON strings into a binary tree structure during insertion. This enables fast field extraction (data->>'email'), containment checks (data @> '{"role": "admin"}'), and path existence testing (data ? 'stripe_id').

Expression indexes (CREATE INDEX ON table ((data->>'email'))) build B-Tree indexes directly over extracted JSONB fields.

Alternatives and trade-offs

• json (Text format): Fast insertion; slow query processing due to repeated parsing.
• jsonb (Binary format): Slightly slower insertion; fast indexing and query extraction.
• Relational Columns: Strong typing and constraints; rigid schema migration requirements.

Failure modes and misconceptions

1. Using Default GIN Index for Scalar Fields: gin(payload) indexes all keys and values, consuming excessive disk space. Use gin(payload jsonb_path_ops) or targeted B-Tree expression indexes for single fields.
2. Missing Type Casts on Expression Indexes: Indexing (payload->>'age') indexes text. You must cast ((payload->>'age')::int) to match numeric queries.

Decision scenario

Use jsonb with a specialized jsonb_path_ops GIN index for audit logs and event store payloads with dynamic attributes that vary across event types.

Learning outcomes

• Contrast json text storage with jsonb binary format performance.
• Index JSONB documents using jsonb_path_ops GIN indexes.
• Build B-Tree expression indexes on extracted JSONB fields.

Trade-offs

JSONB enables flexible schema-less document storage in PostgreSQL, but lacks native row-level column type validation without custom check constraints or triggers.