Multilingual Full-Text Search in Postgres: No Elasticsearch Required

The first time someone on my team said “we need search,” three people immediately said “Elasticsearch.” It’s a reflex at this point, like reaching for React when someone mentions a UI. And honestly, Elasticsearch is fantastic at what it does. But it’s also a separate cluster to deploy, monitor, and keep in sync with your primary database. It needs its own backup strategy, its own scaling plan, its own on-call runbook. For a startup-sized team running a multi-tenant SaaS platform, that operational tax is real.

So before committing to another moving part in the infrastructure, I asked the uncomfortable question: can Postgres handle this? The constraints were a corpus spanning four locales, a tenant population whose searchable record counts varied by three orders of magnitude, and users who expected typo tolerance. The answer, it turned out, was yes. With some effort and a few sharp edges, Postgres got me there.

This post walks through how I built it, what worked, and where the approach hits its limits.

The tsvector/tsquery Foundation

If you haven’t used Postgres full-text search before, the core concept is straightforward. Postgres converts text into tsvector values: sorted lists of normalized lexemes (word stems) with positional information. You query them with tsquery expressions. The database handles stemming, stop word removal, and ranking natively.

-- Basic full-text search: convert text to tsvector, query with tsquery
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

The key parameter here is the regconfig, that 'english' argument. It tells Postgres which dictionary to use for stemming and stop words. Search for “running” and the English config knows to match “run,” “runs,” and “running.” Switch to 'german' and it applies German stemming rules instead.

For a monolingual app, this is basically plug and play. Create a tsvector column, add a GIN index, and you’re done. The multilingual case is where things get interesting.

Storing Language Alongside Content

The first design decision: every searchable row needs a locale column. You can’t apply the right stemming rules if you don’t know what language the text is in. The design that holds up in a multi-tenant setting: let each tenant declare which locales it supports, then tag every row with a locale at creation time.

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    locale TEXT NOT NULL DEFAULT 'en',  -- 'en', 'de', 'fr', 'bn'
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

With the locale column in place, you can build a generated tsvector column that automatically applies the correct language configuration.

Generated tsvector Columns with Per-Locale Configs

This is where the multilingual trick lives. Use a GENERATED ALWAYS AS column that switches regconfig based on the row’s locale:

ALTER TABLE documents ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector(
      CASE locale
        WHEN 'en' THEN 'english'::regconfig
        WHEN 'de' THEN 'german'::regconfig
        WHEN 'fr' THEN 'french'::regconfig
        ELSE 'simple'::regconfig
      END,
      coalesce(title, '') || ' ' || coalesce(body, '')
    )
  ) STORED;

-- GIN index makes full-text queries fast
CREATE INDEX idx_documents_search ON documents USING GIN (search_vector);

-- Compound index for tenant-scoped searches
CREATE INDEX idx_documents_tenant_search
  ON documents USING GIN (search_vector)
  WHERE tenant_id IS NOT NULL;

The GENERATED ALWAYS AS ... STORED clause means Postgres recomputes the vector automatically whenever title, body, or locale changes. No triggers, no application-level sync logic. The column stays consistent with the source data without any effort on your part.

Handling Languages Without a Stemmer

Notice the ELSE 'simple' fallback in that CASE statement. Postgres ships with stemmers for about 15 languages, and one of the four locales I had to support wasn’t among them. The simple configuration skips stemming entirely. It lowercases the text and splits on whitespace, which gives you exact substring matching without any linguistic analysis.

Is that as good as a real stemmer for that language? No. But it’s usable, and for a workload dominated by proper nouns and short phrases rather than long prose, it worked well enough that nobody complained. If I’d needed genuine morphological analysis for that locale, that’s where Elasticsearch or a dedicated NLP service would have been justified.

The unaccent Extension

French text is full of accented characters, and users don’t always type them consistently. The unaccent extension strips diacritical marks so that searching for “resume” matches “résumé.”

-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Use unaccent in your tsvector generation
ALTER TABLE documents DROP COLUMN search_vector;
ALTER TABLE documents ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector(
      CASE locale
        WHEN 'en' THEN 'english'::regconfig
        WHEN 'de' THEN 'german'::regconfig
        WHEN 'fr' THEN 'french'::regconfig
        ELSE 'simple'::regconfig
      END,
      unaccent(coalesce(title, '') || ' ' || coalesce(body, ''))
    )
  ) STORED;

One gotcha: unaccent also needs to be applied to the query side. If a user searches for “résumé” and you only unaccented the indexed text, the accented query won’t match the unaccented index. I wrapped query construction in a helper that always calls unaccent() on the search input.

Typo Tolerance with pg_trgm

Full-text search is great when users spell things correctly. They often don’t. A customer searching for “managment” expects to find “management.” Postgres FTS won’t help here because the stems are completely different. This is where pg_trgm comes in.

