Full-Text Search

Most people reach for a dedicated search product the moment they need anything beyond a LIKE '%keyword%' query. What a lot of teams don't realize is that PostgreSQL ships with a genuinely capable full-text search engine built in — no extension, no separate service, no data synchronization pipeline to keep in sync. For a large share of real-world "search this table of text" problems, it is more than enough.

tsvector and tsquery

PostgreSQL's text search revolves around two special data types:

tsvector — a normalized, searchable representation of a document. It breaks text into lexemes (word stems), removes noise, and stores positional information for ranking.
tsquery — a normalized representation of a search query, supporting boolean operators (& for AND, | for OR, ! for NOT) between search terms.

You compare the two with the @@ match operator, which returns true when the query matches the document.

basic-match.sql

SQL

SELECT to_tsvector('english', 'A fast brown fox jumps over lazy dogs')
       @@ to_tsquery('english', 'fox & jump');
-- returns true — 'jumps' is stemmed down to the same lexeme as 'jump'

Notice that "jump" matched "jumps" — the english configuration applies stemming, so searches match different grammatical forms of the same word automatically. It also strips out common stop words like "a" and "over".

A Worked Example: Searching Product Descriptions

Suppose you have a products table and want to let users search across the name and description:

product-search.sql

SQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT
);

-- Search directly by building the tsvector on the fly
SELECT id, name
FROM products
WHERE to_tsvector('english', name || ' ' || description)
      @@ to_tsquery('english', 'wireless & headphones');

Building the tsvector on every row for every query works, but it's wasteful to redo that normalization work each time. A common pattern is to store the tsvector in a dedicated generated column that's kept up to date automatically:

stored-tsvector-column.sql

SQL

ALTER TABLE products
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
  ) STORED;

SELECT id, name
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphones');

Ranking Results with ts_rank

A boolean match tells you whether a row matches, but not how relevant it is. ts_rank() scores each match based on how often and how prominently the search terms appear, so you can order results from most to least relevant:

ranked-search.sql

SQL

SELECT id, name,
       ts_rank(search_vector, to_tsquery('english', 'wireless & headphones')) AS rank
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphones')
ORDER BY rank DESC
LIMIT 20;

Index your tsvector column

Full-text search on an un-indexed `tsvector` still means scanning every row. In real usage, put a GIN index on the search column — `CREATE INDEX idx_products_search ON products USING GIN (search_vector);` — which makes these lookups fast even over large tables. See the Index Types page for more on how GIN indexes work.

When Is Built-in FTS Enough?

Full-text search in PostgreSQL handles a lot of real-world search requirements well: multi-word queries, stemming, ranking, and filtering combined with normal SQL WHERE clauses (so you can search text and filter by price, category, or date in one query). But dedicated search engines like Elasticsearch or OpenSearch still win in certain areas:

Capability	PostgreSQL FTS	Dedicated Search Engine
Typo / fuzzy tolerance	Limited (needs extensions like `pg_trgm` for approximation)	Built-in, mature fuzzy matching out of the box
Faceted search / aggregated filters	Possible with SQL `GROUP BY`, but not purpose-built	First-class support, designed for this exact use case
Scale (hundreds of millions+ of documents)	Works, but requires careful indexing and tuning	Built to shard and scale horizontally by design
Advanced relevance tuning	Basic ranking via `ts_rank` / `ts_rank_cd`	Rich scoring models, machine-learned relevance

The practical takeaway: if you're not sure whether you need Elasticsearch, you probably don't yet. Start with PostgreSQL full-text search — it's already running, it's transactionally consistent with the rest of your data, and it covers the majority of "search a table of text" needs without adding an entire new system to operate.