Module 3: Full-Text Search + pg_trgm — a quality search engine without Elasticsearch

In module 2 you closed out JSONB with SQLAlchemy: your Blog API already stores flexible metadata, validates payloads with Pydantic, and applies the three canonical patterns (dynamic config, extensible metadata, polymorphic data). You know how to store semi-structured data without giving up the ORM or integrity. But there's a natural question that shows up as soon as the first user lands on the site: how do you let that user search inside the posts?

Most teams' default answer is the same: "let's add Elasticsearch." And with that decision come three things that often aren't needed: a new service to maintain, a double-write with its consistency problem, and extra operations (cluster, indexes, reindex jobs). In most cases, PostgreSQL FTS is enough — and it's transactional, it lives in the same database, and it doesn't add a single dependency to the stack. This module teaches you to make that decision with criteria, not out of fear that "PostgreSQL isn't for search."

By the end of the module, your Blog API will have a Spanish-language search engine with correct stemming (search "cantando" and it matches "canta", "cantó", "cantar"), accent-tolerant (search "cancion" and it matches "canción"), typo-tolerant (search "pythn" and it returns "Python"), with results ordered by relevance and highlighted snippets for the UI. All using only PostgreSQL 16+.

Where are we? Where are we going?

You're in Block 2: Search in PostgreSQL of the Advanced PostgreSQL for Backend guide (#14 of the Backend Python with FastAPI path). It's a single-module block because FTS and pg_trgm are almost always used together in production, and separating them would force redundancy.

Block 1: Deep JSONB
├── Module 1: JSONB Operators and Indexing      ← done
└── Module 2: JSONB with SQLAlchemy + Patterns  ← done

Block 2: Search in PostgreSQL
└── Module 3: Full-Text Search + pg_trgm        ← you are here

Block 3: Scale
├── Module 4: Native Partitioning                ← next
└── Module 5: Materialized Views

Blocks 4-5: Concurrency, Extensions, CTEs

Module 2 gave you the "what" of JSONB with SQLAlchemy. This module gives you the "how do I search inside my data without adding a new service." The narrative connection is direct: your metadata is already flexible, now your content becomes searchable. And without paying the Elasticsearch cost.

Professional objective

By the end of this module you'll be able to:

Distinguish tsvector from tsquery and know when to use each converter (to_tsvector, to_tsquery, plainto_tsquery, phraseto_tsquery, websearch_to_tsquery).
Configure FTS in Spanish with the spanish dictionary (stop words, stemming) and the unaccent extension so that "cancion" matches "canción".
Index tsvector with GIN and keep it up to date automatically with generated columns (GENERATED ALWAYS AS ... STORED).
Order results by relevance with ts_rank and ts_rank_cd, and know why ts_rank_cd almost always wins for results that feel natural.
Highlight matches in snippets with ts_headline, ready to show in the UI.
Combine FTS with pg_trgm to tolerate typos, do a fuzzy fallback, and build autocomplete with prefix matching.
Defend the "FTS or Elasticsearch" decision in a technical meeting with concrete criteria: volume, query complexity, real-time, aggregations, operations.
Express all of it from async SQLAlchemy 2.0 with FastAPI 0.110+, without dropping to raw SQL more than when it adds clarity.

Why does this module matter?

Search is one of the most expensive features to implement badly. Three concrete scenarios where you need to know this:

1. The team is debating whether to add Elasticsearch to the stack. The product manager asked for search. The tech lead proposes Elasticsearch because "it's the standard." You show up with numbers: the database has 2 million posts, the queries are keyword search plus simple filters, there's no geospatial aggregation or complex faceted search. PostgreSQL FTS solves it in <50ms with a correct GIN index. The team saves itself the cluster, the double-write, and the oncall for the new service. That decision, defended with criteria, is worth more than knowing the whole Elasticsearch API.

2. The current search doesn't find obvious results. The user searches "como aprender python" and it doesn't return a post titled "Python para principiantes" because someone implemented it with LIKE '%python%'. No stemming, no dictionary, no ranking. You rewrite it with to_tsvector('spanish', ...) + websearch_to_tsquery + ts_rank_cd and user feedback changes overnight.

3. The product needs typo-tolerant autocomplete. The search input fires requests on every keystroke. If the user types "pythn" you have to return relevant suggestions, not an empty result. You combine FTS for the main search and pg_trgm with similarity() or a GiST index for the fuzzy fallback. The user gets results even when typing badly — without Algolia, without Typesense, without anything external.

If you finish the module knowing how to apply Spanish FTS + pg_trgm and how to defend the "FTS vs Elasticsearch" decision with criteria, you become the dev on the team who gets handed the ticket "evaluate whether search stays in PostgreSQL or needs a separate service." That skill gets paid differently.

A scenario that illustrates the module

