Multilingual FTS: the `spanish` dictionary and `unaccent`

Capsule description

Almost everything you'll read about FTS in PostgreSQL is written for English. Searches work reasonably well because English is relatively flat: few conjugations, few accents, few morphological variants. For Spanish, reality is different. "Cantar", "canté", "cantó", "cantábamos", "cantando" are five words in the database but a single user intent. "Español" and "espanol" are the same word for whoever is searching, but PostgreSQL treats them as different unless you tell it otherwise.

This capsule teaches you the two components that make PostgreSQL's FTS useful for a Spanish-speaking audience: the spanish dictionary (which already ships with PostgreSQL and applies stemming + stop words in Spanish) and the unaccent extension (which normalizes the characters before stemming). You're going to see, side by side, what happens when you configure it correctly and what happens when you leave the default. The differences are visible in any real corpus.

By the end you'll be able to configure Spanish FTS end-to-end (including creating a custom text search config that combines unaccent + spanish), understand why to_tsvector('english', 'caminando') is practically useless for Spanish text, and predict which lexemes the spanish dictionary produces for any word. It's the heart of the module's differentiator.

Mental model: the processing pipeline of a text search config

When you call to_tsvector('spanish', text), PostgreSQL doesn't do a single thing. It does a chain of steps. Understanding the chain lets you modify it.

