MIME-Version: 1.0
References: 
 <CADzfLwW9QczZW-E=McxcjUv0e5VMDctQNETbgao0K-SimVhFPA@mail.gmail.com>
 <202505181556.3n6oiowvntyr@alvherre.pgsql>
 <CADzfLwXKtriMnfCNVGNH2ahwXaByjo-QOMWiDTU-9WZqh+zQ5g@mail.gmail.com>
 <CADzfLwW5bDWSxjHK7mqX8Lewki3+5FBydBC+nVcxg4xMGKscyw@mail.gmail.com>
 <CAMAof6-4xaV3QE2ErYJaJhu6qjFn99sWyo_HQeBhHikZM3GexA@mail.gmail.com>
 <CADzfLwXocKhpW3eFP1oScz+m+1XJ3bpi9QmVpoqC9RX9oyX=UA@mail.gmail.com>
 <CAMAof695VA+mbVRhWCTus=E0WnsMAQyqXxfOTohbcb7VUHSP4g@mail.gmail.com>
 <CAMAof69JSL8MYWG2qRScs3RQDpfcyZT_wFwW4SoAvftW+K_p1g@mail.gmail.com>
 <CADzfLwVMtwjHh8KY9kP=_vcYPqHs=JDzuexO4RFQ2fM8VoqovA@mail.gmail.com>
 <CAMAof68L0GO0F0bwuXtLZAjh9k_Hj+o0-8mqfO6iEQyXr4PuVA@mail.gmail.com>
 <CADzfLwUrodAcOggK+3j3LbPLaSXemgHxa-n=LhZTwRAsaakL2g@mail.gmail.com>
 <CAMAof691D4O=3QTuPwJXBYxYpG6s3A=tVhL9vN=T3eeRTMnaig@mail.gmail.com>
 <CADzfLwVT3Y14g6Maz2y92sP2L7rPvpznt+MHM++xiy-U3XMLZQ@mail.gmail.com>
 <CADzfLwXQe9XfQfJs3W-DCPqeqG4rq-6FoYUpGbbpgjcT1Eotpg@mail.gmail.com>
 <CAMAof68kNgwWdkhmZd1ysfyU3PF66Wz+UaUr9g-LJg-_0xBV_Q@mail.gmail.com>
 <CADzfLwUtLqYrupZp4QQuWwv4W_LgYWBRStybvQ+S0SZiHrp62A@mail.gmail.com>
 <CADzfLwVYUBb8cUVQ_1mzVzNMyJH84VZKFCRyATvBZKbLW377CA@mail.gmail.com>
 <CADzfLwWbV1i7+cP_Hqr3qgQnBXkAqgrCQxd5PFzqp2AOTK=40w@mail.gmail.com>
 <CADzfLwXJc0jdDDS43-Fj0gKmwX-FURS3eY7MyLQ89qDPA6T5Ug@mail.gmail.com>
 <CADzfLwVaV15R2rUNZmKqLKweiN3SnUBg=6_qGE_ERb7cdQUD8g@mail.gmail.com>
 <CAEze2WgBffcC_SKGLmVxW8uRTEsrwWOHDQujN6zyxy1tSYLJ=Q@mail.gmail.com>
 <CADzfLwVon8ESWOkg+8KU0F9=Hg7QKriNVX-hqcm-v-XZmHkzig@mail.gmail.com>
 <CAEze2WiXYx1LKr=9d7PLsZOYrGytY9AN__tFFw4p_Ysgm1-e5g@mail.gmail.com>
 <CADzfLwUKXcXKZgX+e8ACsOXe_CgtWmNJY_6dyn8EO0AXYOn2pA@mail.gmail.com>
 <CAEze2WiiR2PeXg_vaURjjiiwvjQ=Um8wxWi1BcVS0BGyxiD2gQ@mail.gmail.com>
 <CAMT0RQQP9JiGqqB+pVBzPT7unG1BMBuLj=kGPk4BeS3g6VyT1A@mail.gmail.com>
 <CAMT0RQSbFJCpetFy22=O=gKR2ZfH=tMTQeCM743T4o3rMjaeTQ@mail.gmail.com>
 <8010.1764584989@localhost>
 <CADzfLwUjgOxk8dd8XLv3jn05gAxxwEZoAA7-2Owb2CczkSb6Tw@mail.gmail.com>
 <5778.1764660480@localhost>
 <CADzfLwXMzv4_2Mc54qpASJ7FrKwQ6OsG0d9GxiYDxEKZ2KqHSA@mail.gmail.com>
In-Reply-To: 
 <CADzfLwXMzv4_2Mc54qpASJ7FrKwQ6OsG0d9GxiYDxEKZ2KqHSA@mail.gmail.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Tue, 2 Dec 2025 12:12:17 +0100
Message-ID: 
 <CAEze2Wg6d2M8hop4uwdQXeH-YkOmEHyqp83+aE7vEzKdmu7w-A@mail.gmail.com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Antonin Houska <ah@cybertec.at>, Hannu Krosing <hannuk@google.com>,
	Sergey Sargsyan <sergey.sargsyan.2001@gmail.com>, alvherre@kurilemu.de,
	Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Andrey Borodin <amborodin86@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2Wg6d2M8hop4uwdQXeH-YkOmEHyqp83%2BaE7vEzKdmu7w-A%40mail.gmail.com>
Precedence: bulk

On Tue, 2 Dec 2025 at 11:27, Mihail Nikalayeu <mihailnikalayeu@gmail.com> w=
rote:
>
> Hello, Antonin!
>
> On Tue, Dec 2, 2025 at 8:28=E2=80=AFAM Antonin Houska <ah@cybertec.at> wr=
ote:
> > I suppose you don't want to use logical decoding for CIC, do you? How c=
an then
> > it be "the same" like in REPACK (CONCURRENTLY)? Or do you propose to re=
work
> > REPACK (CONCURRENTLY) from scratch so that it does not use logical deco=
ding
> > either?
>
> My logic here chain is next:
> * looks like we may reuse snapshot reset technique for REPACK, using
> LR+some tricks
> * if it worked, why should we use reset technique + STIR (not LR too) in =
CIC?

Because it's more easy to reason about STIR than it is to reason about
LR, especially when it concerns things like "overhead in heavily
loaded systems".

For CIC, you know that the amount of IO required is proportional only
to the table's data. With LR, that guarantee is gone; concurrent
workloads may bloat the WAL that needs to be scanned to many times the
size of the data you didn't have to scan.

> * mostly because it is not possible to active LR for some of tables
> * but there is (your) patch what aims to add the ability to activate
> LR for any table


> * if it worked - it feels natural to replace STIR by LR to keep things
> looking the same and working the same way
>
> While STIR may be more efficient and simple for CIC - it is still an
> additional entity in the PG domain, so LR may be a better solution
> from a system design perspective.

LR is a very complicated system that depends on WAL and various other
subsystems to work; and has a significant amount of overhead.
I disagree with any work to make (concurrent) index creation depend on
WAL; it is _not_ the right approach. Don't shoe-horn this into that.

> But it is only thought so far, because I have not yet proved reset
> snapshot is possible for REPACK (need to do some POC at least).
> What do you think?

I don't think we should be worrying about REPACK here and now.

> Also, I think I'll extract reset-snapshot for CIC in a separate CF
> entry, since it still may be used with or without either STIR or LR.

Thanks!


Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)