Ranking with `ts_rank` and `ts_rank_cd`: ordering by relevance, not by date

Capsule description

Your FTS works and it's fast. The user searches "python fastapi" and you get 50 posts that match. You return them ordered by id DESC and the first result is "My lemon chicken recipe with a note about Python that I published in May" — because it happens to have both words somewhere and it's the most recent. The user assumes your search engine doesn't work.

The problem isn't the match. It's the order. PostgreSQL FTS gives you two functions for ranking results by relevance instead of by date or id: ts_rank (which counts weight-adjusted frequency) and ts_rank_cd (which considers the proximity of the terms, "cover density"). This capsule teaches you the difference between the two, when to choose each one, and how to combine them with the setweight weights you set up in capsule 04 so that a match in the title weighs more than a match in the body.

By the end you'll be able to write search endpoints that return the right result in the first position — the one that actually answers the user's intent, not the most recent one that happens to mention the terms.

Mental model: relevance is the inverse of "how noisy the match is"

To understand ranking in FTS, think about what makes a document "more relevant" for a query. Three typical signals:

1. Frequency. A document that mentions "python" 8 times is more about python than one that mentions it once. But long documents mention everything more times, so frequency is normalized by length.

2. Position / section. If "python" shows up in the title, it's central to the document. If it shows up in a footnote, it's tangential. This is modeled with weights (A for title, B for body, etc.).

3. Proximity. If the query is "python fastapi", a document where they appear right next to each other ("Python con FastAPI") is more relevant than one where they appear 50 paragraphs apart. This is modeled with cover density.

PostgreSQL offers two functions that combine these signals in different ways:

┌──────────────────────────────────────────────────────────────────┐
│                                                                  │
│  ts_rank(tsv, query, [normalization])                            │
│  ────────────────────────────────────                            │
│  - Considers weight-adjusted frequency.                          │
│  - Does NOT consider term proximity.                             │
│  - Faster.                                                       │
│  - Appropriate: when the query has few terms                     │
│    and they're all separate, or when only frequency matters.     │
│                                                                  │
│  ts_rank_cd(tsv, query, [normalization])                         │
│  ──────────────────────────────────────                          │
│  - Cover Density: the distance between the matches.              │
│  - Matches close together → high score.                          │
│  - Matches far apart → low score.                                │
│  - It also considers weights.                                    │
│  - Appropriate: multi-term queries where proximity               │
│    is a relevance signal (the recommended default).              │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Both functions return a float4 (typically between 0 and ~10, not normalized). The absolute values aren't comparable across queries; what matters is the relative order within the results of a single query.

Default rule: ts_rank_cd. Proximity almost always feels more natural to the user. ts_rank is reserved for specific cases where you know only frequency matters to you.

ts_rank: weight-adjusted frequency

ts_rank(tsvector, tsquery) counts how many times the query's terms appear in the tsvector, adjusted by the weights assigned with setweight.

SELECT
  ts_rank(
    setweight(to_tsvector('spanish_unaccent', 'Python para principiantes'), 'A') ||
    setweight(to_tsvector('spanish_unaccent', 'Aprende Python con muchos ejemplos. Python es genial. Python facilita el desarrollo.'), 'B'),
    websearch_to_tsquery('spanish_unaccent', 'python')
  ) AS rank;

Output:

   rank
-----------
 0.7109370

If you compare two documents:

SELECT
  -- Doc 1: "python" in the title (weight A) + once in the body
  ts_rank(
    setweight(to_tsvector('spanish_unaccent', 'Python para principiantes'), 'A') ||
    setweight(to_tsvector('spanish_unaccent', 'Aprende a programar con Python.'), 'B'),
    websearch_to_tsquery('spanish_unaccent', 'python')
  ) AS doc1_rank,

  -- Doc 2: no "python" in the title, 5 times in the body
  ts_rank(
    setweight(to_tsvector('spanish_unaccent', 'Programación moderna'), 'A') ||
    setweight(to_tsvector('spanish_unaccent', 'Python es popular. Python es flexible. Python crece rápido. Aprende Python ya. Python te conviene.'), 'B'),
    websearch_to_tsquery('spanish_unaccent', 'python')
  ) AS doc2_rank;

Typical output:

 doc1_rank | doc2_rank
-----------+-----------
   0.6687  |    0.3559

Doc 1 (with "python" in the title, weight A) wins, even though doc 2 has the word five times in the body. Weight A is worth more than frequency. That's what you want.

