PostgreSQL full-text search: tsvector, tsquery, GIN

PostgreSQL can search text without Elasticsearch or any other second system. You turn each document into a tsvector (a sorted list of normalised words with positions), turn the search box input into a tsquery, compare them with the @@ operator, index the tsvector with GIN so the comparison does not read every row, and sort the matches with ts_rank. For a blog, a help centre, a product catalogue or an internal wiki with up to a few million rows, that is enough, and it lives in the same transactions, the same backups and the same connection string as the rest of your data. This guide builds it step by step on a real table, and says where it stops being the right tool.

Everything below runs on any currently supported PostgreSQL release unless a version is mentioned. A few functions are newer than the rest and are marked where they appear.

What full-text search does that LIKE cannot#

The first thing people try is WHERE body ILIKE '%server%'. It works on a hundred rows and then fails in three ways:

• It cannot use a normal index. A pattern with a leading wildcard forces a scan of every row, reading every byte of every body. Time grows with the size of the table.
• It matches characters, not words. Searching for run finds prune and brunch, and does not find running or ran.
• It has no idea of relevance. A row that mentions the word once in a footnote ranks equal to a row about nothing else.

Full-text search fixes all three by working on lexemes instead of characters. Parsing a document breaks it into tokens, each token is passed through a dictionary that drops stop words (the, and, of) and reduces the rest to a root form (running, runs and ran do not all become run - a stemmer is rule-based, so irregular forms like ran are the usual casualty - but running and runs do), and what is stored is the result. The query goes through the same process, so both sides agree on what a word is.

You can see the transformation directly:

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');

 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

The words are sorted, stemmed (foxes became fox, lazy became lazi, which is not an English word and does not need to be) and each carries the position it occupied. the and over are gone, but positions were still counted for them, which is how phrase search later knows that two words were adjacent.

The two types: tsvector and tsquery#

A tsvector is the document side. A tsquery is the question side. The match operator is @@, and it returns a boolean:

SELECT to_tsvector('english', 'PostgreSQL stores documents as lexemes')       @@ to_tsquery('english', 'document & lexeme');

That returns t. Both sides were stemmed with the same configuration, so documents met document. If you mix configurations - a tsvector built with english and a query built with simple - you get silent misses, which is the most common reason a search "works on my machine" and not in production. The language configurations section below covers how to avoid that.

PostgreSQL gives you four ways to build a tsquery, and the difference is mostly about what happens when a user types something odd into a search box:

to_tsquery is strict. Pass it a raw string from a user and a stray apostrophe or an unbalanced bracket raises a syntax error. It is the right tool when your code builds the expression, and the wrong one for user input.

websearch_to_tsquery (PostgreSQL 11 and later) is the one to put behind a search box. It never raises a syntax error on user input and understands the conventions people already know from web search engines:

SELECT websearch_to_tsquery('english', '"dedicated server" -windows or linux');

 'dedic' <-> 'server' & !'window' | 'linux'

A quoted phrase becomes a phrase (the <-> operator means "immediately followed by"), -word becomes a negation, and or becomes |. Unquoted words are ANDed.

Prefix matching is a to_tsquery feature: to_tsquery('english', 'postgr:*') matches any lexeme that starts with postgr. It is the basis of search-as-you-type, with one caveat - a prefix is not stemmed the way a full word is, so test the cases you care about.

A worked example: a small articles table#

Create a table and put a few rows in it. This stands in for whatever you want to search:

CREATE TABLE articles (    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    title      text NOT NULL,    body       text NOT NULL,    created_at timestamptz NOT NULL DEFAULT now());INSERT INTO articles (title, body) VALUES  ('Backups that restore', 'A backup you have never restored is a hope, not a backup.'),  ('Connection pooling', 'Each connection costs memory. A pooler keeps the count low.'),  ('Choosing an index', 'An index speeds up reads and slows down writes on the table.');

The naive version of a search computes the tsvector on the fly:

SELECT id, titleFROM articlesWHERE to_tsvector('english', title || ' ' || body)      @@ websearch_to_tsquery('english', 'restore backup');

That is correct and it is slow, because PostgreSQL has to parse and stem every row's text for every query. There are two standard ways to stop doing that, and both end in a GIN index.

Option 1: a stored generated column

Since PostgreSQL 12 a column can be computed from other columns and stored on disk, kept in sync automatically:

