JSONB Anti-Patterns: when NOT to use JSONB and how to spot it

Capsule overview

Up to here the guide pushed you toward JSONB: "it's PostgreSQL's JSON-native engine," "it beats JOINs when it's well indexed," "it's what you want 99% of the time." All true. But the other side of the coin — the filter that separates the senior dev from the junior one — is knowing when NOT to use JSONB. Schema flexibility has a cost, and abusing it ends in systems that technically "work" but drag permanent debt along.

This capsule is the counterweight. We're going to go through the anti-patterns that show up in real production, in code reviews, in legacy migrations. You'll learn to recognize "JSONB used as a bag-of-everything," "JSONB for relational data," "over-indexed JSONB," "queries no GIN can save," and "payload bloat" — all mechanisms by which a JSONB column becomes the worst decision in the schema.

More importantly: you'll learn the smell test — concrete questions you apply before declaring JSONB in a schema, and before accepting a PR that adds it. And you'll have a playbook for migrating existing JSONB to relational tables when it's already debt. It's the capsule that makes you the dev people ask "should we put this in JSONB?" — and the answer is educated, technical, and well-reasoned.

Mental model: JSONB has a "hidden price"

When you add data JSONB to a table, you don't just add flexibility. You add:

┌────────────────────────────────────────────────────────────┐
│                                                            │
│  COSTS OF JSONB:                                           │
│                                                            │
│  • Storage:        +30% vs columns                         │
│  • GIN size:       2-5x more than an equivalent B-tree     │
│  • Insert cost:    GIN pays for every token                │
│  • Update cost:    JSONB is immutable → rewrites it whole  │
│  • Query overhead: extraction + cast on every use          │
│  • Schema doc:     there is none, you read code/data       │
│  • Constraints:    limited (CHECK + path queries)          │
│  • Tooling:        ORMs handle it worse than columns       │
│  • Query plan:     the planner has less info to optimize   │
│                                                            │
└────────────────────────────────────────────────────────────┘

In exchange, you gain:

• A flexible schema (no migration for a new optional field).
• Native support for nested structures / arrays.
• Polymorphic data.

The rule: JSONB is the right tool when the benefits outweigh the costs. When they don't, it's technical debt with modern syntax.

Anti-Pattern 1: JSONB as a "bag-of-everything"

The most common one. A table with a data JSONB column where the team progressively dumps everything they don't want to add as a formal column.

Symptoms

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  data JSONB DEFAULT '{}'
);

-- Example row after 2 years:
-- {
--   "email": "ana@example.com",
--   "phone": "+5491112345678",
--   "tier": "premium",
--   "active": true,
--   "joined_at": "2023-01-15",
--   "last_login": "2026-04-30T10:00:00Z",
--   "preferences": {...},
--   "stripe_customer_id": "cus_abc123",
--   "addresses": [...],
--   "notes": "...",
--   "tags": ["vip", "early_adopter"]
-- }

Each of those fields:

• email: every customer has exactly one, it should be UNIQUE → column.
• phone: every customer has one, format validation → column.
• tier: an enum with fixed values → column (with a CHECK or an ENUM type).
• active: a boolean that's always present → column.
• joined_at, last_login: timestamps with range queries → column.
• stripe_customer_id: an external identifier used in implicit JOINs → column.
• addresses: relational entities with their own ID → a separate table.
• notes: long text → a text column.
• tags: an array you filter on (@> '["vip"]') → it could be a JSONB array, or a tags table with M2M, depending on the use.
• preferences: a flexible schema that evolves → legitimate JSONB.

Why it happens

• Short-term development speed. "I need to add phone. If I put it in data, I don't do a migration." 5 minutes vs 30 minutes.
• A lack of schema governance. Without code reviews that question "should this be a column?", nobody stops the growth.
• Inherited models. Schemas migrated from NoSQL to PostgreSQL keep the "everything in one document" mindset.