┌────────────────────────────────────────────────────────────────────┐
│                                                                    │
│  Raw text: "Las canciones tristes me hacen llorar"                 │
│                          │                                         │
│                          ▼                                         │
│  Step 1: Parser                                                    │
│  Tokenizes the text: splits words, identifies type                 │
│  ['Las', 'canciones', 'tristes', 'me', 'hacen', 'llorar']          │
│                          │                                         │
│                          ▼                                         │
│  Step 2: Dictionary(ies) — for each token, in order                │
│                                                                    │
│  Each dictionary decides:                                          │
│   - Accepts the token and returns its lexeme (transformed)         │
│   - Rejects it (it's a stop word) and discards it                  │
│   - Passes it to the next dictionary in the chain                  │
│                                                                    │
│  If you configure [unaccent, spanish_stem]:                        │
│    'Las'       → unaccent: 'Las' → spanish_stem: stop word ✗       │
│    'canciones' → unaccent: 'canciones' → spanish_stem: 'cancion'   │
│    'tristes'   → unaccent: 'tristes' → spanish_stem: 'trist'       │
│    'me'        → unaccent: 'me' → spanish_stem: stop word ✗        │
│    'hacen'     → unaccent: 'hacen' → spanish_stem: 'hac'           │
│    'llorar'    → unaccent: 'llorar' → spanish_stem: 'llor'         │
│                          │                                         │
│                          ▼                                         │
│  resulting tsvector:                                               │
│    'cancion':2 'hac':5 'llor':6 'trist':3                          │
│                                                                    │
└────────────────────────────────────────────────────────────────────┘

Three ideas:

1. A text search config is a composition. "spanish" is the name of a preconfigured config. Internally it combines a parser + a chain of dictionaries. You can create your own by combining others.

2. The dictionaries process in order. If you put unaccent before spanish_stem, the characters are normalized before stemming. If you put them the other way around, the stemmer sees the unnormalized word and produces a different lexeme from the one its normalized twin produces.

3. Dictionaries can filter (stop words) or transform (stemming, normalization). Each token goes in and comes out (potentially empty) of each dictionary.

With that box in your head, configuring Spanish FTS is assembling the right chain.

What the spanish dictionary does by default

The spanish config that ships with PostgreSQL is defined roughly like this (see pg_ts_config and pg_ts_config_map on any instance):

Config: spanish
Parser: pg_catalog.default
Dictionaries by token type:
  asciiword       → spanish_stem
  word            → spanish_stem
  hword_part      → spanish_stem
  ... (other types for URLs, emails, etc.)

spanish_stem is a "Snowball" dictionary (the Snowball algorithm for Spanish). It does two things:

1. Filters Spanish stop words according to a built-in list (Las, El, Y, O, De, A, Que, En, Es, Me, Se, etc.).
2. Applies Spanish Snowball stemming: reduces the word to its morphological root.

Examples:

-- Verbs: different conjugations, same lexeme
SELECT to_tsvector('spanish', 'cantar canté cantó cantamos cantando cantaré');
-- Output: 'cant':1,2,3,4,5,6

-- Nouns: singular and plural
SELECT to_tsvector('spanish', 'gato gatos perro perros');
-- Output: 'gat':1,2 'perr':3,4

-- Adjectives: gender and number
SELECT to_tsvector('spanish', 'rojo roja rojos rojas');
-- Output: 'roj':1,2,3,4

-- Stop words removed
SELECT to_tsvector('spanish', 'el gato y los perros');
-- Output: 'gat':2 'perr':5

-- The verb "ser": the stop word list eats it whole
SELECT to_tsvector('spanish', 'soy eres es somos son fueron');
-- Output: (empty tsvector)
-- Every form of "ser" is in the spanish dictionary's stop word list,
-- including "fueron". A text made up ONLY of stop words produces an empty tsvector,
-- and an empty tsvector matches nothing.

For typical blog, marketing, or web content text — spanish_stem stemming covers 90%+ of cases. For a very specialized domain (medical, legal with jargon) it may not be enough, but for blogging and general product it's excellent.

A visible comparison: spanish vs english vs simple for Spanish text

-- Text: "Los desarrolladores backend están aprendiendo PostgreSQL avanzado"

SELECT to_tsvector('spanish', 'Los desarrolladores backend están aprendiendo PostgreSQL avanzado');
-- 'aprend':5 'avanz':7 'backend':3 'desarroll':2 'postgresql':6

SELECT to_tsvector('english', 'Los desarrolladores backend están aprendiendo PostgreSQL avanzado');
-- 'aprendiendo':5 'avanzado':7 'backend':3 'desarrollador':2 'están':4 'los':1 'postgresql':6
-- (it doesn't recognize Spanish stop words and doesn't stem Spanish verbs; on top of that
--  it applies English rules to the word: "desarrolladores" → "desarrollador", stripping the
--  English plural 's'. It doesn't help, and it mangles on the way)

SELECT to_tsvector('simple', 'Los desarrolladores backend están aprendiendo PostgreSQL avanzado');
-- 'aprendiendo':5 'avanzado':7 'backend':3 'desarrolladores':2 'están':4 'los':1 'postgresql':6
-- (only lowercase + tokenization: it doesn't touch a single word)

The practical difference: if the user searches "desarrollador" and the document says "desarrolladores", only the spanish config joins them reliably. simple treats them as different words, and english gets it right here by accident (because the Spanish plural happens to coincide with the English one), but it will fail as soon as a verb conjugation shows up.

There's no debate. For Spanish apps, the base config is spanish. That decision is already made.

The character problem: why spanish alone isn't enough

Here comes the nuance that almost no tutorial explains well, and it's worth taking away precisely.

The Spanish Snowball stemmer, as the last step of its algorithm, strips acute accents (á, é, í, ó, ú). That means a good chunk of the "accent problem" is already solved without doing anything:

SELECT to_tsvector('spanish', 'canción cancion');
-- Output: 'cancion':1,2
-- A single lexeme, two positions. "canción" and "cancion" ALREADY are the same word.

SELECT to_tsvector('spanish', 'canción popular')
       @@ websearch_to_tsquery('spanish', 'cancion popular');
-- true  ← it does match, without unaccent

If someone tells you "without unaccent your search engine won't find 'canción' when the user types 'cancion'", they're repeating a myth. Check it yourself: it's true.

So, what is unaccent for? For the two things the stemmer does NOT solve:

1. ñ and ü aren't accents: the stemmer doesn't touch them.

SELECT to_tsvector('spanish', 'español')  AS with_enie,
       to_tsvector('spanish', 'espanol')  AS without_enie;
-- with_enie: 'español'   without_enie: 'espanol'
-- Two DIFFERENT lexemes. They don't match each other.

SELECT to_tsvector('spanish', 'hablar español')
       @@ websearch_to_tsquery('spanish', 'espanol');
-- false  ← here it DOES break

And this isn't an edge case: español, niño, mañana, año, diseño, sueño, pequeño, compañía, enseñar, bilingüe, pingüino. The ñ is everywhere, and it's the letter people most often skip when typing fast on a mobile keyboard.

2. An accented word can stem to a DIFFERENT lexeme than its unaccented twin.

The stemmer strips the accent, but it does it at the end, after deciding which suffix to cut. And that decision can change depending on whether the word came in accented or not:

SELECT to_tsvector('spanish', 'María') AS with_accent,
       to_tsvector('spanish', 'maria') AS without_accent;
-- with_accent: 'mar'   without_accent: 'mari'
-- Different lexemes!

SELECT to_tsvector('spanish', 'María')
       @@ websearch_to_tsquery('spanish', 'maria');
-- false

In other words: even for acute accents, trusting the stemmer is fragile. unaccent canonicalizes the text before the stemmer decides anything, and with that the two forms go in identical and come out identical.

The rule you take away: the stemmer solves accents by accident and incompletely; unaccent solves them by design, and it also fixes ñ and ü. For a Spanish app, unaccent isn't optional.

The unaccent extension

unaccent is an extension that ships with PostgreSQL (the postgresql-contrib package). Enable it in your database:

CREATE EXTENSION IF NOT EXISTS unaccent;

Once enabled, you can use the unaccent(text) function:

SELECT unaccent('canción mañana español');
-- Output: 'cancion manana espanol'

It removes accents, diaereses, eñes (ñ → n), and other Latin diacritics. It's destructive (you lose information) but it's exactly what you want for FTS.

Option A (quick, simple): unaccent applied to the text before to_tsvector

SELECT to_tsvector('spanish', unaccent('hablar español'));
-- Output: 'espanol':2 'habl':1

And in the query:

SELECT to_tsvector('spanish', unaccent('hablar español'))
       @@ websearch_to_tsquery('spanish', unaccent('espanol'));
-- true

This option works and it's the simplest to explain. It has one limitation: you have to remember to apply unaccent on both sides (when saving and when querying). If you forget on one of them, the matches break silently.

Option B (correct, recommended): a custom text search config with unaccent in the pipeline

PostgreSQL lets you create a text search config that combines unaccent with spanish_stem. Once created, you use the config and unaccent is applied automatically.

-- 1. Enable the extension (if you haven't)
CREATE EXTENSION IF NOT EXISTS unaccent;

-- 2. Create a text search config based on spanish
CREATE TEXT SEARCH CONFIGURATION spanish_unaccent (COPY = spanish);

-- 3. Map the relevant token types so they go through unaccent BEFORE spanish_stem
ALTER TEXT SEARCH CONFIGURATION spanish_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, spanish_stem;

Now spanish_unaccent is a new config that applies unaccent and then spanish_stem. You use it exactly like spanish:

SELECT to_tsvector('spanish_unaccent', 'hablar español');
-- Output: 'espanol':2 'habl':1

SELECT to_tsvector('spanish_unaccent', 'hablar español')
       @@ websearch_to_tsquery('spanish_unaccent', 'espanol');
-- true

Why this option is preferable:

• You don't have to apply unaccent() manually in every query.
• The config applies consistently across generated columns, indexes, queries — all with the same name.
• If you decide to change the behavior (add more filters, change the order), you touch a single place.
• The query plan is cleaner (there are no unaccent calls every time).

When to use option A instead:

• One-off migrations where you don't want to modify the global config.
• Isolated tests where you prefer to pass unaccent explicitly for clarity.
• Compatibility with old code that already uses manual unaccent().

For a new app in production, always option B. Consistency beats the effort of creating it.

How the dictionary order works

In ALTER MAPPING ... WITH unaccent, spanish_stem, the order matters:

1. The parser tokenizes the text.
2. Each token enters the first dictionary (unaccent). If the dictionary "accepts" it (transforms it), it returns the result and the chain stops. If it "passes" it, it enters the next dictionary.
3. Key trick: unaccent is configured by default as a "filtering" dictionary — it always transforms (normalizes the characters) and passes the result to the next dictionary instead of accepting.
4. The next dictionary (spanish_stem) receives the already-normalized token. It applies stemming and/or stop word filtering.

The result: each word is normalized first (unaccent) and then stemmed (spanish_stem). If you put the order the other way around, the stemmer would see the unnormalized word and would decide the suffix cut over it — which is exactly the María → 'mar' vs maria → 'mari' case you saw above.

-- ✅ Correct: unaccent → spanish_stem
ALTER TEXT SEARCH CONFIGURATION spanish_unaccent
  ALTER MAPPING FOR word
  WITH unaccent, spanish_stem;

-- ⚠️ Incorrect (rare but possible): spanish_stem → unaccent
-- spanish_stem receives the unnormalized word; the stemmer may give a different lexeme

Worked example: complete setup from Python

We're going to set up the Spanish-with-unaccent text search config from an Alembic migration, and use it from async SQLAlchemy 2.0. It's the setup you'll copy into the module project.

1. Alembic migration to create the extension and the config

# alembic/versions/20260501_add_spanish_unaccent_config.py
"""Adds the unaccent extension and the spanish_unaccent text search config.

Revision ID: 20260501_unaccent
Revises: <previous>
Create Date: 2026-05-01
"""
from alembic import op


revision = "20260501_unaccent"
down_revision = "<previous>"


def upgrade() -> None:
    # 1. Enable the unaccent extension (idempotent)
    op.execute("CREATE EXTENSION IF NOT EXISTS unaccent")

    # 2. Create the config by copying from spanish
    op.execute(
        "CREATE TEXT SEARCH CONFIGURATION spanish_unaccent (COPY = spanish)"
    )

    # 3. Map the token types to unaccent + spanish_stem
    op.execute(
        """
        ALTER TEXT SEARCH CONFIGURATION spanish_unaccent
          ALTER MAPPING FOR hword, hword_part, word
          WITH unaccent, spanish_stem
        """
    )


def downgrade() -> None:
    op.execute("DROP TEXT SEARCH CONFIGURATION IF EXISTS spanish_unaccent")
    # I don't drop unaccent because other things may depend on it

alembic upgrade head

2. Verify the config works

# verify_config.py
import asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine


async def main() -> None:
    engine = create_async_engine(
        "postgresql+asyncpg://postgres:postgres@localhost:5432/fts_demo"
    )

    async with engine.connect() as conn:
        # Compare three configs over the same phrase
        for config in ["simple", "spanish", "spanish_unaccent"]:
            result = await conn.execute(
                text(f"SELECT to_tsvector('{config}', :t) AS tsv"),
                {"t": "Las canciones populares cantadas en español"},
            )
            row = result.first()
            print(f"{config:20s}: {row.tsv}")

    await engine.dispose()


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

Expected output:

simple              : 'canciones':2 'cantadas':4 'en':5 'español':6 'las':1 'populares':3
spanish             : 'cancion':2 'cant':4 'español':6 'popular':3
spanish_unaccent    : 'cancion':2 'cant':4 'espanol':6 'popular':3

The important part:

• simple doesn't remove stop words, doesn't stem, doesn't touch anything.
• spanish removes stop words and stems. Notice that "canciones" already came out as cancion, without the accent: the stemmer stripped the accent on its own. But español keeps the ñ.
• spanish_unaccent does all of the above + normalizes the ñ (español → espanol). That's the only difference from spanish in this phrase — and it's precisely the one that matters.

3. Matching with spanish_unaccent

# search_with_unaccent.py
import asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine


async def search(term: str) -> None:
    engine = create_async_engine(
        "postgresql+asyncpg://postgres:postgres@localhost:5432/fts_demo"
    )
    Session = async_sessionmaker(engine, expire_on_commit=False)

    async with Session() as session:
        # Test data: posts with accents and eñes in the body
        await session.execute(
            text(
                """
                INSERT INTO posts (title, body) VALUES
                  ('Música andina', 'Las canciones tradicionales del Perú y Bolivia'),
                  ('Aprender español', 'Tips para hablar español con fluidez nativa'),
                  ('Recetas con quinoa', 'La quinoa es un grano andino ideal para niños pequeños')
                """
            )
        )
        await session.commit()

        for config in ["spanish", "spanish_unaccent"]:
            result = await session.execute(
                text(
                    f"""
                    SELECT id, title
                    FROM posts
                    WHERE to_tsvector('{config}', body)
                          @@ websearch_to_tsquery('{config}', :q)
                    ORDER BY id
                    """
                ),
                {"q": term},
            )
            rows = result.all()
            print(f"\n[{config}] '{term}': {len(rows)} results")
            for row in rows:
                print(f"  [{row.id}] {row.title}")

    await engine.dispose()


async def main() -> None:
    await search("cancion")   # accent: the stemmer ALREADY solves it
    await search("espanol")   # eñe: only unaccent solves it
    await search("ninos")     # eñe: only unaccent solves it


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

Expected output:

[spanish] 'cancion': 1 results
  [N] Música andina

[spanish_unaccent] 'cancion': 1 results
  [N] Música andina

[spanish] 'espanol': 0 results

[spanish_unaccent] 'espanol': 1 results
  [N] Aprender español

[spanish] 'ninos': 0 results

[spanish_unaccent] 'ninos': 1 results
  [N] Recetas con quinoa

Read that output carefully, because it tells the whole story:

• cancion matches under both configs. The accent wasn't the problem: the stemmer was already stripping it. If your only test had been this one, you would have concluded (wrongly) that unaccent adds nothing.
• espanol and ninos only match with spanish_unaccent. The ñ was the problem, and it's the letter the user drops all the time typing on a phone.

Without unaccent, the user who types "espanol" or "ninos" finds nothing. That's the difference between a useful search engine and a frustrating one — and now you know exactly where it comes from, not as an act of faith.

4. Using it from the SQLAlchemy expression API

from sqlalchemy import func, select


async def search_es(session: AsyncSession, q: str) -> list[Post]:
    """Search posts with Spanish FTS + unaccent."""
    tsv = func.to_tsvector("spanish_unaccent", Post.body)
    tsq = func.websearch_to_tsquery("spanish_unaccent", q)

    stmt = (
        select(Post)
        .where(tsv.bool_op("@@")(tsq))
        .order_by(Post.id)
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

It's the same pattern from capsule 02, with the sole difference of passing 'spanish_unaccent' instead of 'spanish'. The config encapsulates all the processing.

Why does this matter in real work?

1. It's the non-negotiable differentiator for Spanish apps. Your product is for Spanish speakers. Your users type "espanol", "nino", "manana" without thinking. If your search doesn't match "español", the user assumes the content doesn't exist and leaves. That friction doesn't show up in "error" metrics but it does show up in retention metrics.

2. It's the visible difference between your app and mediocre competition. Many Spanish apps built with a generic stack have search broken for eñes because they copied the default setup from an English tutorial. If your product solves this, users notice it even if they can't explain why it feels "more professional."

3. An explicit trade-off you defend in a meeting. Applying unaccent means that "año" (year) and "ano" (anus) are the same word for FTS. That's a loss of information (and an example that gets a laugh, but it's real). The question for your team: is the cost of not distinguishing "año" vs "ano" greater or smaller than the cost of not finding "español" when the user searches "espanol"? For a general product, the first cost is almost always negligible and the second is enormous. That decision, defended with criteria, is senior work.

4. The "custom config vs applying unaccent in every query" decision is a mental pattern that gets reused. The same reasoning (encapsulate processing in a reusable abstraction vs applying it manually) shows up in generated columns, in triggers, in views. Internalizing it in one domain makes you better in the others.

Traps and common mistakes

Mistake 1 (conceptual): believing the myth that spanish doesn't touch accents

Symptom: you repeat "without unaccent, 'cancion' doesn't find 'canción'" because you read it on a blog. Someone on the team tests it, sees that it does match, and concludes that unaccent is unnecessary. You end up without unaccent and with search broken for every word with an ñ.

Why it happens: the myth gets repeated a lot and almost nobody verifies it. The Spanish Snowball stemmer strips acute accents as its last step, so the "canción/cancion" case works by accident.

How to tell: run SELECT to_tsvector('spanish', 'canción cancion');. If you see a single lexeme ('cancion':1,2), the myth is debunked.

Fix: enable unaccent anyway, but for the right reason: the ñ, the ü, and the stemmer's fragility with accented words (María → 'mar', maria → 'mari'). Argue with the español/espanol case, which does break. If you argue with the wrong case and someone refutes it, you lose the discussion while being right.

Mistake 2 (practical): applying unaccent only on the document or only on the query

Symptom: you try unaccent(body) when saving but the query still has to_tsvector('spanish', :q) without unaccent. Result: the document has espanol and the query searches for español, no match.

Why it happens: you forgot to update the query after changing the column, or you copy-pasted from the official docs without understanding the required symmetry.

How to detect it: an integration test: insert a post with español, search for espanol, expect a match. If it doesn't match, unaccent is missing on one of the two sides.

Fix: use the spanish_unaccent text search config (option B), which applies unaccent automatically on both sides. It ensures symmetry with no effort.

Mistake 3 (practical): forgetting CREATE EXTENSION unaccent before creating the config

Symptom: the migration fails with ERROR: text search dictionary "unaccent" does not exist.

Why it happens: the ALTER MAPPING ... WITH unaccent, spanish_stem references the unaccent dictionary, which only exists after CREATE EXTENSION unaccent.

Fix: make sure CREATE EXTENSION IF NOT EXISTS unaccent runs before the CREATE TEXT SEARCH CONFIGURATION ... ALTER MAPPING. In Alembic, put it as the migration's first operation.

Mistake 4 (conceptual): thinking the unaccent, spanish_stem order can be inverted

Symptom: someone skims the docs and writes WITH spanish_stem, unaccent. The stemmer receives unnormalized words. For some it works, for others it doesn't, giving inconsistent results.

Why it's confusing: the dictionary order isn't alphabetical or optional. It's the processing order.

How to tell: the María case shows it unambiguously. With unaccent, spanish_stem, "María" and "maria" produce the same lexeme ('mari') and they match. With the inverted order, the stemmer decides the cut over the accented word ('mar') and only afterwards does someone strip the accent from a lexeme that already came out different: they don't match.

Fix: always WITH unaccent, spanish_stem. Document the reason in a comment in the migration in case someone refactors it later.

Mistake 5 (practical): changing the config without reindexing the generated columns

Symptom: you have a tsv tsvector GENERATED ALWAYS AS (to_tsvector('spanish', body)) STORED column. You change the code to spanish_unaccent. The new rows use spanish_unaccent, the old ones stay with spanish. Inconsistent searches.

Why it happens: the GENERATED ALWAYS AS clause is evaluated when inserting/updating the row. Changing the expression does NOT recompute the existing rows automatically.

Fix: after changing the config in the generated column, do a forced UPDATE to regenerate:

-- Change the generated column's expression (drop + add, or ALTER)
ALTER TABLE posts DROP COLUMN tsv;
ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('spanish_unaccent', body)) STORED;

-- The existing rows are regenerated automatically when adding the new column
-- (because GENERATED is evaluated on INSERT, and every row is "reinserted" on the ADD COLUMN)

Verify with a SELECT after the ALTER that the tsv of an old row reflects the new config.

Mistake 6 (conceptual): thinking unaccent also affects the tsquery if you apply it in the tsvector

Symptom: "I already enabled unaccent in the config, I don't need to apply it in the query." But the query uses 'spanish' (not 'spanish_unaccent'), so the left side of the @@ is normalized and the right side isn't. No match.

Why it's confusing: the config applies in the place where you invoke it. If you invoke 'spanish_unaccent' in to_tsvector but 'spanish' in websearch_to_tsquery, the two sides aren't symmetric.

How to detect it: SELECT to_tsvector('spanish_unaccent', 'español') @@ websearch_to_tsquery('spanish', 'español'); — it returns false even though it looks obvious that it should match. (The left side produces espanol; the right side, español.)

Fix: the same config on both sides. If you declare a variable or helper for the config, you ensure symmetry:

TS_CONFIG = "spanish_unaccent"

stmt = select(Post).where(
    func.to_tsvector(TS_CONFIG, Post.body).bool_op("@@")(
        func.websearch_to_tsquery(TS_CONFIG, q)
    )
)

Exercises

Exercise 1: predict the output based on the config

Without running the SQL, predict the output of each one:

SELECT to_tsvector('simple', 'Niños pequeños');
SELECT to_tsvector('spanish', 'Niños pequeños');
SELECT to_tsvector('spanish_unaccent', 'Niños pequeños');
SELECT to_tsvector('spanish_unaccent', 'Mañana es el cumpleaños de María');

-- 1
to_tsvector
-------------------
 'niños':1 'pequeños':2
-- simple: lowercase, no stemming, keeps accents and eñes.

-- 2
to_tsvector
---------------------
 'niñ':1 'pequeñ':2
-- spanish: stemming with the eñes preserved (the stemmer doesn't touch the ñ).

-- 3
to_tsvector
---------------------
 'nin':1 'pequen':2
-- spanish_unaccent: unaccent turns ñ → n, then stemming.

-- 4
to_tsvector
-----------------------------------
 'cumplean':4 'manan':1 'mari':6

-- "es", "el", "de" → stop words, removed.
-- "Mañana"     → unaccent: 'manana'     → stem: 'manan'
-- "cumpleaños" → unaccent: 'cumpleanos' → stem: 'cumplean'
-- "María"      → unaccent: 'maria'      → stem: 'mari'
-- Careful: the stemmer keeps trimming AFTER unaccent. The lexemes aren't
-- the words without accents, they're the words without accents AND stemmed.
-- Positions are counted over the original tokens (1..6), not over the surviving ones.

Exercise 2: fix an FTS broken by a config mismatch

A teammate shows you this code and says "it doesn't work, it returns nothing when the user searches without eñes":

async def search(session: AsyncSession, q: str) -> list[Post]:
    stmt = (
        select(Post)
        .where(
            func.to_tsvector("spanish_unaccent", Post.body)
            .bool_op("@@")(func.to_tsquery("spanish", q))
        )
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

Identify the two problems and fix them.

TS_CONFIG = "spanish_unaccent"


async def search(session: AsyncSession, q: str) -> list[Post]:
    stmt = (
        select(Post)
        .where(
            func.to_tsvector(TS_CONFIG, Post.body)
            .bool_op("@@")(func.websearch_to_tsquery(TS_CONFIG, q))
        )
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

Exercise 3: create the config in an idempotent Alembic migration

Write an Alembic migration that is idempotent (it can be run multiple times without failing): it enables unaccent, creates spanish_unaccent only if it doesn't exist, and applies the ALTER MAPPING. The downgrade only drops the config (not the extension).

# alembic/versions/20260501_add_spanish_unaccent.py
"""Adds the spanish_unaccent FTS config.

Revision ID: 20260501_unaccent
Revises: <prev>
Create Date: 2026-05-01
"""
from alembic import op


revision = "20260501_unaccent"
down_revision = "<prev>"


def upgrade() -> None:
    # Idempotent extension
    op.execute("CREATE EXTENSION IF NOT EXISTS unaccent")

    # Create the config only if it doesn't exist (PostgreSQL has no native IF NOT EXISTS for text search configs)
    op.execute(
        """
        DO $$
        BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM pg_ts_config WHERE cfgname = 'spanish_unaccent'
          ) THEN
            CREATE TEXT SEARCH CONFIGURATION spanish_unaccent (COPY = spanish);
            ALTER TEXT SEARCH CONFIGURATION spanish_unaccent
              ALTER MAPPING FOR hword, hword_part, word
              WITH unaccent, spanish_stem;
          END IF;
        END
        $$;
        """
    )


def downgrade() -> None:
    op.execute("DROP TEXT SEARCH CONFIGURATION IF EXISTS spanish_unaccent")

Exercise 4: measure the config's impact on a real query

Take your posts table (with at least 10 rows containing accents and eñes in their bodies). For each of these three queries, count how many results it returns and why:

-- Query 1
SELECT count(*) FROM posts
WHERE to_tsvector('simple', body) @@ websearch_to_tsquery('simple', 'espanol');

-- Query 2
SELECT count(*) FROM posts
WHERE to_tsvector('spanish', body) @@ websearch_to_tsquery('spanish', 'espanol');

-- Query 3
SELECT count(*) FROM posts
WHERE to_tsvector('spanish_unaccent', body) @@ websearch_to_tsquery('spanish_unaccent', 'espanol');

Assume that in your corpus some posts have "español" and others have "espanol", and that your corpus has unrelated posts.

Summary and next step

In this capsule you learned the setup that separates a useful search engine from a frustrating one for Spanish-speaking users:

• The default spanish config applies Spanish Snowball stemming + stop word filtering. It reduces conjugations to their root: cantando, canté, cantar → cant. It's the non-negotiable base.
• The spanish stemmer already strips acute accents as the last step of its algorithm: canción and cancion produce the same lexeme without anyone's help. The myth of "without unaccent, canción doesn't match" is false, and it's worth knowing that before you defend it in a meeting.
• What the stemmer does NOT solve is ñ and ü (they aren't accents, they're letters): español ≠ espanol, niño ≠ nino. And on top of that, its handling of accents is fragile: María → 'mar' but maria → 'mari', so they don't even match each other.
• The unaccent extension normalizes accents, diaereses, and eñes. Applying it before stemming in a custom text search config (spanish_unaccent) solves both things permanently.
• Creating spanish_unaccent with CREATE TEXT SEARCH CONFIGURATION + ALTER MAPPING gives you a reusable config that applies unaccent automatically in any to_tsvector or websearch_to_tsquery.
• Mandatory symmetry: if you use spanish_unaccent on the document, use it on the query too. The same config on both sides of the @@.
• Encapsulating the config in a constant (TS_CONFIG = "spanish_unaccent") in your code prevents accidental asymmetries.

Before moving on you should be able to:

• Create the unaccent extension and the spanish_unaccent config in an Alembic migration.
• Predict the output of to_tsvector('spanish_unaccent', 'any phrase').
• Defend the "enable unaccent" decision with the right argument (the ñ), not with the accent myth.
• Diagnose an FTS that doesn't match because of a config mismatch between tsvector and tsquery.

Next capsule — GIN indexes for FTS and generated columns. So far your FTS works but it does a Seq Scan on every query: PostgreSQL calls to_tsvector(body) for each row when filtering. For 100 rows you barely notice. For 100k rows the endpoint takes seconds. Capsule 04 teaches you the modern pattern: a generated column tsv tsvector GENERATED ALWAYS AS (to_tsvector('spanish_unaccent', body)) STORED with a GIN index on top. You're going to see the EXPLAIN ANALYZE before (Seq Scan) and after (Bitmap Index Scan). It's the moment where FTS goes from "works in a demo" to "works in production."

Resources

1. PostgreSQL 16 — Text Search Configurations (12.7) — the reference for creating and modifying text search configs.
2. PostgreSQL 16 — unaccent extension — the complete extension with all its edge cases (non-Latin characters, custom rules).
3. PostgreSQL 16 — Snowball Stemmer Dictionary — how the stemming algorithm used by spanish_stem works internally.
4. Crunchy Data — "Multi-language Full-Text Search" — covers multilingual setup including custom configs.
5. Snowball — Stemming Algorithm Reference (Spanish) — the academic reference for the stemming algorithm. The final step that strips acute accents is written right there, in full. Useful for understanding which cases it covers and which it doesn't.
6. Stack Overflow — PostgreSQL FTS with unaccent: best practices — the classic discussion with community use cases.

Module 3 — Advanced PostgreSQL for Backend Guide

Next capsule: GIN indexes for FTS + generated columns — the pattern that takes your FTS from Seq Scan to Bitmap Index Scan.