Postgresql/PgSQL如何高效的快速插入记录

1、前言

前两天，需要给构建的测试库灌入测试数据，发现Insert性能非常差。

2、分析

之前已经知道，数据库的索引会影响Insert，而且后台的AutoVacuum也会频繁的访问你正在写入的表；

再次在网上查询，没找到临时禁用索引的方案；

不过，AutoVacuum倒是有办法处理。

3、第一次优化

3.1、尝试先删除索引，事后再重新创建；

3.2、AutoVacuum可以尝试调整相关的阈值，或者是直接在这个表上禁用，我采用的是禁用临时方案（脚本见文末）。

3.3、优化结果：写入性能提升了很多，具体大家可以自己尝试，因为没做记录，具体性能数据不做表述。

4、第二次优化

4.1、想起很久以前关于事务的描述，于是在测试记录生成工具中添加了数据库事务，每500条做一次提交；

4.2、忘记是出于什么目的，每10万条记录，在脚本中对这个表做一次Vacuum（多线程下不要这样做）；

4.3、调整配置参数 log_min_duration_statement 为100ms，仅将执行达到100ms及更长时间的SQL语句记录在日志里，以减少磁盘无意义的IO；

4.4、优化结果：再次启动测试记录生成工具，发现写入性能提升很大，磁盘IO也满了，基本满意。

5、无日志表优化方案

2023-04-17，今天偶然发现一个方案，就是无日志表（unlogged Table），这种表因为不写WAL性能也是超快的。

可以先建无日志表，写入测试数据之后，再将无日志表转成普通表。

不过这种方案有风险，万一异常崩溃的话，表里所有的记录都会光光，在我这个场合下可能不适用。

文末补充一个无日志表与带日志的普通表相互转换的SQL。

6、相关SQL脚本

5.1、对某个表禁用AutoVacuum，及时生效，无需重启

-- 禁用AutoVacuum，中括号及其内部单词是表名
ALTER TABLE [TableName] SET (autovacuum_enabled = off);

5.2、对某个表启用AutoVacuum，及时生效，无需生启

-- 启用AutoVacuum，中括号及其内部单词是表名
ALTER TABLE [TableName] SET (autovacuum_enabled = on);

5.3、调整 log_min_duration_statement参数

-- 调整log_min_duration_statement 参数为，仅将耗时达到100ms及以上的SQL记录在日志文件里。
-- 此参数需要重新连接数据库？或者是需要重启？
-- 当然，直接去修改配置文件，并重启数据库也行
ALTER SYSTEM SET log_min_duration_statement = '100';

5.4、查看表和索引所占用的磁盘空间

--以KB、MB、GB的方式来查看表大小，引号里面是表名
select pg_size_pretty(pg_relation_size('TableName')); 

--以KB、MB、GB的方式来查看表的总大小，包括索引大小，引号里面是表名
select pg_size_pretty(pg_total_relation_size('TableName')); 

5.5、无日志表（unlogged Table）转换相关SQL 

-- 建一个无日志表，表名t_bigdata 
CREATE UNLOGGED TABLE t_bigdata 
(
  id bigint primary key,
  name text
);

-- 将无日志表转能带日志的普通表
ALTER TABLE t_bigdata SET LOGGED;

-- 将带日志的普通表转成无日志表
ALTER TABLE t_bigdata SET UNLOGGED;

带日志的普通号（logged Table）相关