PostgreSQL Full-Text Search: tsvector, GIN Indexes, and ts_rank in Production

A mid-sized e-commerce platform came to JusDB with a product catalog of 4.2 million SKUs. Their search endpoint was hitting a LIKE '%keyword%' query against a single TEXT column, bypassing every index on the table and triggering sequential scans that pegged CPU at 100% for three to eight seconds per request during peak hours. Adding an index on the column helped not at all — PostgreSQL cannot use a B-tree index for a leading-wildcard LIKE pattern. They were one marketing campaign away from a full outage.

We replaced the LIKE query with PostgreSQL's built-in full-text search engine in a single afternoon. The same product search query now runs in under 12ms, uses a GIN index on a pre-computed tsvector column, and returns ranked results sorted by relevance — something the LIKE approach could never provide. The index took 18 minutes to build and added 380 MB of storage on a 22 GB table.

PostgreSQL ships a complete full-text search system — lexer, stemmer, stop-word lists, language dictionaries, relevance ranking, and query syntax — with no extensions required. Most engineering teams reach for Elasticsearch when a LIKE query stops scaling, without realizing the solution is already in the database they are already running.

This guide covers everything you need to deploy production-grade full-text search inside PostgreSQL: the tsvector and tsquery types, GIN vs GiST indexes, ranking functions, multi-column search, and an honest comparison against Elasticsearch and pg_trgm.

Background

PostgreSQL has shipped a full-text search subsystem since version 8.3 (2008). Unlike an external search engine, it runs inside the database process, shares the same transaction and MVCC semantics as your relational data, and requires no separate infrastructure to deploy, monitor, or keep in sync.

The system is built around two specialized data types:

• tsvector — a sorted list of normalized lexemes (stems) with their positional information. Produced by parsing and normalizing a text string: stop words are removed, words are reduced to their root form ("running" becomes "run"), and positions are recorded for phrase-distance queries.
• tsquery — a parsed search query consisting of lexemes combined with Boolean operators (& for AND, | for OR, ! for NOT) and optionally phrase operators (<-> for adjacent, <2> for within-2-positions).

The match operator @@ returns true when a tsquery matches a tsvector. This is the operator GIN and GiST indexes are built to accelerate.

Full-text search in PostgreSQL is language-aware. The text search configuration — 'english', 'french', 'german', 'simple', etc. — determines which dictionary is used for stemming and which words are treated as stop words. This is both the most powerful and the most commonly misunderstood aspect of PostgreSQL FTS.

How It Works

When you call to_tsvector('english', 'The quick brown foxes are running fast'), PostgreSQL applies the english text search configuration in sequence:

1. Tokenize — split into individual tokens: words, numbers, URLs, email addresses, etc.
2. Normalize — apply the dictionary: "foxes" stems to "fox", "running" stems to "run", "are" and "The" are stop words and are removed entirely.
3. Store with positions — the result is 'brown':3 'fast':7 'fox':4 'quick':2 'run':6. Stop words preserve position gaps so phrase-distance queries remain accurate.

The result is a compact, indexed representation of the text's semantic content. Two documents with different surface forms but the same root words — "The system is running" and "Systems run" — produce overlapping tsvector values and will both match a query for "run".

The four query-building functions serve different purposes:

• to_tsquery('english', 'run & fast') — expects pre-formatted Boolean query syntax. Raises an error on stop words. Use for programmatically constructed queries.
• plainto_tsquery('english', 'quick brown fox') — converts plain text to an AND query. Safe for direct user input. Equivalent to 'quick' & 'brown' & 'fox'.
• phraseto_tsquery('english', 'quick brown fox') — requires the terms to appear in adjacent positions. Useful for exact-phrase matching.
• websearch_to_tsquery('english', 'quick -slow "brown fox"') — parses Google-style syntax: quoted phrases, minus-prefix exclusion, OR keywords. The safest and most user-friendly function for search boxes.

Configuration and Setup

Step 1: Understand Your Schema

