[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: Index recreation details with REINDEX TABLE CONCURRENTLY
From:       Matthew Planchard <matthew () specprotected ! com>
Date:       2023-05-11 17:03:40
Message-ID: CAFw+HFSCH53-upkPVmSuthUv80a991g7GpgyepZh29VJevykMA () mail ! gmail ! com
[Download RAW message or body]

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
>

[Attachment #3 (text/html)]

<div><div dir="auto">That's great, thank you very much!</div></div><div><div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, May 11, 2023 at 11:09 \
Laurenz Albe &lt;<a href="mailto:laurenz.albe@cybertec.at" \
target="_blank">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">On \
Wed, 2023-05-10 at 10:04 -0500, Matthew Planchard wrote:<br> &gt; We&#39;re working \
on setting up some regular jobs to reindex tables where we<br> &gt; wind up \
generating a lot of index bloat. We&#39;re planning on using REINDEX<br> &gt; ... \
CONCURRENTLY. We&#39;d like to reindex all of the indexes on the tables.<br> &gt; \
<br> &gt; In some of our environments, these tables are very large and under high<br>
&gt; load, and we want to minimize the resource consumption of index<br>
&gt; recreation if possible.<br>
&gt; <br>
&gt; With that in mind, my question is: does REINDEX TABLE CONCURRENTLY<br>
&gt; operate on the indexes of the table in parallel, or sequentially? If<br>
&gt; in parallel, I imagine we would see less DB resource utilization by<br>
&gt; updating one index at a time.<br>
<br>
If you use REINDEX TABLE CONCURRENTLY, the indexes will be built one<br>
after the other.   Set &quot;max_parallel_maintenance_workers&quot; to 0 to keep<br>
the resource utilization low (at the price of a longer duration).<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div></div>
</div>



[prev in list] [next in list] [prev in thread] [next in thread]