Relative weights

By default the weights {A, B, C, D} map to {1.0, 0.4, 0.2, 0.1}. You can change those values by passing an array of 4 floats as the third argument:

ts_rank(
  '{0.1, 0.2, 0.4, 1.0}',  -- D, C, B, A respectively (inverted order)
  tsv,
  query
)

For 95% of cases the default is fine. Only adjust it if you have evidence that the ranking feels "off" for your specific corpus.

Normalization

ts_rank accepts a fourth argument (normalization, an integer flag) that controls how the rank is normalized by document length, number of unique matches, etc. It's bitwise:

For general use, ts_rank(tsv, query, 32) gives you scores between 0 and 1, easy to compare and show in a UI.

ts_rank_cd: cover density (proximity)

ts_rank_cd (cover density) considers how close the query's terms are in the document. Two consecutive terms rank higher than two terms separated by 50 words.

A clear example:

SELECT
  -- Doc 1: "python fastapi" right next to each other
  ts_rank_cd(
    to_tsvector('spanish_unaccent', 'Aprende a usar Python con FastAPI desde cero'),
    websearch_to_tsquery('spanish_unaccent', 'python fastapi')
  ) AS doc1,

  -- Doc 2: "python" and "fastapi" very far from each other
  ts_rank_cd(
    to_tsvector('spanish_unaccent',
      'Python es un lenguaje versátil. ' || repeat('Tiene muchos frameworks. ', 50) ||
      'Uno de ellos es FastAPI.'),
    websearch_to_tsquery('spanish_unaccent', 'python fastapi')
  ) AS doc2;

Typical output:

   doc1   |    doc2
----------+----------
  0.0500  |  0.0006

Doc 1 (the words adjacent) wins by nearly two orders of magnitude. ts_rank_cd captured the proximity: in doc 2 the terms are separated by 50 filler sentences and the score collapses. ts_rank wouldn't make that distinction — it would only count that both documents have the two terms.

When to use ts_rank_cd:

• A typical 2-5 word query where proximity indicates intent.
• Conversational searches ("como aprender python", "cuál es el mejor framework").
• The safe default for a generic search engine.

When to use ts_rank:

• A single-term query (python). Proximity doesn't apply with a single term — ts_rank_cd and ts_rank behave similarly, but ts_rank is slightly faster.
• Catalogs where repetition is a pure signal (e.g., products tagged with keywords).
• Backwards compatibility with legacy systems that use ts_rank.

Combining weights + ranking: the canonical pattern

The pattern you're going to use 95% of the time:

1. The generated tsv column has the title at weight A and the body at weight B (what you set up in capsule 04).
2. The query uses ts_rank_cd(tsv, query) in the ORDER BY DESC.
3. Matches in the title automatically rise to the top because their weight is 1.0 vs the body's 0.4.

SELECT
  id,
  title,
  ts_rank_cd(tsv, websearch_to_tsquery('spanish_unaccent', 'python fastapi')) AS rank
FROM posts
WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python fastapi')
ORDER BY rank DESC
LIMIT 20;

Expected output on a table with diverse data:

 id  |              title                        |   rank
-----+-------------------------------------------+----------
 142 | Python con FastAPI: tutorial completo     |  0.18
  87 | FastAPI vs Flask: comparativa práctica    |  0.12
  44 | Python para web: guía de frameworks       |  0.09
 201 | Mi blog con FastAPI                       |  0.06
 ...

The first one is the most relevant (both words in the title, weight A, together → high cover density + high weight). The following ones degrade progressively.

The complete pattern in a FastAPI endpoint

# api.py
from fastapi import FastAPI, Query
from sqlalchemy import desc, 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:
    """FTS search ranked by relevance (cover density + weights)."""
    async with SessionLocal() as session:
        tsquery = func.websearch_to_tsquery(TS_CONFIG, q)
        rank = func.ts_rank_cd(Post.tsv, tsquery).label("rank")

        stmt = (
            select(Post.id, Post.title, rank)
            .where(Post.tsv.bool_op("@@")(tsquery))
            .order_by(desc(rank))
            .limit(20)
        )
        result = await session.execute(stmt)
        rows = result.all()
        return {
            "query": q,
            "count": len(rows),
            "results": [
                {"id": r.id, "title": r.title, "rank": float(r.rank)}
                for r in rows
            ],
        }

Generated SQL:

