Creating a Resumable Postgres-to-ClickHouse ETL Pipeline

A ClickHouse backfill that writes into the live table has no rollback. How I rebuilt one around resumable extraction, a shadow load and an atomic swap.

Keeping the system of record in Postgres and serving analytics from a ClickHouse copy is a setup I see all the time. When that copy drifts, the fix is usually a full reload, and that's where the risk is. A ClickHouse backfill that writes straight into the live table has no rollback.

On a recent engagement I had built that pipeline myself earlier in the year, for routine daily loads. A full reload of about 1.5 billion rows asked more of it, so I rebuilt it. The second version doesn't touch the live table until the last statement, and on staging it ran the full reload with extraction roughly ten times faster.

The situation

This client ran exactly that setup. When I started this pass, its ClickHouse copy was about 3% off the source. Nearly a fifth of the live table's rows were stale duplicate versions that had never been cleaned up, and one whole source table had never been exported at all.

The goal was a full, correct reload, first on staging and then as a runbook the client's team could repeat in production. The pipeline I'd built for daily loads needed two things it didn't have:

• A way back. Daily loads went straight into the live table, insert-only into a ReplacingMergeTree that was never emptied. Stale versions had piled up, and a full reload had no rollback.
• Checkpoints. Each run loaded everything in one shot, so a failure meant starting over.

Three walls in the first full ClickHouse backfill run

Loading 1.5 billion rows into ClickHouse is the fast part: the final load took under an hour and a half. Extraction from Postgres was the slow part, and the second version's first full run on staging hit three walls in a day.

The host couldn't finish

On the small always-on host the run was supposed to use, extraction managed tens of millions of rows an hour, and the per-worker timeout killed every main shard partway through, twice. Waiting longer wouldn't have helped: that host could not have finished the job at all.

On a slow ETL job, my usual first move is to decouple the CPU-bound work from the I/O-bound work, so workers stop sitting idle while they wait on the database or object storage. It had worked on another engagement. This time I measured before reaching for it, and the numbers ruled it out:

Decoupling works by hiding I/O wait behind CPU work. These workers spent their time on CPU, so there was nothing to hide:

Sharding instead of rewriting

Making the extraction code itself cheaper, by vectorizing the per-row transform or building Arrow column by column, was out of scope for this engagement. So I went at it with parallelism instead. The source tables' existing ID columns could be split into contiguous ranges, which gave every worker its own shard without changing the data model, and a bigger throwaway worker pool ran all the shards at once.

A reusable Postgres-to-ClickHouse ETL pattern

It comes down to three moves, staging, squashing and swapping, and the same shape works for most Postgres-to-ClickHouse reloads:

• Throwaway workers next to the source. A larger worker pool in the source's network, started for the run and torn down afterwards, for a few dollars a run instead of permanently resizing the host. The client's DevOps engineer provisioned it to my sizing.
• Staging through object storage. One worker per shard reads its ID range from Postgres at the same time as the others, sixteen at once in the full run, and writes numbered Parquet chunks to object storage. ClickHouse loads from those chunks later, so reading the source and loading ClickHouse are separate steps.
• A pinned shard plan. Each source table's ID space is split into contiguous ranges with exact expected counts, written next to the data and never re-split on resume.
• Checkpoints and manifests. Each shard records its last ID after every chunk and writes a manifest when it finishes. A resumed run skips finished shards and continues the rest from their checkpoints, overwriting the same chunk keys, so however often a run restarts, it stages the same data with no gaps and no duplicates.
• A barrier. Nothing loads until every shard has its manifest.
• An emptied shadow, a bounded load, OPTIMIZE FINAL, an atomic swap. The shadow table is truncated before every attempt and filled by one memory-bounded load. OPTIMIZE FINAL then squashes the load's tens of thousands of parts before anything goes live, and EXCHANGE TABLES swaps the shadow with live in one statement, and the previous live table stays behind for rollback. The client's CTO hardened the swap in review: it verifies both tables by UUID, so an ambiguous disconnect is never blindly retried.

TRUNCATE TABLE analytics.facts_shadow;          -- every attempt starts from empty

INSERT INTO analytics.facts_shadow
SELECT * FROM s3('https://example-bucket.s3.amazonaws.com/runs/run-0001/*/*.parquet', 'Parquet')
SETTINGS max_insert_threads = 16, max_insert_block_size = 262144, optimize_on_insert = 0;

OPTIMIZE TABLE analytics.facts_shadow FINAL;    -- squash the load's parts before it goes live

EXCHANGE TABLES analytics.facts_shadow AND analytics.facts;   -- atomic; run again to roll back

TRUNCATE TABLE analytics.facts_shadow;          -- every attempt starts from empty

INSERT INTO analytics.facts_shadow
SELECT * FROM s3('https://example-bucket.s3.amazonaws.com/runs/run-0001/*/*.parquet', 'Parquet')
SETTINGS max_insert_threads = 16, max_insert_block_size = 262144, optimize_on_insert = 0;

OPTIMIZE TABLE analytics.facts_shadow FINAL;    -- squash the load's parts before it goes live

EXCHANGE TABLES analytics.facts_shadow AND analytics.facts;   -- atomic; run again to roll back

Results

The transform code didn't get faster. The extraction speedup came from sharding across eight times the cores, used on demand. The run finished from its checkpoints after two interruptions without re-extracting a finished shard, and rows that arrived mid-run were counted and left for the production cutover. The live table wasn't touched until the swap.

What to check on your own backfill

• Measure before you parallelise. A CPU-bound job and an I/O-bound job need opposite fixes.
• Load into an empty shadow, never the live table. A failed attempt then costs nothing, and rollback is one statement.
• Benchmark the full load, not a slice. A narrow-slice benchmark ran twice as fast as the full load could.

When this is overkill: for small tables or additive changes, a metadata-only ADD COLUMN or a plain INSERT … SELECT is enough. If anything else writes to the live table during the copy, EXCHANGE TABLES drops those rows unless you pause writers or replay the gap. This swap was safe because only the batch wrote to that table. And one EXCHANGE can't ship a change that spans several tables, grants and dictionaries at once.

Related

• ClickHouse & real-time data services: migrations, backfills and full moves into ClickHouse.
• ClickHouse Migrations Without Transactions (ch-migrate 0.6): the open-source migration tool I maintain, and why every statement has to be safe to run twice.
• Migrating 1.3 Billion IoT Rows to ClickHouse Cloud: a different client and a different move, from SQL Server.