由 John Doe 八月 6, 2025
你经常使用到分区表吗？你知道如何更好的维护和管理好分区表吗？
分区相关函数介绍
PostgreSQL 自版本 10 引入分区的概念以来，在过去几年得到了极大改进。通过对系统表执行特定查询来收集分区信息虽然可行，但过程较为复杂，且这些查询往往不够直观。例如，要获取完整的分区树，在处理多层分区时就需要使用WITH RECURSIVE语句。
PostgreSQL 12 在这方面有所改进，主要体现在新增了两个函数。第一个是pg_partition_tree函数，用于轻松获取完整分区树的信息。这个新函数可以用于显示给定分区表的完整分区树，避免了在查看层数较深的分区树时使用复杂的WITH RECURSIVE查询。
它会返回一组记录，每个分区对应一条记录，包含分区名称、其直接的父分区名称、一个表示该关系是否为分区树中叶子节点的布尔值，以及一个表示该分区在分区树中所处的层级数（以给定表为根节点，根节点层级为 0，每向下深入一层层级加 1）。
第二个是pg_partition_root函数，能够查找显示出分区树的最顶层父节点。这在处理多层分区树时非常有用，与pg_partition_tree结合使用，只需知道任意层级的一个成员，就能显示整个分区树。
使用示例
首先，我们创建一组分区，分为两层，并为所有分区定义一个索引：
CREATE TABLE parent_tab (id int) PARTITION BY RANGE (id);
CREATE INDEX parent_index ON parent_tab (id);
CREATE TABLE child_0_10 PARTITION OF parent_tab
FOR VALUES FROM (0) TO (10);
CREATE TABLE child_10_20 PARTITION OF parent_tab
FOR VALUES FROM (10) TO (20);
CREATE TABLE child_20_30 PARTITION OF parent_tab
FOR VALUES FROM (20) TO (30);
INSERT INTO parent_tab VALUES (generate_series(0,29));
CREATE TABLE child_30_40 PARTITION OF parent_tab
FOR VALUES FROM (30) TO (40)
PARTITION BY RANGE(id);
CREATE TABLE child_30_35 PARTITION OF child_30_40
FOR VALUES FROM (30) TO (35);
CREATE TABLE child_35_40 PARTITION OF child_30_40
FOR VALUES FROM (35) TO (40);
INSERT INTO parent_tab VALUES (generate_series(30,39));
这组分区表及其分区非常简单：一个父表，其直接子表按值的范围划分。其中一个子表child_30_40自身也有分区，这些分区基于其自身范围的子集定义。CREATE INDEX会应用于所有分区，这意味着所有这些关系在 “id” 列上都有一个 B 树索引。
首先，pg_partition_tree()会显示完整的分区树，它接收一个关系作为输入，以此作为分区树的父节点起点，因此使用parent_tab作为输入会得到完整的树：
SELECT * FROM pg_partition_tree('parent_tab');
relid | parentrelid | isleaf | level
-------------+-------------+--------+-------
parent_tab | null | f | 0
child_0_10 | parent_tab | t | 1
child_10_20 | parent_tab | t | 1
child_20_30 | parent_tab | t | 1
child_30_40 | parent_tab | f | 1
child_30_35 | child_30_40 | t | 2
child_35_40 | child_30_40 | t | 2
(7 rows)
如果使用某个子表作为输入，若该关系是叶子分区，则仅返回其自身；否则可能返回一个子树：
SELECT * FROM pg_partition_tree('child_0_10');
relid | parentrelid | isleaf | level
------------+-------------+--------+-------
child_0_10 | parent_tab | t | 0
(1 row)
SELECT * FROM pg_partition_tree('child_30_40');
relid | parentrelid | isleaf | level
-------------+-------------+--------+-------
child_30_40 | parent_tab | f | 0
child_30_35 | child_30_40 | t | 1
child_35_40 | child_30_40 | t | 1
(3 rows)
分区树中的索引也会被妥善处理，其处理方式与其所依赖的关系保持一致：
SELECT * FROM pg_partition_tree('parent_index');
relid | parentrelid | isleaf | level
--------------------+--------------------+--------+-------
parent_index | null | f | 0
child_0_10_id_idx | parent_index | t | 1
child_10_20_id_idx | parent_index | t | 1
child_20_30_id_idx | parent_index | t | 1
child_30_40_id_idx | parent_index | f | 1
child_30_35_id_idx | child_30_40_id_idx | t | 2
child_35_40_id_idx | child_30_40_id_idx | t | 2
(7 rows)
返回的字段说明如下：
- relid 是 OID，可视为树中给定元素的关系名称。它使用 regclass 作为输出类型以方便使用。
- parentrelid 指向该元素的直接父节点。
- isleaf 为 true 时，表示该元素没有自己的分区，简言之，它有物理存储。
- level 是一个计数器，表示在树中的层级，最顶层父节点为 0，每向下移动一层则加 1。
在处理数百个分区时，首先，这比遍历系统表记录的方法更快，如前面提到的，可封装到 SQL 函数中，以提供与此处介绍的内置函数相同结果的WITH RECURSIVE查询。其次，它使聚合操作更容易且更具可读性。要获取给定分区树所占用的总物理空间，可通过以下方式汇总：
SELECT pg_size_pretty(sum(pg_relation_size(relid)))
AS total_partition_size
FROM pg_partition_tree('parent_tab');
total_partition_size
----------------------
40 kB
(1 row)
这对索引同样适用，使用pg_total_relation_size()还可以获取包含所有索引的给定分区树所使用的总物理空间。
第二个函数pg_partition_root()在处理复杂分区树时很有用。根据业务应用中使用分区的策略，关系名称可能会带有结构化的命名规则，但随着版本迭代以及添加新的功能或逻辑层，这些规则很容易被打破，这首先会导致混乱，其次在试图弄清楚分区树的实际结构时会遇到困难。该函数可以接收一个关系名称作为输入，返回分区树的最顶层父节点：
SELECT pg_partition_root('child_35_40');
pg_partition_root
-------------------
parent_tab
(1 row)
如果输入是最顶层父节点或单个关系，则结果为其自身：
SELECT pg_partition_root('parent_tab');
pg_partition_root
-------------------
parent_tab
(1 row)
CREATE TABLE single_tab ();
SELECT pg_partition_root('single_tab');
pg_partition_root
-------------------
single_tab
(1 row)
最后，将这两个函数结合使用，只需知道分区树的一个成员，就能获取完整分区树的信息：
SELECT * FROM pg_partition_tree(pg_partition_root('child_35_40'));
relid | parentrelid | isleaf | level
-------------+-------------+--------+-------
parent_tab | null | f | 0
child_0_10 | parent_tab | t | 1
child_10_20 | parent_tab | t | 1
child_20_30 | parent_tab | t | 1
child_30_40 | parent_tab | f | 1
child_30_35 | child_30_40 | t | 2
child_35_40 | child_30_40 | t | 2
(7 rows)
最后需要注意的是，如果输入涉及不能作为分区树一部分的关系类型（如视图或物化视图），这些函数会返回 NULL 而非报错。这使得创建例如扫描 pg_class 的 SQL 查询更加容易，因为无需根据关系类型使用更多的 WHERE 过滤条件。
非常不错的体验，希望这几个函数能帮助到你。