流式数据同步：一种PostgreSQL到ClickHouse的高效数据同步方案

最新推荐文章于 2025-06-30 14:49:47 发布

longqinx

最新推荐文章于 2025-06-30 14:49:47 发布

阅读量1.8k

点赞数 23

CC 4.0 BY-SA版权

文章标签： postgresql clickhouse 数据库 java 性能优化

本文链接：https://blog.csdn.net/Poker_Facex/article/details/139220265

背景

PostgreSQL和MySQL一样，是一种关系型数据库，多用于存储业务数据，且数据以记录（Record）为单位，采用行式存储，比较适合OLTP的场景；而ClickHouse则是一种强大的列式数据库，同列的数据通常被存储在一起，比较适合OLAP的场景。

在很多项目中，我们会同时用到PostgreSQL和ClickHouse，一个侧重业务，一个侧重数据分析。而难以避免的是我们可能需要将PostgreSQL中存储的一些业务数据同步到ClickHouse中，以便可以使用ClickHouse进行分析或为ClickHouse提供支持分析的一些数据。

比如我们可能需要分析应用日志，而日志可能在某些字段上有所缺失，此时就可能需要通过业务数据来进行日志补全操作。

或者我们的业务会产生大量的数据，如大量的用户订单或交易数据，此时我们准备对这些历史数据进行离线分析，试图从中找出一些模式或规律。这时虽然可以使用PostgreSQL来低效率强行完成这个工作，但毕竟“没有金刚钻”就不应该“揽瓷器活”，万一把业务数据库跑崩了…此时通常会将专业的事情交给专业的工具来做，不可避免地，又需要数据迁移/同步了。

当然，以上只是简单举例，实际项目中的架构也并非这么简单，可能会有专业的大数据一条龙，解决采集、清洗、补全、存储等等各阶段的问题。本文重在讨论需要从PostgreSQL业务数据库同步数据到ClickHouse分析数据库的场景。

PostgreSQL到ClickHouse之间的数据同步有很多方案，网上的介绍也比较多，比如使用ClickHouse的数据库引擎功能，可以连接到PostgreSQL，甚至基于此执行insert into ... select from ... 进行数据同步，具体参见 PostgreSQL | ClickHouse Docs；此外，还可以将PostgreSQL的数据导出到文件，然后通过文件导入到ClickHouse（应该没人这么用吧…），总之方法比较多，各有优势。

本文侧重描述分页查询+批量插入同步和基于流的数据同步两种方案，文中以CK表示ClickHouse，以PG表示PostgreSQL

传统的分页查询和批量插入

为何用这种方式？

可能不少读者会想，既然上文提到CK都提供了数据库引擎，可以让我们在CK中将PG当作“自家兄弟”一样SELECT和INSERT实现数据交换，那何苦又去分页查询然后再批量插入呢？况且分页查询在数据量大的时候还面临深分页问题，这不明摆着降低效率？

究其原因还是因为业务需要！虽然CK的数据库引擎看起来很诱人，但也存在一些问题：

使用CK的数据库引擎需要在CK中创建DATABASE连接到PG，此过程还需要PG的账户密码，存在维护问题
较难动态指定不同PG作为源和不同CK作为目的，即对于动态数据源的支持较差
使用这种不经过代码逻辑的数据交换过程，无法在数据传输过程中对数据进行一些变换，如无法执行字段值映射、翻译等中间转换过程

当然可能远不止上述问题，以上三点是笔者实际遇到的主要问题

因此，第一版数据同步便慢慢成型…

逻辑分析

分页查询+批量插入SQL可能是十分容易想到的解决方案，逻辑也十分简单：

触发同步
根据业务选择源PG库
分页查询需要同步的数据，指定每页查5000条数据
遍历查询到的数据，按指定逻辑进行转换
将转换后的数据批量插入到目标CK库
重复3~5，直到数据同步完毕

缺点分析

有潜在的深分页问题
每页查询出来的数据都需要到JVM内存中过一遍，可能会给新生代GC上强度
查询出来的数据一般是反序列化成Java对象后存在内存中，反序列化本身也会浪费不少性能
使用Mybatis等ORM框架进行批量插入的性能可能不如原生JDBC的批量操作，但换成原生JDBC批量操作也是治标不治本
如果排序字段选择有问题，分页查询可能会出现同一条数据出现在不同页或页边界数据丢失的问题

分页查询后批量插入本身的逻辑较为简单，此处略过具体代码实现示例

流式数据同步

如果把PG到CK的数据同步类比为将水从一个水桶转移到另一个水桶，那么分页查询+批量插入的方式就像是一个人拿了一个瓢，这个瓢的容量固定，每次都从一个水桶舀一瓢水，然后将瓢里的水倒入另一个水桶。很明显，这种方式需要反复舀水、倒水、舀水…而且水瓢的容积（分页大小）的选择也是技术活，若瓢太大，虽然一次可以转移更多水，转移次数也变少了，但是对拿水瓢的人要求就高了，必须要能拿得起；而瓢太小了，虽然可能小孩儿都能承担起这个任务，但往返次数变多了，时间自然也就久了。