ALTER TABLE articlesADD COLUMN search tsvectorGENERATED ALWAYS AS (    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

coalesce matters: concatenating a NULL with anything gives NULL, and a row whose title is empty would vanish from search entirely. The expression must be immutable, which is why the configuration name is written out. This is the option to pick by default. The cost is that the tsvector takes disk space beside the original text, and adding the column rewrites the table once.

On PostgreSQL 11 and older there are no generated columns; use a tsvector column kept current with a trigger. PostgreSQL ships a helper for that, tsvector_update_trigger, documented in the manual's text search chapter.

Option 2: an expression index, no extra column

If you would rather not store the vector, index the expression itself:

CREATE INDEX articles_fts_exprON articles USING gin (to_tsvector('english', title || ' ' || body));

The query then has to repeat the identical expression for the planner to recognise it:

WHERE to_tsvector('english', title || ' ' || body) @@ query

This saves the stored column but makes it easy to drift out of sync: change the expression in one place and not the other and the index silently stops being used. Note also that the index only accepts the two-argument form of to_tsvector, with the configuration spelled out. The one-argument form depends on the session setting default_text_search_config, so it is not immutable and PostgreSQL refuses to index it.

The GIN index#

GIN stands for Generalized Inverted Index. A tsvector has many lexemes per row, so the useful structure is the reverse of the table: for each lexeme, a list of the rows that contain it. Looking up backup is then a single lookup, not a scan.

CREATE INDEX articles_search_idx ON articles USING gin (search);

Check that it is used with EXPLAIN:

EXPLAINSELECT id, title FROM articlesWHERE search @@ websearch_to_tsquery('english', 'backup');

On a table of three rows the planner will choose a sequential scan whatever you do, because reading three rows is cheaper than consulting an index. That is correct behaviour, not a missing index. To see the index plan on a toy table, run SET enable_seqscan = off; for the session and then explain again; look for Bitmap Index Scan on articles_search_idx. Do not leave that setting on. On a table with tens of thousands of rows the index is chosen without any prompting.

GIN is the right default for text search. The alternative, GiST, builds a smaller index that is faster to update but slower to search and lossy (it can return rows that then get rechecked). The PostgreSQL documentation's own advice is GIN for static or mostly-read data, and that covers most cases. See PostgreSQL indexes: types, order and cost for the general picture.

The costs to know about:

• Size. A GIN index on text can be a large fraction of the size of the text itself, sometimes more. Check with SELECT pg_size_pretty(pg_relation_size('articles_search_idx'));.
• Write speed. Inserting one document touches many index entries, one for each distinct lexeme. To soften this, GIN buffers new entries in a pending list and merges them in batches (the fastupdate storage parameter, on by default). Bulk-loading a large table is much faster if you load first and create the index afterwards.
• Bloat and maintenance. The pending list is cleaned up by autovacuum, so a table that is written to heavily needs autovacuum to keep up. Postgres VACUUM and bloat explains how to check.

Ranking results with ts_rank#

A boolean match tells you which rows qualify, not which to show first. ts_rank returns a float for each match based on how often and how densely the query terms appear:

SELECT id, title,       ts_rank(search, q) AS rankFROM articles,     websearch_to_tsquery('english', 'backup restore') AS qWHERE search @@ qORDER BY rank DESCLIMIT 10;

Putting the query in the FROM clause as q computes it once rather than once per row. There is a sibling function, ts_rank_cd, which also rewards terms appearing close together ("cover density"). It costs more to compute and is worth trying when matching phrases matters to your users.

Two limits are worth knowing about early:

• Ranking cannot use the index. The GIN index finds the matching rows; the rank has to be computed for every one of them, after which they are sorted. If a common word matches 400,000 rows, you rank 400,000 rows. The usual remedies are to narrow the query (more required words, a filter on category or date) or to rank only a candidate set.
• Ranking does not use document frequency. PostgreSQL's built-in ranking does not know that one word is rare across the corpus and another is common, the way BM25 does in dedicated search engines. It is acceptable for most applications and is the most common reason to graduate to something else.

Weights: a title should count for more than a body

You can label parts of a document with one of four weights, A to D, using setweight. By default the weights multiply a match by 1.0 for A, 0.4 for B, 0.2 for C and 0.1 for D. Rebuild the generated column so titles are A and bodies are B:

ALTER TABLE articles DROP COLUMN search;ALTER TABLE articlesADD COLUMN search tsvectorGENERATED ALWAYS AS (    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||    setweight(to_tsvector('english', coalesce(body, '')),  'B')) STORED;CREATE INDEX articles_search_idx ON articles USING gin (search);

Dropping the column also drops the old index, so it is created again. A page whose title contains the search word now outranks one that mentions it in passing. You can pass your own weights as the first argument of ts_rank, as an array in the order {D, C, B, A}:

SELECT id, ts_rank('{0.1, 0.2, 0.4, 1.0}', search, q) AS rankFROM articles, websearch_to_tsquery('english', 'backup') AS qWHERE search @@ q;

ts_rank also takes a normalisation option as its last argument, a bit mask. Without it, long documents score higher simply because they contain more words. The option 1 divides the rank by 1 + log(document length), and 32 rescales the rank into the range 0 to 1. They can be combined by adding: ts_rank(search, q, 1 | 32).

Showing the match with ts_headline#

Search results look much better with the matching passage and the words highlighted. ts_headline does it:

SELECT id,       ts_headline('english', body, q,                   'StartSel=<mark>, StopSel=</mark>, MaxWords=25, MinWords=10') AS snippetFROM articles,     websearch_to_tsquery('english', 'backup') AS qWHERE search @@ qORDER BY ts_rank(search, q) DESCLIMIT 10;

Two cautions. ts_headline works on the original text, not on the tsvector, so it re-parses the document; call it only on the rows you are actually going to display (the ten after LIMIT), never on the whole result set before sorting. And it returns text containing the markers you chose, so if you are building a web page, escape the document text yourself before applying highlights, because the function does not do HTML escaping.

Language configurations#

A text search configuration is the recipe that decides how text is tokenised and which dictionaries reduce the tokens. english is one of them. List what your server has:

$ psql -c '\dF'

or from SQL:

SELECT cfgname FROM pg_ts_config ORDER BY cfgname;SHOW default_text_search_config;

A standard installation includes configurations for many European languages (french, german, spanish, russian, italian and others) and a configuration called simple. There are two kinds of gap to know about:

• Languages with no built-in configuration. Czech and Georgian, to name two, are not in the standard list. Without one, use simple, which lowercases words and keeps them whole, with no stemming and no stop words. It gives exact-word matching only, so server will not find servers. Fuller support means adding a dictionary (an Ispell or Hunspell one for Czech, for example) and building your own configuration on it, which needs files on the database server's disk and is usually not possible on managed hosting.
• Accents. café and cafe are different lexemes by default. The unaccent extension fixes that, if you can create it.

Which language to pick is a real design decision, and it must match on both sides. The cheapest way to stay consistent is to name the configuration explicitly in every call, never rely on the session default, and put it in one place such as the generated column and one query helper.

Documents in more than one language

If rows are in different languages, store the configuration per row. The type is regconfig:

ALTER TABLE articles ADD COLUMN lang regconfig NOT NULL DEFAULT 'english';-- a generated column that uses the row's own languageALTER TABLE articlesADD COLUMN search tsvector GENERATED ALWAYS AS (    to_tsvector(lang, coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

That is valid because a regconfig argument is immutable. At query time you stem the search words with the language the user is searching in, which is usually known from the interface, not from the content. Stemming the query in the wrong language gives the same silent misses as mixing configurations.

Ignoring accents with a custom configuration

Create the extension, copy a built-in configuration, and put unaccent in front of the stemmer:

CREATE EXTENSION IF NOT EXISTS unaccent;CREATE TEXT SEARCH CONFIGURATION english_unaccent (COPY = english);ALTER TEXT SEARCH CONFIGURATION english_unaccent    ALTER MAPPING FOR hword, hword_part, word    WITH unaccent, english_stem;

Now to_tsvector('english_unaccent', 'Café') and the query cafe produce the same lexeme. If CREATE EXTENSION is refused with a permissions error, the role you are connected as is not allowed to install it; on a hosted database, check which extensions your plan lets you create before designing around one. PostgreSQL roles and permissions explains who can do what.

Debugging why a search finds nothing#

The built-in debugging functions answer most "why does this not match" questions:

SELECT * FROM ts_debug('english', 'The quick brown foxes');SELECT to_tsvector('english', 'Backups') @@ to_tsquery('english', 'backup');SELECT ts_lexize('english_stem', 'foxes');SELECT * FROM ts_stat('SELECT search FROM articles') ORDER BY ndoc DESC LIMIT 20;

Each of those answers a different question:

• ts_debug shows each token, which parser rule produced it, which dictionary handled it and what lexeme came out.
• ts_lexize runs one word through one dictionary. An empty result means the word is a stop word in that dictionary.
• ts_stat lists the lexemes in a column and how many documents and occurrences each has. It is useful for finding words so common they should be treated as stop words, and for spotting junk tokens such as long hex strings or URL fragments inflating the index.

Common causes of an empty result, in rough order of frequency:

1. The tsvector was built with one configuration and the query with another.
2. A search word is a stop word in the language, so the query reduces to nothing. to_tsquery('english', 'the') produces an empty query with a notice, and an empty query matches no rows.
3. Searching for a partial word without :*. postgr is not postgres.
4. The tsvector column is NULL because the source columns were NULL and coalesce was missing.
5. A column was changed but the tsvector was not rebuilt (a manually maintained column without its trigger).

Search on a small server#

Full-text search is memory-hungry in one particular way: GIN lookups are fast when the index is in cache and noticeably slower when it is read from disk. On a small instance, the number that matters is the size of the index compared to the RAM left over after the rest of your working set. A 2 GB database plan with a 600 MB search index is comfortable; a 3 GB index on the same plan will be read from disk much of the time. Check the size before and after you add a search column:

SELECT pg_size_pretty(pg_total_relation_size('articles')) AS table_and_indexes,       pg_size_pretty(pg_relation_size('articles_search_idx')) AS gin_index;

Tuning shared_buffers and work_mem is covered in PostgreSQL tuning for small servers. For search specifically, the quickest wins are to index only the columns users actually search, to drop weights and columns you never use, and to make queries more selective so fewer rows have to be ranked.

On RE:NODE the PostgreSQL database plans start from $6 a month on the database hosting page, with the host, port, user and password generated for you when the server is created. You connect from psql or your application exactly as in PostgreSQL remote connections: getting started, then run every statement in this guide as written. A search column and its GIN index are ordinary table data, so they are included in your plan's backup slots and in a dump. Before you build on an extension such as unaccent, run the CREATE EXTENSION statement on your own database to find out whether it is allowed; do not assume it from this guide. A rebuilt index after restoring is automatic when you restore a dump, which is another reason to keep a tested restore routine - see database backups and restores.

When PostgreSQL search is not enough#

Built-in search is a strong default, and an honest guide has to say where it ends:

• Typos and fuzzy matching. Full-text search matches lexemes exactly. For "did you mean" behaviour or misspelling tolerance, add the pg_trgm extension, which indexes three-character fragments (also with GIN) and supports similarity ranking and fast ILIKE '%fragment%'. Some applications use both: trigram for short fields like names, full-text for long bodies.
• Relevance tuning. Without document-frequency scoring and with no per-field boost beyond the four weights, you cannot fine-tune ranking the way a dedicated engine allows.
• Faceted counts and very large corpora. Counting matches by category across many millions of rows is slow, because every match must be visited.
• Heavy search traffic competing with your application. Search and your transactional queries share the same CPU and memory. If search load starts to hurt the rest of the application, that is the time to separate them.

If the question is whether PostgreSQL is the right database to start with at all, which database should I use and MySQL vs PostgreSQL compare the options. If your data is mostly semi-structured documents, PostgreSQL JSONB can be combined with full-text search: build the tsvector from payload ->> 'title' and similar expressions.

A checklist for a new search feature#

1. Pick the configuration (english, or another that matches your data) and write it down once.
2. Add a stored generated tsvector column with setweight on the important fields and coalesce on every nullable one.
3. Create a GIN index on that column.
4. Query with websearch_to_tsquery and the same configuration name.
5. Order by ts_rank and LIMIT the result; call ts_headline only on the final rows.
6. Check the plan with EXPLAIN on a realistic amount of data, not on three rows.
7. Check the index size, and watch autovacuum on write-heavy tables.

FAQ#

Is PostgreSQL full-text search good enough for production?

Yes, for catalogue, documentation, help-centre and blog-style search over up to a few million rows. It is not designed for typo tolerance or advanced relevance tuning. Many production systems run on it for years and add a separate engine only when they hit one of the limits above.

What is the difference between tsvector and tsquery?

A tsvector is the stored, processed form of a document: sorted lexemes with their positions. A tsquery is the processed form of a search: lexemes joined by operators such as AND, OR, NOT and "followed by". The @@ operator tests whether a vector satisfies a query.

Why is my full-text query not using the GIN index?

The usual reasons are a table too small to be worth an index, an expression in the query that differs from the one in the index, a tsvector built with a configuration other than the one in the query, or the one-argument to_tsvector form. Run EXPLAIN on a table with realistic row counts and compare the expressions character by character.

Should I use a generated column or a trigger?

Use a stored generated column on PostgreSQL 12 or newer: it is shorter, cannot be bypassed by a forgotten code path, and is rebuilt for you if the table is restored. A trigger is the fallback for older versions or when the vector must be built from other tables.

Does full-text search work for Czech, Georgian or Russian?

Russian has a built-in configuration. Czech and Georgian do not in a standard installation, so you can use simple for exact-word matching, or install a dictionary and build a custom configuration where your hosting allows it. Test with real words from your data, because the quality of stemming varies by language.

How do I make search ignore accents and capitals?

Capitals are ignored already, because lexemes are lowercased. For accents, install the unaccent extension and build a configuration that runs tokens through it before stemming, as shown above, then use that configuration name in both the column and the query.