Full-text search in Postgres

Product wants a search box above the invoices list: type a few words, get the matching invoices back, ranked by relevance. You know ilike from the CRUD lesson, so you reach for it:

db.select()
  .from(invoices)
  .where(ilike(invoices.description, `%${term}%`));

It works until a user tries the obvious things:

• Search invoices and the row that says “invoice” never appears: %invoices% doesn’t know “invoice” and “invoices” are the same word.
• Search overdue payment and the row that says “payment overdue” is missing: a substring match can’t reorder words.
• The leading % blocks the index, so Postgres scans every row and the box slows as the table grows.
• Search the and every invoice comes back, because %the% matches “theme”, “other”, and “weather”.

This is the wrong tool, not a bug to patch. ilike matches characters, but search needs to match words with all their messiness: plurals, tenses, word order, and noise words like “the”. That is what Postgres full-text search does, in the box.

Build search on Postgres, or buy a service like Algolia or Meilisearch? For most apps in 2026, reach for Postgres first. Its full-text search handles a corpus into the low millions of documents at modest query rates, which covers almost every SaaS already running Postgres. By the end of this lesson you’ll have a ranked, highlighted, injection-safe search query running against Postgres, and you’ll know the line where you’d leave it.

Lexemes, tsvectors, and tsqueries

Full-text search compares normalized words, not strings: it reduces both the stored text and the search term to the same shape before matching. That is three terms and one operator.

A lexeme is a normalized word stem: running, ran, and runs all reduce to run. Lowercasing, stripping plurals and tenses, and dropping noise words is what lets a search match meaning instead of characters.

A tsvector is a document already broken into its lexemes. to_tsvector('english', 'The cats are running') returns 'cat':2 'run':4: the and are are dropped as noise, cats becomes cat, running becomes run, and each surviving lexeme keeps its position.

A tsquery is a search expression in that same lexeme space. Against the vector above, 'run' matches, 'cat & run' matches (both lexemes are present), and 'dog' does not.

The @@ operator connects them: tsvector @@ tsquery returns a boolean, does this document contain these search lexemes?

stored document “The cats are running”

→

normalizer to_tsvector('english', …)

→

cat _:2 run _:4

stop words dropped, words stemmed

search term “running cat”

→

normalizer websearch_to_tsquery('english', …)

→

catrun

user input, same normalization

@@ → ✓ match

Both the stored text and the search term are normalized into lexemes, then compared with `@@`. The match works on meaning, not characters.

The 'english' argument picks the text search configuration , which sets the stemming rules and the stop word list. The 'simple' config does no stemming and keeps every word, which suits near-exact tokens like SKUs or code identifiers, where running should not collapse into run. For a single-language web app, hardcode 'english'; a multilingual one would store the language per row and pass it in dynamically.

Caution

Because 'english' stems and drops stop words, full-text search is not substring or exact match. A search for running matches a document containing “run”, which is what you want. But a search for a bare stop word like the matches nothing, because that lexeme was discarded on both sides. If your product needs exact-token or identifier search, use the 'simple' config or pg_trgm, covered at the end.

A first search, with the vector built per row

Start with the simplest version that works, the unoptimized one, so the fix in the next section lands against a problem you’ve seen.

The query builds the vector right in the where clause, from the two columns you want to search, description and customerName:

db.select()
  .from(invoices)
  .where(
    sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, '')) @@ websearch_to_tsquery('english', ${term})`,
  );

The left side builds a tsvector on the fly, concatenating the two columns with || (SQL string concat). Since description is nullable, coalesce(description, '') swaps a null for an empty string so one null doesn’t collapse the whole concat to null.

db.select()
  .from(invoices)
  .where(
    sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, '')) @@ websearch_to_tsquery('english', ${term})`,
  );

The match operator asks “does this row’s text contain the search lexemes?” and returns a boolean.

db.select()
  .from(invoices)
  .where(
    sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, '')) @@ websearch_to_tsquery('english', ${term})`,
  );

The right side turns the user’s raw search string into a tsquery. Inside a sql template, ${term} binds as a $1 parameter, the same injection-safe path as any eq(col, value): the user’s text never becomes SQL structure.

db.select()
  .from(invoices)
  .where(
    sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, '')) @@ websearch_to_tsquery('english', ${term})`,
  );

The whole expression lives inside an ordinary Drizzle where. The builder owns the query’s structure; the sql template fills in this one predicate.

← Previous 1 / 1 Next →

