PHP小码农

Appearance

FDW 性能优化 ​

前面几节我们把 FDW 搭起来了，也能查出正确结果了。但"能查"和"查得快"之间往往差着几个数量级。一条跨库查询从 200ms 变成 20 秒，最常见的原因不是网络慢，而是该下推的条件没有下推、统计信息不准导致优化器选错计划、或者一次查询发了几万次网络往返。

本节专门讲 FDW 的性能。核心方法论只有一句话：让远端干尽可能多的活，让网络传尽可能少的行。围绕这句话，我们会讲透：

  • 什么是下推（pushdown），哪些表达式是"可下推（shippable）"的；
  • 如何用 EXPLAIN 诊断下推有没有生效；
  • 三个关键估算参数（fdw_startup_cost、fdw_tuple_cost、fetch_size）怎么调；
  • 统计信息为什么在 FDW 场景下格外重要；
  • 连接管理、批量写入、异步与并行提交的使用时机；
  • 以及"什么时候该承认 FDW 不合适，改走 ETL"。

前置知识

阅读本节前，建议先了解：其他第三方 FDW

基础概念 ​

下推：FDW 性能的一切 ​

下推（pushdown） 指：本地优化器把查询的一部分"翻译"成远端能执行的语句，让远端先算，只把结果传回本地。

text
无下推（最坏情况）                    有下推（理想情况）
┌──────────────┐                    ┌──────────────┐
│ 远端全表 1 亿行│                    │ 远端全表 1 亿行│
└──────┬───────┘                    └──────┬───────┘
       │ 传输 1 亿行                       │ 远端先过滤/聚合
       │ （可能几 GB）                     │ → 只剩 200 行
       ▼                                  ▼
┌──────────────┐                    ┌──────────────┐
│ 本地逐行过滤   │                    │ 传输 200 行    │
│ 本地排序/聚合  │                    │ （几 KB）      │
└──────────────┘                    └──────────────┘
   耗时：数十秒                        耗时：几十毫秒

差距的来源很清楚：网络传输量。本地 CPU 再快，也顶不住把几 GB 数据从网络搬一遍。

可下推表达式（shippable expression） ​

并非所有 SQL 构造都能下推。一个表达式"可下推"，意味着它能被安全地转换成远端等价语句。判断标准主要是：

  • 用到的函数在远端也存在且语义一致；
  • 用到的操作符在远端也存在且语义一致；
  • 涉及的类型与排序规则在两端一致（否则比较语义可能不同）。

postgres_fdw 内置了一张"安全函数/操作符"清单，只下推它认为绝对安全的。扩展提供的函数（如 PostGIS）默认不在清单里——这就是 extensions 选项存在的意义。

下推能力的层级 ​

不同下推类型的收益不同。按"收益从高到低"排：

下推类型收益说明
LIMIT / OFFSET极高让远端只返回前 N 行，避免拉全表
聚集（GROUP BY + 聚合函数）极高把百万行压成几十行再传输
WHERE 过滤很高减少传输行数
JOIN（同远端两表）很高避免把两张大表都拉到本地
DISTINCT高去重后传输量可能大幅下降
ORDER BY中省掉本地排序，但传输量不变
表达式计算（如 upper(x)）低省一点本地 CPU

优化顺序建议：先确保 WHERE、LIMIT、聚集能下推；其次是 JOIN；最后才关心 ORDER BY 和表达式。

FDW 查询的三个性能瓶颈 ​

一次外部表查询的耗时可以拆成三部分：

text
总耗时 = 远端执行时间 + 网络传输时间 + 本地处理时间
         ▲              ▲              ▲
         │              │              │
     缺索引/全表扫   下行行数×行宽    未下推的
     远端本身慢      往返次数        过滤/排序/聚合

对应三类优化手段：远端执行（确保远端有合适索引、别把过滤留本地）、网络传输（提高下推程度、调整 fetch_size、避免 SELECT * 拉宽行）、本地处理（尽量让本地只做"接收 + 轻量合并"）。

第三项最容易被忽视：很多人只关注"远端快不快"，却忘了看"本地到底在处理多少行"。

