MIME-Version: 1.0
From: Lok P <loknath.73@gmail.com>
Date: Sat, 8 Jun 2024 12:53:03 +0530
Message-ID: <CAKna9VaOt_FPSSdMQaF0abiAPLdN1jB9renORV+ky2mP1_nDRw@mail.gmail.com>
Subject: Creating big indexes
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000191aa0061a5bcaea"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VaOt_FPSSdMQaF0abiAPLdN1jB9renORV%2Bky2mP1_nDRw%40mail.gmail.com>
Precedence: bulk

--000000000000191aa0061a5bcaea
Content-Type: text/plain; charset="UTF-8"

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 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

Regards
Lok

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

<div dir=3D"ltr">Hello,<div>We have a few tables having size ~5TB and are p=
artitioned 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 tab=
les. They are getting the incoming transactions(mainly inserts) 24/7 , whic=
h are mostly happening on the current day/live partition. Its RDS postgres =
version 15.4. So in this situation<br><br></div><div>Should we go with belo=
w i.e one time create index command on the table..<br><br>CREATE INDEX CONC=
URRENTLY idx1 ON tab(column_name);<br>Or <br>create index on individual par=
titions from different sessions, say for example create indexes on 30 parti=
tions each from three different sessions so as to finish all the 90 partiti=
ons faster?<br>CREATE INDEX CONCURRENTLY idx1 ON tab_part1(column_name);<br=
>CREATE 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 index creation activity online without impacting the incoming transact=
ions or 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=
 activity ?<br><div>3)Should we change the DB parameters in a certain way t=
o make the process faster? We have currently set below parameters<br><br>ma=
x_parallel_workers-16<br>max_parallel_maintenance_workers-2<br>maintenance_=
work_mem- 4GB<br></div><div><br></div><div>Regards</div><div>Lok</div></div=
></div>

--000000000000191aa0061a5bcaea--