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

PostgreSQL has a built-in data type: tsvector. It lets you search across multiple columns and joined tables in a single WHERE clause. You convert your text columns into a searchable vector using to_tsvector(), match it against a query with @@, and get back exactly the rows that contain the terms you're looking for.

No client-side filtering, no chained LIKE clauses, and no extra search service for basic keyword search.

When does client-side search stop working?

Most internal tools start with the same pattern: load the dataset, filter on the frontend. For small tables this is perfectly fine because it's fast, simple and has no extra infrastructure. The problem appears when the dataset gets large enough to require pagination.

Once you're paginating, you're only ever loading a slice of the data. Filtering on the client means filtering that slice, not the full table. A user searching for a product name might be looking at page 1 of 50 and find nothing, not because the record doesn't exist, but because it's sitting on page 23. The search looks broken, because for the user, it is.

This is the point where search needs to move to the database layer. And tsvector is what PostgreSQL gives you to do it properly.

What is tsvector and how does it store text?

A tsvector value is a sorted list of lexemes: normalized, searchable word forms. When you convert a column value into a tsvector, PostgreSQL:

• Strips out stopwords (common words like "the", "a", "in")

• Applies stemming so that "running" and "run" resolve to the same token

• Stores the result in a form the query planner can work with efficiently

You can think of it as a pre-processed index of meaningful words, built on the fly from whatever text you pass in. It doesn't store the original string; it stores the searchable representation of it.

What makes this useful for internal tools is the ability to combine multiple columns, and columns from joined tables, into a single vector before searching. One WHERE clause covers everything.

How do to_tsvector() and plainto_tsquery() work together?

• to_tsvector() takes a language identifier and a text value, and returns the lexeme representation.

• plainto_tsquery() takes the same language identifier and a search phrase, and converts it into the query format that tsvector understands, normalizing the input. It does not preserve exact phrases; for phrase-aware queries, use phraseto_tsquery() or websearch_to_tsquery().

The @@ operator then checks whether the vector and the query match. If all of the lexemes in the query appear in the vector, the row is returned.

For most internal tool search boxes, plainto_tsquery() is the right choice. It handles natural input without requiring users to know PostgreSQL query syntax. If you need more control—such as exact phrase matching, boolean operators, prefix matching—PostgreSQL also provides to_tsquery() and websearch_to_tsquery(), but those are cases for more advanced search interfaces.

What does the query actually look like?

Here's a representative example. Imagine a products table joined to a categories table, and a search input in the interface that a user types into.

sql

Walking through what each part does:

• coalesce(p.name, '') handles nullable columns. If any column is NULL, coalesce replaces it with an empty string before concatenation, which prevents the entire vector from failing. It's worth including this defensively even when columns are NOT NULL in the schema, in case that changes.

• || ' ' || concatenation joins the column values into a single text string before passing them to to_tsvector(). The space between ensures tokens from different columns don't merge into a single unintended lexeme.

• to_tsvector('english', ...) processes the combined string in English, applying English stopword removal and stemming. If your dataset contains content in another language, swap the language identifier accordingly.

• plainto_tsquery('english', {{ SearchTextInput.value }}) converts the value from the search input into a query. In a Retool app, {{ SearchTextInput.value }} pulls the current value of the text input component directly into the parameterized query.

• @@ is the match operator. It returns true if the vector contains the query's lexemes.

Does this work well with large tables?

Yes, but only when you stop calculating the search vector from scratch on every request.

The inline to_tsvector() example is useful because it is simple and works immediately. But on a large table, PostgreSQL has to build that vector for every candidate row before it can compare it with the search query. That can become expensive quickly.

For production use, the usual approach is to store the searchable representation separately and index it with a GIN index.

Then the search query becomes:

This works well when all searchable fields live on the same table.

If your search needs to include data from joined tables, such as categories.name, you should not assume that a products.search_vector column will automatically stay aware of category changes. The database needs a maintained search document.

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

Your app then searches the materialized view:

The tradeoff is that the materialized view must be refreshed when the underlying product or category data changes:

For many internal tools, this is a good fit because the data changes less frequently than it is searched. If the data changes constantly and search results must be immediately fresh, use a trigger-maintained search_vector column or a dedicated search table instead.

FAQ

Can you search across multiple columns and joined tables in a single PostgreSQL query?

Yes. You concatenate the columns from each table into a single string before passing them to to_tsvector(). The join runs first, then the vector is built from whichever columns you include, regardless of which table they come from.

What happens when a column is NULL in a PostgreSQL full-text search query?

The coalesce() function handles this. Wrap each column in coalesce(column_name, '') before concatenating, and any NULL value becomes an empty string. Without it, a single NULL column will make the entire vector return NULL and the row won't match anything.

What is the difference between plainto_tsquery and to_tsquery in PostgreSQL?

plainto_tsquery is appropriate for natural language search inputs. It converts a phrase into an AND query without requiring the user to write PostgreSQL syntax. to_tsquery gives you more control, but requires properly formatted input. For a standard search box in an internal tool, plainto_tsquery is the right default.

Does PostgreSQL full-text search match partial words or substrings?

Not by default. PostgreSQL full-text search matches on lexemes, not substrings. Stemming means "manage" and "management" may match each other, but "prod" won't match "products". For prefix matching, to_tsquery with a :* prefix operator supports it. For true substring matching, pg_trgm (trigram indexing) is a better fit.

Does PostgreSQL full-text search with tsvector work with internal tool builders like Retool or Appsmith?

Yes. The query runs at the database level, so any framework that lets you write raw SQL against a PostgreSQL connection will support it—be it Retool, Appsmith, Tooljet, or a custom backend. The {{ SearchTextInput.value }} syntax in the example is Retool-specific; replace it with however your framework parameterizes query inputs.

Built something similar, or running into a variation of this pattern? Talk to us. We build governed internal tools on PostgreSQL for operations and product teams across EMEA.

More from the Stackdrop engineering team: Engineering Notes.