Introduction: PostgreSQL 12 adds three new partition query functions, as follows: pg_partition_tree(regclass): Returns detailed information of the partition table, such as partition name, upper-level partition name, whether it is a leaf node, level, level 0 indicates the top-level parent table.
PostgreSQL 12 adds three new partition query functions, as follows:
- pg_partition_tree(regclass): Returns the detailed information of the partition table, such as partition name, upper-level partition name, whether it is a leaf node, and level. Level 0 represents the top-level parent table.
- pg_partition_ancestors(regclass): Returns the upper-level partition name, including the current-level partition name.
- pg_partition_root(regclass): Returns the top-level parent table name.
Release Notes
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.
Environment preparation: Create a two-dimensional partition table
Create the parent table as follows:
CREATE TABLE userinfo ( userid int4, username character varying(64), ctime timestamp(6) without time zone ) PARTITION BY HASH(userid);
Create the first level partition as follows:
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 a second-level partition as follows:
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');
Use the meta command to view partition table information, as follows:
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)
Note: Most of the information of the partition table can be viewed above, but the information of the secondary partition is not displayed. The userinfo_p0 line only displays PARTITIONED and does not list the secondary partition information.
If you want to view the secondary partition information, do the following:
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 function
Use the pg_partition_tree() function to view partition table information, as follows:
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)
Note: The pg_partition_tree() function lists all partitions of the partition table, the upper-level partition, whether it is a leaf node, and the level information of the current partition.
pg_partition_ancestors function
The pg_partition_ancestors function returns the name of the upper-level partition, including the name of the current-level partition, as follows:
mydb=> SELECT pg_partition_ancestors('userinfo_p0');
pg_partition_ancestors
--------------------------
userinfo_p0
userinfo
(2 rows)
pg_partition_root function
The pg_partition_root() function returns the top-level parent table name, as follows:
mydb=> SELECT pg_partition_root('userinfo_p0_201907');
pg_partition_root
-------------------
userinfo
(1 row)
Verify data distribution
Finally, verify the userinfo data distribution of the two-dimensional partition table and insert test data, as follows:
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;
Verify the data distribution as follows:
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)
Based on the table size, it is initially determined that only the underlying partition stores data.
View the distribution of secondary partition data, as follows:
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)
View the three-level partition data distribution, as follows:
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
Summary
For one-dimensional partitioned tables, the meta-commands provided by PostgreSQL are enough to view the complete information of the partition, but for multi-dimensional partitioned tables, the meta-commands cannot view detailed partition information. The partition functions provided by PostgreSQL 12 can easily do this.
Although two-dimensional partitioned tables are not widely used, the partitioned table function provides another way to query partitioned tables.
Reference
- Waiting for PostgreSQL 12 – Add pg_partition_tree to display information about partitions
- Postgres 12 highlight – Functions for partitions