JSONB, Arrays, and Generated Columns

1. Introduction

PostgreSQL supports rich data types beyond standard scalars. JSONB stores parsed JSON documents with indexing support, Arrays store ordered collections of any base type, and Generated Columns (PG 12+) compute values automatically from other columns. Together, they let you model flexible schemas within a relational database — combining the best of document and relational paradigms.

2. Why It Matters

Many modern applications need schema flexibility — product catalogs with varying attributes, user profiles with optional fields, or event payloads with evolving schemas. JSONB lets you store these without constant ALTER TABLE migrations. Arrays simplify many-to-many relationships for simple tag/label patterns. Generated columns eliminate the need for triggers to maintain computed fields.

3. Real-World Analogy

JSONB is like a filing cabinet where each folder can have different forms inside — one customer folder has address fields, another has social media handles, but they all fit in the same cabinet. Arrays are like a sticky note with a list of tags on each folder. Generated columns are like an auto-calculated field on each form that sums up other values.

4. How It Works

• JSONB vs JSON: JSON stores raw text (re-parsed on every query). JSONB stores a decomposed binary format — supports indexing, operators (@>, ?|, ->>), and GIN indexes.
• Arrays: Declared as integer[] or text[]. Support ANY, ALL, array_agg, and GIN indexes for containment queries.
• Generated Columns: Declared with GENERATED ALWAYS AS (expression) STORED. Recomputed on every INSERT/UPDATE. Can be indexed unlike virtual columns.

5. Internal Architecture

JSONB stores data as a sorted key-value structure internally, enabling O(log n) key lookups. GIN indexes on JSONB use an inverted index — each key/value pair becomes a separate index entry. Arrays are stored as contiguous memory blocks with a dimension header. Generated columns are stored physically (STORED) — they occupy disk space but are computed automatically, similar to materialized computed expressions.

6. Visual Explanation

The diagram shows a products table with a JSONB attributes column, a text array tags column, and a generated full_name column, with queries using GIN indexes for each.

7. Practical Example

8. Common Mistakes

• Using JSON instead of JSONB: JSON re-parses on every query. Always use JSONB unless you need to preserve exact formatting.
• Missing GIN index on JSONB columns: Without it, containment queries (@>) do sequential scans on large tables.
• Storing relational data in JSONB: If a field is always queried, joined, or constrained, it belongs as a regular column — not inside JSONB.
• Array anti-patterns: Using arrays for data that should be in a junction table (e.g., arrays of user IDs for group membership) loses FK enforcement.

9. Quick Quiz

10. Scenario-Based Challenge

Design an event sourcing table that stores event payloads as JSONB. Each event has a type, timestamp, and flexible payload. Create GIN indexes for payload queries, a generated column that extracts the event type from the payload, and write a query that filters events by nested JSONB fields with proper index usage.

11. Debugging Exercise

12. Interview Questions

• Q: When should you use JSONB vs a separate table?
  A: Use JSONB for semi-structured data where the schema varies per row (product attributes, event payloads). Use separate tables for data that has consistent structure, needs FK constraints, or is frequently joined.
• Q: What is the difference between -> and ->> operators?
  A: -> returns JSONB (can be chained), ->> returns TEXT (for use in WHERE clauses and comparisons).

13. Production Considerations

• GIN index size: GIN indexes on JSONB can be 2-5x the size of the data. Use gin_trgm_ops or partial indexes to reduce size.
• jsonb_path_ops: Use CREATE INDEX ... USING GIN (col jsonb_path_ops) for containment-only queries — smaller and faster than default GIN.
• TOAST storage: Large JSONB values are compressed and stored out-of-line (TOAST). This adds decompression overhead for frequent reads.
• Generated columns: Only STORED is supported (no VIRTUAL). They occupy disk space but can be indexed and used in constraints.