本文轉(zhuǎn)載自微信公眾號(hào)「黑客下午茶」，作者為少。轉(zhuǎn)載本文請(qǐng)聯(lián)系黑客下午茶公眾號(hào)。

 PostgreSQL 在時(shí)間序列工作負(fù)載中，應(yīng)用程序(例如一些實(shí)時(shí)應(yīng)用程序查詢最近的信息，同時(shí)歸檔舊信息。

• https://docs.citusdata.com/en/v10.2/sharding/data_modeling.html#distributing-by-entity-id

為了處理這種工作負(fù)載，單節(jié)點(diǎn) PostgreSQL 數(shù)據(jù)庫(kù)通常會(huì)使用表分區(qū)將一個(gè)按時(shí)間排序的大數(shù)據(jù)表分解為多個(gè)繼承表，每個(gè)表包含不同的時(shí)間范圍。

• https://www.postgresql.org/docs/current/static/ddl-partitioning.html

將數(shù)據(jù)存儲(chǔ)在多個(gè)物理表中會(huì)加速數(shù)據(jù)過期。在單個(gè)大表中，刪除行會(huì)產(chǎn)生掃描以查找要?jiǎng)h除的行，然后清理清空空間的成本。另一方面，刪除分區(qū)是一種與數(shù)據(jù)大小無關(guān)的快速操作。這相當(dāng)于簡(jiǎn)單地刪除磁盤上包含數(shù)據(jù)的文件。

將數(shù)據(jù)存儲(chǔ)在多個(gè)物理表中會(huì)加快數(shù)據(jù)過期的速度。在一個(gè)大表中，刪除行需要掃描以找到要?jiǎng)h除的行，然后清空空的空間。另一方面，刪除分區(qū)是一種與數(shù)據(jù)大小無關(guān)的快速操作。這相當(dāng)于簡(jiǎn)單地刪除磁盤上包含數(shù)據(jù)的文件。

• https://www.postgresql.org/docs/current/static/routine-vacuuming.html

對(duì)表進(jìn)行分區(qū)還可以使每個(gè)日期范圍內(nèi)的索引更小更快。對(duì)最近數(shù)據(jù)進(jìn)行的查詢很可能對(duì)適合內(nèi)存的 hot 索引進(jìn)行操作。這加快了讀取速度。

插入也有更小的索引要更新，所以它們也更快。

在以下情況下，基于時(shí)間的分區(qū)最有意義：

• 大多數(shù)查詢只訪問最近數(shù)據(jù)的一個(gè)非常小的子集
• 舊數(shù)據(jù)定期過期(刪除/丟棄)

請(qǐng)記住，在錯(cuò)誤的情況下，讀取所有這些分區(qū)對(duì)開銷的傷害大于幫助。但是，在正確的情況下，它非常有幫助。例如，保留一年的時(shí)間序列數(shù)據(jù)并定期僅查詢最近一周。

擴(kuò)展 Citus 上的時(shí)間序列數(shù)據(jù)

我們可以將單節(jié)點(diǎn)表分區(qū)技術(shù)與 Citus 的分布式分片相結(jié)合，形成一個(gè)可擴(kuò)展的時(shí)間序列數(shù)據(jù)庫(kù)。這是兩全其美的。它在 Postgres 的聲明性表分區(qū)之上特別優(yōu)雅。

例如，讓我們 distribute 和 partition 一個(gè)包含歷史 GitHub 事件數(shù)據(jù)的表。

• GitHub 事件數(shù)據(jù)

https://examples.citusdata.com/events.csv

此 GitHub 數(shù)據(jù)集中的每條記錄代表在 GitHub 中創(chuàng)建的事件，以及有關(guān)事件的關(guān)鍵信息，例如事件類型、創(chuàng)建日期和創(chuàng)建事件的用戶。

第一步是按時(shí)間創(chuàng)建和分區(qū)(partition)表，就像我們?cè)趩喂?jié)點(diǎn) PostgreSQL 數(shù)據(jù)庫(kù)中一樣：

-- 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 列在有序范圍內(nèi)進(jìn)行分區(qū)。不過，我們還沒有為特定范圍創(chuàng)建任何分區(qū)。

在創(chuàng)建特定分區(qū)之前，讓我們?cè)?Citus 中分布表。我們將按 repo_id 進(jìn)行分片，這意味著事件將被聚集到每個(gè)存儲(chǔ)庫(kù)的分片中。

SELECT create_distributed_table('github_events', 'repo_id');