This runs, but look at what it asks Postgres to do: to_tsvector(...) executes once per row, on every search. Each keystroke makes Postgres re-read and re-tokenize every invoice, and no index can help, because the thing being matched doesn’t exist until the query runs. A few thousand rows turn it into a sequential scan that drags; at real volume it stops responding. The next section fixes exactly this.

One decision in that query is worth a pause: which function turns user input into a tsquery. Only one is safe to point at a raw search box.

to_tsquery (raw input → throws)

-- user typed: overdue payment
to_tsquery('english', 'overdue payment')
-- ERROR: syntax error in tsquery: "overdue payment"

to_tsquery expects operator syntax, not human text. A bare space is a syntax error; it wanted overdue & payment, and it passes operator characters (&, |, !, :) straight through. Point it at a search box and it throws on the first multi-word query. Use it only for queries you build yourself from trusted parts.

websearch_to_tsquery (raw input → safe)

-- user typed: overdue payment
websearch_to_tsquery('english', 'overdue payment')
-- → 'overdue' & 'payment'   (parsed, no error)

websearch_to_tsquery parses Google-style input safely. Bare words get AND-ed, "quoted phrases" stay together, or does what you’d expect, -word excludes. It never throws on arbitrary input and exposes no operator syntax. This is the only function you point at a raw search box.

So websearch_to_tsquery goes on user input; to_tsquery is for server-built queries assembled from parts you control. The coalesce guarding the nullable column is just coalesce , a one-line guard, not a topic of its own.

Watch video

Supabase walks the same arc in under five minutes: to_tsvector, websearch_to_tsquery, the generated column with a GIN index, and ts_rank.

Store the tsvector in a generated column

The fix for re-tokenizing every row on every query is to tokenize each row once, at write time, and store the result. This is the STORED generated column from the schema chapter: computed when the row is written, kept in sync automatically, here recomputed by Postgres on every insert and update rather than by the query.

Drizzle ships no tsvector column builder, so you define the type once with customType:

import { customType } from 'drizzle-orm/pg-core';

const tsvector = customType<{ data: string }>({
  dataType() {
    return 'tsvector';
  },
});

customType takes the TypeScript shape of the value and a dataType() returning the literal SQL type name. Now tsvector(...) is a column builder like any other, and the column reads like the inline expression from before, attached to the schema instead of the query:

const tsvector = customType<{ data: string }>({
  dataType() {
    return 'tsvector';
  },
});

export const invoices = pgTable('invoices', {
  // …existing columns
  searchVector: tsvector('search_vector')
    .notNull()
    .generatedAlwaysAs(
      (): SQL =>
        sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, ''))`,
    ),
});

The custom type, defined once. { data: string } is the TypeScript shape (a tsvector reads as a string in JS) and dataType() returns the SQL type name. Import customType from drizzle-orm/pg-core.

const tsvector = customType<{ data: string }>({
  dataType() {
    return 'tsvector';
  },
});

export const invoices = pgTable('invoices', {
  // …existing columns
  searchVector: tsvector('search_vector')
    .notNull()
    .generatedAlwaysAs(
      (): SQL =>
        sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, ''))`,
    ),
});

generatedAlwaysAs takes a callback returning a SQL expression, the same vector-building expression as the query-time version, now run at write time. sql comes from drizzle-orm; the explicit : SQL return type needs import type { SQL } from 'drizzle-orm', since the project’s verbatimModuleSyntax requires import type for type-only imports.

const tsvector = customType<{ data: string }>({
  dataType() {
    return 'tsvector';
  },
});

export const invoices = pgTable('invoices', {
  // …existing columns
  searchVector: tsvector('search_vector')
    .notNull()
    .generatedAlwaysAs(
      (): SQL =>
        sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, ''))`,
    ),
});

The same null guard as before: description can be null, so coalesce(col, '') keeps the concatenation from collapsing to null. Only when the logic runs changed.

const tsvector = customType<{ data: string }>({
  dataType() {
    return 'tsvector';
  },
});

export const invoices = pgTable('invoices', {
  // …existing columns
  searchVector: tsvector('search_vector')
    .notNull()
    .generatedAlwaysAs(
      (): SQL =>
        sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, ''))`,
    ),
});

The coalesce guarantees a value even when both source columns are empty, so the vector is always derivable and the column is NOT NULL. Every row has a searchable vector.

← Previous 1 / 1 Next →

A generated column can never drift: every insert and update recomputes the vector straight from description and customerName, so no trigger and no application code has to remember to rebuild it after an edit. Older Postgres code did this with a BEFORE INSERT OR UPDATE trigger calling to_tsvector. Recognize it in legacy code, but don’t write it.