Why it hurts in the long run

• Nonexistent validation. An email with no format, a tier with typos ('premim' instead of 'premium') — everything gets through.
• Slow queries on common fields. Without specific expression indexes, every filter by data->>'tier' is a Seq Scan or requires an expression index. Soon you have 10 expression indexes and the "savings" of not migrating have evaporated.
• Schema documentation = the code. Onboarding takes longer, every new bug requires "exploring the JSONB" to understand which keys exist.
• The refactor gets more expensive over time. At 6 months, moving email to a column is trivial. At 3 years with 20M rows, it's a project.

How to spot it in a code review

Filter questions:

1. "Is this key going to be in EVERY row?" If yes → column.
2. "Do you want to validate the format/value?" If yes → a column with a CHECK / a dedicated type.
3. "Do you want a UNIQUE constraint?" If yes → column.
4. "Are you going to index this key?" If yes → column (cheaper and more predictable).

If all four answers are "yes" for a specific key, that key is NOT JSONB.

Anti-Pattern 2: JSONB for relational data

More subtle. It puts things in JSONB that are clearly entities with their own relationships.

Symptoms

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id),
  data JSONB
);

-- Example:
-- {
--   "items": [
--     {"product_id": 1, "qty": 3, "unit_price": 50.00},
--     {"product_id": 5, "qty": 1, "unit_price": 200.00}
--   ],
--   "discounts": [
--     {"code": "SAVE10", "amount": 10.00}
--   ]
-- }

items is an array of objects where each one has a product_id that points at the products table. Those are relational order_items, not metadata.

Why it hurts

• No FK, no integrity. You can have product_id: 99999 that doesn't exist. PostgreSQL doesn't know.
• JOIN is impossible/ugly. "List the products sold this week" requires jsonb_array_elements + a cast + a JOIN — not usable by the planner for optimization.
• Slow aggregations. "Top 10 products sold" forces an unnest of the JSON on every row.
• Per-item updates are catastrophic. Changing the qty of a specific item requires rewriting the whole JSONB (jsonb_set with a path by array index).

Solution: a relational table

CREATE TABLE order_items (
  order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT REFERENCES products(id),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_discounts (
  order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
  code TEXT NOT NULL,
  amount NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, code)
);

Trivial JOIN, FKs, constraints, fast aggregations. What JSONB was pretending to do is now explicit.

The specific smell test

Apply the 5 questions from capsule 02:

1. Am I going to JOIN with this data? If yes → table.
2. Am I going to have foreign keys pointing at this? If yes → table.
3. Is every record going to have exactly the same keys? If yes → columns.
4. Am I going to run aggregations (SUM, AVG, GROUP BY) over these fields in critical queries? If yes → table.
5. Do I need constraints on these fields? If yes → table.

order_items fails all 5. JSONB is the wrong choice.

Anti-Pattern 3: Over-indexing with GIN "just in case"

-- A table with 5 JSONB columns
CREATE TABLE logs (
  id BIGSERIAL PRIMARY KEY,
  request_data JSONB,
  response_data JSONB,
  user_data JSONB,
  trace_data JSONB,
  metadata JSONB
);

-- And someone creates this:
CREATE INDEX ON logs USING gin(request_data);
CREATE INDEX ON logs USING gin(response_data);
CREATE INDEX ON logs USING gin(user_data);
CREATE INDEX ON logs USING gin(trace_data);
CREATE INDEX ON logs USING gin(metadata);

Why it hurts

Every GIN pays a cost per insert. If your table is write-heavy (1000 logs/second), you have 5 GINs updating on every insert. Write latency multiplies.

And worse: if only 1 of the 5 indexes is used in real queries, the other 4 are pure cost with no benefit.

How to spot it

-- How many times is each index used?
SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Indexes with idx_scan = 0 after 30+ days of operation = candidates for deletion.

Operational rule