Imagine you arrive on Monday and the product manager tells you: "I want search on the blog. It should work in Spanish, order by relevance, tolerate typos, and show the result with the searched word highlighted." With no budget for new services.

With what you'll learn in this module, your plan is:

Capsule 02: you understand tsvector and tsquery with Spanish examples. You convert a post's body to tsvector and do your first match with @@. You've got the basic lego piece.
Capsule 03: you discover that to_tsvector('spanish', 'caminando') reduces the word to camin (correct stemming), that to_tsvector('english', 'caminando') does nothing (it doesn't know Spanish), and that without unaccent "cancion" doesn't match "canción". You enable unaccent and the search engine stops frustrating users.
Capsule 04: you add a generated column tsv tsvector GENERATED ALWAYS AS (to_tsvector('spanish', unaccent(title || ' ' || body))) STORED and a GIN index over tsv. You measure EXPLAIN before and after: the query goes from a Seq Scan at 800ms to a Bitmap Index Scan at 12ms.
Capsule 05: the results show up but the order is random. You apply ts_rank_cd to order by relevance (matches that are close together weigh more). The first search result is now the right one, not "the most recent one that happens to mention the word."
Capsule 06: someone searches "pythn" and it comes back empty. You enable pg_trgm with CREATE EXTENSION pg_trgm, add a GiST index over the title, and build a fallback with similarity(title, 'pythn') > 0.3. The user who typed it wrong still finds results.
Capsule 07: the tech lead asks "shouldn't we use Elasticsearch for this?". You show up with the module's decision matrix: 2M posts, simple queries, no faceted search → PostgreSQL FTS wins. You defend the decision with numbers, not with an opinion.
Capsule 08: the module project. You build a complete Spanish-language search engine over posts: generated column, GIN index, a FastAPI endpoint with ?q=... that returns ranked results with highlighted snippets, a fuzzy fallback with pg_trgm when FTS returns nothing, and autocomplete with prefix matching. It's the exact component that later gets integrated into the Blog API refactor in module 8.

Each capsule is a concrete piece of the solution. It isn't isolated theory — it's the chain that goes from "I don't know what a tsvector is" to "I have a search engine in production that ranks well and survives typos."

Module map

Capsule	Topic	What you'll learn
01	Module introduction (this capsule)	The "why" of the module, the "FTS vs Elasticsearch" decision as the central theme
02	`tsvector` and `tsquery`: fundamentals	Converting text to normalized tokens, writing queries with `@@`, the five query converters
03	Multilingual FTS: the `spanish` dictionary and `unaccent`	Stemming in Spanish, handling accents, why the `simple` default isn't enough
04	GIN indexes for FTS and generated columns	Indexing `tsvector`, keeping it automatic with `GENERATED ALWAYS AS ... STORED`, EXPLAIN before/after
05	Ranking with `ts_rank` and `ts_rank_cd`	Ordering by relevance, the difference between frequency and cover density, weights with `setweight`
06	`pg_trgm`: fuzzy search and similarity	Typo tolerance, GiST/GIN indexes, combining FTS + `pg_trgm` as a fallback
07	FTS vs Elasticsearch: when PostgreSQL is enough	A decision matrix with concrete criteria, cases where Elasticsearch still wins
08	Project: the blog's Spanish-language search	A complete `/search?q=...` endpoint, ranking, snippets with `ts_headline`, autocomplete with `pg_trgm`

Connection with the integrative project

This module is the direct foundation of the "Spanish FTS over posts.title + posts.body" component of the final project in module 8. The mini-project (capsule 08) is functionally identical to that component of the final project — the context changes but the techniques are the same:

A generated tsv column with to_tsvector('spanish', unaccent(...)).
A GIN index over tsv.
An endpoint that receives ?q=..., runs it through websearch_to_tsquery, orders with ts_rank_cd, and returns snippets with ts_headline.
A fallback with pg_trgm for fuzzy queries.
Autocomplete with prefix matching.

The decisions you make here (ts_rank or ts_rank_cd? separate columns for title and body with different weights? unaccent before or after stemming?) are the same ones that get replicated in the Blog API refactor. Take advantage of this module to make them with time on your side.

What is NOT covered in this module

❌ Deep JSONB — covered in modules 1-2 of the previous block. If you need to index JSONB with GIN, that's the place.
❌ Partitioning — comes in module 4. Partitioning a table with FTS is a valid topic but it belongs to the scale block.
❌ Materialized views — come in module 5. If you need to pre-compute aggregated search results, that's the pattern.
❌ pgvector and semantic embeddings — outside the scope of this guide. It belongs to the AI Engineering path. FTS and embeddings solve different problems: FTS is keyword search, embeddings is semantic search ("find conceptually similar posts even if they don't share words"). If your product needs semantic search, that's another guide.
❌ Distributed or sharded search — outside the scope. If your corpus goes past 50M docs and you need to distribute, that's where Elasticsearch or equivalents do win.
❌ Custom dictionaries and thesaurus — the spanish dictionary covers 95% of the cases. Creating custom dictionaries (synonyms, domain jargon) is an advanced topic covered in the official docs and left out of the module.

Traps to avoid while taking the module

Don't skip capsule 03 even if it sounds "small." It's the difference between a useful search engine and one that frustrates the user. The spanish dictionary and unaccent are the heart of the module's differentiator. If you only skim it, you come out knowing FTS but not FTS for a Spanish-speaking audience — which is exactly what you need.
Don't confuse pg_trgm with FTS. They're different tools for different problems. FTS searches for known words with stemming. pg_trgm searches by character similarity (trigrams). The correct pattern is to combine them, not to choose one. Capsule 06 makes this explicit.
Don't memorize to_tsquery before websearch_to_tsquery. Any example in the official docs shows to_tsquery('palabra & otra') with operator syntax. But the end user doesn't type & or | into an input — they type "como aprender python". websearch_to_tsquery (PG 11+) accepts the Google format and it's what you're going to use 95% of the time. Capsule 02 presents it as the default; resist the temptation to default to to_tsquery.
Don't settle for triggers to maintain tsvector. Pre-PG 12 you had to create a trigger to keep the tsv column up to date. Today GENERATED ALWAYS AS ... STORED solves it without a trigger, without duplicated code, and without the risk of falling out of sync. Capsule 04 shows the modern pattern; using it from the start saves you the legacy.
Don't make the "FTS vs Elasticsearch" decision without measuring. "It's PostgreSQL, it's going to be slow" is an opinion. "I have 2M docs, average query 35ms with a GIN index over tsv" is data. Capsule 07 gives you criteria for measuring and deciding. Take them to your team instead of arguing with anecdotes.

Self-assessment question

Before starting this module, can you answer?

What makes LIKE '%python%' different from FTS and why doesn't LIKE scale?
What is a GIN index and how does it differ from a B-tree? (Covered in module 1.)
In your current Blog API, how is search implemented? If it's LIKE or ILIKE, what happens when your posts table reaches 100k rows?
What does your team think about adding Elasticsearch to the stack? Is there an opinion based on measurement or just "it's the standard"?

If you hesitate on the first or second, review capsule 04 of module 1 (GIN indexes for JSONB) — the mechanics of GIN are the same for FTS. If you hesitate on the third or fourth, perfect: this module gives you the answers with criteria.

Evidence of success

By the end of the module, you'll know you succeeded if:

✅ You can write to_tsvector('spanish', unaccent(text)) without consulting the docs.
✅ You know why to_tsvector('english', 'caminando') does nothing and to_tsvector('spanish', 'caminando') reduces to camin.
✅ Your local Blog API has a tsv tsvector GENERATED ALWAYS AS (...) STORED column with a GIN index.
✅ Your /search?q=... endpoint returns results ranked with ts_rank_cd and snippets highlighted with ts_headline.
✅ If the user types "pythn", you return results with pg_trgm as a fallback.
✅ You can defend the decision "PostgreSQL FTS is enough for our case" or "we need Elasticsearch" with concrete criteria (volume, queries, real-time, aggregations).

We start in the next capsule

We start with capsule 02: tsvector and tsquery from scratch. You're going to understand how PostgreSQL converts text to tokens, what a lexeme is, why stemming matters, and how to put together your first match with @@. It's the base the whole module builds on.

Before moving on, make sure you have PostgreSQL 16+ installed locally (or in Docker), a test database with the posts table of the Blog API you've been refactoring, and at least 20-30 posts with Spanish content to test the queries. If you need seed data, I leave you a script in capsule 02.

Resources for the module

PostgreSQL 16 — Full Text Search (chapter 12) — the canonical reference. Read it as a lookup dictionary, not cover to cover.
PostgreSQL 16 — pg_trgm — the extension for fuzzy and similarity, with all its operators and functions.
PostgreSQL 16 — unaccent — the extension for accent normalization, fundamental for Spanish.
Crunchy Data — "Indexing PostgreSQL Full-Text Search" — a complete review with a production focus.
Supabase Docs — Full-Text Search — a modern perspective with real use cases and a comparison with Elasticsearch.
Hubert "depesz" Lubaczewski — "Waiting for PostgreSQL: websearch_to_tsquery" — why websearch_to_tsquery is the converter you're going to use.
SQLAlchemy 2.0 — PostgreSQL dialect: TSVECTOR/TSQUERY — how to express FTS from the ORM.

Module 3 — Advanced PostgreSQL for Backend Guide

Next capsule: tsvector and tsquery — the two types that make all of the FTS machinery possible.

← PreviousModule project: extend the Blog API with `posts.metadata` JSONB Next →`tsvector` and `tsquery`: the two types that make FTS possible