Eric Campbell 5 min read PostgreSQL

How to Use Full-Text Search in PostgreSQL

PostgreSQL includes robust full-text search capabilities that most developers overlook in favor of external solutions like Elasticsearch. For many applications, PostgreSQL's search features are...

Key Insights

  • PostgreSQL’s built-in full-text search is production-ready for most applications, eliminating the need for external search engines until you hit millions of documents or need sub-50ms response times.
  • Using GIN indexes on generated tsvector columns with automatic trigger updates provides the best balance of query performance and maintenance simplicity.
  • Proper ranking with ts_rank() and result highlighting with ts_headline() transforms basic text matching into a professional search experience comparable to dedicated search engines.

Introduction to Full-Text Search in PostgreSQL

PostgreSQL includes robust full-text search capabilities that most developers overlook in favor of external solutions like Elasticsearch. For many applications, PostgreSQL’s search features are faster to implement, easier to maintain, and perform well enough until you’re dealing with tens of millions of records.

The core advantage is simplicity: your search data lives alongside your transactional data, eliminating synchronization headaches, infrastructure complexity, and additional failure points. You get ACID guarantees, familiar SQL syntax, and one less service to monitor.

PostgreSQL full-text search works through two specialized data types: tsvector (a sorted list of lexemes—normalized words) and tsquery (the search query). The system handles stemming, stop words, and ranking automatically. For a blog with 100,000 posts or an e-commerce site with 500,000 products, PostgreSQL will handle your search needs without breaking a sweat.

Setting Up Your First Full-Text Search

Let’s start with a practical example. Assume you have a blog posts table:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO posts (title, body) VALUES
    ('PostgreSQL Full-Text Search Guide', 'Learn how to implement search functionality using PostgreSQL built-in features...'),
    ('Building REST APIs with Go', 'Go provides excellent tools for building high-performance REST APIs...'),
    ('PostgreSQL Performance Tuning', 'Optimizing PostgreSQL queries requires understanding indexes and execution plans...');

The simplest search uses to_tsvector() to convert text to searchable format and to_tsquery() to create a search query:

SELECT title, body
FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql');

This query searches for “postgresql” in both title and body. The @@ operator checks if the tsvector matches the tsquery. The 'english' parameter tells PostgreSQL to use English language rules for stemming and stop words.

For multiple search terms, use & (AND), | (OR), and ! (NOT):

SELECT title
FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql & search');

This finds posts containing both “postgresql” AND “search” (or their stems like “searching”).

Indexing for Performance

Running to_tsvector() on every query is expensive. For real applications, you need indexes. PostgreSQL offers two index types for full-text search: GIN (Generalized Inverted Index) and GiST (Generalized Search Tree).

GIN indexes are larger but faster for queries—use them for full-text search. GiST indexes are smaller and faster to update, but slower for queries. Stick with GIN unless you have extremely high write volume.

The best approach is a generated column with a GIN index:

ALTER TABLE posts 
ADD COLUMN search_vector tsvector 
GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED;

CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

Now your queries become simpler and much faster:

SELECT title, body
FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgresql');

Let’s verify the performance improvement:

-- Without index
EXPLAIN ANALYZE
SELECT title FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql');

-- With index
EXPLAIN ANALYZE
SELECT title FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgresql');

On a table with 100,000 rows, you’ll see query times drop from 200-300ms to 1-5ms. The index scan replaces a sequential scan with to_tsvector() computation on every row.

Advanced Search Features

Basic matching isn’t enough for production search. Users expect ranked results with the most relevant content first. The ts_rank() function scores documents based on how well they match the query:

SELECT 
    title,
    ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

For searches where you want to weigh title matches higher than body matches:

ALTER TABLE posts ADD COLUMN title_vector tsvector 
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, ''))) STORED;

ALTER TABLE posts ADD COLUMN body_vector tsvector 
GENERATED ALWAYS AS (to_tsvector('english', coalesce(body, ''))) STORED;