连接缓存与连接数 ​

postgres_fdw 为每个会话维护到远端的连接缓存。这意味着：

  • 同一会话的连续查询复用连接，握手成本只付一次；
  • 不同会话各自建连接，100 个应用连接并发访问外部表 = 远端多 100 个连接；
  • 不同 user mapping 各占一条连接。
text
本地连接池（100 个连接）
   │
   ├─ 会话 1 ──► 远端连接 1
   ├─ 会话 2 ──► 远端连接 2
   ├─ ...
   └─ 会话 100 ─► 远端连接 100      ← 远端 max_connections 可能被撑爆

这是 FDW 最常见的生产事故：本地一切正常，远端因为连接数耗尽开始拒绝服务。评估 FDW 时，必须把远端连接数纳入容量规划。

语法与用法 ​

查看执行计划中的远端语句 ​

诊断下推的第一工具是 EXPLAIN (VERBOSE)：

sql
EXPLAIN (VERBOSE, COSTS OFF)
SELECT tenant_id, sum(amount)
FROM remote_orders
WHERE status = 'paid' AND created_at >= now() - interval '7 days'
GROUP BY tenant_id
ORDER BY sum(amount) DESC
LIMIT 10;

关键是看输出的 Remote SQL 行——它是实际发给远端的语句：

text
                                  QUERY PLAN
------------------------------------------------------------------------------
 Limit
   Output: tenant_id, (sum(amount))
   ->  Sort
         Output: tenant_id, (sum(amount))
         Sort Key: (sum(remote_orders.amount)) DESC
         ->  Foreign Scan
               Output: tenant_id, (sum(amount))
               Relations: Aggregate on (public.remote_orders)
               Remote SQL: SELECT tenant_id, sum(amount) FROM public.orders
                           WHERE ((status = 'paid') AND (created_at >= (now() - '7 days'::interval)))
                           GROUP BY tenant_id ORDER BY (sum(amount)) DESC NULLS LAST LIMIT 10

如果 Remote SQL 里只有 SELECT tenant_id, amount, status, created_at FROM public.orders，没有 WHERE、没有 GROUP BY、没有 LIMIT，那就说明下推全部失败了——这时你是在把整张远端表拉回本地。

用 EXPLAIN ANALYZE 看真实数据量 ​

COSTS OFF 看结构，ANALYZE 看真实数字：

sql
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT tenant_id, sum(amount)
FROM remote_orders
WHERE status = 'paid'
GROUP BY tenant_id;

关注输出里的 rows= 与实际返回行数。如果 Foreign Scan 节点返回的行数与远端表总行数接近，说明过滤没有下推。

调整估算参数与批大小 ​

sql
-- 提高"进入外部扫描"的门槛，让优化器更谨慎
ALTER SERVER remote_pg
    OPTIONS (SET fdw_startup_cost '1000', SET fdw_tuple_cost '0.5');

-- 读：每次取 2000 行；写：每批 500 行
ALTER FOREIGN TABLE remote_orders OPTIONS (SET fetch_size '2000');
ALTER FOREIGN TABLE remote_orders OPTIONS (SET batch_size '500');

-- 对特定表改用远端实时估算
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD use_remote_estimate 'true');

-- 启用异步扫描与并行提交/回滚
ALTER SERVER remote_pg
    OPTIONS (ADD async_capable 'true',
             ADD parallel_commit 'true',
             ADD parallel_abort 'true');

-- 收集统计信息（外部表统计不会自动保持新鲜）
ANALYZE remote_orders;
ANALYZE remote_order_items;

详细说明 ​

哪些能下推，哪些不能 ​

这是本节最需要背下来的内容。基于 postgres_fdw 的实际能力：

