JSONB Columns: When They Save You, When They Trap You

You need to store arbitrary user-defined fields. Your first instinct is to add a jsonb column. That instinct is sometimes right and sometimes the start of a 6-month schema debt spiral.

Here is when each outcome happens.

---

1. What JSONB Actually Gives You

PostgreSQL's jsonb stores a binary-parsed, deduplicated, ordered representation of JSON. It is not just a text dump. You get:

• GIN indexes on keys and values
• Containment operators (@>, <@)
• Path-based querying with -> and ->>
• jsonb_set() for in-place mutation

That is a real feature set. The problem is not that JSONB is weak. The problem is that engineers reach for it when a normalized column would do the job in a third of the effort.

---

2. Four Cases Where JSONB Saves You

Case 1: Truly variable, user-defined attributes

A SaaS CRM where each client configures their own contact fields. You cannot know the schema at deploy time. JSONB is correct here. Trying to normalize this leads to EAV tables, which are worse.

Case 2: Third-party API payloads you need to store but rarely query

You receive a Stripe or Twilio webhook. You need to replay it later. Store the full payload in jsonb. You will query by event_id (a normal column) not by nested fields.

Case 3: Sparse optional attributes

A product table with hundreds of optional spec fields. If 90 percent of rows leave 80 percent of those fields null, a jsonb column saves you from a table with 80 nullable columns and the migration headache that comes with each schema change.

Case 4: Prototyping before the schema stabilizes

Your data model is shifting weekly. JSONB buys you time. The contract: when the shape stabilizes, you migrate those fields out into typed columns. Most teams skip the migration. That is where the trap starts.

---

3. Five Traps That Will Eventually Hurt You

Trap 1: Querying deeply nested keys at scale

data->>'email' works fine at 10k rows. At 10M rows, without the right GIN expression index, it degrades to a sequential scan. The query planner does not help you here the way it does with a typed column.

Fix: create an expression index on the key you query.

CREATE INDEX idx_users_data_email
ON users ((data->>'email'));

Still slower than an indexed varchar column. Accept that trade-off consciously.

Trap 2: Type coercion is your problem now

PostgreSQL stores everything inside JSONB as a JSON type. Numbers come back as text when you use ->>. data->>'price' returns "9.99" as a string. You cast in application code, or you use (data->>'price')::numeric everywhere. Miss one cast and you get silent string comparisons on numeric fields.

Trap 3: Constraint enforcement is gone

A normal integer NOT NULL column enforces its own contract. A jsonb key does not. Nothing stops a bad deploy from writing {"amount": null} or {"amount": "twenty"} into rows that used to have {"amount": 20}. You find out during an aggregation query at 2am.

Workaround: check constraints on computed JSONB expressions. Ugly, but they work.

ALTER TABLE orders
ADD CONSTRAINT chk_amount_numeric
CHECK ((data->>'amount') IS NULL OR (data->>'amount') ~ '^\d+(\.\d+)?$');

Trap 4: Schema migrations become archaeology

Six months in, nobody is sure whether settings->>'notify_email' is a boolean string "true" or an actual JSON boolean true. Both exist in production because two different code paths wrote different shapes. You cannot run a migration until you know which rows have which shape. That audit takes time you did not budget.

Trap 5: You lose foreign key enforcement

If data->>'user_id' references your users table, PostgreSQL cannot enforce that relationship. You get orphaned references. The referential integrity contract that a normal foreign key gives you for free does not exist inside a JSONB column.

---

4. The Decision Heuristic - Three Questions

Answer these before you add the column:

1. Will you filter or aggregate by any of these keys in SQL? If yes, the pressure on you is strong to normalize. You can index, but a typed column will always be cheaper to query and safer to aggregate.

2. Does the shape vary per row in a way you cannot enumerate? If no, you know the fields - use columns. If yes, JSONB earns its place.

3. Do you need referential integrity or NOT NULL enforcement on any key? If yes, normalized column. No amount of check constraints on JSONB is as reliable as a database-enforced column constraint.

A rough rule: if you can name the fields, use columns. If users define the fields, use JSONB.

---

5. Index It or Forget Querying It

Three index patterns worth knowing:

• GIN on the whole column: CREATE INDEX idx_data_gin ON table USING GIN (data); - good for containment queries (@>), bad for specific key lookups.
• Expression index on a key: CREATE INDEX idx_key ON table ((data->>'key')); - fast for equality on that one key.
• GIN with jsonb_path_ops: tighter index, only supports @>. Smaller on disk.

Pick based on query shape. Do not index speculatively. Each GIN index has a meaningful write overhead and storage cost.

---

6. When to Migrate Out

You added JSONB to prototype. The shape stabilized. Now you should migrate.

Signs it is time:

• You are casting the same 3 keys in every query
• A developer wrote a wrong cast and it hit production
• You want to add a foreign key that lives inside the column
• You want NOT NULL or unique enforcement on a JSONB key

The migration is straightforward. Add the typed column, backfill with a one-time script, update the write path to set both columns, verify, then drop the JSONB key or the whole column.

-- Add typed column
ALTER TABLE contacts ADD COLUMN email varchar(255);

-- Backfill
UPDATE contacts SET email = data->>'email' WHERE data->>'email' IS NOT NULL;

-- Add constraint after backfill
ALTER TABLE contacts ALTER COLUMN email SET NOT NULL;

Do it in stages. Backfill in batches if the table is large. This is the same principle as any zero-downtime schema change.

This is the same discipline you need for any performance-sensitive query path. If you're dealing with query performance issues at the ORM level as well, Overusing with() and Missing the Real N+1 in Laravel covers the hidden query patterns that compound the problem.

---

Summary

Situation	JSONB	Normalized
User-defined fields	Yes	No
Known fields, queried often	No	Yes
Webhook / API payload storage	Yes	No
Needs foreign key	No	Yes
Needs NOT NULL / unique	Painful	Yes
Prototype, schema unstable	Yes	Later

JSONB is a sharp tool. It solves real problems that EAV tables and nullable column sprawl make worse. But it pushes type safety, constraint enforcement, and query efficiency back onto you. Use it where schema variability is genuine. Migrate out when the shape solidifies. Index the keys you actually query.

The trap is not using JSONB. The trap is forgetting to revisit the decision six months later.