Start with a representative table. This example uses a product catalog:

CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    description TEXT,
    brand       TEXT,
    category    TEXT,
    tags        TEXT[],
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMPTZ DEFAULT now()
);

Step 2: Add a Pre-Computed tsvector Column

Calling to_tsvector() inline on every query row is the most common performance mistake. For a 4-million-row table, it means re-parsing and re-stemming every row on every query — even with a GIN index, the index cannot be used if the expression does not match the indexed expression exactly. The correct approach is a stored, generated column:

-- PostgreSQL 12+: use a generated column (automatically maintained)
ALTER TABLE products
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(brand, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'C') ||
        setweight(to_tsvector('english', coalesce(category, '')), 'D')
    ) STORED;

setweight() assigns a relevance weight to each source column: 'A' is highest, 'D' is lowest. When ts_rank() scores results, matches in the product name outrank matches in the description. The || operator concatenates multiple tsvector values.

Step 3: Build the GIN Index

-- GIN index on the pre-computed column
-- This is what makes full-text search fast
CREATE INDEX products_search_vector_gin_idx
    ON products
    USING gin (search_vector);

-- For large tables, set maintenance_work_mem before building
-- to avoid slow disk-based sort steps:
SET maintenance_work_mem = '1GB';
CREATE INDEX products_search_vector_gin_idx
    ON products USING gin (search_vector);

On the 4.2-million-row product table, this index build took 18 minutes with maintenance_work_mem = '2GB' and produced a 380 MB index on disk. After the build, search queries execute in 8–15ms.

Step 4: Query with Ranking

-- Basic full-text search with ranking
SELECT
    id,
    name,
    brand,
    ts_rank(search_vector, query) AS rank
FROM products,
     websearch_to_tsquery('english', 'wireless noise cancelling headphones') query
WHERE
    is_active = TRUE
    AND search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- With ts_headline for result snippets
SELECT
    id,
    name,
    ts_headline(
        'english',
        description,
        websearch_to_tsquery('english', 'wireless noise cancelling headphones'),
        'StartSel=, StopSel=, MaxWords=35, MinWords=15, MaxFragments=2'
    ) AS excerpt,
    ts_rank(search_vector, websearch_to_tsquery('english', 'wireless noise cancelling headphones')) AS rank
FROM products
WHERE
    is_active = TRUE
    AND search_vector @@ websearch_to_tsquery('english', 'wireless noise cancelling headphones')
ORDER BY rank DESC
LIMIT 10;

ts_headline() is intentionally called on the raw description column, not the tsvector — it operates on the original text to produce human-readable snippets with highlighted terms. The options string controls snippet length, the highlight tags, and whether to return multiple fragments.

Step 5: Multi-Column Search with Custom Weights

The generated column approach above already handles multi-column search. For cases where you need dynamic weight adjustment at query time:

-- Construct a weighted tsvector on the fly for dynamic weight tuning
-- (only necessary if weights need to change per query; use generated column otherwise)
SELECT
    id,
    name,
    ts_rank(
        setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'C'),
        plainto_tsquery('english', $1)
    ) AS rank
FROM products
WHERE
    (
        setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'C')
    ) @@ plainto_tsquery('english', $1)
ORDER BY rank DESC
LIMIT 20;
-- Note: this form does NOT use the pre-built GIN index on search_vector.
-- Use only for prototyping or infrequent queries on small tables.

Step 6: Custom Text Search Configurations

For domain-specific content — medical records, legal documents, source code — the default English dictionary may stem words incorrectly or treat important terms as stop words. Create a custom configuration:

-- Create a custom text search configuration based on English
-- but with no stop words (useful for product SKUs, part numbers, codes)
CREATE TEXT SEARCH CONFIGURATION product_search (COPY = english);

-- Replace the English stop words dictionary with a simple one (no stop words)
ALTER TEXT SEARCH CONFIGURATION product_search
    ALTER MAPPING FOR asciiword, word
    WITH english_stem;

