{"id":205,"date":"2026-05-09T13:01:42","date_gmt":"2026-05-09T13:01:42","guid":{"rendered":"https:\/\/abrarqasim.com\/blog\/postgres-jsonb-2026-when-i-reach-for-it\/"},"modified":"2026-05-09T13:01:42","modified_gmt":"2026-05-09T13:01:42","slug":"postgres-jsonb-2026-when-i-reach-for-it","status":"publish","type":"post","link":"https:\/\/abrarqasim.com\/blog\/postgres-jsonb-2026-when-i-reach-for-it\/","title":{"rendered":"PostgreSQL JSONB in 2026: When I Reach for It (and When I Don’t)"},"content":{"rendered":"

I built a side-project schema in 2022 where almost every table had a column called data jsonb<\/code>. I thought I was being clever. I was being lazy. Two years later an analyst asked me to find every order from a specific supplier in the last quarter, and I spent forty-five minutes writing a query that should have taken three. The cause: I’d stuffed supplier_id<\/code> into JSONB instead of giving it its own column.<\/p>\n

If you’re reaching for jsonb<\/code> in Postgres because it feels flexible, this post is the conversation I wish someone had with me. JSONB is great in the right place and a slow tax everywhere else. Here’s how I decide in 2026.<\/p>\n

Short version for the impatient: JSONB is for data whose shape genuinely varies per row<\/strong>. Everything else belongs in normal columns. If you can list the keys you’d put in JSONB on a piece of paper, those keys are columns.<\/p>\n

The mistake I made for two years<\/h2>\n

Early on I treated JSONB as “schema-less Postgres”. That framing is technically true and operationally awful. The trap is this: schema-less storage means schema-less queries, which means every consumer of your data has to know the implicit shape. The implicit shape rots. People add keys. Someone capitalizes one wrong. You end up with the worst of both: a relational database holding documents, with neither relational guarantees nor document-database tooling.<\/p>\n

The version of my schema that finally worked looked like this:<\/p>\n

-- before: lazy, regretted later\nCREATE TABLE orders (\n  id    bigserial PRIMARY KEY,\n  data  jsonb NOT NULL\n);\n\n-- after: real columns for the things I query, jsonb for the rest\nCREATE TABLE orders (\n  id           bigserial PRIMARY KEY,\n  customer_id  bigint NOT NULL REFERENCES customers(id),\n  supplier_id  bigint NOT NULL REFERENCES suppliers(id),\n  total_cents  integer NOT NULL,\n  placed_at    timestamptz NOT NULL,\n  metadata     jsonb NOT NULL DEFAULT '{}'::jsonb\n);\n<\/code><\/pre>\n
The split is the whole game. Anything I filter, sort, group, or join on becomes a column. Anything that’s payload (webhook bodies, customer-supplied form data, vendor-specific extras) goes in metadata<\/code>.<\/p>\n
What JSONB is actually good for<\/h2>\n
Three places I keep reaching for it on purpose.<\/p>\n
First, webhook payloads and audit trails. The shape comes from the outside world. I don’t control it. I want to keep the original blob for forensics, plus a few extracted columns I actually query. The full payload sits in JSONB, untouched.<\/p>\n
Second, per-tenant custom fields. A B2B app where every tenant adds their own attributes. I keep a custom_fields jsonb<\/code> and let the app define what’s in there. The Postgres docs cover the JSONB type and operators<\/a> in detail; the -><\/code>, ->><\/code>, and @><\/code> operators do most of the work.<\/p>\n
Third, configuration that genuinely varies. Feature-flag rules, integration settings, theming. Stuff where a separate settings<\/code> table would mean a wide schema with mostly null columns.<\/p>\n
A concrete example of querying it cleanly:<\/p>\n
-- find orders where the metadata says shipping is express\nSELECT id, customer_id, total_cents\nFROM orders\nWHERE metadata @> '{"shipping": "express"}'::jsonb\n  AND placed_at > now() - interval '7 days';\n<\/code><\/pre>\n
The @><\/code> containment operator is fast when you have the right index. We’ll get there.<\/p>\n
Where JSONB quietly hurts<\/h2>\n
Now the parts that the marketing posts skip.<\/p>\n
No type checking inside JSONB. A field is \"42\"<\/code> in some rows and 42<\/code> in others. Postgres doesn’t care. Your code does. I’ve watched a billing job silently exclude rows because a string \"true\"<\/code> slipped in where everything else had true<\/code>. The Crunchy Data team wrote a sober post called JSONB: the good, the bad, and the ugly<\/a> that names this exact failure mode and a few others worth reading.<\/p>\n
Updates rewrite the whole document. A small change to one key inside a 4 KB JSONB blob writes 4 KB of WAL. If you’re updating a JSONB column on a hot path, that adds up. TOAST helps with storage but updates still go through the full document path.<\/p>\n
Column statistics are weak. The planner has rich statistics for normal columns: histograms, most common values, n_distinct. For JSONB it has almost nothing. If you filter on metadata->>'status'<\/code>, the planner is guessing at row counts. Bad guesses become bad plans.<\/p>\n
Joins are awkward. You can’t do JOIN suppliers ON suppliers.id = (orders.metadata->>'supplier_id')::bigint<\/code> and feel good about yourself. The cast prevents the planner from using a hash join cleanly. Foreign keys won’t work at all.<\/p>\n
Schema migrations get harder, not easier. The whole pitch of JSONB is “no migrations”. In practice, when the shape stabilizes you still need to backfill, validate, and maybe split keys into columns. Now you have a migration that scans and rewrites every row instead of an ALTER TABLE ADD COLUMN<\/code> that completes in milliseconds. I’ve sat through three of these. Each one took longer than the relational version would have.<\/p>\n
Indexing JSONB without losing your weekend<\/h2>\n
Most of the JSONB grief I’ve seen comes from missing or wrong indexes. Three patterns cover most needs.<\/p>\n
Whole-document containment (@><\/code>). Use a GIN index with jsonb_path_ops<\/code>:<\/p>\n
CREATE INDEX idx_orders_metadata_path\n  ON orders USING gin (metadata jsonb_path_ops);\n<\/code><\/pre>\n
jsonb_path_ops<\/code> is smaller and faster than the default GIN ops, but only supports @><\/code>. That’s usually fine. The full reference is in the Postgres JSON functions docs<\/a>.<\/p>\n
Specific key lookups. Use an expression index:<\/p>\n
CREATE INDEX idx_orders_metadata_status\n  ON orders ((metadata->>'status'));\n<\/code><\/pre>\n
This supports WHERE metadata->>'status' = 'shipped'<\/code> and gives the planner real statistics for that expression. It’s how I get most of the wins.<\/p>\n
Partial indexes for hot subsets. Combine the above:<\/p>\n
CREATE INDEX idx_orders_active_express\n  ON orders ((metadata->>'priority'))\n  WHERE metadata @> '{"shipping": "express"}'::jsonb;\n<\/code><\/pre>\n
Partial indexes are underused. If five percent of your rows match a hot filter, a partial index over them is tiny and obscenely fast.<\/p>\n
A blunt rule: if you query a JSONB key more than once a week and there’s no index for it, you have a problem. Check pg_stat_user_indexes<\/code> to see whether the indexes you added are actually being used.<\/p>\n
ORMs and JSONB: keep the ergonomics, drop the abstraction<\/h2>\n
I’ve worked through both Drizzle and Prisma with JSONB<\/a>, and they both end up wanting raw SQL once the queries get real. JSONB containment, partial indexes, and expression indexes are all features ORMs leak through awkwardly. My rule: model the JSONB column as opaque in the type system, and write the few queries that touch it with raw SQL inside a thin repository function. Don’t let the ORM pretend it understands the document shape.<\/p>\n
\/\/ drizzle: keep jsonb opaque, query with sql template\nconst expressOrders = await db.execute(sql`\n  SELECT id, customer_id, total_cents\n  FROM orders\n  WHERE metadata @> ${JSON.stringify({ shipping: 'express' })}::jsonb\n    AND placed_at > now() - interval '7 days'\n`);\n<\/code><\/pre>\n
You get predicate pushdown, you get the index, you skip the round trips through the ORM’s JSON helpers. I cover this kind of pattern in my database-heavy backend work<\/a>.<\/p>\n
The decision tree I actually use now<\/h2>\n
Three questions, in order.<\/p>\n
Will I query, sort, or join on this field? If yes, it’s a column. JSONB is not where queryable data lives.<\/p>\n
Is the shape controlled by my application and stable? If yes, it’s still a column or a side table, not JSONB.<\/p>\n
Does the shape come from outside or genuinely vary per row? If yes, JSONB is the right tool. Add the indexes you need and treat the column as opaque to the rest of the schema.<\/p>\n
I keep getting better answers when I’m willing to add a column instead of stuffing one more key into JSONB. Schema migrations are cheap. Slow queries are not.<\/p>\n
What you can do this week<\/h2>\n
Open one of your apps with a JSONB column. Run this:<\/p>\n
SELECT\n  jsonb_object_keys(metadata) AS key,\n  count(*) AS rows\nFROM orders\nGROUP BY key\nORDER BY rows DESC\nLIMIT 20;\n<\/code><\/pre>\n
Any key that shows up in most rows and that you also query against is a column waiting to be pulled out. Promote it. You’ll feel the difference the next time you write a WHERE<\/code> clause.<\/p>\n
After that, look at your slowest query log for any plan that touches JSONB. If the plan does a sequential scan and filters with metadata->>'something' = '...'<\/code>, add an expression index. If the plan filters with @><\/code>, add a jsonb_path_ops<\/code> GIN index. Most of the time you don’t need both. Run EXPLAIN ANALYZE<\/code> before and after so you can see the win, not just hope for it. None of this is fancy. It’s the boring middle ground between treating JSONB as a magic schema-less escape hatch and refusing to use it at all, which is where I’ve ended up after a few years of getting both extremes wrong.<\/p>\n","protected":false},"excerpt":{"rendered":"
JSONB in Postgres is great in the right place and a slow tax everywhere else. Here’s the decision tree, indexing tips, and ORM patterns I use in 2026.<\/p>\n","protected":false},"author":2,"featured_media":204,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"JSONB in Postgres is great in the right place and a slow tax everywhere else. Here's the decision tree, indexing tips, and ORM patterns I use in 2026.","rank_math_focus_keyword":"postgres jsonb","rank_math_canonical_url":"","rank_math_robots":"","footnotes":""},"categories":[147,237],"tags":[49,240,242,239,177,238,241],"class_list":["post-205","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-backend","category-databases","tag-backend","tag-database-design","tag-indexes","tag-jsonb","tag-postgres","tag-postgresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/comments?post=205"}],"version-history":[{"count":0,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/205\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media\/204"}],"wp:attachment":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media?parent=205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/categories?post=205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/tags?post=205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}