Built-In Full-Text Search: Dictionaries, tsvector, tsquery, Ranking, and GIN

Learning outcomes

ServiceHub operators search troubleshooting notes by concepts rather than exact substrings: “pump failures,” “failed pump,” and “pumps failing” should share normalized search terms. PostgreSQL Full-Text Search (FTS) tokenizes documents, maps tokens through dictionaries to normalized lexemes, stores them in tsvector, and matches them against tsquery.

1. See what the English configuration actually does

SELECT alias, description, token, dictionaries, dictionary, lexemesFROM ts_debug('english', 'Technicians repaired the failing pumps and valves.');

A text search configuration selects a parser and maps token types to one or more dictionaries. Dictionaries can remove stop words or normalize related word forms. For English, stemming can turn words such as plural/inflected forms into shared lexemes. The exact lexeme output is observable through ts_debug; do not guess stemming rules in application code.

SELECT to_tsvector(  'english',  'Technicians repaired the failing pumps and valves.') AS vector;SELECT plainto_tsquery('english', 'failed pumps') AS plain_query,       websearch_to_tsquery('english', '"pump failure" -billing') AS web_query;

plainto_tsquery treats plain words as AND terms. websearch_to_tsquery accepts search-engine-like phrases, OR, and dash-negation and is deliberately forgiving of raw user-style syntax.

2. Build a maintained weighted search vector

DROP TABLE IF EXISTS app.ch17_article;CREATE TABLE app.ch17_article (  article_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  title text NOT NULL,  body text NOT NULL,  search_vector tsvector GENERATED ALWAYS AS (    setweight(to_tsvector('english', coalesce(title,'')), 'A')    ||    setweight(to_tsvector('english', coalesce(body,'')), 'B')  ) STORED);INSERT INTO app.ch17_article(title, body) VALUES('Pump failure diagnosis', 'A technician repaired the failed pump after inspecting the pressure valve.'),('Electrical inspection checklist', 'Inspect cables, breakers, grounding, and motor current before replacement.'),('Billing workflow', 'Customer invoices and payment reminders are handled by the billing service.'),('Pump maintenance interval', 'Routine pump inspections reduce unexpected failures and improve service history.');ANALYZE app.ch17_article;

The explicit 'english' configuration makes the generated expression deterministic with respect to the server's default text-search setting. Weight A marks title lexemes as more important than body lexemes for ranking.

3. Add GIN for the @@ match operator

CREATE INDEX ch17_article_search_ginON app.ch17_article USING GIN (search_vector);EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT article_id, titleFROM app.ch17_articleWHERE search_vector @@ plainto_tsquery('english','pump failure');

The built-in GIN tsvector_ops class indexes tsvector @@ tsquery. A four-row lab can still choose a sequential scan because that is cheaper; the index's operator capability is nonetheless correct. Scale testing should use representative document counts, term frequencies, and update rates.

4. User-oriented query parsing

WITH q AS (  SELECT websearch_to_tsquery(           'english',           '"pump failure" OR "pump maintenance" -billing'         ) AS query)SELECT a.article_id, a.titleFROM app.ch17_article AS a, qWHERE a.search_vector @@ q.queryORDER BY a.article_id;

to_tsquery is more powerful but expects valid tsquery syntax. Use it for trusted/generated query expressions. websearch_to_tsquery is safer for human search-box syntax because it never raises syntax errors for user punctuation.

5. Ranking is a relevance feature, not truth

WITH q AS (  SELECT websearch_to_tsquery('english','pump failure') AS query)SELECT a.article_id,       a.title,       ts_rank(a.search_vector, q.query) AS rankFROM app.ch17_article AS a, qWHERE a.search_vector @@ q.queryORDER BY rank DESC, a.article_id;

ts_rank and ts_rank_cd are built-in relevance heuristics. Product relevance can also depend on recency, authority, customer, service status, or click feedback. Do not claim the largest built-in score is universally “most relevant.”

6. Headlines use original text—and need HTML sanitization

WITH q AS (  SELECT websearch_to_tsquery('english','pump failure') AS query)SELECT a.article_id,       ts_headline(         'english',         a.body,         q.query,         'MaxFragments=2, MaxWords=12, MinWords=5'       ) AS headlineFROM app.ch17_article AS a, qWHERE a.search_vector @@ q.query;

ts_headline works from the original document and can be significantly more expensive than matching against a stored vector. PostgreSQL also warns that headline output is not guaranteed safe for direct inclusion in a web page. Sanitize untrusted content/output before rendering HTML.

7. FTS is not LIKE or regular-expression search

-- Substring semantics:SELECT article_id, titleFROM app.ch17_articleWHERE lower(body) LIKE '%repair%';-- Lexeme/search semantics:SELECT article_id, titleFROM app.ch17_articleWHERE search_vector @@ plainto_tsquery('english','repair');

LIKE searches character patterns; regex searches character patterns with a richer language. FTS tokenizes and normalizes language. A stemmed lexeme match can find grammatical variants that a literal substring does not, while FTS intentionally ignores some punctuation and stop words that LIKE would see.

8. NULL input and configuration drift are correctness concerns

to_tsvector(config, NULL) returns SQL NULL, so a concatenated search vector can disappear if nullable document fields are not wrapped with coalesce. The lab columns are NOT NULL, but the generated expression still demonstrates the defensive pattern used when title/body are optional.

Also avoid the one-argument to_tsvector(text) in stored/indexed search definitions when linguistic behavior must be stable. It uses default_text_search_config, a setting that can differ between environments or sessions. Persist the intended regconfig explicitly and treat dictionary/configuration changes as search-schema migrations.

SELECT to_tsvector('english', NULL::text) AS null_vector,       current_setting('default_text_search_config') AS session_default;SELECT to_tsvector('english', coalesce(NULL::text,'')) AS empty_vector;

9. Operational maintenance

SELECT article_id, title, search_vectorFROM app.ch17_articleORDER BY article_id;SELECT indexrelid::regclass AS index_name,       idx_scan,       pg_size_pretty(pg_relation_size(indexrelid)) AS sizeFROM pg_stat_user_indexesWHERE relid = 'app.ch17_article'::regclass;

A stored generated vector stays synchronized with title/body automatically. An application-maintained vector requires every write path to update it correctly. GIN adds write/WAL/vacuum cost, so monitor article update rates and index size alongside search latency.

10. Checkpoint

Authoritative references

These data types and index/operator contracts are version-sensitive. The lesson uses the PostgreSQL 18 primary documentation below.

• PostgreSQL 18 — Full Text Search
• PostgreSQL 18 — Controlling Text Search
• PostgreSQL 18 — Text Search Functions and Operators
• PostgreSQL 18 — GIN Indexes