PostgreSQL to Elasticsearch: How to Move Your Data

Syncing PostgreSQL to Elasticsearch turns relational rows into searchable documents, which is how most teams add full-text search, faceted filtering, or a search-driven interface on top of data that already lives in a database. Airbyte builds the pipeline without code. Before you start, one constraint decides whether this route is open to you at all: the Elasticsearch destination is available on Airbyte Core and PyAirbyte, but not on the Standard, Plus, Pro, or Enterprise Flex plans.

If you are on a paid Cloud tier, this connector will not appear in your catalogue, and everything below assumes a self-managed deployment.

What happens to a row when it becomes a document?

Each stream becomes an index, each row becomes a document, and each column becomes a field. That much is straightforward. The interesting part is what Postgres holds that a search index handles differently.

Relational schemas normalise. The thing a user wants to search for is frequently spread across three tables joined by foreign keys, and Elasticsearch does not join. Syncing your tables one to one produces indices that mirror your database and serve search poorly, because a query for a product by its category name has nowhere to look.

The usual answer is to denormalise before you sync. Create a view in Postgres that joins the tables into the shape a search result should have, and point Airbyte at that view rather than at the base tables. You get one index containing everything a search needs, and the join logic lives in SQL where it is easy to change.

The trade-off is that a view is read with a cursor rather than through change data capture, so you give up delete propagation in exchange for the right document shape. Which matters more depends on whether stale documents in your search index are an inconvenience or a correctness problem.

Define the index mapping before your first sync

The Elasticsearch destination maps Airbyte types onto field types fairly coarsely: text to string, date to date-time, object to object, array to array, boolean to boolean, and both integer and number onto numeric types.

Postgres types are more precise than that. NUMERIC carries explicit precision and scale, and integer widths are distinct. Those distinctions flatten on the way in, so verify what lands before trusting an aggregation over the index.

The larger risk is dynamic mapping. Left to infer, Elasticsearch guesses the index mapping from the first documents it sees, and a field that is null early and populated later produces a mapping that either rejects subsequent documents or silently coerces them. Define it explicitly up front. This matters even more here than with other sources, because search relevance depends on analysers and field types you would never get from inference: a product name should be analysed text, while a SKU should be an unanalysed keyword, and only you know which is which.

Handling updates without a deduped sync mode

The destination supports full refresh overwrite, full refresh append, and incremental append. Neither deduped mode is available, which matters immediately for a database source where rows change.

The mechanism that compensates is the Upsert Records option. With it enabled, the connector indexes using each record's identifier, so an updated row replaces its existing document rather than adding a second copy. For a Postgres source this is essentially mandatory: without it, editing one row leaves two documents in the index and your search returns both. Set it during initial configuration, because switching it later changes what a re-sync does to an index that already holds data.

Deletes are the remaining gap. If you use CDC on the Postgres side, a delete is captured and arrives as a record, but the destination has no mechanism to remove the corresponding document. Plan a periodic full refresh overwrite, or handle tombstones in your application's query layer.

Setting up both ends

For CDC on the source, set wal_level to logical, create a replication slot using pgoutput, create a publication covering your tables, and grant the Airbyte user the REPLICATION attribute plus SELECT. Alert on replication slot lag from day one: a slot retains write-ahead log segments until read, so a paused or failing connection accumulates disk on your primary until the database stops accepting writes.

On the destination you need Elasticsearch 7.x or above and an endpoint URL. Authentication is optional, but where used the account needs permission to create an index if one does not exist and to create documents. Basic auth and API key ID with secret are supported, along with a CA certificate. Both connectors support SSH tunnelling to a bastion host, which is common when the database and the search cluster both sit on private networks, and on each the SSH login username is the operating system user on the bastion rather than a database or Elasticsearch account.

Frequently asked questions

Can I run this on a paid Airbyte Cloud plan?

The Elasticsearch destination shows as available on Core and PyAirbyte only, not on Standard, Plus, Pro, or Enterprise Flex. Plan on a self-managed deployment.

Why do I get duplicate documents when a row is updated?

There is no deduped sync mode. Enable Upsert Records so the connector replaces documents by identifier rather than appending new ones.

Should I sync tables directly or use a view?

For search, usually a view. Elasticsearch cannot join, so denormalise in Postgres into the shape a search result needs. The cost is losing CDC, since views are read with a cursor.

Do deleted rows disappear from the index?

No. CDC captures the delete but the destination has no way to remove the document. Use a periodic full refresh overwrite, or filter tombstones in your query layer.

Get your PostgreSQL data into Elasticsearch

Denormalise into a view, define the mapping before the first sync, and enable upserts so updates replace rather than duplicate. For the reverse direction, see our guide to Elasticsearch to ClickHouse. For indexing from a different source, see n8n to Elasticsearch.

Start syncing now →