Stop Using Elasticsearch. Use Postgres Instead
This issue is brought to you by:
Postgres for sensor and machine data
Factories, wells, grids, and fleets run on live machine data. Tiger Data keeps it performant and scalable in one Postgres.
|
|
Do you still need Elasticsearch?
I wanted to test that properly, so I put Postgres in Docker, loaded it with thousands of support tickets, and checked how far the same BM25 relevance search used by dedicated search systems could take me.
And it went well enough to raise a slightly uncomfortable question: how many teams are already running Postgres, then rebuilding, operating, or paying for another service (or a suite of services as these normally come bundled with each other) just to search text in the data they already own?
Not every team needs a second database for search.
So let's see what the simpler version looks like.
The second database tax
Elasticsearch is everywhere for a reason.
If somebody says “search” in an engineering meeting, elastic immediately comes up in some form.
It is powerful, no doubt. Mature, open source(!), and can handle much more than a basic text query.
You get relevance ranking, filters, ranges, nested logic, aggregations, vector search, embeddings, geospatial search, and its own query language.
The match query is a very capable starting point.
But that power comes with great responsibility another system to run.
Your application data lives in Postgres, then a copy of that data gets indexed somewhere else.
Now there are mappings, indexes (or indices...??), refreshes, storage, backups, monitoring, and one more thing that can be unhealthy at 2AM.
For serious search workloads, that tradeoff can be completely reasonable.
But if all you need is “find the most relevant support tickets containing these strings,” it might be a lot of machinery for a fairly small job.
To see the normal setup, I started with Elasticsearch and Kibana.
Then I could open Kibana, search the documents, inspect the fields, and see the relevant results.
At six documents, it is not exactly a stress test. But when that list becomes hundreds of thousands of lines, relevance ranked search becomes critical.
The classic architecture works.
Documents live in one system, and search lives in another.
The question is whether both parts need to stay separate for your particular workload..?
The search engine already in your stack
This is where pg_textsearch comes in.
It's an open source postgres extension that adds BM25 ranked text search to the database.
BM25 (invented in the 70s!!) is the relevance ranking algorithm behind pg_textsearch: instead of simply asking whether a row matches, we can ask which matching rows are the best matches.
The nice part is the shape of the solution: one extension, one BM25 index, and one SQL operator.
The extension supports PostgreSQL 17 and 18, multiple PostgreSQL text-search configurations such as English, French, and German, expression indexes, partitioned tables, and parallel index builds.
You can download a baked image with pg and the extension, or suffer through your own installation:
CREATE EXTENSION pg_textsearch;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
title text,
category text,
content text
);
Then create an index:
CREATE INDEX documents_bm25
ON documents USING bm25(content)
WITH (text_config = 'english');
That index is conceptually similar to what Elasticsearch was building for us: it takes the text column we provide and prepares it for relevance-ranked queries.
The difference is that the documents are still in the database where the rest of the application already lives.
"ILIKE" is ok.. BM25 tells you best.
The difference becomes obvious when the dataset gets larger.
I expanded the seed data to 50,010 support tickets across several categories.
That is not a benchmark, and I am not pretending it is one.
It is simply enough data to make the search behavior visible.
SELECT count(*) AS documents FROM documents;
The familiar Postgres solution is ILIKE:
SELECT id, title, category
FROM documents
WHERE content ILIKE '%connection%';
This works. It finds matching rows. But it does not give us a useful relevance order.
For that, we use the <@> operator and order by its score:
SELECT id, title, category,
round((content <@> 'connection timeout')::numeric, 2) AS score
FROM documents
ORDER BY content <@> 'connection timeout';
Now the results are ranked by how closely they match “connection timeout.”
The score is negative because the operator is designed for ascending index scans, so lower values come first. More negative means a better match.
And because this is still SQL, regular filters work exactly where you expect them to:
SELECT id, title, category,
round((content <@> 'connection timeout')::numeric, 2) AS score
FROM documents
WHERE category = 'database'
ORDER BY content <@> 'connection timeout';
That is the useful part for a lot of applications.
Search the text, filter by a normal database field, order by relevance, and limit the result set.
No synchronization pipeline between two systems required.
The query plan is worth checking too:
EXPLAIN (COSTS OFF)
SELECT id, title
FROM documents
ORDER BY content <@> 'connection timeout';
On a tiny table, PostgreSQL may choose a sequential scan because reading three rows is cheaper than using an index. That is not the extension failing; it is the planner doing the sensible thing. On a larger set, the query has a real index to use.
Powerful enough, not universally better
Changing the search is now just changing the query:
SELECT id, title, category,
round((content <@> 'certificate renewal')::numeric, 2) AS score
FROM documents
ORDER BY content <@> 'certificate renewal';
The ranking changes based on the terms and the document content.
That is the point: we are not checking for a simple yes-or-no match anymore.
We are asking Postgres to rank the candidates.
There is an important boundary here: BM25 ranks candidates, but exact phrase matching may still need a SQL condition when that distinction matters:
SELECT id, title
FROM documents
WHERE content ILIKE '%connection timeout%'
ORDER BY content <@> 'connection timeout';
Elasticsearch is still the right answer for many teams.
If you need its broader search language, large scale aggregations, geospatial features, complex search workflows, or a dedicated search platform operated independently from your primary database, use it.
BUT, at the same time it's important to say that PG has great extensions to cover many of these needs.
PostGIS is famous spatial data search extension and I covered many more in this video.
Also, do not take this demo as a speed claim.
Elasticsearch and Postgres are different systems, and a fair comparison needs a proper workload, hardware, and benchmark methodology.
This is an architecture and capability comparison: can useful BM25 search live next to the data?
In many application and support ticket workloads for example, yes.
Search where the data already lives
That is the real takeaway.
Postgres is not only a place to store rows.
With extensions, it can cover more of the infrastructure around an application: search, queues, caching patterns, pub/sub, semi structured documents, and more (again - all in this video).
That does not mean Postgres should replace every specialized system.
It means the default architecture of “add another service” deserves one extra question: do we actually need it?
If your search is mostly documents, filters, and relevance ranking, pg_textsearch is worth trying before you add Elasticsearch.
Keep the data in Postgres, create the index, use the operator, and see whether it covers the problem.
The win here is not that PG has magically become elastic.
The win is that many teams may not need all of Elasticsearch in the first place.
I hope this was valuable! Thank you for reading.
Feel free to reply directly with any question or feedback.
Have a great weekend!
Whenever you’re ready, here’s how I can help you:
|
|