分布式 PostgreSQL 集群(Citus)官方示例-时间序列数据

分布式 *** 集群(Citus)官方示例-时间序列数据

*** 在时间序列工作负载中，应用程序(例如一些实时应用程序查询最近的信息，同时归档旧信息。

为了处理这种工作负载，单节点 *** 数据库通常会使用表分区将一个按时间排序的大数据表分解为多个继承表，每个表包含不同的时间范围。

将数据存储在多个物理表中会加速数据过期。在单个大表中，删除行会产生扫描以查找要删除的行，然后清理清空空间的成本。另一方面，删除分区是一种与数据大小无关的快速操作。这相当于简单地删除磁盘上包含数据的文件。

将数据存储在多个物理表中会加快数据过期的速度。在一个大表中，删除行需要扫描以找到要删除的行，然后清空空的空间。另一方面，删除分区是一种与数据大小无关的快速操作。这相当于简单地删除磁盘上包含数据的文件。

对表进行分区还可以使每个日期范围内的索引更小更快。对最近数据进行的查询很可能对适合内存的 hot 索引进行操作。这加快了读取速度。

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

在以下情况下，基于时间的分区最有意义：

大多数查询只访问最近数据的一个非常小的子集旧数据定期过期(删除/丢弃)

请记住，在错误的情况下，读取所有这些分区对开销的伤害大于帮助。但是，在正确的情况下，它非常有帮助。例如，保留一年的时间序列数据并定期仅查询最近一周。

扩展 Citus 上的时间序列数据

例如，让我们 distribute 和 partition 一个包含历史 GitHub 事件数据的表。

GitHub 事件数据

此 GitHub 数据集中的每条记录代表在 GitHub 中创建的事件，以及有关事件的关键信息，例如事件类型、创建日期和创建事件的用户。

第一步是按时间创建和分区(partition)表，就像我们在单节点 *** 数据库中一样：

-- declaratively partitioned tableCREATE 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 为分区管理提供了辅助函数。我们可以使用 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

-- set two monthly cron jobs:-- 1. ensure we have partitions for the next 12 monthsSELECT 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 dataSELECT cron.schedule('drop-partitions', '0 0 1 * *', $$ CALL drop_old_time_partitions( 'github_events', now() - interval '12 months' /* older_than */ );$$);

一旦设置了定期维护，您就不必再考虑分区了，它们可以正常工作。

请注意，Postgres 中的原生分区仍然很新，并且有一些怪癖。对分区表的维护操作将获取可能会短暂停止查询的激进锁。目前在 postgres 社区中正在进行大量工作来解决这些问题，因此预计 Postgres 中的 time 分区只会变得更好。

使用列式存储归档

列式表存储

分区表可以由行分区和列分区的任意组合组成。在 timestamp key 上使用范围分区时，我们可以将最新的分区制作成行表，并定期将最新的分区滚动到另一个历史列式分区中。

让我们看一个例子，再次使用 GitHub 事件。我们将创建一个名为 github_columnar_events 的新表，以消除前面示例中的歧义。为了完全专注于列式存储方面，我们不会分布此表。

接下来，下载示例数据：

-- our new table, same structure as the example in-- the previous sectionCREATE TABLE github_columnar_events ( LIKE github_events )PARTITION BY RANGE (created_at);-- create partitions to hold two hours of data eachSELECT 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 storageCALL 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 updatedSELECT partition, access_method FROM time_partitions WHERE parent_table = 'github_columnar_events'::regclass;

要查看柱状表的压缩率，请使用 VACUUM VERBOSE。我们三个柱状分区的压缩比相当不错：

VACUUM VERBOSE github_columnar_events;

分区表 github_columnar_events 的一个强大之处在于它可以像普通表一样被完整地查询。

SELECT COUNT(DISTINCT repo_id) FROM github_columnar_events;

只要分区键上有一个 WHERE 子句，它可以完全过滤到行表分区中，条目就可以被更新或删除。

将行分区归档到列式存储

当行分区已填满其范围时，您可以将其归档到压缩的列式存储中。我们可以使用 pg_cron 自动执行此操作，如下所示：

有关详细信息，请参阅列式存储。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。