Index what the hot path queries actually filter on. If a JSONB column doesn't appear in a WHERE with @> (or an indexable equivalent), don't index it "preventively."

For columns that are rarely queried but occasionally useful, three options:

1. No index. You accept a Seq Scan on ad-hoc queries.
2. An expression index over the specific field that gets filtered occasionally.
3. A temporary index: you create it before an analysis and drop it afterwards.

Anti-Pattern 4: Queries no GIN can speed up

Even if you have a GIN over the column, there are queries that don't use it. If your hot path depends on those queries, GIN doesn't save you.

Typical cases

4a. Access operators (->>):

-- GIN doesn't speed this up
SELECT * FROM events WHERE payload->>'field' = 'X';

Solution: an expression index, or rewrite with @>.

4b. Numeric / temporal comparisons:

-- GIN doesn't speed this up
SELECT * FROM events WHERE (payload->>'amount')::numeric > 100;
SELECT * FROM events WHERE (payload->>'created_at')::timestamp > '2026-01-01';

Solution: an expression index over the cast, or move the field to a real column.

4c. Pattern matching (LIKE, regex):

-- GIN doesn't speed this up
SELECT * FROM events WHERE payload->>'description' LIKE '%error%';

Solution: full-text search (module 3), or pg_trgm with an expression index. JSONB isn't the tool for text search.

4d. ORDER BY:

-- GIN doesn't sort
SELECT * FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY (payload->>'amount')::numeric DESC LIMIT 100;

Solution: a sorted expression index, ideally partial.

4e. Negation / "does not contain":

-- GIN doesn't speed this up
SELECT * FROM events WHERE NOT (payload @> '{"verified": true}');

GIN finds "what matches X." "What does NOT match X" is the complement — Seq Scan.

Lesson

GIN is not a panacea. If your key queries fall into 4a-4e, a GIN over the JSONB isn't what you need. The question is: is it OK to use JSONB here, or should this field have been a column from the start?

Sometimes the answer is: "yes, JSONB to store the payload, but with an expression index over the hot field or a mirror column for the ordering/comparison."

-- Mirror column: the amount field also lives as a real column
ALTER TABLE events ADD COLUMN amount_numeric NUMERIC(10,2)
  GENERATED ALWAYS AS ((payload->>'amount')::numeric) STORED;

CREATE INDEX events_amount_idx ON events (amount_numeric);

-- Now ORDER BY uses a direct B-tree
SELECT * FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY amount_numeric DESC LIMIT 100;

Generated columns (PostgreSQL 12+) are a powerful pattern for "I have JSONB but I need a field as a real column." We'll also see them in module 2 with SQLAlchemy.

Anti-Pattern 5: Payload bloat

A JSONB column that grows out of control until each row weighs MBs.

Symptoms

-- A table with a payload that accumulates data
CREATE TABLE webhook_events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB
);

-- And someone stores:
-- {
--   "event_type": "...",
--   "raw_request_headers": {...100 keys...},
--   "raw_response_headers": {...100 keys...},
--   "request_body": "...500KB of stringified XML...",
--   "stack_trace": "...100KB of Java stack...",
--   "history": [{...}, {...}, {...}, ...500 entries...]
-- }

Each row weighs 1-5 MB. The table with 1M rows weighs 3 TB.

Why it hurts

• TOAST. PostgreSQL stores large values in a separate table (TOAST). Every access to the big field means extra I/O.
• GIN over a giant payload is inefficient: every insert tokenizes the whole JSON.
• Slower backup / restore. The backup weighs the same as the table.
• Replication lag. Every UPDATE sends the complete JSONB to the replica.
• Memory pressure. Queries that return the whole payload load MBs per row.

Solutions

Move the big stuff out of the "queryable" payload:

CREATE TABLE webhook_events (
  id BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  payload_compact JSONB  -- only the fields you filter on
);

