Module project: the blog's Spanish-language search

What are you going to build and why?

It's time to apply everything from the module in an integrative mini-project. You're going to build a complete Spanish-language search engine over your Blog API's posts table. The result is going to be a /search?q=... endpoint that feels modern, tolerates typos, is fast over 100k+ posts, and doesn't add Elasticsearch or any external service.

This search engine is functionally identical to the "Spanish FTS over posts.title + posts.body" component of the final project in module 8 (the complete Blog API refactor). The techniques, decisions, and code you produce here get reused literally in that project. That means the work doesn't get thrown away — it stays as a direct foundation.

By completing this project:

• Your local Blog API will have a /search?q=... endpoint that responds in <50ms over 100k posts.
• You'll have an /autocomplete?prefix=... endpoint for search-as-you-type.
• You'll have Alembic migrations with all the components (extensions, configs, generated column, indexes).
• You'll have a BENCHMARK.md with EXPLAIN ANALYZE before/after demonstrating the GIN index's impact.
• You'll have automated tests that verify: Spanish stemming, handling of special characters, ranking, fuzzy fallback.

It's portfolio-worthy work. Put it on GitHub and reference it on your CV — it demonstrates command of advanced Spanish FTS, a decision matrix with criteria, and the ability to implement complex features without external dependencies.

---

Project objective

By completing this project:

• You implemented a Spanish FTS search engine with unaccent, ranking by ts_rank_cd, and highlighted snippets with ts_headline.
• You added a fuzzy fallback with pg_trgm for queries with typos.
• You implemented autocomplete combining exact prefix + fuzzy.
• You created all the necessary Alembic migrations (idempotent, with CONCURRENTLY for indexes).
• You measured the GIN index's impact with EXPLAIN ANALYZE before and after.
• You have automated tests covering the critical cases (stemming, eñes, ranking, fallback).

---

How it fits with what you learned

Module concept	Where it's used in the project
Capsule 02: tsvector and tsquery	All the WHERE machinery with @@
Capsule 03: spanish_unaccent	The config in the generated column and the queries
Capsule 04: generated column + GIN	The heart of the performance
Capsule 05: ts_rank_cd + ts_headline	Ranking and snippets in the main endpoint
Capsule 06: pg_trgm	Fuzzy fallback + autocomplete
Capsule 07: the decision matrix	The justification in BENCHMARK.md

Think of the project as a pyramid: tsvector/tsquery are the base, spanish_unaccent is the language context, GIN + the generated column make it all performant, ts_rank_cd + ts_headline improve the quality of the results, and pg_trgm closes the edge cases (typos, autocomplete). Each capsule is a piece of the building.

---

Technical specifications

Stack

• Language: Python 3.11+
• Main framework: FastAPI 0.110+
• ORM: async SQLAlchemy 2.0+ with asyncpg
• DB: PostgreSQL 16+
• Migrations: Alembic
• Tests: pytest + pytest-asyncio
• Required PostgreSQL extensions: unaccent, pg_trgm

Initial setup

# Create the project
mkdir blog-search
cd blog-search
python -m venv venv
source venv/bin/activate  # on Windows: venv\Scripts\activate

# Dependencies
pip install "fastapi>=0.110" "uvicorn[standard]" "sqlalchemy[asyncio]>=2.0" \
            "asyncpg>=0.29" "pydantic>=2.5" "alembic>=1.13" \
            "pytest>=7.4" "pytest-asyncio>=0.23"

# Structure
mkdir -p app alembic/versions tests
touch app/{__init__,models,db,api,seed}.py
touch tests/{__init__,conftest,test_search}.py

# Initialize Alembic
alembic init alembic
# Edit alembic.ini to point at the async DSN (sqlalchemy.url = postgresql+asyncpg://...)

Expected file structure

blog-search/
├── app/
│   ├── __init__.py
│   ├── models.py          # SQLAlchemy models
│   ├── db.py              # Engine + sessionmaker
│   ├── api.py             # FastAPI endpoints
│   └── seed.py            # Script to populate 100k posts
├── alembic/
│   ├── env.py
│   └── versions/
│       ├── 20260501_unaccent.py
│       ├── 20260502_fts_posts.py
│       └── 20260503_pg_trgm.py
├── tests/
│   ├── __init__.py
│   ├── conftest.py
│   └── test_search.py
├── BENCHMARK.md           # EXPLAIN ANALYZE analysis
├── README.md              # How to run it
├── alembic.ini
└── requirements.txt

---

Required features

1. SQLAlchemy models with a generated column

