微服務架構就像這個世界上的一切一樣，有其優點和缺點。有了它，有些流程變得更容易，而有些流程則變得更困難。為了改變速度和更好的可擴展性，您需要做出犧牲。其中之一是分析的複雜性不斷增加。如果在一個整體中，所有操作分析都可以簡化為對分析副本的 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 萬行，然後在本地進行過濾（過濾行）並執行聚合。成功的關鍵是編寫一個查詢，以便將過濾器傳輸到遠端計算機，並且我們僅接收和聚合必要的行。

這是一些布林值

有了布爾字段，一切就變得簡單了。在最初的請求中，問題是由於運營商造成的 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 字段到遠端伺服器。

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 一起使用，請始終檢查所有過濾器是否都發送到遠端伺服器，您會很高興......至少在您連接來自不同伺服器的表之前。但這是另一篇文章的故事了。

感謝您的關注！我很樂意在評論中聽到有關您的經歷的問題、評論和故事。

來源： www.habr.com

微服務架構中的運營分析：幫助和提示Postgres FDW

一個簡單的查詢和一個計劃

這是一些布林值

時間戳？赫茲

佛萊迪對戰傑森布

解決他

添加評論取消回复

微服務架構中的運營分析：幫助和提示Postgres FDW

一個簡單的查詢和一個計劃

這是一些布林值

時間戳？ 赫茲

佛萊迪對戰傑森布

解決他

添加評論 取消回复

時間戳？赫茲

添加評論取消回复