MIME-Version: 1.0
In-Reply-To: 
 <CAJPiW+hfwGuGQ8fGKSB1-xU6O==eAhVGKrz_FHD3A0KVLv1KRg@mail.gmail.com>
References: 
 <CAJPiW+hfwGuGQ8fGKSB1-xU6O==eAhVGKrz_FHD3A0KVLv1KRg@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sun, 3 Aug 2025 22:35:13 -0700
Message-ID: 
 <CAKFQuwanhAmgSdh4ou=grR0FiwnM4dEojELmtHTmkyjtJEx5sw@mail.gmail.com>
Subject: Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13
To: Mahesh Shetty <maheshetty20@gmail.com>
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cfc232063b83781a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwanhAmgSdh4ou%3DgrR0FiwnM4dEojELmtHTmkyjtJEx5sw%40mail.gmail.com>
Precedence: bulk

--000000000000cfc232063b83781a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sunday, August 3, 2025, Mahesh Shetty <maheshetty20@gmail.com> 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=E2=80=A6

> and I=E2=80=99d 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=E2=80=99s best to just try (I didn=E2=80=99t though)=E2=80=
=A6but 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.

--000000000000cfc232063b83781a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sunday, August 3, 2025, Mahesh Shetty &lt;<a href=3D"mailto:maheshetty20=
@gmail.com">maheshetty20@gmail.com</a>&gt; wrote:<blockquote class=3D"gmail=
_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:=
1ex"><div dir=3D"ltr"><div dir=3D"ltr">
<p>I have a large partitioned table with around 100 partitions, and we&#39;=
re planning to add an index to it. I&#39;m a bit concerned about the potent=
ial impact and duration of the indexing process,</p></div></div></blockquot=
e><div><br></div><div>It will end very quickly=E2=80=A6=C2=A0</div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc s=
olid;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><p>and I=E2=80=99d=
 appreciate your inputs on the following:</p>
<ol>
<li>
<p>If I run <code>CREATE INDEX CONCURRENTLY</code> on the <strong>parent ta=
ble</strong>, will it automatically create indexes concurrently on all its =
partitions?</p></li></ol></div></div></blockquote><div>Sometimes it=E2=80=
=99s best to just try (I didn=E2=80=99t though)=E2=80=A6but the answer is e=
xplicitly documented (see create index)</div><div><br></div><div><span styl=
e=3D"font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">Concurr=
ent builds for indexes on partitioned tables are currently not supported. H=
owever, 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.<=
/span></div><div><br></div><div>David J.</div><div><br></div>

--000000000000cfc232063b83781a--