← Back to the section

When someone says "we need search," the first thought is Elasticsearch. But that means a second cluster, a separate data synchronization pipeline, and new points of failure. PostgreSQL has built-in full-text search, and it covers most tasks. Let's figure out what to reach for and when.

How PostgreSQL searches text

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

PostgreSQL solves this differently. A special tsvector type stores text in a parsed form — a list of root word forms with their weights and positions. For example, the string "red corner sofa" is turned into a dictionary set with stemming: a search for "sofas" will find "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, search is O(log N)
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 whole table, the database goes straight to the needed rows. Millions of rows — tens of milliseconds.

On top of that, the pg_trgm extension can search with typos and by substrings via trigrams — without a full table scan.

What Elasticsearch is

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

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

Which criteria to choose by

Ranking quality

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

Elasticsearch uses BM25 and lets you mix in business signals: demote old products, promote popular ones, apply different weights depending on the query context. If result 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)". In PostgreSQL they are computed with separate queries. In Elasticsearch, aggregations return facets together with the results in a single query — that's its native genre.

Typos and autocomplete

pg_trgm covers typos and prefix search. A synonym dictionary can be plugged into PostgreSQL, but managing it is inconvenient.

Elasticsearch offers ready-made options: fuzzy search, suggesters for autocomplete, a synonym graph, reindexing with a different analyzer without downtime.

Data volume and load

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

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

In PostgreSQL the search index is updated in the same transaction: you create a product — it's immediately visible in search. This is a strong argument in favor of 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 — a source of problems.

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 — everything works the usual way.

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

Operational complexity

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

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

Checklist: when Elasticsearch is justified

Add a point for each "yes":

  1. Result 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. More than ten million documents or more than 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 will have a dedicated owner.

0–2 points — PostgreSQL FTS + pg_trgm. Don't forget the GENERATED column and the GIN index.

3–4 points — start with PostgreSQL, but keep the search logic in one place so it's convenient 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 an Elasticsearch cluster just to search by name in an admin panel over a hundred thousand rows. pg_trgm with a GIN index solves this without extra components.

ILIKE '%query%' without an index. The opposite mistake: a sequential scan on every search and the conclusion "PostgreSQL can't search." It can — you need 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 only through a pipeline (CDC or events) with reindexing as a routine operation.

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 the data from. Elasticsearch is a derivative that can be recreated from PostgreSQL.

Ignoring the indexing lag. The lag is a property of the architecture; it must be declared in the API contract and explained in the UI, not hidden.

When both are used together

A mature large catalog: PostgreSQL is the source of truth and exact filters (price, availability, category), Elasticsearch is full-text search, ranking, and facets. Search from Elasticsearch returns identifiers, and the cards are loaded from PostgreSQL. Each tool does what it does best.

In short

  • PostgreSQL FTS: tsvector + GIN index + pg_trgm — built in, transactional, no new components.
  • Search in PostgreSQL updates in the same transaction — the data is always consistent.
  • A GIN index handles millions of rows and tens of queries per second.
  • Elasticsearch is needed when relevance is a product metric, you need facets and autocomplete, there are tens of millions of documents, or the load is hundreds of queries per second.
  • Elasticsearch is always a derivative of PostgreSQL, recreatable through a pipeline.
  • Writing to Elasticsearch directly from the command handler is an antipattern: synchronization only through CDC or events.
  • ILIKE '%...%' without an index is a common cause of slow search; the replacement is a GIN index over tsvector or trigrams.
  • Elasticsearch Fundamentals — how the inverted index is built and what you pay for with a cluster.
  • Query DSL and relevance — BM25 and facets in practice.
  • PostgreSQL or ClickHouse — a parallel fork for analytical workloads.
  • Distributed patterns — synchronizing two stores without double writes.