MIME-Version: 1.0
References: <CAKna9VaOt_FPSSdMQaF0abiAPLdN1jB9renORV+ky2mP1_nDRw@mail.gmail.com>
In-Reply-To: <CAKna9VaOt_FPSSdMQaF0abiAPLdN1jB9renORV+ky2mP1_nDRw@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Sun, 9 Jun 2024 10:36:02 +0530
Message-ID: <CAD=mzVXtgnFxk0uE6=yaJX8JYY6kt4UiUc-kqi=MyUOCmWp3hg@mail.gmail.com>
Subject: Re: Creating big indexes
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000076e1c061a6dfe88"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVXtgnFxk0uE6%3DyaJX8JYY6kt4UiUc-kqi%3DMyUOCmWp3hg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jun 8, 2024 at 12:53=E2=80=AFPM Lok P <loknath.73@gmail.com> wrote:

> Hello,
> 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 t=
he
> incoming transactions(mainly inserts) 24/7 , which are mostly happening o=
n
> 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 session=
s
> 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
>
>
>
 You can first create the index on the table using the "On ONLY"keyword,
something as below.

CREATE INDEX idx ON ONLY tab(col1);

Then create indexes on each partition in "concurrently" from multiple
sessions in chunks.

CREATE INDEX CONCURRENTLY idx_1    ON tab_part1(col1);
CREATE INDEX CONCURRENTLY idx_2    ON tab_part2(col1);

After this step finishes the table level index which was created in the
first step will be in valid state automatically.

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sat, Jun 8, 2024 at 12:53=E2=80=AFPM L=
ok P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&g=
t; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr">Hello,<div>We have a few tables having size ~5TB and are partiti=
oned on a timestamp column. They have ~90 partitions in them and are storin=
g 90 days of data. We want to create a couple of indexes on those tables. T=
hey are getting the incoming transactions(mainly inserts) 24/7 , which are =
mostly happening on the current day/live partition. Its RDS postgres versio=
n 15.4. So in this situation<br><br></div><div>Should we go with below i.e =
one time create index command on the table..<br><br>CREATE INDEX CONCURRENT=
LY idx1 ON tab(column_name);<br>Or <br>create index on individual partition=
s from different sessions, say for example create indexes on 30 partitions =
each from three different sessions so as to finish all the 90 partitions fa=
ster?<br>CREATE INDEX CONCURRENTLY idx1 ON tab_part1(column_name);<br>CREAT=
E INDEX CONCURRENTLY idx1 ON tab_part2(column_name);</div><div>.....</div><=
div>.....<br><br>Basically I have three questions:<br>1)If we can do this i=
ndex creation activity online without impacting the incoming transactions o=
r do we have to take down time for this activity? <br>2)If we can&#39;t do =
it online then , what is the fastest method to do this index creation activ=
ity ?<br><div>3)Should we change the DB parameters in a certain way to make=
 the process faster? We have currently set below parameters<br><br>max_para=
llel_workers-16<br>max_parallel_maintenance_workers-2<br>maintenance_work_m=
em- 4GB<br></div><div><br></div><div><br></div></div></div></blockquote><di=
v><br></div><div>=C2=A0You can first create the index on the table using th=
e &quot;On ONLY&quot;keyword, something as below.</div><div><br></div><div>=
CREATE INDEX idx ON ONLY tab(col1);<br></div><div><br></div><div>Then creat=
e indexes on each partition in &quot;concurrently&quot; from multiple sessi=
ons in chunks.</div><div><br></div><div>CREATE INDEX CONCURRENTLY idx_1=C2=
=A0 =C2=A0 ON tab_part1(col1);<br>CREATE INDEX CONCURRENTLY idx_2=C2=A0 =C2=
=A0 ON tab_part2(col1);<br></div><div><br></div><div>After this step finish=
es the table level index which was created in the first step will be in val=
id state automatically.</div><div><br></div></div></div>

--000000000000076e1c061a6dfe88--