Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements

From: Sergey Sargsyan <sergey(dot)sargsyan(dot)2001(at)gmail(dot)com>
To: Mihail Nikalayeu <mihailnikalayeu(at)gmail(dot)com>
Cc: Álvaro Herrera <alvherre(at)kurilemu(dot)de>, Andres Freund <andres(at)anarazel(dot)de>, Michael Paquier <michael(at)paquier(dot)xyz>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Andrey Borodin <amborodin86(at)gmail(dot)com>, Melanie Plageman <melanieplageman(at)gmail(dot)com>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
Date: 2025-07-07 12:00:37
Message-ID: CAMAof68kNgwWdkhmZd1ysfyU3PF66Wz+UaUr9g-LJg-_0xBV_Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I’ve tested the patch across several thousand test cases, and no faults of
any kind have been observed.
Additionally, I independently built a closed banking transaction system to
verify consistency during REINDEX CONCURRENTLY while multiple backends were
writing simultaneously. The results showed no missing transactions, and the
validation logic worked exactly as expected. On large tables, I observed a
significant speedup—often several times faster.

I believe this patch is highly valuable, as REINDEX CONCURRENTLY is a
common maintenance operation. I also noticed that there is a separate
thread working on adding support for concurrent reindexing of partitioned
indexes. Without this patch, that feature would likely suffer from serious
performance issues due to the need to reindex many indexes in one go—making
the process both time-consuming and lock-intensive.

Best regards,
S

On Thu, Jul 3, 2025, 3:24 AM Mihail Nikalayeu <mihailnikalayeu(at)gmail(dot)com>
wrote:

> Hello!
>
> Rebased again, patch structure and comments available here [0]. Quick
> introduction poster - here [1].
>
> Best regards,
> Mikhail.
>
> [0]:
> https://www.postgresql.org/message-id/flat/CADzfLwVOcZ9mg8gOG%2BKXWurt%3DMHRcqNv3XSECYoXyM3ENrxyfQ%40mail.gmail.com#52c97e004b8f628473124c05e3bf2da1
> [1]:
> https://www.postgresql.org/message-id/attachment/176651/STIR-poster.pdf
>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2025-07-07 12:01:53 Re: Adding basic NUMA awareness - Preliminary feedback and outline for an extensible approach
Previous Message Tomas Vondra 2025-07-07 11:57:42 Re: Changing shared_buffers without restart