PostgreSQL Can Do That? Building a Production-Ready Search Engine with FTS and Trigram | Part-1

Modern applications often require powerful search capabilities. Many teams immediately reach for tools like Elasticsearch, OpenSearch, or Algolia, but PostgreSQL itself provides surprisingly powerful search capabilities out of the box.

In this article, we will build a production-grade search system using only PostgreSQL.

📑 Table of Contents

1. 🔎 Search Use Case
2. 🏗️ Search Architecture Diagram
3. 🧱 Schema Design for Search
4. ⚙️ Automatically Generating Search Vectors
5. 🗂️ Required Indexes
6. 🧠 The Search Query Explained
7. ✨ Features Supported by the Query
8. 📊 Relevance Ranking
9. 🧭 When PostgreSQL Search Works Well
10. 🔮 Preview: Hybrid Search & RRF (Part 2)

In Part 2, we will explore how techniques like Reciprocal Rank Fusion (RRF) and semantic search can further improve search quality.

1. Search Use Case

Assume we are building a platform where users can search across different types of content such as:

• Books
• Courses
• Magazines
• Articles

Users may search for things like:

System Design course
machine learning book
python magazine

Our search system should support:

• Keyword search
• Partial matches
• Typo tolerance
• Relevance ranking

To support this, we maintain a unified search table called **search_document**.

2. Search Architecture Diagram

3. Schema Design for Search

Instead of searching across multiple tables, we maintain a denormalized search index table. I have currently used SQL triggers to insert data into search document incase of any insert/update/delete operation is performed in the source tables for e.g courses table or magazines table.

Table Schema

CREATE TABLE search_document
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL, --- book , course , magazine
    entity_id BIGINT NOT NULL, --- id of entity_type
    lang_code VARCHAR(5) NOT NULL, --- supports multilingual documents
    title TEXT,
    description TEXT,
    subject TEXT,
    search_text TEXT,
    tsv TSVECTOR, 
    entity_rank INTEGER DEFAULT 0 NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(entity_type, entity_id, lang_code)
);

4. Automatically Generating Search Vectors

Instead of manually populating the search vector, we use a PostgreSQL trigger.

CREATE OR REPLACE FUNCTION search_document_tsv_trigger()
RETURNS trigger AS
$$
BEGIN
    NEW.search_text :=
        CONCAT_WS(' ',
            COALESCE(NEW.title,''),
            COALESCE(NEW.description,''),
            COALESCE(NEW.subject,'')
        );

    NEW.tsv :=
        setweight(to_tsvector('simple', COALESCE(NEW.title,'')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(NEW.description,'')), 'B') ||
        setweight(to_tsvector('simple', COALESCE(NEW.subject,'')), 'C');

    RETURN NEW;
END
$$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trg_search_document_tsv
BEFORE INSERT OR UPDATE
ON search_document
FOR EACH ROW
EXECUTE FUNCTION search_document_tsv_trigger();

Trigger:

CREATE TRIGGER trg_search_document_tsv
BEFORE INSERT OR UPDATE
ON search_document
FOR EACH ROW
EXECUTE FUNCTION search_document_tsv_trigger();

5. Required Indexes

Search queries must be backed by proper indexes.

Full-Text Search Index

CREATE INDEX idx_search_document_tsv
ON search_document
USING GIN(tsv);

GIN indexes are optimized for FTS operations.

Trigram Extension

Enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Trigram Index

CREATE INDEX idx_search_document_trgm
ON search_document
USING GIN(search_text gin_trgm_ops);

This enables fuzzy matching and typo tolerance.

6. The Search Query Explained

The query combines:

• Full-text search
• Trigram similarity
• Substring matching

SELECT
    sd.entity_id AS entityId,
    sd.entity_type AS entityType,
    sd.title,
    sd.description,
    sd.lang_code,
    GREATEST(
        ts_rank(sd.tsv, plainto_tsquery('simple', :query)),
        similarity(sd.search_text, :query)
    ) AS rank,
    sd.entity_rank
FROM search_document sd
WHERE
    sd.tsv @@ plainto_tsquery('simple', :query)
    OR sd.search_text % :query
    OR sd.title ILIKE '%' || :query || '%'
ORDER BY
    sd.entity_rank ASC,
    rank DESC,
    sd.created_at DESC
LIMIT 20;

7. Features Supported by the Query

Full-Text Search

ts_rank(sd.tsv, plainto_tsquery(...))

Matches keywords intelligently.

Example:

"machine learning course"

Finds:

course about machine learning
learning machine basics

Trigram Similarity

search_text % :query

Allows fuzzy matches.

Example:

machin learnig

still finds:

machine learning

Partial Matches

title ILIKE '%query%'

Useful for substring searches.

Example:

python

Matches:

python programming course

8. Relevance Ranking

Ranking is calculated using:

GREATEST(FTS score, trigram similarity)

Documents are then ordered by:

entity_rank
rank
created_at

This ensures:

1. High-priority content appears first
2. Relevant documents rank higher
3. Newer content appears earlier when relevance ties

9. When PostgreSQL Search Works Well

This PostgreSQL search setup works extremely well for:

• Content platforms
• Knowledge bases
• Documentation sites
• Blogs
• Course catalogs

It provides a lightweight alternative to Elasticsearch.

10. Preview: Hybrid Search & RRF (Part 2)

As datasets grow, search ranking becomes more challenging.

Different search methods may produce different result sets:

• Full-text search
• Fuzzy matching
• Semantic search

Combining these signals effectively becomes important.

One technique often used in modern search engines is Reciprocal Rank Fusion (RRF).

RRF merges results from multiple ranking strategies and improves overall search quality.

We will explore this technique in Part 2.

Conclusion

PostgreSQL provides powerful built-in tools for building a robust search system:

• Full-text search
• Fuzzy matching
• Ranking
• Index optimization

With proper schema design and indexing, PostgreSQL can power search for many production systems without requiring external search engines.