pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex recreation details with REINDEX TABLE CONCURRENTLY
6+ messages / 5 participants
[nested] [flat]
* Index recreation details with REINDEX TABLE CONCURRENTLY
@ 2023-05-10 15:04 Matthew Planchard <matthew@specprotected.com>
0 siblings, 3 replies; 6+ messages in thread
From: Matthew Planchard @ 2023-05-10 15:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
We're working on setting up some regular jobs to reindex tables where we
wind up generating a lot of index bloat. We're planning on using REINDEX
... CONCURRENTLY. We'd like to reindex all of the indexes on the tables.
In some of our environments, these tables are very large and under high
load, and we want to minimize the resource consumption of index
recreation if possible.
With that in mind, my question is: does REINDEX TABLE CONCURRENTLY
operate on the indexes of the table in parallel, or sequentially? If
in parallel, I imagine we would see less DB resource utilization by
updating one index at a time.
Thanks in advance for any help you can provide.
Best,
Matthew
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index recreation details with REINDEX TABLE CONCURRENTLY
@ 2023-05-10 20:36 Ron <ronljohnsonjr@gmail.com>
parent: Matthew Planchard <matthew@specprotected.com>
2 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2023-05-10 20:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/10/23 10:04, Matthew Planchard wrote:
> Hello,
>
> We're working on setting up some regular jobs to reindex tables where we
> wind up generating a lot of index bloat. We're planning on using REINDEX
> ... CONCURRENTLY. We'd like to reindex all of the indexes on the tables.
>
> In some of our environments, these tables are very large and under high
> load, and we want to minimize the resource consumption of index
> recreation if possible.
>
> With that in mind, my question is: does REINDEX TABLE CONCURRENTLY
> operate on the indexes of the table in parallel, or sequentially? If
> in parallel, I imagine we would see less DB resource utilization by
> updating one index at a time.
https://www.postgresql.org/docs/11/sql-createindex.html
The CREATE INDEX page certainly mentions parallel index builds. It would
definitely be very good to know whether REINDEX TABLE CONCURRENTLY can have
multiple "threads" leveraging table data already in cache so as not to have
to reread the whole table each time.
That (concurrent CREATE INDEX statements) really sped things up Back In The
Day).
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index recreation details with REINDEX TABLE CONCURRENTLY
@ 2023-05-10 21:01 James Fraley <jamesfraley@yahoo.com>
parent: Matthew Planchard <matthew@specprotected.com>
2 siblings, 1 reply; 6+ messages in thread
From: James Fraley @ 2023-05-10 21:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Matthew Planchard <matthew@specprotected.com>
How do I get off this list?
Respectfully,
James Fraley
Anima Sana in Corpore Sano
On Wednesday, May 10, 2023 at 01:59:37 PM EDT, Matthew Planchard <matthew@specprotected.com> wrote:
Hello,
We're working on setting up some regular jobs to reindex tables where we
wind up generating a lot of index bloat. We're planning on using REINDEX
... CONCURRENTLY. We'd like to reindex all of the indexes on the tables.
In some of our environments, these tables are very large and under high
load, and we want to minimize the resource consumption of index
recreation if possible.
With that in mind, my question is: does REINDEX TABLE CONCURRENTLY
operate on the indexes of the table in parallel, or sequentially? If
in parallel, I imagine we would see less DB resource utilization by
updating one index at a time.
Thanks in advance for any help you can provide.
Best,
Matthew
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index recreation details with REINDEX TABLE CONCURRENTLY
@ 2023-05-11 16:09 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Matthew Planchard <matthew@specprotected.com>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2023-05-11 16:09 UTC (permalink / raw)
To: Matthew Planchard <matthew@specprotected.com>; pgsql-admin@lists.postgresql.org
On Wed, 2023-05-10 at 10:04 -0500, Matthew Planchard wrote:
> We're working on setting up some regular jobs to reindex tables where we
> wind up generating a lot of index bloat. We're planning on using REINDEX
> ... CONCURRENTLY. We'd like to reindex all of the indexes on the tables.
>
> In some of our environments, these tables are very large and under high
> load, and we want to minimize the resource consumption of index
> recreation if possible.
>
> With that in mind, my question is: does REINDEX TABLE CONCURRENTLY
> operate on the indexes of the table in parallel, or sequentially? If
> in parallel, I imagine we would see less DB resource utilization by
> updating one index at a time.
If you use REINDEX TABLE CONCURRENTLY, the indexes will be built one
after the other. Set "max_parallel_maintenance_workers" to 0 to keep
the resource utilization low (at the price of a longer duration).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index recreation details with REINDEX TABLE CONCURRENTLY
@ 2023-05-11 16:19 Katherine Mcmillan <kmcmi046@uottawa.ca>
parent: James Fraley <jamesfraley@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Katherine Mcmillan @ 2023-05-11 16:19 UTC (permalink / raw)
To: James Fraley <jamesfraley@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi James,
If you have an account, go to: https://lists.postgresql.org/manage/
Click "unsubscribe" next to any/all mailing lists that you wish to unsubscribe from.
If you don't have an account, the instructions (per email client) are here:
https://lists.postgresql.org/unsubscribe/
-Katie
________________________________
From: James Fraley <jamesfraley@yahoo.com>
Sent: Wednesday, May 10, 2023 5:01:18 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Matthew Planchard <matthew@specprotected.com>
Subject: Re: Index recreation details with REINDEX TABLE CONCURRENTLY
Attention : courriel externe | external email
How do I get off this list?
Respectfully,
James Fraley
Anima Sana in Corpore Sano
On Wednesday, May 10, 2023 at 01:59:37 PM EDT, Matthew Planchard <matthew@specprotected.com> wrote:
Hello,
We're working on setting up some regular jobs to reindex tables where we
wind up generating a lot of index bloat. We're planning on using REINDEX
... CONCURRENTLY. We'd like to reindex all of the indexes on the tables.
In some of our environments, these tables are very large and under high
load, and we want to minimize the resource consumption of index
recreation if possible.
With that in mind, my question is: does REINDEX TABLE CONCURRENTLY
operate on the indexes of the table in parallel, or sequentially? If
in parallel, I imagine we would see less DB resource utilization by
updating one index at a time.
Thanks in advance for any help you can provide.
Best,
Matthew
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index recreation details with REINDEX TABLE CONCURRENTLY
@ 2023-05-11 17:03 Matthew Planchard <matthew@specprotected.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Matthew Planchard @ 2023-05-11 17:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
That’s great, thank you very much!
On Thu, May 11, 2023 at 11:09 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Wed, 2023-05-10 at 10:04 -0500, Matthew Planchard wrote:
> > We're working on setting up some regular jobs to reindex tables where we
> > wind up generating a lot of index bloat. We're planning on using REINDEX
> > ... CONCURRENTLY. We'd like to reindex all of the indexes on the tables.
> >
> > In some of our environments, these tables are very large and under high
> > load, and we want to minimize the resource consumption of index
> > recreation if possible.
> >
> > With that in mind, my question is: does REINDEX TABLE CONCURRENTLY
> > operate on the indexes of the table in parallel, or sequentially? If
> > in parallel, I imagine we would see less DB resource utilization by
> > updating one index at a time.
>
> If you use REINDEX TABLE CONCURRENTLY, the indexes will be built one
> after the other. Set "max_parallel_maintenance_workers" to 0 to keep
> the resource utilization low (at the price of a longer duration).
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-05-11 17:03 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-10 15:04 Index recreation details with REINDEX TABLE CONCURRENTLY Matthew Planchard <matthew@specprotected.com>
2023-05-10 20:36 ` Ron <ronljohnsonjr@gmail.com>
2023-05-10 21:01 ` James Fraley <jamesfraley@yahoo.com>
2023-05-11 16:19 ` Katherine Mcmillan <kmcmi046@uottawa.ca>
2023-05-11 16:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-11 17:03 ` Matthew Planchard <matthew@specprotected.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox