MIME-Version: 1.0
References: 
 <CAJPiW+hfwGuGQ8fGKSB1-xU6O==eAhVGKrz_FHD3A0KVLv1KRg@mail.gmail.com>
In-Reply-To: 
 <CAJPiW+hfwGuGQ8fGKSB1-xU6O==eAhVGKrz_FHD3A0KVLv1KRg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 5 Aug 2025 23:16:39 -0400
Message-ID: 
 <CANzqJaAhS+dKz0yhz0UDSERfBVzbR1A8=oOtprjaQtPe4xB7gw@mail.gmail.com>
Subject: Re: Indexing Strategy for Partitioned Table in PostgreSQL 15.13
To: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009b19c8063ba9c5e6"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaAhS%2BdKz0yhz0UDSERfBVzbR1A8%3DoOtprjaQtPe4xB7gw%40mail.gmail.com>
Precedence: bulk

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

On Mon, Aug 4, 2025 at 1:25=E2=80=AFAM Mahesh Shetty <maheshetty20@gmail.co=
m> wrote:

> Hello Team,
>
> We=E2=80=99re 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=E2=80=99d appreciate y=
our 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.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Aug 4, 2025 at 1:25=E2=80=AFAM Ma=
hesh Shetty &lt;<a href=3D"mailto:maheshetty20@gmail.com">maheshetty20@gmai=
l.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container">=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><p>Hello Team,</p>
<p>We=E2=80=99re running PostgreSQL version 15.13.</p>
<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, 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>
<li>
<p>Are there any recommended or more efficient approaches for indexing a he=
avily partitioned table that I should consider?</p></li></ol></div></div></=
blockquote><div>Search for &quot;CREATE INDEX ON ONLY&quot; in <a href=3D"h=
ttps://www.postgresql.org/docs/15/ddl-partitioning.html">https://www.postgr=
esql.org/docs/15/ddl-partitioning.html</a></div></div><div><br></div><div>T=
hat&#39;ll tell you how to build indices in parallel and concurrently.</div=
><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div d=
ir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted=
&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div=
><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000009b19c8063ba9c5e6--