分布式 PostgreSQL 集群(Citus)官方示例-時間序列數據
本文轉載自微信公眾號「黑客下午茶」，作者為少。轉載本文請聯系黑客下午茶公眾號。
PostgreSQL 在時間序列工作負載中，應用程序(例如一些實時應用程序查詢最近的信息，同時歸檔舊信息。
- https://docs.citusdata.com/en/v10.2/sharding/data_modeling.html#distributing-by-entity-id
為了處理這種工作負載，單節點 PostgreSQL 數據庫通常會使用表分區將一個按時間排序的大數據表分解為多個繼承表，每個表包含不同的時間范圍。
- https://www.postgresql.org/docs/current/static/ddl-partitioning.html
將數據存儲在多個物理表中會加速數據過期。在單個大表中，刪除行會產生掃描以查找要刪除的行，然后清理清空空間的成本。另一方面，刪除分區是一種與數據大小無關的快速操作。這相當于簡單地刪除磁盤上包含數據的文件。
將數據存儲在多個物理表中會加快數據過期的速度。在一個大表中，刪除行需要掃描以找到要刪除的行，然后清空空的空間。另一方面，刪除分區是一種與數據大小無關的快速操作。這相當于簡單地刪除磁盤上包含數據的文件。
- https://www.postgresql.org/docs/current/static/routine-vacuuming.html
對表進行分區還可以使每個日期范圍內的索引更小更快。對最近數據進行的查詢很可能對適合內存的 hot 索引進行操作。這加快了讀取速度。
插入也有更小的索引要更新，所以它們也更快。
在以下情況下，基于時間的分區最有意義：
- 大多數查詢只訪問最近數據的一個非常小的子集
- 舊數據定期過期(刪除/丟棄)
請記住，在錯誤的情況下，讀取所有這些分區對開銷的傷害大于幫助。但是，在正確的情況下，它非常有幫助。例如，保留一年的時間序列數據并定期僅查詢最近一周。
擴展 Citus 上的時間序列數據
我們可以將單節點表分區技術與 Citus 的分布式分片相結合，形成一個可擴展的時間序列數據庫。這是兩全其美的。它在 Postgres 的聲明性表分區之上特別優雅。
例如，讓我們 distribute 和 partition 一個包含歷史 GitHub 事件數據的表。
- GitHub 事件數據
https://examples.citusdata.com/events.csv
此 GitHub 數據集中的每條記錄代表在 GitHub 中創建的事件，以及有關事件的關鍵信息，例如事件類型、創建日期和創建事件的用戶。
第一步是按時間創建和分區(partition)表，就像我們在單節點 PostgreSQL 數據庫中一樣：
-- declaratively partitioned table
CREATE TABLE github_events (
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
actor jsonb,
org jsonb,
created_at timestamp
) PARTITION BY RANGE (created_at);
注意 PARTITION BY RANGE (created_at)。這告訴 Postgres 該表將由 created_at 列在有序范圍內進行分區。不過，我們還沒有為特定范圍創建任何分區。
在創建特定分區之前，讓我們在 Citus 中分布表。我們將按 repo_id 進行分片，這意味著事件將被聚集到每個存儲庫的分片中。
SELECT create_distributed_table('github_events', 'repo_id');
此時 Citus 已跨工作節點為該表創建分片。在內部，每個分片是一個表，每個分片標識符 N 的名稱為 github_events_N。此外，Citus 傳播了分區信息，每個分片都聲明了 Partition key: RANGE (created_at)。
分區表不能直接包含數據，它更像是跨分區的視圖。因此，分片還沒有準備好保存數據。我們需要創建分區并指定它們的時間范圍，之后我們可以插入與范圍匹配的數據。
自動創建分區
Citus 為分區管理提供了輔助函數。我們可以使用 create_time_partitions() 創建一批每月分區：
SELECT create_time_partitions(
table_name := 'github_events',
partition_interval := '1 month',
end_at := now() + '12 months'
);
Citus 還包括一個視圖 time_partitions，以方便地調查它創建的分區。
隨著時間的推移，您將需要進行一些維護以創建新分區并刪除舊分區。最好設置一個定期 job 來運行帶有 pg_cron 之類的擴展的維護功能：
- pg_cron
https://github.com/citusdata/pg_cron
-- set two monthly cron jobs:
-- 1. ensure we have partitions for the next 12 months
SELECT cron.schedule('create-partitions', '0 0 1 * *', $$
SELECT create_time_partitions(
table_name := 'github_events',
partition_interval := '1 month',
end_at := now() + '12 months'
)
$$);
-- 2. (optional) ensure we never have more than one year of data
SELECT cron.schedule('drop-partitions', '0 0 1 * *', $$
CALL drop_old_time_partitions(
'github_events',
now() - interval '12 months' /* older_than */
);
$$);
一旦設置了定期維護，您就不必再考慮分區了，它們可以正常工作。
請注意，Postgres 中的原生分區仍然很新，并且有一些怪癖。對分區表的維護操作將獲取可能會短暫停止查詢的激進鎖。目前在 postgres 社區中正在進行大量工作來解決這些問題，因此預計 Postgres 中的 time 分區只會變得更好。
使用列式存儲歸檔
一些應用程序的數據在邏輯上分為可更新的小部分和“凍結(frozen)”的較大部分。示例包括日志、點擊流或銷售記錄。在這種情況下，我們可以將分區與列式表存儲(在 Citus 10 中引入)結合起來壓縮磁盤上的歷史分區。Citus 柱狀表目前是僅追加的，這意味著它們不支持更新或刪除，但我們可以將它們用于不可變的歷史分區。
- 列式表存儲
https://docs.citusdata.com/en/v10.2/admin_guide/table_management.html#columnar
分區表可以由行分區和列分區的任意組合組成。在 timestamp key 上使用范圍分區時，我們可以將最新的分區制作成行表，并定期將最新的分區滾動到另一個歷史列式分區中。
讓我們看一個例子，再次使用 GitHub 事件。我們將創建一個名為 github_columnar_events 的新表，以消除前面示例中的歧義。為了完全專注于列式存儲方面，我們不會分布此表。
接下來，下載示例數據：
wget http://examples.citusdata.com/github_archive/github_events-2015-01-01-{0..5}.csv.gz
gzip -c -d github_events-2015-01-01-*.gz >> github_events.csv
-- our new table, same structure as the example in
-- the previous section
CREATE TABLE github_columnar_events ( LIKE github_events )
PARTITION BY RANGE (created_at);
-- create partitions to hold two hours of data each
SELECT create_time_partitions(
table_name := 'github_columnar_events',
partition_interval := '2 hours',
start_from := '2015-01-01 00:00:00',
end_at := '2015-01-01 08:00:00'
);
-- fill with sample data
-- (note that this data requires the database to have UTF8 encoding)
\COPY github_columnar_events FROM 'github_events.csv' WITH (format CSV)
-- list the partitions, and confirm they're
-- using row-based storage (heap access method)
SELECT partition, access_method
FROM time_partitions
WHERE parent_table = 'github_columnar_events'::regclass;
-- convert older partitions to use columnar storage
CALL alter_old_partitions_set_access_method(
'github_columnar_events',
'2015-01-01 06:00:00' /* older_than */,
'columnar'
);
-- the old partitions are now columnar, while the
-- latest uses row storage and can be updated
SELECT partition, access_method
FROM time_partitions
WHERE parent_table = 'github_columnar_events'::regclass;
要查看柱狀表的壓縮率，請使用 VACUUM VERBOSE。我們三個柱狀分區的壓縮比相當不錯：
VACUUM VERBOSE github_columnar_events;INFO: statistics for "github_columnar_events_p2015_01_01_0000":
storage id: 10000000003
total file size: 4481024, total data size: 4444425
compression rate: 8.31x
total row count: 15129, stripe count: 1, average rows per stripe: 15129
chunk count: 18, containing data for dropped columns: 0, zstd compressed: 18
INFO: statistics for "github_columnar_events_p2015_01_01_0200":
storage id: 10000000004
total file size: 3579904, total data size: 3548221
compression rate: 8.26x
total row count: 12714, stripe count: 1, average rows per stripe: 12714
chunk count: 18, containing data for dropped columns: 0, zstd compressed: 18
INFO: statistics for "github_columnar_events_p2015_01_01_0400":
storage id: 10000000005
total file size: 2949120, total data size: 2917407
compression rate: 8.51x
total row count: 11756, stripe count: 1, average rows per stripe: 11756
chunk count: 18, containing data for dropped columns: 0, zstd compressed: 18
分區表 github_columnar_events 的一個強大之處在于它可以像普通表一樣被完整地查詢。
SELECT COUNT(DISTINCT repo_id)
FROM github_columnar_events;
只要分區鍵上有一個 WHERE 子句，它可以完全過濾到行表分區中，條目就可以被更新或刪除。
將行分區歸檔到列式存儲
當行分區已填滿其范圍時，您可以將其歸檔到壓縮的列式存儲中。我們可以使用 pg_cron 自動執行此操作，如下所示：
-- a monthly cron job
SELECT cron.schedule('compress-partitions', '0 0 1 * *', $$
CALL alter_old_partitions_set_access_method(
'github_columnar_events',
now() - interval '6 months' /* older_than */,
'columnar'
);
$$);
有關詳細信息，請參閱列式存儲。
