Full Text Search Implementation

Full text search implementation enables applications to find relevant documents, records, or content based on natural language queries, not just exact matches. It matters when users need to discover data across large volumes of unstructured or semi-structured content—such as product descriptions, blog posts, customer support tickets, or user-generated content—and when search performance and relevance must scale beyond simple LIKE queries or index-based filtering.

Indexing Strategy

Indexing transforms raw text into a searchable structure. The process begins with extracting text from documents, normalizing it, and building inverted indexes that map terms to the documents containing them.

Text extraction typically involves parsing fields from the database or document store. For a posts table with title, body, and tags, indexing might be triggered by a CREATE INDEX command with a custom expression:

CREATE INDEX posts_search_idx ON posts 
USING gin (
  to_tsvector('english', 
    coalesce(title, '') || ' ' || 
    coalesce(body, '') || ' ' || 
    coalesce(tags, '')
  )
);

The to_tsvector function converts text into a tsvector—a sorted list of terms with positions and weights. The gin (Generalized Inverted Index) access method supports efficient @@ (rank) queries.

A common mistake is using tsvector without tsvector normalization. The default to_tsvector applies no normalization. For better performance, include a tsconfig:

CREATE INDEX posts_search_idx ON posts 
USING gin (
  to_tsvector('english', 
    coalesce(title, '') || ' ' || 
    coalesce(body, '') || ' ' || 
    coalesce(tags, '')
  ) 
  WITH (fastupdate = on)
);

The fastupdate option enables incremental updates to the GIN index without full rebuilds. This is critical for high-write workloads.

Indexing pipelines often include preprocessing: removing stop words, stemming, and applying custom dictionaries. A full pipeline might be implemented as a pg_background job or in a message queue:

-- In a trigger function
CREATE OR REPLACE FUNCTION index_post() RETURNS trigger AS $$
BEGIN
  NEW.search_vector = to_tsvector(
    'english',
    coalesce(NEW.title, '') || ' ' ||
    coalesce(NEW.body, '') || ' ' ||
    coalesce(NEW.tags, '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER index_post_trigger
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION index_post();

Querying with Relevance Scoring

Once indexed, queries leverage tsquery to match terms with relevance. The @@ operator returns a boolean, but ts_rank provides a numeric score for ranking results.

SELECT id, title, body, 
       ts_rank(search_vector, query) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'machine learning')
ORDER BY rank DESC;

The to_tsquery function parses natural language into a tsquery, which can include boolean operators, phrase matching, and weights.

-- Phrase query with weights
SELECT id, title, body,
       ts_rank(search_vector, 
         'machine & learning & (neural | deep)'::tsquery
       ) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'machine learning')
ORDER BY rank DESC;

A subtle failure mode: using to_tsquery on unnormalized text. If the query contains "neural networks", the default english dictionary does not normalize "neural" to "neur", so "neur" does not match "neural". The solution is to define a custom tsconfig with textsearch dictionaries.

-- Create a custom config for better matching
CREATE TEXT SEARCH CONFIGURATION my_search (COPY = english);

ALTER TEXT SEARCH CONFIGURATION my_search
  ADD MAPPING FOR asciiword WITH simple;

ALTER TEXT SEARCH CONFIGURATION my_search
  ADD MAPPING FOR word WITH simple;

-- Use it in queries
SELECT id, title, body,
       ts_rank(search_vector, 
         to_tsquery('my_search', 'neural networks')
       ) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('my_search', 'neural networks');

Query Optimization

Efficiency depends on how queries are executed. The EXPLAIN command reveals bottlenecks:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, body,
       ts_rank(search_vector, query) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'data science')
ORDER BY rank DESC
LIMIT 10;

The BUFFERS option shows which buffers are used, helping to diagnose cache misses.

A common performance issue is the cost of to_tsvector during INSERT or UPDATE. If indexing is synchronous, writes block on tsvector computation. Asynchronous indexing via a job queue reduces latency.

-- Example: enqueue indexing jobs
INSERT INTO indexing_jobs (table_name, row_id, query)
VALUES ('posts', 123, 'data science');

-- Background worker process
CREATE OR REPLACE FUNCTION process_indexing_jobs() RETURNS void AS $$
DECLARE
  job RECORD;
BEGIN
  LOOP
    SELECT * FROM indexing_jobs
    WHERE processed = false
    ORDER BY created_at
    LIMIT 1
    INTO job;

    EXIT WHEN job IS NULL;

    UPDATE posts
    SET search_vector = to_tsvector('english', 
      coalesce(title, '') || ' ' ||
      coalesce(body, '') || ' ' ||
      coalesce(tags, '')
    )
    WHERE id = job.row_id;

    UPDATE indexing_jobs
    SET processed = true, processed_at = now()
    WHERE id = job.id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Run in background
SELECT pg_background.start_process('process_indexing_jobs');

Query-Time Enhancements

Relevance can be improved with query-time enhancements: highlighting, fuzzy matching, and query expansion.

Highlighting returns marked-up text showing where search terms appear:

SELECT id, title,
       ts_headline('english',
         body,
         to_tsquery('english', 'machine learning'),
         'MaxFragments=3, FragmentSeparator=..., HighlightAll=true'
       ) AS excerpt
FROM posts
WHERE search_vector @@ to_tsquery('english', 'machine learning')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'machine learning')) DESC;

The ts_headline function supports options like MaxFragments, FragmentSeparator, and HighlightAll. A frequent mistake is assuming ts_headline applies only to the body field, but it works on any text input.

