what is difference between MaterializedPostgreSQL Engine and PostgreSQL Engine in Clickhouse?

Question

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?

Answer 1

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.