A tsvector column is only fast to query with a GIN index behind it. Without one, Postgres still scans every row’s vector: you’ve moved tokenization off the query path but not the scan. The index looks like this:

(t) => [index('idx_invoices_search_vector_gin').using('gin', t.searchVector)];

The next chapter covers declaring and tuning indexes; in practice the column and its GIN index ship together in one migration.

With the vector precomputed and stored, the query’s left side collapses from the whole to_tsvector(...) expression to a bare column reference:

Query-time vector (re-tokenizes every row)

db.select()
  .from(invoices)
  .where(sql`to_tsvector('english', coalesce(${invoices.description}, '') || ' ' || coalesce(${invoices.customerName}, '')) @@ websearch_to_tsquery('english', ${term})`);

The left side is rebuilt for every row. No index applies, because the matched value doesn’t exist until the query runs.

Generated column (@@ hits the GIN index)

db.select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ websearch_to_tsquery('english', ${term})`);

The left side is a stored, pre-tokenized column. @@ matches an indexed value, so the GIN index can answer without touching every row.

Caution

Don’t defer this until search feels slow. A query-time vector versus an indexed generated column is the difference between search that scales and search that grinds to a halt at a few thousand rows. At any real volume the generated column is mandatory.

Ranking results with ts_rank and a tiebreaker

Matches in arbitrary order are only half the feature. When ten invoices match consulting, the one whose description is about consulting should outrank the one that mentions it in passing. ts_rank scores a vector against a query, factoring term frequency and document weights, where higher means more relevant. Order by it descending. (For phrase-like relevance, the cover-density variant ts_rank_cd rewards matched terms that sit close together.)

Sorting by rank alone reintroduces the silent pagination bug from the CRUD and cursor lessons. ts_rank produces ties, especially on short rows, and rows that tie on the sort column have no defined order among themselves, so pagination reshuffles them between pages. Pair the sort with the primary key to force a total order.

db.select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ websearch_to_tsquery('english', ${term})`)
  .orderBy(
    desc(sql`ts_rank(${invoices.searchVector}, websearch_to_tsquery('english', ${term}))`),
    asc(invoices.id),
  );

That version repeats websearch_to_tsquery('english', ${term}) in the where and inside ts_rank, two places to keep in sync. Extract it to a const and reference it in both:

const queryExpr = sql`websearch_to_tsquery('english', ${term})`;

const results = await db
  .select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ ${queryExpr}`)
  .orderBy(
    desc(sql`ts_rank(${invoices.searchVector}, ${queryExpr})`),
    asc(invoices.id),
  );

The query expression, extracted once. ${term} still binds as a $1 parameter; the const only removes duplication.

const queryExpr = sql`websearch_to_tsquery('english', ${term})`;

const results = await db
  .select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ ${queryExpr}`)
  .orderBy(
    desc(sql`ts_rank(${invoices.searchVector}, ${queryExpr})`),
    asc(invoices.id),
  );

First use: the where predicate references the const instead of inlining the websearch_to_tsquery(...) call.

const queryExpr = sql`websearch_to_tsquery('english', ${term})`;

const results = await db
  .select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ ${queryExpr}`)
  .orderBy(
    desc(sql`ts_rank(${invoices.searchVector}, ${queryExpr})`),
    asc(invoices.id),
  );

Second use: ts_rank scores each matching row against that same query, and desc(...) puts the most relevant first.

const queryExpr = sql`websearch_to_tsquery('english', ${term})`;

const results = await db
  .select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ ${queryExpr}`)
  .orderBy(
    desc(sql`ts_rank(${invoices.searchVector}, ${queryExpr})`),
    asc(invoices.id),
  );

The tiebreaker: the primary key breaks equal ranks, the same reflex as the CRUD and cursor lessons.

← Previous 1 / 1 Next →

To project the rank into your result, claim its type with sql<number>`ts_rank(...)`, covered in the raw-SQL lesson.

Caution

Ranking without a tiebreaker is the classic silent pagination bug: fine on page one, then results reshuffle once someone scrolls, because rows that tie on rank have no defined order until the primary key gives them one.

Highlighting matches with ts_headline

A good search UI shows the user why a row matched: the snippet of matching text, with their terms emphasized. ts_headline does exactly that. Give it the config, the original text, and the query, and it returns an excerpt with the matched words wrapped, by default in <b>…</b>. Add it to the projection:

