PostgreSQL、Greenplum DML合并操作 最佳实践

postgresql , greenplum , 合并删除 , 合并更新 , 合并dml

在很多场景中会涉及到数据的合并，比如

1. 某业务系统的总用户有1亿，每天的活跃用户有100万，新增用户10万，每天需要将新增、活跃用户的数据（比如他们的余额变化、等等）合并到数据仓库的用户信息表。

2. 物化视图，某个表被用户不断的增、删、改。需要将这个表（基表）的某些字段或者某部分数据提取到一个物化视图中。这个物化视图不需要对每一笔基表的dml都实施操作，比如对单条记录的操作，合并成一次操作。

3. 数据同步，将oltp的数据，同步到olap系统，由于olap系统的事务处理能力没有tp系统强，所以也必须采用合并的方法，同一条记录被多次更新时，需要将多次更新合并成一次更新。

4. 基于redo日志的逻辑数据复制，优化手段除了并行复制，还有一种就是合并复制。

不管是哪种数据合并，被合并的表最好是有主键的，本文也假设有主键来处理。否则会增加复杂度（需要使用整行记录来区分），而且整行记录有一个缺陷，例如根据行号定位重复记录中的一条，这样变更后，合并时可能会出错。

对于以上几种情况，比较复杂的是逻辑数据复制，它可能涉及到任意操作，单个key可能被删除，后续这个key又被插入、多次更新的情况。

同时还需要考虑事务一致性的问题，每一次合并操作都需要保证一致性。例如基于redo的逻辑复制，对于未结束的事务产生的redo，不能参与合并。

保证单个key，在合并时只操作一次，同时确保未结束的事务不参与合并。

table : 库\schema\表名

old : 主键值

new : 新插入的值 、 被变更的字段变更后的值

tag : insert 、 update 、 delete 、 truncate

以逻辑复制为例，分解一下数据合并的过程。

创建测试表

产生一些dml

将以上dml转换为redo要素如下（通常以下信息可以在数据库的redo日志得到）

合并过程，对已提交的记录，按pk进行分组，按执行先后顺序排序

如果涉及到pk的变更，需要将其分解为delete和insert两条

逻辑复制的合并相对来说比较复杂，但是postgresql是一个功能强大的数据库，它支持窗口查询，编程能力强大的plpgsql函数语言（还有python, java, perl等数据库函数语言），使用sql还是比较方便的可以完成以上合并的。

除了逻辑复制，在olap中也经常要用到合并更新，主要的目的是减少olap系统sql的执行次数（因为olap系统并不是为tp业务设计，而是为批处理或大量运算设计的，多次sql如果能合并成一次的话，可以大幅提升效率）

下面就以更新的合并为例，简单的讲解一下数据合并的例子。

比如一张表有1亿记录，每天要更新其中的10万条记录。我们要做的是将10万条update语句，合并成一条update语句。

合并方法

1. 首先将更新语句转换为数据，插入一张临时表

2. 然后使用join update来更新目标表

过程如下

创建一个生产表（目标表，必须有pk），假设它有1亿（为了演示，仅使用100万记录）用户数据。

创建一张临时表，用来存储合并前的dml，表结构如下，需要包含一个新增的序列pk，以及目标表的所有字段，以及每个字段对应的set位（表示该字段是否被更新）

我们这里假设一条记录，可能被多次更新。

插入100万数据到prod表

tp系统中的update语句，我们将它转换为目标值，插入临时表

true大于false，将用于多条记录的合并

如果一条记录被多次update，需要将多个update合并为一个update

用到了窗口查询，以目标表的pk为分组，按不同字段的set位优先取true的最后一条值，以及它的set状态。

以上就是合并后的数据

更新时，使用case，将字段set位为true的值更新为新的值，false的不变。

删除更加简单，只需要将id记录下来，delete from tbl where id in (...)即可，不再列举。

使用两张目标表，一张为合并更新（合并更新的数据来源于实时更新的触发器日志），一张为实时更新。

使用pgbench，不断更新prod_ck

将tmp1的更新合并到prod

验证合并更新后prod和prod_ck是否一致

数据合并的目标是将多条dml语句合并成一条，

包括将单条记录的多次更新、插入、删除合并为一次更新、插入或删除操作，

也包括将多条记录的多次dml合并成一条dml语句。

在数据逻辑复制、tp到ap业务系统的同步、物化视图 等场景有着广泛的应用。

特别是olap系统，由于并不是针对tp场景涉及，使用合并操作，可以大幅提升ap系统的操作效率。(greenplum更新和删除都是表级锁, 效率也一般)

在greenplum单条记录，基于pk的更新速度测试