PG 14中的postgres_fdw增强功能
Tags： PGPG14postgres_fdw新特性
👉 本文共约1785个字，系统预计阅读时间或需7分钟。
Foreign Data Wrappers（FDW）提供了一种机制，通过这种机制，可以使用常规SQL查询访问PostgreSQL之外的数据。PostgreSQL附带了“File FDW”和“PostgreSQL FDW”。PostgreSQL FDW可能看起来有悖常理，但它是一个非常有用的功能。这个版本的FDW有一些非常有用的更新。
所以，让我们开始了解发生了什么变化。
性能特征
如果您已经在使用PostgreSQL FDW，请注意性能的改进。
1 – 并行/异步 外部扫描
（允许一个查询引用多个外部表，并行执行外部表扫描）
当在多台服务器上执行远程聚合和远程连接时，可能会造成性能噩梦。现在可以异步并行执行。之前的顺序执行非常缓慢，在某些情况下，速度会很慢。为此，添加了一个新的选项async_capable，它允许并行计划和执行外部表扫描。
创建服务和用户映射
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Create foreign server 1. CREATE SERVER postgres_svr1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', async_capable "true"); -- Create foreign server 2. CREATE SERVER postgres_svr2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', async_capable "true"); CREATE USER MAPPING FOR vagrant SERVER postgres_svr1 OPTIONS (user 'postgres', password 'pass'); CREATE USER MAPPING FOR vagrant SERVER postgres_svr2 OPTIONS (user 'postgres', password 'pass'); |
创建本地表
1 2 3 4 5 6 | 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; |
创建外部表
1 2 3 4 5 6 7 8 9 | 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'); |
看看计划树，现在你可以在计划树中看到两个异步的外部扫描计划。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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中实现的。下面是一个例子。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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外部表。
1 2 | 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。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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，只能看到导入的父表
1 2 3 4 5 6 7 8 9 | 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子句中显式指定了分区表，那么它将导入该表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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') |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
3 – 活动和有效的连接列表
（添加postgres_fdw_get_connections函数以报告打开的外部服务连接）
添加了一个新函数postgres_fdw_get_connections()。该函数将打开的连接名本地会话返回到postgres_fdw的外部服务。它还输出连接的有效
1 2 3 4 5 6 7 | postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; server_name | valid -------------------+------- postgres_svr | t postgres_svr_bulk | t (2 rows) |
现在断开所有连接并重试查询。
1 2 3 4 5 6 7 8 9 10 11 | 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，则在事务结束时将丢弃连接。
关闭这个选项
1 | ALTER SERVER loopback OPTIONS (keep_connections 'off'); |
使用远程查询建立连接。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
1 | ALTER SERVER postgres_svr options (set keep_connections 'on'); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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提供了另一个理由。该功能肯定会在接下来的几个版本中添加，使这些版本更具性能，更易于使用。
