Module 1: JSONB Operators and Indexing

Module project: replicating the 4s → 12ms case with your own 5M rows

What are you going to build and why?

Throughout the module I referenced a documented case: a team in production with an events table (50M rows) had a query that took 4 seconds, and by applying three techniques (the jsonb_path_ops operator class, partial indexes, date partitioning) they brought it down to 12 milliseconds. 333x. Without changing application code, just schema/indexes.

The module project is to reproduce that mechanic at a smaller scale on your machine. You're going to generate 5M synthetic rows (a reasonable proportion of the real case, manageable on any laptop), start from a slow baseline (Seq Scan), apply the module's techniques in measurable steps, and produce a BENCHMARKS.md that shows the improvement of each step. When you finish you'll have tangible evidence that the techniques work on your hardware, not just in theory — and a repo that is portfolio-worthy.

This project is the JSONB component of the guide's final project (module 8: Advanced Blog API). The decisions you make here — which GIN operator class, which partial indexes to design, how to write queries that benefit from them — are exactly the decisions you'll make in the Blog API refactor. If you finish it well, the JSONB component of the final project takes you 30 minutes. If you finish it weakly, you're going to get stuck.

Important note: this module is pure SQL. There's no Python, no SQLAlchemy. Everything is done in psql and bash. SQLAlchemy arrives in module 2, translating everything you learn here into the idiomatic ORM.

Project objective

Upon completing this project:

You'll have an events table with 5M rows and a realistic JSONB payload on your machine
You'll produce a BENCHMARKS.md with 5 measured steps (baseline → final), each with an EXPLAIN plan and timings
You'll demonstrate an improvement of at least 2 orders of magnitude (typically 3+) in the project's main query
You'll have versioned scripts (setup.sql, seed.sql, benchmarks.sh) that anyone can run to reproduce your numbers
You'll have made the module's 4 central technical decisions: GIN operator class, partial indexes, expression indexes, query rewriting

How it fits with what you learned

Capsule	Where it's applied in this project
02 — JSONB vs JSON vs TEXT	You decided the column is `JSONB` (not `JSON` or `TEXT`) for queryability + GIN
03 — Core operators	The project's queries use `@>` (search) and `->>` (access). You write both correctly
04 — JSON Path queries	One of the audit queries uses `jsonb_path_query` to report specific values
05 — GIN indexes	The heart of the project: you test `jsonb_ops` vs `jsonb_path_ops` with your own data. You design partial indexes
06 — Complex queries	One of the project's queries combines `@>` + JOIN + `ORDER BY` with keyset pagination
07 — Anti-patterns	You validate that the schema does NOT fall into the anti-patterns. The payload stays compact

Think of it as assembling a car with parts you learned to manufacture: each capsule gave you a part, this module puts them all together into something that works and gets measured.

Technical specifications

Stack

