DEV Community

Bashiru Bukari
Bashiru Bukari

Posted on

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

#postgres #meilisearch #cdc #opensource

Originally published on the VentStream blog.

If you run PostgreSQL and want Meilisearch to stay in step with it, the usual first attempt is writing to both from application code. Two writes, no shared transaction — and a set of headaches that never really goes away:

  • Partial-failure drift — a crash between the two writes leaves the index lying about the database.
  • Retry duplicates — replaying the code path writes the same document twice.
  • Forgotten deletes — a row disappears from Postgres and lives on in search forever.
  • Backfill scripts — a one-off "initial load" path that behaves differently from the live one and drifts.
  • Sync logic everywhere — every new code path that touches the table has to remember to touch the index.

The database already has the truth and a change feed — use it.

The guide: Postgres to Meilisearch in four steps

The flow is: PostgreSQL (WAL) → publication → VentStream → Meilisearch. VentStream is an open-source (Apache-2.0) Rust engine that attaches to Postgres logical replication and materializes documents into the index.

1. Publish the table

The table owner decides what leaves the database, in the database:

CREATE PUBLICATION vs_pub FOR TABLE orders;
Enter fullscreen mode Exit fullscreen mode

2. Write the config

This is the entire file. Secrets are environment references, never inline values:

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: meilisearch
  meilisearch:
    endpoint_ref: env:VS_MEILI_ENDPOINT
    api_key_ref: env:VS_MEILI_KEY
    index_routing:
      mode: fixed
      index: orders
Enter fullscreen mode Exit fullscreen mode

That is the whole setup. The engine also supports table joins — composing an order with its line items into one document — but we're keeping this one simple.

3. Set the secrets

Every env: reference in the config resolves from the engine's environment at startup, so the file itself stays safe to commit. Put the values wherever you normally keep secrets — an env file, systemd Environment= directives, or a Kubernetes Secret. Locally, an env file next to your ventstream.yaml does fine — the shell exports it before the engine starts; the engine itself only reads 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_MEILI_ENDPOINT=https://search.internal:7700
VS_MEILI_KEY=…
Enter fullscreen mode Exit fullscreen mode

4. Install and run

The installer covers macOS and Linux. On Windows, use 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
Enter fullscreen mode Exit fullscreen mode

What you see

The engine snapshot-bootstraps your existing rows in keyset-paginated chunks, then tails the WAL from the exact watermark where the snapshot ended — same document shapes, same ids, one continuous motion. There is no "initial load script" that behaves differently from the live path. Once it is tailing, live changes become searchable within milliseconds to seconds of the commit, depending on the sink's ingestion speed. One detail to know: the sink namespaces index names, so index: orders shows up in Meilisearch as vs_orders (the prefix is configurable).

Why it stays correct

The short version of how VentStream keeps the index and the table in lockstep:

  • Every document gets a deterministic id derived from the primary key — canonical form public.orders:["42"], carried as _vs_id; the Meilisearch primary key _vs_pk is its URL-safe encoding. Re-emits overwrite, never duplicate.
  • Updates upsert in place. Deletes always find their target. Even a primary-key UPDATE removes the old document and writes the new one.
  • Writes are Meilisearch task-confirmed in FIFO order, and the engine's cursor only advances after the sink confirms durability — so after a crash or restart, the engine resumes from the last confirmed write rather than skipping or replaying events.

How fast is it?

Depending on the configuration and the resources you give it, the engine sustains from thousands up to tens of thousands of events per second. Our verified benchmarks reached roughly 930k documents per minute bootstrapping 4 million rows into Meilisearch on a laptop (M3 Max, single node, exact-count-verified), with the engine around 10–14% of one core. Meilisearch's own ingestion is usually the floor, not the engine.

When flat rows stop being enough — say you want each order indexed with its line items — VentStream can compose parent and child rows into one document with a joins spec. That deserves its own walkthrough; we'll cover it in a follow-up post.

Run it wherever you like

The engine is open source and runs anywhere a binary runs. When you want managed configuration, health, and operations across a fleet, the same binary attaches to VentStream Cloud with a single agent key — no key, and it never talks to the platform at all.

Questions or a source/sink you'd like to see supported? Open an issue — I read all of them.

Top comments (0)