最全PostgreSQL数据库如何快速插入大量数据_postgresql 大批量插入

);

* 索引语句

create index i_jdbc_sql_record_zh01 on t_jdbc_sql_record(c_bh_group,dt_zxsj,N_CHECKSTATUS,C_PSSQL_HASH);
create index i_jdbc_sql_record_pshash on t_jdbc_sql_record(c_pssql_hash);
create index i_jdbc_sql_content_pshash on t_jdbc_sql_content(c_pssql_hash);
alter table t_jdbc_sql_content add constraint t_jdbc_sql_content_pkey primary key (C_BH);

* 异步提交和unlogged table

– 异步提交,更改完重启数据库
alter system set synchronous_commit to off;
– unlogged table
create unlogged table t_jdbc_sql_record
…
create unlogged table t_jdbc_sql_content
…

* JDBC日志量  
 19个JDBC日志文件，共2G日志，600万记录


### 方案设定




| 方案名称 | 方案描述 |
| --- | --- |
| 方案一 | 建立结构化表及其索引，多线程单个insert装入数据 |
| 方案二 | 建立结构化表及其索引，多线程批量insert装入数据 |
| 方案三 | 建立结构化表及其索引，库设置为异步提交，多线程批量insert装入数据 |
| 方案四 | 建立结构化表，库设置为异步提交，多线程批量insert装入数据，建立索引 |
| 方案五 | 建立结构化表及其索引，表设置为unlogged table,多线程批量insert装入数据 |
| 方案六 | 建立结构化表，表设置为unlogged table，多线程批量insert装入数据，建立索引 |
| 方案七 | 建立结构化表，多线程批量insert装入数据,建立索引 |


### 实验结果


每次实验时，解析的JDBC日志量，解析代码和中间件环境保持不变。只调整流程顺序和数据库参数。




| 实验次数 | 方案一 | 方案二 | 方案三 | 方案四 | 方案五 | 方案六 | 方案七 |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 第一次 | 3596s | 2043s | 1164s | 779s | 545s | 528s | 1192s |
| 第二次 | 4092s | 2068s | 1283s | 843s | 528s | 528s | 1227s |
| 第三次 | 3891s | 2177s | 1378s | 858s | 536s | 537s | 1248s |
| 平均值 | 3859s | 2096s | 1275s | 826s | 536s | 531s | 1222s |


##结果分析


* 方案一、方案二比较，数据库参数不变，流程顺序不变


	+ 方案一：单个insert提交，用时3859秒
	+ 方案二：批量insert提交，用时2096秒
* 方案二、方案三、方案五比较，流程顺序不变，均为建表->建索引->多线程批量插入。


	+ 方案二：同步提交(等待WAL日志完成)，用时2096秒
	+ 方案三：异步提交(不等待WAL日志完成),用时1275秒
	+ 方案五：不记录WAL日志，用时536秒
* 方案二、方案七比较,均为同步提交


	+ 方案二：插入数据前建立索引,用时2096秒
	+ 方案七：插入数据后建立索引,用时1222秒
* 方案三、方案四比较,均为异步提交


	+ 方案三：插入数据前建立索引,用时1275秒
	+ 方案四：插入数据后建立索引,用时826秒
* 方案五、方案六比较，均为不记录WAL日志


	+ 方案五：插入数据前建立索引，用时536秒
	+ 方案六：插入数据后建立索引，用时531秒


##总结  
 **在该场景下：**


* 批量提交比单个提交快55%
* 异步提交比同步提交快40%
* 不记录日志提交比同步提交快75%
* 记录日志且同步提交时，后建立索引比先建立索引快40%
* 记录日志且异步提交时，后建立索引比先建立索引快35%
* 不记录日志时，后建立索引比先建立索引略快，但差别不大


**插入数据最快组合为：**  
 unlogged table + 多线程批量insert+后建索引


**猜想：**  
 在insert过程中，维护索引的时间占总时间的35%到40%，且主要花费在日志持久化上。


**其他：**  
 同时在实验过程中的一些其他指标信息，如不同方案下数据库的写IO从未超过100MB/s,需要继续分析。


![img](https://img-blog.csdnimg.cn/img_convert/9a0791dd02768c4e180db3d0cf90b9e2.png)
![img](https://img-blog.csdnimg.cn/img_convert/a450064992488449711a5c285badfc70.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

et/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**