SQL 构造能否下推备注
WHERE 常量过滤✅最常见也最有效
WHERE 内置函数过滤✅如 lower(x) = 'a'
WHERE 扩展函数过滤❌（默认）需 extensions 声明
JOIN（两表在同一远端）✅前提是 JOIN 条件可下推
JOIN（跨不同远端/本地）❌必须本地执行
GROUP BY + 内置聚合✅sum/count/min/max/avg 等
GROUP BY + 自定义聚合❌本地执行
ORDER BY✅需排序规则一致
LIMIT / OFFSET✅需与 ORDER BY 配合才有意义
DISTINCT✅
窗口函数（row_number() 等）⚠️ 通常下推不了取决于具体实现与版本
子查询（相关子查询）⚠️ 部分简单形式可下推
集合操作（UNION / INTERSECT）⚠️ 部分
SELECT *✅（作为投影）但会拉宽行，不利传输

最需要警惕的是"看起来能下推、实际不行"的情况：跨远端 JOIN、涉及本地自定义函数、排序规则不一致、类型不一致。这四类都会让优化器静默地把数据拉回本地。

排序规则与类型不一致会阻断下推 ​

两个非常隐蔽的陷阱，都会让本来该下推的语句被静默地拉回本地。

排序规则不一致：假设远端以 C 排序规则初始化，本地是 en_US.UTF-8：

sql
-- 两端排序规则不同 → 排序结果可能不一致 → 不下推，改为本地排序
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM remote_orders ORDER BY status;

输出里会出现本地 Sort 节点，而 Remote SQL 中没有 ORDER BY。修复方式是让两端一致：

sql
-- 显式声明列的排序规则，与远端保持一致
CREATE FOREIGN TABLE remote_orders (
    status text COLLATE "C"
) SERVER remote_pg OPTIONS (schema_name 'public', table_name 'orders');

类型不一致：远端 amount 是 numeric，本地声明成 text，比较时会引入转换，同样可能阻断下推。

sql
-- 错误！本地声明为 text，与远端的 numeric 不一致
CREATE FOREIGN TABLE bad_orders (amount text)
SERVER remote_pg OPTIONS (schema_name 'public', table_name 'orders');

最佳实践：外部表列类型必须与远端完全一致（包括精度、长度、是否带时区）。用 IMPORT FOREIGN SCHEMA 自动生成能避免这类问题，因为它读取的是远端的真实类型。

fdw_startup_cost 与 fdw_tuple_cost 的调优逻辑 ​

这两个参数本质上是给优化器的一剂"心理暗示"：告诉它"外部扫描比本地扫描贵"。

sql
-- 默认值
-- fdw_startup_cost = 100
-- fdw_tuple_cost   = 0.2

调优思路：

情况调整方向
优化器总选外部表扫描，但实际很慢调大两个值
优化器总避开外部表，但实际很快调小两个值
高延迟链路（跨机房）调大 fdw_startup_cost
每行开销大（行很宽）调大 fdw_tuple_cost
sql
-- 跨机房场景：强调连接建立成本高
ALTER SERVER remote_pg_remote_region
    OPTIONS (SET fdw_startup_cost '5000', SET fdw_tuple_cost '0.5');

注意：这两个值不是"毫秒"，而是与本地代价单位同量纲的抽象代价。调参要靠 EXPLAIN 前后对比验证，不要凭感觉。

use_remote_estimate 的取舍 ​

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD use_remote_estimate 'true');

开启后，postgres_fdw 会在生成计划阶段向远端发送 EXPLAIN，用远端的真实估算来算代价。

维度false（默认）true
计划生成速度快每次多一次网络往返
估算准确性依赖本地缓存的统计信息使用远端实时估算
适用场景简单查询、统计稳定复杂 JOIN、远端数据分布多变

推荐策略：

  • 简单查询保持默认 false；
  • 涉及多表 JOIN、且曾观察到计划劣化的表，在表级开启 true；
  • 不要在 server 级全局开启，否则每个查询都要多付一次 EXPLAIN 往返。

统计信息：比参数更重要的东西 ​

FDW 场景下，统计信息的重要性高于一切调参。原因：优化器要决定 JOIN 顺序、连接算法、是否下推，全靠行数估算。估算错了，再好的参数也救不回来。

sql
-- 定期收集
ANALYZE remote_orders;

-- 查看统计信息是否已收集
SELECT relname, reltuples, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'remote_orders';

