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.

Answer from Laurenz Albe on Stack Overflow
🌐
Bobcares
bobcares.com › blog › postgresql-create-index-concurrently-on-partitioned-table
PostgreSQL Create Index Concurrently on partitioned table
August 18, 2023 - Did you know that we can create an index concurrently on a partitioned table by creating an index on the parent table without blocking write operations on the table or its partitions in PostgreSQL?
🌐
Reddit
reddit.com › r/postgresql › how to create big indexes
r/PostgreSQL on Reddit: how to create big indexes
June 8, 2024 -

Hello experts,

We have a few tables having size ~5TB and are partitioned on a timestamp column. They have ~90 partitions in them and are storing 90 days of data. We want to create a couple of indexes on those tables. They are getting the incoming transactions(mainly inserts) 24/7 , which are mostly happening on the current day/live partition. Its RDS postgres version 15.4. So in this situation

Should we go with below i.e one time create index command on the table..

CREATE INDEX CONCURRENTLY idx1 ON tab(column_name);
Or
create index on individual partitions from different sessions, say for example create indexes on 30 partitions each from three different sessions so as to finish all the 90 partitions faster?
CREATE INDEX CONCURRENTLY idx1 ON tab_part1(column_name);
CREATE INDEX CONCURRENTLY idx1 ON tab_part2(column_name);..........

Basically I have three questions:
1)If we can do this index creation activity online without impacting the incoming transactions or do we have to take down time for this activity?
2)If we can't do it online then , what is the fastest method to do this index creation activity ?
3)Should we change the DB parameters in a certain way to make the process faster? We have currently set below parameters

max_parallel_workers-16
max_parallel_maintenance_workers-2
maintenance_work_mem- 4GB

Top answer
1 of 2
7
https://www.visuality.pl/posts/indexing-partitioned-table---postgres-stories Like this article (and the docs) say, I'd recommend creating an "invalid" index on the table as a whole using ONLY, then create the indexes concurrently on the partitions and attach one at a time. A bit slower, but much safer. You can use this query to generate the create index and alter index statements for the partitions SELECT nmsp_parent.nspname AS parent_schema, parent.relname AS parent, nmsp_child.nspname AS child_schema, child.relname AS child, 'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_'|| child.relname || 'on_column_name ON ' || child.relname || '(column_name);' as "idx_create", 'ALTER INDEX idx_table_on_column_name ATTACH PARTITION idx' || child.relname || '_on_column_name;' as "idx_alter" FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE parent.relname='tablename';
2 of 2
1
We have a similar setup and stop the inserts when we have to create a new index. However that's not too often and the system can catch up afterwards. We have 1 partition a week that's about 2TB per partition We also create the partitions weeks in advance with all the indexes needed so they are ready to go when they become active and start getting inserts. Another big help is if you have a faster disk, you can create a new table space on that disk and have just the most used indexes on that faster disk. Then you can have weekly routines that move the indexes to different tablespaces so the active ones are always on the fast disk. Also, on your inserts, if you are using the copy command, you can get a huge performance increase by preprocessing the csv files so each one only has data for one partition, and then on the copy, have it go directly into correct partition table instead of the main/parent table.
🌐
Reddit
reddit.com › r/postgresql › any function like workaround for creating indexes concurrently on a partitioned table?
r/PostgreSQL on Reddit: Any function like workaround for creating indexes concurrently on a partitioned table?
December 23, 2025 -

Hey there, I'm still trying to find such an organized way to create indexes concurrently on a partitioned table.

A normal way to create a proper index without exclusively locking the whole table on a partitioned table is basically

  1. Creating an invalid index on the `parent` table

  2. Creating indexes concurrently for child tables

  3. Attaching the indexes by altering the parent's index

This works fine, but it gets annoying the more partitions you have, because point two and three should be done for every child table.

We could create a stored procedure for it, which would be very easy later just giving some variables and call a procedure, but the problem is `concurrently` since stored procedures and functions are both transactional, there is no way to do that.

Is there an organized way to implement these logics as a procedure or something within the database?