此時(shí) Citus 已跨工作節(jié)點(diǎn)為該表創(chuàng)建分片。在內(nèi)部，每個(gè)分片是一個(gè)表，每個(gè)分片標(biāo)識(shí)符 N 的名稱為 github_events_N。此外，Citus 傳播了分區(qū)信息，每個(gè)分片都聲明了 Partition key: RANGE (created_at)。

分區(qū)表不能直接包含數(shù)據(jù)，它更像是跨分區(qū)的視圖。因此，分片還沒有準(zhǔn)備好保存數(shù)據(jù)。我們需要?jiǎng)?chuàng)建分區(qū)并指定它們的時(shí)間范圍，之后我們可以插入與范圍匹配的數(shù)據(jù)。

自動(dòng)創(chuàng)建分區(qū)

Citus 為分區(qū)管理提供了輔助函數(shù)。我們可以使用 create_time_partitions() 創(chuàng)建一批每月分區(qū)：

SELECT create_time_partitions(
  table_name         := 'github_events',
  partition_interval := '1 month',
  end_at             := now() + '12 months'
);

Citus 還包括一個(gè)視圖 time_partitions，以方便地調(diào)查它創(chuàng)建的分區(qū)。

隨著時(shí)間的推移，您將需要進(jìn)行一些維護(hù)以創(chuàng)建新分區(qū)并刪除舊分區(qū)。最好設(shè)置一個(gè)定期 job 來運(yùn)行帶有 pg_cron 之類的擴(kuò)展的維護(hù)功能：

• 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 */
  );
$$);

一旦設(shè)置了定期維護(hù)，您就不必再考慮分區(qū)了，它們可以正常工作。

請(qǐng)注意，Postgres 中的原生分區(qū)仍然很新，并且有一些怪癖。對(duì)分區(qū)表的維護(hù)操作將獲取可能會(huì)短暫停止查詢的激進(jìn)鎖。目前在 postgres 社區(qū)中正在進(jìn)行大量工作來解決這些問題，因此預(yù)計(jì) Postgres 中的 time 分區(qū)只會(huì)變得更好。

使用列式存儲(chǔ)歸檔

一些應(yīng)用程序的數(shù)據(jù)在邏輯上分為可更新的小部分和“凍結(jié)(frozen)”的較大部分。示例包括日志、點(diǎn)擊流或銷售記錄。在這種情況下，我們可以將分區(qū)與列式表存儲(chǔ)(在 Citus 10 中引入)結(jié)合起來壓縮磁盤上的歷史分區(qū)。Citus 柱狀表目前是僅追加的，這意味著它們不支持更新或刪除，但我們可以將它們用于不可變的歷史分區(qū)。

• 列式表存儲(chǔ)

https://docs.citusdata.com/en/v10.2/admin_guide/table_management.html#columnar

分區(qū)表可以由行分區(qū)和列分區(qū)的任意組合組成。在 timestamp key 上使用范圍分區(qū)時(shí)，我們可以將最新的分區(qū)制作成行表，并定期將最新的分區(qū)滾動(dòng)到另一個(gè)歷史列式分區(qū)中。

讓我們看一個(gè)例子，再次使用 GitHub 事件。我們將創(chuàng)建一個(gè)名為 github_columnar_events 的新表，以消除前面示例中的歧義。為了完全專注于列式存儲(chǔ)方面，我們不會(huì)分布此表。

接下來，下載示例數(shù)據(jù)：

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;

要查看柱狀表的壓縮率，請(qǐng)使用 VACUUM VERBOSE。我們?nèi)齻€(gè)柱狀分區(qū)的壓縮比相當(dāng)不錯(cuò)：

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

分區(qū)表 github_columnar_events 的一個(gè)強(qiáng)大之處在于它可以像普通表一樣被完整地查詢。

SELECT COUNT(DISTINCT repo_id)
  FROM github_columnar_events;

只要分區(qū)鍵上有一個(gè) WHERE 子句，它可以完全過濾到行表分區(qū)中，條目就可以被更新或刪除。

將行分區(qū)歸檔到列式存儲(chǔ)

當(dāng)行分區(qū)已填滿其范圍時(shí)，您可以將其歸檔到壓縮的列式存儲(chǔ)中。我們可以使用 pg_cron 自動(dòng)執(zhí)行此操作，如下所示：

-- 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'
  );
$$);

有關(guān)詳細(xì)信息，請(qǐng)參閱列式存儲(chǔ)。