Question Details

No question body available.

Tags

sql postgresql postgresql-16 polardb

Answers (1)

February 26, 2026 Score: 2 Rep: 30,507 Quality: Medium Completeness: 80%

The parent table isn't really a table. Whatever you do to it, is actually re-routed to its underlying partitions that hold the actual data so an attempt to create index on theparent by default tries to cascade to all partitions.

You can create index on only theparent but that yields an index that remains invalid until you create and attach to it a complete set of indices on all underlying partitions.

It is possible to create this whole index structure (mostly) concurrently: you can use dblink to open parallel sessions and create partition indices from inside each of those, all at once. demo at dbfiddle

create extension if not exists dblink;
create index ptpx on ONLY yourpartitionedtableparent(v);

select concatws('',child.relname,'ptpx',rownumber()over()) as newindexname ,dblinkconnect(concatws('',child.relname,'ptpx',rownumber()over()),'') ,nmspchild.nspname as childschema ,child.relname as child from pginherits join pgclass as parent on pginherits.inhparent = parent.oid join pgclass as child on pginherits.inhrelid = child.oid join pgnamespace as nmspparent on nmspparent.oid = parent.relnamespace join pgnamespace as nmspchild on nmspchild.oid = child.relnamespace where parent.relname='yourpartitionedtableparent' and nmspparent.nspname='yourptableschema';

select dblinkexec( newindexname--my connections are named the same as indices ,format($q$ CREATE INDEX CONCURRENTLY %1$I ON %2$I.%3$I(v); $q$ ,newindexname ,childschema ,child )) from targetpartitions;

select dblinkdisconnect(newindexname) from targetpartitions;

Since you can't concurrently run multiple alter commands against the same parent index, a PL/pgSQL loop is enough to dynamically attach them one by one.

do $d$
declare n text;
begin
for n in select newindexname from targetpartitions loop
  execute format($q$ ALTER INDEX ptpx ATTACH PARTITION %1$I; $q$
                 ,newindex_name);
end loop;
end $d$ language plpgsql;

This sort of thing is also what the doc suggests in 5.12.2.2. Partition Maintenance, the section discussing the limitation:

However, one limitation when creating new indexes on partitioned tables is that it is not possible to use the CONCURRENTLY qualifier, which could lead to long lock times. To avoid this, you can use CREATE INDEX ON ONLY the partitioned table, which creates the new index marked as invalid, preventing automatic application to existing partitions. Instead, indexes can then be created individually on each partition using CONCURRENTLY and attached to the partitioned index on the parent using ALTER INDEX ... ATTACH PARTITION. Once indexes for all the partitions are attached to the parent index, the parent index will be marked valid automatically.

The concurrently option is rejected inside PL/pgSQL routines, even when used in dynamically constructed SQL statements, which is why workarounds like this are necessary.