Introduction: JSONB Operators and Indexing

Overview

If you have been working with PostgreSQL for more than a couple of years, you have seen this: a metadata JSONB column, someone storing "whatever comes up" in there — SEO tags, per-user configuration, third-party integration parameters, polymorphic product attributes. It works. Until it grows. And then any query with WHERE metadata->>'status' = 'active' starts taking seconds, someone shouts "JSONB is slow," and a ticket eventually appears to "migrate all of that to tables."

JSONB is not slow. JSONB without an index is. JSONB with the wrong index is too. And JSONB used for data that should be a table with a foreign key is an anti-pattern that no index saves.

This module teaches you to leave behind the mental model "JSONB is a column where I store JSON" and enter the mental model "JSONB is a JSON-native engine inside PostgreSQL with its own operators, its own indexes (GIN), its own planner-aware decision, and use cases where it beats traditional JOINs if you design it well." By the end you will have a mini-project: an events table of several million rows indexed with the right operator class, partial indexes on JSONB, expression indexes on hot fields, and an EXPLAIN validation that the planner uses what you designed — not that it falls back to a sequential scan in production when cardinality changes.

---

Where are we in the guide?

This is Module 1 of Advanced PostgreSQL for Backend Guide — guide #14 of the Backend Python Developer with FastAPI path and the last one of the Data Layer sub-track (May 2026).

The complete guide has 5 blocks:

Block 1: Deep JSONB (Modules 1-2)                   ← YOU ARE HERE
Block 2: Search in PostgreSQL (Module 3)
Block 3: Scale — Partitioning and MVs (Modules 4-5)
Block 4: Concurrency and Extensions (Modules 6-7)
Block 5: Recursive CTEs + Final Project (Module 8)

JSONB takes up two modules because it is the most differentiating and most misunderstood feature of the modern PostgreSQL stack. This module (01) covers JSONB in pure SQL: operators, JSON Path, GIN indexes, partial and expression indexes, anti-patterns. Module 02 translates all of it to idiomatic SQLAlchemy 2.0 and shows the 3 real use cases (dynamic config, metadata, polymorphic data).

Why SQL first and ORM second: if you learn JSONB through the ORM, you don't understand what you're asking the engine for. You will write Post.metadata["seo"]["title"].astext == "x" without knowing whether that uses the GIN index or not. After this module you will know, because you will understand which operators benefit from which index — before touching SQLAlchemy.

---

The fundamental principle: "JSONB without an index is a shot in the foot"

