Search: PostgreSQL FTS or Elasticsearch

← Back to the section

"We need search" — and the first thought is Elasticsearch. But that means a second cluster, a separate synchronization pipeline, and new points of failure. PostgreSQL has full-text search built in, and it covers most tasks. Let's see when to take which.

How PostgreSQL searches text

Text search used to be done with LIKE '%query%'. The problem: the database scans every row in full — on large tables that is slow, and indexes don't help.

PostgreSQL solves this differently. The tsvector type stores text in a parsed form — a list of root word forms with their weights and positions. The string "red corner sofa" becomes a dictionary set with stemming: a search for "sofas" finds "sofa".

-- Add a search column that updates automatically
ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'B')
    ) STORED;

-- Create a GIN index — it works like a dictionary: word → list of documents
CREATE INDEX product_search_idx ON product USING GIN (search_vector);

-- Search and sort by relevance
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM product, websearch_to_tsquery('english', 'red corner sofa') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

The GIN index stores an inverted dictionary: word → list of rows where it appears. Instead of scanning the table the database goes straight to the rows it needs: tens of milliseconds over millions of rows.

One caveat: this holds while the word is rare. If a word occurs in a third of the products, the database pulls hundreds of thousands of rows, computes relevance for each one (there is no index for it), and sorts them. That is where PostgreSQL falls behind search engines, which have their own tricks for this case.

What Elasticsearch is

Elasticsearch is a separate service specialized in search. Under the hood it's the same inverted index, but the capabilities are broader: BM25 ranking (how often a word occurs in a document and how rare it is across the collection), facets (counting results by category inside the search query), advanced synonym handling, autocomplete, multilingual analyzers.

You pay for this: a separate cluster with its own operations, and data that is always secondary to PostgreSQL — you need a synchronization pipeline.

Which criteria to choose by

Ranking quality

PostgreSQL ranks by ts_rank — a number based on word frequency and field weights. For a catalog with filters that is usually enough.

Elasticsearch uses BM25 and lets you mix in business signals: demote old products, promote popular ones, weigh them differently per query context. If relevance is a product metric that you improve regularly, PostgreSQL won't be enough.

Facets

Facets are the counters next to filters: "Laptops (234)", "Smartphones (87)". PostgreSQL computes them with separate queries; Elasticsearch aggregations return them with the results in one query.

Typos and autocomplete

The pg_trgm extension covers typos, prefix and substring search — through trigrams, without a full table scan. A synonym dictionary plugs into PostgreSQL too, but managing it is inconvenient.

Elasticsearch has all of that ready-made: fuzzy search, suggesters for autocomplete, a synonym graph, reindexing with another analyzer without downtime — through an alias switch.

Data volume and load

A GIN index handles millions of documents and tens of search queries per second — enough for most products.

Elasticsearch scales horizontally with shards: tens of millions of documents, hundreds of queries per second, search latency under 50 ms.

Data freshness in search

In PostgreSQL the search index is updated in the same transaction: you create a product — it is immediately visible in search. A strong argument for PostgreSQL where consistency matters.

In Elasticsearch, between the write and its visibility in search there is a pipeline and a refresh interval, usually a second. For a catalog this is fine; for a "created it and immediately search for it" scenario it is a source of problems.

A small program shows the difference: the index next to the data grows with the write, the copy in the engine only with delivery.

live example

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Deque;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class SearchFreshness {
    static final Map<String, List<String>> nextToData = new TreeMap<>();
    static final Map<String, List<String>> inEngine = new TreeMap<>();
    static final Deque<String[]> pipeline = new ArrayDeque<>();

    static void save(String id, String title) {
        for (String word : title.split(" ")) {
            nextToData.computeIfAbsent(word, key -> new ArrayList<>()).add(id);
        }
        pipeline.add(new String[]{id, title});
    }

    static void deliver() {
        while (!pipeline.isEmpty()) {
            String[] document = pipeline.poll();
            for (String word : document[1].split(" ")) {
                inEngine.computeIfAbsent(word, key -> new ArrayList<>()).add(document[0]);
            }
        }
    }

    static void report(String moment) {
        System.out.println(moment + ": next to data " + nextToData.get("sofa")
                + ", in the engine " + inEngine.get("sofa")
                + ", queued " + pipeline.size());
    }

    public static void main(String[] args) {
        save("prod-01", "corner sofa grey");
        save("prod-02", "straight sofa blue");
        deliver();
        report("after delivery   ");
        save("prod-03", "folding sofa beige");
        report("right after write");
        deliver();
        report("pipeline caught up");
    }
}

