PostgreSQL Full-Text Search 2026: tsvector, Ranking, Multi-Language

PostgreSQL Full-Text Search in 2026: tsvector, Ranking, Multi-Language, and Search-as-You-Type

Quick answer. PostgreSQL full-text search handles most SaaS search needs without Elasticsearch for under ~10 million documents. Store a tsvector column, index it with GIN, query with to_tsquery, rank with ts_rank, and add the pg_trgm extension for typo-tolerant search-as-you-type.

PostgreSQL's built-in full-text search covers most SaaS search use cases without adding Elasticsearch to your stack. For document counts under 10 million and query rates under a few hundred per second, PostgreSQL FTS is faster to implement, cheaper to operate, and simpler to maintain.

This post covers production-ready PostgreSQL FTS: the tsvector data model, GIN indexing, relevance ranking, multi-language stemming, and search-as-you-type via pg_trgm. For most teams, this replaces the need for a dedicated search service.

---

How PostgreSQL FTS Works

Input text: "The PostgreSQL database running on AWS is reliable"
           ↓ to_tsvector('english', ...)
tsvector:   'aws':6 'databas':3 'postgreql':2 'reliabl':8 'run':5
            (stems + positions; stop words removed: "the", "on", "is")

Query: "databases running"
       ↓ to_tsquery('english', ...)
tsquery: 'databas' & 'run'  (both stems must match)

Match: tsvector @@ tsquery → TRUE (both stems present)

---

Schema: Indexed tsvector Column

-- Store tsvector in a generated column — auto-updates when source changes
CREATE TABLE articles (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title        TEXT NOT NULL,
    body         TEXT NOT NULL,
    tags         TEXT[],
    author_name  TEXT NOT NULL,
    published_at TIMESTAMPTZ,
    status       TEXT NOT NULL DEFAULT 'draft',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Generated column: weighted combination of fields
    -- A = highest weight, B = high, C = medium, D = low
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')),  'A') ||
        setweight(to_tsvector('english', coalesce(author_name, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(
            array_to_string(tags, ' '), ''
        )), 'B') ||
        setweight(to_tsvector('english', coalesce(body, '')),   'D')
    ) STORED
);

-- GIN index on the generated tsvector
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

-- Also index for filtering (status, published_at)
CREATE INDEX articles_status_published_idx ON articles (status, published_at DESC);

---

Basic Search Queries

-- Simple search with ranking
SELECT
    id,
    title,
    author_name,
    published_at,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', body, query,
        'MaxWords=30, MinWords=15, ShortWord=3, HighlightAll=false, StartSel=<mark>, StopSel=</mark>'
    ) AS excerpt
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE status = 'published'
  AND search_vector @@ query
ORDER BY rank DESC, published_at DESC
LIMIT 20;

-- Phrase search (words must appear adjacent)
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'machine learning');
-- "machine learning" must appear together, not just both words present

-- Web-style query (handles user input with OR, -, "")
-- plainto_tsquery: "postgresql database" → 'postgresql' & 'database'
-- websearch_to_tsquery: "postgresql OR mysql" → 'postgresql' | 'mysql'
--                       "-oracle"             → !'oracle'
SELECT title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', $1);
-- $1 = user's raw search input — safe, handles any input

TypeScript Search Service

// src/services/search.ts
import { db } from '@/lib/db';

interface SearchResult {
  id: string;
  title: string;
  authorName: string;
  publishedAt: string;
  excerpt: string;
  rank: number;
  tags: string[];
}

interface SearchOptions {
  query: string;
  limit?: number;
  offset?: number;
  language?: string;
  filters?: {
    tags?: string[];
    authorId?: string;
    dateFrom?: Date;
    dateTo?: Date;
  };
}

export async function searchArticles(opts: SearchOptions): Promise<{
  results: SearchResult[];
  total: number;
}> {
  const {
    query,
    limit = 20,
    offset = 0,
    language = 'english',
    filters = {},
  } = opts;

  // Build filter conditions
  const conditions: string[] = ["status = 'published'"];
  const params: unknown[] = [query];
  let paramIdx = 2;

  if (filters.tags?.length) {
    conditions.push(`tags && $${paramIdx}::text[]`);
    params.push(filters.tags);
    paramIdx++;
  }

  if (filters.dateFrom) {
    conditions.push(`published_at >= $${paramIdx}`);
    params.push(filters.dateFrom);
    paramIdx++;
  }

  const whereClause = conditions.join(' AND ');

  const { rows } = await db.query<SearchResult & { total: string }>(
    `WITH results AS (
       SELECT
           id, title, author_name AS "authorName", published_at AS "publishedAt",
           tags,
           ts_rank_cd(search_vector, query, 4) AS rank,   -- 4 = normalize by doc length
           ts_headline(
               '${language}', body, query,
               'MaxWords=25, MinWords=10, StartSel=<b>, StopSel=</b>, HighlightAll=false'
           ) AS excerpt,
           count(*) OVER() AS total
       FROM articles, websearch_to_tsquery('${language}', $1) query
       WHERE ${whereClause}
         AND search_vector @@ query
       ORDER BY rank DESC, published_at DESC
     )
     SELECT * FROM results
     LIMIT $${paramIdx} OFFSET $${paramIdx + 1}`,
    [...params, limit, offset],
  );

  return {
    results: rows,
    total: rows[0] ? Number(rows[0].total) : 0,
  };
}

