Postgres Full Text Search Generator

Full text search in PostgreSQL takes four pieces that have to agree with each other: a tsvector built from the right columns with the right configuration, an index that matches that exact expression, a tsquery function suited to the input your users type, and a ranking expression that knows about your column weights. Get one of them wrong and the query still works — it just does a sequential scan or ranks results badly. Enter your table and searchable columns and this generator writes all four, plus the EXPLAIN and ts_stat queries you need to verify the index is really being used. Everything runs in your browser; no schema is uploaded.

TableVector columnConfiguration

StorageIndexQuery function

Searchable columns — one per line, optional weight (A — highest (title), B — high (summary), C — normal (body), D — lowest (tags, notes))

Schema and index

-- PostgreSQL 12+: a stored generated column keeps the tsvector in sync automatically.
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A')
    || setweight(to_tsvector('english', coalesce(summary, '')), 'B')
    || setweight(to_tsvector('english', coalesce(body, '')), 'C')
  ) STORED;

CREATE INDEX articles_search_vector_idx ON articles USING GIN (search_vector);

Search query

-- $1 is the raw text typed by the user. Never concatenate it into the SQL.
SELECT id,
       title,
       summary,
       body,
       ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', search_vector, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

Highlighting, filters and pagination

-- Highlight the matching words in the result set:
SELECT id,
       ts_headline('english', title, q,
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=8, MaxWords=25') AS snippet,
       ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', search_vector, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

-- Combine full text search with a normal filter. Keep the filter in the same
-- index when it is selective, using a btree_gin composite index:
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX articles_search_vector_idx_status ON articles USING GIN (status, search_vector);

-- Query text plus filter:
SELECT id, ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', search_vector, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search_vector @@ q
  AND status = 'published'
ORDER BY rank DESC
LIMIT 20;

-- Paginate deterministically: rank ties are common, so add a unique tiebreaker.
ORDER BY rank DESC, id DESC;

Debugging and maintenance

-- See exactly what the parser produced for a value:
SELECT setweight(to_tsvector('english', coalesce(title, '')), 'A')
       || setweight(to_tsvector('english', coalesce(summary, '')), 'B')
       || setweight(to_tsvector('english', coalesce(body, '')), 'C')
FROM articles LIMIT 1;

-- See how a user query is parsed into lexemes:
SELECT websearch_to_tsquery('english', 'postgres full text search');

-- Confirm the index is used (look for a Bitmap Index Scan on articles_search_vector_idx):
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM articles, websearch_to_tsquery('english', 'example query') AS q
WHERE search_vector @@ q;

-- GIN pending list: fast writes buffer here and slow reads down until flushed.
SELECT * FROM pg_stat_user_tables WHERE relname = 'articles';
VACUUM ANALYZE articles;

-- Turn the pending list off for read-heavy tables (writes get slower, reads faster):
ALTER INDEX articles_search_vector_idx SET (fastupdate = off);

-- Most common lexemes, useful for spotting words worth adding to a stop-word list:
SELECT word, ndoc, nentry
FROM ts_stat($$SELECT search_vector FROM articles$$)
ORDER BY nentry DESC
LIMIT 25;

Notes

• A STORED generated column is the least error-prone option on PostgreSQL 12 and newer: the tsvector can never drift from the source columns, and there is no trigger to forget when you bulk load.
• GIN is the right default: it is roughly three times faster to search than GiST and its size is proportional to the number of distinct lexemes. Its downside is slower updates, buffered by the pending list.
• Weights A/B/C/D let ts_rank_cd score a title match above a body match. The default weight array is {0.1, 0.2, 0.4, 1.0}, in D, C, B, A order — the array above matches that ordering.
• websearch_to_tsquery is the safest function for text typed by a user: it understands quoted phrases, OR and a leading minus for exclusion, and it never raises a syntax error on odd input.
• The 'english' text search configuration decides stemming and stop words. 'simple' does neither — it lowercases and nothing else — which is what you want for identifiers, tags and product codes.

Do more than postgres full text search generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table name and list the searchable columns one per line, optionally followed by a weight letter (A is highest, D is lowest).
Pick the text search configuration for your language, how the vector is stored (generated column, trigger or expression index) and the index method.
Copy the DDL, the search query and the maintenance snippets, then run EXPLAIN ANALYZE to confirm PostgreSQL uses the new index.

Frequently asked questions

Should I use a generated column or a trigger for tsvector in PostgreSQL?

On PostgreSQL 12 and newer, use a stored generated column. It is declarative, cannot drift out of sync with the source columns, and needs no code to maintain. Use a trigger only when the vector depends on something a generated column cannot see — a value from another table, a lookup function that is not IMMUTABLE, or per-row logic — or when you are still on PostgreSQL 11 or older, where generated columns do not exist. An expression index is the third option: no extra column at all, but every query must repeat the indexed expression exactly or the index is ignored.

GIN or GiST for a tsvector index?

GIN for almost every case. GIN stores each lexeme once with a posting list of row pointers, so lookups are about three times faster than GiST and the results are exact. GiST is lossy: it stores a signature per row, produces false positives and PostgreSQL has to recheck each candidate row against the heap. GiST is smaller and cheaper to update, so it can win on tables with very heavy write traffic and modest search volume. If GIN updates are your bottleneck, tune fastupdate and gin_pending_list_limit before switching index types.

Why does my full text search query not use the index?

The three usual causes: the text search configuration in the query differs from the one in the index (to_tsvector('english', body) is a different expression from to_tsvector(body) with a session default), the query calls to_tsvector on a column instead of comparing the indexed column or expression, or the table is small enough that a sequential scan is genuinely cheaper. Run EXPLAIN (ANALYZE, BUFFERS) and check whether a Bitmap Index Scan appears. To inspect indexes, run EXPLAIN and browse tsvector data across PostgreSQL, MySQL and 20+ other engines in one client, use Chat2DB — download it at https://chat2db.ai/download or open https://app.chat2db.ai in a browser.