PostgreSQL: 16+ (some path query and partial index features assume 14+, but 16 is what's recommended)
Client: psql (it comes with PostgreSQL)
Shell: bash or zsh for the benchmark script
Required disk space: ~3 GB (table + indexes + transient WAL)
Minimum RAM: 8 GB recommended (it works with less but slower)
Total execution time: ~30-60 minutes (including the 5M-row seed, ~5-15 min depending on hardware)

Initial setup

# Create the project DB
createdb advanced_pg_module1

# Check the version
psql -d advanced_pg_module1 -c "SELECT version();"
# Expected: PostgreSQL 16.x

# Project folder
mkdir -p ~/advanced-pg-module1
cd ~/advanced-pg-module1

Required functionality

1. Schema and a 5M-row dataset

Create setup.sql:

-- setup.sql
DROP TABLE IF EXISTS events CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- Auxiliary users table (50k)
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  tier TEXT NOT NULL CHECK (tier IN ('free', 'pro', 'enterprise')),
  country TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Main table: events
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,  -- redundant with payload->>'user_id', on purpose (see explanation)
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

A note about the redundant user_id: we keep it as a real column because that's what we recommend in the module (capsule 07, anti-pattern 1). The payload->>'user_id' is also there, but the user_id column is for an efficient JOIN. It's the right decision.

Create seed.sql:

-- seed.sql
\timing on

-- Seed users (50k)
INSERT INTO users (name, email, tier, country)
SELECT
  'user_' || g,
  'user_' || g || '@example.com',
  (ARRAY['free', 'pro', 'enterprise'])[1 + (g % 3)],
  (ARRAY['US', 'MX', 'ES', 'AR', 'BR', 'CO', 'CL', 'PE', 'VE', 'EC'])[1 + (g % 10)]
FROM generate_series(1, 50000) g;

-- Seed events (5M)
-- Generate in batches so we don't exhaust memory
DO $$
DECLARE
  batch_size INT := 100000;
  i INT := 0;
BEGIN
  WHILE i < 50 LOOP  -- 50 batches × 100k = 5M
    INSERT INTO events (user_id, payload, created_at)
    SELECT
      uid,
      jsonb_build_object(
        'action', (ARRAY['view', 'click', 'purchase', 'signup', 'logout', 'share'])[1 + (random() * 5)::int],
        'user_id', uid,
        'amount', round((random() * 500)::numeric, 2),
        'country', (ARRAY['US', 'MX', 'ES', 'AR', 'BR', 'CO', 'CL', 'PE'])[1 + (random() * 7)::int],
        'currency', (ARRAY['USD', 'MXN', 'EUR', 'ARS', 'BRL'])[1 + (random() * 4)::int],
        'metadata', jsonb_build_object(
          'campaign', 'campaign-' || (random() * 100)::int,
          'referrer', (ARRAY['google', 'direct', 'twitter', 'facebook', 'email', 'organic'])[1 + (random() * 5)::int],
          'device', (ARRAY['desktop', 'mobile', 'tablet'])[1 + (random() * 2)::int],
          'session_id', md5(random()::text)
        )
      ),
      now() - (random() * interval '180 days')
    FROM (
      SELECT (random() * 49999 + 1)::bigint AS uid
      FROM generate_series(1, batch_size)
    ) sub;

    i := i + 1;
    RAISE NOTICE 'Batch % done', i;
  END LOOP;
END $$;

-- Final stats
ANALYZE users;
ANALYZE events;

SELECT
  pg_size_pretty(pg_total_relation_size('events')) AS events_size,
  pg_size_pretty(pg_total_relation_size('users')) AS users_size,
  COUNT(*) AS events_count
FROM events;

Run it:

psql -d advanced_pg_module1 -f setup.sql
psql -d advanced_pg_module1 -f seed.sql

Expected time: 5-15 minutes depending on hardware. You'll have an events table of ~1.5-2 GB with 5M rows.

Validation:

SELECT COUNT(*) FROM events;
-- 5000000

SELECT pg_size_pretty(pg_total_relation_size('events'));
-- ~1.5-2 GB

2. The project's main query

This is the query we're going to optimize step by step. It reflects a typical analytics dashboard case:

-- main_query.sql
SELECT count(*)
FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}'
  AND created_at >= now() - interval '30 days';

And its variant with a JOIN:

-- main_query_with_join.sql
SELECT
  u.name,
  u.tier,
  COUNT(*) AS purchases,
  SUM((e.payload->>'amount')::numeric) AS total_spent
FROM events e
JOIN users u ON u.id = e.user_id
WHERE e.payload @> '{"action": "purchase", "country": "US"}'
  AND e.created_at >= now() - interval '30 days'
GROUP BY u.name, u.tier
ORDER BY total_spent DESC
LIMIT 50;

3. A progressive benchmark in 5 steps

The project consists of measuring the main query in 5 states of the table and reporting the results:

Step 0 — Baseline (no indexes)