const results = await db
  .select({
    id: invoices.id,
    customerName: invoices.customerName,
    snippet: sql<string>`ts_headline('english', ${invoices.description}, ${queryExpr})`,
  })
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ ${queryExpr}`)
  .orderBy(
    desc(sql`ts_rank(${invoices.searchVector}, ${queryExpr})`),
    asc(invoices.id),
  );

Two things to keep in mind:

• It runs on the raw text column (description), not the tsvector. The vector discarded the original words, so ts_headline re-reads the source column to build the snippet, which is why it isn’t free. Compute it only on the already-filtered, already-ranked result set, never across the whole table.
• The <b> tags it returns are real markup. Rendering trusted server markup safely is a render-layer concern for a later UI lesson; here, just know the snippet is markup, not plain text.

When to leave Postgres for a search engine

With full-text search in hand, return to the decision the lesson opened with. It’s a threshold, not a verdict, and it sits further out than most people expect.

Postgres full-text search is the default. It stays the answer up to the low millions of documents at a modest query rate, where relevance means “rank by match.” The deciding factor is usually that the data already lives in Postgres: no second service, no copy to keep in sync, transactional correctness for free.

An external engine (Algolia, Meilisearch, Typesense, OpenSearch, Elastic) earns its weight at many millions of documents and high throughput, or when typo tolerance, synonyms, faceted search , or tuned relevance become real product requirements. The cost is a second datastore every write must fan out to, eventual consistency with Postgres, and a new operational surface.

If typo tolerance is your only gap, there’s an in-database step first. pg_trgm does trigram similarity, handling the typos and partial matches the lexeme model can’t. (Semantic search over embeddings is a different tool, pgvector; the AI unit covers it.)

Walk the decision yourself, in the order you would on a real project: scale first, then the kind of matching the product needs.

Search: Postgres, pg_trgm, or an external engine?

Practice: write a ranked search query

The table below has the generated search_vector column and a handful of invoices for one organization. Write the query: return org 1’s invoices matching consulting, most relevant first. That takes three pieces from this lesson: match the stored vector against websearch_to_tsquery('english', 'consulting') with @@, order by ts_rank(...) descending with id as the tiebreaker, and scope to organization_id = 1.

The seed exposes where full-text search beats the ILIKE '%consulting%' you might still reach for. One description reads “We consult monthly”, which stems to consult, so the lexeme search matches it while ILIKE walks right past. Another reads “Consultancy agreement”, a different stem that neither approach matches.

Return invoices for organization 1 matching the search term 'consulting', most relevant first. Match against search_vector with websearch_to_tsquery, rank with ts_rank, tiebreak by id, and scope to organization_id = 1.

View schema & data

CREATE TABLE invoices (
  id int PRIMARY KEY,
  organization_id int NOT NULL,
  description text,
  customer_name text,
  search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(description, '') || ' ' || coalesce(customer_name, ''))
  ) STORED
);
INSERT INTO invoices (id, organization_id, description, customer_name) VALUES
  (1, 1, 'Overdue invoice for consulting services', 'Northwind Traders'),
  (2, 1, 'Consulting retainer, paid in full', 'Acme Corp'),
  (3, 1, 'We consult monthly on infrastructure', 'Globex'),
  (4, 1, 'Hardware purchase, no services', 'Initech'),
  (5, 1, 'Consultancy agreement, draft', 'Umbrella'),
  (6, 1, NULL, 'Consulting Partners LLC');

Run query Feedback Reset

Expect rows 1, 2, 3, and 6: the two descriptions saying “consulting”, the one stemming from “consult”, and the customer “Consulting Partners LLC”. Row 6 matches on customer_name despite a null description, which is why the generated column wraps both fields in coalesce. Row 4 mentions no consulting, and row 5’s “Consultancy” stems to a different lexeme, so both are excluded.

External resources

The canonical patterns, the reference material, and the build-vs-buy comparison, if you want to go deeper.

Drizzle — PostgreSQL full-text search

orm.drizzle.team

Drizzle's official guide to wiring to_tsvector, @@, and a GIN index through the query builder.

PostgreSQL — Controlling Text Search

postgresql.org

Reference for websearch_to_tsquery, ts_rank, and ts_headline and all their options.

pg_trgm — trigram matching

postgresql.org

The in-database fuzzy-match escape hatch for typo tolerance, when lexeme search isn't enough.

Postgres full-text search vs the rest

supabase.com

Supabase benchmarks Postgres FTS against Elasticsearch, Typesense, and Meilisearch — the build-vs-buy line this lesson closes on.