PostgreSQL FDW 用户映射 OPTIONS 深度解析与性能优化实践

大家好，我是老猿。今天咱们来聊聊 PostgreSQL 的一个强大功能——外部数据包装器（Foreign Data Wrapper，简称 FDW）。更具体地说，咱们要深入研究 FDW 用户映射中的 OPTIONS，看看如何利用这些选项来优化查询性能。相信这对于有一定 FDW 使用经验，并希望进一步提升 FDW 性能的 PostgreSQL DBA 们会很有帮助。

什么是 FDW？

在深入 OPTIONS 之前，咱们先简单回顾一下 FDW。FDW 允许咱们在 PostgreSQL 中访问存储在外部数据源的数据，就像访问本地表一样。这些外部数据源可以是其他 PostgreSQL 服务器、MySQL、Oracle、MongoDB，甚至是 CSV 文件或 Web API。通过 FDW，咱们可以实现跨数据库、跨平台的数据集成和查询。

用户映射与 OPTIONS

在使用 FDW 时，咱们需要创建用户映射（User Mapping），将本地 PostgreSQL 用户映射到外部数据源的用户。在创建用户映射时，咱们可以使用 OPTIONS 来指定一些特定于外部数据源服务器或用户级别的设置。这些设置可以影响 FDW 的行为和性能。

OPTIONS 是一个键值对的集合，其中键是选项名称，值是选项的值。不同的 FDW 可以支持不同的选项。对于 postgres_fdw（用于访问其他 PostgreSQL 服务器的 FDW），有一些通用的选项可以用来优化查询性能。

常用 OPTIONS 及其作用

下面咱们重点介绍几个常用的 OPTIONS 及其作用，并通过示例演示如何使用它们。

1. fetch_size

fetch_size 选项用于指定每次从外部服务器获取的行数。默认情况下，postgres_fdw 会一次性获取所有符合条件的行。如果外部表非常大，这可能会导致本地服务器内存不足或查询性能下降。通过设置 fetch_size，咱们可以控制每次获取的行数，从而减少内存消耗并提高查询性能。

示例：

CREATE USER MAPPING FOR local_user
SERVER remote_server
OPTIONS (user 'remote_user', password 'remote_password', fetch_size '1000');

在这个示例中，咱们将 fetch_size 设置为 1000，这意味着每次从外部服务器获取 1000 行数据。

思考过程： 为什么要用fetch_size？你想想，如果你要查询一个百万级别数据的外部表，一次性把所有数据都拉到本地，那得多占内存啊！而且网络传输也慢。fetch_size 就好比你用一个小桶，一次打一点水，多次打完，这样既省内存，又能避免一次性传输大量数据造成的网络拥堵。

2. use_remote_estimate

use_remote_estimate 选项用于指定是否使用外部服务器的行估计。默认情况下，PostgreSQL 会尝试在本地估计外部表的行数。但对于某些复杂的查询，本地估计可能不准确，导致查询计划不是最优的。通过将 use_remote_estimate 设置为 true，咱们可以让 PostgreSQL 使用外部服务器的行估计，从而生成更准确的查询计划。

示例：

CREATE USER MAPPING FOR local_user
SERVER remote_server
OPTIONS (user 'remote_user', password 'remote_password', use_remote_estimate 'true');

在这个示例中，咱们将 use_remote_estimate 设置为 true，这意味着 PostgreSQL 将使用外部服务器的行估计。

思考过程： 为什么有时候本地估计不准？因为本地 PostgreSQL 不知道外部表的数据分布情况啊！打个比方，你要估算一个班级里有多少人，如果你只看了前几排，那估算结果肯定不准。use_remote_estimate 就像让外部服务器自己来数人数，这样结果更可靠。

3. extensions

extensions 选项用于指定在远程会话中信任哪些扩展。 某些扩展函数下推到远程执行是安全的，而另一些则不是。 如果可以，仅在远程服务器上执行受信任的扩展的函数，将减少必须从远程服务器获取的数据量。

示例：

CREATE USER MAPPING FOR local_user
SERVER remote_server
OPTIONS (user 'remote_user', password 'remote_password', extensions 'pg_trgm, btree_gin');

在这个例子中，我们信任远程服务器上的 'pg_trgm' 和 'btree_gin' 扩展。这意味着，如果查询中包含这两个扩展相关的函数，并且可以下推到远程执行，那么FDW会优先在远端执行这些函数。

思考过程: 为什么要信任扩展？ 默认情况下，PostgreSQL对远程服务器上的扩展是不信任的，因为远程服务器的环境可能与本地不同，盲目执行远程服务器上的函数可能会导致数据不一致或安全问题。通过extensions 选项， 我们可以明确指定信任哪些扩展，在保证安全的前提下，尽可能地利用远程服务器的计算资源。

4. 其他选项

除了上面介绍的几个常用选项外，postgres_fdw 还支持其他一些选项，例如：

• keep_connections: 控制是否保持与外部服务器的连接。默认为 on。
• async_capable: 指示外部服务器是否支持异步执行。

这些选项的具体用法可以参考 PostgreSQL 官方文档。

性能优化实践

了解了这些 OPTIONS 的作用后，咱们来看看如何在实际应用中利用它们来优化 FDW 查询性能。

1. 合理设置 fetch_size

fetch_size 的设置需要根据实际情况进行调整。如果外部表非常大，或者网络带宽有限，可以适当减小 fetch_size 的值。如果外部表比较小，或者网络带宽充足，可以适当增大 fetch_size 的值。一般来说，fetch_size 的值设置为 1000 到 10000 之间比较合适。

2. 谨慎使用 use_remote_estimate

use_remote_estimate 并不总是能提高查询性能。在某些情况下，使用外部服务器的行估计可能会导致查询计划更差。因此，在使用 use_remote_estimate 之前，最好先测试一下，看看是否真的能提高查询性能。

3. 结合 EXPLAIN 分析查询计划

要优化 FDW 查询性能，咱们需要了解 PostgreSQL 是如何执行查询的。通过使用 EXPLAIN 命令，咱们可以查看查询计划，了解 PostgreSQL 是否使用了 FDW，以及 FDW 是如何访问外部数据的。通过分析查询计划，咱们可以找到性能瓶颈，并采取相应的优化措施。

示例：

EXPLAIN SELECT * FROM local_table WHERE id IN (SELECT id FROM remote_table WHERE value > 100);

通过分析 EXPLAIN 的输出，咱们可以了解 PostgreSQL 是否使用了 FDW，以及 FDW 是如何访问外部数据的。如果发现 FDW 的性能不佳，咱们可以尝试调整 OPTIONS，或者优化查询语句。

4. 其他优化技巧

除了调整 OPTIONS 外，咱们还可以使用其他一些技巧来优化 FDW 查询性能，例如：

• 在外部表上创建适当的索引。
• 尽量避免在 FDW 查询中使用复杂的函数或表达式。
• 尽量减少跨网络传输的数据量。
• 尽可能将计算推送到远端执行。

总结

FDW 是 PostgreSQL 的一个强大功能，可以帮助咱们实现跨数据库、跨平台的数据集成和查询。通过合理使用用户映射中的 OPTIONS，咱们可以优化 FDW 查询性能，提高应用程序的整体性能。希望本文能帮助大家更好地理解和使用 FDW。

如果你还有其他关于 FDW 或 PostgreSQL 的问题，欢迎留言讨论。我会尽力解答。