Full-Text Search in Databases: PostgreSQL and Elasticsearch

In this tutorial, you'll learn about Full. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Full-text search is the technique of searching documents and text fields for natural language queries using linguistic analysis -- including stemming, ranking, fuzzy matching, and relevance scoring -- going beyond simple LIKE patterns to find results even with misspellings, different word forms, and partial matches.

What You'll Learn

You will implement full-text search in PostgreSQL using tsvector and GIN indexes, configure Elasticsearch for high-scale search, understand ranking algorithms (TF-IDF, BM25), use fuzzy matching and autocomplete, and choose between database-native and dedicated search solutions.

Why Full-Text Search Matters

Simple LIKE '%keyword%' queries do not scale and miss relevant results. Doda Browser searches millions of bookmarked pages. PostgreSQL full-text search reduced search time from 800ms to 8ms and found results that LIKE queries missed due to word forms.

Full-Text Search Learning Path

flowchart LR
  A[SQL Basics] --> B[PostgreSQL]
  B --> C[Full-Text Search]
  C --> D[Elasticsearch]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

PostgreSQL Full-Text Search

PostgreSQL full-text search uses tsvector (document) and tsquery (query) data types with GIN indexes for fast text search.

Creating a Search Index

-- Add a tsvector column for search
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Populate with title and body text (weighted)
UPDATE articles
SET search_vector = setweight(to_tsvector('english', COALESCE(title, '')), 'A')
                  || setweight(to_tsvector('english', COALESCE(body, '')), 'B');

-- Create GIN index for fast search
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Trigger to keep search_vector updated
CREATE OR REPLACE FUNCTION articles_search_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
BEFORE INSERT OR UPDATE OF title, body ON articles
FOR EACH ROW
EXECUTE FUNCTION articles_search_update();

Searching

-- Basic search
SELECT id, title,
       ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'database indexing performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Expected output:

 id | title                                      | rank
----+---------------------------------------------+-------
 42 | PostgreSQL Indexing Performance Guide        | 0.891
 17 | Database Indexing Strategies Deep Dive       | 0.654
 89 | MySQL Performance Tuning with Indexes        | 0.432

Advanced Search Features

-- Phrase search (exact phrase match)
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');

-- Prefix matching
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'datab:*');

-- Boolean combinations
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & (index | performance) & !mysql');

-- Highlighting
SELECT id, title,
       ts_headline('english', body, plainto_tsquery('english', 'indexing'),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20')
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'indexing')
LIMIT 5;

Expected highlight output:

PostgreSQL <mark>indexing</mark> using B-tree and GIN indexes provides
fast search capabilities for full-text queries. The <mark>indexing</mark>
<a href="/design-patterns/strategy/">Strategy</a> depends on your data type and query patterns.

Relevance Scoring (ts_rank)

PostgreSQL uses a variant of TF-IDF ranking. Customize with normalization options:

SELECT id, title,
       ts_rank(search_vector, query, 32) AS rank_normalized
FROM articles, to_tsquery('english', 'database & indexing') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

Normalization flags:

• 0 (default): no normalization
• 1: divide by 1 + log(length)
• 2: divide by length
• 4: divide by mean document length
• 8: divide by unique words
• 16: divide by 1 + log(distinct words)
• 32: divide by self-log frequency

Elasticsearch Full-Text Search

Elasticsearch is a dedicated search engine built on Apache Lucene with inverted indexes, distributed search, and real-time indexing.

Index Mapping