🌐
PostgreSQL
postgresql.org › docs › current › sql-createindex.html
PostgreSQL: Documentation: 18: CREATE INDEX
May 14, 2026 - Additional restrictions apply when unique indexes are applied to partitioned tables; see CREATE TABLE. ... When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done.
🌐
PostgreSQL
postgresql.org › message-id › CAMAof6-=D9jZyBLpbPq7FZJjtuHUtErxyOgAkBkKnVXDj=BDhw@mail.gmail.com
PostgreSQL: CREATE INDEX CONCURRENTLY on partitioned table
October 4, 2024 - Hello, I noticed that the official documentation states that PostgreSQL does not support concurrent creation of indexes in a partitioned index. "Concurrent builds for indexes on partitioned tables are currently not supported.
🌐
Visuality
visuality.pl › home › blog › indexing partitioned table - postgres stories
Indexing partitioned table - Postgres Stories - Blog - Visuality
March 25, 2024 - If tried, the table will be locked while the index is being applied and it may take… long. Luckily, for existing partitioned tables it is possible to apply concurrent solution which doesn’t lock table forever. There's a simple workaround to enable concurrency in this case. CREATE INDEX idx_users_email ON ONLY users (email); CREATE INDEX CONCURRENTLY idx_users_email_1 ON users_1 (email); ALTER INDEX idx_users_email ATTACH PARTITION idx_users_email_1; CREATE INDEX CONCURRENTLY idx_users_email_2 ON users_2 (email); ALTER INDEX idx_users_email ATTACH PARTITION idx_users_email_2; // repeat for all partitions SELECT * FROM pg_index WHERE pg_index.indisvalid = false;
🌐
Postgres Professional
postgrespro.com › list › thread-id › 2494882
Thread: how to create index concurrently on paritioned table : Postgres Professional
Partitioning is necessary for very large tables. However, I found that postgresql does not support create index concurrently on partitioned tables.
🌐
Alibaba Cloud Community
alibabacloud.com › blog › quick-creation-of-a-large-number-of-partition-indexes-in-postgresql_598626
Quick Creation of a Large Number of Partition Indexes in PostgreSQL - Alibaba Cloud Community
February 23, 2022 - How can I quickly create indexes for all partition tables without blocking DML operations (or by temporarily blocking DML operations)? The answer is procedure (PostgreSQL 11 is used as an example): set lock_timeout=xx; -- 避免长时间等锁导致雪崩. loop xx..xxxx create index [if not exists] [concurrently] ?; -- 仅仅在这个过程中堵塞dml commit or rollback; -- 每创建一个索引后都结束事务, 释放这个索引相关的锁.
Find elsewhere
🌐
Medium
aws.plainenglish.io › mastering-postgres-partition-management-d8b14f337fc5
Mastering Postgres partition management | by Manikaran Kathuria | AWS in Plain English
August 23, 2025 - Unfortunately, PostgreSQL does not support adding an index to a partitioned table concurrently. This poses a challenge — how to add an index to a table serving the production table? We solved this by adding the index to a partitioned table following the steps below- Create an invalid index ...
🌐
GitLab
gitlab.com › gitlab.org › merge requests › !45211
Helper to safely add indexes to partitioned tables (!45211) · Merge requests · GitLab.org / GitLab · GitLab
From the Postgres docs here: https://www.postgresql.org/docs/11/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY · Concurrent builds for indexes on partitioned tables are currently not supported.
🌐
t27duck
t27duck.com › posts › 9-adding-an-index-to-a-partitioned-table-and-its-children-in-postgresql-and-rails
Adding an index to a partitioned table and its children in PostgreSQL and Rails | t27duck
While it is not possible to concurrently add an index to the parent table and have it propagate, you can add an index to the parent table only, apply the index concurrently to each of the child tables, and then attach the index to the parent. Below is an example migration for Rails 7 using ...
🌐
Medium
pankajconnect.medium.com › postgresql-partitioned-index-concurrently-procedure-stored-procedure-transaction-986b3ee02ef5
PostgreSQL, partitioned index, concurrently, procedure, stored procedure, transaction | by Pankaj kushwaha | Medium
January 16, 2021 - To sum up, how to quickly create indexes for all partition tables without blocking dml (or temporarily blocking dml)? ... set lock_timeout=xx; - Avoid waiting for a long time for the lock to cause an avalanche. loop xx..xxxx create index [if not exists] [concurrently] ?; - just block dml in the process commit or rollback; - Each time an index is created, the transaction ends and the lock related to this index is released.
🌐
PostgreSQL
postgresql.org › docs › current › ddl-partitioning.html
PostgreSQL: Documentation: 18: 5.12. Table Partitioning
May 14, 2026 - To avoid this, you can use CREATE ... 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 ......
🌐
PostgreSQL
postgresql.org › message-id › CAM527d9iz6+=_c7EqSKaGzjqWvSeCeRVVvHZ1v3gDgjTtvgsbw@mail.gmail.com
PostgreSQL: CONCURRENTLY in example of index of partitioned table
November 27, 2023 - diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 4490e82aa52f3c0e2039047ab57194933d7c0275..d2951cd754c68237cd97eedd9c4353a671ad0995 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4222,7 +4222,7 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 <programlisting> CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales); -CREATE INDEX measurement_usls_200602_idx +CREATE INDEX CONCURRENTLY measurement_usls_200602_idx ON measurement_y2006m02 (unitsales); ALTER INDEX measurement_usls_idx ATTACH PARTITION measurement_usls_200602_idx;
🌐
PostgreSQL
postgresql.org › message-id › df5e91946320d4c3aade07898d4ba728@postgrespro.ru
PostgreSQL: Re: CREATE INDEX CONCURRENTLY on partitioned index
May 28, 2024 - >> <command>CREATE INDEX ... CONCURRENTLY</command> can incur long lock >> times >> on huge partitioned tables, to avoid that you can >> use <command>CREATE INDEX ON ONLY</command> the partitioned table, >> which >> creates the new index marked ...
🌐
PostgreSQL
postgresql.org › docs › 11 › sql-createindex.html
PostgreSQL: Documentation: 11: CREATE INDEX
November 9, 2023 - However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out.
Top answer
1 of 1
3

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 the_parent by default tries to cascade to all partitions.