-- benchmark_step0.sql
\timing on
EXPLAIN (ANALYZE, BUFFERS, FORMAT text)
SELECT count(*)
FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}'
  AND created_at >= now() - interval '30 days';

Expectation: Seq Scan, 2-5 seconds.

Step 1 — A simple index over `created_at`

CREATE INDEX events_created_at_idx ON events (created_at);
ANALYZE events;

-- Re-run the query

Expectation: the time filter cuts the dataset, improving to 1-2 seconds.

Step 2 — GIN with `jsonb_ops` (default)

CREATE INDEX events_payload_default_idx ON events USING gin(payload);
ANALYZE events;

-- Re-run the query

Expectation: BitmapAnd between the two indexes, improving to 200-500 ms.

Step 3 — GIN with `jsonb_path_ops`

DROP INDEX events_payload_default_idx;
CREATE INDEX events_payload_pathops_idx ON events USING gin(payload jsonb_path_ops);
ANALYZE events;

-- Re-run the query

Expectation: 100-250 ms. The index is also ~50% smaller.

Step 4 — A partial index for purchases

DROP INDEX events_payload_pathops_idx;
CREATE INDEX events_purchase_idx ON events USING gin(payload jsonb_path_ops)
  WHERE payload @> '{"action": "purchase"}';

-- Keep the global one too for queries that aren't purchases
CREATE INDEX events_payload_pathops_idx ON events USING gin(payload jsonb_path_ops);

ANALYZE events;

-- Re-run the query

Expectation: 30-100 ms. The partial is much smaller than the global one.

Step 5 — A partial composite (recent purchases)

-- If the main query ALWAYS filters by recent purchases:
CREATE INDEX events_purchase_recent_idx ON events (created_at DESC)
  WHERE payload @> '{"action": "purchase"}';

-- And for the JOIN:
CREATE INDEX events_user_id_purchase_idx ON events (user_id)
  WHERE payload @> '{"action": "purchase"}';

ANALYZE events;

-- Re-run the main query AND the JOIN one

Expectation: main query 15-50 ms. JOIN query 50-150 ms.

4. An automated benchmark script

Create benchmark.sh:

#!/bin/bash
# benchmark.sh — runs the main query 5 times and reports the mean time

set -e

DB="advanced_pg_module1"
RUNS=5
QUERY="SELECT count(*) FROM events WHERE payload @> '{\"action\": \"purchase\", \"country\": \"US\"}' AND created_at >= now() - interval '30 days';"

echo "Running query $RUNS times..."
total=0
for i in $(seq 1 $RUNS); do
  # \timing on prints at the end of the query
  result=$(psql -d $DB -c "\timing on" -c "$QUERY" 2>&1 | grep "Time:" | awk '{print $2}')
  echo "Run $i: ${result} ms"
  total=$(echo "$total + $result" | bc)
done
avg=$(echo "scale=2; $total / $RUNS" | bc)
echo "---"
echo "Average: $avg ms"

And explain.sh to capture the plan:

#!/bin/bash
# explain.sh — captures the plan of the main query

DB="advanced_pg_module1"
QUERY="EXPLAIN (ANALYZE, BUFFERS, FORMAT text) SELECT count(*) FROM events WHERE payload @> '{\"action\": \"purchase\", \"country\": \"US\"}' AND created_at >= now() - interval '30 days';"

psql -d $DB -c "$QUERY"

Make them executable:

chmod +x benchmark.sh explain.sh

5. The report: `BENCHMARKS.md`

Create BENCHMARKS.md with the following structure:

# Benchmarks — JSONB Indexing on `events` (5M rows)

## Context

- **Hardware:** [your hardware: e.g. MacBook Pro M1, 16GB RAM]
- **PostgreSQL:** 16.2 (local installation via brew)
- **DB:** `advanced_pg_module1`
- **Table:** `events`, 5M rows, typical JSONB payload
- **Total space:** ~1.8 GB (table) + indexes depending on the step

## Main query

