create index concurrently on partitioned table

Question

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

Maybe you could get better answers to this [admin] question at dba.stackexchange.com — The Impaler, Feb 02 '22 at 15:23

score 14 · Accepted Answer · answered Feb 02 '22 at 15:24

14

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.

answered Feb 02 '22 at 15:24

Laurenz Albe

209,280
17
206
263

1

do i use the same name for the index ? – ufk Feb 02 '22 at 15:24
1

No, that is not even possible if the partitions are in the same schema. The names of the indexes won't matter, as long as the definition is the same. – Laurenz Albe Feb 02 '22 at 15:36

score 4 · Answer 2 · answered Oct 14 '22 at 17:47

Step 1: Create an index on the partitioned (parent) table

CREATE INDEX foo_idx ON ONLY foo (col1, col2, col3);

This step creates an invalid index. That way, none of the table partitions will get the index applied automatically.

Step 2: Create the index for each partition using CONCURRENTLY and attach to the parent index

CREATE INDEX CONCURRENTLY foo_idx_1
  ON foo_1 (col1, col2, col3);
ALTER INDEX foo_idx
  ATTACH PARTITION foo_idx_1;

Repeat this step for every partition index.

Step 3: Verify that the parent index created at the beginning (Step 1) is valid. Once indexes for all partitions are attached to the parent index, the parent index is marked valid automatically.

SELECT * FROM pg_index WHERE pg_index.indisvalid = false;

The query should return zero results. If thats not the case then check your script for mistakes.

create index concurrently on partitioned table

2 Answers2

Linked