OpenGauss数据加载最佳实践

批量数据加载导致速度慢的原因，包括索引，触发器，外键，主键，WAL日志，主备数据同步等。

本文总结了批量加载数据到OpenGauss数据库的最佳实践，在其他数据库迁移到OpenGauss数据库时仅供参考。

表修改为无日志模式

先把表修改为无日志模式，加载数据，再把表修改为有日志模式

ALTER TABLE <target table> SET UNLOGGED

<bulk data insert operations…>

ALTER TABLE <target table> LOGGED

修改为无日志模式，可以避免写WAL日志。

注意：无日志模式因为不写日志，如果数据库在加载数据异常关闭时，数据库重启会自动truncate没有日志的表。无日志的表数据也不会同步到备机，需要重新做数据同步。数据同步也会需要很长时间。

删除和重建索引

批量导入数据时，如果表上存在索引，在导入数据时，会同步更新索引信息。建议备份好表上的索引创建语句，删除索引，加载数据，然后重建索引。

DROP INDEX <index_name1>, <index_name2> … <index_name_n>

<bulk data insert operations…>

CREATE INDEX <index_name> ON <target_table>(column1, …,column n)

创建索引时，要调大maintenance_work_mem参数，以增加索引创建速度。

删除和重建外键

外键约束也影响数据加载性能。插入的每条记录，都要到对应的主键进行校验。数据库采用触发器的方式来做校验。

ALTER TABLE <target_table> 

    DROP CONSTRAINT <foreign_key_constraint>

BEGIN TRANSACTION

    <bulk data insert operations…>

COMMIT

ALTER TABLE <target_table> 

    ADD CONSTRAINT <foreign key constraint>  

    FOREIGN KEY (<foreign_key_field>) 

    REFERENCES <parent_table>(<primary key field>)...

重建外键时，调大maintenance_work_mem参数以提升外键创建速度。

禁用触发器

如果表上有插入的触发器，针对批量加载的每一条记录，触发器都会进行校验。推荐先禁用表上的触发器，插入数据，启用触发器。

ALTER TABLE <target table> DISABLE TRIGGER ALL

<bulk data insert operations…>

ALTER TABLE <target table> ENABLE TRIGGER ALL

使用copy命令

copy是OpenGauss自带的性能最高的导入工具，比多值插入性能还高，支持导入二进制，支持带where条件导入。

COPY <target table> [( column1>, … , <column_n>)]

    FROM  '<file_name_and_path>' 

    WITH  (<option1>, <option2>, … , <option_n>)

copy工具其他优势:

• 支持文本和二进制字段的导入
• 本身支持事物
• 允许指定输入文件的结构，例如导入那些字段
• 通过where语句，支持导入满足条件的数据

如果OpenGauss主机配置比较高，可以启动多个copy命令进行并行数据导入。

注：以上代码片段为测试使用，商用时请考虑实际工程能力。

copy导入数据时的性能，也受表上索引，外键，触发器的影响。

使用多值插入

单条记录插入时，优化器会解析sql语句，约束校验，在一条语句中插入多条语句，可以只解析一次sql语句，提升性能。

INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>) 

VALUES 

    (<value a>, <value b>, …, <value x>),

    (<value 1>, <value 2>, …, <value n>),

    (<value A>, <value B>, …, <value Z>),

    (<value i>, <value ii>, …, <value L>),

    ...

多值插入也受索引，外键，触发器的影响。建议删除和重建。

插入的数据，会导致内存中数据刷出，对其他sql查询性能有影响。

建议effective_cache_size参数设置为操作系统内存的50%，shared_buffer设置为操作系统内存的25%。1万条记录提交一次。

运行ANALYZE刷新表的统计信息

批量数据导入后，会导致系统中表的统计信息不准确，影响执行计划的选择。

建议表数据量变化超过10%时手工运行analyze命令，刷新表的统计信息。

参考文档：

http://ossc-db.github.io/pg_bulkload/index.html

https://www.enterprisedb.com/blog/7-best-practice-tips-postgresql-bulk-data-loading