SELECT
  posts.id,
  posts.title,
  ts_rank_cd(posts.tsv, websearch_to_tsquery('spanish_unaccent', $1)) AS rank
FROM posts
WHERE posts.tsv @@ websearch_to_tsquery('spanish_unaccent', $1)
ORDER BY rank DESC
LIMIT 20;

Note: websearch_to_tsquery(...) gets called twice in the SQL (once in the SELECT and once in the WHERE). PostgreSQL optimizes this internally — the plan reuses the result. Don't worry about the "redundancy."

ts_headline: highlighted snippets for the UI

Ranking orders things well, but the user only sees the title in the results list. You also want to show a fragment of the body with the searched word highlighted — like Google. That's what ts_headline is for.

SELECT
  id,
  title,
  ts_headline(
    'spanish_unaccent',
    body,
    websearch_to_tsquery('spanish_unaccent', 'python fastapi'),
    'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=10, MaxWords=25'
  ) AS snippet,
  ts_rank_cd(tsv, websearch_to_tsquery('spanish_unaccent', 'python fastapi')) AS rank
FROM posts
WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python fastapi')
ORDER BY rank DESC
LIMIT 10;

Example output:

 id  | title                              | snippet
-----+------------------------------------+-------------------------------------------------
 142 | Python con FastAPI: tutorial...    | ...vamos a usar <mark>Python</mark> con
                                            <mark>FastAPI</mark> para construir un...

ts_headline trims a fragment around the matches and wraps them in the tags you ask for. The most useful options:

• StartSel, StopSel: the opening/closing tags for the matches (default <b> and </b>).
• MaxFragments: how many fragments to return (default 0 = the whole document with the matches highlighted).
• MinWords, MaxWords: the size of each fragment.
• FragmentDelimiter: the separator between fragments when MaxFragments > 1 (default " ... ").

An important caution with ts_headline:

ts_headline operates on the original body, not on the tsvector. That means PostgreSQL calls ts_headline for every row in the result and processes the body to find the matches and build the snippet. It's expensive.

Good practices:

1. Call ts_headline only in the final LIMIT. Don't put it in subqueries that get evaluated for every row.
2. If your corpus has very long bodies (>10KB), consider limiting the body you pass to ts_headline:

ts_headline(
  'spanish_unaccent',
  left(body, 5000),  -- truncate to the first 5KB for the snippet
  websearch_to_tsquery('spanish_unaccent', 'python')
) AS snippet

That reduces the processing at the price of the snippet only being able to come from the first chunk of the body. For most blogs the relevant match is near the beginning.

3. If your UI doesn't show snippets, don't ask for them. It's free work the DB doesn't need to do.

Worked example: a complete search endpoint with ranking + snippets

Let's put it all together into a production-ready endpoint.

# search_api.py
from typing import Any

from fastapi import FastAPI, Query
from pydantic import BaseModel
from sqlalchemy import desc, 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"
HEADLINE_OPTIONS = (
    "StartSel=<mark>, StopSel=</mark>, "
    "MaxFragments=2, MinWords=8, MaxWords=20, "
    "FragmentDelimiter= ... "
)


class SearchResult(BaseModel):
    id: int
    title: str
    snippet: str
    rank: float


