PostgreSQL 12 新增三个分区查询函数，如下:
- pg_partition_tree(regclass): 返回分区表详细信息，例如分区名称、上一级分区名称、是否叶子结点、层级，层级 0 表示顶层父表。
- pg_partition_ancestors(regclass): 返回上层分区名称，包括本层分区名称。
- pg_partition_root(regclass): 返回顶层父表名称。
发行说明
Add partition introspection functions (Michaël Paquier, Álvaro Herrera, Amit Langote)
New function pg_partition_root() returns the top-most parent of a partition tree, pg_partition_ancestors() reports all ancestors of a partition, and pg_partition_tree() displays information about partitions.
环境准备: 创建二维分区表
创建父表，如下:
CREATE TABLE userinfo (
userid int4,
username character varying(64),
ctime timestamp(6) without time zone
) PARTITION BY HASH(userid);创建第一层分区，如下:
CREATE TABLE userinfo_p0 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 0) PARTITION BY RANGE(ctime);
CREATE TABLE userinfo_p1 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 1);
CREATE TABLE userinfo_p2 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 2);
CREATE TABLE userinfo_p3 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 3);创建第二层分区，如下:
CREATE TABLE userinfo_p0_old PARTITION OF userinfo_p0 FOR VALUES FROM (MINVALUE) TO ('2019-06-01');
CREATE TABLE userinfo_p0_201906 PARTITION OF userinfo_p0 FOR VALUES FROM ('2019-06-01') TO ('2019-07-01');
CREATE TABLE userinfo_p0_201907 PARTITION OF userinfo_p0 FOR VALUES FROM ('2019-07-01') TO ('2019-08-01');使用元命令查看分区表信息，如下:
mydb=> \d+ userinfo
Partitioned table "pguser.userinfo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
userid | integer | | | | plain | |
username | character varying(64) | | | | extended | |
ctime | timestamp(6) without time zone | | | | plain | |
Partition key: HASH (userid)
Partitions: userinfo_p0 FOR VALUES WITH (modulus 4, remainder 0), PARTITIONED,
userinfo_p1 FOR VALUES WITH (modulus 4, remainder 1),
userinfo_p2 FOR VALUES WITH (modulus 4, remainder 2),
userinfo_p3 FOR VALUES WITH (modulus 4, remainder 3)
备注：以上可以查看到分区表大部分信息，但二级分区的信息并没有显示，userinfo_p0 这行只显示 PARTITIONED，没有列出二级分区信息。
若想查看二级分区信息，如下:
mydb=> \d+ userinfo_p0
Partitioned table "pguser.userinfo_p0"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
userid | integer | | | | plain | |
username | character varying(64) | | | | extended | |
ctime | timestamp(6) without time zone | | | | plain | |
Partition of: userinfo FOR VALUES WITH (modulus 4, remainder 0)
Partition constraint: satisfies_hash_partition('16432'::oid, 4, 0, userid)
Partition key: RANGE (ctime)
Partitions: userinfo_p0_201906 FOR VALUES FROM ('2019-06-01 00:00:00') TO ('2019-07-01 00:00:00'),
userinfo_p0_201907 FOR VALUES FROM ('2019-07-01 00:00:00') TO ('2019-08-01 00:00:00'),
userinfo_p0_old FOR VALUES FROM (MINVALUE) TO ('2019-06-01 00:00:00') pg_partition_tree 函数
使用 pg_partition_tree() 函数查看分区表信息，如下:
mydb=> SELECT * FROM pg_partition_tree('userinfo');
relid | parentrelid | isleaf | level
--------------------+-------------+--------+-------
userinfo | | f | 0
userinfo_p0 | userinfo | f | 1
userinfo_p1 | userinfo | t | 1
userinfo_p2 | userinfo | t | 1
userinfo_p3 | userinfo | t | 1
userinfo_p0_201906 | userinfo_p0 | t | 2
userinfo_p0_201907 | userinfo_p0 | t | 2
userinfo_p0_old | userinfo_p0 | t | 2
(8 rows)备注: pg_partition_tree() 函数列出了分区表的所有分区、上一级分区、是否是叶子节点、当前分区所处层级信息。
pg_partition_ancestors 函数
pg_partition_ancestors 函数返回上层分区名称，包括本层分区名称，如下:
mydb=> SELECT pg_partition_ancestors('userinfo_p0');
pg_partition_ancestors
------------------------
userinfo_p0
userinfo
(2 rows)pg_partition_root 函数
pg_partition_root()函数返回最顶层父表名称，如下:
mydb=> SELECT pg_partition_root('userinfo_p0_201907');
pg_partition_root
-------------------
userinfo
(1 row)验证数据分布
最后验证二维分区表 userinfo 数据分布，插入测试数据，如下:
INSERT INTO userinfo(userid,username,ctime) SELECT n, n || '_username',now() FROM generate_series(1,8) n;
INSERT INTO userinfo(userid,username,ctime) SELECT n, n || '_username',now() - interval ' 2 months 'FROM generate_series(1,8) n;验证数据分布，如下:
mydb=> \dt+ userinfo*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------+-------------------+--------+------------+-------------
pguser | userinfo | partitioned table | pguser | 0 bytes |
pguser | userinfo_p0 | partitioned table | pguser | 0 bytes |
pguser | userinfo_p0_201906 | table | pguser | 8192 bytes |
pguser | userinfo_p0_201907 | table | pguser | 0 bytes |
pguser | userinfo_p0_old | table | pguser | 8192 bytes |
pguser | userinfo_p1 | table | pguser | 8192 bytes |
pguser | userinfo_p2 | table | pguser | 8192 bytes |
pguser | userinfo_p3 | table | pguser | 8192 bytes |
(8 rows)根据表大小初步判读仅底层分区存储数据。
查看二级分区数据分布上，如下:
mydb=> SELECT * FROM userinfo_p0;
userid | username | ctime
--------+------------+----------------------------
1 | 1_username | 2019-05-16 09:36:18.83122
1 | 1_username | 2019-07-16 09:36:18.825426
(2 rows)查看三级分区数据分布，如下:
mydb=> SELECT * FROM userinfo_p0_201907;
userid | username | ctime
--------+------------+----------------------------
1 | 1_username | 2019-07-16 09:36:18.825426
(1 row)
mydb=> SELECT * FROM userinfo_p0_201906;
userid | username | ctime
--------+----------+-------
(0 rows)
mydb=> SELECT * FROM userinfo_p0_old;
userid | username | ctime
--------+------------+---------------------------
1 | 1_username | 2019-05-16 09:36:18.83122总结
对于一维分区表，PostgreSQL 提供的元命令足够查看分区的完整信息，但对于多维分区表，元命令无法查看详尽的分区信息，PostgreSQL 12 提供的分区函数很容易做到这点。
尽管二维分区表的使用并不是很多，分区表函数提供了分区表查询的另一种途径。
参考
- Waiting for PostgreSQL 12 – Add pg_partition_tree to display information about partitions
- Postgres 12 highlight - Functions for partitions
新书推荐
最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例!
链接：https://item.jd.com/12405774.html
