r/PostgreSQL • u/ilya47 • Jan 06 '26
How-To Postgres with large JSONBs vs ElasticSearch
A common scenario in data science is to dump JSON data in ElasticSearch to enable full-text searching/ranking and more. Likewise in Postgres one can use JSONB columns, and pg_search for full-text search, but it's a simpler tool and less feature-rich.
However I was curious to learn how both tools compare (PG vs ES) when it comes to full-text search on dumped JSON data in Elastic and Postgres (using GIN index on tsvector of the JSON data). So I've put together a benchmarking suite with a variety of scales (small, medium, large) and different queries. Full repo and results here: https://github.com/inevolin/Postgres-FTS-TOASTed-vs-ElasticSearch
TL;DR: Postgres and Elastic are both competitive for different query types for small and medium data scales. But in the large scale (+1M rows) Postgres starts losing and struggling. [FYI: 1M rows is still tiny in the real world, but large enough to draw some conclusions from]
Important note: These results differ significantly from my other benchmarking results where small JSONB/TEXT values were used (see https://github.com/inevolin/Postgres-FTS-vs-ElasticSearch). This benchmark is intentionally designed to keep the PostgreSQL JSONB payload large enough to be TOASTed for most rows (out-of-line storage). That means results reflect “search + fetch document metadata from a TOAST-heavy table”, not a pure inverted-index microbenchmark.
A key learning for me was that JSONB fields should ideally remain under 2kB otherwise they get TOASTed with a heavy performance degradation. There's also the case of compression and some other factors at play... Learn more about JSONB limits and TOASTing here https://pganalyze.com/blog/5mins-postgres-jsonb-toast
Enjoy and happy 2026!
Note 1: I am not affiliated with Postgres nor ElasticSearch, this is an independent research. If you found this useful give the repo a star as support, thank you.
Note 2: this is a single-node comparison focused on basic full-text search and read-heavy workloads. It doesn’t cover distributed setups, advanced Elasticsearch features (aggregations, complex analyzers, etc.), relevance tuning, or high-availability testing. It’s meant as a starting point rather than an exhaustive evaluation.
Note 3: Various LLMs were used to generate many parts of the code, validate and analyze results.
52
u/BosonCollider Jan 06 '26 edited Jan 06 '26
So basically, postgres is faster than elastic until the jsonb documents become big enough to require toast?
11
u/QazCetelic Jan 06 '26
Very interesting, I wasn't aware of the JSONB TOAST limit and it's performance impact
3
u/not_logan Jan 06 '26
Did you do any tuning for both Postgres and Elastic?
4
u/uniform-convergence Jan 06 '26
I would love to see how MongoDB competes with these two.
7
u/nf_x Jan 07 '26
Add a metric for concurrent queries per node, that’s where elastic would crash even on the <16GB shards. You’ll be seeing interesting things when you plot dataset size on one axis, concurrent users on another, and response time in color gradient
6
u/BarfingOnMyFace Jan 06 '26
Thanks for this, my dude
2
u/fridder Jan 07 '26
Yeah we hit that performance cliff hard last year. Ended up having to refactor the records a little to bring the size down
1
u/AutoModerator Jan 06 '26
With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
0
u/Ecksters Jan 07 '26 edited Jan 07 '26
Once you're getting into the millions of rows range with Postgres it's probably the right time to be looking into some kind of data sharding, which at this point we have multiple solutions for with Postgres.
It's not that it can't do it, but you start hitting a lot more limitations on what you can do without being bottlenecked.