The pg_trgm extension breaks strings into trigrams (three-character sequences) and computes similarity scores between them. “management” and “managment” share most of their trigrams, so the similarity score is high.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Add a trigram index on the title column
CREATE INDEX idx_documents_title_trgm
  ON documents USING GIN (title gin_trgm_ops);

The Two-Tier Query Pattern

I ended up with a two-tier search strategy: try FTS first, fall back to trigram similarity if FTS returns too few results. This gives you the best of both worlds. Stemmed, ranked results when the query is well-formed, and fuzzy matching when it’s not.

-- Tier 1: Full-text search with ranking
WITH fts_results AS (
    SELECT
        id, title, body,
        ts_rank(search_vector, websearch_to_tsquery($2, $1)) AS rank
    FROM documents
    WHERE tenant_id = $3
      AND search_vector @@ websearch_to_tsquery($2, $1)
    ORDER BY rank DESC
    LIMIT 20
),

-- Tier 2: Trigram similarity fallback
trgm_results AS (
    SELECT
        id, title, body,
        similarity(title, $1) AS rank
    FROM documents
    WHERE tenant_id = $3
      AND similarity(title, $1) > 0.3
      AND id NOT IN (SELECT id FROM fts_results)
    ORDER BY rank DESC
    LIMIT 10
)

-- Combine both tiers, FTS results first
SELECT * FROM fts_results
UNION ALL
SELECT * FROM trgm_results;

The websearch_to_tsquery function is worth highlighting. Unlike to_tsquery, it accepts natural-language input (“project management tools”) without requiring the user to write boolean operators. It handles quoted phrases, negation with -, and implicit AND between terms. Much friendlier for building a search box.

The similarity threshold of 0.3 was tuned empirically. Too low and you get garbage matches. Too high and you miss legitimate typos. I started at 0.3, watched the results for a couple weeks, and nudged it to 0.35 for one tenant with very short document titles where false positives were more annoying.

Ranking and Cursor Pagination

Ranking full-text results is straightforward with ts_rank or ts_rank_cd. The tricky part is combining ranking with stable cursor pagination. You can’t use OFFSET for paginated search results in production. It re-executes the entire query and skips rows, which gets slower with every page. Cursors are the answer, but ranked results don’t have a natural sort key.

The pattern I settled on: sort by rank descending, then by ID ascending as a tiebreaker. The cursor encodes both values.

-- First page
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM documents, websearch_to_tsquery('english', $1) AS query
WHERE tenant_id = $2
  AND search_vector @@ query
ORDER BY rank DESC, id ASC
LIMIT 20;

-- Subsequent pages: use the last row's rank and id as cursor
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM documents, websearch_to_tsquery('english', $1) AS query
WHERE tenant_id = $2
  AND search_vector @@ query
  AND (ts_rank(search_vector, query), id) < ($3, $4)  -- cursor values
ORDER BY rank DESC, id ASC
LIMIT 20;

The (rank, id) < (cursor_rank, cursor_id) comparison uses Postgres’s row comparison, which handles the “same rank, different ID” case correctly. Documents with the same relevance score are ordered deterministically by ID, so pagination never skips or duplicates rows.

One subtlety: ts_rank is computed per query, so the rank value can change if the underlying data changes between page fetches. For most search UIs this is fine. Users don’t paginate through search results the way they paginate through a stable list. If you need absolute stability, you’d need to materialize the ranked result set, which is where things start getting complicated enough that Elasticsearch might actually earn its keep.

Wiring It Up in Python

The SQL is only half the story. You also need a clean application layer that constructs the right query based on the user’s locale and input. Here’s the search function I landed on with async SQLAlchemy:

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

LOCALE_TO_REGCONFIG = {
    "en": "english",
    "de": "german",
    "fr": "french",
}

async def search_documents(
    db: AsyncSession,
    tenant_id: str,
    query: str,
    locale: str,
    cursor_rank: float | None = None,
    cursor_id: str | None = None,
    limit: int = 20,
) -> list[dict]:
    """Two-tier search: FTS first, trigram fallback for typos."""
    regconfig = LOCALE_TO_REGCONFIG.get(locale, "simple")

    # Tier 1: Full-text search
    fts_sql = """
        SELECT id, title, body,
               ts_rank(search_vector, websearch_to_tsquery(:regconfig, unaccent(:query))) AS rank
        FROM documents
        WHERE tenant_id = :tenant_id
          AND search_vector @@ websearch_to_tsquery(:regconfig, unaccent(:query))
    """
    params = {"regconfig": regconfig, "query": query, "tenant_id": tenant_id}

    # Apply cursor if paginating
    if cursor_rank is not None and cursor_id is not None:
        fts_sql += """
          AND (ts_rank(search_vector, websearch_to_tsquery(:regconfig, unaccent(:query))), id)
              < (:cursor_rank, :cursor_id)
        """
        params["cursor_rank"] = cursor_rank
        params["cursor_id"] = cursor_id

    fts_sql += " ORDER BY rank DESC, id ASC LIMIT :limit"
    params["limit"] = limit

    result = await db.execute(text(fts_sql), params)
    fts_rows = result.mappings().all()

    if len(fts_rows) >= limit:
        return list(fts_rows)

    # Tier 2: Trigram fallback if FTS returned fewer results than requested
    remaining = limit - len(fts_rows)
    fts_ids = [r["id"] for r in fts_rows]

    trgm_sql = """
        SELECT id, title, body, similarity(title, unaccent(:query)) AS rank
        FROM documents
        WHERE tenant_id = :tenant_id
          AND similarity(title, unaccent(:query)) > 0.3
          AND id != ALL(:exclude_ids)
        ORDER BY rank DESC
        LIMIT :remaining
    """
    trgm_result = await db.execute(text(trgm_sql), {
        "query": query,
        "tenant_id": tenant_id,
        "exclude_ids": fts_ids,
        "remaining": remaining,
    })
    trgm_rows = trgm_result.mappings().all()

    return list(fts_rows) + list(trgm_rows)

