Final documentation + wrap-up of the guide

You close the integrating project and the complete guide. This capsule covers the three documents that close out the deliverable: BENCHMARKS.md with a complete table of improvements, ARCHITECTURE.md with ADRs (Architecture Decision Records) per decision, updated README. Plus a reflection on the close of the Data Layer sub-track.

Without these documents, the project looks half-done in a portfolio. With them, you demonstrate that you think like a senior — the code works, the decisions are justified, the benchmarks prove it.

---

BENCHMARKS.md

Template with sections you fill in with numbers measured on YOUR hardware:

# Blog API — Performance Benchmarks (Advanced PostgreSQL Refactor)

Refactor of the Blog API applying 6 advanced PostgreSQL features.
Benchmarks measured on a reproducible setup.

## Setup

- **PostgreSQL:** 16
- **FastAPI:** 0.110+
- **SQLAlchemy:** 2.0.25 (async)
- **asyncpg:** 0.29
- **Hardware:** [YOUR HARDWARE — e.g. M2 Pro, 16GB RAM, NVMe SSD]
- **Seeded data:** 1,000 users, 50 categories (hierarchical), 100,000 posts, 1,000,000 comments
- **Load tool:** wrk 4.2.0

To reproduce:

```bash
docker-compose up -d
alembic upgrade head
python scripts/seed.py
python benchmarks/baseline.py > before.txt
# Apply the refactor
python benchmarks/baseline.py > after.txt

Results

Summary table

Endpoint	Baseline	After	Improvement
GET /search?q=python	145ms	8ms	18x
GET /posts/popular	850ms	4ms	212x
GET /posts/by-tag/{tag}	N/A	5ms	new
GET /posts/{id}/comments	18ms	4ms	4.5x
GET /categories/{id}/tree	N/A	12ms	new
GET /categories/{id}/breadcrumb	N/A	6ms	new
GET /search?q=Phyton (typo)	145ms (no match)	12ms (suggestions)	UX ↑

Storage

Component	Storage delta
posts.metadata (JSONB)	+200KB total (typical posts)
posts.search_vector (generated)	+500KB total
top_posts_weekly (MV)	+50KB
Indexes (GIN trigram, FTS, JSONB)	~3MB total

Acceptable trade-off: ~5MB extra storage for 2-200x query performance.

CPU/IO during migration

Migration of comments to partitioned took:

• 5 min for 1M comments
• Wrk running 600s with 50 conn: 0 HTTP errors

Zero-downtime confirmed.

Components and their metrics

1. JSONB metadata (modules 1-2)

• Before: queries with custom metadata required an extra JOIN or subquery.
• After: direct queries to JSONB with a GIN index.
• Key metric: WHERE metadata->'tags' @> '["python"]' with a GIN index = 5ms on 100k posts.

2. Spanish FTS (module 3)

• Before: WHERE title ILIKE '%X%' OR body ILIKE '%X%' = full seq scan.
• After: WHERE search_vector @@ to_tsquery('spanish', 'X') with GIN.
• Stemming: finds "programar" when you search "programando" (same stem in Spanish).
• Key metric: 145ms → 8ms (18x improvement).

3. Materialized view (module 5)

• Before: query with LEFT JOIN comments + GROUP BY + ORDER BY.
• After: direct SELECT from the MV, refresh CONCURRENTLY every 30min.
• Trade-off: data up to 30min stale.
• Key metric: 850ms → 4ms (212x).

4. Partitioning (module 4)

• Before: "comments of post X in the last 7 days" queries do a seq scan or index scan on the full table (1M+ rows).
• After: partition pruning — only 1 partition (current month) scanned.
• Key metric: 18ms → 4ms (4.5x). The improvement increases with table size.
• Bonus: dropping old partitions = automatic retention policy.

5. Recursive CTE for categories (module 8)

• Before: no endpoint for the category subtree. The frontend did multiple queries (anti-pattern).
• After: a single GET /categories/{id}/tree endpoint with a recursive CTE.
• Key metric: subtree of 4-5 levels = 12ms in a single query.

6. Advisory locks (module 6)

• Before: crons could overlap, causing duplicate work.
• After: pg_try_advisory_xact_lock prevents concurrent runs.
• Key metric: reliability ↑, no more spike of errors from overlapped crons.

Learnings

1. GIN indexes are the most underused feature in PostgreSQL. JSONB without GIN, FTS without GIN, trigram without GIN — all lose 90% of the improvement.
2. MVs with CONCURRENTLY refresh are production-safe. A clear trade-off: stale data vs blocking refresh.
3. Partitioning with zero-downtime is executable following the expand-contract pattern.
4. A recursive CTE removes the N+1 anti-pattern in hierarchies. One query vs N queries.
5. Advisory locks replace Redis for many coordination cases.

Limitations / considerations

• Single-instance benchmarks. Multi-instance production may vary.
• Specific hardware — numbers vary on other setups.
• Stale data in the MV is an explicit trade-off.
• Storage overhead acceptable for the benefits (~5MB).

---

## `ARCHITECTURE.md` with ADRs

Each architectural decision documented as a mini-ADR:

```markdown
# Blog API — Architecture Decision Records (ADRs)

