Site search: PostgreSQL, Meilisearch or Elasticsearch

For a site with a catalogue of up to hundreds of thousands of records, PostgreSQL's built-in full-text search is usually enough. A dedicated engine — Meilisearch or Elasticsearch — is needed when instant search-as-you-type, typo tolerance, complex filters with counts and relevance tuning without a developer matter.

Here is what is wrong with LIKE, what the database can do, when to move to a dedicated engine and how to make search pleasant to use.

Why is LIKE not search?

A LIKE '%word%' query looks for an exact substring: it does not understand word forms, forgive typos or rank by relevance, and on large tables it cannot use ordinary indexes. A user searches for "red sneakers", the database holds "sneaker in red" — and they get an empty page.

Proper search should:

• match different word forms: "sneaker", "sneakers";
• forgive typos and wrong keyboard layouts;
• rank results by relevance, not by date;
• understand synonyms and abbreviations;
• combine a text query with filters by price, category and availability.

What can PostgreSQL full-text search do?

PostgreSQL can search by words with stemming, rank results and use an index to do so. A trigram extension adds fuzzy matching for typos. The advantage is that no separate system is needed: data and search live in one place and there is nothing to sync.

ALTER TABLE products ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) STORED;

CREATE INDEX products_search_idx ON products USING GIN (search);

SELECT id, title, ts_rank(search, query) AS rank
FROM products, websearch_to_tsquery('english', 'red sneakers') query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

For more on indexes see PostgreSQL indexes, and on choosing a database, PostgreSQL vs MySQL.

Photo: Unsplash

When do you need a dedicated search engine?

You need a dedicated engine when search is a core product feature: an online shop with a large catalogue, a marketplace, a knowledge base. It returns search-as-you-type results in tens of milliseconds, handles typos well and counts items in every filter.

Criterion	PostgreSQL	Meilisearch	Elasticsearch / OpenSearch
Separate infrastructure	Not needed	One service	A cluster
Typos	Via an extension	Out of the box	Configurable
Search as you type	Possible but harder	Out of the box	Configurable
Facets and filter counts	Manual	Out of the box	Out of the box
Data volume	Up to millions of rows	Millions of documents	Very large volumes, logs
Maintenance effort	Low	Low	High

Meilisearch and similar engines are great for catalogues and websites: quick to integrate with high out-of-the-box quality. Elasticsearch and OpenSearch are chosen for very large data, complex analytics or log search.

How do you keep data in sync with the search index?

Sync data through events: when a record is created, changed or deleted, a background job updates the document in the index. The database stays the source of truth, and the index can be rebuilt from scratch at any time.

Sync rules:

1. Update the index through a queue, not inside the user's request.
2. Keep a full reindex command — you will need it when settings change.
3. Index only the fields people search and filter by, not the whole record.
4. Monitor index lag behind the database and job errors.

How do you make search pleasant to use?

Search is pleasant when it responds fast, understands human phrasing and never leaves an empty page. Even perfect technology will not help if the data lacks the right words and results cannot be filtered.

What helps:

• suggestions as you type and keyboard layout correction;
• synonyms: "laptop" and "notebook", "phone" and "smartphone";
• filters showing the number of results next to each value;
• similar items or popular categories instead of an empty page;
• analytics on zero-result queries to improve data and synonyms.

We covered collecting this kind of data in product metrics after launch.

In short

• LIKE ignores word forms, typos and relevance — it is not search.
• PostgreSQL full-text search is enough for most sites.
• A dedicated engine is needed when search is core: instant suggestions, typos, facets.
• Update the index through a queue; the database remains the source of truth.
• Synonyms, filters and zero-result analysis improve search quality.