GIN indexes for FTS and generated columns: the jump from Seq Scan to Bitmap Index Scan

Capsule description

So far your FTS works logically: to_tsvector('spanish_unaccent', body) @@ websearch_to_tsquery(...) returns the correct matches. But there's a problem invisible in small demos that becomes dramatic in production: PostgreSQL calls to_tsvector for every row when filtering. For 100 posts you don't notice. For 100k posts the endpoint takes 1.2 seconds. For 1M posts it stops working.

This capsule teaches you the pattern that makes FTS production-ready in PostgreSQL 16: a generated column tsv tsvector that maintains itself automatically on insert/update, with a GIN index over that column. You're going to see the same query before (Seq Scan, ~800ms) and after (Bitmap Index Scan, ~12ms). You're going to understand why the GIN index beats a B-tree for FTS, what happens with setweight when you combine title and body, and how to declare all of it from SQLAlchemy 2.0.

By the end you'll be able to add performant FTS to any table in an Alembic migration, read an EXPLAIN ANALYZE of a query with FTS and predict whether the GIN index is being used, and understand why the generated column (PG 12+) replaced the legacy trigger pattern.

Mental model: why Seq Scan kills FTS without an index

When PostgreSQL runs this query without an index:

SELECT id, title FROM posts
WHERE to_tsvector('spanish_unaccent', body)
      @@ websearch_to_tsquery('spanish_unaccent', 'python');

The plan is:

Seq Scan on posts
  Filter: (to_tsvector('spanish_unaccent', body) @@ websearch_to_tsquery('spanish_unaccent', 'python'))

That means:

1. PostgreSQL walks every row of posts.
2. For each row, it calls to_tsvector('spanish_unaccent', body) — that is: tokenize the body, apply unaccent, apply stemming, build the tsvector. That's non-trivial work.
3. It does the @@ match. If it passes, it includes the row in the result.

The cost grows linearly with the number of rows. Every row pays the full cost of processing the text, even though most of them don't match.

                ┌────────────────────────────────────────────┐
                │  Seq Scan (no index)                       │
                │                                            │
                │  For each row:                             │
                │   1. Read body (potentially long)          │
                │   2. Tokenize with the parser              │
                │   3. Apply unaccent                        │
                │   4. Apply spanish_stem                    │
                │   5. Build the tsvector in memory          │
                │   6. Match with @@                         │
                │                                            │
                │  Cost: O(N rows × average body length)     │
                └────────────────────────────────────────────┘
                                  │
                                  ▼
                ┌────────────────────────────────────────────┐
                │  Pre-computed GIN Index                    │
                │                                            │
                │  The tsvector is already pre-computed:     │
                │   - A physical tsv column on each row      │
                │   - The GIN index maps: lexeme → ID list   │
                │                                            │
                │  Per query:                                │
                │   1. Convert the query to tsquery (once)   │
                │   2. Look up the lexemes in the GIN        │
                │   3. Return the IDs of the matched rows    │
                │                                            │
                │  Cost: ~ O(log N × number of lexemes)      │
                └────────────────────────────────────────────┘

The idea: pre-compute the tsvector once (when the row is inserted or updated), store it in a physical column, and build a GIN index on top. Queries stop doing per-row work and start using the index.

Why GIN and not B-tree

A B-tree works well when you index values with a total order: integers, dates, sortable strings. For each row, a single key.

A tsvector has no total order — it's a collection of lexemes. A row can have 50 lexemes. Any of those lexemes could be part of the match. A B-tree doesn't know how to index "a row has several keys."

GIN (Generalized Inverted Index) is made exactly for this. For each lexeme, GIN stores the list of rows that contain it. It's the same concept as Lucene/Elasticsearch's "inverted index":

Lexeme    | Rows that contain it
----------|-----------------------
'python'  | {1, 4, 17, 22, 89, ...}
'gat'     | {3, 8, 45, ...}
'corr'    | {2, 19, 67, ...}
'jardin'  | {3, 6, 12, ...}

When a "python" query comes in:

1. PostgreSQL converts "python" to a tsquery → the lexeme python.
2. It looks it up in the GIN: 'python' → {1, 4, 17, 22, 89, ...}.
3. It returns those rows (then applies additional filters if there are any).

