PostgreSQL 14中的postgres_fdw增强功能

本文抄自：

https://mp.weixin.qq.com/s/7XjPa-ZeU8mNCvcIwOTHrA

 

Foreign Data Wrappers（FDW）提供了一种机制，通过这种机制，可以使用常规SQL查询访问PostgreSQL之外的数据。PostgreSQL附带了“File FDW”和“PostgreSQL FDW”。PostgreSQL FDW可能看起来有悖常理，但它是一个非常有用的功能。这个版本的FDW有一些非常有用的更新。

所以，让我们开始了解发生了什么变化。

性能特征

如果您已经在使用PostgreSQL FDW，请注意性能的改进

1 – 并行/异步 外部扫描

（允许一个查询引用多个外部表，并行执行外部表扫描）

当在多台服务器上执行远程聚合和远程连接时，可能会造成性能噩梦。现在可以异步并行执行。之前的顺序执行非常缓慢，在某些情况下，速度会很慢。为此，添加了一个新的选项async_capable，它允许并行计划和执行外部表扫描。

创建服务和用户映射

-- Create foreign server 1.CREATE SERVER postgres_svr1FOREIGN DATA WRAPPER postgres_fdwOPTIONS (host '127.0.0.1', async_capable "true");
-- Create foreign server 2.CREATE SERVER postgres_svr2FOREIGN DATA WRAPPER postgres_fdwOPTIONS (host '127.0.0.1', async_capable "true");
CREATE USER MAPPING FOR vagrantSERVER postgres_svr1OPTIONS (user 'postgres', password 'pass');
CREATE USER MAPPING FOR vagrantSERVER postgres_svr2OPTIONS (user 'postgres', password 'pass');

创建本地表

CREATE TABLE parent_local (a INTEGER, b CHAR, c TEXT, d VARCHAR(255)) PARTITION BY RANGE (a);
CREATE TABLE child_local1 (a INTEGER, b CHAR, c TEXT, d VARCHAR(255));
CREATE TABLE child_local2 (a int, b CHAR, c text, d VARCHAR(255));
GRANT ALL ON child_local1 to postgres;
GRANT ALL ON child_local2 to postgres;

创建外部表

CREATE FOREIGN TABLE parent_remote1
PARTITION OF parent_local VALUES FROM 1000 TO 2000
SERVER postgres_svr1 OPTIONS table_name 'child_local1');

CREATE FOREIGN TABLE parent_remote2
PARTITION OF parent_local
FOR VALUES FROM 2000 TO 3000
SERVER postgres_svr2 OPTIONS table_name 'child_local2');

看看计划树，现在你可以在计划树中看到两个异步的外部扫描计划。

CREATE TABLE sample_table (a INTEGER, b CHAR, c TEXT, d VARCHAR(255));

EXPLAIN (VERBOSE, COSTS OFF)
INSERT INTO sample_table SELECT * FROM parent_local WHERE a % 100 = 0;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Insert on public.sample_table
   ->  Append
         ->  Async Foreign Scan on public.parent_remote1 parent_local_1
               Output: parent_local_1.a, parent_local_1.b, parent_local_1.c, parent_local_1.d
               Remote SQL: SELECT a, b, c, d FROM public.child_local1 WHERE (((a % 100) = 0))
         ->  Async Foreign Scan on public.parent_remote2 parent_local_2
               Output: parent_local_2.a, parent_local_2.b, parent_local_2.c, parent_local_2.d
               Remote SQL: SELECT a, b, c, d FROM public.child_local2 WHERE (((a % 100) = 0))
(8 rows)

2 – 批量插入

（允许postgres_fdw批量插入行。）

现在，批量插入功能已添加到FDW中，postgres_fdw现在支持该功能。其他FDW也有机会实现批量插入。你可以在这里看到。

功能特性

1 – TRUNCATE 命令

（允许TRUNCATE对外部表进行操作）

这意味着它会向外部服务器出TRUNCATE命令，并在表上执行。这个功能是在postgres_fdw中实现的。下面是一个例子。

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');

现在，可以TRUNCATE外部表。

postgres=# TRUNCATE foo_remote;
TRUNCATE TABLE

2 – LIMIT TO 子分区

（如果指定IMPORT FOREIGN SCHEMA … LIMIT TO，则允许postgres_fdw导入表分区。）

postgres_fdw不允许导入表分区，因为可以使用根分区访问数据。但是，如果用户想要导入分区表分区，PostgreSQL 14添加了一个新的选项LIMIT TO。