app/models.py must declare:

• A posts table with: id BIGSERIAL PK, title TEXT NOT NULL, body TEXT NOT NULL, category TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now().
• A generated column tsv tsvector with the title at weight A + the body at weight B, using spanish_unaccent.
• A GIN index over tsv.
• A GiST index over title with gist_trgm_ops.

# app/models.py
from datetime import datetime

from sqlalchemy import BigInteger, Computed, DateTime, Index, String, Text, func
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    category: Mapped[str] = mapped_column(String(50), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    tsv: Mapped[str] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('spanish_unaccent', coalesce(title, '')), 'A') || "
            "setweight(to_tsvector('spanish_unaccent', coalesce(body, '')), 'B')",
            persisted=True,
        ),
        nullable=False,
    )

    __table_args__ = (
        Index("idx_posts_tsv", "tsv", postgresql_using="gin"),
        Index(
            "idx_posts_title_trgm",
            "title",
            postgresql_using="gist",
            postgresql_ops={"title": "gist_trgm_ops"},
        ),
    )

2. Idempotent Alembic migrations

Three migrations, in order:

20260501_unaccent.py: enables unaccent and creates the spanish_unaccent config.

20260502_fts_posts.py: creates the posts table with the generated column and the GIN index.

20260503_pg_trgm.py: enables pg_trgm and creates the GiST index over title.

All of them must be idempotent (runnable multiple times without an error) and use CONCURRENTLY for indexes.

3. The /search endpoint

GET /search?q=<query>&limit=<int>

Response 200:
{
  "query": "python fastapi",
  "count": 4,
  "method": "fts" | "fuzzy" | "empty",
  "results": [
    {
      "id": 142,
      "title": "Python con FastAPI: tutorial completo",
      "snippet": "...vamos a usar <mark>Python</mark> con <mark>FastAPI</mark>...",
      "score": 0.187
    }
  ]
}

Expected behavior:

• Apply the main FTS with ts_rank_cd and ts_headline.
• If FTS returns 0 results, fall back to pg_trgm with similarity over title.
• Validate q: 1-200 characters.
• Validate limit: 1-100, default 20.
• Mark the method used in the response.

4. The /autocomplete endpoint

GET /autocomplete?prefix=<str>&limit=<int>

Response 200:
{
  "prefix": "pos",
  "suggestions": [
    {"id": 1, "title": "PostgreSQL avanzado", "method": "prefix"},
    {"id": 2, "title": "Posibilidades de Postgres", "method": "prefix"},
    {"id": 3, "title": "Comparativa: Postgres vs MySQL", "method": "fuzzy"}
  ]
}

Expected behavior:

• Combine exact prefix (title ILIKE 'prefix%') + fuzzy (title % 'prefix').
• Exact prefix first, fuzzy fills up to limit.
• Validate prefix: 2-50 characters.
• Validate limit: 1-20, default 10.
• Mark each result with a method.

5. The seed script

app/seed.py must generate 100k posts with varied Spanish content. Use tech-domain words + some with accents to test unaccent:

TECH_WORDS = [
    "python", "javascript", "fastapi", "django", "react", "vue",
    "postgresql", "mysql", "redis", "docker", "kubernetes",
    "linux", "git", "github", "testing", "ci/cd", "devops",
    "microservicios", "arquitectura", "patrones de diseño",
    "código limpio", "refactor", "deuda técnica", "performance",
    "machine learning", "datos", "análisis", "visualización",
    # With accents and eñes to test unaccent
    "configuración", "implementación", "información", "opción",
    "función", "operación", "validación", "autenticación",
    "documentación", "integración", "comunicación",
    "diseño", "enseñanza", "año", "pequeño", "español",
]

CATEGORIES = ["backend", "frontend", "devops", "datos", "carrera"]

Notice the last words: on top of accents, the corpus needs eñes. As you saw in capsule 03, the stemmer resolves accents on its own; the ñ is resolved only by unaccent. If your test corpus doesn't have a single ñ, your accent test passes without proving anything.

Each post must have a title of 3-7 words and a body of 200-1000 words.

6. Documented benchmarks

BENCHMARK.md must contain:

• EXPLAIN ANALYZE of a search without the GIN index (Seq Scan, high latency).
• EXPLAIN ANALYZE of the same search with the GIN index (Bitmap Index Scan, low latency).
• A latency comparison for 3 different queries (one common term, two terms, a query with a typo).
• A conclusion justifying "why PostgreSQL FTS is enough for this case" using the criteria from capsule 07.