This documentation captures the architectural decisions of the refactor.
Each ADR has: context, options, decision, trade-offs.

## ADR-001: JSONB for metadata vs individual columns

**Context:** posts need variable metadata (SEO tags, social sharing, custom
fields). Some posts have 2 fields, others 10.

**Options:**
1. Individual columns (`seo_title`, `seo_description`, etc.).
2. Separate `post_metadata` table (key-value).
3. JSONB column.

**Decision:** JSONB with a GIN index.

**Trade-offs:**
- Pro: flexibility, evolution without migrations.
- Pro: queries with the `@>` operator and GIN are fast.
- Con: reduced type safety (Pydantic in the API layer mitigates it).
- Con: storage slightly larger than typed columns.

**Justification:** fast evolution is a priority. Type safety is maintained in
the Pydantic schema.

## ADR-002: tsvector generated column vs trigger-maintained

**Context:** FTS requires a `tsvector` column kept in sync with `title + body`.

**Options:**
1. PL/pgSQL trigger that updates the vector on UPDATE.
2. Generated column (`GENERATED ALWAYS AS ... STORED`) — PG 12+.
3. Application-managed (compute in Python on save).

**Decision:** Generated column.

**Trade-offs:**
- Pro: zero maintenance overhead.
- Pro: impossible to forget the update.
- Con: requires PG 12+ (acceptable, all providers have it).
- Con: STORED variant duplicates storage (vs computed-on-read).

**Justification:** simplicity wins. STORED is what you want for fast queries.

## ADR-003: pg_trgm fallback in search

**Context:** users type emails, search queries with typos. FTS doesn't handle typos
naturally (stem-based, no edit-distance).

**Options:**
1. FTS only, no fallback.
2. Trigram only, no FTS.
3. FTS primary + trigram fallback ("did you mean").

**Decision:** FTS primary, trigram for suggestions.

**Trade-offs:**
- Pro: best of both — relevance ranking from FTS, typo tolerance from trigram.
- Con: two GIN indexes on posts (storage overhead).

**Justification:** UX justifies the overhead.

## ADR-004: refresh frequency of MV `top_posts_weekly`

**Context:** the MV provides instant queries but the data is stale. How often to refresh?

**Options:**
1. Every 5 min (fresh data, expensive refresh).
2. Every 30 min (balance).
3. Every hour (cheap refresh, more stale data).
4. On-demand (client-trigger).

**Decision:** every 30 min.

