转载本文请联系黑客下午茶公众号。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将数据存储在多个物理表中会加速数据过期，在单个大表中，删除一行会产生扫描以找到要删除的行，然后清理空空间的成本。另一方面，删除分区是一个与数据大小无关的快速操作，相当于简单地删除磁盘上包含该数据的文件，将数据存储在多个物理表中会加快数据过期的速度.在一个大表中，deleting一行需要扫描找到要删除的行，然后清空空白空间。另一方面，删除分区是一项与数据大小无关的快速操作。相当于简单的删除了磁盘上的A文件包含的数据。https://www.postgresql.org/docs/current/static/routine-vacuuming.html对表进行分区还可以使每个日期范围内的索引更小更快。最近的数据查询很可能对适合内存的热索引进行操作。这加快了读取速度。插入也有更小的索引来更新，所以它们也更快。基于时间的分区在以下情况下最有意义：大多数查询仅访问最近数据的一小部分旧数据定期过期（删除/丢弃）请记住，在错误的情况下，读取所有这些分区弊大于利。但是，在正确的情况下，这是非常有帮助的。比如保留一年的时序数据，周期性的只查询最近一周的数据。在Citus上扩展时序数据我们可以将单节点表分区技术与Citus的分布式分片相结合，形成一个可扩展的时序数据库。这是两全其美的。它在Postgres的声明性表分区之上特别优雅。例如，让我们对包含历史GitHub事件数据的表进行分布和分区。GitHub事件数据https://examples.citusdata.com/events.csv此GitHub数据集中的每条记录都代表在GitHub中创建的一个事件，以及有关该事件的关键信息，例如事件类型、创建日期、和创建事件的用户。第一步是按时间创建和分区表，就像我们在单节点PostgreSQL数据库中所做的那样：--声明式分区表CREATETABLEgithub_events(event_idbigint,event_typetext,event_publicboolean,repo_idbigint,payloadjsonb,repojsonb,actorjsonb,orgjsonb,created_attimestamp)PARTITIONBYRANGE(created_at);注意PARTITIONBYRANGE(created_at)。这告诉Postgres该表将按created_at列按有序范围分区。但是，我们还没有为特定范围创建任何分区。在创建特定分区之前，让我们在Citus中分配表。我们将按repo_id进行分片，这意味着事件将聚合到每个存储库的分片中。选择create_distributed_table('github_events','repo_id');此时，Citus已经跨工作节点为表创建了分片。在内部，每个分片都是一个表，每个分片标识符N的名称为github_events_N。此外，Citus传播分区信息，每个分片声明分区键：RANGE(created_at)。分区表不能直接包含数据，它更像是一个跨分区的视图。因此，分片还没有准备好保存数据。我们需要创建分区并指定它们的时间范围，之后我们可以插入匹配该范围的数据。自动创建分区Citus提供了分区管理的辅助函数。我们可以使用create_time_partitions()创建一批每月分区：SELECTcreate_time_partitions(table_name:='github_events',partition_interval:='1month',end_at:=now()+'12months');Citus还包括一个视图time_partitions，以轻松调查它创建的分区。随着时间的推移，您将需要进行一些维护以创建新分区并删除旧分区。最好设置一个定期作业来运行带有pg_cron等扩展的维护功能：pg_cronhttps://github.com/citusdata/pg_cron--设置两个月度cron作业：--1.确保我们有接下来12个月的分区SELECTcron.schedule('create-partitions','001**',$$SELECTcreate_time_partitions(table_name:='github_events',partition_interval:='1month',end_at:=now()+'12months')$$);--2.（可选）确保我们永远不会有超过一年的数据SELECTcron.schedule('drop-partitions','001**',$$CALLdrop_old_time_partitions('github_events',now()-interval'12months'/*older_than*/);$$);一旦你设置了定期维护，你就不必再考虑分区了，它们可以正常工作。请注意，Postgres中的本机分区仍然很新，并且有一些怪癖。分区表上的维护操作将获取激进的锁，这可能会暂时停止查询。目前postgres社区正在进行大量工作来解决这些问题，因此期望Postgres中的时间分区只会变得更好。使用列式存储归档某些应用程序在逻辑上将数据分为小的可更新部分和较大的“冻结”部分。示例包括日志、点击流或销售记录。在这种情况下，我们可以将分区与柱状表存储（在Citus10中引入）相结合，以压缩磁盘上的历史分区。Citus柱状表目前是仅追加的，这意味着它们不支持更新或删除，但我们可以将它们用于不可变的历史分区。列表存储https://docs.citusdata.com/en/v10.2/admin_guide/table_management.html#columnar分区表可以由行和列分区的任意组合组成。在时间戳键上使用范围分区时，我们可以将最新的分区做成行表，并周期性地将最新的分区滚动到另一个历史列分区中。让我们看一个示例，再次使用GitHub事件。我们将创建一个名为github_columnar_events的新表，以消除与前面示例的歧义。为了完全专注于列式存储方面，我们不会分发这张表。接下来，下载示例数据：wgethttp://examples.citusdata.com/github_archive/github_events-2015-01-01-{0..5}.csv.gzgzip-c-dgithub_events-2015-01-01-*.gz>>github_events.csv——我们的新表，结构与上一节中的示例相同——创建表github_columnar_events(LIKEgithub_events)PARTITIONBYRANGE(created_at);——创建分区以保存两个小时的数据每个SELECTcreate_time_partitions(table_name:='github_columnar_events',partition_interval:='2hours',start_from:='2015-01-0100:00:00',end_at:='2015-01-0108:00:00');--填充示例数据--（注意此数据需要数据库具有UTF8编码）\COPYgithub_columnar_eventsFROM'github_events.csv'WITH（格式CSV）--列出分区，并确认它们是--使用行-基于存储（堆访问方式）SELECTpartition,access_methodFROMtime_partitionsWHEREparent_table='github_columnar_events'::regclass;--convert旧分区使用柱状存储CALLalter_old_partitions_set_access_method('github_columnar_events','2015-01-0106:00:00'/*older_than*/,'columnar');--旧分区现在是柱状的，而--最新的用途行存储并且可以更新SELECTpartition,access_methodFROMtime_partitionsWHEREparent_table='github_columnar_events'::regclass;要查看柱状表的压缩率，请使用VACUUMVERBOSE我们三个柱状分区的压缩比相当不错：VACUUMVERBOSEgithub_columnar_events;INFO:statisticsfor"github_columnar_events_p2015_01_01_0000":storageid:10000000003totalfilesize:4481024,totaldatasize:4444425compressionrate:8.31xtotalrowcount:15129,stripecount:1，每个条带的平均行数：15129块数：18，包含丢弃列的数据：0，zstd压缩：18INFO：“github_columnar_events_p2015_01_01_0200”的统计信息：存储ID：10000000004总文件大小：3579904，总数据大小：3548221压缩率：8行计数.26x总行数：12714，条带数：1，每个条带的平均行数：12714块数：18，包含删除列的数据：0，压缩的zstd：18INFO：“github_columnar_events_p2015_01_01_0400”的统计信息：存储ID：10000000005总文件大小：2949120，总数据大小：2917407压缩率：8.51x总行数：11756，条带数：1，每个条带的平均行数：11756块数：18，包含丢弃列的数据：0，zstdcompressed:18分区表github_columnar_events的优点之一就是可以像普通表一样完全查询SELECTCOUNT(DISTINCTrepo_id)FROMgithub_columnar_events;只要分区键上有WHERE子句，就可以完全过滤到行表分区，条目可以被更新或删除。将行分区归档到列式存储当行分区填满其范围时，您可以将其归档到压缩的列式存储。我们可以使用pg_cron如下自动执行此操作：--每月cronjobSELECTcron.schedule('compress-partitions','001**',$$CALLalter_old_partitions_set_access_method('github_columnar_events',now()-interval'6months'/*old_than*/,'columnar');$$);有关详细信息，请参阅列式存储。