PUT /articles
{
  "settings": {
    "analysis": {
      "analyzer": {
        "custom_english": {
          "type": "standard",
          "stopwords": "_english_"
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "title": {
        "type": "text",
        "analyzer": "custom_english",
        "boost": 2.0
      },
      "body": {
        "type": "text",
        "analyzer": "custom_english"
      },
      "author": {
        "type": "keyword"
      },
      "published_at": {
        "type": "date"
      }
    }
  }
}

Indexing Documents

POST /articles/_doc
{
  "title": "Database Indexing Performance Guide",
  "body": "Learn how to optimize database indexing strategies...",
  "author": "DodaTech",
  "published_at": "2026-06-22"
}

Search Query (BM25)

GET /articles/_search
{
  "query": {
    "multi_match": {
      "query": "database indexing performance",
      "fields": ["title^2", "body"],
      "type": "best_fields"
    }
  },
  "highlight": {
    "fields": {
      "title": {},
      "body": {}
    }
  },
  "size": 10
}

Fuzzy Search

GET /articles/_search
{
  "query": {
    "fuzzy": {
      "title": {
        "value": "indexing",
        "fuzziness": "AUTO"
      }
    }
  }
}

Hybrid Search Strategy

Combine PostgreSQL and Elasticsearch for optimal results:

def search_articles(query, page=1, page_size=20):
    """
    Hybrid search: Elasticsearch for initial search, PostgreSQL for
    structured filters and final data.
    """
    # Step 1: Search in Elasticsearch
    es_results = es.search(
        index="articles",
        body={
            "query": {
                "multi_match": {
                    "query": query,
                    "fields": ["title^2", "body"]
                }
            },
            "size": 100,  # Fetch more than needed for re-ranking
            "_source": ["id"]
        }
    )

    article_ids = [hit["_source"]["id"] for hit in es_results["hits"]["hits"]]

    if not article_ids:
        return []

    # Step 2: Fetch full data from PostgreSQL with structured filters
    conn = psycopg2.connect("dbname=mydb")
    cur = conn.cursor()
    cur.execute("""
        SELECT id, title, author, published_at, excerpt
        FROM articles
        WHERE id = ANY(%s)
          AND published_at >= NOW() - INTERVAL '1 year'
        ORDER BY array_position(%s, id::text)
    """, (article_ids, article_ids))

    results = cur.fetchall()
    cur.close()
    conn.close()

    return results

Comparison: PostgreSQL vs Elasticsearch

Common Full-Text Search Errors

1. Using LIKE '%term%' for Search

LIKE queries cannot use standard indexes and do not handle stemming, ranking, or relevance. Use full-text search for anything beyond exact prefix matching.

2. Not Using Weighted Fields

Title matches should rank higher than body matches. Use setweight('A') for title and setweight('B') for body in PostgreSQL, or boost in Elasticsearch.

3. Ignoring Stop Words

Common words like "the", "is", "at" add noise without value. PostgreSQL and Elasticsearch handle stop words by default. Customize for your domain.

4. No Stemming Configuration

Without stemming, "running" does not match "run". Both PostgreSQL and Elasticsearch support stemming via text search configurations and analyzers.

5. Re-indexing Entire Table on Every Change

Use database triggers to update tsvector incrementally instead of rebuilding the entire index.

6. Not Using GIN Index for PostgreSQL FTS

Without a GIN index on tsvector, full-text search falls back to sequential scans. Always create a GIN index.

7. Forcing All Search Through a Single System

Use the right tool: PostgreSQL FTS for simple search on moderate datasets, Elasticsearch for large-scale or advanced search requirements.

Practice Questions

1. What is a tsvector in PostgreSQL?

A data type that represents a document optimized for text search. It stores lexemes (normalized words) with position information for ranking.

2. How does BM25 ranking differ from TF-IDF?

BM25 is a modern evolution of TF-IDF that saturates term frequency (a word appearing 100 times is not 100x as relevant) and includes document length normalization.

3. When should you use Elasticsearch instead of PostgreSQL FTS?

When you need distributed search, fuzzy matching, autocomplete, or handle billions of documents. PostgreSQL FTS works well for moderate datasets (under 100M docs).

4. What is a GIN index and why is it needed for FTS?

A Generalized Inverted Index that maps lexemes to document locations. It enables fast search queries on tsvector columns, similar to an inverted index.

5. Challenge: Design a search system for a documentation site.

The site has 50,000 documentation pages, 10M words total. Search must handle: stemming, title boosts, partial matching, and category filtering. Answer: Use PostgreSQL FTS with a GIN index. Title gets weight A, body gets weight B. Use to_tsvector('english') for stemming. Add category filtering with a B-tree index on category_id. Create a trigger to keep search_vector updated. Use ts_headline() for highlighting. Add a separate trigram index for autocomplete with pg_trgm extension.

FAQ

Try It Yourself

Build a full-text search system:

1. Create a table with 1000 sample articles
2. Add a tsvector column with weighted title and body
3. Create a GIN index
4. Search for articles containing "database performance"
5. Compare execution time and relevance against LIKE '%database%'
6. Implement highlighting with ts_headline
7. Test stemming: search for "indexing" and verify "index" matches

What's Next

You have learned full-text search in PostgreSQL and Elasticsearch, including tsvector, GIN indexes, BM25 ranking, fuzzy search, and hybrid search strategies. Start by adding a GIN-indexed tsvector column to your most frequently searched table today.