[译文] PostgreSQL 14 新特性：外部数据包装器的批量插入

基于 SQL-MED 的外部数据包装器是 PostgreSQL 最酷的特性之一。自 9.1 版以来，外部数据包装器的功能集正在扩展。我们知道 PostgreSQL 14 测试版已经出来，GA 很快就会推出，因此研究一下 PostgreSQL 14 即将推出的功能是有帮助的。 PostgreSQL 14 中添加了一个新的性能特性“批量插入”，该 API 得到了扩展，允许将数据批量插入到外部表中，因此，使用该 API，任何外部数据包装器现在都可以实现批量插入。这绝对比插入单行更有效。

API 包含两个新函数，可用于实现批量插入。

• GetForeignModifyBatchSize
• ExecForeignBatchInsert

这里没有必要解释这些函数，因为它对有兴趣将这些功能包含在其外部数据包装器（如 mysql_fdw、mongo_fdw 和 oracle_fdw）中的人很有用。如果有人有兴趣看到它，他们可以在 PostgreSQL文档 中看到它。但好消息是，postgres_fdw 已经实现了它，并且在 PostgreSQL 14 中有它。

添加了一个新的服务器选项，即batch_size，您可以在创建外部服务器或创建外部表时指定。

创建 postgres_fdw 扩展

CREATE EXTENSION postgres_fdw;
复制

创建没有batch_size的外部服务器

CREATE SERVER postgres_svr 
       FOREIGN DATA WRAPPER postgres_fdw 
       OPTIONS (host '127.0.0.1');
 
CREATE USER MAPPING FOR vagrant
       SERVER postgres_svr
       OPTIONS (user 'postgres', password 'pass');
 
CREATE FOREIGN TABLE foo_remote (a INTEGER,
                                 b CHAR,
                                 c TEXT,
                                 d VARCHAR(255))
       SERVER postgres_svr
       OPTIONS(table_name 'foo_local');
 
EXPLAIN (VERBOSE, COSTS OFF) insert into foo_remote values (generate_series(1, 1), 'c', 'text', 'varchar');
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Insert on public.foo_remote
   Remote SQL: INSERT INTO public.foo_local(a, b, c, d) VALUES ($1, $2, $3, $4)
   Batch Size: 1
   ->  ProjectSet
         Output: generate_series(1, 1), 'c'::character(1), 'text'::text, 'varchar'::character varying(255)
         ->  Result
(6 rows)
复制

未指定 batch_size 的执行时间

EXPLAIN ANALYZE 
        INSERT INTO foo_remote
        VALUES (generate_series(1, 100000000),
                'c',
                'text',
                'varchar');
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
Insert on foo_remote  (cost=0.00..500000.02 rows=0 width=0) (actual time=4591443.250..4591443.250 rows=0 loops=1)
   ->  ProjectSet  (cost=0.00..500000.02 rows=100000000 width=560) (actual time=0.006..31749.132 rows=100000000 loops=1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Planning Time: 4.988 ms
Execution Time: 4591447.101 ms -- timing is important
(5 rows)
复制

创建一个batch_size = 10的外部表，以防在服务器创建时没有指定batch_size

CREATE FOREIGN TABLE foo_remote (a INTEGER,
                                 b CHAR,
                                 c TEXT,
                                 d VARCHAR(255))
        SERVER postgres_svr OPTIONS(table_name 'foo_local', batch_size '10');
复制

创建一个batch_size = 10的外部服务器，现在该服务器的每个表都将使用batch_size 10

CREATE SERVER postgres_svr_bulk
       FOREIGN DATA WRAPPER postgres_fdw
       OPTIONS (host '127.0.0.1', batch_size = '10'); -- new option batch_size
 
CREATE USER MAPPING FOR vagrant
       SERVER postgres_svr
       OPTIONS (user 'postgres', password 'pass');
 
CREATE FOREIGN TABLE foo_remote (a INTEGER,
                                 b CHAR,
                                 c TEXT,
                                 d VARCHAR(255))
        SERVER postgres_svr OPTIONS(table_name 'foo_local');
 
EXPLAIN (VERBOSE, COSTS OFF) insert into foo_remote_bulk values (generate_series(1, 1), 'c', 'text', 'varchar');
 
                                                QUERY PLAN                                                 
 
-----------------------------------------------------------------------------------------------------------
Insert on public.foo_remote_bulk
   Remote SQL: INSERT INTO public.foo_local_bulk(a, b, c, d) VALUES ($1, $2, $3, $4)
   Batch Size: 10
   ->  ProjectSet
         Output: generate_series(1, 1), 'c'::character(1), 'text'::text, 'varchar'::character varying(255)
         ->  Result
(6 rows)
复制

batch_size = 10 的执行时间：

EXPLAIN ANALYZE
        INSERT INTO foo_remote_bulk
        VALUES (generate_series(1, 100000000),
                'c',
                'text',
                'varchar');
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Insert on foo_remote_bulk  (cost=0.00..500000.02 rows=0 width=0) (actual time=822224.678..822224.678 rows=0 loops=1)
   ->  ProjectSet  (cost=0.00..500000.02 rows=100000000 width=560) (actual time=0.005..10543.845 rows=100000000 loops=1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
 Planning Time: 0.250 ms
 Execution Time: 822239.178 ms -- timing is important
(5 rows)
复制

结论

PostgreSQL 正在扩展外部数据包装器的特性列表，批量插入是另一个很好的补充。由于此功能已添加到核心中，希望所有其他外部数据包装器也能实现它。

原文链接：percona.com/blog/2021/05/27/new-features-in-postgresql-14-bulk-inserts-for-foreign-data-wrappers/