ClickHouse中MaterializedPostgreSQL与PostgreSQL引擎区别及适用场景

两者核心区别是数据访问模式完全不同：前者是不存数据的联邦查询代理，后者是带实时同步能力的本地数据副本。

核心差异对比 #

• 定位与配置粒度不同
  PostgreSQL引擎：属于表引擎+表函数，使用时可以逐表配置长期映射关系，也可以通过表函数临时发起查询，不需要提前建库建表做预配置。
  MaterializedPostgreSQL引擎：属于库级引擎，只需要配置一次PostgreSQL连接信息、同步过滤规则，就会自动在ClickHouse侧创建同名数据库，拉取并同步库下所有符合规则的表，不需要逐张表手动建映射。
• 数据存储逻辑不同
  PostgreSQL引擎：本身不存储任何业务数据，ClickHouse侧仅保存连接配置、表结构元信息，所有查询都直接访问远端PostgreSQL。
  MaterializedPostgreSQL引擎：会在ClickHouse本地存储对应PostgreSQL库表的完整数据副本，首次启动时全量拉取存量数据，后续通过解析PostgreSQL的WAL日志做增量实时同步，数据持久化在ClickHouse本地磁盘上，要求PostgreSQL侧提前开启逻辑复制、配置合理的WAL保留周期，同步时会创建独立的replication slot，不影响PG本身业务运行。
• 查询执行链路与性能不同
  PostgreSQL引擎：所有查询请求都会实时下推到远端PostgreSQL执行，性能完全取决于远端PG的负载、网络延迟，无法用到ClickHouse本身的列式存储、向量化执行优势，跨表联查尤其是大表联查时开销极高，还会占用PG的运行资源。
  MaterializedPostgreSQL引擎：查询直接读取ClickHouse本地存储的数据，完全走ClickHouse原生执行引擎，查询性能和ClickHouse原生MergeTree表无本质差异，不会给远端PostgreSQL造成任何查询压力。
• 数据一致性与操作限制不同
  PostgreSQL引擎：读取的数据和远端PG实时一致，无同步延迟；支持直接在ClickHouse侧对映射表做写入、更新、删除操作，操作会直接作用在远端PG上；支持的SQL语法受PG侧语法限制，无法直接使用ClickHouse特有的函数、预聚合等能力。
  MaterializedPostgreSQL引擎：存在毫秒到秒级的同步延迟（取决于WAL解析速度、网络状况），支持自动同步PG侧的DDL变更（加减字段、修改字段类型等），链路中断后会自动从断点续传不丢数据；默认同步过来的表是只读状态，不支持直接在ClickHouse侧做写入、删改，可通过挂载物化视图、下游表的方式做二次加工，支持所有ClickHouse原生查询语法、函数、联查能力。

适用业务场景 #

• 适合使用PostgreSQL引擎的场景
  • 仅需偶尔查询PostgreSQL中的少量数据，不想做冗余数据存储，比如跨库做小数据量的维度表关联校验
  • 需要临时通过ClickHouse操作PG中的在线业务数据，比如小批量数据订正、临时数据探查
  • 对数据实时一致性要求极高，不能接受任何同步延迟，且查询频率低、数据量小，不会给PG造成额外负载压力
• 适合使用MaterializedPostgreSQL引擎的场景
  • 需要对PG中的业务数据做大规模OLAP分析，比如多表关联聚合、全量数据统计、用户行为分析等，这类查询如果直接在PG上运行会打满线上库负载
  • 需要把PG的业务数据和ClickHouse中存储的日志、埋点等其他数据源做联合分析，充分发挥ClickHouse的大数据查询性能优势
  • 需要实现业务库和分析库的负载隔离，把所有分析类请求全部路由到ClickHouse侧，降低线上PG的运行压力
  • 需要基于PG的业务数据做二次加工，比如在ClickHouse侧构建物化视图、做数据预聚合、对接BI报表系统

内容的提问来源于stack exchange，提问作者Hiep Tran