Production-Ready Full-Text Search in Postgres (No Elasticsearch Required)

Every software product eventually requires a search box. And every search box starts life the same way:

SELECT title FROM movies WHERE plot ILIKE '%heist%';

This works in the demo but fails everywhere else. It can't rank results, so the best match is buried on page four. It doesn't understand language, so a search for "running" misses documents that say "runs". And ILIKE '%...%' can't use a B-tree index, so every query is a sequential scan over your entire corpus. At 3 million rows, this is an outage.

The usual reflex at this point is to reach for Elasticsearch. Before you take on a second data store with its own cluster, its own failure modes, and the eternal joy of keeping it in sync with your database, it's worth knowing that Postgres ships with a genuinely good full-text search engine. For most products, this is not a compromise, but the right tool.

This post builds a real search feature step by step: matching, ranking, indexing, snippets, autocomplete, and typo tolerance, then wires the whole thing into SQLAlchemy. If you want to follow along, I'm using a dataset of 33,535 movies with plot summaries from Wikipedia. The load script and an exercise sheet are linked at the end.

TL;DR — the schema we're building toward:

CREATE TABLE movies (
    id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title  text NOT NULL,
    year   int,
    plot   text NOT NULL,
    search_vec tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', title), 'A') ||
        setweight(to_tsvector('english', plot),  'B')
    ) STORED
);

CREATE INDEX movies_search_idx ON movies USING GIN (search_vec);

-- Query:
SELECT title, ts_rank_cd(search_vec, q) AS rank
FROM movies, websearch_to_tsquery('english', 'bank heist') q
WHERE search_vec @@ q
ORDER BY rank DESC
LIMIT 10;

If that already makes sense to you, skim the SQLAlchemy section and grab the dataset. Otherwise, let's build up to it.

The one idea that makes everything click

Most FTS tutorials jump straight into function names. The reason Postgres search feels arcane is that people skip the mental model, which is actually a single sentence:

Both your documents and your queries are pushed through the same text-processing pipeline, and matching happens on the normalized output, not on the original text.

That pipeline (a text search configuration, e.g. 'english') does three things: lowercases and tokenizes the text, throws away stopwords ("the", "was", "over"), and stems each remaining word to its root form, called a lexeme. You can watch it happen:

SELECT to_tsvector('english', 'The quick brown foxes were jumping over lazy dogs');

'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2

"Foxes" became fox, "jumping" became jump, "lazy" became the slightly alarming lazi, and "the", "were", "over" vanished entirely. The numbers are word positions. These matter for ranking later.

The output type is a tsvector: a sorted list of lexemes with positions. Queries get the same treatment on their way in, producing a tsquery. Because a search for "running" and a document containing "runs" both normalize to the lexeme run, they match. That's it. That's the whole trick. Every function in the FTS toolbox is either feeding this pipeline, scoring its output, or formatting results.

When a search doesn't behave the way you expect, ts_debug('english', 'your text') shows exactly how each token was classified and normalized. It's the first thing to reach for, and the last thing most people learn exists.

A first working search

Three pieces: a function to parse the user's input, the match operator, and a ranking function.

SELECT title, year, ts_rank_cd(to_tsvector('english', plot), q) AS rank
FROM movies, websearch_to_tsquery('english', 'bank heist') q
WHERE to_tsvector('english', plot) @@ q
ORDER BY rank DESC
LIMIT 5;

Against the movie corpus, this returns:

              title               | year |    rank
----------------------------------+------+------------
 Killing Zoe                      | 1994 | 0.11329566
 The Doberman Gang                | 1972 | 0.10166885
 Inside Man                       | 2006 | 0.10106102
 Now You See Me                   | 2013 | 0.09910322
 The Great St. Louis Bank Robbery | 1959 |  0.0922116

Actual heist films, ranked sensibly, from a two-word query. A few things worth unpacking.

websearch_to_tsquery is the input parser you want for anything user-facing. It understands the search syntax people already know, such as "exact phrases", OR, and -excluded . Also critically, it never throws an error on weird input.

Its stricter sibling to_tsquery gives you full boolean and proximity operators (&, |, <->, :*) but will happily raise a syntax error on unbalanced quotes, which is not a property you want connected to a text box on the internet. Use to_tsquery for queries you construct in code, websearch_to_tsquery for everything users type.

@@ is the match operator — vector on one side, query on the other.

ts_rank_cd scores each match. The _cd stands for cover density: it rewards documents where the query terms appear close together, not just frequently. Plain ts_rank exists too, but for multi-word queries cover density almost always produces more intuitive orderings, so I default to it.

The FROM movies, websearch_to_tsquery(...) q construction is a small trick worth stealing: it's an implicit lateral cross join that binds the parsed query to q once, so you're not repeating (and re-parsing) the expression in both the WHERE and the ORDER BY.

Making it fast

The query above has a problem hiding in plain sight: to_tsvector('english', plot) runs on every row, every time. We're re-normalizing the entire corpus per query. You can use EXPLAIN ANALYZE to confirm it. This only gets worse with scale.

The fix has two parts. First, compute the tsvector once per row and store it, using a generated column. This is also the moment to introduce weights because a match in a title should count for more than a match buried in paragraph three:

ALTER TABLE movies
  ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', plot),  'B')
  ) STORED;

setweight tags every lexeme in a vector with a class from A (most important) to D, and || concatenates weighted vectors into one. The ranking functions understand the weights natively. Because the column is GENERATED ... STORED, Postgres maintains it on every insert and update. There is no application code, no trigger, and no sync job that can drift out of date. This is precisely the failure mode that makes bolt-on search engines painful.

Second, index it:

CREATE INDEX movies_search_idx ON movies USING GIN (search_vec);

