What does it mean to sync Postgres to OpenSearch reliably?
Reliability starts with a strict division of roles. Postgres is the only source of truth. OpenSearch is a projection of it: a denormalized, query-optimized copy that you can delete and rebuild at any time from Postgres alone. If the two disagree, Postgres is right by definition, and the fix is to re-project. You never reconcile in the other direction.
The common first attempt breaks this model. The application commits to Postgres and then calls OpenSearch from the same request handler. That gives you two writes with no shared transaction. Each failure mode loses data quietly:
- Crash or deploy between the two writes. The row is committed, but the index update never happens, and nothing records that it was missed.
- Timeout on the OpenSearch call. You can't tell whether the write landed. Retrying inline slows the request, and skipping the retry leaves the index stale.
- Concurrency. Two requests update the same row. Their Postgres commits happen in one order, and their index writes can arrive in the opposite order. An older version then overwrites a newer one and stays that way.
The pipeline in this article targets three properties:
- No committed change is ever lost.
- A stale version never overwrites a newer one.
- A full rebuild is a routine operation, not an emergency.
This approach is for backend and platform engineers running a monolith against one primary Postgres. Use it when your search, filtering, or relevance requirements have outgrown Postgres full-text search, for example faceted aggregations, language-specific analyzers, or tunable relevance scoring.
DUAL WRITE (fragile) PROJECTION MODEL (target)
+-------------+ +-------------+
| Request | | Application |
| handler | +------+------+
+--+-------+--+ | writes only
| | v
| (1) | (2) +-------------+
v v | Postgres |---------+
+------+ +------------+ | source of | | full
| PG | | OpenSearch | | truth | | rebuild,
+------+ +------------+ +------+------+ | routine
no shared transaction | committed |
| changes |
x crash between (1) and (2): v |
index update lost, unrecorded +-------------+ |
x timeout on (2): landed or not? | Sync | |
x concurrent requests: (2)s can | pipeline | |
arrive out of commit order, +------+------+ |
older version overwrites newer | idempotent, |
| version-checked|
v |
+-------------+ |
| OpenSearch | |
| projection |<--------+
| disposable |
+-------------+
Dual writes from a request handler can lose or reorder index updates. The target model treats Postgres as the only source of truth and feeds a disposable OpenSearch projection through a sync pipeline, and a routine full rebuild can recreate that projection from Postgres alone.
Who should use this approach?
The outbox-based pipeline described here fits a specific situation. It is a good choice if most of the following are true:
- You run a monolith, or one codebase owns all writes to the indexed tables. The outbox depends on every write path inserting an event in the same transaction. That is easy when one application performs the writes and hard when several services, scripts, and manual SQL sessions modify the same tables.
- You have one primary Postgres. Transactional guarantees only hold within one database. Workers can read from replicas, but the outbox row and the business change must commit together on the primary.
- Your search documents are built from several tables. A product document that combines prices, categories, and inventory is easier to build from "reindex product 42" events than from row-level change streams.
- Seconds of indexing delay are acceptable. The pipeline is asynchronous. Search results become eventually consistent with the database, and screens that need read-your-writes read from Postgres instead.
- Your team can own a small amount of infrastructure. That means a worker process, an outbox cleanup job, autovacuum tuning for one table, and a few alerts.
It is a poor fit in these cases:
- Postgres full-text search already meets your requirements. Adding a second datastore adds operational work that buys you nothing.
- Many independent writers modify the tables and you can't change all of them. Log-based CDC captures changes without application cooperation, so it suits this case better, provided you can operate replication slots.
- You need search results to be strictly consistent with every commit. No asynchronous projection gives you that. Query Postgres for those reads.
- The dataset is small and changes rarely. A scheduled full rebuild through an alias swap may be all you need.
How does the sync pipeline work?
The pipeline has five moving parts. Each one is covered in detail in the sections that follow.
- Capture the change in the same transaction. When the application modifies an entity that appears in search, it also inserts a row into an outbox table that says "reindex this entity, version N". Both rows commit or roll back together, so an event exists if and only if the change is committed.
- Claim events with a worker. One or more worker processes poll the outbox and claim batches using
SELECT ... FOR UPDATE SKIP LOCKED. Workers never block each other and never process the same locked row at the same time. - Rebuild the full document from Postgres. The worker deduplicates events by entity, re-reads the current committed state with the necessary joins, and builds the complete OpenSearch document. Events carry identity and version, not data.
- Write idempotently and in version order. The worker sends documents through the
_bulkAPI, using the primary key as_idand the database version counter withversion_type=external. Replays overwrite the same document, and OpenSearch rejects stale versions with a 409. The worker inspects every item in the response, marks successes and 409s done, retries transient failures with backoff, and moves permanent failures to a dead-letter table. - Acknowledge only after OpenSearch confirms. The outbox rows are deleted or marked processed after the bulk response is handled. A crash before that point releases the locks, and the rows are retried.
Two supporting processes keep the projection healthy over time:
- Zero-downtime rebuilds. Clients query an alias. A new index is backfilled from Postgres with the same document builder, verified, and swapped in atomically.
- Drift detection. A scheduled reconciliation job compares counts and maximum versions per ID range between Postgres and OpenSearch and reindexes ranges that disagree.
The result is at-least-once delivery from the outbox combined with idempotent, version-checked writes in OpenSearch. Together they make the index converge on the committed state in Postgres, even when workers crash, requests time out, or events arrive out of order.
Should you use a transactional outbox or logical replication CDC?
What are the alternatives? The outbox is one of five approaches that come up in practice. The others are dual writes from the request handler, polling on an updated_at column, staying on Postgres full-text search with no sync at all, and log-based change data capture (CDC) through logical replication. For small, slow-changing datasets, a scheduled full rebuild via an alias swap is a sixth option. The approaches differ mainly in what breaks when a component fails.
| Option | Operational requirements | Main failure mode | Fit for multi-table documents | Development cost |
|---|---|---|---|---|
| Dual writes | None extra | DB commits, index write fails; silent divergence | Good | Low |
Polling on updated_at |
Index on updated_at |
Misses hard deletes and same-timestamp commits | Good | Low |
| Postgres FTS, no sync | None | Hits feature limits, not a sync failure | N/A | Lowest |
| Transactional outbox | Cleanup job, vacuum tuning | Table bloat, worker lag | Good | Medium |
| Logical replication CDC | wal_level=logical, slot monitoring, CDC consumer |
Stalled slot retains WAL and fills primary disk | Poor | High |
Dual writes look cheap, but nothing makes the database and index writes atomic. Avoid them unless losing an index update is acceptable.
Polling works for append-mostly tables. A long transaction can commit with an updated_at earlier than your high-water mark, so the poller skips that row. Avoid polling if you need hard deletes or tight freshness.
Staying on Postgres FTS remains the right answer until the requirements listed above apply.
The outbox writes an extra row in the same transaction, so the index can never be ahead of committed data. The cost is write amplification and dead tuples. Without batched deletes and per-table autovacuum tuning, the outbox table bloats. The outbox suits a monolith because the application already knows which document a change affects.
Log-based CDC requires no application changes. However, a replication slot holds WAL until its consumer confirms it, so a stalled consumer can fill the primary's disk. You need alerts on retained WAL and a runbook for dropping the slot and reindexing. CDC also emits row-level events, so building a document from five tables means re-querying or joining streams. Avoid CDC when your documents are heavily denormalized or your team cannot own the slot operations.
How do you implement a transactional outbox for search indexing?
Start with a narrow outbox table: id (bigserial), entity_type (such as product), entity_id, version (the entity's version counter or a monotonically increasing value), created_at, and attempts. Index on id, or on created_at if workers claim rows in time order.
-
Write the outbox row in the business transaction. When the application updates a product, it inserts
('product', 42, 17)beforeCOMMIT. If the transaction rolls back, the event disappears with it. This removes the dual-write gap where the database commits but the indexing call fails. -
Claim a batch with
SELECT ... FOR UPDATE SKIP LOCKED. UseORDER BY idandLIMIT. Concurrent workers skip rows that another worker has locked, so you can scale out without a coordinator. -
Acknowledge last. Delete the rows, or set
processed_at, only after OpenSearch confirms the write. If the worker crashes before that point, the lock is released and another worker retries the rows.
Emit "reindex product 42" events, not row diffs. The worker re-reads the current committed state, joins prices, categories, and inventory, and builds the full document. Several queued events for the same entity collapse into one fetch.
This design adds read load on the primary. Reading from a replica moves that load off the primary, but replication lag can then index a version older than the event that triggered the work. Compare the replica row's version against the outbox version, and requeue the event if the replica is behind.
The engineering savings come from having one code path that builds documents. You don't have to write diff-merge logic for each table, and you don't have to handle partial updates arriving out of order. A full reindex calls the same builder.
-- Outbox: one row per 'reindex this entity' event, not a row diff.
CREATE TABLE search_outbox (
id bigserial PRIMARY KEY, -- PK index also serves ORDER BY id claims
entity_type text NOT NULL, -- e.g. 'product'
entity_id bigint NOT NULL,
version bigint NOT NULL, -- entity version at commit time
created_at timestamptz NOT NULL DEFAULT now(),
attempts int NOT NULL DEFAULT 0
);
-- Business transaction: the change and its outbox event commit or roll back together.
BEGIN;
WITH p AS (
UPDATE products
SET price_cents = 1999, version = version + 1
WHERE id = 42
RETURNING id, version
)
INSERT INTO search_outbox (entity_type, entity_id, version)
SELECT 'product', id, version FROM p;
COMMIT;
-- Worker: claim a batch. Concurrent workers skip rows locked by others.
BEGIN;
SELECT id, entity_type, entity_id, version
FROM search_outbox
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;
-- Application step (outside SQL): dedupe by (entity_type, entity_id), re-read
-- current state with joins, build full documents, send to OpenSearch, wait for ack.
-- Only after OpenSearch acknowledges: remove the acknowledged rows.
-- $1 is an array of claimed ids bound by the worker.
DELETE FROM search_outbox WHERE id = ANY($1);
COMMIT;
-- On failure or crash: ROLLBACK (or the dropped connection) releases the locks
-- and the rows become claimable again. Increment attempts in a separate
-- transaction, because the rollback would discard it.
The outbox table, a business update that writes its outbox event in the same transaction, and a worker that claims rows with FOR UPDATE SKIP LOCKED and deletes them only after OpenSearch acknowledges the write.
How do you make indexing idempotent and order-safe?
Every event needs two things to be safe to replay: a stable document identity and an ordering signal that OpenSearch can enforce.
Use the primary key as _id
Set the document _id to the row's primary key. Retries and replays then overwrite the same document instead of creating duplicates, so at-least-once delivery from the outbox becomes effectively-once in the index.
Version from the database, not the clock
Store a counter that increases with every write, such as a version column incremented by the same transaction or a value taken from a sequence. Wall-clock timestamps are a poor substitute. Two writes in the same millisecond collide, and clock adjustments can move time backwards. Put the counter in the outbox event and index with external versioning:
PUT /products/_doc/42?version=17&version_type=external
OpenSearch accepts the write only if 17 is greater than the stored version. If a delayed event carrying version 15 arrives later, OpenSearch rejects it instead of overwriting newer data.
Make deletes versioned too
Emit a tombstone event for hard deletes, or treat a soft-delete flag as a delete. In both cases, send the delete with the next version:
DELETE /products/_doc/42?version=18&version_type=external
OpenSearch keeps deleted-version information only for the index.gc_deletes window. After that window, a very late stale index event can bring the document back. Drift detection catches these cases.
Treat 409 as success
A version_conflict_engine_exception (HTTP 409) means a newer write is already indexed. Acknowledge the event and move on. Retrying it or sending it to the dead-letter table only adds noise.
import requests
OPENSEARCH = "" # placeholder endpoint
INDEX = "products"
def apply_event(event: dict) -> None:
"""Apply one outbox event idempotently. Raise only on real failures."""
# Primary key as _id: replays hit the same document, never a duplicate.
url = f"{OPENSEARCH}/{INDEX}/_doc/{event['id']}"
# Version comes from the DB row counter, not a timestamp.
params = {"version": event["version"], "version_type": "external"}
if event["op"] == "delete" or event["payload"].get("deleted"):
# Tombstone or soft-delete: a versioned delete, so an older
# index event that arrives late is rejected.
resp = requests.delete(url, params=params, timeout=5)
# 404 means the document is already gone, which is the goal.
if resp.status_code == 404:
return
else:
resp = requests.put(url, params=params, json=event["payload"], timeout=5)
if resp.status_code == 409:
# version_conflict_engine_exception: a newer version is already
# indexed. This event is stale, so acknowledge it. Do not retry it
# or dead-letter it.
return
# Other errors (5xx, mapping errors) go to the retry or dead-letter path.
resp.raise_for_status()
apply_event({"id": 42, "op": "delete", "version": 18, "payload": {}})
apply_event({"id": 42, "op": "upsert", "version": 17,
"payload": {"name": "Desk lamp", "price_cents": 3499}})
An outbox consumer that indexes and deletes with the primary key as _id and external versioning, and treats 409 version conflicts and 404 deletes as success.
Batching with the bulk API, error handling, and dead-letter tables
Pull a batch of outbox rows in commit order, convert each into an action/document pair, and send them in a single _bulk request. Cap batches by both row count and payload bytes. One large document can push a count-limited batch past the cluster's request size limit.
Read every item, not the status code
A _bulk call usually returns HTTP 200 even when some of its operations failed. The response carries a top-level errors flag and an items array in the same order as the request. Each item has its own status and, on failure, an error.type. If the worker only checks the HTTP status, it will mark failed rows as processed and the index will quietly fall behind the database.
The response-handling loop works like this:
- If the request itself failed (connection error, timeout, 5xx), mark nothing as done and retry the whole batch with backoff. Idempotent writes make resending safe.
- Otherwise, zip
itemswith the outbox rows and sort each item into one of four outcomes:- 2xx: mark the row done.
- 409: mark the row done. As covered above, a newer write is already indexed.
- 429 or 5xx: retryable. Leave the row pending, increment its attempt count, and back off before the next batch. A 429 means the cluster is shedding load, so a tight retry loop makes it worse.
- 4xx such as
mapper_parsing_exception: permanent. Retrying won't fix a document the mapping rejects.
Dead-letter permanent failures
Copy each permanent failure into a dead-letter table with the event ID, payload, error type, and reason. Mark the outbox row done in the same transaction. A single poison row then can't stall the queue behind it. Do the same for retryable rows that pass a maximum attempt count. Alert on the dead-letter table's size, and replay rows once the mapping or the data is fixed.
MAX_ATTEMPTS = 8
def handle_bulk_response(conn, rows, resp):
"""Apply per-item outcomes. Returns True if the caller should back off."""
# A transport failure (timeout, 5xx on the whole request) is handled by the
# caller: no row is marked done and the batch is resent. This function
# only runs on a parsed response, which is often HTTP 200 with failed items.
backoff = False
with conn.transaction():
# items[] matches the request order, so zip lines up each item with its row.
for row, item in zip(rows, resp["items"]):
result = next(iter(item.values())) # keyed by action: "index", "delete"
status = result["status"]
err = result.get("error") or {}
if 200 <= status < 300 or status == 409:
# Indexed, or a newer external version is already there.
mark_done(conn, row.id)
elif status == 429 or status >= 500:
# Retryable. The cluster may be shedding load, so slow down.
backoff = True
if row.attempts + 1 >= MAX_ATTEMPTS:
dead_letter(conn, row, err.get("type", "max_attempts"), err.get("reason"))
else:
conn.execute("UPDATE outbox SET attempts = attempts + 1 WHERE id = %s", (row.id,))
else:
# Permanent, e.g. mapper_parsing_exception. Retrying won't help.
dead_letter(conn, row, err.get("type"), err.get("reason"))
return backoff
def mark_done(conn, row_id):
conn.execute("UPDATE outbox SET processed_at = now() WHERE id = %s", (row_id,))
def dead_letter(conn, row, error_type, reason):
# Runs in the same transaction as mark_done, so a poison row is either
# recorded in the dead-letter table and cleared, or left untouched.
conn.execute(
"INSERT INTO outbox_dead_letter (event_id, payload, error_type, reason) VALUES (%s, %s, %s, %s)",
(row.event_id, row.payload, error_type, reason),
)
mark_done(conn, row.id)
A sketch of a bulk-response handler that sorts each item by status into done, retry, or dead-letter, and moves poison rows out of the queue in the same transaction that marks them processed.
How do you reindex OpenSearch with zero downtime?
Clients should only ever query an alias such as products, never a concrete index. That lets you build products_v8 next to the live products_v7 and switch over without downtime.
The procedure
- Create the new index with the updated mappings. During the load, set
refresh_intervalto-1and replicas to0to make it cheaper, then restore both settings before cutover. - Capture live changes. Either have the outbox worker write to both indexes, or record the current outbox position and replay from it after the backfill.
- Backfill with keyset pagination:
SELECT ... WHERE id > $last_id ORDER BY id LIMIT 1000. Each batch is an index seek. WithOFFSET, Postgres reads and discards every skipped row, so each batch gets slower than the last. - Verify by comparing document counts against Postgres, diffing sampled documents, and running a set of known queries with expected results.
- Swap atomically with a single
POST /_aliasesrequest that contains bothremove(v7) andadd(v8) actions. Keep v7 for a short period so you can roll back, then delete it.
Why versioning prevents lost updates
A backfill batch reads a snapshot of each row, and a live change can supersede that snapshot before the bulk write arrives. To handle this, send every write from both paths with version_type=external, using the row's monotonically increasing version column. OpenSearch rejects the stale write with a 409 conflict. Treat that 409 as success, because the newer document is already in the index.
Costs
Illustrative estimate (assumed numbers, not a benchmark):
- Backfill time = rows ÷ sustained docs/s = 50M ÷ 10,000 ≈ 83 minutes.
- Peak disk = primary size × (1 + replicas) × 2 = 200 GB × 2 × 2 = 800 GB.
The backfill also competes with live queries for CPU and I/O. Throttle the batch rate and watch search latency while it runs.
How do you detect drift and monitor the sync pipeline?
Even a correct pipeline drifts. A bug skips a row, an operator deletes a document by hand, or a restore rewinds the database. Periodic reconciliation catches this. Split the primary key space into fixed ranges, such as 10,000 IDs each. For each range, compare the row count and the maximum version in Postgres against a filtered count and a max aggregation in OpenSearch. When a range does not match, reindex only that range from the source of truth.
Reconciliation finds drift after it happens. It does not prevent it. Run it on a schedule, walk the ranges in small throttled steps, and avoid peak hours, so the checks do not compete with production queries.
Track these metrics continuously:
| Metric | What it tells you | Why it matters |
|---|---|---|
| Outbox backlog size | Rows waiting to be indexed | Shows whether the workers are keeping up with writes |
| Oldest unprocessed row age | How stale the worst case is | A stuck row can hide behind a small backlog |
| Replication slot lag | WAL retained for CDC | An unconsumed slot keeps WAL and can fill the disk |
| Bulk error rate | Per-item failures | Mapping or data bugs show up here first |
| End-to-end indexing latency | Commit-to-searchable time | This is the freshness users actually experience |
Some features need read-your-writes, such as showing an item right after the user saves it. For those, read from Postgres directly. Alternatively, pass refresh=wait_for on the write. Use this sparingly, because forcing refreshes reduces indexing throughput.
Together, these controls turn a vague report that "search is wrong" into an alert, a mismatched range, and a scoped reindex. Each incident then follows a routine runbook instead of requiring an investigation.
How does this design save engineering time?
The pipeline takes more work up front than a dual write. Most of the time it saves comes later, from work your team no longer has to do:
- One document builder. The live worker, the zero-downtime backfill, and the range reindex all call the same function that reads from Postgres and builds a document. A new field or a mapping change means editing one code path and running a rebuild. You don't maintain separate per-table diff handlers or a separate migration script.
- No manual cleanup after failures. Because writes are idempotent and version-checked, a crashed worker, a timed-out request, or a replayed batch needs no human attention. The rows are retried and the index converges. Engineers don't have to work out which updates were lost during a deploy or an OpenSearch outage.
- Ordering bugs disappear by construction. External versioning means you don't debug race conditions where an older document overwrote a newer one. OpenSearch rejects the stale write.
- Poison data is isolated, not escalated. The dead-letter table records the payload, error type, and reason for each permanent failure. Fixing a mapping bug becomes "fix, then replay these rows", instead of hunting for the record that stalled the queue.
- Schema changes don't need maintenance windows. The alias swap makes a reindex a routine task that can run during working hours and be rolled back by pointing the alias at the previous index.
- Incidents are scoped. Drift detection and the metrics above point to a specific range, backlog, or error type. On-call engineers start from a concrete signal and a runbook instead of reproducing a report that "search is wrong".
- Less infrastructure to own than CDC. In a monolith, the outbox needs a worker, a table, and vacuum tuning. You avoid running a CDC consumer, monitoring retained WAL on replication slots, and writing logic to reassemble multi-table documents from row-level events.
The trade-off is that every write path that touches indexed data must insert an outbox row. In a monolith you can enforce that in one place, for example in the repository or service layer that owns each entity. That is what makes the approach cheaper there than in a system with many independent writers.
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 sync postgres to opensearch, 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 route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
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.
- Consumers learn about a breaking change at review time rather than from production errors.
- 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.