How does OpenSearch work compared with Postgres search?
Both systems rely on the same basic data structure, an inverted index that maps each term to the documents containing it. They differ in where that index lives and how data gets into it.
OpenSearch stores JSON documents in indices. Each index is split into primary shards, and each primary can have replica shards on other nodes. At index time, an analyzer tokenizes each text field, lowercases the tokens, and optionally stems them or expands synonyms. The results go into a per-shard inverted index. When a query arrives, the receiving node fans it out to one copy of every relevant shard. Each shard scores its matches, by default with BM25, which uses term frequency, document length, and how rare a term is across the corpus. The coordinating node then merges the per-shard results. New documents become searchable after a refresh rather than at write time, so the index is near-real-time. A cluster manager node tracks cluster state and decides where shards are placed. Because OpenSearch is a separate system, your application or a pipeline has to send it every change made in the source database.
Postgres keeps the search index next to the rows. A tsvector column, ideally generated from the source columns, holds normalized lexemes, and a GIN index provides the inverted lookup. A query compares a tsquery against that column with the @@ operator. The planner can combine the match with ordinary SQL filters, joins, and LIMIT in a single plan, and ranking functions score the matching rows. For names, codes, and typo tolerance, the pg_trgm extension adds trigram-based indexes on the same tables. Index entries are written in the same transaction as the row, so there is no refresh delay and nothing to synchronize.
The sections below cover each side in detail: what the cluster and its pipeline cost, how far Postgres full-text and trigram search go, and how to tell when you have outgrown them.
What does a managed OpenSearch cluster really cost beyond the instance price?
The instance price is the smallest line item. A production cluster that survives a node failure needs at least three data nodes spread across availability zones. It also needs three dedicated cluster manager nodes so that elections and cluster-state updates don't compete with indexing and queries for heap. One replica per primary shard doubles stored bytes. Disk watermarks block shard allocation well before a volume is full, so you also pay for headroom. Snapshots add object storage, and replicating shards across zones adds transfer charges on every indexed document.
| Cost component | Scales with data size | Scales with query load | Scales with availability needs |
|---|---|---|---|
| Data nodes | Yes | Yes | Yes (minimum count, zone spread) |
| Dedicated cluster manager nodes | Weakly (cluster state, shard count) | No | Yes |
| Replica shards | Yes | Yes (replicas serve reads) | Yes |
| Storage headroom | Yes | No | No |
| Snapshots | Yes | No | Yes (retention) |
| Cross-AZ traffic | Yes (indexing volume) | Somewhat | Yes |
"Managed" means the provider patches nodes and replaces failed hardware. It does not choose your shard count. It also won't fix a field mapped as text that should have been keyword, or run the reindex that the fix requires. Version upgrades still need you to test queries, analyzers and client libraries.
Underprovisioned clusters fail in ways CPU graphs don't show. Heap pressure causes long garbage-collection pauses and circuit-breaker rejections on large aggregations. When a node drops out, its shards become unassigned and cluster health turns yellow or red. Recovery then copies shards over the network while the cluster is already under strain.
Why is the sync pipeline the most underestimated search infrastructure cost?
The cluster is only half of the bill. Search data has to come from somewhere, and in most systems the source of truth stays in Postgres. Adding OpenSearch means running a second stateful system, plus a pipeline to keep it in step with the first.
Postgres only:
app ──► UPDATE row + tsvector ──► COMMIT (search sees it immediately)
Postgres + external index:
app ──► Postgres ──► CDC (WAL) ──► queue/connector ──► indexer ──► OpenSearch
│ │ │
slot lag consumer lag mapping errors,
bulk rejections
On the left, the search vector changes in the same transaction as the row. A rollback undoes both, and there is no window in which search disagrees with the data. On the right, every hop can lag, fail, or drop events. Dual writes from the application avoid CDC, but a crash between the two writes leaves them inconsistent with nothing to repair it. CDC fixes that, but adds a replication slot that retains WAL and can fill the disk if the consumer stalls. It also adds a connector that needs deploying, upgrading, and alerting.
The ongoing work is mostly about drift:
- Stale results. Users see deleted items or miss new ones, and support files them as search bugs.
- Consistency checks. You end up writing jobs that compare row counts or checksums between the two stores.
- Backfills and reindexes. Mapping changes, analyzer changes, and recovery from pipeline bugs each need a full rebuild, often with alias swaps to avoid downtime.
Each of these is code someone writes, tests, and gets paged for.
A) Postgres only: one system, one transaction
┌───────┐ UPDATE row + tsvector ┌───────────────────────────┐
│ app │ ──────────────────────► │ Postgres │
└───────┘ (same transaction) │ rows + tsvector (GIN) │
│ COMMIT: search sees it │
│ ROLLBACK: undoes both │
└───────────────────────────┘
B) Postgres + CDC + queue + OpenSearch: two stateful systems
┌───────┐ write ┌──────────┐ WAL ┌───────────┐ ┌─────────┐
│ app │ ────► │ Postgres │ ──► │ repl slot │ ──► │ queue │
└───┬───┘ └──────────┘ │ connector │ └────┬────┘
┆ dual write (crash = drift) └───────────┘ │ consumer lag
┆ slot lag, WAL kept ▼
┆ ┌────────────┐ ┌───────────┐
└┄┄┄┄┄┄┄┄┄┄►│ OpenSearch │ ◄──── bulk ─────── │ indexer │
└─────┬──────┘ └───────────┘
▲ stale results mapping errors,
│ bulk rejections
┌─────────────────────┴───────┐ ┌──────────────────────────────┐
│ backfill / reindex jobs │ │ consistency checks: counts / │
│ (alias swap, full rebuild) │ │ checksums, Postgres vs index │
└─────────────────────────────┘ └──────────────────────────────┘
With Postgres alone the row and its search vector commit or roll back together, while an external index adds a CDC slot, connector, queue, indexer, and backfill and consistency jobs, each of which can lag, fail, or drift from the source of truth.
How does Postgres full-text search work, and what can it already do?
Postgres can do much of the same work inside the database, with no pipeline to maintain. Full-text search rests on two types. A tsvector is a document reduced to normalized lexemes with positions: to_tsvector('english', 'Running shoes') yields 'run':1 'shoe':2, with stop words dropped and words stemmed. A tsquery is a boolean expression over lexemes, built by websearch_to_tsquery('english', 'running shoe'). The @@ operator tests whether one matches the other, and a GIN index makes that test fast. The index maps each lexeme to the rows that contain it, which is the same inverted-index structure a search engine uses.
The sync problem from the previous section goes away if the vector is a generated column. In the schema, define search_vector tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B')) STORED, then run CREATE INDEX ON articles USING GIN (search_vector). Every insert or update recomputes the vector in the same transaction as the write, so the index cannot drift from the data. The config name must be passed explicitly, because the expression has to be immutable to be used in a generated column.
setweight labels title lexemes A and body lexemes B. Ranking functions then score A matches higher. For ordering, use ts_rank(search_vector, q), or use ts_rank_cd if you want proximity to count. ts_rank_cd rewards documents where the query terms appear close together. A typical ranked query filters with WHERE search_vector @@ q, orders by rank descending, and applies a LIMIT.
There are two trade-offs:
- No corpus-wide statistics.
ts_rankscores each document on its own. It has no inverse document frequency, so a rare term counts the same as a common one. BM25 uses corpus-wide statistics to weight rare terms higher. - Manual language maintenance. You maintain language configurations, dictionaries, and synonym files by hand, and you must regenerate the vectors whenever you change them.
-- Schema: the search vector is a stored generated column, so it is
-- recomputed in the same transaction as every INSERT/UPDATE (PostgreSQL 12+).
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text,
search_vector tsvector GENERATED ALWAYS AS (
-- The config is passed explicitly so the expression is immutable.
-- Title lexemes get weight A, body lexemes weight B.
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED
);
-- Inverted index: maps each lexeme to the rows containing it.
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);
INSERT INTO articles (title, body) VALUES
('Running shoes for trail runners', 'Grip and cushioning compared.'),
('Choosing a road bike', 'Frame sizes, and why running shoes do not fit here.');
-- Ranked query: @@ filters via the GIN index, then rows are ordered by score.
-- websearch_to_tsquery accepts user-style input (quotes, OR, -exclusions).
SELECT id,
title,
ts_rank(search_vector, q) AS rank, -- term frequency + weights
ts_rank_cd(search_vector, q) AS rank_cd -- also rewards proximity
FROM articles,
websearch_to_tsquery('english', 'running shoe') AS q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 10;
A generated tsvector column weighted title-over-body, its GIN index, and a ranked full-text query using websearch_to_tsquery with ts_rank and ts_rank_cd.
When should you use pg_trgm instead of full-text search?
Full-text search works on words. It stems them, drops stop words, and matches whole lexemes. That suits prose, but it works poorly for short identifiers. A SKU like AB-1234-X gets split by the parser, an email address is treated as a single token, and a user typing 1234 or a misspelled surname gets nothing back. The pg_trgm extension takes a different approach. It breaks each string into three-character sequences and compares the overlap between those sets. That gives you substring matching and typo tolerance without any language rules.
Enable it with CREATE EXTENSION pg_trgm;, then index the column with CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);. This GIN index speeds up name ILIKE '%smit%', which would otherwise require a sequential scan. For fuzzy matching, name % 'jonh smith' uses similarity() against pg_trgm.similarity_threshold, which defaults to 0.3. Use 'smith' <% name when the search term should match one word inside a longer value. To rank results by closeness, use ORDER BY name <-> 'jonh smith' LIMIT 10. That ordering can use a GiST index built with gist_trgm_ops.
| Concern | Full-text search | pg_trgm |
|---|---|---|
| Best fit | Articles, descriptions | Names, SKUs, emails |
| Substring match | Prefix only (:*) |
Any position |
| Typos | No | Yes, via similarity |
| Relevance ranking | ts_rank |
Similarity score |
pg_trgm has three limitations:
- Short terms. Terms under three characters produce few or no useful trigrams, so the index cannot narrow the search.
- Low thresholds. A threshold set too low matches a large share of the table, and every candidate row must be rechecked.
- Write cost. A trigram GIN index adds many entries per row, which slows writes. With
fastupdateenabled, new entries collect in a pending list that must be merged into the main index. That merge happens during vacuum or when the list grows pastgin_pending_list_limit, so some inserts and queries pay the cost.
-- Enable trigram support (once per database)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN index: accelerates ILIKE '%term%' and the % / <% operators
CREATE INDEX customers_name_trgm
ON customers USING gin (name gin_trgm_ops);
-- GiST index: supports ORDER BY name <-> 'term' (nearest-neighbour ranking)
CREATE INDEX customers_name_trgm_gist
ON customers USING gist (name gist_trgm_ops);
-- Substring match anywhere in the value; can use the GIN index
SELECT id, name FROM customers WHERE name ILIKE '%smit%';
-- Typo-tolerant match. Raise the threshold above the 0.3 default
-- so fewer weak candidates need to be rechecked.
SET pg_trgm.similarity_threshold = 0.4;
SELECT id, name, similarity(name, 'jonh smith') AS score
FROM customers
WHERE name % 'jonh smith'
ORDER BY score DESC;
-- Match a term against one word inside a longer value
-- (threshold: pg_trgm.word_similarity_threshold, default 0.6)
SELECT id, name FROM customers WHERE 'smith' <% name;
-- Top 10 closest names by trigram distance; can use the GiST index
SELECT id, name, name <-> 'jonh smith' AS distance
FROM customers
ORDER BY name <-> 'jonh smith'
LIMIT 10;
-- Write-heavy table: cap the GIN pending list so it is merged in smaller batches,
-- and flush it manually (for example after a bulk load)
ALTER INDEX customers_name_trgm SET (gin_pending_list_limit = 1024); -- in kB
SELECT gin_clean_pending_list('customers_name_trgm');
Creating trigram GIN and GiST indexes, then running substring, similarity, word-similarity and distance-ranked queries, with a tuned threshold and GIN pending-list maintenance for write-heavy tables.
OpenSearch cost vs Postgres: an illustrative monthly estimate
Illustrative estimate (assumed numbers, not a benchmark). The point of this exercise is the shape of the comparison, not the totals. Substitute your provider's prices for the variables.
Assumed inputs:
- 5 million rows, about 10 GB of searchable text.
- Peak load of 20 search queries per second.
- Search option: three small nodes for high availability, each storing a full copy of the data (primary plus replica shards). Each node gets 30 GB of disk.
- Postgres option: the existing primary and replica each move up one instance size. Each also gets 15 GB of extra disk for GIN and trigram indexes.
- Engineering time per month: 8 hours to maintain the sync pipeline, 6 hours for cluster operations, and 3 hours for Postgres index and query tuning.
Variables: H = 730 hours per month, P_node = hourly price of a search node, ΔP_pg = hourly price difference between the current and next Postgres instance size, P_gb = monthly price per GB of storage, R = loaded hourly cost of an engineer, E_pipe = monthly cost of pipeline infrastructure (queue, workers).
| Cost line | Search cluster | Postgres |
|---|---|---|
| Compute | 3 × P_node × H |
2 × ΔP_pg × H |
| Storage | 3 × 30 × P_gb |
2 × 15 × P_gb |
| Pipeline infra | E_pipe |
0 |
| Engineering | (8 + 6) × R |
3 × R |
The two totals are each column added up:
- Search cluster: 3·
P_node·H+ 90·P_gb+E_pipe+ 14·R - Postgres: 2·
ΔP_pg·H+ 30·P_gb+ 3·R
The engineering line usually decides the comparison. At typical salaries, R dominates P_gb. Eleven extra hours a month can cost more than the cluster's compute.
How can staying on Postgres save engineering time?
The illustrative estimate above assumes 14 hours a month of search-related work with a cluster and 3 hours without one. Those hours are assumed inputs, not measurements. The categories of work behind them are concrete, though, and you can check each one against your own team.
Work that disappears when search stays in Postgres:
- No pipeline to build or run. There is no CDC connector, queue, indexer, replication slot, or dual-write logic to write, deploy, upgrade, and monitor. A generated
tsvectorcolumn updates in the same transaction as the row. - No drift to investigate. "Search shows a deleted item" stops being a ticket category, and you do not need consistency-check jobs comparing two stores.
- No reindex orchestration. Changing the search expression means altering a column and rebuilding an index in a database the team already operates. You do not run a separate rebuild with alias swaps and a backfill from the source of truth.
- No second system to upgrade. You have one set of version upgrades, client libraries, backups, access controls, and runbooks instead of two.
- No new on-call surface. Unassigned shards, heap pressure, and a stalled replication slot filling the primary's disk are failure modes you do not have to learn and alert on.
- One query language. Search, filters, joins, and permissions live in SQL, in the same code review and the same test database. Engineers do not have to learn a second query DSL or keep two schemas in sync.
Some work remains. You still tune indexes, watch GIN pending-list behaviour on write-heavy tables, maintain dictionaries if you use custom ones, and re-test relevance when you change weights. That work happens inside a system the team already understands.
The time savings also show up at the start. Adding a GIN index and a generated column is a schema migration. Standing up a cluster, a mapping, and a sync pipeline is a project. If Postgres turns out to be insufficient later, the measurement work in the next section tells you so before you have spent that effort.
How can you measure whether Postgres search is good enough?
Start with the queries your users actually run. Pull the top search patterns from logs or pg_stat_statements and include short, common, and pathological terms. Run each one as EXPLAIN (ANALYZE, BUFFERS) SELECT ... against production-sized data. Small test tables will not show the costs you need to see.
Reading the plan
Check that the plan uses a Bitmap Index Scan on your GIN index rather than a sequential scan. Then read the Buffers: line. shared hit counts pages served from Postgres's buffer cache. shared read counts pages fetched from outside it, either the OS cache or disk. If read dominates after repeated runs, the index does not stay in memory and latency will vary with I/O.
Sizing the index
Compare SELECT pg_size_pretty(pg_relation_size('your_gin_index')); against shared_buffers and the RAM available for OS cache. If the index fits with room left for your hot transactional data, most searches stay cheap.
Testing under load
A single fast query proves little. Replay realistic traffic at peak concurrency and track p95 and p99, not the average. Tail latency is what users notice.
Separating search from writes
Run search reads on a read replica. Search scans compete with transactional queries for CPU, memory, and I/O. On a replica, a heavy query cannot slow down checkout.
What the evidence buys you
A day spent collecting these numbers either confirms that Postgres is enough or shows exactly which limit you have hit. Either result is cheaper than building a sync pipeline you did not need.
-- 1. Find the most expensive search queries actually hitting the database.
-- Requires the pg_stat_statements extension (columns as of Postgres 13+).
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements
WHERE query ILIKE '%to_tsquery%'
ORDER BY total_exec_time DESC
LIMIT 10;
-- 2. Run a representative query against production-sized data.
-- Run it several times: the first run may show cold-cache reads.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM products
WHERE search_vector @@ websearch_to_tsquery('english', 'wireless headphones')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'wireless headphones')) DESC
LIMIT 20;
-- Look for: Bitmap Index Scan on products_search_gin (not Seq Scan)
-- Buffers: shared hit=N read=M
-- hit = pages served from shared_buffers
-- read = pages fetched from the OS cache or disk
-- If read stays high on repeated runs, the index is not staying in memory.
-- 3. Compare GIN index size with the memory available to hold it.
SELECT pg_size_pretty(pg_relation_size('products_search_gin')) AS gin_index_size;
SHOW shared_buffers;
-- 4. Cluster-wide cache hit ratio for the table's indexes.
SELECT indexrelname,
idx_blks_hit,
idx_blks_read,
round(idx_blks_hit::numeric / nullif(idx_blks_hit + idx_blks_read, 0), 4) AS hit_ratio
FROM pg_statio_user_indexes
WHERE relname = 'products';
SQL that finds expensive search queries, checks for a GIN index scan and the buffer hit/read split in EXPLAIN output, and compares index size and cache hit ratio against available memory.
Who should use Postgres search, and who should use OpenSearch?
Postgres search fits teams that:
- Already run Postgres as the source of truth for the data being searched.
- Need search as a supporting feature, such as finding customers, orders, tickets, documents, or catalog items, rather than as the core product.
- Search a corpus that fits on one database server, where the GIN and trigram indexes can mostly stay in memory.
- Need search results to reflect writes immediately, for example right after a user creates or deletes a record.
- Have a small platform team with no spare capacity to operate a second stateful system and a sync pipeline.
- Filter search results by tenant, permissions, or status columns that already live in relational tables.
OpenSearch fits teams that:
- Sell search as the product, as marketplaces, large catalogs, and content discovery sites do, where relevance and faceting directly affect revenue or retention.
- Need faceted counts over large result sets, BM25-style relevance tuning, per-language analyzers, or large synonym sets.
- Index data from several source systems into one searchable store.
- Ingest log, metric, or event data at volumes that do not belong in the transactional database.
- Have, or can staff, engineers to own the cluster and the pipeline that feeds it.
If your team fits the first list but expects to move toward the second, start with Postgres behind a search interface, as described in the next section, so the switch stays contained when it comes.
When is a dedicated search cluster worth it, and how do you design for the switch?
Some limits do not move with better indexing. Faceted navigation is the clearest. Showing counts across ten dimensions for a result set of millions of rows means a GROUP BY per facet or materialized views that lag behind writes, whereas OpenSearch computes aggregations alongside the query. Other signals include:
- Relevance tuning beyond
ts_rankweights. - Per-language analyzers and large synonym sets.
- Search spanning several source databases.
- Log or event analytics volumes.
- Search load that strains the primary even after you add read replicas.
| Requirement | Postgres | Postgres with workarounds | OpenSearch |
|---|---|---|---|
| Keyword and fuzzy match | Yes | — | Yes |
| Facets over large result sets | No | GROUP BY, materialized views |
Native aggregations |
| Tunable relevance | Basic | Custom ranking SQL | Native |
| Multi-language and synonyms at scale | Limited | Per-language configs | Native |
| Search across several databases | No | ETL into one database | Native |
| Log and event analytics | No | Partitioning | Native |
Adopt early when the roadmap is clear
If search is your product, as with marketplaces or catalogs with deep filtering, start with OpenSearch. A later migration means rebuilding the relevance users have grown used to, building the sync pipeline under production pressure, and retraining the team on a new query model.
Design for the switch
Route every search call through an internal search interface or query service, so that changing the backend touches one module instead of every caller. Keep a relevance test set of real queries with their expected top results. Define latency budgets up front, for example p95 targets per endpoint. When Postgres misses those budgets or fails the test set, you have evidence for the switch, and the same artifacts validate the replacement.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to opensearch cost vs postgres, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.