How to Add Full-Text Search to PostgreSQL With `tsvector` and GIN Indexes

To search article titles and bodies by words or phrases, PostgreSQL turns the text into a searchable form and uses an index to find matching rows. A tsvector stores normalized words from a document. A GIN index helps PostgreSQL find rows that contain the words in a search. This approach suits document search, where matching word forms matters more than matching an exact character sequence.

A text-search configuration tells PostgreSQL how to split text into tokens, reduce words to normalized forms called lexemes, and remove common words such as “the.” PostgreSQL parses a search query into a tsquery, which represents the terms and operators to look for. The @@ operator checks whether a tsvector matches a tsquery.

For a single-language corpus, a practical starting point is a stored generated tsvector column and a GIN index on that column. The example below uses PostgreSQL’s English configuration. Choose a configuration that matches your content, and check version-specific behavior against the PostgreSQL version you run.

Store a search vector for each article

Add a stored generated column, which PostgreSQL fills from a SQL expression whenever it writes a row. The column combines the fields readers will search, so the vector stays in sync when the title or body changes. coalesce replaces a NULL field with an empty string so one missing field does not make the full expression NULL.

ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector(
      'english',
      coalesce(title, '') || ' ' || coalesce(body, '')
    )
  ) STORED;

The English configuration reduces related word forms to the same lexeme and removes some common words. The vector also records word positions, which PostgreSQL uses to match phrases and rank results. PostgreSQL documents this pattern for generated columns built from multiple fields in its tables and indexes guidance.

A generated column uses storage for the vector. If you prefer not to store it separately, you can index the to_tsvector expression directly. In that design, the query must use the matching expression and the same explicit text-search configuration for PostgreSQL to use the index. For most new implementations, the generated column makes the search predicate simpler.

Add a GIN index

Create a GIN index on the vector so PostgreSQL can find rows by lexeme instead of checking every document. GIN, short for Generalized Inverted Index, maps each lexeme to the rows that contain it. PostgreSQL prefers GIN for text-search indexes.

CREATE INDEX articles_search_idx
  ON articles USING GIN (search_vector);

The index helps PostgreSQL find matches, but it does not decide what “relevant” means or guarantee a particular speedup. Performance depends on the table, search terms, and write activity.

For a populated table that must remain writable during index creation, use a concurrent build:

CREATE INDEX CONCURRENTLY articles_search_idx
  ON articles USING GIN (search_vector);

A concurrent build lets PostgreSQL accept writes while it creates the index, but the build takes longer and scans the table twice. PostgreSQL cannot run it inside a transaction block. If the command fails, inspect the index because PostgreSQL can leave an invalid index behind. The CREATE INDEX documentation describes these constraints. Plan carefully before adding a generated column to a large existing table. Test the change with your PostgreSQL version and data volume instead of assuming it will finish instantly.

Parse user input and return matches

Use websearch_to_tsquery when a search box should accept ordinary words, quoted phrases, OR, and a leading dash to exclude a term. Pass the user’s input as a SQL parameter, such as $1, so the database treats it as a value rather than executable SQL.

WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
  a.id,
  a.title,
  ts_rank_cd(a.search_vector, q.query) AS rank
FROM articles AS a
CROSS JOIN q
WHERE a.search_vector @@ q.query
ORDER BY rank DESC
LIMIT 20;

The @@ condition returns rows whose stored vector matches the parsed query. ts_rank_cd assigns a cover-density score based on how closely matching terms appear together. Use the score as a relevance guide and tune it with representative searches; it is not an objective measure. PostgreSQL notes that ranking can be expensive because it examines the vectors of matching documents. Its text-search controls documentation explains query parsing and ranking.

Choose a parser that matches how people will search. plainto_tsquery treats input as plain text and joins the remaining terms with AND. phraseto_tsquery looks for terms in sequence. to_tsquery accepts explicit operators and prefix matching, but requires correctly formatted query syntax. Test each parser with the phrases and operators users will enter because each one searches differently.

Full-text search normalizes words for document search. It does not provide general substring matching or typo correction. If users need those behaviors, choose a separate search technique and test it against that requirement.

Tune fields and writes as needed

If a title match should matter more than a body match, assign weights when building the vector:

setweight(to_tsvector('english', coalesce(title, '')), 'A')
||
setweight(to_tsvector('english', coalesce(body, '')), 'D')

PostgreSQL’s ranking functions can use these labels to favor title matches. GIN does not store weight labels, so a weighted search can require PostgreSQL to recheck matching rows against the table. Add weights only when the product needs that ranking distinction, then evaluate the results with real queries.

For mixed-language content, a single configuration such as english does not fit every row. PostgreSQL supports a different text-search configuration for each row. To use the index, the expression that builds it and the search query must use matching configurations. Review the text-search table patterns before adopting this design.

GIN improves search performance at some cost to writes. Its fastupdate feature buffers index entries from writes in a pending list. Searches check that list, and cleanup can make an update unusually slow. PostgreSQL recommends suitable autovacuum maintenance to limit these effects, but the right settings depend on the workload. Measure search latency and write behavior on your data before changing GIN settings. For large bulk loads, PostgreSQL also advises considering whether to load rows first and build the index afterward. The index is unavailable until the build finishes. See the GIN implementation notes.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com