PostgreSQL 服务器编程 高性能批量插入

1 背景知识

在现代应用中，尤其是在需要处理大量数据和高并发请求的场景下，如何高效地进行数据插入成为了一个重要的议题。本章将介绍 PostgreSQL 中实现并发插入的技术方法。

2 SQL 一次性插入多行

2.1 创建测试数据表

首先，我们需要准备一张表 t01。这个表包含两个字段 id 和 name 。

DROP TABLE  IF EXISTS t01;
CREATE TABLE t01 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

2.2 一次性插入多行

使用 INSERT INTO 语句可以批量多条数据，因为减少了事务的开销，所以能够提升插入的速度。

INSERT INTO t01 (id, name) VALUES
(1, 'kingbase1'),
(2, 'kingbase2'),
(3, 'kingbase3');

INSERT 0 3

3 PLSQL 并发插入

3.1 创建测试数据表

首先，我们需要在 PostgreSQL 数据库中创建一个用于测试的数据表。以下的SQL语句创建一个简单的测试表。

DROP TABLE  IF EXISTS t01;
CREATE TABLE t01 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

3.2 编写PL/SQL存储过程

接下来，我们需要编写一个PL/SQL存储过程，实现高性能的批量插入操作。下面是一个简单的示例。

CREATE OR REPLACE FUNCTION bulk_insert_test_data()
RETURNS VOID AS $
DECLARE
    i INT := 1;
BEGIN
    -- 开启事务
    BEGIN
        LOOP
            -- 批量插入数据
            INSERT INTO t01 (name) VALUES ('Name ' || i);
            i := i + 1;
            EXIT WHEN i > 10000;  -- 设置插入数据的数量
        END LOOP;
        -- 提交事务
    END;
END;
$ LANGUAGE plpgsql;

3.3 调用存储过程

通过以下的SQL语句来执行它。

SELECT bulk_insert_test_data();

SELECT COUNT(*) FROM t01;

//屏幕输出：
count 
-------
 10000
(1 row)

4 COPY 命令并发插入

COPY 命令是 PostgreSQL 中最快的导入数据方式之一。它直接从文件或标准输入中读取数据，并高速地将其加载到数据库表中。

4.1 上传CSV 文件

此 isbn.csv 文件为 Zlibrary 的数据转换后的 csv 文件。请上传到 /soft 目录。

ls -l /soft/isbn.csv

//屏幕输出：
-rw-r--r-- 1 postgres postgres 253894432 May  2 18:57 /soft/isbn.csv

4.2 创建表

创建用于装载 csv 数据的表 zlibrary.isbn。

psql -U postgres -d testdb

DROP SCHEMA IF EXISTS zlibrary CASCADE;
CREATE SCHEMA zlibrary;

CREATE TABLE zlibrary.isbn (
    zlibrary_id integer NOT NULL,
    isbn character varying(13) NOT NULL
);


ALTER TABLE ONLY zlibrary.isbn
    ADD CONSTRAINT "PRIMARY_9E9D7BB2" PRIMARY KEY (zlibrary_id, isbn);


ALTER TABLE ONLY zlibrary.isbn
    ADD CONSTRAINT "isbn_id_2E99E8E3" UNIQUE (isbn, zlibrary_id);

4.3 清空数据

清空 zlibrary.isbn 表。

psql -U postgres -d testdb

TRUNCATE TABLE zlibrary.isbn;

4.4 导入数据

COPY 导入数据共计耗时为 2m24.027s。

 time psql -U postgres -d testdb -c '\copy zlibrary.isbn from /soft/isbn.csv csv'

COPY 12180911

real    2m24.027s
user    0m0.492s
sys     0m0.306s

5 pg_bulkload 并发插入

5.1 安装 PostgreSQL pg_bulkload

具体安装过程请参考 PostgreSQL pg_bulkload 安装与配置。

5.2 上传CSV 文件

此 isbn.csv 文件为 Zlibrary 的数据转换后的 csv 文件。请上传到 /soft 目录。

ls -l /soft/isbn.csv

//屏幕输出：
-rw-r--r-- 1 postgres postgres 253894432 May  2 18:57 /soft/isbn.csv

5.3 创建表

创建用于装载 csv 数据的表 zlibrary.isbn。

psql -U postgres -d testdb

DROP SCHEMA IF EXISTS zlibrary CASCADE;
CREATE SCHEMA zlibrary;

CREATE TABLE zlibrary.isbn (
    zlibrary_id integer NOT NULL,
    isbn character varying(13) NOT NULL
);


ALTER TABLE ONLY zlibrary.isbn
    ADD CONSTRAINT "PRIMARY_9E9D7BB2" PRIMARY KEY (zlibrary_id, isbn);


ALTER TABLE ONLY zlibrary.isbn
    ADD CONSTRAINT "isbn_id_2E99E8E3" UNIQUE (isbn, zlibrary_id);

5.4 创建控制文件

创建并编辑用于数据导入的控制文件 sample_csv.ctl 。

su - postgres 
vi /soft/sample_csv.ctl 

//输入内容：
#
# sample_csv.ctl -- Control file to load CSV input data
#
#    Copyright (c) 2007-2024, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
#
OUTPUT = zlibrary.isbn                # 导入数据所存放的目的地。
INPUT = /soft/isbn.csv                # 指定导入的数据来源和位置。绝对路径。    
TRUNCATE = YES                        # 清空数据目标表。
WRITER = DIRECT                       # 导入数据时，绕过共享缓冲区，并跳过WAL日志。 
TYPE = CSV                            # 导入数据的类型
QUOTE = "\""                          # 指定什么字符作为引号。
ESCAPE = \                            # 引号的转义符号。
DELIMITER = ","                       # CSV 文件的分隔符。

对于以上参数详细信息，请参考 pg_bulkload 控制文件参数。

5.5 导入数据

单进程导入数据的耗时为 1m14.976s。

time pg_bulkload -U postgres -d testdb /soft/sample_csv.ctl

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        12180911 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

real    1m14.976s
user    0m0.002s
sys     0m0.006s

6 小结 & FAQ

1. COPY 命令此处共计耗时 2m24.027s
2. pg_bulkload 此处共计耗时 1m14.976s。
   证明了使用 copy 命令加载将会比 pg_bulkload 导入耗时更长。