PostgreSQL快速导入千万条数据

为了与MySQL做个对比，做一个PG的数据导入测试，使用COPY方式，测试环境保持一致，具体如下所述。

一、测试环境

■ 48CPU/376G

CPU(s):                48
Thread(s) per core:    2
Core(s) per socket:    12
Socket(s):             2
Model name:            Intel(R) Xeon(R) Gold 5118 CPU @ 2.30GHz
L1d cache:             32K
L1i cache:             32K
L2 cache:              1024K
L3 cache:              16896K

■ DISK

Model: AVAGO HW-SAS3508 (scsi)
Disk /dev/sdb: 12.0TB
test write：1.1GB/s
test read： 3.9GB/s

■ OS

Red Hat Enterprise Linux Server 7.4 (Maipo)

■ FS

Filesystem                  Type      Size  Used Avail Use% Mounted on
/dev/mapper/vgora-lv_u01    xfs        11T  792G   11T   8% /u01

■ db version

[postgres@adg1:2 ~]$ psql -V
psql (PostgreSQL) 14.2
[postgres@adg1:2 ~]$ pg_ctl -V
pg_ctl (PostgreSQL) 14.2

二、修改源数据为COPY可用的格式

源数据：与MySQL测试保持一致

格式：MySQL导出的DOS文本文件格式(\n\r)，修改为unix文本文件格式(\n)

【此处使用csv格式】

使用head、awk、sed工具取出所需的记录行，并修改各行数据格式。

由于源数据存在\'，导致无法正确导入，需把\'替换成"或其他字符。

sed -i "s/\\\'/\"/g" mydata.sql

三、DDL

■ 根据最原始的源数据，调整DDL语句

CREATE TABLE tablename (
  id int4 NOT NULL,
  code varchar(100) DEFAULT NULL,
  init_value text,
  master_id text DEFAULT NULL,
  code_id varchar(11) DEFAULT NULL,
  end_value text
);
ALTER TABLE tablename ADD PRIMARY KEY (id);
CREATE INDEX tablename_master_id ON tablename USING btree (master_id);
CREATE INDEX tablename_code ON tablename USING btree (code);

四、COPY

COPY tablename FROM '/u01/pg/mydata.sql' WITH DELIMITER ',' CSV QUOTE '''';

耗时记录如下：

■ 100万

COPY 1000000

Time: 8938.071 ms (00:08.938)

■ 500万

COPY 4999966

Time: 70209.830 ms (01:10.210)

COPY 5000000

Time: 56365.434 ms (00:56.365)

COPY 5000000

Time: 65061.424 ms (01:05.061)

■ 2000万

COPY 19999961

Time: 257502.571 ms (04:17.503)

COPY 19999962

Time: 252777.564 ms (04:12.778)

COPY 20000000

Time: 250620.213 ms (04:10.620)

COPY 10578983

Time: 138167.048 ms (02:18.167)

■ 3000万

COPY 30578975

Time: 397111.329 ms (06:37.111)

COPY 30578983

Time: 391768.993 ms (06:31.769)

COPY 30578983

Time: 388049.729 ms (06:28.050)

五、结论

pg用LOAD方法的大数据量导入，千万条数据需要1分钟以上。

关于pg的数据导入，后续再探讨一下其他更好的方式。