-- 控制采样方式（远端表极大、或物理分布与逻辑分布不一致时有用）
ALTER FOREIGN TABLE remote_orders OPTIONS (SET analyze_sampling 'random');

注意：外部表的 ANALYZE 需要能够访问远端（或文件）。对 postgres_fdw，它会去远端采样或使用远端统计；对 file_fdw，它需要读取文件。另外，外部表很少被自动 ANALYZE，建议用运维任务定期执行。

fetch_size：读路径的批大小 ​

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (SET fetch_size '2000');

默认 100，决定每次往返取回多少行。取 10 万行时，fetch_size = 100 需要 1000 次往返，fetch_size = 5000 只需 20 次——高延迟链路上差距是数量级的。

场景建议
局域网（<1ms 延迟）默认 100 尚可，可调到 1000
跨机房（5~30ms 延迟）调到 2000~5000
带 LIMIT 的小查询不要调太大，否则可能多取
行非常宽（大文本/JSON）调小，避免单批内存占用过高

注意：fetch_size 只影响 SELECT。写入路径由 batch_size 控制。

batch_size：写路径的批大小 ​

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (SET batch_size '500');

默认 1，即逐行发送。批量插入 10 万行时，默认值意味着 10 万次往返，而 batch_size = 500 只需 200 次。推荐做法是临时调大、写完恢复：

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (SET batch_size '500');
INSERT INTO remote_orders SELECT * FROM staging;
ALTER FOREIGN TABLE remote_orders OPTIONS (SET batch_size '1');

注意：批量写入会占用远端更多内存（一批缓存在内存中）。batch_size 不是越大越好，500~1000 是常见的安全区间。

另外，对"把大量数据灌入外部表"的场景，COPY 通常比 INSERT 更高效——它走的是批量协议而非逐行协议。注意 COPY 的目标是外部表时，文件仍需要服务器进程可读（与 file_fdw 相同的权限约束）。

异步执行：async_capable ​

sql
ALTER SERVER remote_pg OPTIONS (ADD async_capable 'true');

默认 false。开启后，对同一远端的多个外部表扫描可以异步并行。典型受益场景：一条查询需要扫描同一远端的 3 张外部表（例如三表 UNION 或多次扫描），默认情况下它们串行往返；开启后可以并发发出，代价是同一会话可能同时持有多个远端连接。

并行提交/回滚：parallel_commit / parallel_abort ​

sql
ALTER SERVER remote_pg OPTIONS (ADD parallel_commit 'true', ADD parallel_abort 'true');

默认均为 false。parallel_commit 在本地 COMMIT 时并行提交所有涉及的远端事务，parallel_abort 在本地回滚时并行回滚所有远端事务。多个远端的写入事务（如"同时写 A 库和 B 库"）下收益明显：三次远端提交从串行变为并发。前提条件是远端必须启用两阶段提交：

ini
# 远端 postgresql.conf：默认为 0（禁用）
max_prepared_transactions = 100

两阶段提交的性能代价 ​

顺带说一个反向的优化点：如果不需要跨库原子性，就不要触发 2PC。什么时候会触发？在一个本地事务里修改了两个及以上远端。

sql
-- 这会在提交时走 2PC（三次 PREPARE + COMMIT PREPARED）
BEGIN;
INSERT INTO remote_a ...;
INSERT INTO remote_b ...;
COMMIT;

若业务允许"两个远端各自独立提交"，可以拆成两个独立事务，省掉 2PC 的额外往返：

sql
-- 拆成两个事务，避免 2PC
BEGIN; INSERT INTO remote_a ...; COMMIT;
BEGIN; INSERT INTO remote_b ...; COMMIT;

反过来说，如果确实需要原子性，就必须付这个代价——正确性优先于性能。

连接管理与连接数控制 ​

FDW 连接数问题在生产中非常真实。几个缓解手段：

为报表类访问单独建 server，这样连接池可以独立限制：

sql
-- 报表专用 server，与业务访问隔离
CREATE SERVER report_pg FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '10.0.0.5', port '5432', dbname 'report', updatable 'false');

限制访问外部表的角色数量，避免大量并发会话各自建连。

在远端设置合理的 max_connections，并为 FDW 访问预留配额。

