在實際數據平台架構中，ClickHouse 通常不是唯一的資料庫，而是與其他資料源（如 MySQL、PostgreSQL、S3、Kafka 等）整合，扮演 高效查詢與分析層 的角色。

為什麼要整合 PostgreSQL？

在許多應用場景中，PostgreSQL 作為 OLTP 系統 儲存業務資料（如交易、用戶、訂單），但在報表分析時遇到以下挑戰：

• OLTP 查詢性能無法滿足大量聚合分析
• 避免 ETL 搬運延遲帶來的資料不一致
• 不想複製全量資料，只需要即時查詢部分資料

ClickHouse 的 PostgreSQL Table Engine 可直接連線 PostgreSQL，並以類似「外部表」的方式查詢資料，適合快速整合多方資料源。

PostgreSQL Table Engine — 即時雙向查詢與插入

適用情境

• 不需全量同步，只想即時查詢 PostgreSQL 資料
• 需要在 ClickHouse 直接插入資料回 PostgreSQL
• 資料量相對較小、即時性需求高

PostgreSQL 端設定

1. 允許網路連線

   # postgresql.conf
   listen_addresses = '*'
   

2. 建立使用者

   CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
   

3. 建立資料庫與表

   CREATE DATABASE db_in_psg;
   
   CREATE TABLE table1 (
       id integer primary key,
       column1 varchar(10)
   );
   
   INSERT INTO table1 VALUES (1, 'abc'), (2, 'def');
   

4. 設定連線權限

   # pg_hba.conf
   host    db_in_psg  clickhouse_user  192.168.1.0/24  password
   

5. 重新載入設定

   pg_ctl reload
   

ClickHouse 端設定

1. 建立資料庫

   CREATE DATABASE db_in_ch;
   

2. 建立連線表

   CREATE TABLE db_in_ch.table1
   (
       id UInt64,
       column1 String
   )
   ENGINE = PostgreSQL(
       'postgres-host.domain.com:5432',
       'db_in_psg',
       'table1',
       'clickhouse_user',
       'ClickHouse_123'
   );
   

3. 測試查詢

   SELECT * FROM db_in_ch.table1;
   

4. 雙向測試

   • 在 PostgreSQL 新增資料，ClickHouse 查得到
   • 在 ClickHouse 新增資料，PostgreSQL 查得到

MaterializedPostgreSQL Database Engine — 持續資料同步（CDC）

適用情境

• 需要將 PostgreSQL 整個資料庫或多個表持續同步到 ClickHouse
• 資料更新頻率高
• 適合報表與即時分析

注意事項

• 實驗功能，需啟用設定
• 不支援 ClickHouse 直接修改同步表（避免與 CDC 衝突）
• 適合用於 只讀分析 場景

PostgreSQL 端設定

1. 開啟複製功能

   # postgresql.conf
   listen_addresses = '*'
   max_replication_slots = 10
   wal_level = logical
   

2. 建立使用者與資料庫

   CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
   CREATE DATABASE db1;
   

3. 建立表與資料

   \connect db1
   CREATE TABLE table1 (
       id integer primary key,
       column1 varchar(10)
   );
   INSERT INTO table1 VALUES (1, 'abc'), (2, 'def');
   

4. 設定權限

   # pg_hba.conf
   host    db1  clickhouse_user  192.168.1.0/24  password
   

ClickHouse 端設定

1. 啟用實驗功能

   SET allow_experimental_database_materialized_postgresql=1;
   

2. 建立同步資料庫

   CREATE DATABASE db1_postgres
   ENGINE = MaterializedPostgreSQL(
       'postgres-host.domain.com:5432',
       'db1',
       'clickhouse_user',
       'ClickHouse_123'
   )
   SETTINGS materialized_postgresql_tables_list = 'table1';
   

3. 驗證資料

   SELECT * FROM db1_postgres.table1;
   

4. 測試同步
   在 PostgreSQL 新增資料，ClickHouse 會自動更新。

選擇策略建議

運作機制與限制

優點

• 即時查詢 PostgreSQL，不需先 ETL
• 可與 ClickHouse 原生表 Join
• 適合低延遲資料整合需求

限制

• 查詢效能受限於 PostgreSQL 回應速度
• 大量資料掃描時延遲較高
• 適合即時查詢小批量資料，不適合全量大表分析
  （建議使用 clickhouse-copier 或 ETL 工具將歷史資料導入 ClickHouse）

總結

透過 PostgreSQL Table Engine，ClickHouse 可以直接訪問 PostgreSQL 的即時資料，實現跨系統分析，特別適合混合查詢與即時 BI 報表需求。

在實務中，建議：

• 大表做 ETL 導入 ClickHouse
• 小表 / 最新資料透過外部表查詢
• 結合 Materialized View 進行即時彙總