CREATE TABLE webhook_event_blobs (
  event_id BIGINT REFERENCES webhook_events(id) ON DELETE CASCADE,
  raw_request TEXT,
  raw_response TEXT,
  stack_trace TEXT,
  history JSONB
);

Now payload_compact stays small, GIN works well, and hot path queries don't load the blobs. The blobs live separately and are loaded only when needed.

Another option: object storage (S3/GCS) for the blobs.

If the blobs are rarely queried (debug, audit), store them in S3 and keep only the URL in the JSONB:

{
  "event_type": "...",
  "raw_request_url": "s3://bucket/events/123/request.json",
  "raw_response_url": "s3://bucket/events/123/response.json"
}

PostgreSQL shouldn't be your blob store.

Detection

-- See the average and maximum size of the JSONB
SELECT
  pg_size_pretty(AVG(pg_column_size(payload))) AS avg_size,
  pg_size_pretty(MAX(pg_column_size(payload))) AS max_size,
  pg_size_pretty(SUM(pg_column_size(payload))) AS total_size
FROM webhook_events;

If avg_size > 50 KB or max_size > 1 MB, you have bloat. Investigate which keys are the heavy ones.

Anti-Pattern 6: Frequent updates to a single field of the JSONB

-- Every login updates the last timestamp inside the JSONB
UPDATE users
SET data = jsonb_set(data, '{last_login}', to_jsonb(now()))
WHERE id = 42;

Why it hurts

JSONB is immutable. PostgreSQL doesn't update "field X" — it rewrites the whole JSONB, marks the old row as dead, writes a new row, and the GIN gets updated.

If data weighs 50 KB and you update a single key every 5 minutes:

• It rewrites 50 KB every 5 min.
• Table bloat (dead rows until vacuum).
• The GIN is updated on every change.
• Replication: 50 KB go to the replica on every update.

Solution

If the field is updated very frequently, move it to a column:

ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

UPDATE users SET last_login = now() WHERE id = 42;
-- An update of 8 bytes, not 50 KB.

Heuristic: if a JSONB field is updated more than once per hour per row, consider moving it to a column. If it's updated once per day per row, JSONB is fine.

Anti-Pattern 7: using JSONB "because it's flexible and we'll see later"

The most insidious one. With no clear requirement, someone declares JSONB "because it might grow." Six months go by, the keys have settled, and the "flexible schema" is in reality always the same 12 keys.

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  data JSONB
);

-- 6 months later, every row has exactly:
-- {"sku", "category", "price", "stock", "weight", "dimensions",
--  "supplier_id", "created_at", "updated_at", "tags", "description", "images"}

If the "flexible schema" has settled, that's a fixed schema with JSONB syntax. You paid the cost of JSONB without using the flexibility.

When to migrate

If after 3-6 months in production:

• The JSONB keys are always the same.
• The frontends/APIs document those fields as "always present."
• The queries all cast and filter by the same fields.

→ You're describing columns. Migrate.

When to accept the cost

If the JSONB has a legitimately variable schema (per-client config, polymorphic metadata, type-dependent properties), it's fine. The control question: BEFORE putting the data in, do I know which keys it's going to have? If yes, they're columns. If not (it depends on the client / the type / the workflow), JSONB.

The consolidated smell test: the list you apply before declaring JSONB

Before accepting JSONB in a new schema, run these questions:

1. Is every record going to have exactly the same keys?
   YES → columns. NO → continue.

2. Am I going to JOIN with this data?
   YES → table. NO → continue.

3. Am I going to have foreign keys pointing at this?
   YES → table. NO → continue.

4. Am I going to run critical aggregations (SUM, AVG, GROUP BY) over these fields?
   YES → columns. NO → continue.

5. Do I need a UNIQUE / NOT NULL / CHECK constraint on these fields?
   YES → columns. NO → continue.

6. Is any field going to be updated more than once per hour per row?
   YES → that field goes to a column. Others can stay in JSONB.

