3

why we need this? since the docs say:

MaterializedPostgreSQL Creates a ClickHouse database with tables from PostgreSQL database.

PostgreSQL Allows to connect to databases on a remote PostgreSQL server.

I cannot see clearly the difference between them?

when to use each other?

Hiep Tran
  • 3,735
  • 1
  • 21
  • 29

1 Answers1

3

MaterializedPostgreSQL will use replication slots and will physically replicate data from PostgreSQL to ClickHouse

PostgreSQL it's just a proxy table engine. When you try SELECT FROM postgreqsql_table_engine then your clickhouse query will rewrite. GROUP BY, ORDER BY and HAVING BY sections will remove and query just proxy to remote PostgreSQL server, after it GROUP BY ORDER BY and HAVING BY clause will apply on clickhouse side.

Slach
  • 1,672
  • 13
  • 21
  • It boils down to where the final data resides. Materialized copies to ClickHouse, the table engine leaves the data on PostgreSQL. If you want ClickHouse performance on the data and are willing to pay the cost of replication, you want Materialized. If there is a small amount of data that you just want to join with ClickHouse data you can leave it on Postgres and use the table engine. – Geoff Genz Jul 11 '22 at 14:23
  • Also note that the MaterializedPostgres engine is experimental, the table engine is not. – Geoff Genz Jul 11 '22 at 14:23
  • that nice, I have second question, how about the performance? is that MaterializedPostgreSQL faster PostgreSQL Engine? MaterializedPostgreSQL as fast as Clickhouse since data is on Clickhouse and PostgreSQL is slower than Clickhouse? – Hiep Tran Jul 12 '22 at 07:40
  • Yes, you right, MaterializedPostgreSQL much faster than PostgreSQL but require disk space on clickhouse server – Slach Jul 13 '22 at 05:25