使用连接池（如 PgBouncer）时要注意：postgres_fdw 的连接由本地会话持有。若本地也走连接池（例如 transaction 模式），会话的建立/释放会更频繁，FDW 连接缓存可能失效，导致远端连接数波动更大。

监控远端连接来源：

sql
-- 在远端执行，查看来自 FDW 的连接
SELECT client_addr, count(*), state
FROM pg_stat_activity
WHERE application_name = 'postgres_fdw' OR backend_type = 'client backend'
GROUP BY client_addr, state
ORDER BY count(*) DESC;

减少传输量的其他手段 ​

sql
-- 不推荐：拉回所有列，网络传输量翻倍
SELECT * FROM remote_orders;
-- 推荐：只取需要的列
SELECT id, amount FROM remote_orders WHERE status = 'paid';

-- 不推荐：把明细拉回本地聚合
SELECT tenant_id, sum(amount) FROM (SELECT * FROM remote_orders) x GROUP BY tenant_id;
-- 推荐：让远端聚合（下推）
SELECT tenant_id, sum(amount) FROM remote_orders GROUP BY tenant_id;

-- 不推荐：本地函数包裹导致无法下推
SELECT * FROM remote_orders WHERE my_local_func(status);
-- 推荐：使用两端都有的内置函数
SELECT * FROM remote_orders WHERE status = 'paid';

远端建索引：这一点容易被忘记——FDW 的过滤条件最终要在远端执行，远端没索引照样全表扫。

sql
-- 在远端执行
CREATE INDEX ON orders (status, created_at);
CREATE INDEX ON orders (tenant_id);

何时该承认 FDW 不合适 ​

FDW 的性能优化有天花板。出现以下信号时，应该考虑改方案：

信号建议方案
Remote SQL 永远下推不了关键条件第三方 FDW 能力不足 → ETL
每次查询都拉回数百万行数据量超出 FDW 适用范围 → ETL/数仓
远端连接数持续打满架构问题 → 逻辑复制/物化
跨源 JOIN 占查询耗时 90%把其中一方同步到本地
需要本地索引加速逻辑复制或 ETL 落库
远端抖动直接拖垮本地业务核心链路必须本地化

判断原则：如果优化的方向是"不断调参数去迁就它"，那说明这个场景本质上不适合 FDW。FDW 适合"少搬数据"的场景，不适合"必须搬很多数据"的场景。

一个完整的优化流程 ​

把前面的内容串成一个可执行的检查清单：

text
第 1 步：看 Remote SQL
        EXPLAIN (VERBOSE) → 确认 WHERE / GROUP BY / LIMIT / JOIN 是否都下推
        没下推 → 查原因（类型？排序规则？扩展函数？跨远端？）

第 2 步：看数据量
        EXPLAIN (ANALYZE) → 看 Foreign Scan 实际返回多少行
        返回行数 ≈ 远端总行数 → 过滤没生效

第 3 步：看统计信息
        外部表是否 ANALYZE 过？reltuples 是否合理？
        计划明显选错 → 表级开 use_remote_estimate

第 4 步：调网络参数
        高延迟 → 调大 fetch_size
        批量写 → 调大 batch_size
        多远端写 → parallel_commit

第 5 步：优化远端
        远端是否缺索引？
        远端是否有慢查询？

第 6 步：还不行就换方案
        ETL / 逻辑复制 / 本地物化

实战示例 ​

示例一：诊断一次"慢得离谱"的跨库查询 ​

问题现象：一条本该秒级的统计查询跑了 40 秒。

sql
SELECT status, count(*), sum(amount)
FROM remote_orders
WHERE created_at >= now() - interval '30 days'
GROUP BY status;

先看计划：

sql
EXPLAIN (VERBOSE, COSTS OFF)
SELECT status, count(*), sum(amount)
FROM remote_orders
WHERE created_at >= now() - interval '30 days'
GROUP BY status;
text
                             QUERY PLAN
