Message-ID: <5ec087d5-e07c-e704-96cd-a3f8fb9f91d7@gmail.com>
Date: Wed, 10 May 2023 15:36:02 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.10.0
Subject: Re: Index recreation details with REINDEX TABLE CONCURRENTLY
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <87ild0p6d4.fsf@gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <87ild0p6d4.fsf@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/5ec087d5-e07c-e704-96cd-a3f8fb9f91d7%40gmail.com>
Precedence: bulk

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.