7. Automated tests

tests/test_search.py must cover at minimum:

• Exact match: searching "python" returns posts with "python".
• Stemming: searching "configurar" matches posts with "configuración" / "configuran".
• Eñes: searching "espanol" (without the eñe) matches posts with "español". This is the test that proves unaccent is configured correctly.
• Ranking: posts with the word in the title show up before posts with the word only in the body.
• Fuzzy fallback: searching "pythn" (a typo) returns results with method: "fuzzy".
• Autocomplete: the prefix "pos" returns posts whose title starts with "Pos".

⚠️ Don't use "configuracion" → "configuración" as your accent test. That case passes even if unaccent isn't configured, because the Spanish stemmer already strips acute accents on its own (capsule 03). It's a test that goes green without proving anything. The ñ case is the only one that genuinely fails without unaccent.

---

Validations and error handling

Input validations

• q in /search: 1-200 characters, required. Return 422 if it doesn't comply.
• prefix in /autocomplete: 2-50 characters, required. Return 422.
• limit in both: an integer within range. Return 422 if out of range.

Errors that must be handled

• 422 Unprocessable Entity: invalid input (length, type, required). FastAPI gives it automatically with Pydantic.
• 500 Internal Server Error: a DB error. Return a generic message to the client, log the full traceback.
• No results: return 200 with count: 0 and method: "empty". Do NOT return 404 (no results isn't an error).

Edge cases to cover

• A query with only spaces: treat it as empty, return 422.
• A query with special characters (<script>, ' OR 1=1): pass it to websearch_to_tsquery, which sanitizes it, with no additional validation needed — but log it if you suspect abuse.
• A query with very common words (Spanish stop words: "el", "la", "de"): it can return 0 because stop words get filtered out. The right thing is not to fail; return count: 0.

---

Minimal implementation example

app/db.py:

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

DATABASE_URL = "postgresql+asyncpg://postgres:postgres@localhost:5432/blog_search"

engine = create_async_engine(DATABASE_URL, echo=False)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

app/api.py (a minimal skeleton):

from typing import Any, Literal

from fastapi import FastAPI, Query
from pydantic import BaseModel
from sqlalchemy import desc, func, select

from app.db import SessionLocal
from app.models import Post


app = FastAPI(title="Blog Search API")
TS_CONFIG = "spanish_unaccent"
HEADLINE_OPTIONS = (
    "StartSel=<mark>, StopSel=</mark>, "
    "MaxFragments=2, MinWords=8, MaxWords=18, "
    "FragmentDelimiter= ... "
)


class SearchResult(BaseModel):
    id: int
    title: str
    snippet: str | None = None
    score: float


class SearchResponse(BaseModel):
    query: str
    count: int
    method: Literal["fts", "fuzzy", "empty"]
    results: list[SearchResult]


@app.get("/search", response_model=SearchResponse)
async def search(
    q: str = Query(..., min_length=1, max_length=200),
    limit: int = Query(20, ge=1, le=100),
) -> SearchResponse:
    """FTS search with a fuzzy fallback."""
    if not q.strip():
        return SearchResponse(query=q, count=0, method="empty", results=[])

    async with SessionLocal() as session:
        # Step 1: main FTS
        tsquery = func.websearch_to_tsquery(TS_CONFIG, q)
        rank = func.ts_rank_cd(Post.tsv, tsquery).label("score")
        snippet = func.ts_headline(
            TS_CONFIG, Post.body, tsquery, HEADLINE_OPTIONS
        ).label("snippet")

        fts_stmt = (
            select(Post.id, Post.title, snippet, rank)
            .where(Post.tsv.bool_op("@@")(tsquery))
            .order_by(desc(rank))
            .limit(limit)
        )
        fts_rows = (await session.execute(fts_stmt)).all()

        if fts_rows:
            return SearchResponse(
                query=q,
                count=len(fts_rows),
                method="fts",
                results=[
                    SearchResult(
                        id=r.id,
                        title=r.title,
                        snippet=r.snippet,
                        score=float(r.score),
                    )
                    for r in fts_rows
                ],
            )

        # Step 2: fuzzy fallback with pg_trgm
        similarity = func.similarity(Post.title, q).label("score")
        fuzzy_stmt = (
            select(Post.id, Post.title, similarity)
            .where(Post.title.bool_op("%")(q))
            .order_by(desc(similarity))
            .limit(limit)
        )
        fuzzy_rows = (await session.execute(fuzzy_stmt)).all()

        if fuzzy_rows:
            return SearchResponse(
                query=q,
                count=len(fuzzy_rows),
                method="fuzzy",
                results=[
                    SearchResult(
                        id=r.id, title=r.title, snippet=None, score=float(r.score)
                    )
                    for r in fuzzy_rows
                ],
            )

        return SearchResponse(query=q, count=0, method="empty", results=[])


@app.get("/autocomplete")
async def autocomplete(
    prefix: str = Query(..., min_length=2, max_length=50),
    limit: int = Query(10, ge=1, le=20),
) -> dict[str, Any]:
    """Autocomplete with exact prefix + fuzzy."""
    async with SessionLocal() as session:
        # Exact prefix first
        prefix_stmt = (
            select(Post.id, Post.title)
            .where(Post.title.ilike(f"{prefix}%"))
            .order_by(Post.title)
            .limit(limit)
        )
        prefix_rows = (await session.execute(prefix_stmt)).all()

        if len(prefix_rows) >= limit:
            return {
                "prefix": prefix,
                "suggestions": [
                    {"id": r.id, "title": r.title, "method": "prefix"}
                    for r in prefix_rows
                ],
            }

        # Fill the rest with fuzzy
        already_ids = [r.id for r in prefix_rows]
        remaining = limit - len(prefix_rows)

        fuzzy_stmt = (
            select(Post.id, Post.title)
            .where(Post.title.bool_op("%")(prefix))
            .order_by(Post.title.op("<->")(prefix))
            .limit(remaining)
        )
        if already_ids:
            fuzzy_stmt = fuzzy_stmt.where(Post.id.notin_(already_ids))

        fuzzy_rows = (await session.execute(fuzzy_stmt)).all()

        return {
            "prefix": prefix,
            "suggestions": [
                *[{"id": r.id, "title": r.title, "method": "prefix"} for r in prefix_rows],
                *[{"id": r.id, "title": r.title, "method": "fuzzy"} for r in fuzzy_rows],
            ],
        }


# To run it: uvicorn app.api:app --reload --port 8000

This skeleton:

• ✅ Is runnable end-to-end (assuming the migrations and the seed have been run).
• ✅ Covers the two required endpoints.
• ❌ Does NOT include tests (you must write them).
• ❌ Does NOT include the seed (you must write it).
• ❌ Does NOT include BENCHMARK.md (you must generate the EXPLAIN ANALYZEs and document them).

---

Evaluation rubric (self-check)

Total: 100 points. Passing: ≥70 points.

Functionality (40 points)

• (8 pts) Idempotent Alembic migrations that create: unaccent, the spanish_unaccent config, the posts table, the generated tsv column with setweight A/B, the GIN index, pg_trgm, the GiST index over title.
• (8 pts) The /search endpoint with the main FTS: websearch_to_tsquery, ts_rank_cd, ts_headline with <mark>. Returns method: "fts".
• (8 pts) A fuzzy fallback with pg_trgm when FTS returns 0. Returns method: "fuzzy". When both are empty, method: "empty" with count: 0.
• (8 pts) The /autocomplete endpoint combining exact prefix + fuzzy. Marks each suggestion with a method.
• (8 pts) A seed script that generates 100k posts with varied Spanish content, with accented words and categories.

Code quality (25 points)

• (5 pts) Consistent type hints (Mapped[...], response_model on the endpoints).
• (5 pts) TS_CONFIG = "spanish_unaccent" and HEADLINE_OPTIONS as constants (not scattered strings).
• (5 pts) A SQLAlchemy model with correct Computed(...) and Index(..., postgresql_using="gin").
• (5 pts) Explicit validations in the endpoints (min_length, max_length, ge, le).
• (5 pts) Handling an empty / whitespace-only query without crashing.

Demonstrated performance (15 points)

• (5 pts) A documented EXPLAIN ANALYZE without the GIN index (Seq Scan, high latency).
• (5 pts) A documented EXPLAIN ANALYZE with the GIN index (Bitmap Index Scan, low latency).
• (5 pts) A comparison table of latencies for 3 different queries (common, multi-term, with a typo).

Tests (10 points)

• (2 pts) Test: stemming (searching "configurar" matches "configuración").
• (2 pts) Test: eñes (searching "espanol" matches "español"). The case that actually proves unaccent.
• (2 pts) Test: ranking (a post with the word in the title ranks before a post with the word only in the body).
• (2 pts) Test: fuzzy fallback (searching "pythn" returns results with method: "fuzzy").
• (2 pts) Test: autocomplete (a prefix returns posts whose title starts with the prefix).

Documentation (10 points)

• (3 pts) README.md with: how to run it, how to seed it, curl examples.
• (4 pts) BENCHMARK.md with EXPLAIN ANALYZE before/after and a conclusion on "PostgreSQL FTS is enough for this case" using the criteria from capsule 07.
• (3 pts) Comments in the code at key points (why ts_rank_cd and not ts_rank, why gist_trgm_ops for autocomplete, why coalesce in the generated column).

Extra credit (optional, up to +15 pts)

• (+5 pts) A /search endpoint with a "did you mean" suggestion when FTS returns 0 (using trigrams against a dictionary of popular terms — exercise 2 of capsule 06).
• (+5 pts) A load test with locust or wrk showing the endpoint sustains >500 req/s on typical hardware.
• (+5 pts) An optional implementation with RETURNING to combine the INSERT of a new post + an immediate search, demonstrating transactional consistency vs ES.

---

Common mistakes in this project

Mistake 1 (practical): the CREATE INDEX CONCURRENTLY migration fails

Symptom: Alembic fails with "CREATE INDEX CONCURRENTLY cannot run inside a transaction block".

Why it happens: Alembic wraps each migration in a transaction by default. CONCURRENTLY can't run inside a transaction.

How to fix it: two options:

a) Configure Alembic to run that migration without a transaction:

# alembic/env.py
def run_migrations_online():
    # ...
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            transaction_per_migration=True,  # each migration gets its own tx (or none)
        )