--------------------------------------------------------------------
 HashAggregate
   Output: status, count(*), sum(amount)
   Group Key: remote_orders.status
   ->  Foreign Scan on public.remote_orders
         Output: id, tenant_id, amount, status, created_at
         Remote SQL: SELECT id, tenant_id, amount, status, created_at FROM public.orders
(5 rows)

问题一目了然：Remote SQL 里没有任何 WHERE、没有 GROUP BY，等于把远端 orders 全表拉回本地聚合。远端有 800 万行。

排查原因：检查外部表的列类型是否与远端一致。

sql
\d remote_orders
text
              Foreign table "public.remote_orders"
   Column   |            Type             | Collation | Nullable
------------+-----------------------------+-----------+----------
 created_at | timestamp without time zone |           |

远端实际类型是 timestamp with time zone，而本地声明成了 timestamp without time zone（少了 with time zone）。类型不一致导致 now() - interval '30 days' 的比较无法下推。

修复：

sql
-- 用 IMPORT FOREIGN SCHEMA 重新生成正确的列类型
DROP FOREIGN TABLE remote_orders;

IMPORT FOREIGN SCHEMA public LIMIT TO (orders)
    FROM SERVER remote_pg INTO public;

ANALYZE remote_orders;

同样检查计划：

sql
EXPLAIN (VERBOSE, COSTS OFF)
SELECT status, count(*), sum(amount)
FROM remote_orders
WHERE created_at >= now() - interval '30 days'
GROUP BY status;
text
                             QUERY PLAN
--------------------------------------------------------------------
 Foreign Scan
   Output: status, (count(*)), (sum(amount))
   Relations: Aggregate on (public.remote_orders)
   Remote SQL: SELECT status, count(*), sum(amount) FROM public.orders
               WHERE (created_at >= (now() - '30 days'::interval))
               GROUP BY status
(4 rows)

现在过滤和聚合都下推了，远端只返回几行（每个 status 一行）。查询从 40 秒降到不到 200ms。

这个案例的教训：类型不一致导致的"静默不下推"，是最常见也最隐蔽的 FDW 性能问题。修复成本很低（重新导入），但不知道要去看 Remote SQL 就完全找不到方向。

示例二：优化跨机房报表查询的往返次数 ​

场景：本地在北京，远端在上海，RTT（往返时延）约 20ms。查询需要每次分页返回 50 行，总共翻 200 页。

sql
-- 每页查询（第 n 页）
SELECT id, tenant_id, amount, created_at
FROM remote_orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

远端索引已建好，WHERE、ORDER BY、LIMIT/OFFSET 都能下推。但每页仍要传输 5000+50 行吗？不会——下推的 OFFSET 在远端执行，只返回 50 行。

真正的瓶颈是往返次数：200 页 × 20ms RTT = 4 秒纯网络开销（这已经是最优情况）。若下推失败，则每次要传几千行，网络时间会放大几十倍。

优化手段一：调大 fetch_size，避免后端在单页内多次往返。

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (SET fetch_size '1000');

优化手段二：批量拉取，而不是逐页拉取。把多页合并成一次查询。

sql
-- 一次取回前 1000 行，应用层分页
SELECT id, tenant_id, amount, created_at
FROM remote_orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 1000;

优化手段三：如果报表是固定形态，用本地物化表 + 增量刷新替代实时查询，把 RTT 成本降到最低。

sql
-- 本地物化一张摘要表，定期刷新
CREATE TABLE local_orders_summary (
    tenant_id  bigint,
    day        date,
    cnt        bigint,
    total      numeric(14,2),
    PRIMARY KEY (tenant_id, day)
);

-- 刷新（每晚）
INSERT INTO local_orders_summary (tenant_id, day, cnt, total)
SELECT tenant_id,
       created_at::date,
       count(*),
       sum(amount)
FROM remote_orders
WHERE created_at >= current_date - 7
GROUP BY tenant_id, created_at::date
ON CONFLICT (tenant_id, day) DO UPDATE
    SET cnt = EXCLUDED.cnt, total = EXCLUDED.total;

这里把"聚合下推 + 小结果集传输"的优势发挥到极致：无论远端有多少行，网络只传"租户 × 天数"量级的汇总数据。