```sql
SELECT count(*)
FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}'
  AND created_at >= now() - interval '30 days';

Methodology

Each measurement: the average of 5 runs (discarding the first one for warmup)
EXPLAIN (ANALYZE, BUFFERS) to capture the plan
The table in a clean state (no concurrent queries) between measurements

Results

Step	Indexes	Plan	Mean time	Total index size
0	None on payload/created_at	Seq Scan	YOUR_NUMBERS ms	0 MB
1	+ `events_created_at_idx` (B-tree)	Index Scan + Filter	YOUR_NUMBERS ms	XX MB
2	+ `events_payload_default_idx` (GIN jsonb_ops)	BitmapAnd	YOUR_NUMBERS ms	XX MB
3	+ `events_payload_pathops_idx` (GIN jsonb_path_ops) instead of the default	BitmapAnd	YOUR_NUMBERS ms	XX MB
4	+ `events_purchase_idx` (partial GIN)	BitmapAnd with the partial	YOUR_NUMBERS ms	XX MB
5	+ partial composites for the JOIN	Partial Index Scan	YOUR_NUMBERS ms	XX MB

Observations per step

Step 0 → 1

[Your observation: how much it improved, why, what the plan was]

Step 1 → 2

[...]

Step 2 → 3

[...]

Step 3 → 4

[...]

Step 4 → 5

[...]

Total improvement

Baseline: XXXX ms Final state: YY ms Improvement factor: ZZ x

Comparison with the anchor case

The anchor case (50M rows, 4s → 12ms) shows an improvement of ~333x. My project at 5M rows demonstrates an improvement of XXx with the same techniques. The proportion is consistent — partial indexes + jsonb_path_ops + queries rewritten with @> apply at any scale.

Technical decisions

Operator class: I chose jsonb_path_ops because the hot path queries use only @>. I'd accept losing ? (not used).
Partial over purchase: because ~17% of the rows are purchases (1 of 6 actions). The partial is ~17% of the size of the global one, much faster.
user_id as a real column (not in JSONB): for an efficient JOIN with users. Without this, the JOIN pays for a cast on every row.
created_at as a real column: for the BitmapAnd with the JSONB partial.

Limitations / pending

I didn't test with date partitioning (module 4) — the anchor case also used this. That's left to combine with module 4.
5M rows doesn't stress the system as much as 50M. The absolute improvement is similar, but the relative improvement can grow even more at 50M.
I didn't measure the impact on write performance — the indexes pay on every insert. In a write-heavy workload this matters.

Reproduction

# 1. Create the DB
createdb advanced_pg_module1

# 2. Setup and seed (5-15 min)
psql -d advanced_pg_module1 -f setup.sql
psql -d advanced_pg_module1 -f seed.sql

# 3. Apply each step
psql -d advanced_pg_module1 -f step1.sql
./benchmark.sh

psql -d advanced_pg_module1 -f step2.sql
./benchmark.sh
# ... etc


### 6. Final file structure

Your repo / project folder should end up like this:

advanced-pg-module1/ ├── setup.sql # create the schema ├── seed.sql # generate 5M rows ├── step1.sql # each optimization step ├── step2.sql ├── step3.sql ├── step4.sql ├── step5.sql ├── benchmark.sh # measurement script ├── explain.sh # script to capture the plan ├── BENCHMARKS.md # your report └── README.md # how to reproduce


---

## Validations and error handling

### Validations your project must pass

- [ ] The `events` table has exactly 5M rows (`SELECT COUNT(*) = 5000000`)
- [ ] The `users` table has 50k rows
- [ ] The JSONB payload has the keys: `action`, `user_id`, `amount`, `country`, `currency`, `metadata`
- [ ] `metadata` is a nested JSONB object with `campaign`, `referrer`, `device`, `session_id`
- [ ] The main query returns a number (it varies depending on the random data; typically 50,000-200,000 matching rows)
- [ ] The EXPLAIN of step 5 shows an Index Scan / Bitmap Index Scan (NOT a Seq Scan)
- [ ] The improvement from step 0 to step 5 is at least **20x** (ideally 50-100x)

### Errors that can show up and how to resolve them

**"Out of disk space" during the seed:**
- Free up ~3 GB on your main disk before starting.
- If you just want to test the techniques without the full dataset, drop down to 1M rows (change `WHILE i < 50` to `WHILE i < 10`). The improvement proportions hold.

**"Out of memory" during CREATE INDEX:**
- `SET maintenance_work_mem = '512MB';` before creating the GIN.
- If you have < 8GB RAM, drop the indexes before creating the new ones so you don't have several under construction at once.

**Query timeout in step 0:**
- It's expected that the baseline is slow. If it takes > 30s, try `SET statement_timeout = 0;` to disable the timeout and let it run.

**An unexpected plan in step 4 (it doesn't use the partial):**
- The planner may choose the global one if it considers it more selective. Validate with `EXPLAIN` and if you wanted to force it to compare: `DROP INDEX events_payload_pathops_idx;` temporarily.

**Very variable times between runs:**
- Warm vs cold cache. Do `\set ECHO_HIDDEN on` and discard the first measurement. Or restart PostgreSQL between each step to have a comparable cold cache.

---

## Evaluation rubric (self-check)

Total: 100 points. Pass: ≥70.

### Setup and dataset (20 pts)

- [ ] (5 pts) Correct schema: an `events` table with `id BIGSERIAL`, `user_id BIGINT`, `payload JSONB NOT NULL`, `created_at TIMESTAMPTZ`
- [ ] (5 pts) An auxiliary `users` table with 50k rows (for the JOIN)
- [ ] (5 pts) 5M rows in `events` with a realistic payload (all the keys, varied values)
- [ ] (5 pts) `setup.sql` and `seed.sql` versioned, idempotent (they run without error on a fresh DB)

### Benchmarking (35 pts)

- [ ] (10 pts) Measurements of the 5 steps complete, each with a mean time over 5 runs
- [ ] (10 pts) An EXPLAIN plan captured for each step (not just the time)
- [ ] (10 pts) The size of each index recorded
- [ ] (5 pts) A total improvement of at least **20x** from step 0 to step 5

### The BENCHMARKS.md report (25 pts)

- [ ] (5 pts) Context documented (hardware, PostgreSQL version, table size)
- [ ] (5 pts) A table with the results per step (every field of the table)
- [ ] (5 pts) Observations per step (not just numbers — interpretation)
- [ ] (5 pts) A comparison with the module's anchor case
- [ ] (5 pts) A "technical decisions" section justifying each choice

### Technical decisions (15 pts)

- [ ] (5 pts) A clear justification of `jsonb_path_ops` vs `jsonb_ops` for your workload
- [ ] (5 pts) A design of partial indexes with reasoning (what % of rows it covers, why)
- [ ] (5 pts) `user_id` as a real column (not just in JSONB) with an explanation

### Reproduction (5 pts)

- [ ] (5 pts) A `README.md` with the steps to reproduce from scratch (createdb → setup → seed → benchmarks → report)

### Extra credit (optional, up to +15 pts)

- [ ] (+5 pts) Also measure the impact on INSERT performance (how much the insert throughput dropped with each index)
- [ ] (+5 pts) Test the main query with variant queries (another country, another action) and validate that the partial is still useful
- [ ] (+5 pts) Document the EXPLAIN plan in a visual format (a diagram or ASCII art) for the key steps

---

## Common mistakes in this project

### Mistake 1: the seed takes too long

**Symptom:** generating 5M rows takes 30+ minutes.

**Why it happens:** inserting row by row is slow; the planner / disk IO becomes the bottleneck.

**How to fix it:** make sure you use the `INSERT INTO ... SELECT ... FROM generate_series(...)` pattern in large batches (100k per batch). Avoid `INSERT INTO ... VALUES (...)` × 5M.

### Mistake 2: the benchmarks give very variable numbers

**Symptom:** the first run is 800 ms, the second 80 ms, the third 90 ms.

**Why it happens:** the first one loads pages into memory (cold cache). The following ones are hot.

**How to fix it:** discard the first measurement of each series. Report the average of the next 4. If you want cold times, restart PostgreSQL between each step (`brew services restart postgresql@16`).

### Mistake 3: the partial index isn't used when you expected it to be

**Symptom:** you create `events_purchase_idx` with `WHERE payload @> '{"action": "purchase"}'` but EXPLAIN doesn't choose it.

**Why it happens:**
- ANALYZE didn't run → stale statistics.
- The planner considers the global one more efficient for this particular case.
- The query doesn't include the partial's literal predicate.

**How to fix it:** `ANALYZE events;` after creating each index. If the planner keeps choosing another one, try `SET enable_seqscan = OFF;` and `SET enable_bitmapscan = ON;` to diagnose. If forcing it still doesn't use the partial, check that the query's `WHERE` includes the partial's predicate.

### Mistake 4: the step 5 JOIN is slow even though you have the index on `user_id`

**Symptom:** the query with the JOIN takes more than 200 ms even though you have `events_user_id_purchase_idx`.

**Why it happens:** the plan may choose a Hash Join loading all of users into memory. For 50k users that's OK, for more users it would be expensive.

**How to verify:** EXPLAIN should show `Index Scan using events_user_id_purchase_idx on events` or a `Nested Loop` if the filtered dataset is small. If you see a Hash Join without an Index Scan on events, the planner chose another strategy — valid if the datasets are small.

### Mistake 5: the report focuses on numbers but not on explanation

**Symptom:** the `BENCHMARKS.md` has a table of numbers and nothing else.

**Why it's problematic:** the value of the project isn't "I produced numbers" — it's **demonstrating understanding**. Without observations and interpretation, the numbers don't prove you understood why they happened.

**How to fix it:** each step needs 2-3 sentences of "what changed and why." The EXPLAIN plan tells you what happened internally — interpret it. If you can't explain why a change gave a 5x improvement, you didn't understand it — reread the corresponding capsule.

### Mistake 6: forgetting to drop obsolete indexes

**Symptom:** in step 5 you have all the created indexes accumulated, including the ones that are no longer used (e.g. `events_payload_default_idx` from step 2).

**Why it's problematic:** accumulated indexes pay on inserts without being used. It's exactly anti-pattern 3 from capsule 07.

**How to fix it:** each step should end up with ONLY the indexes that contribute. When you replace `events_payload_default_idx` with `events_payload_pathops_idx`, drop the first one. Document which indexes exist at the end of step 5 (there shouldn't be more than 4-5).

---

## What to do if you get stuck

- **If the seed doesn't finish:** check `top` or `htop` — is the CPU or the disk saturated? If it's the disk, consider reducing the dataset to 1M and validating the techniques at a smaller scale.
- **If EXPLAIN shows a Seq Scan when you expected an Index:** first `ANALYZE events;`. Then validate with `\d+ events` that the index exists. Then try `SET enable_seqscan = OFF;` to force it and compare.
- **If the times don't improve as you expected:** post your `EXPLAIN ANALYZE` in the bootcamp channel. The plan usually reveals the problem (statistics, cardinality, wrong operator).
- **If the seed code fails on an old version of PostgreSQL:** make sure you're on 14+ (ideally 16+). The `DO $$ ... $$;` with `RAISE NOTICE` requires PL/pgSQL enabled (the default in any installation).

---

## Resources for the project

1. [PostgreSQL 16 Documentation — JSONB Indexing](https://www.postgresql.org/docs/16/datatype-json.html#JSON-INDEXING) — the central reference during the project.
2. [PostgreSQL 16 Documentation — `EXPLAIN`](https://www.postgresql.org/docs/16/sql-explain.html) — to interpret plans.
3. [dev.to/ohugonnot — Advanced PostgreSQL: JSONB, partial indexes and partitioning](https://dev.to/ohugonnot/advanced-postgresql-jsonb-partial-indexes-and-partitioning-24em) — the module's anchor case. Reread it before assembling your report.
4. [pganalyze — "Understanding GIN Indexes"](https://pganalyze.com/blog/gin-index) — to go deeper into the index's behavior if your plan doesn't look the way you expected.
5. [Use the Index, Luke! — `EXPLAIN`](https://use-the-index-luke.com/sql/explain-plan) — the classic reference for reading plans.
6. [PostgreSQL Wiki — Performance Tips](https://wiki.postgresql.org/wiki/Performance_Optimization) — a general checklist that helps when diagnosing.

---

## What comes next

What you built here gets reused in module 2 when we put SQLAlchemy on top of it. The `events` table with its indexes is going to be exactly what you query from the ORM, validating that the pure SQL techniques translate to `Mapped[dict]`, `func.jsonb_extract_path_text()`, and `JSONB.contains()` without losing performance.

And every technical decision you made in this project (operator class, partial indexes, expression indexes for the JOIN) is **identical** to the decisions you'll make in module 8 (the final project: the Blog API refactor). When you get there and have to decide "which index on `posts.metadata JSONB`?", the answer will be immediate: you applied the same logic here with your own data.

Before moving on to module 2, make sure your project meets ≥70 points of the rubric. A total improvement of at least 20x, a plan validated with EXPLAIN at each step, technical decisions justified. If you're still in the "yes it improved but I don't understand why" range, reread capsule 05 — something in the GIN mental model probably didn't settle.

---

## Module summary

You made it here having internalized:

- **JSONB is a JSON-native engine** inside PostgreSQL, with its own operators, its own indexes, and cases where it beats relational tables.
- **80% of JSONB's value in production** is in the indexing decision — operator class, partials, expression indexes — not in the operators.
- **`jsonb_path_ops`** wins over `jsonb_ops` when you only use `@>`, which is the majority of cases.
- **Partial indexes** are the difference between 4 seconds and 12 ms at scale. They're the technique that sets the senior apart.
- **Anti-patterns** are as important as patterns. Knowing when NOT to use JSONB saves you years of technical debt.
- **EXPLAIN ANALYZE is not optional.** Every index is validated or it doesn't exist.

Next module: translating all of this to idiomatic SQLAlchemy 2.0. The same power, in Python code.

---

*Module 1 — Advanced PostgreSQL for Backend Guide*

**Next module:** JSONB with SQLAlchemy + Patterns — idiomatic Python code for everything you learned in pure SQL.

← PreviousJSONB Anti-Patterns: when NOT to use JSONB and how to spot it Next →Module 2: JSONB with SQLAlchemy and usage patterns

Module project: replicating the 4s → 12ms case with your own 5M rows

What are you going to build and why?

Project objective

How it fits with what you learned

Technical specifications

Stack

Initial setup

Required functionality

1. Schema and a 5M-row dataset

2. The project's main query

3. A progressive benchmark in 5 steps

Step 0 — Baseline (no indexes)

Step 1 — A simple index over created_at

Step 2 — GIN with jsonb_ops (default)

Step 3 — GIN with jsonb_path_ops

Step 4 — A partial index for purchases

Step 5 — A partial composite (recent purchases)

4. An automated benchmark script

5. The report: BENCHMARKS.md

Methodology

Results

Observations per step

Step 0 → 1

Step 1 → 2

Step 2 → 3

Step 3 → 4

Step 4 → 5

Total improvement

Comparison with the anchor case

Technical decisions

Limitations / pending

Reproduction

Step 1 — A simple index over `created_at`

Step 2 — GIN with `jsonb_ops` (default)

Step 3 — GIN with `jsonb_path_ops`

5. The report: `BENCHMARKS.md`