A recursive CTE to get information about partitions
I’m used to partitioning, it allows me to quickly and precisely split data across different tables. PostgreSQL 10 introduced the native partitioning, and since that I’m using native partitioning over inheritance whenever it is possible.But how to get a quick overview of the partition status? I mean, knowing which partition is growing the more?
In the beginning I was thinking to write a function to do that task, quickly finding myself iterating recursively over
pg_inherits, the table that links partitions to their parents. But the keyword here is recursively: PostgreSQL provides recursive Common Table Expression, and a quick search revelead I was right: it is possible to do it with a single CTE. Taking inspiration from this mailing list message, here it is a simple CTE to get a partition status (you can find it on my GitHub repository):
WITH RECURSIVE inheritance_tree AS (
SELECT c.oid AS table_oid
, c.relname AS table_name
, NULL::name AS table_parent_name
, c.relispartition AS is_partition
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'p'
AND c.relispartition = false
UNION ALL
SELECT inh.inhrelid AS table_oid
, c.relname AS table_name
, cc.relname AS table_parent_name
, c.relispartition AS is_partition
FROM inheritance_tree it
JOIN pg_inherits inh ON inh.inhparent = it.table_oid
JOIN pg_class c ON inh.inhrelid = c.oid
JOIN pg_class cc ON it.table_oid = cc.oid
)
SELECT
it.table_name
, c.reltuples
, c.relpages
, CASE p.partstrat
WHEN 'l' THEN 'BY LIST'
WHEN 'r' THEN 'BY RANGE'
ELSE 'not partitioned'
END AS partitionin_type
, it.table_parent_name
, pg_get_expr( c.relpartbound, c.oid, true ) AS partitioning_values
, pg_get_expr( p.partexprs, c.oid, true ) AS sub_partitioning_values
FROM inheritance_tree it
JOIN pg_class c ON c.oid = it.table_oid
LEFT JOIN pg_partitioned_table p ON p.partrelid = it.table_oid
ORDER BY 1,2;
pg_inherits in order to extract the children information.
The query attached to the CTE extracts information like the number of tuples and pages (that’s what I need), and a summary of the partitioning including second level partitioning. Thanks to pg_get_expr it is possible to get a human readable partitioning startegy.
The output results in something like the following:
...
-[ RECORD 5 ]-----------|----------------------------------
table_name | y2018
reltuples | 0
relpages | 0
partitionin_type | BY LIST
table_parent_name | root
partitioning_values | FOR VALUES IN ('2018')
sub_partitioning_values | date_part('month'::text, mis_ora)
...
-[ RECORD 15 ]----------|----------------------------------
table_name | y2018m10
reltuples | 1.48956e+07
relpages | 139212
partitionin_type | not partitioned
table_parent_name | y2018
partitioning_values | FOR VALUES IN ('10')
sub_partitioning_values |
y2018 is child of table root, accepts values '2018' and is partitioned by list, and children are partitioned by month. On the other hand, y2018m10 is not partitioned anymore and is child of y2018'.
That’s a quick glance at the partitioning status in the cluster! Of course, it is possible to improve on this to get more information or restrict it depending on your needs.
UPDATE 2019-06-15
As per discussion reported on thebugs mailing list the query I originally proposed was tricky: while it was working on v11, it was not on upcoming v12 and the reason was that I was erronously casting NULL to text in the non-recursive term and then unioning with a name in the recursive part. Thanks to the explaination by Tom Lane I was able not only to fix the query, but to gain some more knowledge about PostgreSQL!