PostgreSQL 12: 新增 pg_partition_tree() 函数显示分区表信息

  1. 云栖社区>
  2. PostgreSQL技术进阶>
  3. 博客>
  4. 正文

PostgreSQL 12: 新增 pg_partition_tree() 函数显示分区表信息

francs.tan 2019-07-18 08:42:00 浏览1583
展开阅读全文

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 提供的分区函数很容易做到这点。

尽管二维分区表的使用并不是很多,分区表函数提供了分区表查询的另一种途径。

参考

新书推荐

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

链接:https://item.jd.com/12405774.html

_5_PostgreSQL_

网友评论

登录后评论
0/500
评论
francs.tan
+ 关注
所属云栖号: PostgreSQL技术进阶