The cost is proportional to the number of lexemes in the query, not to the number of rows in the table. That's the difference between a Seq Scan and a Bitmap Index Scan.

The modern pattern: generated column + GIN index

There are three historical ways to maintain an indexed tsvector:

1. Compute to_tsvector(body) on every query (what we've seen so far). No pre-computation, no index. Slow.

2. A trigger, pre-PG 12. Create a tsv tsvector column, a BEFORE INSERT OR UPDATE trigger that sets NEW.tsv = to_tsvector('spanish_unaccent', NEW.body), and a GIN index over tsv. It works but it requires maintaining the trigger and there's a risk of desynchronization if someone drops it.

3. A generated column (PG 12+). tsv tsvector GENERATED ALWAYS AS (to_tsvector('spanish_unaccent', body)) STORED. PostgreSQL maintains the column automatically on every INSERT and UPDATE. You don't need a trigger or application code.

The modern pattern is option 3. Simpler, safer, no trigger code to maintain.

SQL syntax

-- Table with a generated column for FTS
CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  tsv tsvector GENERATED ALWAYS AS (
    to_tsvector('spanish_unaccent', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED
);

-- GIN index over the generated column
CREATE INDEX idx_posts_tsv ON posts USING GIN (tsv);

Three important details:

• STORED is mandatory for generated columns in PostgreSQL (unlike the SQL standard, which also accepts VIRTUAL). The value is physically materialized.
• coalesce(title, '') prevents a NULL in title or body from propagating NULL into the tsv. If title is NULL, NULL || ' ' || body would give NULL. With coalesce, it returns an empty string and the concatenation works.
• GENERATED ALWAYS means you can't manually insert a value into tsv. If you try, PostgreSQL fails. That's good: it guarantees consistency.

How the column is used from queries

-- Before (no index, no generated column): Seq Scan
SELECT id, title FROM posts
WHERE to_tsvector('spanish_unaccent', body) @@ websearch_to_tsquery('spanish_unaccent', 'python');

-- After (with generated column + GIN): Bitmap Index Scan
SELECT id, title FROM posts
WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python');

The query is shorter and, more importantly, it uses the index. PostgreSQL sees tsv @@ tsquery and goes straight to the GIN index over tsv.

Worked example: adding FTS with a generated column + index to your Blog API

We're going to set up the complete thing from Alembic + SQLAlchemy and measure the EXPLAIN ANALYZE before and after.

1. Alembic migration

# alembic/versions/20260502_add_fts_to_posts.py
"""Adds the generated tsv column and a GIN index to posts.

Assumes the previous migration already created spanish_unaccent.

Revision ID: 20260502_fts_posts
Revises: 20260501_unaccent
Create Date: 2026-05-02
"""
from alembic import op


revision = "20260502_fts_posts"
down_revision = "20260501_unaccent"


def upgrade() -> None:
    # 1. Add the generated tsv column
    op.execute(
        """
        ALTER TABLE posts
        ADD COLUMN tsv tsvector
        GENERATED ALWAYS AS (
          to_tsvector(
            'spanish_unaccent',
            coalesce(title, '') || ' ' || coalesce(body, '')
          )
        ) STORED
        """
    )

    # 2. GIN index over tsv
    # CONCURRENTLY so we don't lock the table in production
    op.execute("COMMIT")  # CONCURRENTLY can't run inside a transaction
    op.execute("CREATE INDEX CONCURRENTLY idx_posts_tsv ON posts USING GIN (tsv)")


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS idx_posts_tsv")
    op.execute("ALTER TABLE posts DROP COLUMN IF EXISTS tsv")

Why CONCURRENTLY: creating a GIN index over a table with data can take time (seconds to minutes depending on size). Without CONCURRENTLY, PostgreSQL takes a lock that blocks writes for that whole time. In production that's unacceptable. With CONCURRENTLY, the index is built without blocking writes (at the cost of taking more total time).

Limitation: CREATE INDEX CONCURRENTLY can't run inside a transaction. That's why the COMMIT beforehand — it interrupts the transaction Alembic opened by default. If your Alembic config doesn't support this, there are alternatives: use op.create_index() without CONCURRENTLY (accepting a brief lock) or use transaction_per_migration = False.

2. Declaring the column in the SQLAlchemy model

# models.py
from sqlalchemy import BigInteger, Computed, Index, String, Text
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)

    # Generated column (read-only from Python)
    tsv: Mapped[str] = mapped_column(
        TSVECTOR,
        Computed(
            "to_tsvector('spanish_unaccent', "
            "coalesce(title, '') || ' ' || coalesce(body, ''))",
            persisted=True,  # equivalent to STORED
        ),
        nullable=False,
    )

    __table_args__ = (
        Index(
            "idx_posts_tsv",
            "tsv",
            postgresql_using="gin",
        ),
    )

Three important things:

• Computed(..., persisted=True) is SQLAlchemy's way of declaring GENERATED ALWAYS AS ... STORED.
• Mapped[str] is the pragmatic type hint. A tsvector isn't an idiomatic Python str, but it's what SQLAlchemy returns by default. What matters is that it's read-only: never assign to post.tsv manually.
• postgresql_using="gin" indicates the index method. Without it, SQLAlchemy creates a B-tree by default, which is NO use for FTS.

3. Search endpoint using the tsv column

# api.py
from fastapi import FastAPI, Query
from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

from models import Post


engine = create_async_engine(
    "postgresql+asyncpg://postgres:postgres@localhost:5432/blog"
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
app = FastAPI()

TS_CONFIG = "spanish_unaccent"


@app.get("/search")
async def search(q: str = Query(..., min_length=1, max_length=200)) -> dict:
    """Search posts by title and body with Spanish FTS + unaccent."""
    async with SessionLocal() as session:
        tsquery = func.websearch_to_tsquery(TS_CONFIG, q)
        stmt = (
            select(Post.id, Post.title)
            .where(Post.tsv.bool_op("@@")(tsquery))
            .order_by(Post.id)
            .limit(20)
        )
        result = await session.execute(stmt)
        rows = result.all()
        return {
            "query": q,
            "count": len(rows),
            "results": [{"id": r.id, "title": r.title} for r in rows],
        }

Note: the WHERE uses Post.tsv directly (the pre-computed column), not func.to_tsvector(...). That's what triggers the use of the GIN index.

4. Measuring the impact: EXPLAIN ANALYZE before and after

To reproduce this, you need a large test dataset. Here's a script that generates 100k posts with Spanish content:

# seed_large.py
import asyncio
import random

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from models import Post


WORDS = [
    "python", "fastapi", "postgres", "django", "javascript", "react",
    "redis", "docker", "kubernetes", "linux", "git", "testing",
    "performance", "scaling", "microservicios", "arquitectura",
    "diseño", "patrones", "código", "refactor", "deuda técnica",
    "seguridad", "autenticación", "encriptación", "monitoring",
    "observabilidad", "logging", "métricas", "alertas",
    "machine learning", "datos", "análisis", "visualización",
    "frontend", "backend", "fullstack", "devops", "sre",
    "agile", "scrum", "kanban", "retrospectiva", "sprint",
    "canción", "español", "música", "cultura", "tecnología",
]


def gen_title() -> str:
    return " ".join(random.sample(WORDS, k=random.randint(2, 4))).capitalize()


def gen_body() -> str:
    paragraphs = []
    for _ in range(random.randint(3, 8)):
        sentences = " ".join(
            random.choice(WORDS) + " " + " ".join(random.sample(WORDS, k=random.randint(2, 6)))
            for _ in range(random.randint(2, 5))
        )
        paragraphs.append(sentences + ".")
    return " ".join(paragraphs)


async def main(n: int = 100_000) -> None:
    engine = create_async_engine(
        "postgresql+asyncpg://postgres:postgres@localhost:5432/blog"
    )
    Session = async_sessionmaker(engine, expire_on_commit=False)
    BATCH = 1000

    async with Session() as session:
        for batch_start in range(0, n, BATCH):
            posts = [
                Post(title=gen_title(), body=gen_body())
                for _ in range(BATCH)
            ]
            session.add_all(posts)
            await session.commit()
            print(f"  inserted {batch_start + BATCH:>7}/{n}")

    await engine.dispose()


if __name__ == "__main__":
    asyncio.run(main(100_000))

python seed_large.py

(It takes a few minutes depending on your hardware. If you want to go faster, drop to 10k posts — the contrast is still visible.)

Then you run EXPLAIN ANALYZE before and after having the GIN index. If you want to simulate "before," you can temporarily drop the index:

DROP INDEX idx_posts_tsv;

And run:

EXPLAIN ANALYZE
SELECT id, title FROM posts
WHERE to_tsvector('spanish_unaccent', coalesce(title, '') || ' ' || coalesce(body, ''))
      @@ websearch_to_tsquery('spanish_unaccent', 'python fastapi');

Typical output (no index, 100k rows):

 Seq Scan on posts  (cost=0.00..15234.00 rows=100 width=...)
   Filter: ((to_tsvector('spanish_unaccent', ...)) @@ '''python'' & ''fastapi'''::tsquery)
   Rows Removed by Filter: 99XXX
 Planning Time: 0.4 ms
 Execution Time: 1247.8 ms

You recreate the index and the generated column (if you had dropped them):

ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('spanish_unaccent', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
CREATE INDEX idx_posts_tsv ON posts USING GIN (tsv);

And now you run:

EXPLAIN ANALYZE
SELECT id, title FROM posts
WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python fastapi');

Typical output (with the GIN index, 100k rows):

 Bitmap Heap Scan on posts  (cost=68.50..456.75 rows=100 width=...)
   Recheck Cond: (tsv @@ '''python'' & ''fastapi'''::tsquery)
   Heap Blocks: exact=85
   ->  Bitmap Index Scan on idx_posts_tsv  (cost=0.00..68.47 rows=100 width=0)
         Index Cond: (tsv @@ '''python'' & ''fastapi'''::tsquery)
 Planning Time: 0.5 ms
 Execution Time: 13.2 ms

From 1247ms to 13ms with the same query and the same data. Almost 100× faster. And the cost of maintaining the generated column + index is negligible compared to the recurring cost of every query.

This is what makes FTS production-ready.

Weights: separating title and body with setweight

A natural improvement: you want a match in the title to weigh more than a match in the body. PostgreSQL allows this with setweight, which assigns a "weight letter" (A, B, C, D — A is the highest) to each tsvector.

SELECT
  setweight(to_tsvector('spanish_unaccent', 'Python para principiantes'), 'A')
  || setweight(to_tsvector('spanish_unaccent', 'Aprende Python desde cero con ejemplos prácticos'), 'B');

Output (a weighted tsvector):

 'aprend':4B 'cer':7B 'ejempl':9B 'practic':10B 'principi':3A 'python':1A,5B

Note how 'python' has 1A,5B — it appears at position 1 with weight A (title) and at position 5 with weight B (body). Ranking (capsule 05) uses these weights.

Also look at the positions of the second tsvector: they don't start at 1. The || operator shifts the right operand's positions by adding the left one's maximum position. The title has 3 tokens ("Python para principiantes"), so the body starts at 4: aprend lands on 4, not on 1. That's what makes proximity between title and body compute correctly.

To use it in a generated column:

ALTER TABLE posts DROP COLUMN tsv;

ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('spanish_unaccent', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('spanish_unaccent', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX idx_posts_tsv ON posts USING GIN (tsv);

This then lets you rank with ts_rank(tsv, query) and matches in the title (weight A) add up more than matches in the body (weight B).

For now it's enough that you know the generated column can hold any valid tsvector expression — including combinations with weights. Capsule 05 teaches you to use the weights for ranking.

Why does this matter in real work?

1. It's the difference between "FTS works in a demo" and "FTS works in production." Without a GIN index, FTS over real tables (>10k rows) has latencies that make the user reload the page. With a GIN index, the queries are in milliseconds. If your search PR reaches production without a GIN index, the first Friday at 6 PM is going to hurt.

2. It's your chance to show an EXPLAIN ANALYZE in a PR. "Ah, I added FTS" isn't a reviewable PR. "I added FTS, here's the EXPLAIN before (Seq Scan, 1.2s) and after (Bitmap Index Scan, 13ms) on a 100k-row table" is a PR the tech lead approves without objections. The number convinces.

3. The generated column eliminates a class of bugs. Pre-PG 12, the trigger pattern broke when someone dropped the trigger by accident, when a batch job inserted with COPY (which some triggers didn't fire on), when the app did a bulk insert without RETURNING. The generated column eliminates all of that. PostgreSQL guarantees consistency.

4. CREATE INDEX CONCURRENTLY sets you apart from junior devs. In production you can't run a plain CREATE INDEX on a table with traffic — it blocks writes. CONCURRENTLY is the version that doesn't block. Knowing how to use it (and the gotchas: it doesn't work in transactions, it can fail and leave an invalid index you have to clean up) is senior level.

Traps and common mistakes

Mistake 1 (conceptual): assuming that any index speeds up FTS

Symptom: you see the slow query, you add CREATE INDEX ON posts (tsv) (without USING GIN). The query is still slow.

Why it happens: without USING GIN, PostgreSQL creates a B-tree. A B-tree doesn't know how to index a tsvector for @@. The query plan ignores the index and does a Seq Scan.

How to detect it: run EXPLAIN ANALYZE and see a Seq Scan even though the index exists. The query plan mentions the index only if it uses it.

Fix: always USING GIN (tsv) for tsvector columns. There's a GiST alternative that's faster to write but slower to read — for typical FTS, GIN wins. Only consider GiST if your workload is write-heavy and read-light.

Mistake 2 (practical): forgetting CONCURRENTLY in production

Symptom: you run the migration in production. The table locks for writes for 30 seconds. The post-creation endpoints time out. Slack fills up with "the API is down."

Why it happens: CREATE INDEX without CONCURRENTLY takes a SHARE lock that blocks INSERT, UPDATE, DELETE. If your table has traffic, writes pile up and clients see timeouts.

Fix: always CREATE INDEX CONCURRENTLY for production. Trade-off: it takes longer (the migration can take minutes), but it doesn't block. And before applying it, measure in staging how long it takes — for 100M rows it can be hours.

Important edge case: if CREATE INDEX CONCURRENTLY fails halfway, an "INVALID" index is left in pg_indexes. You have to drop it manually with DROP INDEX before retrying. IF NOT EXISTS doesn't detect it as existing.

Mistake 3 (practical): a NULL in title or body breaks the generated column

Symptom: the migration works, but some posts have tsv = NULL and don't show up in searches.

Why it happens: NULL || ' ' || body returns NULL in SQL. If title is NULL, the coalesce(title, '') wasn't applied, and the whole tsv ends up NULL.

How to detect it: SELECT count(*) FROM posts WHERE tsv IS NULL;. If it returns > 0, you have the bug.

Fix: always wrap the arguments that can be NULL with coalesce:

to_tsvector('spanish_unaccent', coalesce(title, '') || ' ' || coalesce(body, ''))

If your schema guarantees NOT NULL on title and body, it isn't necessary, but defensively it's worth having. It costs 0 performance.

Mistake 4 (conceptual): thinking the query uses the index just because it exists

Symptom: you have the generated column and the GIN index. Your query is:

SELECT * FROM posts
WHERE to_tsvector('spanish_unaccent', body) @@ websearch_to_tsquery('spanish_unaccent', 'python');

It's still a Seq Scan. Why?

Why it happens: the query uses to_tsvector('spanish_unaccent', body) (the expression) instead of tsv (the column). PostgreSQL isn't smart enough to recognize that the expression is equivalent to the column. The GIN index is over tsv, not over to_tsvector('spanish_unaccent', body).

Fix: always use the pre-computed column in the query:

SELECT * FROM posts WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python');

Note: PG 17+ has improvements where the planner can recognize expressions equivalent to generated columns in some cases. But don't count on it — writing the query with the column directly is always more predictable.

Mistake 5 (practical): changing the GENERATED expression doesn't recompute existing rows

Symptom: you decide to add setweight to the tsv column. You change the GENERATED AS. The old rows still have no weights; only the new ones do.

Why it happens: ALTER TABLE ... ALTER COLUMN ... SET GENERATED doesn't exist cleanly. You have to DROP COLUMN tsv and ADD COLUMN tsv with the new expression. When doing ADD COLUMN GENERATED, PostgreSQL evaluates the expression for all the existing rows — that does recompute them.

Fix: to change a generated column in production:

-- 1. Drop the index (CONCURRENTLY)
DROP INDEX CONCURRENTLY idx_posts_tsv;

-- 2. Drop the column
ALTER TABLE posts DROP COLUMN tsv;

-- 3. Recreate it with the new expression (this recomputes every row)
ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('spanish_unaccent', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('spanish_unaccent', coalesce(body, '')), 'B')
  ) STORED;

-- 4. Recreate the index (CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_posts_tsv ON posts USING GIN (tsv);

In production this is an expensive operation (3 and 4 scan the table). Plan a brief downtime or a low-traffic window.

Mistake 6 (conceptual): a GIN index is always the best option

Symptom: you assume GIN is optimal and you never consider GiST.

Why it's confusing: GIN has better reads, but worse writes (inserts into a table with GIN are slower). GiST is the opposite.

When to consider GiST: a workload with lots of INSERT/UPDATE and few searches. For typical blog/catalog FTS (reads >> writes), GIN always. For a log table with massive writes and occasional searches, GiST can win.

Rule: GIN by default. GiST only if you measured and it's worth it.

Exercises

Exercise 1: read an EXPLAIN ANALYZE and diagnose

Someone shows you this EXPLAIN ANALYZE from a search endpoint:

 Seq Scan on posts  (cost=0.00..18450.00 rows=89 width=120) (actual time=2.1..2150.3 rows=89 loops=1)
   Filter: ((to_tsvector('spanish_unaccent'::regconfig, body)) @@ websearch_to_tsquery('spanish_unaccent'::regconfig, 'python'::text))
   Rows Removed by Filter: 99911
 Planning Time: 0.5 ms
 Execution Time: 2151.8 ms

What's the problem and how do you fix it?

-- Does the tsv column exist?
\d+ posts

-- Does the GIN index exist?
\di+ posts*

ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('spanish_unaccent', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

CREATE INDEX CONCURRENTLY idx_posts_tsv ON posts USING GIN (tsv);

-- BEFORE (Seq Scan)
SELECT id FROM posts
WHERE to_tsvector('spanish_unaccent', body) @@ websearch_to_tsquery('spanish_unaccent', 'python');

-- AFTER (Bitmap Index Scan)
SELECT id FROM posts
WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python');

Exercise 2: declare the SQLAlchemy model with a generated column and a GIN index

Declare an articles table with:

• id BIGINT PK
• title TEXT NOT NULL
• body TEXT NOT NULL
• category TEXT NOT NULL
• tsv, a generated column that combines title (weight A) + body (weight B), using spanish_unaccent
• a GIN index over tsv

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


class Base(DeclarativeBase):
    pass


class Article(Base):
    __tablename__ = "articles"

    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)

    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_articles_tsv",
            "tsv",
            postgresql_using="gin",
        ),
    )

def upgrade():
    op.create_table(
        "articles",
        sa.Column("id", sa.BigInteger(), primary_key=True),
        sa.Column("title", sa.String(200), nullable=False),
        sa.Column("body", sa.Text(), nullable=False),
        sa.Column("category", sa.String(50), nullable=False),
        sa.Column(
            "tsv",
            postgresql.TSVECTOR(),
            sa.Computed(
                "setweight(to_tsvector('spanish_unaccent', coalesce(title, '')), 'A') || "
                "setweight(to_tsvector('spanish_unaccent', coalesce(body, '')), 'B')",
                persisted=True,
            ),
            nullable=False,
        ),
    )
    op.create_index("idx_articles_tsv", "articles", ["tsv"], postgresql_using="gin")

Exercise 3: write the search endpoint using the pre-computed column

Implement the GET /search?q=... endpoint that:

1. Uses the pre-computed Article.tsv column (don't call to_tsvector in the query).
2. Uses websearch_to_tsquery with spanish_unaccent.
3. Orders by id descending (most recent first).
4. Limits to 20 results.
5. Returns [{id, title, category}].

from fastapi import FastAPI, Query
from sqlalchemy import desc, func, select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

from models import Article


engine = create_async_engine(
    "postgresql+asyncpg://postgres:postgres@localhost:5432/blog"
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
app = FastAPI()

TS_CONFIG = "spanish_unaccent"


@app.get("/search")
async def search(q: str = Query(..., min_length=1, max_length=200)) -> dict:
    """Full-text search over articles using the GIN index."""
    async with SessionLocal() as session:
        tsquery = func.websearch_to_tsquery(TS_CONFIG, q)
        stmt = (
            select(Article.id, Article.title, Article.category)
            .where(Article.tsv.bool_op("@@")(tsquery))
            .order_by(desc(Article.id))
            .limit(20)
        )
        result = await session.execute(stmt)
        rows = result.all()
        return {
            "query": q,
            "count": len(rows),
            "results": [
                {"id": r.id, "title": r.title, "category": r.category}
                for r in rows
            ],
        }

SELECT articles.id, articles.title, articles.category
FROM articles
WHERE articles.tsv @@ websearch_to_tsquery('spanish_unaccent', $1)
ORDER BY articles.id DESC
LIMIT 20;

Limit
  ->  Bitmap Heap Scan on articles
        Recheck Cond: (tsv @@ websearch_to_tsquery(...))
        ->  Bitmap Index Scan on idx_articles_tsv
              Index Cond: (tsv @@ websearch_to_tsquery(...))

Exercise 4: architectural decision — GIN vs GiST for your workload

Your team has three tables with FTS planned. For each one, decide GIN or GiST and justify it:

a) posts (10M rows, ~50 new per day, ~100k searches/day from the web). b) audit_logs (200M rows, ~5000 inserts/sec, occasional searches by the security team). c) support_tickets (500k rows, ~200 inserts/day, ~20k searches/day from the support dashboard).

Summary and next step

In this capsule you learned the pattern that makes FTS production-ready in PostgreSQL 16:

• Without an index, FTS is a Seq Scan: PostgreSQL calls to_tsvector(body) for every row. The cost grows linearly with N. Unacceptable for >10k rows.
• A GIN index is the right one for tsvector: an inverted index that maps lexeme → list of rows. Queries in O(log N × number of lexemes).
• A generated column tsv tsvector GENERATED ALWAYS AS (...) STORED (PG 12+) is the modern pattern: PostgreSQL maintains the tsv automatically on every INSERT/UPDATE. It replaces the legacy trigger pattern.
• Combining title and body with weights using setweight(..., 'A') and setweight(..., 'B') sets up the ranking from capsule 05.
• CREATE INDEX CONCURRENTLY is mandatory in production so you don't block writes while the index is being created.
• Using tsv directly in the WHERE (not to_tsvector(body)) guarantees the GIN index is used.
• coalesce(col, '') in the GENERATED expression prevents a NULL in title/body from propagating into tsv.

Before moving on you should be able to:

• Add a generated tsv column + GIN index to a table in an Alembic migration with CONCURRENTLY.
• Declare the column and the index from a SQLAlchemy 2.0 model with Computed(...) and Index(..., postgresql_using="gin").
• Read an EXPLAIN ANALYZE and tell a Seq Scan from a Bitmap Index Scan for FTS.
• Decide between GIN and GiST based on your table's workload.
• Diagnose why a query with FTS doesn't use the index even though it exists.

Next capsule — Ranking with ts_rank and ts_rank_cd. You have the query working and fast, but the results show up in arbitrary order (by id, in the example). The user expects the first result to be the most relevant: the one that has the word the most times, in the title, close to other words in the query. That's ranking. Capsule 05 teaches you the two algorithms PostgreSQL offers, when to use each one, and how to combine them with the setweight weights you already set up.

Resources

1. PostgreSQL 16 — GIN Indexes (70.4) — the complete GIN reference, including tuning options (fastupdate, gin_pending_list_limit).
2. PostgreSQL 16 — Generated Columns (5.3) — the official generated columns reference with all its restrictions.
3. PostgreSQL 16 — CREATE INDEX CONCURRENTLY — syntax and caveats (not in a transaction, INVALID indexes if it fails).
4. Lukas Fittl (pganalyze) — "Indexing PostgreSQL JSONB and Full Text Search" — a deep analysis of GIN for JSONB and FTS, with real cases.
5. SQLAlchemy 2.0 — Computed — the documentation for Computed() with all its options (persisted=True/False, regenerate=...).
6. Crunchy Data — "Postgres Full-Text Search" — an end-to-end review with an emphasis on production.
7. AWS RDS Blog — "Full-text search performance with PostgreSQL" — real benchmarks on RDS for different table sizes.

Module 3 — Advanced PostgreSQL for Backend Guide

Next capsule: Ranking with ts_rank and ts_rank_cd — ordering results by relevance, not by date.