Module project: Indexing the Bookstore

What are you going to build and why?

The moment to apply the whole module in an integrative case has arrived. You'll receive a simplified schema of the Bookstore API (the same database that will be used in the final project of module 8) with representative data planted. On top of that schema, you'll face five problematic queries — each reflects a typical indexing anti-pattern you've seen in this module:

1. A query with multiple filters without a composite.
2. A query that returns lists and projects few columns — a case for INCLUDE.
3. A query with a conditional filter over a skewed subset — a case for a partial.
4. A query with a wrapping function — a case for an expression index.
5. A query with sorting over a selective filter — a case for a composite with an order.

For each query you'll:

1. Reproduce the problem: capture the initial plan with EXPLAIN (ANALYZE, BUFFERS). Document Execution Time and Buffers.
2. Diagnose: explain which node is the bottleneck and what type of index the query needs.
3. Design the index: create the appropriate composite/covering/partial/expression, justifying the column order and choices.
4. Validate: capture the plan afterward. Confirm the planner uses the new index. Measure the improvement.
5. Document: record everything in an INDICES.md file that's delivered as the module's artifact.

By the end, you'll have:

• A local database with five new, validated indexes.
• An INDICES.md with before/after plans and justifications — the deliverable.
• The direct practice you need for the final project of module 8 (where you'll do this + N+1 + pool tuning).

---

Project objective

By completing this project you'll be able to:

• Diagnose a slow query by capturing its plan and reasoning about which index is missing.
• Design the right type of index (composite/covering/partial/expression) based on the query's pattern.
• Always validate with EXPLAIN (ANALYZE, BUFFERS) before and after.
• Document indexing decisions with quantitative evidence.

---

How it fits with what you learned

Module capsule	Where it's used in the project
02: B-tree fundamentals	Reasoning about selectivity before proposing an index
03: Composite indexes	Queries 1 and 5 (multi-filter, multi-filter + order)
04: Covering indexes with INCLUDE	Query 2 (list with a limited projection)
05: Partial indexes	Query 3 (filter over a skewed subset)
06: Expression indexes	Query 4 (function in the WHERE)
07: Maintenance	Post-creation validation with pg_stat_user_indexes

Think of the project as a mini-Bookstore that already has the structure of the final project, but simplified so you can iterate quickly in this module. The techniques you apply here will replicate at a larger scale in module 8.

---

Stack and setup

Requirements

• PostgreSQL 16+ running locally (Docker or a native install).
• SQL client: psql or the client you prefer (DBeaver, TablePlus, etc.).
• Disk space: ~500 MB (for data + indexes).

Database setup

Create a new database for the project:

createdb bookstore_module03
psql bookstore_module03

Schema and seed

Run this complete script in psql (it's the base setup — don't modify it):

-- ============================================================
-- Bookstore — simplified schema for Module 3
-- ============================================================

DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS users;

CREATE TABLE authors (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    country     TEXT NOT NULL
);

CREATE TABLE books (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,
    author_id       INTEGER NOT NULL REFERENCES authors(id),
    price           NUMERIC(10, 2) NOT NULL,
    pages           INTEGER NOT NULL,
    isbn            TEXT NOT NULL,
    published_year  INTEGER NOT NULL,
    in_stock        BOOLEAN NOT NULL DEFAULT TRUE,
    description     TEXT
);

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    book_id     INTEGER NOT NULL REFERENCES books(id),
    status      TEXT NOT NULL,
    total       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE reviews (
    id          BIGSERIAL PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(id),
    user_id     INTEGER NOT NULL REFERENCES users(id),
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
);

-- ============================================================
-- Synthetic data
-- ============================================================

-- 1,000 authors
INSERT INTO authors (name, country)
SELECT
    'Author ' || g,
    (ARRAY['AR', 'MX', 'ES', 'CO', 'PE', 'CL', 'VE', 'US', 'UK', 'FR'])[1 + (g % 10)]
FROM generate_series(1, 1000) g;

-- 200,000 books
INSERT INTO books (title, author_id, price, pages, isbn, published_year, in_stock, description)
SELECT
    'Book ' || g,
    (random() * 999)::INTEGER + 1,
    (random() * 80 + 5)::NUMERIC(10, 2),
    (random() * 700 + 50)::INTEGER,
    'ISBN-' || lpad(g::TEXT, 13, '0'),
    1900 + (random() * 125)::INTEGER,
    random() < 0.7,                         -- 70% in_stock
    repeat('Lorem ipsum dolor sit amet. ', 5)
FROM generate_series(1, 200000) g;

-- 100,000 users (90% active, 10% soft-deleted)
INSERT INTO users (email, name, created_at, deleted_at)
SELECT
    -- Mix of capitalizations (real case)
    CASE (random() * 3)::INTEGER
        WHEN 0 THEN 'user' || g || '@example.com'
        WHEN 1 THEN 'User' || g || '@Example.com'
        ELSE 'USER' || g || '@EXAMPLE.COM'
    END,
    'User ' || g,
    NOW() - (random() * INTERVAL '1095 days'),
    CASE WHEN random() < 0.10
         THEN NOW() - (random() * INTERVAL '180 days')
         ELSE NULL
    END
FROM generate_series(1, 100000) g;

-- 1,000,000 orders (95% completed, 4% pending, 1% cancelled)
INSERT INTO orders (user_id, book_id, status, total, created_at)
SELECT
    (random() * 99999)::INTEGER + 1,
    (random() * 199999)::INTEGER + 1,
    CASE
        WHEN random() < 0.95 THEN 'completed'
        WHEN random() < 0.99 THEN 'pending'
        ELSE 'cancelled'
    END,
    (random() * 200 + 10)::NUMERIC(10, 2),
    NOW() - (random() * INTERVAL '730 days')
FROM generate_series(1, 1000000);

-- 500,000 reviews (5% soft-deleted)
INSERT INTO reviews (book_id, user_id, rating, body, created_at, deleted_at)
SELECT
    (random() * 199999)::INTEGER + 1,
    (random() * 99999)::INTEGER + 1,
    (random() * 4)::INTEGER + 1,
    'Review body ' || g || '. Lorem ipsum dolor sit amet.',
    NOW() - (random() * INTERVAL '730 days'),
    CASE WHEN random() < 0.05
         THEN NOW() - (random() * INTERVAL '90 days')
         ELSE NULL
    END
FROM generate_series(1, 500000) g;

-- Refresh statistics
ANALYZE;

If all goes well, you'll have:

• 1,000 authors
• 200,000 books
• 100,000 users (90,000 active, 10,000 deleted)
• 1,000,000 orders (~950k completed, ~40k pending, ~10k cancelled)
• 500,000 reviews (~475k active, ~25k deleted)

Verify with:

SELECT 'authors' AS table_name, COUNT(*) AS rows FROM authors
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

---

The five problematic queries

Each query represents a real API endpoint. For each one, the initial setup is without additional indexes (only PKs and implicit FKs). Your job: add the right index.

Query 1 — A user's pending orders

Endpoint: GET /users/{user_id}/orders/pending

SQL query:

SELECT id, book_id, total, created_at
FROM orders
WHERE user_id = 4242
  AND status = 'pending';

Expected behavior:

• Returns a specific user's pending orders.
• Typically between 1 and 5 rows.
• Frequency: very high (every user queries it when loading their profile).

Your task:

1. Capture the initial plan.
2. Diagnose.
3. Design the index.
4. Capture the plan afterward and compare.
5. Document in INDICES.md.

Query 2 — Book catalog by author

Endpoint: GET /authors/{author_id}/books

SQL query:

SELECT id, title, price
FROM books
WHERE author_id = 42;

Expected behavior:

• Returns the title and price of an author's books.
• Typically 50-300 rows.
• Frequency: very high (author page, catalog listings).
• Important: the API only returns id, title, price — it doesn't need description or other heavy columns.

Your task: the same, but keep in mind that the limited list of columns enables a specific technique.

Query 3 — A book's active reviews

Endpoint: GET /books/{book_id}/reviews

SQL query:

SELECT id, user_id, rating, body, created_at
FROM reviews
WHERE book_id = 1234
  AND deleted_at IS NULL
ORDER BY created_at DESC;

Expected behavior:

• Returns a book's active reviews, most recent first.
• Typically 5-50 rows (depends on the book).
• Frequency: high (each book view loads its reviews).
• Important: most reviews are active (95%), but the condition deleted_at IS NULL is always applied.

Your task: decide whether a partial is worth it or not, justifying with real numbers from the dataset.

Query 4 — Case-insensitive login

Endpoint: POST /auth/login

SQL query:

SELECT id, name FROM users WHERE lower(email) = lower($1);

Where $1 is the email the user enters when logging in.

Expected behavior:

• Login: the user types JUAN@example.com or juan@example.com, the system must find the account regardless of capitalization.
• Returns 0 or 1 rows.
• Frequency: extremely high (every login).

Your task: the traditional index on email won't be used — you'll diagnose why and create the appropriate index.

Query 5 — A user's recent orders, sorted

Endpoint: GET /users/{user_id}/orders/recent

SQL query:

SELECT id, book_id, status, total, created_at
FROM orders
WHERE user_id = 4242
ORDER BY created_at DESC
LIMIT 20;

Expected behavior:

• Returns a user's 20 most recent orders.
• Used in the user's dashboard.
• Frequency: very high.

Your task: consider not only the filter but also the ORDER BY ... LIMIT. Can you avoid a separate Sort?

---

Mandatory validations

For each query, your plan after creating the index must meet:

• ✅ It uses the new index (the plan mentions the index's name).
• ✅ It improves Execution Time by at least 5x vs the initial plan.
• ✅ It reduces Buffers significantly (at least 3x).
• ✅ It doesn't introduce a separate Sort in queries with ORDER BY (when applicable — query 5).
• ✅ For query 2: the plan shows Index Only Scan with Heap Fetches: 0.
• ✅ For query 4: the plan shows Index Scan (not Seq Scan).

If any validation isn't met, review your design. Re-read the corresponding capsule.

---

Error handling and edge cases

Things that can happen and how to resolve them:

• Query 2: the plan afterward is Index Scan, not Index Only Scan. Probably a dirty visibility map. Run VACUUM books; and recapture.
• Query 4: the index is created but the plan still shows Seq Scan. You probably forgot the lower() in the creation. Review capsule 06.
• Query 5: the plan afterward includes Sort. The column order in the composite probably doesn't match the ORDER BY. Review capsule 03.
• Creating the index takes a long time (>30s) on your machine. Normal with 1M rows. Wait. In production you'd use CONCURRENTLY.
• The improvements are less than 5x. Possible causes: the table is too small for the index to win, the data is too uniform, another cause. Capture both plans and document the case to discuss it.

---

Minimal implementation example: how to approach Query 1

So you see the complete flow, I'll show you only the Query 1 case. The remaining four are your work.

Step 1: capture the initial plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, book_id, total, created_at
FROM orders
WHERE user_id = 4242
  AND status = 'pending';

Expected output (without custom indexes):

Seq Scan on orders  (cost=0.00..23456.78 rows=200 width=44)
                    (actual time=0.123..245.456 rows=2 loops=1)
  Filter: ((user_id = 4242) AND (status = 'pending'::text))
  Rows Removed by Filter: 999998
  Buffers: shared hit=12345 read=890
Planning Time: 0.234 ms
Execution Time: 248.123 ms

Seq Scan over 1M rows to return 2. Reads 13k buffers. 248ms.

Step 2: diagnose

• There are two equality filters: user_id = 4242 and status = 'pending'.
• user_id: high cardinality (~100,000 values), user_id = 4242 covers ~10 rows.
• status: low cardinality (3 values), status = 'pending' covers ~40,000 rows (4%).
• The right composite index: equality + equality. The most selective column first (user_id).

Step 3: design the index

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

ANALYZE orders;

Justified decision:

• user_id first because it's more selective (~10 rows per value vs ~40k for status='pending').
• The composite covers exactly the two filters.
• No INCLUDE: the endpoint returns id, book_id, total, created_at — that list is long and book_id, total, created_at change frequently (orders is write-heavy). The INCLUDE doesn't pay off.

Step 4: validate the plan afterward

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, book_id, total, created_at
FROM orders
WHERE user_id = 4242
  AND status = 'pending';

Expected output:

Index Scan using idx_orders_user_status on orders
  Index Cond: ((user_id = 4242) AND (status = 'pending'::text))
  Buffers: shared hit=4
Planning Time: 0.345 ms
Execution Time: 0.234 ms

Improvements:

Metric	Before	After	Improvement
Plan	Seq Scan	Index Scan	—
Buffers	13,235	4	3,300x
Execution Time	248ms	0.23ms	1,000x

Validations: uses the index ✅, improves >5x ✅, buffers reduced >3x ✅.

Step 5: document in INDICES.md

## Query 1: A user's pending orders

**Endpoint:** GET /users/{id}/orders/pending

**Query:**
```sql
SELECT id, book_id, total, created_at
FROM orders
WHERE user_id = 4242 AND status = 'pending';

Plan BEFORE (without custom indexes):

Seq Scan on orders
  Filter: ((user_id = 4242) AND (status = 'pending'::text))
  Rows Removed by Filter: 999998
  Buffers: shared hit=12345 read=890
Execution Time: 248.123 ms

Diagnosis: A sequential scan over 1M rows to return 2. Missing a composite that covers both filters.

Selectivity analysis:

• user_id (cardinality ~100k): user_id=4242 covers ~10 rows
• status (cardinality 3): status='pending' covers ~40k rows (4%)
• user_id is ~4000x more selective than status for this query

Index created:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Justification:

• Composite with user_id first (more selective).
• No INCLUDE: the returned columns are many and the table is write-heavy.
• No partial: user_id varies a lot, there's no skewed subset.

Plan AFTER:

Index Scan using idx_orders_user_status on orders
  Index Cond: ((user_id = 4242) AND (status = 'pending'::text))
  Buffers: shared hit=4
Execution Time: 0.234 ms

Improvements:

Metric	Before	After	Improvement
Buffers	13,235	4	3,300x
Time	248ms	0.23ms	1,000x

Validations:

• ✅ Uses the new index
• ✅ Improves >5x (1,000x)
• ✅ Buffers reduced >3x (3,300x)

Now apply the same flow to queries 2-5.

---

## Evaluation rubric (self-verification)

Total: 100 points. Passing: ≥70.

### Correct index design (40 points)

- [ ] (8 pts) Query 1: composite `(user_id, status)` or equivalent, justified by selectivity.
- [ ] (8 pts) Query 2: index with INCLUDE for an `Index Only Scan`. Validated with `Heap Fetches: 0`.
- [ ] (8 pts) Query 3: composite with a partial `WHERE deleted_at IS NULL` and a suitable order for `created_at DESC`.
- [ ] (8 pts) Query 4: expression index on `lower(email)`, validated with `Index Scan`.
- [ ] (8 pts) Query 5: composite with `(user_id, created_at DESC)`, no separate `Sort` in the plan.

### Validation with EXPLAIN (25 points)

- [ ] (5 pts × 5 queries) Plan before and after captured for each query, showing `Index Cond`, `Buffers`, `Execution Time`.

### Quantifiable improvement (15 points)

- [ ] (3 pts × 5 queries) Improvement ≥5x in Execution Time documented per query.

### Documentation in INDICES.md (15 points)

- [ ] (3 pts × 5 queries) Each query with: the SQL query, plan before, diagnosis, index created + justification, plan after, improvements table.

### Critical analysis (5 points)

- [ ] (5 pts) A final section in `INDICES.md` that answers: "If I had 100 more candidate indexes, how would I decide which are worth it? Reflect on the reads vs writes trade-off and how it applies to this dataset."

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

- [ ] (+3 pts) For Query 3: compare partial vs non-partial with a disk-size benchmark.
- [ ] (+3 pts) For Query 5: compare the plan with `(user_id, created_at)` vs `(user_id, created_at DESC)` and explain the difference.
- [ ] (+4 pts) A "Next steps" section in INDICES.md identifying the next 2-3 indexing candidates in the schema (queries that aren't in the project but that you'd see as problematic if they were endpoints).

---

## Common mistakes in this project

### Mistake 1: forgetting `ANALYZE` after creating an index

**Symptom:** the plan afterward doesn't use the new index, it still shows `Seq Scan`.

**Why it happens:** PostgreSQL may not update the planner's statistics immediately. `ANALYZE table;` forces the update.

**How to fix it:** after each `CREATE INDEX`, run `ANALYZE table;` and recapture the plan.

### Mistake 2: Query 2 shows `Index Scan`, not `Index Only Scan`

**Symptom:** you create the INCLUDE correctly but the plan says `Index Scan` with `Heap Fetches > 0`.

**Why it happens:** a stale visibility map after the seed's inserts.

**How to fix it:** `VACUUM books;` before capturing the plan afterward.

### Mistake 3: Query 5 shows `Sort` even though the composite includes `created_at`

**Symptom:** the plan has `Index Scan` + a separate `Sort`.

**Why it happens:** possibly the column order doesn't fit, or you created `(created_at, user_id)` by mistake.

**How to fix it:** verify that the composite is `(user_id, created_at DESC)`. The order of the column after the equality must match the `ORDER BY`.

### Mistake 4: documenting a "100x" improvement without accounting for variability

**Symptom:** you run the query once cold and once warm. You report 100x.

**Why it's problematic:** the first execution pays for a cold cache; the following ones are in cache. The difference exaggerates the benefit.

**How to fix it:** run the query 3-5 times, discard the first, report the median. Or use the module 1 pattern (warmup + runs).

### Mistake 5: creating the partial with a complex condition that doesn't match real queries

**Symptom:** Query 3 is still slow even though you created a partial.

**Why it happens:** the partial's `WHERE` doesn't match the query. For example, you created `WHERE deleted_at IS NULL AND rating >= 4` but the query only filters `WHERE deleted_at IS NULL`.

**How to fix it:** the partial's `WHERE` must be **as simple as possible** and compatible with the real queries. For Query 3, only `WHERE deleted_at IS NULL`.

### Mistake 6: using `SELECT *` to "see the result better"

**Symptom:** you modify the original query to use `SELECT *` while testing, now the plan doesn't use an Index Only Scan.

**Why it happens:** `SELECT *` requires all the columns; the INCLUDE doesn't cover them all. The planner falls back to an `Index Scan` with a heap visit.

**How to fix it:** validate with the exact query from the statement. The project's queries specify concrete columns — respect them.

---

## What to do if you get stuck?

Simple diagnosis:

- **If the plan doesn't change after creating the index** → check that the query's `WHERE` matches what the index covers. For a partial, verify the exact predicate matching.
- **If creating the index fails** → read the error. If it says "must be marked IMMUTABLE", review capsule 06. If it says something about types, verify the column exists with the right name.
- **If the `Heap Fetches` doesn't drop to 0** → run `VACUUM table;` and capture the plan again.
- **If the improvement is <5x** → consider whether your dataset is large enough for the index to win. If the table has <10k rows, `Seq Scan` may win anyway.

Quick resources:

- Capsule 02 → B-tree fundamentals, when the planner ignores an index.
- Capsule 03 → composite, column order.
- Capsule 04 → INCLUDE, visibility map.
- Capsule 05 → partial, predicate matching.
- Capsule 06 → expression, IMMUTABLE.
- Capsule 07 → maintenance, validation with `pg_stat_user_indexes`.

---

## Resources for the project

1. [Markus Winand — Use The Index, Luke!](https://use-the-index-luke.com/) — the central reference. Especially useful chapters: "The Equality Operator" (composite), "Index-Only Scan" (covering), "Partial Indexes", "Functions" (expression).
2. [PostgreSQL Documentation — Indexes (full chapter PG 16)](https://www.postgresql.org/docs/16/indexes.html) — the official reference with the exact syntax of each type.
3. [Hubert "depesz" Lubaczewski — depesz.com (indexes)](https://www.depesz.com/tag/indexes/) — real cases with before/after plans.
4. [PostgreSQL Wiki — Index Maintenance](https://wiki.postgresql.org/wiki/Index_Maintenance) — useful queries to validate sizes, usage, bloat.
5. [pg_stat_user_indexes — official reference](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-USER-INDEXES-VIEW) — for post-creation validation.
6. [explain.depesz.com](https://explain.depesz.com/) — paste plans for visualization (useful when the plan gets complex).

---

## What comes next

What you built here is the direct foundation of the final project of module 8. In that project you'll:

- **Index** (what you learned here) + **eliminate N+1** (module 4) + **profiling with pg_stat_statements** (module 5) + **pool tuning** (module 6) + **anti-pattern refactoring** (modules 7-8) in a complete API.
- Measure improvements with `wrk`/`locust` (not just `EXPLAIN`).
- Report results in `BENCHMARKS.md` with a before/after table per endpoint.

The reflexes you trained in this project:

- "I see Seq Scan, first instinct: which index is missing?"
- "I see `Filter` with many rows removed: composite, or does the index cover only one column?"
- "I see a separate `Sort`: would a composite with an order eliminate it?"
- "I see `Heap Fetches > 0`: does VACUUM fix it? Is the INCLUDE complete?"

Those reflexes are the foundation of the "agile diagnosis" mode you'll need in module 8.

Before moving on to module 4, make sure you:

- ✅ Have your `INDICES.md` with the 5 queries documented (before/after plans + justifications).
- ✅ Have validated that each index improves the target query by at least 5x.
- ✅ Understand why each index (not just what you created).
- ✅ Have reflected on the reads vs writes trade-off of each one.

If all that is done, you're ready for module 4: **the N+1 problem with SQLAlchemy**. That module changes layers: from the SQL level to the ORM level. But you'll arrive with the confidence that the individual queries are well indexed — if something is slow, it's not the index. It's another problem. And you'll learn to detect and resolve it.

---

*Module 3 — Database Performance & Query Tuning Guide*