Module 6 · Advanced Data Features

Full-Text Search and Generated Columns

Lesson 18 of 30 10 min read Updated

Full-text search turns human language into searchable tokens, and generated columns let PostgreSQL keep that searchable representation beside the original text. The outcome is simple: application code writes normal document fields, while the database maintains a tsvector column that can be indexed and queried with language-aware operators.

In this advanced data features section, this topic connects relational modeling with specialized indexes. You still design tables, constraints, and query plans, but the searched value is no longer a raw string comparison. It is a derived document vector with lexemes, positions, optional weights, and a GIN index that can find matching rows without scanning every title or body.

How PostgreSQL Text Search Works

PostgreSQL full-text search has two central data types. A tsvector stores normalized lexemes extracted from a document. A tsquery stores search terms and operators. The match operator @@ asks whether the vector satisfies the query.

The conversion is controlled by a text search configuration such as english, simple, or a custom configuration. The parser breaks text into token categories, dictionaries normalize tokens, stop words may be removed, and stemming may reduce related words to the same lexeme. For example, an English configuration can index searching and searched under a shared root lexeme. This is why full-text search finds language concepts that ILIKE '%search%' either misses or finds too broadly.

A tsvector can also include positions. Positions allow phrase matching and ranking functions to prefer documents where terms are close together. Weights A through D let you treat a title lexeme as more important than a body lexeme. Ranking functions such as ts_rank and ts_rank_cd use these details to order matches, but ranking is computed after candidate rows are found.

Generated columns solve the synchronization problem. Without them, an application might update title and forget to update search_vector. A stored generated column is computed by PostgreSQL from other columns when the row is inserted or updated. The expression must be deterministic enough for PostgreSQL to store it safely, so the search configuration is normally written as a constant regconfig value. The generated value is then just another column for indexing and querying, but users do not assign it directly.

Syntax Anatomy

The usual pattern is a table with source text columns, a stored generated tsvector, and a GIN index on that vector. The query side builds a tsquery from user input, matches with @@, and optionally ranks results.

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL,
    search_document tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'D')
    ) STORED
);
CREATE INDEX articles_search_document_gin ON articles USING gin (search_document);

to_tsvector converts text to lexemes. coalesce prevents a null source column from nulling the whole expression. setweight labels title tokens as more important than body tokens. The concatenation operator combines the two vectors. STORED means the computed vector is materialized in the row and maintained during writes. The GIN index stores an inverted map from lexeme to row locations, which is the structure that makes term lookups fast.

Example 1: Inspect the Stored Vector

Start by seeing what the database stores. This example inserts two rows and returns their generated search vectors.

INSERT INTO articles (title, body) VALUES
('Generated columns for search', 'PostgreSQL keeps the tsvector synchronized with title and body text.'),
('Vacuum basics', 'Vacuum removes dead tuples and helps indexes stay efficient.');

SELECT id, search_document FROM articles ORDER BY id;

A representative result contains normalized lexemes rather than the original sentences:

 id | search_document
----+-------------------------------------------------------------
  1 | 'bodi':13 'column':2A 'gener':1A 'keep':6 'postgresql':5 'search':4A 'synchron':9 'text':14 'titl':11 'tsvector':8
  2 | 'basic':2A 'dead':5 'effici':11 'help':8 'index':9 'remov':4 'stay':10 'tupl':6 'vacuum':1A,3

The exact positions can vary if the text changes, but the important behavior is deterministic: stop words such as the and and are absent, words are normalized, and title terms carry weight A.

Example 2: Query Safely from User Input

Use plainto_tsquery or websearch_to_tsquery for ordinary user-entered search boxes. They turn plain text into a valid query instead of treating punctuation as raw tsquery syntax.

SELECT id, title
FROM articles
WHERE search_document @@ plainto_tsquery('english', 'generated search')
ORDER BY id;
 id |            title
----+-----------------------------
  1 | Generated columns for search

The query means both normalized terms must be present. If the user types generated & search, plainto_tsquery treats the punctuation as input text. That is safer than handing the raw string to to_tsquery, where operators such as &, |, and ! are part of the query language and malformed input can raise an error.

Example 3: Rank Title Matches Higher

The vector already marks title lexemes with weight A. Ranking can use those weights to put more relevant rows first.

INSERT INTO articles (title, body) VALUES
('Index maintenance', 'Generated search columns are often backed by GIN indexes.'),
('Search maintenance', 'Generated columns make indexed search data less error-prone.');

SELECT id, title
FROM articles
WHERE search_document @@ plainto_tsquery('english', 'search maintenance')
ORDER BY ts_rank(search_document, plainto_tsquery('english', 'search maintenance')) DESC, id;
 id |        title
----+---------------------
  4 | Search maintenance
  3 | Index maintenance

Both rows match, but the row with both search terms in the title sorts first. Ranking numbers are useful for ordering, not as a universal relevance score. They depend on vector weights, term frequency, positions, and the ranking function you choose.

