public inbox for [email protected]help / color / mirror / Atom feed
Indexing Strategy for Partitioned Table in PostgreSQL 15.13 4+ messages / 4 participants [nested] [flat]
* Indexing Strategy for Partitioned Table in PostgreSQL 15.13 @ 2025-08-04 05:25 Mahesh Shetty <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Mahesh Shetty @ 2025-08-04 05:25 UTC (permalink / raw) To: [email protected] Hello Team, We’re running PostgreSQL version 15.13. I have a large partitioned table with around 100 partitions, and we're planning to add an index to it. I'm a bit concerned about the potential impact and duration of the indexing process, and I’d appreciate your inputs on the following: 1. If I run CREATE INDEX CONCURRENTLY on the *parent table*, will it automatically create indexes concurrently on all its partitions? 2. Are there any recommended or more efficient approaches for indexing a heavily partitioned table that I should consider? Thanks in advance for your guidance. Regards, Mahesh ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 @ 2025-08-04 05:35 David G. Johnston <[email protected]> parent: Mahesh Shetty <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: David G. Johnston @ 2025-08-04 05:35 UTC (permalink / raw) To: Mahesh Shetty <[email protected]>; +Cc: [email protected] <[email protected]> On Sunday, August 3, 2025, Mahesh Shetty <[email protected]> wrote: > > I have a large partitioned table with around 100 partitions, and we're > planning to add an index to it. I'm a bit concerned about the potential > impact and duration of the indexing process, > It will end very quickly… > and I’d appreciate your inputs on the following: > > 1. > > If I run CREATE INDEX CONCURRENTLY on the *parent table*, will it > automatically create indexes concurrently on all its partitions? > > Sometimes it’s best to just try (I didn’t though)…but the answer is explicitly documented (see create index) Concurrent builds for indexes on partitioned tables are currently not supported. 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. In this case, building the partitioned index is a metadata only operation. David J. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 @ 2025-08-06 02:40 DINESH NAIR <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: DINESH NAIR @ 2025-08-06 02:40 UTC (permalink / raw) To: David G. Johnston <[email protected]>; Mahesh Shetty <[email protected]>; +Cc: Pgsql-admin <[email protected]> Hi, For partition tables concurrent indexes are not supported(checked in postgres 17.5 version ) Work around will be to create normal indexes on the main table. Thanks Dinesh Nair ________________________________ From: David G. Johnston <[email protected]> Sent: Monday, August 4, 2025 11:05 AM To: Mahesh Shetty <[email protected]> Cc: [email protected] <[email protected]> Subject: Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. On Sunday, August 3, 2025, Mahesh Shetty <[email protected]<mailto:[email protected]>> wrote: I have a large partitioned table with around 100 partitions, and we're planning to add an index to it. I'm a bit concerned about the potential impact and duration of the indexing process, It will end very quickly… and I’d appreciate your inputs on the following: 1. If I run CREATE INDEX CONCURRENTLY on the parent table, will it automatically create indexes concurrently on all its partitions? Sometimes it’s best to just try (I didn’t though)…but the answer is explicitly documented (see create index) Concurrent builds for indexes on partitioned tables are currently not supported. 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. In this case, building the partitioned index is a metadata only operation. David J. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13 @ 2025-08-06 03:16 Ron Johnson <[email protected]> parent: Mahesh Shetty <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Ron Johnson @ 2025-08-06 03:16 UTC (permalink / raw) To: pgsql-admin On Mon, Aug 4, 2025 at 1:25 AM Mahesh Shetty <[email protected]> wrote: > Hello Team, > > We’re running PostgreSQL version 15.13. > > I have a large partitioned table with around 100 partitions, and we're > planning to add an index to it. I'm a bit concerned about the potential > impact and duration of the indexing process, and I’d appreciate your inputs > on the following: > > 1. > > If I run CREATE INDEX CONCURRENTLY on the *parent table*, will it > automatically create indexes concurrently on all its partitions? > 2. > > Are there any recommended or more efficient approaches for indexing a > heavily partitioned table that I should consider? > > Search for "CREATE INDEX ON ONLY" in https://www.postgresql.org/docs/15/ddl-partitioning.html That'll tell you how to build indices in parallel and concurrently. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-06 03:16 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-04 05:25 Indexing Strategy for Partitioned Table in PostgreSQL 15.13 Mahesh Shetty <[email protected]> 2025-08-04 05:35 ` David G. Johnston <[email protected]> 2025-08-06 02:40 ` DINESH NAIR <[email protected]> 2025-08-06 03:16 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox