使用 YugabyteDB 自动分片实现高级 PostgreSQL 日期分区
摘要: 本文探讨了使用 YugabyteDB 自动分片功能实现高级 PostgreSQL 日期分区策略。作者解释了如何在日期列上进行范围分片，同时保持高效的插入和查询性能，并展示了纯自动分片与结合 PostgreSQL 声明式分区进行生命周期管理的区别。
最近，我写了一篇关于 YugabyteDB 如何在不使用 PostgreSQL 声明式分区的情况下实现扩展以及如何解决其限制的文章。我引用了 Kyle Hailey 博客中的一个表格，详细描述了一个涉及 "payments" 表的 PostgreSQL 生产问题，其中 "payment_id" 是主键。Kyle 的文章指出，在 PostgreSQL 中，主键必须包含分区键。
在我的博客文章中，我讨论了扩展过程，并解释瞭如何在对 "payment_id" 进行哈希分片的同时不更改其主键。这种方法通过将表行分布在集群节点之间来确保快速插入。这种方法与 YugabyteDB 的自动分片无缝配合，并支持使用任何二级索引来满足额外的用例。
我在 Reddit 和其他网站上收到了一些反馈，认为在不同的列上进行分区可能会更好。这对 PostgreSQL 确实如此，因为分区键会限制表和所有索引的分片。然而，使用 YugabyteDB 分片，我不需要更改表分区来查询日期范围。YugabyteDB 的二级索引是全局的，意味着它们不需要与表共享分片键。此外，在像 YugabyteDB 这样的分布式 PostgreSQL 数据库中分布行提供了额外的好处，因为它将数据分散到多个活动服务器上。
要查询日期范围，你可以在日期上创建索引，并添加更多列以实现索引仅扫描扫描。当定义为升序或降序时，此索引将按其键进行范围分片和分布。以下是一个示例：
create index on payments ( created ASC, payment_id ) include ( amount );
如果你有理由按日期对表进行分区以将接近的日期组合在一起，也可以在 YugabyteDB 中这样做。以下是一个示例。
按日期范围分片
我已修改表的主键以包含 "created" 列，类似于在 PostgreSQL 中进行分区的方式。
create table payments (
primary key (created ASC, payment_id)
, unique(payment_id)
, payment_id bigint not null generated always as identity (cache 1000)
, created timestamptz not null default now()
, account_id bigint not null
, amount decimal(10,2) not null
)
split at values ( ('2001-01-01'),('2002-01-01'),('2003-01-01'),('2004-01-01') )
;
我这种做法的原因包括三个关键点。
- 我将
payment_id设置为唯一键，以确保它被索引且没有重复。在 SQL 中，具有非空列的唯一键类似于主键。主要区别在于其物理组织。通过以 "created" 日期开头主键，我可以将近日期组合在一起，这在日期范围查询是关键访问模式而非分发插入时非常有用。 - 我在主键中添加了 'ASC'，因为 YugabyteDB 默认为主键的第一列使用 'HASH'。由于我想按日期范围组织表，我不想对此列应用哈希函数。如果你经常用 'order by created desc limit 10' 查询最新日期，那么最好避免向后扫描并将其定义为 'DESC'。两种选项都允许范围分片。
- 虽然不是强制的（因为表会随着 YugabyteDB 自动增长而被自动拆分），但我添加了 'split at values' 子句将表预拆分为五个 tablet。在我之前的博客中，我展示了 tablet 数量可以达到数百或数千。虽然可以通过提供更多拆分值来预创建数百个 tablet，但我选择保持简单并坚持使用自动拆分阈值。
该表为空，有五个 tablet，用于唯一约束的哈希分片索引每个节点有一个 tablet：
yugabyte=> select num_tablets from yb_table_properties('payments'::regclass);
num_tablets
-------------
5
(1 row)
yugabyte=> select num_tablets from yb_table_properties('payments_payment_id_key'::regclass);
num_tablets
-------------
9
(1 row)
我可以从控制台查看 tablet 边界，它以内部 int64 表示显示数据范围，对应于 PostgreSQL Epoch（自 2000 年 1 月 1 日以来的微秒数）：
自动拆分：插入一亿行
我将插入与上一博客相同数量的行：一亿行。
yugabyte=> insert into payments (account_id, amount, created)
select 1000 * random() as account, random() as amount,
random() * interval '24.25 year' + timestamp '2000-01-01 00:00:00' as created
from generate_series(1,100000) account_id
\watch c=1000
当表增长时，tablet 会被拆分，控制台中会显示更多的 tablet。"SplitDepth" 记录了导致每个 tablet 的自动拆分次数。
加载结束时，表有 17 个 tablet（日期范围），唯一索引也有 17 个（按哈希）。
yugabyte=> select num_tablets, pg_size_pretty(pg_table_size('payments')) from yb_table_properties('payments'::regclass);
num_tablets | pg_size_pretty
-------------+----------------
17 | 5738 MB
(1 row)
yugabyte=> select num_tablets, pg_size_pretty(pg_table_size('payments_payment_id_key')) from yb_table_properties('payments_payment_id_key'::regclass);
num_tablets | pg_size_pretty
-------------+----------------
17 | 4527 MB
(1 row)
要查看范围分片创建的 tablet 数量，可以使用 yb_get_range_split_clause() 函数。如果你想要用相同的 tablet 划分复制表，此函数提供所需的范围子句：
yugabyte=> select yb_get_range_split_clause('payments'::regclass);
由此可以清楚地看出，范围分片考虑整个键。因此，如果为同一日期加载大量数据，可以在同一日期内进一步拆分。
按日期范围查询：250 毫秒内查询 80000 行
请记住，我按日期进行分区的主要目标是查询日期范围。
yugabyte=> explain (analyze, dist, costs off, summary on)
select * from payments
where created between date '2023-12-25'::timestamptz
and '2024-01-02'::timestamptz
order by created
;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using payments_pkey on payments
(actual time=3.568..246.471 rows=80142 loops=1)
Index Cond: ((created >= ('2023-12-25'::date)::timestamp with time zone) AND
(created <= '2024-01-02 00:00:00+00'::timestamp with time zone))
Storage Table Read Requests: 79
Storage Table Read Execution Time: 202.721 ms
Planning Time: 0.065 ms
Execution Time: 250.869 ms
Storage Read Requests: 79
Storage Read Execution Time: 202.721 ms
Storage Execution Time: 202.721 ms
Peak Memory Usage: 8 kB
(14 rows)
这种方法将数据拆分为日期范围，以保持表大小可管理，并将行按 LSM 树排序。因此，它消除了 ORDER BY 额外排序操作的需要。
按全局唯一索引查询：2 毫秒
payment_id 上的附加唯一键可用于快速访问表的业务键，因为我们的主键现在是一个具有不同顺序的代理键：
yugabyte=> explain (analyze, dist, costs off, summary off )
select * from payments
where payment_id = 42
order by created
;
QUERY PLAN
----------------------------------------------------------
Sort (actual time=4.105..4.105 rows=1 loops=1)
Sort Key: created
Sort Method: quicksort Memory: 25kB
-> Index Scan using payments_payment_id_key on payments
(actual time=4.094..4.097 rows=1 loops=1)
Index Cond: (payment_id = 42)
Storage Table Read Requests: 1
Storage Table Read Execution Time: 2.840 ms
Storage Index Read Requests: 1
Storage Index Read Execution Time: 1.080 ms
(9 rows)
由于使用的索引不是主索引，有必要访问表以检索其他列的值，从而导致表读请求和索引读请求。但是，由于它是按值查询的唯一标识符并按哈希分片，它只会读取一行。额外的"跳转"到表仅限于每个查询一次读取请求。尽管听起来有点反直觉，但使用唯一约束声明业务键并为其他目的使用主键是正确的做法。这是因为业务键可以与集群键重载，将经常一起查询的数据存储在一起。
注意：数据建模提示 在 SQL 应用程序中，表通常有多个候选键。一个被选为主键用于物理组织，而其他则使用唯一约束声明。在像 Microsoft Citus、Oracle 全局分布式数据库这样的分区或分片系统中，索引只能是本地的，分区或分片键必须属于所有唯一键或主键。然而，像 YugabyteDB 这样的分布式 SQL 数据库提供全局一致的二级索引，因此你可以将索引与用例相匹配。
用于其他用例的二级索引
在之前的博客中，我创建了一个通过 "account_id" 访问的索引。
create index payments_by_account on payments(account_id, amount desc)
;
yugabyte=> select num_tablets, pg_size_pretty(pg_table_size('payments_by_account')) from yb_table_properties('payments_by_account'::regclass);
num_tablets | pg_size_pretty
-------------+----------------
9 | 3513 MB
(1 row)
创建更多二级索引确实会在插入、删除或更新索引中的列时产生开销。然而，与传统数据库相比，这种开销在 YugabyteDB 中很小。使用 B 树的数据库（如 Oracle、PostgreSQL 或 SQL Server）需要多次读写来插入新索引条目——从根到分支找到叶块，更新它，以及（满时）拆分并更新分支。YugabyteDB 的 LSM-Tree 结构允许通过将新条目直接追加到 MemTable 来快速插入索引条目，而无需读取之前的值。
#1：用于快速摄入的附加桶号前缀
一个关键的数据库原理是将频繁访问的数据存储在一起，以最小化 I/O 并有利于缓存局部性。单个访问模式决定你的主键。如果你有多个访问模式，一个被选为主键，而其他定义你的二级索引，简化读取操作，因为每个索引独立运行。然而，在数据插入等写操作中，所有索引（覆盖不同值的部分索引除外）都会参与。采取一种策略以防止任何索引成为数据摄入期间的瓶颈至关重要。
在之前的博客中，插入分布在各个 tablet 上，因为主键是按哈希的 "payment_id"。然而，在本博客中，以创建日期开头主键会导致所有插入针对同一个 tablet。要实现分发和高效的行集群，需要一个分区键（用于分发）和一个排序键（用于集群）。将主键定义为（payment_id HASH, created ASC）会无效，因为分区键的高基数会破坏排序键。在没有低基数列可用的情况下，可以使用模函数创建一个附加的"桶#"列
create table payments (
primary key ("bucket#" ASC, created ASC, payment_id)
, unique(payment_id)
, "bucket#" int default (random()*1e6)::int%8
, payment_id bigint not null generated always as identity (cache 1000)
, created timestamptz not null default now()
, account_id bigint not null
, amount decimal(10,2) not null
) split at values ( (1),(2),(3),(4),(5),(6) )
;
在这个简单的演示中，我设置了一个介于 0 到 7 之间的随机"桶#"来将插入分发到 8 个 tablet。我可以选择一个现有的低基数列，或使用模函数从高基数列创建一个，使用触发器或生成列进行计算。如果你有可以用于快速访问的值，你可以使用它。如果你想让来自同一会话的插入进入同一个 tablet，你也可以使用 pg_backend_pid()。使用此技术列的目标是让不同值的数量足够高以分发写入，但又足够低以将行保持在排序键上集群。
我已根据不同值的数量预拆分了 tablet（将进一步自动拆分）。这个过程对应用程序透明，允许我运行相同的 INSERT 和 SELECT 语句。
你可能想知道，现在这些日期来自 8 个范围，按日期范围的查询表现如何。在 YugabyteDB 中，只要前缀的基数较低，索引仍然可以通过索引跳跃扫描高效运行。
"桶#"的添加只会增加约 20 毫秒。这主要是由于排序和合并范围以进行 ORDER BY，而不是索引扫描本身，后者只需要寻址八个范围而不是一个。
添加桶号被证明是比声明式分区更简单且更具可扩展性的解决方案。
#2：用于生命周期管理的日期分区
按日期范围分组可以通过删除旧分区（例如使用 PostgreSQL 范围分区的每年一个分区）来快速清理数据。然而，这会限制索引可能性。例如，无法在多个字段上强制执行唯一约束。在考虑优化之前，评估自动分片是否已经满足性能需求。日期范围查询很快，这对清理作业至关重要。这些作业为 LSM 树中的行删除插入墓碑，只有在压缩后才能释放空间。压缩按 tablet 进行，拆分按日期进行，因此任何删除和压缩只会影响特定的 tablet。这就是自动分区的优势：将每个 LSM 树保持在可管理的大小。
此删除很慢，因为它使用强一致性更新索引。写请求被缓冲并批量刷新，可以在后台运行。请注意，当每天对少量数据进行删除时，此解决方案是有效的。当清除很大百分比时，整个 tablet 可能会变空并保持未使用状态，因为新行进入更高的范围。空 tablet 在当前版本的 YugabyteDB 中不会合并（请参阅增强请求 #21816）。对于这种情况，我会在其上使用声明式分区。
分片之上的 PostgreSQL 声明式分区
YugabyteDB 的自动分片有效地分发数据而不限制 SQL 功能，透明且自动运行。然而，PostgreSQL 的声明式分区仍然可以使用，然后分片应用于每个分区。由于分区被分片和分发，它们的大小可以扩展而不会出现可扩展性问题，有助于将声明式分区的数量保持在最小。
例如，当有太多空 tablet 时，你可以删除旧分区，但你不需要每年都这样做。在我的示例中，我可以定义三个分区，每个覆盖 10 年范围。10 年后，常规清理留下的空 tablet 可以被删除。
create table payments (
primary key ("bucket#" ASC, created ASC, payment_id)
-- no global unique constraints -- , unique(payment_id)
, "bucket#" int default (random()*1e6)::int%8
, payment_id bigint not null generated always as identity (cache 1000)
, created timestamptz not null default now()
, account_id bigint not null
, amount decimal(10,2) not null
) partition by range ( created )
;
create table payments1 partition of payments
for values from ( minvalue ) to ('2009-12-31 23:59:59') ;
create table payments2 partition of payments
for values from ('2010-01-01 00:00:00') to ('2019-12-31 23:59:59') ;
create table payments3 partition of payments
for values from ('2020-01-01 00:00:00') to ('2029-12-31 23:59:59') ;
create index payments_by_account on payments(account_id, amount desc);
alter table payments1 add unique(payment_id);
alter table payments2 add unique(payment_id);
alter table payments3 add unique(payment_id);
由于 PostgreSQL 分区的限制，有两个缺点：
- 我无法在所有分区上强制执行唯一约束，尽管如果由序列生成，这可能是可以接受的。
- 我只能创建本地索引，因此不包含分区键（"created"）的查询将不得不读取三个索引而不是一个。
尽管有这些限制，使用最少数量的分区时性能仍然可以接受。对一亿行运行相同的 INSERT 操作导致每个分区自动拆分。
使用日期范围查询，会发生分区裁剪，因此性能与没有声明式分区相同。
使用在每个分区上声明为唯一的业务键的点查询，需要读取三个索引。但这仍然很快。
当查询索引列而不指定分区键时，响应时间会因分区数量而增加。然而，只有三个分区，对性能的影响仍然可以接受。
实际上，由于分区是为操作原因创建的，你可以隐藏它们并向用户提供一个视图，只显示允许他们查询的时间窗口，例如最近五年。
yugabyte=> create view visible_payments as
select * from payments
when created between now() - interval '5 years' and now()
;
现在，为未来创建的其他分区不必读取，查询会更快。
这是一个很好的策略，为未来创建更多分区以防止以后丢失，同时确保应用程序只访问必要的分区。
当你按日期分区时可能不需要这样做。但是，在 YugabyteDB 中使用声明式分区还有其他原因，例如地理分发，其中每个分区被分配到特定的表空间，可能通过 "account_id" 列表将其中一些隔离，或按国家/地区代码。规则保持不变：将声明式分区的数量限制为生命周期管理或数据放置的最小值，利用 YugabyteDB 的自动分片在每个分区内实现可扩展性。
结论：带全局索引的自动间隔分区
自动分片在 YugabyteDB 的分布式存储中促进了广泛的扩展和分发可能性。通过定义服务于访问模式的主键和二级索引，表和索引的分片键可以不同。结果？你可以为各种用例优化一个模式。
键可以包含一个分区键——用于分发——和一个排序键——用于集群。你还可以添加一个技术键来控制基数，这样你就可以在增加写入吞吐量的分发插入与增加读取响应时间的行或索引条目的集群之间取得平衡。
此外，你可以使用声明式分区将某些分区放置在特定位置以进行地理分发，在该级别只有本地索引是有意义的。因为每个分区都受益于 YugabyteDB 分布式存储的可扩展性，所以不需要创建大量分区，从而避免 PostgreSQL 数百个分区时遇到的限制。
