OpenSearch vs PostgreSQL for application search: when Postgres is enough
Search engines earn their complexity at web scale. Below it, a well-indexed Postgres often wins on latency, cost, and operations. How to tell which side of the line you are on, and how to prove it with replayed production queries.
Most application search does not need a search engine. It needs an index, a ranking function the product team understands, and results that agree with the database of record. Elasticsearch and OpenSearch earn their complexity at web scale. Below that, a well-indexed PostgreSQL often wins on latency, cost, and the number of systems you have to keep in sync. Here is how to tell which side of that line you are on, and how to prove it before you commit.
When you need a search engine
Keep OpenSearch or Elasticsearch if any of these describe your workload.
- Hundreds of millions of documents or more, with a write rate that a single Postgres primary could not absorb alongside its transactional load.
- Relevance tuning is a product feature: per-field boosting, synonyms, custom analyzers for several languages, learning-to-rank.
- Aggregations over the full corpus are on the hot path, such as faceted counts on every query across tens of fields.
- Log or event search, where the data is append-only and retention rolls off by time. That is what these engines were built for.
When Postgres is enough
Consider PostgreSQL if this sounds more like you.
- The corpus is in the tens of millions of rows, not billions.
- Search is mostly filtered lookups with a text component: name matches, address matches, "companies in this industry near this point," ranked by a handful of fields.
- The source of truth is already a relational database and the search index is a copy you have to keep in sync.
- Geospatial or vector similarity is part of the query. PostGIS and pgvector cover both inside the same query planner.
- Tail latency is what users complain about, and the cluster is small enough that one big shard is doing most of the work.
That last point is the common failure mode. A three-node cluster with one index of 34 million documents on a single 93 GB shard is not really a distributed search engine. It is one machine with a lot of ceremony, resharding is a multi-day project, and the 90th percentile query can sit above three seconds while the median looks fine.
What the Postgres version looks like
On PostgreSQL 18 the building blocks are full-text search with GIN indexes, trigram indexes for fuzzy and prefix matching, PostGIS for geography, pgvector for embeddings, and the planner combining them with ordinary filters. The design work is in three places.
- A query translator. The application already speaks a search request format. Map it onto SQL in one layer so product code does not change and the two systems can be compared request for request.
- Index design against real queries. Not the queries you imagine; the ones in the logs. Composite indexes that match the actual filter combinations matter more than any single text index.
- Rebuild time. Migrations and schema changes will rebuild large indexes. On a 268 GB corpus, PostgreSQL 18 rebuilt the indexes in 12 minutes where 17 took 59, which shaped the upgrade path before the migration started.
How to prove it before you commit
Do not argue about it. Measure it.
- Build a loader and a benchmark harness so every claim has a number.
- Capture a sample of real production queries. Around a thousand is enough to cover the shapes that matter.
- Replay them against both systems and diff the results, not just the timings. Agreement is the metric that decides whether users will notice the switch.
- Verify row-exact parity across the full corpus before any cutover planning.
On a commercial real estate data platform covering millions of companies, contacts, and buildings, that process produced these numbers: p50 search latency 197 milliseconds on Postgres against 302 on OpenSearch, p90 704 milliseconds against 3,091, and 99.2 percent agreement across 1,200 replayed production queries. Parity was row-exact across 25.2 million pages, 5.6 million contacts, and 4.5 million buildings. The tail got 4.4 times faster, and the tail is where users feel search speed.
What you gain besides speed
One data store to operate, back up, secure, and put in the audit scope instead of two. No sync pipeline to fall behind. No cluster to reshard. A smaller bill, because the search nodes go away and the database was already there.
What to watch
- Search load now shares a primary with transactional writes. Put search on a read replica if the write rate is meaningful, and size the replica for the index working set.
- Relevance is simpler. If the product depended on a tuned analyzer chain, reproduce the behaviour that users actually rely on and let the rest go. The replay diff tells you which is which.
- Vector search in pgvector is good to tens of millions of rows with the right index type. Past that, benchmark before assuming.
The full story
The harness, the translator, and the parity checks are described in the OpenSearch to PostgreSQL case study.
Running search on a system you'd rather not?
We'll benchmark the alternative against your real queries and tell you which side of the line you are on before recommending anything.
