pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
REINDEX in tables
8+ messages / 5 participants
[nested] [flat]

* REINDEX in tables
@ 2023-10-25 09:24 Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 1 reply; 8+ messages in thread

From: Matthias Apitz @ 2023-10-25 09:24 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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?

Thanks

	matthias

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
I am not at war with Russia.
Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
@ 2023-10-25 09:33 ` Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 09:57   ` Re: REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 23:33   ` Re: REINDEX in tables Michael Paquier <michael@paquier.xyz>
  0 siblings, 2 replies; 8+ messages in thread

From: Andreas Kretschmer @ 2023-10-25 09:33 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



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.


Regards, Andreas

-- 
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2023-10-25 09:57   ` Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:59     ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  1 sibling, 1 reply; 8+ messages in thread

From: Matthias Apitz @ 2023-10-25 09:57 UTC (permalink / raw)
  To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: pgsql-general@lists.postgresql.org

El día miércoles, octubre 25, 2023 a las 11:33:11 +0200, Andreas Kretschmer escribió:

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

But, this wasn't the question. It was: can it happen that the number of
rows in thze table will increase by this operation?

	matthias

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

I am not at war with Russia.  Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 09:57   ` Re: REINDEX in tables Matthias Apitz <guru@unixarea.de>
@ 2023-10-25 09:59     ` Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 12:11       ` Re: REINDEX in tables Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 8+ messages in thread

From: Andreas Kretschmer @ 2023-10-25 09:59 UTC (permalink / raw)
  To: Matthias Apitz <guru@unixarea.de>; +Cc: pgsql-general@lists.postgresql.org



Am 25.10.23 um 11:57 schrieb Matthias Apitz:
> El día miércoles, octubre 25, 2023 a las 11:33:11 +0200, Andreas Kretschmer escribió:
>
>> 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.
> But, this wasn't the question. It was: can it happen that the number of
> rows in thze table will increase by this operation?

no, reindex will not add rows to the table.


Regards, Andreas

-- 
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 09:57   ` Re: REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:59     ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2023-10-25 12:11       ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-10-25 12:14         ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 1 reply; 8+ messages in thread

From: Laurenz Albe @ 2023-10-25 12:11 UTC (permalink / raw)
  To: Andreas Kretschmer <andreas@a-kretschmer.de>; Matthias Apitz <guru@unixarea.de>; +Cc: pgsql-general@lists.postgresql.org

On Wed, 2023-10-25 at 11:59 +0200, Andreas Kretschmer wrote:
> Am 25.10.23 um 11:57 schrieb Matthias Apitz:
> > El día miércoles, octubre 25, 2023 a las 11:33:11 +0200, Andreas Kretschmer escribió:
> > > 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?
> > 
> 
> no, reindex will not add rows to the table.

But if the indexes were corrupted before the REINDEX, it is possible that
a query that didn't find a result before the REINDEX can find one afterwards.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 09:57   ` Re: REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:59     ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 12:11       ` Re: REINDEX in tables Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-10-25 12:14         ` Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 13:17           ` Re: REINDEX in tables Bruce Momjian <bruce@momjian.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Andreas Kretschmer @ 2023-10-25 12:14 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; Matthias Apitz <guru@unixarea.de>; +Cc: pgsql-general@lists.postgresql.org



Am 25.10.23 um 14:11 schrieb Laurenz Albe:
> On Wed, 2023-10-25 at 11:59 +0200, Andreas Kretschmer wrote:
>> Am 25.10.23 um 11:57 schrieb Matthias Apitz:
>>> El día miércoles, octubre 25, 2023 a las 11:33:11 +0200, Andreas Kretschmer escribió:
>>>> 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?
>> no, reindex will not add rows to the table.
> But if the indexes were corrupted before the REINDEX, it is possible that
> a query that didn't find a result before the REINDEX can find one afterwards.

Thanks for the addition my friend, that's correct and could be an 
explanation here.


Andreas

-- 
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 09:57   ` Re: REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:59     ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
  2023-10-25 12:11       ` Re: REINDEX in tables Laurenz Albe <laurenz.albe@cybertec.at>
  2023-10-25 12:14         ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2023-10-25 13:17           ` Bruce Momjian <bruce@momjian.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Bruce Momjian @ 2023-10-25 13:17 UTC (permalink / raw)
  To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Matthias Apitz <guru@unixarea.de>; pgsql-general@lists.postgresql.org

On Wed, Oct 25, 2023 at 02:14:49PM +0200, Andreas Kretschmer wrote:
> Am 25.10.23 um 14:11 schrieb Laurenz Albe:
> > On Wed, 2023-10-25 at 11:59 +0200, Andreas Kretschmer wrote:
> > > Am 25.10.23 um 11:57 schrieb Matthias Apitz:
> > > > El día miércoles, octubre 25, 2023 a las 11:33:11 +0200, Andreas Kretschmer escribió:
> > > > > 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?
> > > no, reindex will not add rows to the table.
> > But if the indexes were corrupted before the REINDEX, it is possible that
> > a query that didn't find a result before the REINDEX can find one afterwards.
> 
> Thanks for the addition my friend, that's correct and could be an
> explanation here.

However, if that is happening, there is something seriously wrong,
perhaps bad hardware.  I would find the cause of this corruption.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: REINDEX in tables
  2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
  2023-10-25 09:33 ` Re: REINDEX in tables Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2023-10-25 23:33   ` Michael Paquier <michael@paquier.xyz>
  1 sibling, 0 replies; 8+ messages in thread

From: Michael Paquier @ 2023-10-25 23:33 UTC (permalink / raw)
  To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: pgsql-general@lists.postgresql.org

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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-10-25 23:33 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-25 09:24 REINDEX in tables Matthias Apitz <guru@unixarea.de>
2023-10-25 09:33 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-25 09:57   ` Matthias Apitz <guru@unixarea.de>
2023-10-25 09:59     ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-25 12:11       ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-25 12:14         ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-25 13:17           ` Bruce Momjian <bruce@momjian.us>
2023-10-25 23:33   ` Michael Paquier <michael@paquier.xyz>

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