-- Use the custom configuration
SELECT to_tsvector('product_search', 'the quick brown fox')::text;
-- Result includes 'the' as a lexeme instead of discarding it

Performance and Best Practices

Full-text search performance in PostgreSQL is largely determined by index type selection and whether the pre-computed vector is being used correctly.

GIN vs GiST: The Real Trade-off

Both GIN (Generalized Inverted Index) and GiST (Generalized Search Tree) support the @@ operator, but they have fundamentally different characteristics:

• GIN stores an inverted index: for each lexeme, a sorted list of all document IDs containing that lexeme. Lookups are fast because finding documents containing "fox" is a direct index entry lookup. Build is slower than GiST and the index is larger (typically 2–4x the tsvector column size), but query performance is 3–10x better than GiST for typical FTS workloads.
• GiST stores a lossy, balanced tree structure. It is faster to build and smaller on disk, but queries require re-checking each candidate row (the index may have false positives). For write-heavy tables where the index is rebuilt frequently, GiST may be preferable. For read-heavy production search, use GIN.

-- Check index size and usage statistics
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products'
ORDER BY pg_relation_size(indexname::regclass) DESC;

Handling Update Overhead

GIN indexes have higher update overhead than B-tree indexes. Each insert or update must modify the inverted index entries for every lexeme in the document. For high-write tables, PostgreSQL mitigates this with a "pending list" — new entries are collected in a small pending list and merged into the main GIN structure in batches.

-- Tune the GIN pending list size (default 4MB)
-- Larger pending list = fewer, larger merges = better insert throughput
-- but slightly slower queries until the next merge
ALTER INDEX products_search_vector_gin_idx
    SET (fastupdate = on, gin_pending_list_limit = 16384); -- 16MB

-- Manually trigger a pending list flush if needed
SELECT gin_clean_pending_list('products_search_vector_gin_idx'::regclass);

Phrase Search and Proximity Queries

-- Exact phrase search (adjacent words)
SELECT id, name
FROM products
WHERE search_vector @@ phraseto_tsquery('english', 'noise cancelling');

-- Words within N positions of each other
-- <2> means "within 2 positions"
SELECT id, name
FROM products
WHERE search_vector @@ to_tsquery('english', 'noise <2> cancelling');

-- Negation: find documents with "wireless" but not "bluetooth"
SELECT id, name
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & !bluetooth');

Comparison: PostgreSQL FTS vs Elasticsearch vs pg_trgm

Key Takeaways

Working with JusDB on PostgreSQL Full-Text Search

Implementing PostgreSQL full-text search correctly — choosing between GIN and GiST, setting up generated columns with proper weights, tuning fastupdate for your write volume, and combining FTS with pg_trgm for autocomplete — requires production PostgreSQL experience. The difference between a working FTS implementation and a fast one is often a single misconfigured index expression or a language mismatch that shows up as mysteriously empty results in a specific locale.

JusDB's PostgreSQL consultants have deployed full-text search on catalogs ranging from 50,000 to 40 million rows, across e-commerce, legal document platforms, SaaS knowledge bases, and multi-language content sites. We cover schema design, index strategy, query optimization, ts_headline snippet rendering, custom dictionary configuration, and monitoring — before go-live, not after the first production incident.

PostgreSQL Consulting Services Talk to a PostgreSQL Expert

We also offer a PostgreSQL search audit for teams currently running LIKE queries or considering an Elasticsearch migration — reviewing your query plans, index configuration, and data model to identify the fastest path to production-quality search before committing to a new system.

Related Reading

• PostgreSQL Explained: A Complete Guide for Modern Applications — the foundations of PostgreSQL data types, indexing, and query planning before diving into FTS specifics
• PostgreSQL Table Partitioning with pg_partman — how to scale FTS across partitioned tables when row counts exceed tens of millions
• OpenSearch vs Elasticsearch in 2026 — a detailed comparison for teams evaluating whether to move FTS workloads out of PostgreSQL into a dedicated search engine