Postgresql – Is it possible to add a spatial index to a partition with Postgres 10

indexpartitioningpostgresqlpostgresql-10spatial

I am creating declarative partitions like:

CREATE TABLE mydata.my_features (        
    id bigint,
    my_feature1 character(1),
    my_feature2 integer,
    ...
    my_geom geometry
) PARTITION BY LIST (my_feature1);

And using some ad-hoc functions:

CREATE TABLE partitions.%I PARTITION OF cores.rents_core FOR VALUES IN (%L) PARTITION BY LIST (my_feature2)

CREATE TABLE partitions.%I PARTITION OF partitions.%I FOR VALUES IN (%L)

The partitions get created and populated successfully once the data get inserted.

However, when I create the table at the beginning, if I try to add an index like suggested in this Postgres official documentation resource:

Create an index on the key column(s), as well as any other indexes you might want for every partition. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)

like a spatial index:

CREATE INDEX "mydata_my_features_geom_idx" ON mydata.my_features using gist(geom);

I get an error like:

ERROR: cannot create index on partitioned table "my_features"

Is that actually possible add this kind of index or I am doing something wrong?

Best Answer

As documented in the manual

Partitions may have their own indexes, constraints and default values, distinct from those of other partitions. Indexes must be created separately for each partition

(emphasis mine)

So as you are using Postgres 10, you need to create that index manually on each partition (the part you quoted from the manual even shows that the indexes are created for each partition, not for the partitioned table).

With Postgres 11, indexes can defined on the master table and will automatically be created on each partition.

If you need partitioning you should consider upgrading to Postgres 11 anyway as that massively improved the partitioning capabilities (including performance)

Related Question