Skip to content

PGSync ClickHouse

Real-time PostgreSQL → ClickHouse that keeps an analytics store in lock-step with your database, with no ETL.

Pricing Get started Query examples

Your database stays the source of truth. Every insert, update and delete streams into ClickHouse in real time off the Postgres write-ahead log, landing on ReplacingMergeTree so the latest row always wins and deletes tombstone cleanly. Built on PGSync; the same schema.json and CLI.

PGSync ClickHouse data flow PostgreSQL streams changes through PGSync ClickHouse into ClickHouse in real time. WAL changes upserts · tombstones PostgreSQL source of truth · WAL PGSync ClickHouse real-time CDC · exactly-once mirror · denormalize ClickHouse ReplacingMergeTree · <table>_live

Query live Postgres data with ClickHouse SQL

No ETL job, no nightly batch. A change committed in Postgres shows up in ClickHouse in real time, on a ReplacingMergeTree where the newest row wins and deletes disappear. Read the clean, deduplicated state straight from a companion <table>_live view:

SELECT count() FROM book_live;

Two modes

  • Mirror (free)

    One PostgreSQL table → one ClickHouse table, in real time.

    • Versioned upserts + tombstone deletes on ReplacingMergeTree
    • Full native type mapping (Decimal, DateTime64 UTC, arrays, …)
    • A companion <table>_live view: clean, deduplicated, no FINAL gymnastics
    • Exactly-once on replay (keyed on the source WAL LSN)

  • Denormalized (paid)

    A whole relational tree → one wide ClickHouse table.

    • One-to-one children flattened into typed columns
    • One-to-many children as native Array(Tuple(...)) (query with ARRAY JOIN)
    • Grandchildren fold recursively; a child change re-emits the parent
    • Per-table engine control (PARTITION BY / ORDER BY / TTL / settings)

What you get

  • Real-time, no ETL

    Changes stream off the Postgres WAL and land in ClickHouse continuously, with no batch jobs and no orchestration to babysit.

  • Correct by construction

    ReplacingMergeTree(_version, _is_deleted) keeps the latest row and tombstones deletes; a <table>_live view hides the mechanics.

  • Native denormalization

    Fold a relational tree into one wide table: flattened columns and native Array(Tuple(...)), queryable with ARRAY JOIN. No JSON wrangling.

  • Exactly-once on replay

    _version is the source WAL LSN, so a crash-replay can't duplicate rows or resurrect deletes.

  • Per-table engine control

    PARTITION BY / ORDER BY / TTL / settings per table, straight from your schema.json, to tune ClickHouse without leaving the config.

  • The PGSync you know

    Same schema.json, same CLI, same WAL/trigger change capture, with a ClickHouse sink instead of Elasticsearch.

Get started

pip install pgsync-clickhouse
pgsync-clickhouse -c examples/book.json --bootstrap   # slot + ClickHouse table
pgsync-clickhouse -c examples/book.json --daemon      # keep it live

# from your private index (token emailed on subscribe):
pip install pgsync-clickhouse-pro --extra-index-url https://TOKEN@HOST/simple/
pgsync-clickhouse -c examples/book_denormalized.json --bootstrap
pgsync-clickhouse -c examples/book_denormalized.json --daemon

The pgsync-clickhouse-pro package adds denormalized mode on top of the free base: same command, one wide table instead of a mirror.

Query ClickHouse

-- easiest: the companion _live view already applies FINAL + tombstone filter
SELECT title, isbn FROM book_live LIMIT 5;

-- denormalized: flattened one-to-one + native nested-array explode
SELECT title, publisher_name, a.name AS author
FROM book_wide FINAL ARRAY JOIN authors AS a
WHERE _is_deleted = 0;

Ready to keep ClickHouse in sync with Postgres?

See pricing PGSync Pro