在远程计算机上创建一个新schema，并添加一个父表foo_schema.foo_table_parent和一个子表foo_schema.foo_table_child。

postgres=# \d+ foo_schema.*
                                   Table "foo_schema.foo_table_child"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           |          |         | plain   |             |              | 
Partition of: foo_schema.foo_table_parent FOR VALUES FROM (0) TO (10)

Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 10))
Access method: heap

                            Partitioned table "foo_schema.foo_table_parent"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           |          |         | plain   |             |              | 
Partition key: RANGE (a)
Partitions: foo_schema.foo_table_child FOR VALUES FROM (0) TO (10)

在不指定LIMIT TO的情况下导入schema，只能看到导入的父表。

IMPORT FOREIGN SCHEMA foo_schema FROM SERVER postgres_svr INTO bar_schema;
postgres=# \d+ bar_schema.*
                                 Foreign table "bar_schema.foo_table_parent"
 Column |  Type   | Collation | Nullable | Default |    FDW options    | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+-------------------+---------+--------------+-------------
 a      | integer |           |          |         | (column_name 'a') | plain   |              | 
Server: postgres_svr
FDW options: (schema_name 'foo_schema', table_name 'foo_table_parent')

如果您在LIMIT TO子句中显式指定了分区表，那么它将导入该表。

postgres=# IMPORT FOREIGN SCHEMA foo_schema LIMIT TO (foo_table_parent, foo_table_child)
            FROM SERVER loopback INTO bar_schema;
IMPORT FOREIGN SCHEMA

postgres=# \d+ bar_schema.*
                                  Foreign table "bar_schema.foo_table_child"
 Column |  Type   | Collation | Nullable | Default |    FDW options    | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+-------------------+---------+--------------+-------------
 a      | integer |           |          |         | (column_name 'a') | plain   |              | 
Server: loopback
FDW options: (schema_name 'foo_schema', table_name 'foo_table_child')

                                 Foreign table "bar_schema.foo_table_parent"

 Column |  Type   | Collation | Nullable | Default |    FDW options    | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+-------------------+---------+--------------+-------------
 a      | integer |           |          |         | (column_name 'a') | plain   |              | 
Server: loopback
FDW options: (schema_name 'foo_schema', table_name 'foo_table_parent')

3 – 活动和有效的连接列表

（添加postgres_fdw_get_connections函数以报告打开的外部服务连接）

添加了一个新函数postgres_fdw_get_connections()。该函数将打开的连接名本地会话返回到postgres_fdw的外部服务。它还输出连接的有效性。

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
    server_name    | valid 
-------------------+-------
 postgres_svr      | t
 postgres_svr_bulk | t
(2 rows)

现在断开所有连接并重试查询。

postgres=# SELECT 1 FROM postgres_fdw_disconnect_all();
 ?column? 
----------
        1
(1 row)

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
 server_name | valid 
-------------+-------
(0 rows)

4 – 保持连接

（添加丢弃缓存的连接功能）

添加了一个新选项keep_connections，以保持连接处于活动状态，以便后续查询可以重用它们。默认情况下，此选项处于on状态，但如果off，则在事务结束时将丢弃连接。

关闭这个选项

ALTER SERVER loopback OPTIONS (keep_connections 'off');

使用远程查询建立连接。

postgres=# BEGIN;
BEGIN

postgres=*# select * from foo_remote;
 a | b | c | d 
---+---+---+---
(0 rows)

postgres=*# END;
COMMIT

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
 server_name | valid 
-------------+-------
(0 rows)

设置 keep_connections 选项为 on

ALTER SERVER postgres_svr options (set keep_connections 'on');

postgres=# BEGIN;
BEGIN
postgres=*# select * from foo_remote;
 a | b | c | d 
---+---+---+---
(0 rows)

-- Establish the connection using the remote query.

postgres=*# END;
COMMIT

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
 server_name  | valid 
--------------+-------
 postgres_svr | t
(1 row)

5 – 重建断开连接

（必要时允许postgres_fdw重新建立外部服务连接）

以前，当远程服务重新启动并且postgres_fdw连接断开时，由于缓存的连接不再可用，因此引发了错误。这在PostgreSQL中是固定的，在任何情况下，连接都会断开并且不再存在于缓存中，postgres_fdw将重新建立连接。

 

结论

FDW API很有希望在每个版本中都得到扩展，但PostgreSQL 14提供了一些以用户为中心的新功能。与性能相关的改进为许多相关用例使用FDW提供了另一个理由。该功能肯定会在接下来的几个版本中添加，使这些版本更具性能，更易于使用。