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.