The middle line of the output is the lag: the product is in the database already, not in the engine yet.

Combining with ordinary filters

PostgreSQL: search is just another filter in SQL.

WHERE search_vector @@ query
  AND price < 1000
  AND category_id = 5
  AND in_stock = true

JOINs, subqueries, window functions — all work as usual.

In Elasticsearch the whole query is written in the Query DSL — a separate JSON format. In return you get highlight (marking the found words), nested documents, and "more like this".

Operational complexity

PostgreSQL: the search index is backed up with the database, zero new components.

Elasticsearch: a cluster with shards, index lifecycle management, snapshots, monitoring — plus a separate pipeline from PostgreSQL (CDC or events) with its own monitoring.

Checklist: when Elasticsearch is justified

One point per "yes":

1. Relevance is a product metric that will be improved regularly.
2. You need facets with counts on every search.
3. Synonyms, autocomplete, typo correction — requirements now, not "later".
4. Over ten million documents, or over fifty search queries per second.
5. An indexing lag of a few seconds is acceptable.
6. A CDC or event pipeline already exists or is planned.
7. Search has a dedicated owner.

0–2 points — PostgreSQL FTS + pg_trgm: a GENERATED column and a GIN index.

3–4 points — start with PostgreSQL, keeping the search logic in one place so it's easy to extract later.

5+ points — Elasticsearch, and right away with a proper synchronization pipeline: CDC or domain events, reindexing as a routine operation.

Common mistakes

Elasticsearch for the sake of LIKE. Standing up a cluster just to search by name in an admin panel over a hundred thousand rows. pg_trgm with a GIN index solves it without extra components.

ILIKE '%query%' without an index. The opposite mistake: a sequential scan on every search and the verdict "PostgreSQL can't search." It can — with a GIN index over tsvector or trigrams.

Writing to Elasticsearch straight from the command handler. At the first failure, search drifts apart from the database. Synchronization goes through a pipeline only — CDC or events.

Elasticsearch as the source of truth. Documents live only in Elasticsearch, PostgreSQL is "for transactions." When the index structure changes or the cluster is lost, there is nothing to restore from: Elasticsearch is a derivative, recreated from PostgreSQL.

Ignoring the indexing lag. The lag is a property of the architecture: declare it in the API contract and explain it in the UI instead of hiding it.

When both are used together

A mature large catalog: PostgreSQL holds the source of truth and the exact filters (price, availability, category), Elasticsearch does full-text search, ranking, and facets. Search returns identifiers, the cards come from PostgreSQL.

In short

• PostgreSQL FTS: tsvector + GIN index + pg_trgm — built in, transactional, no new components.
• The PostgreSQL index is updated in the same transaction, so a new row is searchable at once; a separate engine lags about a second behind the write.
• A GIN index handles millions of rows and tens of queries per second; on a frequent word relevance is still computed row by row.
• Elasticsearch is needed when relevance is a product metric, facets and autocomplete are required, documents number tens of millions, or the load is hundreds of queries per second.
• Elasticsearch is always a derivative of PostgreSQL: synchronize through CDC or events only; writing straight from the command handler is an antipattern.
• ILIKE '%...%' without an index is a common cause of slow search: use a GIN index over tsvector or trigrams.

What to read next

• Elasticsearch: how a search engine works — the inverted index, shards, and what a cluster costs.
• How Queries Work in Elasticsearch and Why Documents Are Ranked the Way They Are — BM25 and facets in practice.
• PostgreSQL or ClickHouse: when to add a second database — the parallel fork for analytical workloads.
• Distributed patterns: data consistency across services — how to synchronize two stores without double writes.