示例三：批量写入的性能对比与调优 ​

场景：把本地 20 万行暂存数据写入远端外部表。

默认配置（batch_size = 1）：

sql
\timing on
INSERT INTO remote_orders (id, tenant_id, amount, created_at)
SELECT id, tenant_id, amount, created_at FROM staging_orders;
text
INSERT 0 200000
Time: 184320.551 ms (03:04.321)

调大 batch_size：

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD batch_size '500');

INSERT INTO remote_orders (id, tenant_id, amount, created_at)
SELECT id, tenant_id, amount, created_at FROM staging_orders;
text
INSERT 0 200000
Time: 9841.220 ms (00:09.841)

从 3 分 4 秒降到 9.8 秒，约 19 倍提升。差异全部来自"往返次数 20 万次 → 400 次"。

再用 COPY 对比：

sql
-- 先把暂存数据导出为 CSV（本地）
COPY staging_orders TO '/data/staging_orders.csv' WITH (FORMAT csv);
sql
-- 再从文件直接灌入外部表
\timing on
COPY remote_orders (id, tenant_id, amount, created_at)
FROM '/data/staging_orders.csv' WITH (FORMAT csv);
text
COPY 200000
Time: 6420.118 ms (00:06.420)

性能阶梯：

方式耗时相对倍数
INSERT（batch_size=1，默认）184.3 s1×
INSERT（batch_size=500）9.84 s~19×
COPY FROM6.42 s~29×

结论：批量写远端时，COPY 优于 INSERT，且务必调大 batch_size。但同时要注意：COPY 需要文件位于服务器可读路径，若数据来自本地暂存表，需要额外落盘一次，要权衡这个成本。

记得用完恢复 batch_size，避免影响日常小事务写入：

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (SET batch_size '1');

注意事项 ​

不检查 Remote SQL 就等于盲调；调参前也要先量化基线

sql
-- 任何外部表查询的调优都从这一步开始
EXPLAIN (VERBOSE, COSTS OFF) SELECT ... FROM remote_table ...;

-- 记录优化前的耗时作为对照
\timing on
SELECT ...;

类型或排序规则不一致会静默阻断下推

sql
-- 错误！远端是 timestamptz，本地写成 timestamp
CREATE FOREIGN TABLE remote_orders (created_at timestamp)
SERVER remote_pg OPTIONS (schema_name 'public', table_name 'orders');

-- 两端 collation 不同 → ORDER BY 只能在本地做
SELECT * FROM remote_orders ORDER BY status;

扩展函数不声明 extensions 就不下推

sql
-- 错误！未声明 extensions，ST_DWithin 无法下推
SELECT * FROM remote_shops WHERE ST_DWithin(location, ...);
-- 正确
ALTER SERVER remote_geo OPTIONS (ADD extensions 'postgis');

use_remote_estimate 全局打开会拖慢所有查询

sql
-- 不推荐
ALTER SERVER remote_pg OPTIONS (SET use_remote_estimate 'true');
-- 推荐：只在需要的表上开
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD use_remote_estimate 'true');

fetch_size 与 batch_size 不能互换，过大会撑爆内存

sql
-- 错误思路：想加快写入却调节 fetch_size
ALTER FOREIGN TABLE t OPTIONS (SET fetch_size '5000');  -- 对 INSERT 无帮助
-- 不推荐：过大的批大小会占用大量内存（本地或远端）
ALTER FOREIGN TABLE t OPTIONS (SET fetch_size '100000');
ALTER FOREIGN TABLE t OPTIONS (SET batch_size '100000');

跨远端写事务需要 max_prepared_transactions

ini
# 错误！默认 0 会导致跨远端事务提交失败
max_prepared_transactions = 0

不要试图在外部表上建索引

sql
CREATE INDEX ON remote_orders (created_at);   -- 错误！外部表不支持索引

正确做法是在远端建索引。同理，SELECT * 会拉回全部列（行更宽、传输更多），大表上不应无 LIMIT 地全量拉取：

sql
SELECT * FROM remote_orders;              -- 不推荐
SELECT id, status, amount FROM remote_orders;  -- 推荐

