PostgreSQL Full-Text Search in 2026: tsvector, Ranking, Multi-Language, and 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