How to sync PostgreSQL to OpenSearch in real time — without dual-writes

A customer searches your app and clicks an order that was deleted an hour ago. The row is long gone from PostgreSQL; the document is still sitting in OpenSearch. If application code is responsible for writing to both stores, reaching this state is a question of when, not whether. It is the bug that made us build VentStream, so before the guide, a short account of why the hand-rolled version keeps producing it:

• No transaction spans PostgreSQL and a search cluster. However careful the code, some failure eventually lands between the two writes, and from that moment the index disagrees with the table until something happens to notice.
• Retries and deletes surface it first. A retried handler indexes the same change twice, and removal logic has a habit of living only on the happy path.
• The repair job becomes its own liability: a backfill script written during an incident encodes assumptions the live write path has already outgrown.
• Keeping the index honest is not something you build once. Every new service or worker that touches the table inherits the duty to get the sync right again.

The guide: Postgres to OpenSearch in four steps

PostgreSQL exposes that ledger through logical replication. VentStream, a single Apache-2.0 binary written in Rust, reads it and keeps an OpenSearch index current. Four steps take you end to end:

1. Publish the table

Logical replication starts with a publication: the database’s way of declaring which tables are allowed to leave. It is also the only change you make on the Postgres side:

CREATE PUBLICATION vs_pub FOR TABLE orders;

2. Write the config

One file describes the whole pipeline. This is it, complete and exactly as we ran it against a live cluster:

schema_version: 1
roles: [cdc]

source:
  kind: postgres
  postgres:
    host_ref: env:VS_PG_HOST
    user_ref: env:VS_PG_USER
    password_ref: env:VS_PG_PASSWORD
    database_ref: env:VS_PG_DATABASE
    publication_ref: env:VS_PG_PUBLICATION
    slot_ref: env:VS_PG_SLOT
    bootstrap:
      mode: snapshot

sink:
  kind: opensearch
  opensearch:
    endpoint_ref: env:VS_OS_ENDPOINT
    index_routing:
      strategy: fixed
      name: orders

Auth is optional, which is why the block above works against an open dev cluster as written. Secured clusters add an auth block: mode: basic with username_ref and password_ref, or mode: api_key with api_key_ref, plus TLS verification for https endpoints. The sink docs walk through the options.

Running Elasticsearch instead? Same sink. Set kind: elasticsearch and everything else carries over.

3. Set the secrets

Notice that the config holds no credentials, which is what makes it safe to commit. At startup the engine resolves each env: reference from its own environment; where those variables come from is a deployment decision. A Kubernetes Secret works, so do systemd Environment= lines. For local runs, keep a plain env file beside ventstream.yaml and have the shell export it before launching, since the engine reads nothing but environment variables:

# .env — the values the config references
VS_PG_HOST=db.internal
VS_PG_USER=ventstream
VS_PG_PASSWORD=…
VS_PG_DATABASE=shop
VS_PG_PUBLICATION=vs_pub
VS_PG_SLOT=vs_slot
VS_OS_ENDPOINT=https://search.internal:9200

4. Install and run

The install script supports macOS and Linux. On Windows, reach for WSL2 or the container image.

curl -fsSL https://ventstream.dev/install.sh | sh

set -a && source ./.env && set +a
VS_ENGINE_CONFIG=./ventstream.yaml ventstream

At this point the pipeline is live. The engine can also compose joined documents, folding an order’s line items into the order itself, but this walkthrough stays with a single table.

What you see

First the engine snapshots what the table already contains, walking it in keyset-paginated chunks. When the snapshot finishes, it switches to tailing the WAL at the precise watermark where the snapshot stopped. Bootstrap and live tail produce identical document shapes and ids, so there is no separate initial-load script to keep consistent with the live path. From then on, a committed change is searchable within milliseconds to seconds, governed mostly by the index refresh interval (OpenSearch refreshes near-real-time, about once a second by default). Two details stand out in the cluster itself: the index is named exactly what you configured, orders, with no prefix, and each document’s _id is the canonical id itself, so GET /orders/_doc/public.orders:["42"]fetches a row’s document directly.

Why it stays correct

What actually keeps the index in lockstep with the table:

• The _id is a pure function of the primary key, canonical form public.orders:["42"]. Emit the same change twice and the second write lands on the first; duplication has nowhere to come from.
• Every change resolves to the right document. Updates upsert in place, deletes target the id they were derived from, and an UPDATE that changes the primary key removes the old document before writing the new one.
• Each bulk write carries an external version taken from the source watermark, so a stale or replayed write loses to a newer document instead of clobbering it. The engine’s cursor moves only after the bulk response confirms durability, which means a crash or restart resumes from the last confirmed write.

How fast is it?

Throughput depends on the configuration and the resources you hand the engine; the realistic range runs from thousands of events per second up to tens of thousands. In our container-matrix benchmark, PostgreSQL into OpenSearch sustained 58k events/s on 2 vCPUs and 1 GiB of memory, and a run only counts as passing after its exact document counts check out.

Once a flat row per document stops being enough, say orders that should carry their line items, a joins spec tells the engine how to compose parent and child rows into one document. That topic earns its own post, and it is the next one in this series.

Why VentStream

What you end up with, in practical terms:

• Reliable sync and live tailing without the overhead. One binary and one config file cover the snapshot, the tail, and recovery — there is no separate streaming stack to stand up and babysit.
• Visibility into your pipelines. Health, convergence, and throughput are first-class: a local health endpoint and metrics in standalone mode, and full per-pipeline status from the CLI and dashboard when attached to VentStream Cloud.
• Light on the source and the sink.The snapshot reads in keyset-paginated chunks, the tail is a single replication consumer, and writes go out in bounded, backpressured batches — in our benchmarks the engine held around 10–14% of one core.
• A CLI for the whole lifecycle. ventstreamctl creates pipelines, versions their configuration, and handles pause, resume, drain, and reconcile without touching the deployment.

Run it wherever you like

Everything above runs on the open-source engine alone, on any machine that can start a binary. When you want configuration, health, and operations handled centrally across a fleet of agents, the identical binary connects to VentStream Cloud with one agent key, and the data path stays exactly where it was.

The code lives on GitHub, the docs quickstart takes you from zero, and a dashboard account gets your first agent enrolled.