You can create index on only the_parent 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 db<>fiddle

create index ptpx on ONLY your_partitioned_table_parent(v);

create extension if not exists dblink;

create temp table target_partitions on commit preserve rows
as 
select dblink_connect(concat_ws('_',child.name,'ptpx',row_number()over()),'')
      ,concat_ws('_',child.name,'ptpx',row_number()over()) as new_index_name
      ,child.schema  as child_schema
      ,child.name as child
from pg_inherits as i
cross join lateral pg_get_object_address('table', array[i.inhrelid::regclass::text],'{}')as a
cross join lateral pg_identify_object(a.classid, a.objid, a.objsubid) as child
where i.inhparent = 'your_schema.your_partitioned_table_parent'::regclass;

select dblink_exec( new_index_name--my connections are named the same as indices
                   ,format($q$ CREATE INDEX CONCURRENTLY %1$I ON %2$I.%3$I(v); $q$
                           ,new_index_name
                           ,child_schema
                           ,child ))
from target_partitions;

select dblink_disconnect(new_index_name) 
from target_partitions;

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 new_index_name from target_partitions loop
  execute format($q$ ALTER INDEX ptpx ATTACH PARTITION %1$I; $q$
                 ,new_index_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.

🌐
Postgresql
commitfest.postgresql.org › patch › 2815
CREATE INDEX CONCURRENTLY on partitioned table
CREATE INDEX CONCURRENTLY on partitioned table · Comment/Review · Comment · Review · Change Status · Open statuses · Needs review · Waiting on Author · Ready for Committer · Closed statuses
🌐
PostgreSQL
postgresql.org › message-id › 20200904005113.GC19499@paquier.xyz
PostgreSQL: Re: 回复:how to create index concurrently on partitioned table
September 4, 2020 - We need to use partoid, as returned by find_all_inheritors() for all the members of the partition tree (relid can be a partitioned index or partitioned table in reindex_partitions). > 1) in create_index.sql > > Are these two lines intentional checks that must fail? If so, I propose to > add a comment. > > REINDEX TABLE concur_reindex_part_index; > REINDEX TABLE CONCURRENTLY concur_reindex_part_index; > > A few lines around also look like they were copy-pasted and need a second > look.