PostgreSQL 12: New partition reporting functions

PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (Updating the partition key now works in PostgreSQL 11, Insert…on conflict with partitions finally works in PostgreSQL 11, Local partitioned indexes in PostgreSQL 11, Hash Partitioning in PostgreSQL 11) and PostgreSQL 12 goes even further. Beside that foreign keys can now reference partitioned tables three new functions made it into PostgreSQL 12 that will give you information about your partitioned tables.

Our little demo setup consist of a list partitioned table with three partitions:

postgres=# create table animals ( id int generated always as identity ( cache 10 ),
postgres(#                        name text unique,
postgres(#                        primary key(id,name)
postgres(#                      ) 
postgres-#                      partition by list (name);
CREATE TABLE
postgres=# create table animals_elephants
postgres-#   partition of animals
postgres-#   for values in ('elephant');
CREATE TABLE
postgres=# create table animals_cats
postgres-#   partition of animals
postgres-#   for values in ('cats');
CREATE TABLE
postgres=# create table animals_dogs
postgres-#   partition of animals
postgres-#   for values in ('dogs');
CREATE TABLE

What already worked in PostgreSQL 11 is that psql will give you information about your partitions:

postgres=# d animals
                   Partitioned table "public.animals"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | generated always as identity
 name   | text    |           | not null | 
Partition key: LIST (name)
Indexes:
    "animals_pkey" PRIMARY KEY, btree (id, name)
    "animals_name_key" UNIQUE CONSTRAINT, btree (name)
Number of partitions: 3 (Use d+ to list them.)

Using “d+” will even show you more information:

postgres=# d+ animals
                                       Partitioned table "public.animals"
 Column |  Type   | Collation | Nullable |           Default            | Storage  | Stats target | Description 
--------+---------+-----------+----------+------------------------------+----------+--------------+-------------
 id     | integer |           | not null | generated always as identity | plain    |              | 
 name   | text    |           | not null |                              | extended |              | 
Partition key: LIST (name)
Indexes:
    "animals_pkey" PRIMARY KEY, btree (id, name)
    "animals_name_key" UNIQUE CONSTRAINT, btree (name)
    "animals_i1" btree (name)
Partitions: animals_cats FOR VALUES IN ('cats'),
            animals_dogs FOR VALUES IN ('dogs'),
            animals_elephants FOR VALUES IN ('elephant')

Now with PostgreSQL 12 there are three new functions which you can use get information about your partitioned tables and the partitions itself. The first one will give you the partition tree:

postgres=# select pg_partition_tree('animals');
        pg_partition_tree        
---------------------------------
 (animals,,f,0)
 (animals_elephants,animals,t,1)
 (animals_dogs,animals,t,1)
 (animals_cats,animals,t,1)
(4 rows)

The second one will give you the ancestor relations of the given partition:

postgres=# select pg_partition_ancestors('animals_dogs');
 pg_partition_ancestors 
------------------------
 animals_dogs
 animals
(2 rows)

The third one will give you the root for a given partition:

postgres=# select pg_partition_root('animals_cats');
 pg_partition_root 
-------------------
 animals
(1 row)

All of them do also work for partitioned indexes:

postgres=# create index animals_i1 on animals (name);
CREATE INDEX
postgres=# select pg_partition_tree('animals_i1');
              pg_partition_tree              
---------------------------------------------
 (animals_i1,,f,0)
 (animals_cats_name_idx,animals_i1,t,1)
 (animals_dogs_name_idx,animals_i1,t,1)
 (animals_elephants_name_idx,animals_i1,t,1)
(4 rows)

Nice.