**Trade-offs:**
- 30min stale is acceptable for "popular this week".
- The refresh cron costs ~10s on current data; every 30min is manageable.
- Every 5min would be overkill (popular posts don't change that much in 5min).

**Justification:** the sweet spot between freshness and cost.

## ADR-005: monthly vs weekly partitioning

**Context:** the comments table grows linearly. Partition by date.

**Options:**
1. Daily (365 partitions/year).
2. Weekly (~52 partitions/year).
3. Monthly (12 partitions/year).
4. Yearly (1 partition/year).

**Decision:** Monthly.

**Trade-offs:**
- Monthly: 12 partitions/year, manageable. "Last 7 days" queries may touch 1-2 partitions.
- Weekly: 52 partitions/year. "Last 7 days" queries touch exactly 1 — but more administrative overhead.
- Daily: 365 partitions/year. Too much overhead.

**Justification:** balance between partition count and query selectivity.

## ADR-006: closure table vs recursive CTE for categories

**Context:** subtree and breadcrumb queries are frequent (search, filtering).

**Options:**
1. Recursive CTE (compute on each query).
2. Closure table (pre-computed, maintenance overhead).
3. Materialized view over a recursive CTE.

**Decision:** Recursive CTE.

**Trade-offs:**
- Categories change rarely but the tree is small (50 nodes max expected).
- A recursive CTE on a small tree is <15ms — acceptable.
- Closure table = unnecessary maintenance overhead for a small tree.
- An MV would be over-engineering.

**Justification:** simplicity. If the tree grows to >10k nodes or the queries become
critical path, re-evaluate.

## ADR-007: advisory locks vs Redis for distributed coordination

**Context:** periodic crons need a single-instance lock. The app already uses
PostgreSQL.

**Options:**
1. Redis SETNX.
2. PostgreSQL advisory locks.
3. ZooKeeper / etcd.

**Decision:** Advisory locks (transaction-level).

**Trade-offs:**
- Pro: zero new infrastructure (we already have PG).
- Pro: ACID, transactional.
- Con: no native TTL (mitigated with TCP keepalive).
- Con: no multi-region (not relevant for our single-region setup).

**Justification:** simplicity. If in the future we need multi-region or TTL,
re-evaluate.

## ADR-008: Generated column STORED vs VIRTUAL for search_vector

**Context:** PG 12 introduces generated columns. There's STORED and VIRTUAL.

**Options:**
1. STORED — value persisted, can be indexed.
2. VIRTUAL — computed on read, NOT in PG (PG doesn't support VIRTUAL yet).

**Decision:** STORED.

**Trade-offs:**
- VIRTUAL doesn't exist in PG (Postgres 16). Forced choice.
- STORED duplicates storage but allows indexing.

**Justification:** STORED is the only real option in current PG.

---

Updated README

# Blog API — Advanced PostgreSQL Refactor

Multi-feature refactor demonstrating 6 advanced PostgreSQL patterns.

## Features

- **JSONB metadata** with GIN index for flexible post attributes.
- **Full-Text Search** (Spanish) with `pg_trgm` fallback for typos.
- **Materialized view** `top_posts_weekly` with periodic refresh.
- **Monthly partitioning** of `comments` for time-series performance.
- **Recursive categories** with `WITH RECURSIVE` for subtrees and breadcrumbs.
- **Advisory locks** preventing concurrent cron runs.

Plus extensions: `citext` (case-insensitive emails), `pg_trgm` (fuzzy search).

## Setup

```bash
git clone <this-repo>
cd blog-api-advanced
docker-compose up -d
alembic upgrade head
python scripts/seed.py  # Generates 1k users, 100k posts, 1M comments
uvicorn app.main:app --reload

Run benchmarks

python benchmarks/baseline.py

Run tests

pytest -v

Performance highlights

Endpoint	Improvement
/search	18x (FTS replaces LIKE)
/posts/popular	212x (MV replaces complex JOIN)
/comments/recent	4.5x (partitioning)

See BENCHMARKS.md for full numbers.

Architecture decisions

See ARCHITECTURE.md for ADRs explaining each design choice.

Stack

• FastAPI 0.110+
• SQLAlchemy 2.0+ (async)
• asyncpg 0.29+
• PostgreSQL 16
• Alembic 1.13+

Cron jobs

*/30 * * * * python -m app.tasks.refresh_top_posts
0 3 * * * python -m app.tasks.reindex_fts

Repository

github.com/USER/blog-api-advanced

---

## Wrap-up of the guide and the sub-track

You've reached the end. What you have:

### A public GitHub repo with

- Code for the refactored Blog API.
- 6 advanced features applied.
- Alembic migrations including zero-downtime partitioning.
- Tests with a real Postgres.
- `BENCHMARKS.md` with measured numbers.
- `ARCHITECTURE.md` with ADRs per decision.
- Reproducible README.

### Capabilities demonstrated

- Schema design with advanced features (JSONB, FTS, partitioning).
- Zero-downtime migration with expand-contract.
- Materialized views with a refresh strategy.
- Recursive CTEs for hierarchies and graphs.
- Advisory locks for distributed coordination.
- Architectural documentation with ADRs.

### Patterns internalized

Throughout the guide:

1. **Modules 1-2** — deep JSONB (storage, queries, GIN indexing).
2. **Module 3** — FTS with tsvector + pg_trgm + ranking.
3. **Module 4** — declarative partitioning with range/list/hash.
4. **Module 5** — materialized views with refresh strategies.
5. **Module 6** — advisory locks + savepoints for coordination.
6. **Module 7** — useful extensions (citext, UUIDs, hstore vs JSONB, advanced pg_trgm).
7. **Module 8** — recursive CTEs + integrating project.

---

## Complete Data Layer sub-track

This guide (#14) closes the **Data Layer sub-track** of the Backend Python path:

- **Guide #12** — Database Performance & Query Tuning. Indexing, EXPLAIN, N+1, profiling, pooling, autovacuum, anti-patterns.
- **Guide #13** — SQL Patterns for Production APIs. Cursor pagination, soft delete, audit logs, multi-tenancy, zero-downtime migrations, optimistic locking, bulk operations.
- **Guide #14** — Advanced PostgreSQL for Backend (this guide). JSONB, FTS, partitioning, MVs, advisory locks, extensions, CTEs.

The 3 guides cover ~95% of what a senior backend dev will need from PostgreSQL in their career.

**Going deeper** enters DBA territory: replication, high availability, deep PG observability, memory/IO tuning. That's DBA work, not backend dev.

---

## Your next action

1. **Push the repo to public GitHub**.
2. **Link it in your CV/portfolio** — "Demonstration of advanced PostgreSQL patterns: JSONB, FTS, partitioning, MVs, recursive CTEs, advisory locks, with measured benchmarks and ADRs."
3. **Practice the presentation** — walk through the repo out loud, explain each decision. The next senior interview is coming.
4. **Apply it to your production project** — identify places where these patterns apply. Start with the one with the highest impact.

---

## Upcoming guides in the path

After the Data Layer sub-track, the next guides in the Backend Python path (depending on the student's order):

- **#10 — Redis & Caching Strategies**: complement PostgreSQL with strategic caching.
- **#11 — Testing Backend Applications**: comprehensive testing of APIs including the DB layer.
- **#15-#18 — Deployment, CI/CD, Docker, Monitoring**: real production.

Each one builds on the solid PostgreSQL fundamentals you have now.

---

## Final resources

1. [PostgreSQL Documentation](https://www.postgresql.org/docs/) — the official reference, always.
2. [Brandur Leach Blog](https://brandur.org/) — deep analyses of real patterns.
3. [Crunchy Data Blog](https://www.crunchydata.com/blog/) — technical analyses.
4. [PostgreSQL Weekly](https://postgresweekly.com/) — newsletter.
5. [The Art of PostgreSQL — book](https://theartofpostgresql.com/) — deep reference.
6. [Markus Winand — Use The Index, Luke!](https://use-the-index-luke.com/) — technical analysis.
7. [Awesome Postgres](https://github.com/dhamaniasad/awesome-postgres) — curated resources.

---

*Capsule 08 of 08 — Module 8 — Advanced PostgreSQL for Backend Guide*

*End of the guide. End of the Data Layer sub-track. Your next senior interview will find you ready.*