短暂休息后，又开始撰写有关 Postgres 的技术文章。这也是我宣布改变活动的机会。自 2021 年以来，我成为一名自由职业者，让公司有机会从我在 Postgres 的经验中受益。
PostgreSQL 分区的历史
长期以来，PostgreSQL 通过使用表继承使分区表成为可能。但是，这种方法实现起来相当困难：它意味着设置触发器来重定向写入（效率低于原生分区），如果分区超过一百个，规划时间可能会显著增加……
从 Postgres 10 版本开始，postgres 能够（除其他外）将写入发送到正确的分区，执行分区修剪以仅读取选定的分区，使用利用分区的算法等。因此它提供了更好的性能和更容易维护。
从 Postgres 10 开始，我们可以：
分割：
按列表
按哈希
按范围
多级分区
多列分区
使用主键和外键
所有这些特性都很有趣，但我们可以问自己一个非常简单的问题：什么时候使用分区？
我将向您介绍我遇到的几个用例。但首先，这里有一些关于分区的常见错误。
常见错误
“只要尺寸很重要，就需要分区”
首先，什么是“大”尺寸？
有的说几百GB，有的超过1TB，有的还超过PB……
这个问题没有真正的答案，通常它取决于工作负载的类型：比率 INSERT/UPDATE/DELETE、SELECT 的类型（OLTP、OLAP……）。它还取决于硬件。10 年前，当服务器只有几 GB 的 RAM 和机械磁盘时，几百 GB 的数据库很可能会被视为大型数据库。现在，使用超过 TB 的 RAM、NVMe 驱动器的服务器并不少见。
因此，几百 GB 的数据库不再被视为大型数据库。而是作为一个适度的数据库。
一个小故事，为了让自己放心，一位客户问我 Postgres 是否已经用于“大”卷。我们谈论的是在具有 64 GB RAM 的服务器上的 40 GB 数据库。所有读取都是从缓存中完成的...... :)。我能够向他保证，他的数据库规模相对较小。
对几个 TB 的数据库进行分区可能是多余的，因为可能需要对几百 GB 中的一个进行分区。例如，如果活动只是向表中添加行并且查询很简单WHERE column = 4
，返回几行。一个简单的 Btree 就可以完成这项工作。如果查询返回很多行，则可以使用 BRIN 索引或bloom过滤器。
BRIN 索引提供与水平分区或分片类似的好处，但不需要显式声明分区。
https://blog.anayrat.info/en/2021/09/01/partitioning-use-cases-with-postgresql/#fn:1
“分区对于将数据分布在多个磁盘上是必要的”
这个想法是在不同的磁盘上创建分区和表空间，以分散输入/输出操作。
对于 PostgreSQL，表空间只不过是目录的路径。例如，可以在操作系统级别管理存储并聚合多个磁盘（在 RAID10 中）。然后，只需将表存储在创建的卷上即可。因此，I/O 可以分布在一组磁盘上。
在这种情况下，没有必要实施分区。但是，我们将看到一个可能有意义的案例。
现在我们将看看分区的“合法”用例。
分区用例
分区为管理
由于 MVCC 模型，大量删除会导致表格膨胀。
一个可能的用例是按日期分区。删除旧数据与删除完整分区相同。操作会很快，表也不会臃肿。
分区为控制索引膨胀
随着时间的推移，添加和修改数据会使索引膨胀。简而言之，在块为空之前，您无法恢复块中的可用空间。随着时间的推移，索引拆分会在索引中创建“空”空间，恢复该空间的唯一方法是重建索引。
这被称为“膨胀”。Postgres 的最新版本有很多改进：
版本 12，我们可以在 Releases Notes中阅读：
提高具有许多重复项的 btree 索引的性能和空间利用率（Peter Geoghegan，Heikki Linnakangas）
以前，重复的索引条目在其重复组中无序存储。这会导致索引插入期间的开销，由于过多的页面拆分而浪费空间，并且降低了 VACUUM 回收整个页面的能力。重复的索引条目现在按堆存储顺序排序。
版本 13，我们可以在 Releases Notes中阅读：
在 B 树索引中更有效地存储重复项 (Anastasia Lubennikova, Peter Geoghegan)
这允许通过仅存储一次重复键来对低基数列进行有效的 B 树索引。使用 pg_upgrade 升级的用户将需要使用 REINDEX 使现有索引使用此功能。
第 14 版，我们可以在 Releases Notes中阅读：
允许添加 btree 索引以删除过期的索引条目以防止页面拆分 (Peter Geoghegan)
这对于减少索引列经常更新的表上的索引膨胀特别有用。
为了控制膨胀，我们可以定期重建索引（感谢REINDEX CONCURRENTLY
版本 12）。这种解决方案会很麻烦，因为必须定期重建整个索引。
如果大部分修改都是对最近的数据进行的，例如：日志表、客户订单、预约……我们可以想象按月进行分区。因此，在每个月初，我们从一个“新”表开始，我们可以重新索引前一个表以消除膨胀。
我们还可以利用这一点在表上创建一个集群，使数据与存储具有良好的相关性。
低基数分区
渐渐地我们会看到更复杂的用例:)
举个例子：一个有发货状态的订单表，几年后99%的订单都发货了（我们希望！），很少有在付款或发货的过程中。
假设我们要检索 100 个正在进行的订单。我们将在状态上创建一个索引并使用它来检索记录。如果我们有点聪明，我们可以在这个特定状态上创建一个部分索引。问题在于，随着订单的交付，该指数会很快膨胀。
在这种情况下，我们可以对状态进行分区。因此，在交付过程中检索 100 条订单相当于从分区中读取 100 条记录。
分区以获得更准确的统计信息
为了确定最佳执行计划，Postgres 根据统计数据做出决策。它们是从表的样本中获得的（default_statistic_target
默认为 100）。
默认情况下，postgres 将收集 300 xdefault_statistic_target
行，即 30 000 行。对于一个有几亿行的表，这个样本有时太小了。
我们可以大幅增加样本量，但这种方法有一些缺点：
它增加了计划时间
它使
ANALYZE
更重。有时，如果数据分布不均，这还不够。例如，如果你从一个有几亿行的表中取出几十万行，你可能会错过处于交付状态的行。
通过分区，我们可以有相同的样本，这使我们能够提高准确性。
当我们在列之间关联数据时，这也很有用。我将以订单为例。我们有一整年的订单量：超过一个月的订单全部发货，上个月的订单90%发货（10%在进行中）。
直觉上，如果我查找超过 6 个月前在进行中的订单，我应该不会得到任何结果。另一方面，如果我搜索上个月在进行中的订单，我应该得到 10% 的表。但是 postgres 不知道，对于它来说，正在进行的订单遍布整个表。
通过按日期划分，它可以估计超过一个月的交货没有正在进行的订单。这种方法主要用于减少执行计划中的估计误差。
这是一个带有此订单表的示例，orders_p
是该表的月份分区版本orders
。两个表中的数据相同。
我们可以注意到，在表被分区的情况下，估计要好得多，postgres 每个分区都有统计信息。
EXPLAIN ANALYZE SELECT * FROM orders_p WHERE state = 'pending' AND c1 BETWEEN '2021-01-01' AND '2021-01-31'; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Index Scan using orders_13_state_idx on orders_13 (cost=0.42..4.45 rows=1 width=12) (actual rows=0 loops=1) Index Cond: (state = 'pending'::text) Filter: ((c1 >= '2021-01-01'::date) AND (c1 <= '2021-01-31'::date)) Planning Time: 0.120 ms Execution Time: 0.059 ms (5 rows) EXPLAIN ANALYZE SELECT * FROM orders WHERE state = 'pending' AND c1 BETWEEN '2021-01-01' AND '2021-01-31'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using orders_state_idx on orders (cost=0.44..13168.25 rows=3978 width=12) (actual rows=0 loops=1) Index Cond: (state = 'pending'::text) Filter: ((c1 >= '2021-01-01'::date) AND (c1 <= '2021-01-31'::date)) Rows Removed by Filter: 100161 Planning Time: 0.188 ms Execution Time: 141.571 ms (6 rows)
现在让我们在上个月进行相同的查询：
EXPLAIN ANALYZE SELECT * FROM orders_p WHERE state = 'pending' AND c1 BETWEEN '2021-07-01' AND '2021-07-31'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Index Scan using orders_19_state_idx on orders_19 (cost=0.43..2417.50 rows=19215 width=12) (actual rows=20931 loops=1) Index Cond: (state = 'pending'::text) Filter: ((c1 >= '2021-07-01'::date) AND (c1 <= '2021-07-31'::date)) Planning Time: 0.297 ms Execution Time: 32.618 ms (5 rows) EXPLAIN ANALYZE SELECT * FROM orders WHERE state = 'pending' AND c1 BETWEEN '2021-07-01' AND '2021-07-31'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Index Scan using orders_state_idx on orders (cost=0.44..13168.25 rows=15008 width=12) (actual rows=20931 loops=1) Index Cond: (state = 'pending'::text) Filter: ((c1 >= '2021-07-01'::date) AND (c1 <= '2021-07-31'::date)) Rows Removed by Filter: 79230 Planning Time: 0.173 ms Execution Time: 146.326 ms (6 rows)
在这里我们再次可以看到估计更好。
分区连接和分区聚合
分区的另一个好处是从更好的连接和聚合算法中受益。
允许对partitionwise aggregate
每个分区进行聚合或分组分区。一个例子胜过长篇大论：
explain (analyze,timing off) select count(*), c1 from orders_p group by c1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- HashAggregate (cost=508361.80..508365.45 rows=365 width=12) (actual rows=365 loops=1) Group Key: orders_01.c1 -> Append (cost=0.00..408317.35 rows=20008890 width=4) (actual rows=20000000 loops=1) -> Seq Scan on orders_01 (cost=0.00..22.70 rows=1270 width=4) (actual rows=0 loops=1) -> Seq Scan on orders_02 (cost=0.00..22.70 rows=1270 width=4) (actual rows=0 loops=1) [...] -> Seq Scan on orders_19 (cost=0.00..45308.04 rows=2941004 width=4) (actual rows=2941004 loops=1) -> Seq Scan on orders_20 (cost=0.00..131708.21 rows=8549421 width=4) (actual rows=8549421 loops=1) Planning Time: 0.576 ms Execution Time: 5273.217 ms (25 rows) set enable_partitionwise_aggregate to on; explain (analyze,timing off) select count(*), c1 from orders_p group by c1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Append (cost=29.05..408343.83 rows=1765 width=12) (actual rows=365 loops=1) -> HashAggregate (cost=29.05..31.05 rows=200 width=12) (actual rows=0 loops=1) Group Key: orders_01.c1 -> Seq Scan on orders_01 (cost=0.00..22.70 rows=1270 width=4) (actual rows=0 loops=1) -> HashAggregate (cost=29.05..31.05 rows=200 width=12) (actual rows=0 loops=1) Group Key: orders_02.c1 -> Seq Scan on orders_02 (cost=0.00..22.70 rows=1270 width=4) (actual rows=0 loops=1) [...] -> HashAggregate (cost=60013.06..60013.37 rows=31 width=12) (actual rows=31 loops=1) Group Key: orders_19.c1 -> Seq Scan on orders_19 (cost=0.00..45308.04 rows=2941004 width=4) (actual rows=2941004 loops=1) -> HashAggregate (cost=174455.32..174455.55 rows=24 width=12) (actual rows=24 loops=1) Group Key: orders_20.c1 -> Seq Scan on orders_20 (cost=0.00..131708.21 rows=8549421 width=4) (actual rows=8549421 loops=1) Planning Time: 1.461 ms Execution Time: 4669.315 ms (63 rows)
在第一种情况下，对所有表进行一次聚合，而在第二种情况下，聚合是针对每个分区进行的。我们还可以注意到，在每个分区聚合的计划中，总成本较低。
partitionwise join
执行相同的原则，我们为每个分区连接做一个分区。连接两个分区表很有用。
存储分层
最后，另一个用例是将表的一部分存储在不同的存储中：
我们可以将分区表存储在不同的表空间中。例如 NVMe SSD 上快速表空间上的最新数据。然后，另一个表空间上访问较少的数据，机械磁盘成本较低。
这种方法在存储非常昂贵的云时代也很有意义。
结论
我想我已经涵盖了我想到的主要用例。
显然，分区的实现意味着更大的复杂性（分区管理......）和必须向上研究其局限性。