b) Use op.execute("COMMIT") before the CREATE INDEX CONCURRENTLY to close the implicit tx and run the CREATE outside it:

def upgrade():
    op.execute("COMMIT")
    op.execute("CREATE INDEX CONCURRENTLY ...")

Mistake 2 (conceptual): the EXPLAIN shows a Seq Scan even though the index exists

Symptom: you created the generated column and the GIN index, but EXPLAIN shows a Seq Scan. The GIN index isn't used.

Why it happens: the query uses to_tsvector(body) (the expression) instead of tsv (the pre-computed column). PostgreSQL doesn't associate them automatically.

How to fix it: use Post.tsv directly in the WHERE:

# Bad: doesn't use the index
.where(func.to_tsvector(TS_CONFIG, Post.body).bool_op("@@")(tsquery))

# Good: uses the GIN index
.where(Post.tsv.bool_op("@@")(tsquery))

Mistake 3 (practical): the seed takes hours

Symptom: you insert the 100k posts one by one and it takes 2 hours.

Why it happens: one INSERT per row with an individual commit generates 100k transactions. Lots of overhead.

Fix: insert in batches:

BATCH_SIZE = 1000

async with SessionLocal() as session:
    for batch_start in range(0, total, BATCH_SIZE):
        posts = [Post(title=..., body=..., category=...) for _ in range(BATCH_SIZE)]
        session.add_all(posts)
        await session.commit()  # one commit per batch

