Re: REINDEX in tables

On Wed, Oct 25, 2023 at 11:33:11AM +0200, Andreas Kretschmer wrote:
> Am 25.10.23 um 11:24 schrieb Matthias Apitz:
>> We have a client who run REINDEX in certain tables of the database of
>> our application (on Linux with PostgreSQL 13.x):
>>
>> REINDEX TABLE CONCURRENTLY d83last;
>> REINDEX TABLE CONCURRENTLY d86plz;
>> REINDEX TABLE CONCURRENTLY ig_memtable;
>> REINDEX TABLE CONCURRENTLY ig_dictionary;
>> REINDEX TABLE CONCURRENTLY ig_dictionary;
>> REINDEX TABLE CONCURRENTLY d50zweig ;
>> REINDEX TABLE CONCURRENTLY d50zweig ;
>>
>> We as the software vendor and support, do not use or recommend this
>> procedure, because we have own SQL files for creating or deleting
>> indices in the around 400 tables.
>>
>> The client is now concerned about the issue that the number of
>> rows in some of the above tables has increased. Is this possible?
>
> In principle, there is nothing wrong with doing this in a maintenance
> window, for example.

If you have a maintenance window where your production server is not
going to be active, you may want to just do a more aggressive REINDEX
without CONCURRENTLY as that's going to be cheaper and faster.
CONCURRENTLY is useful even in non-maintenance cases as it allows
concurrent reads and writes to happen on the index while running the
operation. CONCURRENTLY is much slower of course, as it needs to wait
two times for older snapshots held by concurrent sessions when the
index build is finished and when the index built gets validated.
--
Michael