Question 1

How do I replicate Postgres into ClickHouse?

Accepted Answer

Connect both, choose your tables, set a schedule. Polytomic does a first full load then follows PostgreSQL logical replication. Postgres needs a publication covering those tables, a replication slot and wal_level set to logical, with a user carrying the REPLICATION attribute or rds_replication on RDS. ClickHouse needs the native protocol endpoint, normally port 9440 with TLS, plus staging storage the server itself can read.

Question 2

How does an update work if ClickHouse cannot UPDATE rows?

Accepted Answer

By writing a newer version of the row rather than modifying the old one. Keyed tables are created as ReplacingMergeTree with a version column, so an updated record is simply inserted again and ClickHouse resolves which version wins. The consequence worth planning for is that deduplication happens lazily in the background, so a plain SELECT can briefly return more than one version of a row. Query with FINAL, or run OPTIMIZE TABLE with FINAL, when you need the settled state.

Question 3

Do Postgres deletes reach ClickHouse?

Accepted Answer

Yes, through the same versioning mechanism. Polytomic compares the incoming keys against the current state of the target and inserts tombstone versions carrying a deleted at timestamp, which ReplacingMergeTree then treats as authoritative. If you would rather the rows physically disappear, hard delete first forces a merge with OPTIMIZE FINAL and then runs an ALTER TABLE DELETE mutation synchronously. Note that the fallback read paths, a tracking column or an xmin read, do not carry deletes at all.

Question 4

Are the tables partitioned?

Accepted Answer

No. Polytomic sets an ORDER BY key on the identity column but does not emit a PARTITION BY clause, so created tables are unpartitioned and there is no automatic date or cardinality based key selection. On a large event style table you will usually want to add partitioning yourself after the table exists, chosen around how you actually query it, since ClickHouse rewards getting that right far more than most engines.

Question 5

Why move Postgres analytics into ClickHouse?

Accepted Answer

Usually because the queries outgrew the database. Teams describe running analytics on Postgres until scan volumes make it painful, then standing ClickHouse up for event and product data. ETL into a data warehouse lets both run side by side during that shift, with application tables landing in ClickHouse while Postgres keeps serving the product, rather than forcing a single cutover weekend.

Sync PostgreSQL to ClickHouse

Sync PostgreSQL to ClickHouse with Polytomic

ReplacingMergeTree targets

Deletes carried as tombstones

Built for the move

Or sync ClickHouse to PostgreSQL

Trusted by businesses worldwide

Step-by-step integration guide

Connect PostgreSQL in Polytomic

Connect ClickHouse in Polytomic

Create a sync between PostgreSQL and ClickHouse

Map fields and enable the sync

Frequently asked questions

The sync and ETL platform that does it all

Powerful data sync

Advanced analytics

Enterprise security

Easy setup

Ready to connect PostgreSQL with ClickHouse?