Experiment · 2026

Search Without Elasticsearch

In progress
  • Postgres
  • Search

How far Postgres full-text search actually goes before you need a dedicated engine. Further than I expected.

01 — WhyWhat made this worth building

Every project I join has a ticket to "add Elasticsearch for search". Usually the dataset is under a million rows and Postgres would answer in under 20ms with a GIN index. I wanted a defensible number for where that stops being true.

02 — NotesHow it works

A `tsvector` column with a GIN index gets you stemming, ranking, prefix matching and boolean operators. Combine it with `pg_trgm` for fuzzy matching and you have covered typo tolerance too — which is the feature people usually think they need a search engine for.

sql
alter table article
  add column search tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')),  'B')
  ) stored;

create index on article using gin (search);

select title, ts_rank(search, query) as rank
from   article, websearch_to_tsquery('english', $1) query
where  search @@ query
order  by rank desc
limit  20;

Still open: where this actually falls over. So far the honest answer is that relevance tuning gets awkward before performance does. Faceting across many dimensions and true typo-tolerant ranking are where a dedicated engine earns its operational cost.

All experiments