Design Choices and Trade-Offs

A generated vector is best when the searchable text lives in the same row and should always change atomically with that row. It removes application drift and makes migrations easier to reason about. A trigger-maintained vector is more flexible when the expression must reference other tables, call custom logic that cannot be used in a generated column, or update only under special conditions. An external search engine may be appropriate for cross-table relevance, typo tolerance, faceting, distributed search, or specialized analyzers, but it adds replication lag and another operational system.

GIN indexes are usually the right index type for tsvector containment. They are fast for lookup but have write costs because every inserted or changed document updates many lexeme entries. Heavy update workloads can accumulate pending index work before cleanup. GiST can support full-text search too, but it is usually chosen only for specific space or combined-index trade-offs.

Language configuration is a product decision. english improves stemming for English text but may damage codes, names, or mixed-language content. simple performs less linguistic normalization and can be better for identifiers, product names, or logs. If each row has a language, store the configuration separately and be careful: a generated column expression needs a deterministic configuration reference, so many systems use separate columns or separate indexes per language class instead of trying to make one expression fit every document.

Failure Modes and Troubleshooting

  • Symptom: searches return no rows even though the words are visible. Cause: the vector and query used different configurations, such as simple for indexing and english for querying. Diagnose: run SELECT to_tsvector(...) and SELECT plainto_tsquery(...) side by side. Correct: use the same configuration in the generated expression and every query path.
  • Symptom: user searches sometimes fail with syntax errors. Cause: raw input was passed to to_tsquery. Diagnose: test inputs containing punctuation, quotes, and operators. Correct: use plainto_tsquery or websearch_to_tsquery for user text, reserving to_tsquery for trusted advanced syntax.
  • Symptom: the planner scans the table instead of using the GIN index. Cause: the indexed expression or column is not the one used in the predicate, statistics are stale, or the table is small enough that a scan is cheaper. Diagnose: run EXPLAIN (ANALYZE, BUFFERS) on the exact query. Correct: query the generated column directly, analyze the table, and test with representative row counts.
  • Symptom: writes slow down after adding search. Cause: each text update recomputes the stored vector and updates the GIN index. Diagnose: compare insert and update latency before and after the index, and inspect autovacuum and index maintenance activity. Correct: narrow indexed text, batch updates, tune maintenance settings, or move rarely searched fields out of the vector.

Security, Performance, and Reliability

Full-text search is not SQL injection protection by itself. Parameterize SQL normally, and also choose the right tsquery constructor. Do not expose raw to_tsquery syntax unless users are allowed to express operators. Limit result counts because broad terms can match many rows and ranking can be CPU intensive.

For reliability, generated columns are valuable because the database owns the derived value. A failed application request cannot leave the vector half-updated when the row update rolls back. The cost is write amplification and less flexibility in the generation expression. Treat the GIN index as a performance structure that must be measured under realistic insert, update, and search volume.

Hands-On Lab

Prerequisites: a PostgreSQL database where you can create and drop a table, and a SQL client such as psql. Use a scratch database, not a shared production schema.

  1. Create the articles table and GIN index from the syntax section.
  2. Insert the rows from Examples 1 and 3.
  3. Run the Example 2 search and verify that only the generated-column article is returned.
  4. Run the ranking query and verify that Search maintenance sorts before Index maintenance.
  5. Run EXPLAIN to confirm that the predicate can use the GIN index once the table is large enough for the planner to prefer it.
EXPLAIN
SELECT id, title
FROM articles
WHERE search_document @@ plainto_tsquery('english', 'generated search');

Verification: look for a plan containing a bitmap index scan on articles_search_document_gin when representative data makes the index worthwhile. On a tiny table, a sequential scan can be correct because reading a few rows is cheaper than using the index.

Cleanup: remove the lab objects when finished.

DROP TABLE IF EXISTS articles;

Assessment Exercises

  1. You need to search English marketing copy and exact product model numbers in the same table. Which text search configuration choices would you test, and what evidence would decide between them?
  2. A team wants to rank title matches above body matches. Show how the generated column should encode that preference and explain why ranking alone is not enough if the vector has no weights.
  3. A search box accepts arbitrary user input. Compare to_tsquery, plainto_tsquery, and websearch_to_tsquery for that interface.
  4. An update-heavy table becomes slower after adding a generated tsvector and GIN index. Name two measurements you would collect before changing the schema.
  5. Why can a generated column be safer than an application-maintained search column, and when would a trigger still be the better design?

Summary

PostgreSQL full-text search works by converting documents and searches into normalized linguistic structures, then using operators and indexes designed for lexeme lookup. Generated columns make the searchable vector part of the row definition, so it stays synchronized with source text. The main engineering choices are configuration, weighting, indexing, ranking, and whether the write cost is acceptable for the workload.

Progress is saved in this browser.