There is a sentence you will internalize this module: JSONB is a trade-off between schema flexibility and query cost. Every time you put a field in JSONB instead of a column, you gain flexibility (no migration to add fields) and you pay a cost (queries filtering on that field don't use a B-tree out of the box). The cost is offset by the right index. Without an index, the cost becomes catastrophic once the table grows.

The documented case we're going to use as the anchor for the whole module:

A team in production had an events table (50M rows) with a JSONB payload. The main query filtered WHERE payload @> '{"action": "purchase", "country": "US"}' and took 4 seconds with a default GIN. After applying three techniques you'll learn in this module (the jsonb_path_ops operator class, partial indexes filtered by the most common conditions, and date partitioning from module 4), the same query dropped to 12 milliseconds. 333 times faster. Without changing a single line of application code.

Source: dev.to/ohugonnot — Advanced PostgreSQL: JSONB, partial indexes and partitioning

The module project (capsule 08) replicates that mechanic at a smaller scale (5M rows) on your machine. You will start from a slow baseline, apply the techniques, and produce a before/after benchmark. That case is the living motivation of the module — everything we teach converges on being able to reproduce it.

---

Professional objective

By the end of this module you will be able to:

• Distinguish JSON vs JSONB and articulate why 99% of the time you want JSONB
• Use every JSONB operator with confidence: access (->, ->>, #>, #>>), search (@>, <@, ?, ?|, ?&), and manipulation (||, -, jsonb_set)
• Write JSON Path queries with @@, @?, jsonb_path_query (PostgreSQL 12+)
• Create GIN indexes and choose between jsonb_ops (default) and jsonb_path_ops with good judgment
• Design partial indexes on JSONB for your app's most common filters
• Design expression indexes on specific JSONB fields (((payload->>'user_id')))
• Validate with EXPLAIN ANALYZE that the planner uses the right index, not that it falls back to a sequential scan
• Recognize when well-indexed JSONB beats traditional JOINs (the 50M-row case as the anchor)
• Identify and avoid anti-patterns: using JSONB for relational data, over-indexing, queries that GIN cannot speed up

These are not "nice to have" skills — they are the difference between a dev who abuses JSONB because "it's flexible" and a senior dev who decides when to use it, how to index it, and why.

---

Why does this module matter?

You will be on the decision-maker's side of these conversations at least three times a year:

"We need to store custom metadata per client — should we put it in a metadata JSONB column?"

"This JSONB query takes 3 seconds in production — do we have to migrate to a separate table?"

"The product team wants field X to be filterable. Do I add it as a column or as a key in the JSONB we already have?"

Without this module, the answer to any of these is usually improvised — based on "what Stack Overflow said" or "what we did last time." With this module, you have a framework: is the data relational (does it need a JOIN)? Table. Is it semi-structured and does the schema change? JSONB. Does the main query filter by containment? jsonb_path_ops + partial index. Does it filter by a specific field used in JOINs? Expression index.

You will apply it:

• When you join a team and have to diagnose a slow JSONB query without touching the app code — just by adding indexes
• When you're designing a new schema and the question is "does this go in columns or in JSONB?"
• In senior interviews where they ask you "when would you use JSONB in PostgreSQL instead of MongoDB?" — and the expected answer is technical, not opinionated
• When a "everything to JSONB" or "JSONB to tables" migration is proposed and you need to argue with real benchmarks instead of generalities
• In code reviews where someone declares a JSONB column without thinking about how it will be indexed

The skill isn't JSONB — it's the decision of when and how to use JSONB, validated with EXPLAIN.

---

A scenario that illustrates the module

Imagine you join a backend team that maintains a SaaS analytics API. The events table has 5M rows (you'll hit 50M in 6 months at the current rate) with this shape:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

payload stores things like {"action": "purchase", "amount": 49.99, "country": "US", "metadata": {"campaign": "spring-sale", "referrer": "google"}}. Each SaaS client defines which keys they put in — the schema is flexible by design.

The first ticket assigned to you: "The /dashboards/conversions?country=US&action=purchase endpoint takes 4 seconds. Speed it up."

The wrong way to proceed (the one you'll learn to avoid):

1. You open the endpoint code, you see it runs SELECT count(*) FROM events WHERE payload->>'action' = 'purchase' AND payload->>'country' = 'US'.
2. You say "I'll add a GIN index to payload": CREATE INDEX ON events USING gin(payload).
3. You re-measure. The endpoint still takes 3.8 seconds. Almost nothing changed.
4. You conclude "JSONB is slow" and open a ticket to migrate to columns.

The right way (the one you'll be able to execute by the end of the module):

1. You rewrite the query to use the containment operator @> (which is the one GIN actually speeds up): SELECT count(*) FROM events WHERE payload @> '{"action": "purchase", "country": "US"}'.
2. You create the GIN index with the correct operator class for this case: CREATE INDEX ON events USING gin(payload jsonb_path_ops). It is faster and more compact than the default jsonb_ops when you only use @>.
3. If 95% of the dashboard queries filter on country = 'US', you add a partial index that indexes only those rows: CREATE INDEX ON events USING gin(payload jsonb_path_ops) WHERE payload @> '{"country": "US"}'. The index goes from covering 5M rows to covering, say, 1.2M.
4. You validate with EXPLAIN ANALYZE that the planner uses a Bitmap Heap Scan with your new index and doesn't fall back to a Seq Scan.
5. You re-measure. Query: 4s → 80ms. You report with numbers.

The rest of the module trains you in exactly that. Capsule 08 (the project) is a reproducible version of this scenario.

---

Module map

Capsule	Topic	What you'll learn
02	JSONB vs JSON vs TEXT	Internal differences (binary vs string), why you almost always want JSONB, when plain JSON still works, the TEXT-with-JSON anti-pattern
03	Core JSONB operators	Access (->, ->>, #>, #>>), search (@>, <@, ?, `?
04	JSONB Path queries	jsonb_path_query, @@, @?, JSON Path syntax, filters and predicates, when to use it instead of basic operators
05	GIN indexes on JSONB	How GIN works internally, jsonb_ops vs jsonb_path_ops (decision matrix), index size, which queries each one speeds up
06	Complex queries: filters and aggregations	Combining operators with WHERE, GROUP BY, JOIN, aggregations with jsonb_agg/jsonb_object_agg, when expression indexes are necessary
07	JSONB Anti-Patterns	When NOT to use JSONB (relational data, data that needs FKs), over-indexing, queries no GIN speeds up, payload bloat
08	Project: events table with 5M rows	Reproduce the 4s → 12ms case at a smaller scale: generate the dataset, slow baseline, apply the techniques, before/after benchmark

Learning flow: first you understand what JSONB is and why (02). Then you master the basic operators and path queries (03, 04) — this is the module's "syntax." Then comes the heart of the module: indexing (05) and real queries that need more than a bare GIN (06). You close with anti-patterns (07) — the filter that separates the senior from the junior is knowing when NOT to use the tool. And you finish with the project (08), which integrates everything into a reproducible benchmark.

---

Connection with the path's integrative project

The final project of the whole guide (module 8) is refactoring the Blog API built in guide #8 to incorporate every advanced PostgreSQL feature. One of the central pieces is moving post metadata to a metadata JSONB column (SEO tags, custom fields, integration data) with a GIN index.

The decisions you make in this module — which GIN operator class, which partial indexes to design, how to write queries that benefit from the index — are exactly the decisions you'll make in the final project. The events table of the module's mini-project is functionally analogous to the posts table with metadata JSONB from the refactor.

If you finish this module well, the JSONB component of the final project takes you 30 minutes of Alembic + a SQLAlchemy tweak. If you finish it weakly, you're going to get stuck there.

---

What is NOT covered in this module?

An explicit list, with reasons:

• ❌ JSONB with SQLAlchemy — Mapped[dict], func.jsonb_extract_path_text, JSONB.contains(), validation with Pydantic. All of that is module 02. Here we work in pure SQL on purpose (mental model first).
• ❌ Full-text search over JSONB fields — That goes in module 3 (FTS + pg_trgm). Here "search" means containment (@>, ?), not full-text.
• ❌ Partitioning tables with JSONB — Module 4 covers it as its main technique. In the module 1 project it is mentioned as a factor in the anchor case (4s → 12ms also required partitioning), but it isn't taught — it's referenced.
• ❌ Materialized views over JSONB aggregations — Module 5.
• ❌ EXPLAIN ANALYZE in depth — Covered in guide #12. Here we use it as a validation tool, not as a topic. We assume you can read a query plan (Bitmap Heap Scan vs Seq Scan, Index Scan vs Index Only Scan).
• ❌ N+1, OFFSET pagination, multitenancy with RLS, soft deletes — Covered in guides #12 and #13. They don't get mixed in here.
• ❌ pgvector for embeddings — It belongs to the AI Engineering path. PostgreSQL as a vector DB is an extensive domain that isn't covered in this guide. Decision documented in STRATEGY.md.

Golden rule of this module: pure SQL + EXPLAIN. Not a single line of Python. When you finish, you will open module 2 with a clear mental model of what you're asking PostgreSQL for from the ORM.

---

Traps to avoid while taking the module

1. "The operators are what matters, I'll rush through indexes."

The other way around. Operators are learned fast (capsule 03 covers them all). 80% of the module's value is in indexing — specifically in the jsonb_ops vs jsonb_path_ops decision, partial indexes, and expression indexes. That decision is the difference between 4 seconds and 12 milliseconds. Give capsules 05 and 06 the time they deserve.

2. "I'm going to skip capsule 02 because I already know JSONB is better than JSON."

Tempting, but capsule 02 isn't just "JSONB > JSON." It covers the mental model of when to use JSONB vs when to use relational columns — the most important decision of the module and the one nobody teaches explicitly. Skip it and you'll reach capsule 07 (anti-patterns) without the tools to understand why something is an anti-pattern.

3. "I'm going to index everything with GIN just in case."

GIN indexes are expensive to maintain (inserts and updates pay the cost) and large (sometimes they multiply the table size by 1.5x). Indexing "just in case" is a serious anti-pattern. The rule is: index what the main query filters on. For everything else, don't.

4. "EXPLAIN is for understanding the query, and I already understand it."

You will learn in this module that many times the index exists but the planner doesn't use it (stale statistics, bad cardinality, badly written query). EXPLAIN is the only way to validate that your index is useful. If you don't run EXPLAIN after creating each index, you aren't indexing — you're creating files on disk.

5. "JSON Path queries (capsule 04) are very niche, I'll skip them."

JSON Path is PostgreSQL 12+ and it solves queries that with basic operators are unreadable or impossible (filters over arrays, nested predicates). You won't use it every day, but when it shows up, it saves hours. And it shows up in senior interviews where "do you know jsonb_path_query?" filters out a lot of people.

6. "I'm going to try the project (08) without doing the previous capsules."

The project requires the 6 techniques taught in capsules 02-07. Without them, you'll reach the 4-second baseline and won't know what to change. Order matters.

---

Self-assessment question

Before starting this module, try to answer honestly:

1. What is the internal difference between JSON and JSONB in PostgreSQL? (Not "one is binary and the other isn't" — what does that imply for queries and storage?)
2. If you have a metadata JSONB column and you want to filter by metadata @> '{"status": "active"}', which index do you create? Why not a B-tree?
3. What is the difference between jsonb_ops and jsonb_path_ops? When would you use each one?
4. What does a partial index do and why is it useful in JSONB specifically?
5. Do you know jsonb_path_query? When is it preferable to chained ->>?
6. If a JSONB query takes 3 seconds and you added a GIN, how do you validate that the planner is using it?

If you hesitated on any of them, this module is for you. If you answered them all with confidence, read it anyway — you're going to find nuances of the 50M-row case that are only learned with scars.

---

Evidence of success

By the end of the module, you'll know you succeeded if:

• You can explain in an interview the jsonb_ops vs jsonb_path_ops decision with concrete examples of when to choose each one
• You have an events table of 5M rows with before/after benchmarks showing a 2+ order-of-magnitude improvement from applying partial indexes and the correct operator class
• When someone says "JSONB is slow," your first reaction is "what index does it have? what operator does the query use? did you run EXPLAIN?" — not "yeah, we have to migrate to tables"
• You recognize in code review when someone is using JSONB for relational data (anti-pattern)
• You can write a jsonb_path_query with filters without consulting the docs every time

---

How to get the most out of this module

Estimated time: 1.5-2 hours reading + 1.5-2 hours running the benchmarks in capsules 05, 06, and 08.

Minimum recommended setup:

• macOS or Linux (Windows on WSL2)
• PostgreSQL 16+ installed locally or in Docker (some JSON Path features require 12+, others like JSON_TABLE improve in 17+, but the whole module assumes 16+)
• The psql client
• ~3GB of disk space for the project dataset (5M rows + indexes)
• Familiarity with EXPLAIN ANALYZE (covered in guide #12)

You don't need Python or SQLAlchemy in this module. Everything is done in psql.

---

We start in the next capsule

Capsule 02 — JSONB vs JSON vs TEXT — is the most conceptual one of the module and it's where the mental model that will guide you through the next 6 capsules gets built. If after reading it you can't explain why JSONB parses eagerly, deduplicates keys, and supports indexes, read it again. It's the foundation.

Before moving on, make sure you can answer: why does PostgreSQL have two JSON types when MySQL/Oracle have only one?

If you don't have a clear answer, perfect — that's what the next capsule is for.

---

Summary

• JSONB is a JSON-native engine inside PostgreSQL, not "a column where I store JSON." It has its own operators, its own indexes (GIN), and cases where it beats traditional JOINs.
• Without an index, JSONB is slow. With the right index, JSONB competes with or beats well-designed relational tables for semi-structured data.
• 80% of the module's value is in the indexing decision (GIN operator class + partial indexes + expression indexes), not in the operators.
• The anchor case (50M rows, 4s → 12ms) proves that this module's techniques apply to real production, they aren't academic.
• In this module you work in pure SQL. SQLAlchemy arrives in module 2.
• The module's rule: EXPLAIN ANALYZE after every index. If you don't validate that the planner uses it, you aren't indexing.

---

Resources for the module

1. PostgreSQL 16 Documentation — JSON Functions and Operators — the official reference for JSONB operators and functions. Recommended reading for the whole module.
2. PostgreSQL 16 Documentation — JSON Types — json vs jsonb differences, restrictions, indexing. The foundation of capsule 02.
3. PostgreSQL 16 Documentation — GIN Indexes — how GIN works internally. Capsule 05.
4. dev.to/ohugonnot — Advanced PostgreSQL: JSONB, partial indexes and partitioning — the module's anchor case. 50M rows, 4s → 12ms. Read it before capsule 08.
5. Bruce Momjian — "Unlocking JSON" (slides) — presentations from the PostgreSQL core team about the JSON engine.
6. pganalyze — Lukas Fittl — "Understanding GIN indexes in PostgreSQL" — a deep technical analysis of GIN, fastupdate, maintenance. Reference material for capsule 05.
7. PostgreSQL Wiki — JSONB Indexing — the historical wiki with classic examples and design decisions.

---

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: JSONB vs JSON vs TEXT — the mental model that will guide you through the whole module.