2

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?

2

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)

  • true! erroneously I was reading postgresql.org/docs/11/… that is relative to version 11 of Postgres which looks allowing to put indexes directly on the main table avoiding repetitive indexes. – Randomize Feb 20 at 19:50
  • @Randomize: I really recommend to upgrade to Postgres 11 if partitioning is that important to you. v10 only added very basic partitioning support. Postgres 11 added a lot of important features and improved performance. – a_horse_with_no_name Feb 20 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.