PostgreSQL jdbc batch insert

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , jdbc , batch , addbatch , executebatch , insert


背景

如何快速的将数据导入数据库?

比如ETL程序,数据还原程序,数据初始化,数据同步等场景都会有这样的诉求。

从几个方面来分析

1. 统计信息

PostgreSQL会自动统计表的统计信息,包括柱状图等。会有一定的开销,所以在做批量导入时,可以先关闭表的autovacuum.

2. 索引

构造索引,会有一定的CPU和IO开销,影响导入速度,所以可以在数据导入后再建索引。

3. 约束

约束会耗费一定的CPU,也可以在数据导入后再加索引。

4. 检查点

数据库导入势必产生WAL日志,如果WAL很多,可能会产生检查点,影响IO。因此可以把检查点的时间拉长(设置ckpt target, timeout)。

5. COMMIT fsync

如果是单条INSERT的导入方式,并且使用了autocommit,那么每条COMMIT都会导致FSYNC,flush wal。从而降低速度。

可以设置为异步提交,降低RT。

或者使用批量提交,例如100条提交一次。

6. parser开销

insert需要parser, 即使使用绑定变量,也有诸多tuple form的工作。

使用copy 接口可以提高效率。

7. 交互次数

如果使用的是单条insert,也有批量的方式提高速度,例如insert into table values (),(),(),.....;

相比以下,减少了交互次数,可以大幅提升性能,与COPY效率类似。

begin;  
insert into table values ();  
insert into table values ();  
...  
end;  

8. 10.0即将推出的一个新特性,将允许libpq有批量提交QUERY的功能。

《PostgreSQL 10.0 preview 性能增强 - libpq支持pipeline batch模式减少网络交互提升性能》

本文将要讲一下jdbc的batch insert.

jdbc batch insert

目前,使用jdbc prepared statement insert与addbatch, execute batch,你可能会发现,最后SQL还是没有封装成这样insert into table values (),(),(),.....;而是一条一条的insert。

社区也有类似的讨论帖子

Hi,  
  
As document said, in the extended query protocol, "The query string  
contained in a Parse message cannot include more than one SQL  
statement".  
  
So to support batch in prepared statement, I think the only way is to  
determine the batch size in advance and then create the appropriate  
prepared statement, e.g.  
  
Given the batch size is fixed to 3, then prepare below statement:  
-- create table foobar(a int, b text);  
insert into foobar values($1, $2), ($3, $4), ($5, $6);  
  
Then this prepared statement must be bound with 3 set of arguments.  
  
The limitation is obvious: the batch size is fixed, so if you need to  
do batch with size of 4, the previous prepared statement is useless  
and you need to recreate it.  
  
On the other hand, in JDBC, it seems that you just need to prepare  
below statement:  
  
insert into foobar values($1, $2);  
  
And then calls addBatch() repeatedly until you think the batch size is enough.  
  
What's the final statement does postgresql jdbc driver convert to? I'm  
so curious.  
  
I'm not familiar with jdbc, and although I check the driver source  
codes, but I still cannot understand it.  
  
Anybody knows the answer? Thanks.  
  
  
Regards,  
Jinhua Luo  

github里面有一个JDBC batch insert rewrite功能,可以将single batch转换为multi-row batch

https://github.com/whitingjr/batch-rewrite-statements-perf

The first measures INSERT statements in a single batch  
  
 batch begin  
  | INSERT  
  | INSERT  
  | INSERT  
  | INSERT  
  | n INSERT  
 batch end  
the second uses an individual multi-row INSERT statement.  
  
INSERT INTO orderline VALUES (?,?),(?,?),(?,?),(?,?),(n,n)  
Both types has 3 individual benchmarks with varying numbers of statement/row. There is a benchmark called SMALL, MEDIUM and LARGE. The count for each is configurable. See Configuration section later for details.  

下面是测试对比,很显然multi-row的效果好很多。

pic

pic

pic

建议要么使用multi-row batch,要么使用copy.

jdbc 版本

可以看到multi-row batch已经支持了,在这

https://github.com/pgjdbc/pgjdbc/commit/510e6e0fec5e3a44ffda854f5cf808ce9f72ee2e

fix: improve insert values(...) batch rewrite
Now it supports VALUES (?,1), and splits very long batch into series of smaller ones
to keep overall number of bind variables <32768 (that is PG's limit in v3 protocol)

Trigger property: reWriteBatchedInserts=true. Default value is still "false".

closes #580
closes #584

用法

reWriteBatchedInserts=true

参考

《PostgreSQL 10.0 preview 性能增强 - libpq支持pipeline batch模式减少网络交互提升性能》

https://jdbc.postgresql.org/

https://github.com/pgjdbc/pgjdbc/

https://www.postgresql.org/message-id/55130DC8.2070508@redhat.com

https://github.com/pgjdbc/pgjdbc/pull/491

https://www.postgresql.org/message-id/flat/CAAc9rOwTZ3d6%3DYUV-vJPndebVyUGAz_Pk8WV1fYkmpVykttLug%40mail.gmail.com#CAAc9rOwTZ3d6=YUV-vJPndebVyUGAz_Pk8WV1fYkmpVykttLug@mail.gmail.com

https://jdbc.postgresql.org/documentation/publicapi/index.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
8月前
|
SQL 关系型数据库 Java
聊聊 JDBC 的 executeBatch || 对比下不同数据库对 JDBC batch 的实现细节
聊聊 JDBC 的 executeBatch || 对比下不同数据库对 JDBC batch 的实现细节
|
1月前
|
SQL Java 关系型数据库
jdbc(insert,update,,create,以及各个类详解)
jdbc(insert,update,,create,以及各个类详解)
|
7月前
|
关系型数据库 PostgreSQL
postgresql insert into插入记录时使用select子查询
postgresql insert into插入记录时使用select子查询
|
SQL 缓存 数据管理
开源分布式数据库PolarDB-X源码解读——PolarDB-X源码解读(五):DML之Insert流程.
开源分布式数据库PolarDB-X源码解读——PolarDB-X源码解读(五):DML之Insert流程.
632 0
|
存储 网络协议 Java
PostgreSQL JDBC 开发指导
Java Database Connectivity (JDBC) 是一个应用程序编程接口 (API),用于 编程语言 Java,它定义了客户端如何访问数据库。 它是Java标准版平台的一部分,提供查询和 更新数据库中的数据,并面向关系数据库。 PostgreSQL JDBC驱动程序(简称pgJDBC)允许Java程序连接到PostgreSQL®® 数据库使用标准的、独立于数据库的 Java 代码。是一个开源的JDBC驱动程序 用纯Java(类型4)编写,并使用PostgreSQL®本机网络协议进行通信。 因此,驱动程序独立于平台;编译后,驱动程序 可以在任何系统上使用。
117 0
|
SQL AliSQL 数据库连接
PolarDB-X 源码解读系列:DML 之 INSERT IGNORE 流程
本文将进一步介绍 PolarDB-X 中 INSERT IGNORE 的执行流程,其根据插入的表是否有 GSI 也有所变化。
PolarDB-X 源码解读系列:DML 之 INSERT IGNORE 流程
|
Java 数据库连接 数据库
使用JDBC(Dbutils工具包)来从数据库拿取map类型数据来动态生成insert语句
前言: 大家在使用JDBC来连接数据库时,我们通过Dbutils工具来拿取数据库中的数据,可以使用new BeanListHandler<>(所映射的实体类.class),这样得到的数据,不知道表的字段名字,我们在往数据库里添加时,需要自己来挨个写字段,非常麻烦! 于是,小编想到通过MapListHandler(),结果集为一个List<Map<String, Object>>,map中key为数据库字段名字,value为对应的值,这样就可以实现insert语句动态拼接了!!
182 3
|
关系型数据库 PostgreSQL
PostgreSQL INSERT INTO 语句
PostgreSQL INSERT INTO 语句
314 0
|
SQL 缓存 数据管理
PolarDB-X源码解读系列:DML之Insert流程
Insert类的SQL语句的流程可初略分为:解析、校验、优化器、执行器、物理执行(GalaxyEngine执行)。本文将以一条简单的Insert语句通过调试的方式进行解读。
270 0
|
SQL druid Java
Java 执行 Postgresql Jdbc 类型异常时,复杂sql难定位的解决方案
Java 执行 Postgresql Jdbc 类型异常时,复杂sql难定位的解决方案
1275 0
Java 执行 Postgresql Jdbc 类型异常时,复杂sql难定位的解决方案

相关产品

  • 云原生数据库 PolarDB