A GIN (Generalized Inverted Index) is the same fundamental structure Elasticsearch uses under the hood: a mapping from each lexeme to the set of rows containing it. Re-running the search against search_vec:

Before:  Parallel Seq Scan ... Execution Time: ~1400 ms
After:   Bitmap Index Scan on movies_search_idx ... Execution Time: ~8 ms

Two orders of magnitude, one column and one index. And the weighted vector means title matches now surface first — search "godfather" and The Godfather films top the list ahead of every movie whose plot merely mentions one.

Snippets, autocomplete, and typos

A ranked list of titles is a search query. A search feature needs three more things.

Snippets. Users want to see why a result matched. ts_headline takes the original text (not the vector — it needs the real words) and returns an excerpt with matches wrapped in markers of your choosing:

SELECT title,
       ts_headline('english', plot, q,
                   'StartSel=<b>, StopSel=</b>, MaxWords=25') AS snippet
FROM movies, websearch_to_tsquery('english', 'submarine nuclear') q
WHERE search_vec @@ q
ORDER BY ts_rank_cd(search_vec, q) DESC
LIMIT 10;

One important caveat: ts_headline is expensive — it re-parses the document text. Only ever call it on the final page of results, after LIMIT has done its job. If your query shape makes that awkward, select ranked IDs in a subquery first and apply ts_headline in the outer select.

Autocomplete. The :* operator matches lexeme prefixes, which is exactly the shape of search-as-you-type:

SELECT title FROM movies
WHERE search_vec @@ to_tsquery('english', 'assassi:*')
LIMIT 10;

Typos. Here's an honest limitation: FTS has no fuzzy matching. Stemming maps valid word forms together; it does nothing for "Shawhsank". The companion tool is the pg_trgm extension, which compares strings by their three-character fragments:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX movies_title_trgm_idx ON movies USING GIN (title gin_trgm_ops);

SELECT title FROM movies
ORDER BY title <-> 'The Shawhsank Redemtion'
LIMIT 5;

The <-> distance operator, backed by that trigram index, finds The Shawshank Redemption despite two misspellings. A common production pattern: run the FTS query first, and if it comes back empty, fall back to a trigram search over titles as a "did you mean" layer. pg_trgm ships in Postgres contrib, so on any mainstream install or managed service the CREATE EXTENSION line is all it takes.

Wiring it into SQLAlchemy

Everything above is plain SQL, which means it's ORM-agnostic. But almost every FTS writeup stops there and leaves you to figure out the application layer. Here's the full pattern in SQLAlchemy 2.0.

The model, including the generated column and index, so Alembic autogenerate captures the entire setup:

from sqlalchemy import Computed, Index
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.orm import Mapped, mapped_column

class Movie(Base):
    __tablename__ = "movies"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    year: Mapped[int | None]
    plot: Mapped[str]

    search_vec: Mapped[str] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('english', title), 'A') || "
            "setweight(to_tsvector('english', plot), 'B')",
            persisted=True,
        ),
    )

    __table_args__ = (
        Index("movies_search_idx", "search_vec", postgresql_using="gin"),
    )

Computed(..., persisted=True) compiles to GENERATED ALWAYS AS (...) STORED. The search itself is a straightforward composition of func calls:

from sqlalchemy import Select, func, select

def search_movies(term: str, limit: int = 20) -> Select:
    query = func.websearch_to_tsquery("english", term)
    rank = func.ts_rank_cd(Movie.search_vec, query).label("rank")
    snippet = func.ts_headline(
        "english", Movie.plot, query,
        "StartSel=<b>, StopSel=</b>, MaxWords=25",
    ).label("snippet")

    return (
        select(Movie.id, Movie.title, Movie.year, rank, snippet)
        .where(Movie.search_vec.op("@@")(query))
        .order_by(rank.desc())
        .limit(limit)
    )

.op("@@") is the explicit match operator. SQLAlchemy also offers a .match() method, but on Postgres it compiles to plainto_tsquery and hides the choice of parser from you. I prefer keeping it visible. Reusing the same query expression in the filter, the rank, and the snippet is fine; Postgres recognizes the repeated stable function call.

Because the function returns a Select, it composes cleanly with whatever else your endpoint needs. Tenant scoping, year filters and pagination slot directly behind a repository interface. FTS-specific code touches exactly two places: the model definition and this one query builder. Everything else in your stack is unaware search exists.

When you actually do need a search engine

Postgres FTS has real limits, and pretending otherwise would undercut the recommendation. Reach for Elasticsearch, OpenSearch, or Meilisearch when you hit one of these:

Relevance tuning as a product surface. Postgres gives you weights and two rank functions. If your team needs BM25 with per-field boosts, decay functions, and A/B-tested scoring, a dedicated engine earns its keep.

Faceted search at scale. Counting results per category/brand/price-bucket alongside every query is what inverted-index engines with aggregations are built for. You can do it in Postgres; past a certain scale you won't enjoy it.

Heavy multi-language content. Postgres supports many language configurations, but per-document language detection and mixed-language fields get awkward.

Search traffic that competes with your OLTP load. Sometimes the strongest argument for a separate engine isn't features, it's isolation.

If none of those describe your product today, you likely don't have a search problem; you have a Postgres feature you haven't turned on yet. One generated column, one GIN index, ten functions, no second cluster to keep in sync, no dual-write bugs, search results that are transactionally consistent with your data, and one fewer thing paging you at night.

Want to try this hands-on? The exact dataset from this post containing 33,535 movies with plot text presented as as a single psql-loadable dump plus a progressive exercise sheet ending in five unsolved challenges can be found here: movies.sql and here: fts_exercises.sql. If you build something with it, I'd genuinely like to hear how it went.