7. Can the payload grow to >50 KB per row?
   YES → check whether you need to split out blobs. NO → continue.

8. Do you genuinely not know, BEFORE putting the data in, which keys it will have?
   YES → legitimate JSONB. NO → they're probably columns.

If you reach point 8 with an honest "yes," JSONB is the right choice. If questions 1-7 gave you a majority of "yes, it should be a column/table," JSONB is a shortcut you're going to pay for.

Playbook: migrating existing JSONB to tables

For when you've already done the damage and need to reverse it.

Step 1: identify the real keys

-- Which keys appear in the JSONB and how often?
SELECT
  key,
  COUNT(*) AS rows_with_key,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM customers), 2) AS pct
FROM customers, jsonb_object_keys(data) key
GROUP BY key
ORDER BY rows_with_key DESC;

-- Output:
--      key       | rows_with_key | pct
-- ---------------+---------------+-------
--  email         |        100000 | 100.00  ← an obvious column
--  phone         |         98500 |  98.50  ← column
--  tier          |         99800 |  99.80  ← column (with a CHECK)
--  preferences   |         75000 |  75.00  ← legitimate JSONB
--  notes         |          5000 |   5.00  ← nullable column
--  legacy_field1 |           120 |   0.12  ← deprecated, delete

Keys present in > 90% of rows are candidates for a column. Keys with low presence are legitimate metadata or deprecated data.

Step 2: add the columns in parallel (zero-downtime)

-- Migration 1: add the columns
ALTER TABLE customers
  ADD COLUMN email CITEXT,
  ADD COLUMN phone TEXT,
  ADD COLUMN tier TEXT;

Step 3: backfill from the JSONB

-- Backfill in batches so you don't block
DO $$
DECLARE
  batch_size INT := 10000;
  total INT;
BEGIN
  LOOP
    UPDATE customers
    SET email = data->>'email',
        phone = data->>'phone',
        tier = data->>'tier'
    WHERE id IN (
      SELECT id FROM customers
      WHERE email IS NULL
      LIMIT batch_size
    );

    GET DIAGNOSTICS total = ROW_COUNT;
    EXIT WHEN total = 0;
    PERFORM pg_sleep(0.1);  -- a brief pause
  END LOOP;
END $$;

Step 4: dual write from the app

Change the app code to write both to the new columns and to the JSONB. For a period (days/weeks), both are in sync.

Step 5: add constraints and indexes

ALTER TABLE customers
  ALTER COLUMN email SET NOT NULL,
  ADD CONSTRAINT customers_email_unique UNIQUE (email);

CREATE INDEX customers_tier_idx ON customers (tier);

Step 6: switch the queries to read from the columns

A code change. Endpoints read from email (the column), not data->>'email'.

Step 7: stop writing to the JSONB

Once you've confirmed that the app reads from the columns and writes to both, stop writing to the JSONB. The key "freezes."

Step 8: clean up the JSONB

-- After the validation period
UPDATE customers
SET data = data - 'email' - 'phone' - 'tier';

VACUUM customers;

Step 9: drop the JSONB indexes/resources that are no longer used

-- If you had an expression index over these JSONB fields
DROP INDEX customers_email_jsonb_idx;

These techniques are the zero-downtime migrations covered in depth in guide #13. Here we apply them to the JSONB → columns case.

Why does this matter in real work?

1. Code reviews that prevent future debt.

When someone proposes "let's put this in JSONB," you apply the smell test in the comment and the team decides with full information. The difference between "we added JSONB just because" and "we added JSONB because it meets criterion X" is years of maintainability.

2. Diagnosing legacy tables.

You land on a team, pg_stat_user_tables shows a table with enormous bloat, slow queries, an unused GIN. Anti-pattern detector. You know exactly what to look for (the 7 categories) and how to propose a remediation plan.

3. Avoiding the "JSONB tax" in write-heavy systems.