It takes minutes instead of hours.

Mistake 4 (conceptual): pg_trgm doesn't match words with eñes or accents

Symptom: you search for "disenio" or "espanol" with pg_trgm and it doesn't match posts with "diseño" or "español" in the title.

Why it happens: pg_trgm normalizes case ('Python' % 'python' is true), but it does not normalize accents or eñes. "español" and "espanol" have different trigrams.

Fix: normalize on both sides of the comparison. But watch out for the index:

-- ❌ THIS FAILS:
--    ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX idx_posts_title_unaccent_trgm
  ON posts USING GiST (unaccent(title) gist_trgm_ops);

unaccent() isn't IMMUTABLE by default (it depends on the dictionary it has loaded), and PostgreSQL doesn't index expressions that aren't. The correct pattern is to wrap it in your own function declared IMMUTABLE:

CREATE FUNCTION immutable_unaccent(text) RETURNS text AS $$
  SELECT unaccent('unaccent', $1)
$$ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;

CREATE INDEX idx_posts_title_unaccent_trgm
  ON posts USING GiST (immutable_unaccent(title) gist_trgm_ops);

And query against the same expression, or the index doesn't get used:

.where(
    func.immutable_unaccent(Post.title).bool_op("%")(
        func.immutable_unaccent(prefix)
    )
)

It's the same symmetry rule from capsule 03: the index's expression and the WHERE's expression have to be identical.

Mistake 5 (practical): ts_headline returns strings with HTML from the body

Symptom: the snippet includes HTML fragments from the original body, mixed in with <mark>.

Why it happens: ts_headline operates on plain text. It doesn't know HTML.

