微服务架构就像这个世界上的一切一样，有其优点和缺点。有了它，有些流程变得更容易，而另一些流程则变得更困难。为了改变的速度和更好的可扩展性，你需要做出牺牲。其中之一是分析的复杂性。如果在一个整体中，所有操作分析都可以简化为对分析副本的 SQL 查询，那么在多服务架构中，每个服务都有自己的数据库，并且一个查询似乎是不够的（或者也许会？）。对于那些对我们如何解决公司运营分析问题以及我们如何学会使用此解决方案感兴趣的人 - 欢迎。

我叫 Pavel Sivash，在 DomClick 的一个团队中负责维护分析数据仓库。传统上，我们的活动可以归因于数据工程，但事实上，任务的范围要广泛得多。有标准的数据工程ETL/ELT、数据分析工具的支持和适配以及自己开发的工具。特别是，对于运营报告，我们决定“假装”我们拥有一个整体，并为分析师提供一个包含他们所需的所有数据的数据库。

总的来说，我们考虑了不同的选择。构建一个成熟的存储库是可能的 - 我们甚至尝试过，但是，说实话，我们无法通过构建存储库并对其进行更改的相当缓慢的过程来与逻辑中相当频繁的更改交朋友（如果有人成功了，请在评论中写下如何）。你可以对分析师说：“伙计们，学习Python并进入分析领域”，但这是一个额外的招聘要求，而且似乎应该尽可能避免这种情况。我们决定尝试使用FDW（Foreign Data Wrapper）技术：事实上，这是一个标准的dblink，它是在SQL标准中的，但是它的接口更加方便。在此基础上，我们做出了一个决定，该决定最终扎根，我们决定了。它的细节是另一篇文章的主题，也许不止一篇，因为我想谈论很多：从数据库模式同步到个人数据的访问控制和去个性化。还应该指出的是，该解决方案并不能替代真正的分析数据库和存储库，它仅解决特定问题。

在顶层，它看起来像这样：

有一个 PostgreSQL 数据库，用户可以在其中存储他们的工作数据，最重要的是，所有服务的分析副本都通过 FDW 连接到该数据库。这使得向多个数据库编写查询成为可能，无论它是什么：PostgreSQL、MySQL、MongoDB 或其他数据库（文件、API，如果突然没有合适的包装器，您可以编写自己的包装器）。嗯，一切似乎都很棒！分手？

如果一切都结束得这么快、这么简单，那么这篇文章很可能就不会存在。

清楚 postgres 如何处理远程服务器的请求非常重要。这看起来合乎逻辑，但通常人们不会注意到它：postgres 将查询分为在远程服务器上独立执行的部分，收集这些数据，并自行执行最终计算，因此查询执行速度将很大程度上取决于如何执行。这个已经写完了。还应该注意的是：当数据来自远程服务器时，它们不再有索引，没有任何东西可以帮助调度程序，因此，只有我们自己可以提供帮助和建议。这就是我想更详细地讨论的内容。

一个简单的请求和一个计划

为了展示 Postgres 如何查询远程服务器上的 6 万行表，让我们看一个简单的计划。

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

使用 VERBOSE 语句可以让您查看将发送到远程服务器的查询以及我们将收到的用于进一步处理的结果（RemoteSQL 字符串）。

让我们更进一步，为我们的查询添加几个过滤器：一个布尔字段，逐项输入 时间戳 每个间隔和一个由 jsonb.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

这就是关键时刻，您在编写查询时需要注意这一点。过滤器没有传输到远程服务器，这意味着要执行它，postgres 会拉取所有 6 万行，以便在本地进行过滤（Filter 行）并稍后执行聚合。成功的关键是编写一个查询，以便将过滤器传输到远程计算机，并且我们仅接收和聚合必要的行。

这是一些布尔值

有了布尔字段，一切就变得简单了。在原始查询中，问题是由于运算符引起的 is。如果我们将其替换为 =，然后我们得到以下结果：

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

可以看到，过滤器飞到了远程服务器，执行时间从 27 秒减少到了 19 秒。

需要注意的是，运营商 is 与运营商不同 = 可以使用 Null 值的值。代表着 不是真的 过滤器中将留下值 False 和 Null，而 != 正确 只会留下 False 值。因此，更换操作员时 是不是 您应该使用 OR 运算符将两个条件传递给过滤器，例如， WHERE (col != True) OR (col 为空).

布尔值弄清楚后，继续。同时，让我们将布尔值过滤器返回到其原始形式，以便独立考虑其他更改的影响。

时间戳？赫兹

一般来说，您经常必须尝试如何正确编写涉及远程服务器的查询，然后才寻找发生这种情况的原因的解释。在互联网上可以找到很少关于这方面的信息。因此，在实验中，我们发现固定日期过滤器“砰”的一声飞到远程服务器，但是当我们想要动态设置日期时，例如 now() 或 CURRENT_DATE，这种情况就不会发生。在我们的示例中，我们添加了一个过滤器，以便 created_at 列包含过去 1 个月的数据（BETWEEN CURRENT_DATE - INTERVAL '7 个月' AND CURRENT_DATE - INTERVAL '6 个月'）。在这种情况下我们做了什么？

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

我们提示规划器在子查询中提前计算日期，并将已经准备好的变量传递给过滤器。这个提示给了我们一个很好的结果，查询速度几乎快了 6 倍！

再次强调，这里要小心：子查询中的数据类型必须与我们过滤的字段相同，否则规划器会决定，因为类型不同，所以需要先获取所有的数据并在本地进行过滤。

让我们按日期将过滤器返回到其原始值。

弗莱迪对战jsonb

一般来说，布尔字段和日期已经足以加速我们的查询，但还有一种数据类型。老实说，与它的过滤之战还没有结束，尽管这里也取得了成功。所以，这就是我们如何设法通过过滤器的 jsonb 字段到远程服务器。

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

您必须使用一个运算符的存在，而不是过滤运算符。 jsonb 在不同的。 7 秒，而不是原来的 29 秒。到目前为止，这是将过滤器转移到的唯一成功的选项 jsonb 到远程服务器，但这里重要的是要考虑一个限制：我们使用数据库的 9.6 版本，但我们计划在 12 月底完成最后的测试并迁移到版本 10。一旦我们更新，我们将写下它是如何影响的，因为有很多变化值得期待：json_path、新的 CTE 行为、下推（从版本 XNUMX 开始存在）。我真的很想尽快尝试一下。

解决他

我们分别检查了每个更改如何影响查询速度。现在让我们看看当所有三个过滤器都正确编写时会发生什么。

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

是的，查询看起来比较复杂，是强制价格，但是执行速度是2秒，快了10倍以上！我们正在讨论对相对较小的数据集的简单查询。根据真实的要求，我们收到的增幅高达数百倍。

总结一下：如果您将 PostgreSQL 与 FDW 一起使用，请始终检查所有过滤器是否都发送到远程服务器，您会很高兴......至少在您连接来自不同服务器的表之前是这样。但这是另一篇文章的故事了。

感谢您的关注！我很乐意在评论中听到有关您的经历的问题、评论和故事。

来源： habr.com

微服务架构中的运营分析：帮助和提示Postgres FDW

一个简单的请求和一个计划

这是一些布尔值

时间戳？赫兹

弗莱迪对战jsonb

解决他

添加评论取消回复

微服务架构中的运营分析：帮助和提示Postgres FDW

一个简单的请求和一个计划

这是一些布尔值

时间戳？ 赫兹

弗莱迪对战jsonb

解决他

添加评论 取消回复

时间戳？赫兹

添加评论取消回复