Full-Text Search with tsvector and tsquery

Advanced18 min

Build a powerful full-text search engine using PostgreSQL native tsvector columns, GIN indexes, and ranked query results.

Prerequisites

-PostgreSQL 14+
-A table with text content to search

Steps

Add a tsvector column to your table

Create a generated tsvector column that automatically stays in sync with the source text.

$ psql -U postgres -d mydb -c "ALTER TABLE articles ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;"

GENERATED ALWAYS AS creates a stored computed column that updates automatically when title or body change.

Create a GIN index for fast search

Add a GIN index on the tsvector column for efficient full-text queries.

$ psql -U postgres -d mydb -c "CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);"

Run a basic full-text search

Search for articles matching a text query with automatic stemming.

$ psql -U postgres -d mydb -c "SELECT id, title, ts_rank(search_vector, to_tsquery('english', 'database & performance')) AS rank FROM articles WHERE search_vector @@ to_tsquery('english', 'database & performance') ORDER BY rank DESC LIMIT 10;"

Use websearch_to_tsquery for user-friendly input

Convert natural language search input into a tsquery without manual operators.

$ psql -U postgres -d mydb -c "SELECT id, title FROM articles WHERE search_vector @@ websearch_to_tsquery('english', 'how to optimize database queries') ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'how to optimize database queries')) DESC LIMIT 10;"

websearch_to_tsquery handles quoted phrases, OR, and - (NOT) operators like a web search engine.

Generate search result snippets

Highlight matching terms in search results with ts_headline.

$ psql -U postgres -d mydb -c "SELECT id, title, ts_headline('english', body, to_tsquery('english', 'performance'), 'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15') AS snippet FROM articles WHERE search_vector @@ to_tsquery('english', 'performance') LIMIT 5;"

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL Full-Text Search Setup
set -euo pipefail

DB="mydb"
USER="postgres"

echo "=== Setting up full-text search ==="
psql -U "$USER" -d "$DB" -c "
-- Add tsvector column (if table exists)
ALTER TABLE articles ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

-- Create GIN index
CREATE INDEX IF NOT EXISTS idx_articles_search ON articles USING GIN (search_vector);
"

echo ""
echo "=== Testing search ==="
psql -U "$USER" -d "$DB" -c "
SELECT id, title,
       ts_rank(search_vector, q) AS rank,
       ts_headline('english', body, q, 'StartSel=**, StopSel=**, MaxWords=30') AS snippet
FROM articles, websearch_to_tsquery('english', 'database optimization') AS q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 5;
"

echo ""
echo "=== Search index stats ==="
psql -U "$USER" -d "$DB" -c "
SELECT pg_size_pretty(pg_relation_size('idx_articles_search')) AS index_size,
       (SELECT count(*) FROM articles) AS total_articles;
"

echo ""
echo "=== Available text search configurations ==="
psql -U "$USER" -d "$DB" -c "\dF+"

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL Full-Text Search Setup set -euo pipefail DB="mydb" USER="postgres" echo "=== Setting up full-text search ===" psql -U "$USER" -d "$DB" -c " -- Add tsvector column (if table exists) ALTER TABLE articles ADD COLUMN IF NOT EXISTS search_vector tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B') ) STORED; -- Create GIN index CREATE INDEX IF NOT EXISTS idx_articles_search ON articles USING GIN (search_vector); " echo "" echo "=== Testing search ===" psql -U "$USER" -d "$DB" -c " SELECT id, title, ts_rank(search_vector, q) AS rank, ts_headline('english', body, q, 'StartSel=**, StopSel=**, MaxWords=30') AS snippet FROM articles, websearch_to_tsquery('english', 'database optimization') AS q WHERE search_vector @@ q ORDER BY rank DESC LIMIT 5; " echo "" echo "=== Search index stats ===" psql -U "$USER" -d "$DB" -c " SELECT pg_size_pretty(pg_relation_size('idx_articles_search')) AS index_size, (SELECT count(*) FROM articles) AS total_articles; " echo "" echo "=== Available text search configurations ===" psql -U "$USER" -d "$DB" -c "\dF+"