Systems that ingest millions of events/day are especially sensitive to anti-patterns 3, 5, and 6. Knowing them lets you design tables that ingest at a high rate without paying invisible costs.

4. Defending technical decisions.

"Why is email a column and not in JSONB" is a reasonable question. Your answer: "because it's always present, validatable, indexable, unique — all column criteria. JSONB here would pay a cost with no benefit." That clarity makes you the dev people ask.

Traps and common mistakes

Mistake 1 (conceptual): thinking that "everything in JSONB" is modern or flexible

Symptom: teams coming from NoSQL put everything in JSONB because "that's how we worked in MongoDB."

Why it's wrong: PostgreSQL isn't MongoDB. The relational tools (FK, JOIN, constraints) are advantages, not restrictions. Giving them up to "imitate NoSQL" loses what PostgreSQL gives you for free.

How to fix it: relational modeling for relational data, JSONB for genuinely semi-structured data.

Mistake 2 (practical): migrating to JSONB "for future flexibility" with no current requirement

Symptom: "let's use JSONB instead of columns because tomorrow we might add fields."

Why it's wrong: YAGNI. PostgreSQL lets you add columns with ALTER TABLE (with zero-downtime techniques for large tables — guide #13). "Future flexibility" rarely materializes, and when it does, adding a column is trivial.

How to fix it: model for the current requirements with columns. If a genuinely variable schema shows up in the future, add a metadata JSONB column specifically for that.

Mistake 3 (conceptual): assuming JSONB saves storage

Symptom: "let's put everything in JSONB because it saves space vs empty columns."

Why it's wrong: PostgreSQL handles NULL columns very efficiently (1 bit in the null bitmap). 20 nullable columns, mostly null, take up less than a JSONB with 5 keys. JSONB has structural overhead per row.

How to fix it: measure. pg_column_size tells you the real cost.

Mistake 4 (conceptual): seeing expression indexes as "the solution to everything"

Symptom: a table with 15 expression indexes to compensate for the lack of columns.

Why it's wrong: at that point, those 15 keys ARE columns. What you have is "columns with verbose syntax and double the maintenance." Migrate.

How to fix it: if you see more than 3-4 expression indexes over a single JSONB column, reconsider: those fields should probably be real columns.

Mistake 5 (practical): thinking JSONB can be cleaned up "later"

Symptom: "let's put this in JSONB now, we'll refactor it later."

Why it's wrong: "later" becomes "never" in most teams. And when it becomes "now, with 50M rows," the migration is a weeks-long project.

How to fix it: the right decision at design time is 100x cheaper than the migration. Apply the smell test before, not after.

Mistake 6 (conceptual): treating generated columns as "magic that fixes anti-patterns"

Symptom: keeping a huge JSONB + generated columns for every important field.

Why it's problematic: now you pay two costs: the storage of the original JSONB + the storage of the generated columns + the maintenance of both. If all the queries use the generated columns, the JSONB is pure duplication.

How to fix it: generated columns for 1-2 hot fields that you need as a real column but where you want to keep the JSONB. Not for 10 fields.

Exercises

Exercise 1: apply the smell test

For each schema proposal, apply the smell test and decide: legitimate JSONB, columns, or a separate table?

a) posts.metadata JSONB with {seo_title, seo_description, og_image, twitter_card}. Every post has exactly these 4. It's occasionally filtered by seo_title for an SEO audit.

b) users.feature_flags JSONB with {flag_name: boolean}. Each user can have 0-50 active flags depending on experiments. It's queried to check whether a flag is enabled.

c) events.attendees JSONB with an array of {user_id, status, rsvp_at}. Typically 10-500 attendees per event. You need to list attendees, count by status, mark as cancelled.

d) products.specifications JSONB with technical specs that vary by category (a laptop has cpu, ram, screen_size; clothing has size, color, material). These fields aren't filtered on in hot path queries.

