当前位置：首页 > news >正文

postgresql的SQL或MED

news 2026/5/27 5:29:06

Postgresql的SQL或MED

[TOP]

PostgreSQL 中的 SQL/MED（SQL Management of External Data）是 ISO/IEC 9075-9 标准的一部分，用于通过标准 SQL 语句访问和管理存储在数据库之外的数据源。PostgreSQL 从 9.1 版开始通过 Foreign Data Wrapper（FDW）机制实现了该标准，使用户可以像查询本地表一样查询远程或异构数据。

一、SQL/MED 的核心概念

组件	作用
Foreign Data Wrapper（FDW）	外部数据包装器，定义如何访问某种外部数据源（如 MySQL、Oracle、文件、Web 服务等）。
Foreign Server	外部服务器，表示一个外部数据源的连接配置（如 IP、端口、数据库名）。
User Mapping	用户映射，定义本地用户如何以哪个外部用户身份访问外部数据源。
Foreign Table	外部表，映射到外部数据源中的某个对象（如远程表、文件、API 接口等），可像普通表一样查询。

二、PostgreSQL 中 SQL/MED 的实现

PostgreSQL 的 SQL/MED 实现基于 FDW 插件机制，支持以下功能：

只读/读写访问：9.1 支持只读，9.3 起支持写入（如 INSERT/UPDATE/DELETE）。
多种数据源：支持 PostgreSQL、MySQL、Oracle、MongoDB、CSV 文件、Web API 等。
性能优化：支持 WHERE 条件下推、JOIN 下推、统计信息收集等。

三、典型应用场景

1.跨库查询：无需 ETL，直接通过 SQL 查询远程 PostgreSQL 或其他数据库。
2.数据集成：将多个异构系统（如 MySQL + MongoDB + CSV）统一接入 PostgreSQL。
3.数据迁移：通过外部表快速将数据从旧系统导入 PostgreSQL。
4.联邦查询：PostgreSQL 作为查询网关，统一访问多个数据源。

四. 执行流程（以 postgres_fdw 为例）

1. Parse 阶段：外表的列定义从 pg_foreign_table 系统表读出，校验语法。
1. Planner 阶段：
- ① 调用 FDW 的 GetForeignRelSize 估算行数、宽度；
- ② GetForeignPaths 生成候选访问路径（顺序、索引、JOIN 顺序、COST）；
- ③ GetForeignPlan 生成 ForeignScan 节点，决定是否把 WHERE、JOIN、AGG、ORDER BY、LIMIT 下推。
3.Executor 阶段：
- ① BeginForeignScan 建立远程会话（libpq）；
- ② 如果可下推，则拼成远程 SQL 发出去；否则把外表当“行源”拉回本地再过滤；
- ③ 游标式批量抓行（fetch_size 可配），支持可写事务（INSERT/UPDATE/DELETE 走 ForeignModify）。
4.Commit 阶段：两阶段提交（2PC）可配，保证跨库一致性。

五、核心特性

列/条件下推：减少网络往返，支持将 WHERE、JOIN、AGG、ORDER BY、LIMIT 推到远端。
事务代理：本地 BEGIN/COMMIT/ROLLBACK 会同步到远端；支持 synchronous_commit 级别。
可写外表：INSERT/UPDATE/DELETE 可回写远程，支持 RETURNING。
并行查询：PG 14+ 支持外表并行扫描（parallel_workers）。
IMPORT FOREIGN SCHEMA：一键把远程整个 schema 映射成外表，省去手写 100 张表定义。
JOIN 下推：PG 11+ 可以把两副外表的 JOIN 直接下推到远程执行，极大减少拉回数据量。

四、简单示例（以 postgres_fdw 为例）

user01=> create table testtab01(id int, note text);
CREATE TABLE
user01=> insert into testtab01 select generate_series(1,10000),
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
INSERT 0 10000在本地数据库中安装postgres_fdw插件，建外部数据表，命令如下：
CREATE EXTENSION postgres_fdw;建外部数据服务器，命令如下：
CREATE SERVER postgres_fdw_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '10.0.3.236', dbname 'user01', port '5432');建用户映射，指定连接远程数据库的用户名和密码，命令如下：
CREATE USER MAPPING FOR CURRENT_USER
SERVER postgres_fdw_server
OPTIONS ( user 'user01', password 'okuser01');创建外部表，命令如下：
CREATE FOREIGN TABLE fttest01 (
id int,
note text
) SERVER postgres_fdw_server
OPTIONS (table_name 'testtab01');查询外部表，命令如下：
osdba=# select * from fttest01 limit 2;
id | note
----+----------------------------------------
1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
(2 rows)查看查询外部表的SQL命令的执行计划，命令如下：
osdba=# explain select * from fttest01 limit 2;
QUERY PLAN
--------------------------------------------------------------------------
Limit (cost=100.00..100.07 rows=2 width=36)
-> Foreign Scan on fttest01 (cost=100.00..150.95 rows=1365 width=36)
(2 rows)