The unaccent() call on the query side mirrors the unaccent() in the generated column. Without both sides matching, accented queries against unaccented indexes produce zero results. Ask me how I know.

One design decision worth calling out: pass the regconfig as a parameter rather than hardcoding it into the SQL. That lets one query function serve every locale without generating locale-specific SQL strings. The config is just data, and Postgres handles the dispatch.

Performance in Practice

At the top of the range I benchmarked, a single tenant holding several hundred thousand documents across three locales, the two-tier query typically returns in 15-40ms. The GIN index on the generated tsvector column does the heavy lifting. A few things that helped:

• Tenant-scoped queries. Every query includes WHERE tenant_id = $X, which prunes the search space dramatically. I added a composite index on (tenant_id) with INCLUDE (search_vector) for the most common query patterns.
• Connection pooling. PgBouncer in front of Postgres, transaction-mode pooling. Search queries are short-lived and benefit from connection reuse.
• Monitoring. Track P50/P95 search latency per tenant in Prometheus. Once a tenant crosses into the hundreds of thousands of documents, results get slightly slower, but nothing that required intervention.
• GIN index maintenance. GIN indexes slow down bulk inserts because Postgres updates the index on every row change. For tenants that do large batch imports, I temporarily set fastupdate=on for the GIN index and run REINDEX after the import finishes. This trades slightly stale search results during import for much faster bulk write throughput.

For smaller tenants (under 50,000 documents), search latency is consistently under 10ms. The overhead of the two-tier query is negligible because the trigram fallback rarely fires when the corpus is small enough that FTS covers most reasonable queries.

When You Actually Need Elasticsearch

I’m not going to pretend Postgres FTS covers every case. Here’s when I’d reach for a dedicated search engine without hesitation:

• Faceted search with aggregations. If you need to show “15 results in category A, 8 in category B” alongside your search results, Elasticsearch’s aggregation framework is purpose-built for this. Doing it in Postgres involves multiple queries or complex CTEs.
• Autocomplete at scale. Prefix completion, “search as you type,” and suggestion engines are hard to do well in Postgres. Elasticsearch’s completion suggester or Typesense’s out-of-the-box autocomplete are significantly better.
• Multi-field boosting with complex relevance tuning. If you need “title matches are 3x more important than body matches, but only for this category of documents,” Elasticsearch’s query DSL gives you much finer control than ts_rank weights.
• Corpus over 10 million documents. Postgres FTS works surprisingly well up to a few million rows per tenant. Beyond that, you start noticing GIN index maintenance costs and query latency creep. At true search-engine scale, a dedicated tool earns its operational overhead.
• Languages Postgres has no stemmer for. If your primary language lacks a built-in regconfig and the simple tokenizer isn’t good enough, you’ll need something with pluggable language analysis.

The decision isn’t binary. I’ve run the Postgres FTS approach for over a year now without adding Elasticsearch, and users are happy. But I’ve kept Typesense on the architecture radar as a lighter-weight alternative for the day this approach outgrows what Postgres can do.

Takeaways

1. Start with what you have. If you’re already on Postgres, try its FTS before adding another service to your stack. The operational simplicity of one database is worth a lot.
2. Generated columns are your friend. A GENERATED ALWAYS AS tsvector column with a GIN index gives you full-text search with zero application-level sync logic.
3. Handle multiple languages with per-row regconfig. The CASE locale WHEN ... pattern maps each row to the right stemmer automatically. Use 'simple' as a fallback for unsupported languages.
4. Layer pg_trgm on top for typo tolerance. The two-tier pattern (FTS first, trigram fallback) covers both well-formed queries and misspellings without much extra complexity.
5. Use cursor pagination, not OFFSET. The (rank, id) tiebreaker cursor gives you stable, performant pagination over ranked results.
6. Know your limits. Postgres FTS isn’t a search engine. For faceted search, autocomplete, or massive corpora, a dedicated tool is the right call.

---

This post is based on building search for a multi-tenant SaaS platform serving four locales. Query patterns and performance numbers are representative of real production workloads.