窗口函数通常下推不了

sql
-- 这类查询会把数据拉到本地再算窗口
SELECT *, row_number() OVER (PARTITION BY tenant_id ORDER BY created_at)
FROM remote_orders;

对大数据集要谨慎；可考虑在远端建视图预先计算，或用本地物化。

外部表的统计信息不会自动保持新鲜；连接数耗尽会表现为"随机报错"而非"慢"

远端数据变化后本地统计可能严重过时，导致 JOIN 顺序选错，需要定期 ANALYZE。另需注意，远端 max_connections 打满时报错类似 could not connect to server: FATAL: sorry, too many clients already——这类故障排查方向与性能问题完全不同，要能区分。

最佳实践 ​

每次改外部表定义后都看一遍 Remote SQL

sql
EXPLAIN (VERBOSE, COSTS OFF) SELECT ... FROM remote_orders WHERE ...;

外部表列类型严格与远端一致

优先用导入方式生成：

sql
DROP FOREIGN TABLE IF EXISTS remote_orders;
IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER remote_pg INTO public;

显式保证排序规则一致；远端必须有支撑查询的索引

sql
CREATE FOREIGN TABLE remote_orders (
    status text COLLATE "C"
) SERVER remote_pg OPTIONS (schema_name 'public', table_name 'orders');

-- 在远端执行，否则过滤条件照样全表扫
CREATE INDEX ON orders (status, created_at);

定期 ANALYZE 外部表

bash
psql -c "ANALYZE remote_orders;" -c "ANALYZE remote_order_items;"

关键表用 use_remote_estimate，其余保持默认；按链路延迟设置 fetch_size

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD use_remote_estimate 'true');

-- 内网
ALTER SERVER remote_pg OPTIONS (SET fetch_size '1000');
-- 跨机房
ALTER SERVER remote_pg_remote OPTIONS (SET fetch_size '5000');

批量写用 COPY 或调大 batch_size；多远端写入开启并行提交

sql
ALTER FOREIGN TABLE remote_orders OPTIONS (SET batch_size '500');
COPY remote_orders FROM '/data/x.csv' WITH (FORMAT csv);

ALTER SERVER remote_pg_a OPTIONS (ADD parallel_commit 'true', ADD parallel_abort 'true');
ALTER SERVER remote_pg_b OPTIONS (ADD parallel_commit 'true', ADD parallel_abort 'true');

不需要原子性就不要触发 2PC；调参前先用 \timing 建立基线

sql
-- 拆成两个独立事务，避免 2PC 开销
BEGIN; INSERT INTO remote_a ...; COMMIT;
BEGIN; INSERT INTO remote_b ...; COMMIT;

-- 记录优化前后的耗时作为对照
\timing on
SELECT tenant_id, sum(amount) FROM remote_orders WHERE status = 'paid' GROUP BY tenant_id;

查询只取需要的列，把过滤写成可下推形式，大结果集改为"远端聚合 + 本地精加工"

sql
-- 只取需要的列，避免 SELECT * 拉宽行；过滤条件避免被本地函数包裹
SELECT id, amount FROM remote_orders WHERE status = 'paid';

-- 让远端先把百万行压成几十行
SELECT tenant_id, sum(amount) AS total
FROM remote_orders
WHERE created_at >= now() - interval '30 days'
GROUP BY tenant_id;

为 FDW 访问规划远端连接数配额，用物化表替代高频实时查询

sql
-- 远端侧监控连接来源
SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY client_addr ORDER BY 2 DESC;
-- 高频固定报表改用物化表刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_remote_summary;

把优化决策写成注释留在表定义旁；定期复核 FDW 方案是否仍然合适

sql
COMMENT ON FOREIGN TABLE remote_kpi IS
    '性能经调优：fetch_size=5000（跨机房 20ms RTT）、analyze_sampling=random';

数据量、查询模式、网络拓扑都会变。半年前合适的 FDW 方案，今天可能已经撞到天花板；此时应当考虑改走 ETL 或逻辑复制，而不是继续调参。

下一节

继续学习：创建扩展（CREATE EXTENSION）

参考链接 ​