Exercise 2: spot the anti-pattern

They show you this legacy schema with reported problems of "slow queries and bloat":

CREATE TABLE webhook_logs (
  id BIGSERIAL PRIMARY KEY,
  endpoint TEXT,
  data JSONB
);

-- A typical example row:
-- {
--   "method": "POST",
--   "path": "/api/v1/orders",
--   "status_code": 200,
--   "request_headers": {...50 keys...},
--   "request_body": "...100KB...",
--   "response_headers": {...30 keys...},
--   "response_body": "...50KB...",
--   "duration_ms": 145,
--   "user_id": 12345,
--   "timestamp": "2026-04-30T10:00:00Z"
-- }

-- Table with 100M rows, average row size: 200KB.
-- Most common queries: filters by method, path, status_code, user_id, timestamp.

Identify the anti-patterns and propose a refactor.

-- Main table: only the queryable stuff + semi-structured metadata
CREATE TABLE webhook_logs (
  id BIGSERIAL PRIMARY KEY,
  endpoint TEXT NOT NULL,
  method TEXT NOT NULL,
  path TEXT NOT NULL,
  status_code INT NOT NULL,
  user_id BIGINT,
  duration_ms INT,
  timestamp TIMESTAMPTZ NOT NULL,
  metadata JSONB  -- compact headers if you need to filter them
);

-- Appropriate indexes
CREATE INDEX ON webhook_logs (timestamp DESC);
CREATE INDEX ON webhook_logs (user_id, timestamp DESC);
CREATE INDEX ON webhook_logs (status_code) WHERE status_code >= 400;  -- partial for errors
CREATE INDEX ON webhook_logs (path, timestamp DESC);

-- A separate table for the large blobs
CREATE TABLE webhook_log_bodies (
  log_id BIGINT PRIMARY KEY REFERENCES webhook_logs(id) ON DELETE CASCADE,
  request_body TEXT,
  response_body TEXT,
  request_headers JSONB,
  response_headers JSONB
);

Exercise 3: defend a decision

Your PM tells you: "We're going to put all the user's context (preferences, roles, permissions, notification settings, UI configuration, a history of the last 100 actions) into a data JSONB column of the users table. That way the frontend makes a single query and gets everything." What do you argue?

-- users with basic data + semi-structured preferences
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email CITEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  preferences JSONB DEFAULT '{}',  -- legitimate JSONB
  ui_settings JSONB DEFAULT '{}',  -- legitimate JSONB
  notification_settings JSONB DEFAULT '{}'  -- legitimate
);

-- Roles and permissions: M2M
CREATE TABLE user_roles (
  user_id BIGINT REFERENCES users(id),
  role_id BIGINT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

-- History: a separate table, partitionable, paginatable
CREATE TABLE user_action_log (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  action TEXT,
  occurred_at TIMESTAMPTZ DEFAULT now(),
  details JSONB
);
CREATE INDEX ON user_action_log (user_id, occurred_at DESC);

Exercise 4: prioritize the refactor

You have 4 anti-patterns in a legacy codebase. Limited resources, you can attack one this sprint. Which do you prioritize and why?

a) A customers table with 1M rows with email and tier in JSONB. Queries by email are slow (50-100ms). 100k requests/day.

b) An audit_logs table with 50M rows with 5 GIN indexes, only 1 of which is used according to pg_stat_user_indexes. Inserts at 1k/sec.

c) A products table with 100k rows with a metadata JSONB that has a description text averaging 500 bytes. No queries reported as slow.

d) An events table with 500M rows with a payload JSONB that grows 2GB/day. It's already at 8TB. Backups take 6 hours.

Exercise 5: recognize the hidden anti-pattern

Sometimes the anti-pattern isn't obvious. Look at this query and schema, and identify whether there's an anti-pattern and which one:

CREATE TABLE notifications (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  data JSONB
);