Fix: two options:

a) If your body is plain text, it isn't a problem.

b) If your body is HTML, sanitize it before passing it to ts_headline:

import re

def strip_html(text: str) -> str:
    return re.sub(r"<[^>]+>", "", text)

# In the query, you can create a generated body_plain column and use that

Or pre-generate body_plain as a generated column in the table.

Mistake 6 (practical): tests fail in CI because of the database

Symptom: tests pass locally but fail in CI with "connection refused".

Why it happens: the test database isn't available in CI or it uses a different URL.

Fix: use environment variables for the URL and a docker-compose or test fixture that brings up PostgreSQL in CI:

# tests/conftest.py
import os
import pytest_asyncio
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

DATABASE_URL = os.getenv(
    "TEST_DATABASE_URL",
    "postgresql+asyncpg://postgres:postgres@localhost:5432/test_blog_search",
)


@pytest_asyncio.fixture
async def engine():
    engine = create_async_engine(DATABASE_URL)
    yield engine
    await engine.dispose()

And in your .github/workflows/ci.yml:

services:
  postgres:
    image: postgres:16
    env:
      POSTGRES_PASSWORD: postgres
    ports:
      - 5432:5432

---

What to do if you get stuck?

• The setup doesn't work: review capsule 04, the "Alembic migration" section. Make sure you have PostgreSQL 16+ and that the extensions are installed (some distros split them out into postgresql-contrib).
• FTS doesn't return the expected results: review capsule 03 on unaccent and capsule 02 on tsvector/tsquery. Try SELECT to_tsvector('spanish_unaccent', 'your text') in psql to see the real output.
• Performance doesn't improve with the index: review capsule 04, "Traps and common mistakes": the query must use the tsv column, not the expression.
• Tests fail with "extension does not exist": make sure the test database has the migrations applied. One option is to run alembic upgrade head before the tests.
• You don't know how to put together BENCHMARK.md: review capsule 04's "Measuring the impact" section. Run EXPLAIN ANALYZE without the index (after a DROP INDEX), copy the output. Recreate the index. Run EXPLAIN ANALYZE again. Compare them in a markdown table.

---

Resources for the project

1. PostgreSQL 16 — Full Text Search (chapter 12) — the canonical reference for everything FTS.
2. PostgreSQL 16 — pg_trgm extension — for fuzzy and autocomplete.
3. SQLAlchemy 2.0 — PostgreSQL dialect: Full Text Search — how to express FTS from SQLAlchemy.
4. Alembic — op.execute and custom migrations — for running raw SQL (necessary for CREATE INDEX CONCURRENTLY and CREATE TEXT SEARCH CONFIGURATION).
5. pytest-asyncio — async fixtures — for async tests.
6. FastAPI — Query Parameters and String Validations — for input validations.

---

What comes next

What you built here gets reused directly in the final project of module 8 (the Blog API refactor). In that project you're going to integrate this search engine with: JSONB metadata (modules 1-2), partitioning of comments (module 4), materialized views (module 5), advisory locks (module 6), and recursive CTEs (module 8). The Spanish FTS component will already be ready — you just connect it to the refactor.

Before moving on to module 4, make sure your project:

• ✅ Runs alembic upgrade head without errors on a clean database.
• ✅ The seed completes 100k posts in under 5 minutes.
• ✅ curl 'http://localhost:8000/search?q=python+fastapi' returns ranked results with highlighted snippets in <50ms.
• ✅ curl 'http://localhost:8000/search?q=pythn' returns results with method: "fuzzy".
• ✅ curl 'http://localhost:8000/autocomplete?prefix=pos' returns suggestions in <30ms.
• ✅ The tests pass with pytest.
• ✅ BENCHMARK.md exists and shows the before/after contrast of the GIN index.

If all of that passes, you finished module 3. You arrived with a production-ready Spanish-language search engine, without adding Elasticsearch, and with a defensible architectural decision.

Next module (4): Native Partitioning. Your Blog API has good search, but the comments table is growing: 5 million rows and climbing. The queries are fast but the INSERTs are starting to slow down and VACUUM takes hours. The solution isn't migrating to another engine — it's partitioning the table by date. PostgreSQL 16 does this natively. You're going to learn range partitioning, hash partitioning, partition pruning, and the gotchas. It's the natural next step when "FTS is fast but the table is enormous."

---

Module 3 — Advanced PostgreSQL for Backend Guide — Module project

Next module: Native Partitioning — scale massive tables without migrating to another engine.