Fuzzy matching allows for spelling corrections and approximate matching:

SELECT id, title, body,
       ts_rank(search_vector, 
         to_tsquery('english', 'mashine learning')
       ) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'mashine learning');

The to_tsquery function implicitly applies websearch_to_tsquery, which includes phraseto_tsquery, placetoken, and weighting. For better fuzzy matching, use websearch_to_tsquery with Fuzzy option:

SELECT id, title, body,
       ts_rank(search_vector,
         websearch_to_tsquery('english', 'mashine learning')
       ) AS rank
FROM posts
WHERE search_vector @@ websearch_to_tsquery('english', 'mashine learning');

Query expansion improves recall by adding synonyms or related terms:

-- Expand 'machine learning' to include 'ML', 'artificial intelligence'
SELECT id, title, body,
       ts_rank(search_vector,
         to_tsquery('english', 'machine learning | ML | artificial intelligence')
       ) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'machine learning')
ORDER BY rank DESC;

This pattern is often implemented by precomputing a synonym map stored in a synonyms table:

CREATE TABLE synonyms (
  term text PRIMARY KEY,
  expanded tsquery
);

INSERT INTO synonyms (term, expanded) VALUES
  ('machine learning', 'machine learning | ML | artificial intelligence'),
  ('data science', 'data science | data analytics | data mining');

-- Query using expansion
SELECT id, title, body,
       ts_rank(search_vector,
         (
           SELECT expanded
           FROM synonyms
           WHERE term = 'machine learning'
         )
       ) AS rank
FROM posts
WHERE search_vector @@ (
  SELECT expanded
  FROM synonyms
  WHERE term = 'machine learning'
);

Failure Modes and Diagnostics

Full text search breaks in subtle ways. One common failure is missing tsvector updates after INSERT or UPDATE. Without a trigger or background job, queries return stale results.

Another failure occurs when ts_rank does not account for document length. Long documents score higher even if the query terms appear only once. The fix is to use ts_rank_cd, which normalizes by document length:

SELECT id, title, body,
       ts_rank_cd(search_vector, query) AS rank
FROM posts
WHERE search_vector @@ query
ORDER BY rank DESC;

The cd version (co-occurrence distance) also improves ranking by considering term proximity.

A silent failure: to_tsvector fails on NULL values. If tags is NULL, coalesce(tags, '') returns '', but to_tsvector on '' produces a tsvector with no entries. This means a post with only a title and NULL tags is not matched by any query.

The solution is to use coalesce(tags, '') before to_tsvector, or use tsvector with null handling:

-- Ensure non-null input
SELECT id, title, body,
       ts_rank(
         to_tsvector('english', 
           coalesce(title, '') || ' ' ||
           coalesce(body, '') || ' ' ||
           coalesce(tags, '')
         ),
         query
       ) AS rank
FROM posts
WHERE to_tsvector('english', 
     coalesce(title, '') || ' ' ||
     coalesce(body, '') || ' ' ||
     coalesce(tags, '')
   ) @@ query;

Error messages often reveal missing configurations. A ts_rank query returning NULL ranks likely means the search_vector column is NULL in some rows, or the to_tsvector function was not applied.

A psql session with EXPLAIN ANALYZE helps diagnose index usage:

EXPLAIN (ANALYZE, BUFFERS, COSTS)
SELECT id, title, body,
       ts_rank_cd(search_vector, to_tsquery('english', 'machine learning'))
FROM posts
WHERE search_vector @@ to_tsquery('english', 'machine learning')
ORDER BY rank DESC
LIMIT 10;

The output includes Seq Scan, Index Scan, and Bitmap Heap Scan steps. A Bitmap Index Scan on search_vector indicates the query is using the GIN index efficiently.

Maintenance and Monitoring

Index maintenance includes rebuilding and vacuuming. The REINDEX command rebuilds an index:

REINDEX INDEX posts_search_idx;

For large indexes, REINDEX can be run online:

REINDEX INDEX CONCURRENTLY posts_search_idx;

This avoids blocking queries during rebuilds.

Vacuuming is crucial for GIN indexes. The VACUUM command collects statistics and reclaims space:

VACUUM (ANALYZE, VERBOSE) posts;

The ANALYZE option updates statistics used by the query planner. The VERBOSE flag shows progress.

Monitoring the health of full text search involves checking for:

• Missing or stale search_vector values
• High query latency in EXPLAIN ANALYZE
• Inefficient tsquery parsing (e.g., to_tsquery returns NULL for malformed input)
• High pg_stat_user_indexes idx_scan and idx_tup_read counts

A simple health check script:

#!/bin/bash


echo "=== Full Text Search Health Check ==="

# Check for stale search_vector
psql -c "
SELECT
  count(*) AS stale_rows,
  avg(pg_size_pretty(pg_total_relation_size('posts'))) AS avg_table_size
FROM posts
WHERE search_vector IS NULL OR search_vector = ''::tsvector
"

# Check index usage
psql -c "
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND tablename = 'posts'
"

The script can be scheduled via cron or systemd.

Summary

Full text search implementation is not just about CREATE INDEX and @@. It is a pipeline: extract, transform, index, query, rank, and maintain. Success depends on correct use of to_tsvector, to_tsquery, ts_rank, and ts_headline. Key failure modes include missing tsvector updates, incorrect ranking due to document length, and NULL handling in to_tsvector. Monitoring through EXPLAIN, VACUUM, and REINDEX ensures sustained performance.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.