[SOLVED] create index concurrently on partitioned table

create index concurrently on partitioned table

I am using postgresql 14.1, and I re-created my live database using parititons for some tables.

since i did that, i could create index when the server wasn't live, but when it's live i can only create the using concurrently but unfortunately when I try to create an index concurrently i get an error.

running this:

create index concurrently foo  on foo_table(col1,col2,col3));

provides the error:

ERROR:  cannot create index on partitioned table "foo_table" concurrently

now it's a live server and i cannot create indexes not concurrently and i need to create some indexes in order to improve performance. any ideas how do to that ?

thanks

Solution

No problem. First, use CREATE INDEX CONCURRENTLY to create the index on each partition. Then use CREATE INDEX to create the index on the partitioned table. That will be fast, and the indexes on the partitions will become the partitions of the index.