而流式数据同步，就像是给这两个水桶之间加了一根管子，让水从一个桶直接流向另一个桶。水源源不断地从一个桶流向另一个桶，省去了我们舀水、倒水、再舀水…的操作。用水管运输水的过程中，水管中通常都是满的，而用瓢时，在将瓢移动到另一个桶并把水倒进去这个过程中瓢一直是被占用的，因此这段时间是有所浪费的。

流式数据同步想法从何而来？

在查阅众多关于PG和CK同步数据的资料之后，发现主要的方式还是利用CK的数据库引擎功能来进行数据交换，但正如前文所说，这种方式并不能满足一些特定的业务需求。在我快要妥协使用分页+批量插入这种方式时，我突然想到：数据同步不就是一个导入另一个导入吗？为何不先去研究研究PG和CK分别支持哪些数据导入、导出的方式？

有了这个想法后，开始查阅PG和CK的官方文档，发现CK支持从JSON、CSV、TSV等文件导入数据，具体参见Importing from various data formats to ClickHouse | ClickHouse Docs

而PG则通过COPY命令也恰好支持了导出到CSV、TSV等格式，具体参见PostgreSQL: Documentation: 14: COPY

理论可行，那么就可以通过将PG数据导出到CSV/TSV文件，然后将导出的文件导入到CK来实现PG和CK的数据同步，但这样很明显有个致命问题——需要中间文件。既然一头是输出，一头是输入，那能否像Java InputStream中的transferTo(OutputStream)一样直接将输入流的内容写到输出流呢？相当于将PG的输出直接连接到CK的输入？

因为是基于Java开发，连接PG或CK通常是要走JDBC驱动的，顺着这条路就开始搜索和PG的COPY命令相关的类，别说还真有一个名为CopyManager的类担当起了这个重任。有兴趣的读者可以参考官方文档了解更多 PostgreSQL® Extensions to the JDBC API | pgJDBC

在官方文档的示例中表明，CopyManager中有个名为copyOut(sql)的方法，可以将指定sql的查询结果输出。在IDEA中查看这个JDBC驱动包，定位一下CopyManager会发现还有一些相关的东西：

在这里插入图片描述

当你充满好奇地查看此包下的PGCopyOutputStream和PGCopyInputStream时，惊喜便来了。这里我们重点看与本文相关的PGCopyInputStream，这个类的注释是：

InputStream for reading from a PostgreSQL COPY TO STDOUT operation.

意思是PGCopyInputStream是用于读取PG的COPY TO STDOUT命令的输出的输入流

这里注意理清输入输出的关系，从PG的视角来说，执行COPY TO STDOUT是往标准输出输出数据，但对于我们Java程序来说，是要读到这个输出的数据。所以从自身程序的角度来说应该叫输入，所以对应PGCopyInputStream

能拿到这个PGCopyInputStream说明了在程序中得到PG的COPY命令的输出结果是没什么问题了。稍微想一下CK那边既然支持CSV/TSV导入，那大概率也是读文件进行的，都是读文件了，那直接给个文件流应该也支持吧？同样的方法，从JDBC驱动开始入手，看看CK都能通过JDBC支持哪些导入操作

对于CK来说，要找到这个功能没有像PG那么简单，CK的JDBC驱动按照官网的说法本质上是基于CK的Client API构建的，而Client API理论上可以执行所有CK客户端的操作，自然也就包括通过CSV/TSV导入数据。移步Client API的文档，有这么一个示例：

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers).write()
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("insert into my_table select c2, c3 from input('c1 UInt8, c2 String, c3 Int32')")
        //这里表示可以从输入流获取输入插入CK
        .data(myInputStream) // load data into a table and wait untilit's completed
        .executeAndWait()) {
   
   
            ClickHouseResponseSummary summary = response.getSummary();
            summary.getWrittenRows();
}

具体文档参考：Java Language Client Options for ClickHouse | ClickHouse Docs

由于文档没有详尽列出所有功能，因此再看官方代码示例，发现有这么一个操作：

//意思就是如何unwrap JDBC连接来使用Client API
static String unwrapToUseClientApi(String url) throws SQLException {
   
   
        String sql = "select 1 n union all select 2 n";
        try (Connection conn = getConnection(url); Statement stmt = conn.createStatement()) {
   
   
            // unwrap 得到 ClickHouseRequest
            ClickHouseRequest<?> request = stmt.unwrap(ClickHouseRequest.class);
            // server setting is not allowed in read-only mode
            if (!conn.isReadOnly()) {
   
   
                // not required for ClickHouse 22.7+, only works fo