On 30th of October 2018, Michael Paquier committed patch:
Add pg_partition_tree to display information about partitions This new function is useful to display a full tree of partitions with a partitioned table given in output, and avoids the need of any complex WITH RECURSIVE query when looking at partition trees which are deep multiple levels. It returns a set of records, one for each partition, containing the partition's name, its immediate parent's name, a boolean value telling if the relation is a leaf in the tree and an integer telling its level in the partition tree with given table considered as root, beginning at zero for the root, and incrementing by one each time the scan goes one level down. Author: Amit Langote Discussion: https://postgr.es/m/-9a51-ad02-d53e-@lab.ntt.co.jp
Let's imagine you have a table that has been partitioned:
=$ CREATE TABLE users ( id serial NOT NULL, username text NOT NULL, UNIQUE (username) ) PARTITION BY RANGE ( username ); =$ CREATE TABLE users_1 partition OF users (PRIMARY KEY (id)) FOR VALUES FROM (minvalue) TO ('e'); =$ CREATE TABLE users_2 partition OF users (PRIMARY KEY (id)) FOR VALUES FROM ('e') TO ('s'); =$ CREATE TABLE users_3 partition OF users (PRIMARY KEY (id)) FOR VALUES FROM ('s') TO (maxvalue);
It's pretty simple, and all, yet \d doesn't show it clearly:
=$ \d users TABLE "public.users" COLUMN | TYPE | Collation | NULLABLE | DEFAULT ----------+---------+-----------+----------+----------------------------------- id | INTEGER | | NOT NULL | NEXTVAL('users_id_seq'::regclass) username | text | | NOT NULL | Partition KEY: RANGE (username) Indexes: "users_username_key" UNIQUE CONSTRAINT, btree (username) NUMBER OF partitions: 3 (USE \d+ TO list them.)
Sure, I can \d+:
\d+ users TABLE "public.users" COLUMN | TYPE | Collation | NULLABLE | DEFAULT | Storage | Stats target | Description ----------+---------+-----------+----------+-----------------------------------+----------+--------------+------------- id | INTEGER | | NOT NULL | NEXTVAL('users_id_seq'::regclass) | plain | | username | text | | NOT NULL | | extended | | Partition KEY: RANGE (username) Indexes: "users_username_key" UNIQUE CONSTRAINT, btree (username) Partitions: users_1 FOR VALUES FROM (MINVALUE) TO ('e'), users_2 FOR VALUES FROM ('e') TO ('s'), users_3 FOR VALUES FROM ('s') TO (MAXVALUE)
and it does show all partitions, but it could be long, and in certain cases, not fully helpful.
In what cases?
Let's assume we want to further partition users_1:
=$ CREATE TABLE new_users_1 (LIKE users including ALL) partition BY range (username); =$ CREATE TABLE new_users_1_1 partition OF new_users_1 (PRIMARY KEY (id)) FOR VALUES FROM (minvalue) TO ('a'); =$ CREATE TABLE new_users_1_2 partition OF new_users_1 (PRIMARY KEY (id)) FOR VALUES FROM ('a') TO ('c'); =$ CREATE TABLE new_users_1_3 partition OF new_users_1 (PRIMARY KEY (id)) FOR VALUES FROM ('c') TO (maxvalue); =$ DROP TABLE users_1; =$ ALTER TABLE new_users_1 RENAME TO users_1; =$ ALTER TABLE users attach partition users_1 FOR VALUES FROM (MINVALUE) TO ('e');
Now, our old users_1 has been subpartitioned, and \d+ of users no longer shows all partitions:
=$ \d+ users TABLE "public.users" COLUMN | TYPE | Collation | NULLABLE | DEFAULT | Storage | Stats target | Description ----------+---------+-----------+----------+-----------------------------------+----------+--------------+------------- id | INTEGER | | NOT NULL | NEXTVAL('users_id_seq'::regclass) | plain | | username | text | | NOT NULL | | extended | | Partition KEY: RANGE (username) Indexes: "users_username_key" UNIQUE CONSTRAINT, btree (username) Partitions: users_1 FOR VALUES FROM (MINVALUE) TO ('e'), PARTITIONED, users_2 FOR VALUES FROM ('e') TO ('s'), users_3 FOR VALUES FROM ('s') TO (MAXVALUE)
Luckily, the new function reveals all partitions:
=$ SELECT * FROM pg_partition_tree('users'); relid | parentrelid | isleaf | level ---------------+-------------+--------+------- users | | f | 0 users_2 | users | t | 1 users_3 | users | t | 1 users_1 | users | f | 1 new_users_1_1 | users_1 | t | 2 new_users_1_2 | users_1 | t | 2 new_users_1_3 | users_1 | t | 2 (7 ROWS)
Multi-level partitioned tables are not common, but I'm glad that PostgreSQL will have a way to display their details nicely, thanks all.
Congrats on the site redesign but you should be aware that the “typing” title has an annoying side effect: if your browser isn’t expanded enough, the page periodically “jumps” to rewrap the headings. On Chromium, this apparently only happens when the top of the page is visible but on Firefox it happens even way down the page.
Hi!
I’m a DWH developer at kfzteile24 GmbH and want to share our little case. I’m not bound by secrecy on this topic because I’ve already presented it in public before.
We use partitioning quite a lot. We had the need to have 2-level partitioning like the one you describe, to attach tables temporarily as partitions, to have foreign key references between partitions for better query planning, etc.
So far, the best way for us to visualize the data schema has been with DBeaver, and it’s own Postgresql client library. To view the entire schema like you described however, we had to write recursive CTEs that go through pg_* tables. We only did that when we needed some statistics on the hierarchy (table and index size, last modified time of the table file, etc).
We just create all these fancy functions, that clean up stuff, that return underlying structures, that automatically create the whole partition tree, etc, in a specific schema, and use them later on.
I’m at liberty to discuss more, in the interest of experience exchange.