class SearchResponse(BaseModel):
    query: str
    count: int
    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 cover density ranking and highlighted snippets.
    Uses the GIN index over the generated tsv column.
    """
    async with SessionLocal() as session:
        tsquery = func.websearch_to_tsquery(TS_CONFIG, q)

        rank = func.ts_rank_cd(Post.tsv, tsquery).label("rank")
        snippet = func.ts_headline(
            TS_CONFIG,
            Post.body,
            tsquery,
            HEADLINE_OPTIONS,
        ).label("snippet")

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

        return SearchResponse(
            query=q,
            count=len(rows),
            results=[
                SearchResult(
                    id=r.id,
                    title=r.title,
                    snippet=r.snippet,
                    rank=float(r.rank),
                )
                for r in rows
            ],
        )

Example curl:

curl 'http://localhost:8000/search?q=python+fastapi'

Typical response:

{
  "query": "python fastapi",
  "count": 4,
  "results": [
    {
      "id": 142,
      "title": "Python con FastAPI: tutorial completo",
      "snippet": "...vamos a usar <mark>Python</mark> con <mark>FastAPI</mark> para construir un buscador full-text...",
      "rank": 0.187
    },
    {
      "id": 87,
      "title": "FastAPI vs Flask: comparativa práctica",
      "snippet": "...elegir entre <mark>FastAPI</mark> y Flask depende de tu stack actual de <mark>Python</mark>...",
      "rank": 0.122
    },
    {
      "id": 44,
      "title": "Python para web: guía de frameworks",
      "snippet": "...los frameworks más usados en <mark>Python</mark> moderno incluyen Django, Flask y <mark>FastAPI</mark>...",
      "rank": 0.091
    }
  ]
}

Query plan (EXPLAIN ANALYZE):

 Limit  (cost=425.30..425.81 rows=20 width=...)
   ->  Sort  (cost=425.30..425.55 rows=100 width=...)
         Sort Key: (ts_rank_cd(...)) DESC
         ->  Bitmap Heap Scan on posts
               Recheck Cond: (tsv @@ websearch_to_tsquery(...))
               ->  Bitmap Index Scan on idx_posts_tsv
                     Index Cond: (tsv @@ websearch_to_tsquery(...))
 Planning Time: 0.4 ms
 Execution Time: 23 ms

23ms for 100k posts with Spanish FTS + ranking + snippets. That's production-ready.

Optimization: filter first, rank afterwards with a CTE

For large corpora (>1M rows), ts_rank_cd can be expensive because it's computed over every row that matches, not just over the LIMIT. If your query matches 50k rows and you only want the top 20, you still compute the rank for 50k.

Optimization: a CTE that filters first, then ranks only the subset.

WITH matches AS (
  SELECT id, title, body, tsv
  FROM posts
  WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', 'python fastapi')
  LIMIT 1000  -- a ceiling so we don't rank thousands of matches
)
SELECT
  id,
  title,
  ts_rank_cd(tsv, websearch_to_tsquery('spanish_unaccent', 'python fastapi')) AS rank,
  ts_headline('spanish_unaccent', body, websearch_to_tsquery('spanish_unaccent', 'python fastapi'),
              'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') AS snippet
FROM matches
ORDER BY rank DESC
LIMIT 20;

Trade-off: if the CTE's LIMIT is too low, you can lose highly ranked results that are "further down" in the index's scan order. For consumer queries, 1000 candidates is usually enough — the truly relevant matches almost always fall in the top 1000 by GIN's construction.

For the first versions of a search engine, you probably don't need this optimization. Apply it when you measure that ts_rank_cd is the bottleneck.

Why does this matter in real work?

1. It's the difference between "my search works" and "my search is useful." Without ranking, users see random results. With ranking, they see the right result in first place. That's the metric that matters: "click-through-rate of the first result." If it's high, your ranking works.

2. It lets you have a grounded opinion in product discussions. When someone says "the search doesn't find X", you can review the ranking and diagnose: does the document match? what rank does it have? which documents rank above it? That's concrete debugging, not opinion.

3. The "ts_rank vs ts_rank_cd" decision is debatable and you defend it with criteria. Your tech lead asks "why did you use cover density?". You answer: "because most queries have 2-3 terms and proximity is a relevance signal. For single-term queries the cost is marginal and the benefit is consistency. If we wanted to optimize the last 5% of performance, ts_rank would be slightly faster but we'd lose the proximity signal." That's senior work.

4. ts_headline is the visual difference between a "Google-style" search engine and a "year-2002-style" one. Showing the searched word highlighted in a contextual fragment is what makes the results list feel modern. Without it, the user has to open every link to see what it's about. With it, the user decides in the list itself.

Traps and common mistakes

Mistake 1 (conceptual): treating the rank as an absolute score

Symptom: you try to "show results with rank > 0.5" or "the ranks look weird, they should be between 0 and 1."

Why it happens: the absolute values of ts_rank and ts_rank_cd aren't comparable across queries. A query with a very frequent term can have low ranks in absolute terms but the relative ones are correct. A query with a rare term can have high ranks.

How to tell: look at two different queries — one with "python" (common) and another with "kubernetes" (rarer). The rank ranges will be different. There's no "rank > X = relevant."

Fix: the rank is for ordering, not for filtering. Use ORDER BY rank DESC LIMIT N. If you want to normalize, pass the 32 flag to ts_rank (rank between 0 and 1) and understand that even so the value is relative within the query.

Mistake 2 (practical): calling ts_headline over the whole body of many rows

Symptom: the endpoint takes 2 seconds to return 100 results with snippets. Without snippets, it takes 30ms.

Why it happens: ts_headline operates on the raw body, not on the tsvector. For each row, it walks the body looking for matches. For 100 bodies of 5KB each, that's 500KB of processing per request.

Fix:

a) Apply the LIMIT before the headline. In SQLAlchemy:

# Bad: headline computes for all the matches before the limit
stmt = select(Post.id, snippet).where(...).order_by(desc(rank)).limit(20)

# Good: subquery with a limit, then headline only on the 20
subq = (
    select(Post.id, Post.body, rank)
    .where(...)
    .order_by(desc(rank))
    .limit(20)
    .subquery()
)
stmt = select(
    subq.c.id,
    func.ts_headline(TS_CONFIG, subq.c.body, tsquery, HEADLINE_OPTIONS).label("snippet"),
)

PostgreSQL generally applies the LIMIT before the SELECT when it can, but being explicit with a subquery guarantees the order.

b) Truncate the body for the headline. ts_headline(TS_CONFIG, left(body, 5000), tsquery, ...). You lose the possibility of snippets from the end of the body, but you gain a lot of performance.

c) Don't ask for snippets if the UI doesn't show them. It sounds obvious, but it happens: you copy/paste from one endpoint to another and you dragged the ts_headline along even though the new UI only shows the title.

Mistake 3 (conceptual): using ts_rank by default without understanding what ts_rank_cd does

Symptom: you follow a tutorial that uses ts_rank and you copy it without thinking. Then your search returns results where "python fastapi" as an adjacent pair ranks the same as separated by 50 paragraphs.

Why it happens: many tutorials are old or copied from English docs that don't go deep into the difference. ts_rank is the first function that shows up in the docs and many people don't read further.

Fix: understand the difference and choose consciously. For 95% of cases, ts_rank_cd. That decision, justified with a comment in the code, sets you apart from copy-paste:

# We use ts_rank_cd (cover density) because the proximity of the terms
# is a strong relevance signal for the user's multi-term queries.
# Basic ts_rank ignores proximity and ranks adjacent vs separated matches the same.
rank = func.ts_rank_cd(Post.tsv, tsquery).label("rank")

Mistake 4 (practical): the ORDER BY rank doesn't use the GIN index for ordering

Symptom: your query has ORDER BY ts_rank_cd(...) DESC LIMIT 20. You expected the GIN index to help with the ordering. The plan says "Sort" after the Bitmap Heap Scan.

Why it happens: the GIN index has the lexemes and the rows that contain them, but it doesn't have the ranks pre-computed (because the rank depends on the query of the moment). PostgreSQL filters with the index, does a bitmap heap scan to read the rows, computes ts_rank_cd for each resulting row, and then sorts.

This is expected and correct. There's no way to pre-compute the ranks because they depend on the query.

What you can do:

• Limit the rows that enter the Sort (with a CTE like I showed earlier).
• If your corpus allows pre-ranking by a fixed metric (popularity, votes, recency), you can combine: ORDER BY (ts_rank_cd(tsv, q) * 0.7 + popularity * 0.3) DESC.

Mistake 5 (conceptual): thinking that ts_rank_cd always needs weights

Symptom: someone tells you "ts_rank_cd doesn't work if you didn't use setweight." You get confused and start setting weights on all your columns.

Why it's confusing: ts_rank_cd uses weights if they're present. If they aren't, it assumes everything is at weight D (the lowest) — but the relative rank between documents still works because all the rows are at the same default weight.

The truth: ts_rank_cd works without setweight. But if you want matches in the title to weigh more than matches in the body, you need setweight in your generated column to distinguish the sections. Without weights, there's no way for the rank to know which part of the document contains the match.

Rule: if your table has a single "search field" (the whole body), you don't need weights. If it has several (title, body, tags, etc.) and you want some to weigh more, you do.

Mistake 6 (practical): ts_headline breaks the body's existing HTML

Symptom: your body has HTML (it's a blog post). ts_headline wraps the matches with <mark> but the resulting snippet mixes in <p>, <a>, etc. Rendering it is ugly.

Why it happens: ts_headline operates on plain text. It doesn't know how to tell HTML tags from words.

Fix:

a) Sanitize the body before passing it to ts_headline. If you store the body as HTML, consider also storing a "plain text" version in another column and using that for the headline. Example:

ALTER TABLE posts ADD COLUMN body_plain TEXT
  GENERATED ALWAYS AS (regexp_replace(body, '<[^>]+>', '', 'g')) STORED;

(A simple regex, not a perfect one — for complex HTML use a plpgsql function or sanitize in the app.)

b) Sanitize the snippet in the app before showing it. This is the most common one: the snippet from the DB is "best effort" and the UI cleans it with a library like bleach or DOMPurify, allowing only <mark>.

Exercises

Exercise 1: predict the order of the results

You have this table with 4 posts (the tsv already has the title at weight A and the body at weight B):

For the query "python fastapi", order the 4 posts by ts_rank_cd (from most ranked to least). Justify it.

SELECT id, title,
  ts_rank_cd(
    setweight(to_tsvector('spanish_unaccent', title), 'A') ||
    setweight(to_tsvector('spanish_unaccent', body), 'B'),
    websearch_to_tsquery('spanish_unaccent', 'python fastapi')
  ) AS rank
FROM (
  VALUES
    (1, 'FastAPI tutorial', 'FastAPI es un framework moderno.'),
    (2, 'Python para principiantes', 'Aprende Python con muchos ejemplos. FastAPI es uno de los frameworks que vas a ver.'),
    (3, 'Frameworks web modernos', 'Hablamos de Python, FastAPI, Flask, Django.'),
    (4, 'Mi viaje a Bali', 'Aprendí algo de Python y FastAPI durante el avión, pero más tiempo nadando.')
) AS t(id, title, body)
WHERE
  setweight(to_tsvector('spanish_unaccent', title), 'A') ||
  setweight(to_tsvector('spanish_unaccent', body), 'B')
  @@ websearch_to_tsquery('spanish_unaccent', 'python fastapi')
ORDER BY rank DESC;

Exercise 2: choose ts_rank or ts_rank_cd by scenario

For each case, decide which function to use and justify it.

a) A product search engine in an e-commerce site. Typical queries: 1-2 words (zapatillas, zapatillas running, cafetera nespresso). b) An internal technical documentation search. Queries of 3-7 words (como configurar nginx con ssl letsencrypt). c) A tag search engine in a ticket system. Single-word queries (urgente, regression). d) A candidate-to-role matching system: declared skills vs required skills (several technical words).

Exercise 3: implement the endpoint with ranking + snippets from SQLAlchemy

Implement async def search(session, q: str, limit: int = 20) -> list[dict] that:

1. Uses the pre-computed Post.tsv column.
2. Uses ts_rank_cd to order by relevance.
3. Uses ts_headline with <mark></mark> tags, MaxFragments=2, MinWords=8, MaxWords=18.
4. Returns a list of {id, title, snippet, rank}.

from typing import Any

from sqlalchemy import desc, func, select
from sqlalchemy.ext.asyncio import AsyncSession


TS_CONFIG = "spanish_unaccent"
HEADLINE_OPTIONS = (
    "StartSel=<mark>, StopSel=</mark>, "
    "MaxFragments=2, MinWords=8, MaxWords=18, "
    "FragmentDelimiter= ... "
)


async def search(
    session: AsyncSession, q: str, limit: int = 20
) -> list[dict[str, Any]]:
    """
    Ranked FTS search with snippets.
    Assumes Post.tsv is a generated column with setweight A for title, B for body.
    """
    tsquery = func.websearch_to_tsquery(TS_CONFIG, q)

    rank = func.ts_rank_cd(Post.tsv, tsquery).label("rank")
    snippet = func.ts_headline(
        TS_CONFIG, Post.body, tsquery, HEADLINE_OPTIONS
    ).label("snippet")

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

    return [
        {
            "id": r.id,
            "title": r.title,
            "snippet": r.snippet,
            "rank": float(r.rank),
        }
        for r in rows
    ]

SELECT
  posts.id,
  posts.title,
  ts_headline('spanish_unaccent', posts.body, websearch_to_tsquery('spanish_unaccent', $1),
              'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=8, MaxWords=18, FragmentDelimiter= ... ') AS snippet,
  ts_rank_cd(posts.tsv, websearch_to_tsquery('spanish_unaccent', $1)) AS rank
FROM posts
WHERE posts.tsv @@ websearch_to_tsquery('spanish_unaccent', $1)
ORDER BY rank DESC
LIMIT 20;

Exercise 4: optimize an endpoint that takes 2 seconds

Your search endpoint takes 2.1 seconds for queries that match ~30k rows in a 5M-row table. The plan says:

Sort  (cost=12450..12500 rows=30000)
  Sort Key: ts_rank_cd(...) DESC
  ->  Bitmap Heap Scan on posts (rows=30000)
        ->  Bitmap Index Scan on idx_posts_tsv

What do you optimize and how?

WITH candidates AS (
  SELECT id, title, body, tsv
  FROM posts
  WHERE tsv @@ websearch_to_tsquery('spanish_unaccent', :q)
  LIMIT 1000   -- a reasonable ceiling
)
SELECT
  id, title,
  ts_rank_cd(tsv, websearch_to_tsquery('spanish_unaccent', :q)) AS rank,
  ts_headline('spanish_unaccent', body, websearch_to_tsquery('spanish_unaccent', :q),
              'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') AS snippet
FROM candidates
ORDER BY rank DESC
LIMIT 20;

ORDER BY (ts_rank_cd(tsv, query) * 0.6 + popularity_score * 0.4) DESC

CREATE INDEX idx_posts_tsv_category ON posts USING GIN (tsv, category);

SET gin_fuzzy_search_limit = 1000;
SELECT ... FROM posts WHERE tsv @@ ... ORDER BY ts_rank_cd(...) DESC LIMIT 20;

Exercise 5: architectural decision — custom weights

Your team decides that in the posts table the matches should weigh:

• Title: high.
• Tags (a list of keywords): medium-high.
• Body: medium.
• Comments (concatenated): low.

Design the generated tsv column with setweight and justify the mapping to A/B/C/D.

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

ts_rank_cd('{0.1, 0.2, 0.7, 1.0}', tsv, query)
-- D=0.1, C=0.2, B=0.7, A=1.0 (more weight to tags, B)

Summary and next step

In this capsule you learned to order FTS results by relevance, not by date:

• ts_rank considers weight-adjusted frequency. Useful for single-term queries or when proximity adds nothing.
• ts_rank_cd (cover density) also considers proximity: adjacent matches rank higher than separated ones. The recommended default for multi-term queries from real users.
• Weights with setweight (A, B, C, D) in the generated column let matches in the title weigh more than matches in the body. Default: {A: 1.0, B: 0.4, C: 0.2, D: 0.1}.
• ts_headline generates highlighted snippets with HTML to show in the UI. Careful: it operates on the raw body, expensive for many rows. Apply it only after the LIMIT.
• The rank isn't absolute, it's relative within a query. Don't filter by "rank > X"; order.
• Optimization for large corpora: a CTE that limits the candidates before computing the rank.

Before moving on you should be able to:

• Write a search endpoint with ts_rank_cd ordered by relevance.
• Add ts_headline for snippets with <mark> and size options.
• Decide between ts_rank and ts_rank_cd based on the type of query and corpus.
• Design setweight weights for a table with several searchable fields.
• Optimize a slow endpoint by limiting the candidates with a CTE.

Next capsule — pg_trgm: fuzzy search and similarity. You have a fast, ranked search engine, but there's still a hole: if the user types "pythn" (a typo), FTS returns nothing because "pythn" isn't in the dictionary. Capsule 06 teaches you the pg_trgm extension, which indexes text by trigrams (3-character sequences) and enables similarity search. You're going to learn to use it for autocomplete with prefix matching, fuzzy search ("pythn" → "Python"), and as a fallback to the main FTS when it returns no results. It's the tool that closes the last gap between your search engine and an Algolia-style one.

Resources

1. PostgreSQL 16 — Ranking Search Results (12.3.3) — the reference for ts_rank, ts_rank_cd, and all the normalization flags.
2. PostgreSQL 16 — Highlighting Results (12.3.4) — the complete ts_headline reference with all its options.
3. Crunchy Data — "Postgres Full-Text Search and Phrase Search" — a deep analysis of ranking and real use cases.
4. Lukas Fittl — "Tuning Search Quality with PostgreSQL Full-Text Search" — tips for improving ranking quality beyond the defaults.
5. SQLAlchemy 2.0 — func.ts_rank and func.ts_headline — how to express the ranking functions from the ORM.
6. Hubert "depesz" Lubaczewski — "Tuning ts_rank" — an advanced analysis of ranking in PostgreSQL.

Module 3 — Advanced PostgreSQL for Backend Guide

Next capsule: pg_trgm — fuzzy search, similarity, and the "main FTS + trigram fallback" pattern.