---

Multi-Language Search

-- Store the document language per row
ALTER TABLE articles ADD COLUMN language REGCONFIG NOT NULL DEFAULT 'english';

-- Dynamically use the document's language for indexing
-- (Cannot use GENERATED ALWAYS for dynamic language — use trigger instead)
CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector(NEW.language, coalesce(NEW.title, '')),  'A') ||
    setweight(to_tsvector(NEW.language, coalesce(NEW.body, '')),   'D');
  RETURN NEW;
END;
$$;

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

-- Supported languages (PostgreSQL built-in dictionaries):
-- english, german, french, spanish, italian, portuguese, dutch,
-- russian, turkish, danish, finnish, hungarian, norwegian, romanian, swedish

// Search in the document's own language
export async function searchMultiLanguage(
  userQuery: string,
  userLanguage: 'english' | 'german' | 'french' | 'spanish',
): Promise<SearchResult[]> {
  // Query must match document language — cross-language search requires translation
  const { rows } = await db.query(
    `SELECT id, title, ts_rank(search_vector, query) AS rank
     FROM articles,
          websearch_to_tsquery($1::regconfig, $2) query
     WHERE language = $1
       AND search_vector @@ query
     ORDER BY rank DESC
     LIMIT 20`,
    [userLanguage, userQuery],
  );
  return rows;
}

---

Search-as-You-Type (Partial Word Matching)

PostgreSQL FTS requires complete words — it won't match "postg" for "postgresql". For autocomplete, combine with pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN index for trigram similarity (partial word matching)
CREATE INDEX articles_title_trgm_idx ON articles USING GIN (title gin_trgm_ops);
CREATE INDEX articles_author_trgm_idx ON articles USING GIN (author_name gin_trgm_ops);

-- Search-as-you-type: combine trigram prefix match + FTS
-- Use for queries < 3 words; full FTS for longer queries
SELECT
    id,
    title,
    author_name,
    similarity(title, $1) AS title_sim
FROM articles
WHERE status = 'published'
  AND (
      title % $1                    -- Trigram similarity (handles partials)
      OR author_name % $1
  )
ORDER BY title_sim DESC
LIMIT 10;

-- For "type-ahead" prefix completion specifically:
SELECT DISTINCT unnest(string_to_array(title, ' ')) AS word
FROM articles
WHERE title ILIKE $1 || '%'   -- Starts with prefix
  AND status = 'published'
ORDER BY word
LIMIT 10;

---

Hybrid Search: FTS + Semantic (pgvector)

For best-in-class search, combine keyword FTS with vector semantic search:

-- Reciprocal Rank Fusion (RRF) combining FTS + vector similarity
WITH fts_results AS (
    SELECT id, row_number() OVER (ORDER BY ts_rank_cd(search_vector, query) DESC) AS fts_rank
    FROM articles, websearch_to_tsquery('english', $1) query
    WHERE status = 'published'
      AND search_vector @@ query
    LIMIT 60
),
vector_results AS (
    SELECT id, row_number() OVER (ORDER BY embedding <=> $2::vector) AS vec_rank
    FROM articles
    WHERE status = 'published'
    ORDER BY embedding <=> $2::vector
    LIMIT 60
),
fused AS (
    SELECT
        COALESCE(f.id, v.id) AS id,
        COALESCE(1.0/(60 + f.fts_rank), 0) + COALESCE(1.0/(60 + v.vec_rank), 0) AS score
    FROM fts_results f
    FULL OUTER JOIN vector_results v USING (id)
)
SELECT a.id, a.title, a.excerpt, f.score
FROM fused f
JOIN articles a ON a.id = f.id
ORDER BY f.score DESC
LIMIT 20;
-- $1 = text query, $2 = query embedding vector

---

FTS vs Elasticsearch: When to Switch

Start with PostgreSQL FTS. Migrate to Elasticsearch only when you hit these limits.

---

Our Approach at Viprasol

We implement full-text search for SaaS applications — from PostgreSQL FTS schema design through hybrid vector + keyword search and search-as-you-type autocomplete.

What we deliver:

• tsvector generated column setup with weighted fields
• GIN index configuration and query optimization
• Multilingual search with language detection
• Search-as-you-type via pg_trgm + prefix matching
• Hybrid FTS + pgvector semantic search for highest relevance

→ Discuss your search implementation → Backend development services

---

You Might Also Like

• PostgreSQL Extensions: pgvector, pg_trgm, TimescaleDB
• PostgreSQL Performance Tuning
• LLM RAG in Production
• Database Design Patterns
• API Documentation and OpenAPI

Reading the PostgreSQL Full-Text Search Documentation in 2026

When you consult the postgresql full text search documentation 2026, focus on the sections that change how production systems behave. The text search functions chapter explains how to build a tsvector column and keep it current with a generated column or trigger, while the indexing notes cover when a GIN index beats GIN with fast-update or a RUM extension for ranked queries. Pair that with the controls chapter for parsers, dictionaries, and configurations so multi-language search returns sensible stems. We also recommend studying the ranking section, since ts_rank and ts_rank_cd weight matches very differently. Our engineers treat the official docs as the baseline, then benchmark against real query patterns. If you need this implemented end to end, Viprasol Tech owns the schema, indexing, and tuning outright.