How to run full-text search across multiple columns in PostgreSQL.

A user types a product name into the search box and gets nothing back. The record exists. It sits on page 23 of 50, and the filter only ran against the page loaded in the browser.

Client-side search works until a table needs pagination. After that, the app filters a slice of the data and reports the slice as the answer. The fix is to move search into the database. PostgreSQL has a built-in type for it, tsvector, which searches several columns, and columns from joined tables, in one WHERE clause. No chained LIKE clauses, and no separate search service for keyword search.

What tsvector stores

A tsvector value is a sorted list of lexemes: normalized word forms built for searching. When PostgreSQL converts text into one, it:

• Drops stopwords such as "the", "a", and "in".

• Applies stemming, so "running" and "run" become the same token.

• Stores the result in a form the query planner and an index can use.

It does not keep the original string. It keeps the searchable representation of it. Several columns, including columns from a joined table, can be combined into one vector before the search runs.

How the functions fit together

• to_tsvector('english', text) turns text into lexemes, using English stopwords and stemming.

• plainto_tsquery('english', phrase) turns what the user typed into a query in which every word must match.

• @@ returns true when the vector contains every lexeme in the query.

For a search box in an internal tool, plainto_tsquery() is the right default, because users do not need to know any query syntax. websearch_to_tsquery() accepts quoted phrases and - for exclusion, in the style of a web search engine. to_tsquery() gives full control over operators and prefixes, and expects well-formed input.

The first version: build the vector in the query

Take a products table joined to categories, and a text input the user types into. This query searches the product name, the description, and the category name together.

SELECT
  p.id,
  p.name AS product_name,
  p.description,
  c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE
  to_tsvector(
    'english',
    coalesce(p.name, '') || ' ' ||
    coalesce(p.description, '') || ' ' ||
    coalesce(c.name, '')
  ) @@ plainto_tsquery('english', {{ SearchTextInput.value }});

What each part does:

• coalesce(p.name, '') replaces a NULL with an empty string. Without it, one NULL column makes the whole concatenation NULL, and the row never matches. Keep it even on NOT NULL columns, in case the schema changes.

• || ' ' || joins the columns with a space, so words from two columns do not merge into one.

• 'english' sets the stopwords and stemming. Change it if the content is in another language.

• {{ SearchTextInput.value }} is Retool syntax for the current value of a text input. Retool passes it to Postgres as a query parameter, so user input is never spliced into the SQL. In another framework, use its own parameter syntax.

This version works immediately on any table. It also builds a vector for every candidate row on every search, which gets expensive as the table grows.

The production version: a generated column and a GIN index

Store the vector once and index it. Since PostgreSQL 12, a generated column keeps it current on every insert and update, with no trigger to maintain.

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

CREATE INDEX products_search_idx
ON products
USING GIN (search_vector);

setweight marks name matches as more important than description matches, which the ranking below uses. A plain UPDATE products SET search_vector = ... also works once, but rows inserted afterwards have no vector until someone runs it again. The generated column removes that gap.

The search then reads the stored column, and can rank the results:

SELECT
  p.id,
  p.name AS product_name,
  p.description,
  ts_rank(p.search_vector, q) AS rank
FROM products p,
  plainto_tsquery('english', {{ SearchTextInput.value }}) AS q
WHERE p.search_vector @@ q
ORDER BY rank DESC
LIMIT 50;

The GIN index finds the matching rows. ts_rank orders them, so a product with the term in its name comes before one that mentions it in passing.

Searching across joined tables

A generated column can only read its own row. If the category name changes, products.search_vector does not know. Search across tables needs a search document that is maintained on purpose.

One pattern is a materialized view that flattens the searchable data:

CREATE MATERIALIZED VIEW product_search_documents AS
SELECT
  p.id AS product_id,
  p.name AS product_name,
  p.description,
  c.name AS category_name,
  to_tsvector(
    'english',
    coalesce(p.name, '') || ' ' ||
    coalesce(p.description, '') || ' ' ||
    coalesce(c.name, '')
  ) AS search_vector
FROM products p
JOIN categories c ON p.category_id = c.id;

CREATE INDEX product_search_documents_idx
ON product_search_documents
USING GIN (search_vector);

The app searches the view:

SELECT
  product_id,
  product_name,
  description,
  category_name
FROM product_search_documents
WHERE search_vector @@ plainto_tsquery('english', {{ SearchTextInput.value }});

The view has to be refreshed when products or categories change:

REFRESH MATERIALIZED VIEW product_search_documents;

Run the refresh on a schedule, or after the writes that matter. If the data changes constantly and results must be current to the second, keep a search table up to date with triggers instead.

What full-text search does not do

It matches lexemes, not substrings. Stemming means "manage" and "management" may match each other, but "prod" will not match "products". For prefix matching, to_tsquery supports the :* operator, as in to_tsquery('english', 'prod:*'). For true substring or typo-tolerant matching, the pg_trgm extension with a trigram index fits better.

The approach works in any tool that sends raw SQL to a Postgres connection: Retool, Appsmith, Tooljet, or a custom back end. Only the parameter syntax changes.

Our view

We recommend the generated column and GIN index for any search over a single table, because it stays current without triggers and costs one index. Add the materialized view only when search must cover joined tables, because the refresh schedule is one more thing someone has to own. The inline query is fine for a prototype, and for tables small enough never to paginate.

Search is one of the first places an internal tool feels slow as it grows. Slow tools are an architecture problem covers the other common causes, and non-overlapping ranges with INT4RANGE shows another rule worth handing to the database.