PostgreSQL Full-Text Search: Better Than You Think, No Elasticsearch Required

The instinct to reach for Elasticsearch or Typesense the moment search appears in a feature list is understandable but often wrong. PostgreSQL's full-text search is genuinely capable, and adding Elasticsearch to your architecture adds operational complexity — another service to deploy, monitor, keep in sync, and scale — that most applications do not need.

For many applications, PostgreSQL search is not just good enough. It is the right choice.

Understanding tsvector and tsquery

PostgreSQL represents searchable documents as tsvector — a sorted list of lexemes (normalized word forms) with position information. The query language is tsquery — a boolean expression over lexemes.

-- Convert text to searchable tsvector
SELECT to_tsvector('english', 'PostgreSQL is a powerful open-source database');
-- Output: 'databas':8 'open-sourc':6 'postgreSql':1 'powerful':4

-- Convert a search query to tsquery
SELECT to_tsquery('english', 'postgresql & full-text');
-- Output: 'postgresql' & 'full-text'

The text processing pipeline:

1. Parser: splits text into tokens (words, URLs, email addresses, etc.)
2. Dictionary: normalizes tokens — removes stop words, applies stemming
3. Result: a vector of normalized terms with position information

The english configuration does English-specific processing. Other configurations handle other languages: french, german, spanish, etc.

Setting Up Full-Text Search

Add a generated search_vector column to your table:

ALTER TABLE posts ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'C')
  ) STORED;

CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

The setweight function assigns different weights to different fields. Weight A (most important) through D (least) affects ranking — title matches rank higher than body matches.

The GENERATED ALWAYS AS ... STORED syntax creates a column that is automatically maintained by PostgreSQL. No triggers, no application code to keep it in sync.

Basic Search Queries

-- Find posts matching a search query
SELECT
  id,
  title,
  description,
  ts_rank(search_vector, query) AS rank
FROM posts,
  to_tsquery('english', 'postgresql & indexing') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

The @@ operator tests whether the document matches the query. ts_rank computes a relevance score from 0 to 1 based on how frequently matching terms appear.

Handling User Input Safely

User search queries need sanitization. Raw user input can contain characters that break to_tsquery. Use plainto_tsquery or websearch_to_tsquery instead:

-- plainto_tsquery: treats the whole string as AND of words
SELECT * FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'postgresql full text search');
-- Equivalent to: postgresql & full & text & search

-- websearch_to_tsquery: supports quoted phrases and - exclusions (like Google)
SELECT * FROM posts
WHERE search_vector @@ websearch_to_tsquery('english', '"full text search" -elasticsearch');
-- Equivalent to: 'full text search' phrase AND NOT elasticsearch

websearch_to_tsquery is my default for user-facing search. Users are familiar with quoted phrases and -exclusions from search engines, and this function handles malformed input gracefully.

Search Result Highlighting

PostgreSQL can generate highlighted excerpts showing where the search terms appear in your text:

SELECT
  id,
  title,
  ts_headline(
    'english',
    content,
    query,
    'MaxWords=50, MinWords=20, MaxFragments=3, HighlightAll=false'
  ) AS excerpt
FROM posts,
  websearch_to_tsquery('english', 'postgresql indexing') query
WHERE search_vector @@ query
ORDER BY ts_rank(search_vector, query) DESC;

The options:

• MaxWords/MinWords: excerpt length
• MaxFragments: how many separate excerpt fragments to include
• HighlightAll: highlight all occurrences (slower) or just the most relevant

The default HTML output wraps matches in <b> tags. Configure custom tags:

'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20'

Fuzzy Matching With pg_trgm

Full-text search does not handle typos. For fuzzy matching — finding "PostgreSQl" when searching for "PostgreSQL" — use the pg_trgm extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_posts_title_trgm ON posts USING GIN (title gin_trgm_ops);

-- Find posts with titles similar to the query
SELECT title, similarity(title, 'PostgresQL indexing') AS sim
FROM posts
WHERE title % 'PostgresQL indexing'  -- % is the similarity threshold operator
ORDER BY sim DESC
LIMIT 10;

Combine full-text search for relevant results with trigram similarity for typo tolerance:

SELECT
  id,
  title,
  ts_rank(search_vector, to_tsquery('english', 'postgresql')) AS text_rank,
  similarity(title, 'postgresql') AS fuzzy_rank,
  (ts_rank(search_vector, to_tsquery('english', 'postgresql')) * 0.7 +
   similarity(title, 'postgresql') * 0.3) AS combined_rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgresql')
   OR title % 'postgresql'
ORDER BY combined_rank DESC;

Adding Search to Your ORM

With Prisma, use raw queries for full-text search operations that are not supported in the Prisma client API:

async function searchPosts(query: string, page = 1, limit = 20) {
  const offset = (page - 1) * limit

  const results = await prisma.$queryRaw<SearchResult[]>`
    SELECT
      id,
      title,
      description,
      ts_headline(
        'english',
        content,
        websearch_to_tsquery('english', ${query}),
        'MaxWords=50, MinWords=15, MaxFragments=2'
      ) AS excerpt,
      ts_rank(search_vector, websearch_to_tsquery('english', ${query})) AS rank
    FROM posts
    WHERE search_vector @@ websearch_to_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT ${limit}
    OFFSET ${offset}
  `

  const [{ count }] = await prisma.$queryRaw<[{ count: bigint }]>`
    SELECT COUNT(*) as count
    FROM posts
    WHERE search_vector @@ websearch_to_tsquery('english', ${query})
  `

  return {
    results,
    total: Number(count),
  }
}

With Drizzle:

import { sql } from 'drizzle-orm'

const results = await db.execute(sql`
  SELECT id, title,
    ts_rank(search_vector, websearch_to_tsquery('english', ${query})) AS rank
  FROM posts
  WHERE search_vector @@ websearch_to_tsquery('english', ${query})
  ORDER BY rank DESC
  LIMIT ${limit}
`)

When to Choose Elasticsearch Over PostgreSQL

PostgreSQL search is excellent for:

• Applications with under 10-20 million searchable documents
• Search over structured data with filtering by other columns
• Applications where simplicity and reduced operational overhead matter
• Budget-conscious deployments where another service has real cost

Consider Elasticsearch or Typesense when:

• You need sub-50ms search over 100+ million documents
• You need sophisticated relevance tuning (BM25, custom scoring)
• You need faceted search with real-time aggregations at scale
• You have a dedicated search use case where Elasticsearch's specialized features justify the operational cost

Most SaaS applications I have worked on never reach the scale where PostgreSQL's search limitations become real problems. Start with PostgreSQL, instrument your query performance, and migrate if the data shows you need to.

Adding search to your application and unsure whether to reach for PostgreSQL or a dedicated search service? I can help you make the right call. Book a call: calendly.com/jamesrossjr.

Keep Reading

• PostgreSQL Row-Level Security: Data Isolation at the Database Layer
• Database Backup Strategies for Production: The Ones That Actually Work
• Database Connection Pooling: Why It Matters and How to Configure It
• Database Indexing Strategies That Actually Make Queries Fast