Local partitioned indexes in PostgreSQL 11

When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in PostgreSQL 10.

A very simple list partitioned table:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

In PostgreSQL 10 what happens when we create an index on the partitioned table?

postgres=# create index i_test on part (a);
ERROR:  cannot create index on partitioned table "part"

You just can not do it. But you can create indexes on the partitions directly:

postgres=# create index i_test_1 on part_1 (a);
CREATE INDEX
postgres=# create index i_test_2 on part_2 (a);
CREATE INDEX

Lets do the same test with PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Try to create the index on the partitioned table:

postgres=# create index i_test on part (a);
CREATE INDEX
postgres=# d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_a_idx" btree (a)

postgres=# d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_a_idx" btree (a)

The index is cascaded down to all the partitions in PostgreSQL 11 which is really nice. As a side effect of this, when you try this in PostgreSQL 10:

postgres=# alter table part add constraint part_pk primary key(a,list);
ERROR:  primary key constraints are not supported on partitioned tables
LINE 1: alter table part add constraint part_pk primary key(a,list);
                             ^

… you will get an error message telling you that primary keys are not supported on partitioned tables. The same applies here, you can do that on the partitions directly:

postgres=# alter table part_1 add constraint part1_pk primary key(a,list);
ALTER TABLE
postgres=# alter table part_2 add constraint part2_pk primary key(a,list);
ALTER TABLE

Now in PostgreSQL 11 this works as well:

postgres=# alter table part add constraint part_pk primary key(a,list);
ALTER TABLE
postgres=# d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "part_pk" PRIMARY KEY, btree (a, list)
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_pkey" PRIMARY KEY, btree (a, list)
    "part_1_a_idx" btree (a)

postgres=# d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_pkey" PRIMARY KEY, btree (a, list)
    "part_2_a_idx" btree (a)

Quite some improvements to show up in PostgreSQL 11.