CREATE INDEX ON notifications USING gin(data);
CREATE INDEX ON notifications ((data->>'type'));
CREATE INDEX ON notifications ((data->>'priority'));
CREATE INDEX ON notifications ((data->>'category'));
CREATE INDEX ON notifications ((data->>'channel'));
CREATE INDEX ON notifications (((data->>'created_at')::timestamptz));

-- Typical query:
SELECT * FROM notifications
WHERE user_id = 42
  AND data->>'type' = 'message'
  AND data->>'channel' = 'email'
  AND (data->>'created_at')::timestamptz > now() - interval '7 days'
ORDER BY (data->>'created_at')::timestamptz DESC;

CREATE TABLE notifications (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  type TEXT NOT NULL,
  priority TEXT NOT NULL,
  category TEXT NOT NULL,
  channel TEXT NOT NULL,
  read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload JSONB DEFAULT '{}'  -- only specific metadata, not common fields
);

CREATE INDEX ON notifications (user_id, created_at DESC);
CREATE INDEX ON notifications (user_id, read, created_at DESC) WHERE read = FALSE;
-- specific partials depending on the queries

Summary and next step

In this capsule you learned the 7 critical JSONB anti-patterns:

1. Bag-of-everything: putting all the fields in JSONB because "it saves migrations."
2. Relational data in JSONB: entities with FK / JOIN / aggregations that should be tables.
3. Over-indexing with GIN: "preventive" GINs that pay on inserts without being used.
4. Queries GIN doesn't speed up: access operators, numeric comparisons, ORDER BY, LIKE — GIN doesn't solve them.
5. Payload bloat: large blobs inside the JSONB that kill storage, replication, queries.
6. Frequent updates to one field: JSONB is immutable, every update rewrites everything.
7. JSONB for no reason: a fixed schema disguised as "flexible" that pays the costs of JSONB without using the flexibility.

And the consolidated smell test you apply before declaring JSONB:

• Fixed schema? → columns.
• Needs JOIN/FK/critical aggregations? → table.
• Needs unique constraints/validation? → column.
• Frequent updates to specific fields? → those fields to a column.
• Can the payload grow a lot? → consider splitting it.
• Genuinely variable schema? → legitimate JSONB.

Before moving on you should be able to:

• Spot the 7 anti-patterns in a code review in under 5 minutes
• Apply the smell test to any schema proposal with JSONB
• Design a zero-downtime migration plan for JSONB → columns
• Defend schema decisions with technical criteria, not opinions

Next capsule — Project: an events table with 5M rows. Time to apply everything: you're going to generate a 5M-row dataset on your machine, start from a slow baseline (4-8 seconds), apply the module's techniques (jsonb_path_ops, partial indexes, expression indexes, rewritten queries), and produce a before/after benchmark that shows a 2+ order-of-magnitude improvement. It's the reproducible version of the anchor case (50M, 4s → 12ms) at a scale that fits on your disk. And it's the module's deliverable.

Resources

1. PostgreSQL 16 Documentation — JSON Types: Designing JSON documents — the project's guidelines on when to use JSON.
2. pganalyze — "Why Postgres JSONB is overrated and what to use instead" — a critical perspective with cases.
3. Crunchy Data — "JSONB modeling pitfalls" — patterns and anti-patterns with examples.
4. Hussein Nasser — "When NOT to use JSONB" — a video walkthrough with real cases.
5. Bruce Momjian — "JSON Capabilities Slides" — a section on when to use native JSON vs relational structures.
6. PostgreSQL Wiki — Index Maintenance — queries for auditing unused indexes.
7. Tom Lane on JSONB design choices (pgsql-hackers) — technical discussions from the committer about JSONB tradeoffs.
8. The Art of PostgreSQL — Dimitri Fontaine — a chapter dedicated to relational vs document modeling (a complete book, a free chapter is available).

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: The module project — replicating the 4s → 12ms case with your own dataset.