JSONB, arrays, and when a side table wins

A team adds a “metadata” JSONB column for “occasional extra fields.” Three years later the column has 30 fields that every query touches — none indexed, none constrained, all parsed on every read. The schema grew into exactly what they were trying to avoid.

JSONB vs JSON: always JSONB

Postgres has two JSON storage types. JSON stores text verbatim — it is re-parsed on every read, not indexable, and slightly smaller. JSONB stores a parsed binary structure — indexable, queryable with path operators, slightly larger. In production, always JSONB. The only reason to use JSON is if you need to preserve key order or duplicate keys, which legitimate schemas never do.

When JSONB is the right choice

JSONB fits three shapes well:

1. Genuinely heterogeneous data. Event logs where each event type has a different payload shape. Third-party API responses where the schema is controlled by someone else. Configuration objects where keys differ per tenant.

2. Long-tail metadata. A products table where 80% of products share 10 columns and 20% have 50 additional supplier-specific fields. The 10 columns are typed; the 50-field tail is JSONB.

3. Schema-first, query-last. Data you store but rarely query — file uploader metadata, per-row user preferences. If the only query is “show me this row’s metadata,” JSONB is fine.

The wrong shape: any field you GROUP BY, JOIN on, aggregate, enforce uniqueness on, or reference with a foreign key. Those fields must be typed columns.

When a side table wins

Typed arrays (TEXT[], INTEGER[]) and JSONB both lose to a side table when:

• You need to query “all rows with tag X” at scale — a GIN index helps, but a join table with a B-tree index on (tag_id, row_id) is faster and lets you enforce FK integrity.
• You need to rename tag X globally — one UPDATE on the tags table vs. scanning every row that contains the tag.
• You need to count rows per tag, or join tags to another table — SQL aggregation over a join table is an order of magnitude cheaper than GIN-indexed JSONB.
• You need uniqueness per row (no duplicate tags on one item) — easily enforced on a join table’s composite PK; impossible to enforce inside an array without a constraint function.

The decision rule: if you only ever read “the tags on this row,” an array column is fine. The moment you query from the tag direction, use a side table.

JSONB index strategies

JSONB has two index families. Choosing the wrong one makes queries 10-100x slower than needed.

GIN (Generalized Inverted Index). Indexes every key or path inside the JSONB. Default operator class indexes every key; jsonb_path_ops indexes whole paths (faster for @> containment queries, larger index). Supports @>, ?, ?|, ?& operators.

-- Default GIN: supports ?, ?|, ?& and @>
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- jsonb_path_ops: only @> (containment), but faster for it
CREATE INDEX idx_events_payload_paths ON events USING GIN (payload jsonb_path_ops);

Expression B-tree. Indexes one specific path extracted as a typed value. Supports equality and range queries on that path. Much smaller than GIN.

-- Indexes the user_id field as text
CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));

-- Indexes user_id as integer (cast)
CREATE INDEX idx_events_user_id_int ON events ((( payload->>'user_id')::BIGINT));

Use GIN for “does this JSONB contain key/value X?” Use expression B-tree for “find rows where data.field equals this specific value.”

Generated STORED columns

Postgres supports generated columns whose value is computed from other columns at write time and stored:

ALTER TABLE order_items
  ADD COLUMN line_total_cents INTEGER
  GENERATED ALWAYS AS (unit_price_cents * quantity) STORED;

The column is queryable, indexable, and updated automatically on every write. Unlike a trigger, the computation is declared in the schema and is visible to any reader without knowing the trigger exists.

Use cases: derived values you query frequently (full_name, line_total, is_final from a status enum), audit flags, computed denormalization. Trade: writes are slightly slower (expression evaluated per write); migrations on generated columns can trigger table rewrites.

FK at scale: the PlanetScale pattern

Some hyperscale shops (PlanetScale on Vitess, several large Postgres deployments) recommend disabling foreign keys. The specific conditions where this is reasonable:

• Data is sharded and the relationship crosses shard boundaries — FKs cannot span shards.
• A cascade would create a multi-million-row transaction holding locks for minutes.
• The FK validation pass on a column-type-change DDL is the operational bottleneck.

None of those conditions apply to a typical SaaS schema below ~100M rows per table. For most teams, the FK constraint costs ~5-50 μs per row at write time and refuses every orphan-row bug forever. Disabling it pushes the integrity guarantee into application code, where it is implemented inconsistently and breaks during refactors.

Senior engineers should treat “we disabled FKs” as a signal of specific scale constraints — not a general best practice.