Elasticsearch to PostgreSQL: How to Move Your Data

Moving data from Elasticsearch to PostgreSQL takes documents out of a search index and puts them into a relational database, usually so an application, an internal tool, or a reporting layer that speaks SQL can work with them. Airbyte does this without code. Before you plan a schedule, one property of the source shapes everything: it supports full refresh only, with no incremental sync and no cursor field.

Each run reads the whole index. On a modest reference index that is unremarkable. On a large or growing one it becomes the dominant cost of the pipeline.

Working without a cursor

The Elasticsearch source lists full refresh sync as supported and incremental sync as not supported. There is nothing to configure, because the capability does not exist.

Use full refresh overwrite. Append would add a complete second copy of the index to your Postgres table on every run, which is almost never wanted. Overwrite gives you a table that matches the index as of the last sync, which is a clear and defensible contract to give downstream consumers.

Two mitigations are worth considering if the volume is uncomfortable. If your index naming rolls daily or monthly, select only the recent indices rather than the whole set, which approximates incremental movement through configuration. And if you control the writing application, writing to both Elasticsearch and Postgres at source is more efficient than repeatedly scanning the index.

Bear in mind that Elasticsearch calls may be rate limited depending on the deployment, and repeated full scans are a heavy read pattern against a cluster that is probably also serving production search.

Why nothing arrives as a clean type

Elasticsearch has no dedicated array type. Any field can hold zero or more values as long as they share a type, so every field is potentially an array, and the connector's type map is honest about it.

A long maps to integer or array, a double to number or array, and keyword and text to a union of string, array, number, and integer. Elasticsearch date fields map to string or array rather than to a temporal type, and nested fields map to object or string.

This matters more with Postgres than with a warehouse, because Postgres is strict about types and applications reading these tables expect real columns. Union-typed and nested values land as JSON, which Postgres queries capably with its JSON operators, but which will not behave like typed columns in an ORM or a BI tool.

So plan a view or dbt model that casts the fields you actually use into real Postgres types, and point applications at that rather than at the synced table. Dates deserve particular attention, since they arrive as strings and will not sort or filter correctly until cast.

Setting up both ends

The source needs an Elasticsearch endpoint URL and, optionally, credentials depending on how the cluster is secured. It then discovers every index in the domain automatically and presents them for selection.

Select deliberately. System and internal indices appear in that list alongside yours, and each index you enable is a full scan on every run. This is a Marketplace-tier connector with a sync success rate Airbyte reports as low, though its usage rate is high.

On the destination side, the Postgres connector writes each stream to a table in your target schema with Airbyte's metadata columns alongside your fields. It is Airbyte-supported with a high reported sync success rate, so most of your attention belongs on the source side and on the model you build over the result.

One thing to plan for with full refresh overwrite against a large index: the table is replaced on each run, so any index you created on it needs recreating, and long-running queries against it during a sync will see the table change beneath them. If the table serves an application, consider syncing to a staging table and swapping.

Frequently asked questions

Can I sync only documents that changed?

No. The source supports full refresh only and provides no cursor. Selecting only recent date-rolled indices is the closest configuration-level workaround.

Why are my date fields text in Postgres?

Elasticsearch date fields map to string or array rather than to a temporal type. Cast them in a view or model before sorting or filtering on them.

Should my application read the synced table directly?

Better not to. Full refresh overwrite replaces the table on each run, and the union types need casting. Point applications at a view or a swapped staging table instead.

Will syncing affect my search cluster?

It can. Elasticsearch calls may be rate limited by your deployment, and a repeated full scan is a heavy read. Test off-peak or against a replica before scheduling frequently.

Get your Elasticsearch data into PostgreSQL

Use full refresh overwrite, select indices deliberately, cast the union types in a view, and keep applications off the synced table. For the same source into a warehouse, see our guide to Elasticsearch to BigQuery. For another document store into Postgres, see MongoDB to PostgreSQL.

Start syncing now →