CREATE INDEX posts_title_idx ON posts USING GIN(title_vector);
CREATE INDEX posts_body_idx ON posts USING GIN(body_vector);

SELECT 
    title,
    ts_rank(title_vector, query) * 10 + ts_rank(body_vector, query) AS rank
FROM posts, to_tsquery('english', 'postgresql') query
WHERE title_vector @@ query OR body_vector @@ query
ORDER BY rank DESC;

This weights title matches 10x higher than body matches.

For phrase searches, use the <-> operator to find words in sequence:

SELECT title
FROM posts
WHERE search_vector @@ to_tsquery('english', 'full <-> text <-> search');

This matches “full text search” as a phrase. Use <N> for words separated by N positions.

For prefix matching (autocomplete-style searches), use the :* operator:

SELECT title
FROM posts
WHERE search_vector @@ to_tsquery('english', 'post:*');

This matches “post”, “posts”, “postgresql”, “posting”, etc.

Building a Search Function with Triggers

While generated columns are convenient, triggers offer more control. Here’s a pattern for maintaining search vectors with triggers:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    author TEXT,
    search_vector tsvector
);

CREATE OR REPLACE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(NEW.author, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update 
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);

The setweight() function assigns importance levels (A is highest, D is lowest). This gives you fine-grained control over ranking without complex multiplication in queries.

Real-World Implementation Patterns

Here’s a complete search function combining ranking, filtering, and pagination:

CREATE OR REPLACE FUNCTION search_posts(
    search_query TEXT,
    max_results INT DEFAULT 20,
    offset_val INT DEFAULT 0
)
RETURNS TABLE (
    id INT,
    title TEXT,
    excerpt TEXT,
    rank REAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.title,
        ts_headline('english', p.body, query, 'MaxWords=50, MinWords=25') AS excerpt,
        ts_rank(p.search_vector, query) AS rank
    FROM posts p, to_tsquery('english', search_query) query
    WHERE p.search_vector @@ query
    ORDER BY rank DESC
    LIMIT max_results
    OFFSET offset_val;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM search_posts('postgresql & performance', 10, 0);

The ts_headline() function highlights matching terms in context, giving users a preview of why each result matched. This is essential for good search UX.

Combining search with filters is straightforward:

SELECT 
    p.title,
    p.created_at,
    ts_rank(p.search_vector, query) AS rank
FROM posts p, to_tsquery('english', 'postgresql') query
WHERE p.search_vector @@ query
    AND p.created_at > NOW() - INTERVAL '1 year'
    AND p.status = 'published'
ORDER BY rank DESC, p.created_at DESC
LIMIT 20;

Limitations and When to Move Beyond

PostgreSQL full-text search has real limitations. Consider alternatives when you need:

Sub-50ms response times at scale: PostgreSQL search is fast, but dedicated search engines are faster. If you’re searching millions of documents and need consistent sub-50ms responses, Elasticsearch or Meilisearch will perform better.

Typo tolerance and fuzzy matching: PostgreSQL has basic fuzzy matching via trigram indexes (pg_trgm extension), but it’s not as sophisticated as dedicated search engines. If “postgre” should match “postgresql” reliably, you’ll need more advanced fuzzy matching.

Complex relevance tuning: While ts_rank() works well, dedicated search engines offer more sophisticated relevance algorithms, machine learning-based ranking, and easier A/B testing of ranking functions.

Distributed search: PostgreSQL isn’t designed for horizontal scaling of search workloads. If you need to search across multiple database instances or require search-specific replication, dedicated solutions are better.

Real-time analytics on search queries: Search engines often include analytics features for understanding search behavior, popular queries, and conversion tracking.

For most applications—blogs, e-commerce sites, SaaS products with moderate data volumes—PostgreSQL full-text search is the right choice. Start here, and migrate to dedicated search only when you have concrete evidence that PostgreSQL isn’t meeting your needs. The simplicity and reliability of keeping search in your database is worth a lot.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.