Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements

Mihail Nikalayeu <mihailnikalayeu(at)gmail(dot)com> wrote:

> Hello, Antonin!
>
> On Mon, Dec 1, 2025 at 11:29 AM Antonin Houska <ah(at)cybertec(dot)at> wrote:
> > With logical replication, we cannot really use multiple snapshots as Mihail is
> > proposing elsewhere in the thread, because the logical decoding system only
> > generates the snapshot for non-catalog tables once (LR uses that snapshot for
> > the initial table synchronization). Only snapshots for system catalog tables
> > are then built as the WAL decoding progresses. It can be worked around by
> > considering regular table as catalog during the processing, but it currently
> > introduces quite some overhead:
>
> My idea related to REPACK is a little bit different. I am not talking
> about snapshots generated by LR - just GetLatestSnapshot.
>
> > The core problem here is that the snapshot you need for the first pass
> > restricts VACUUM on all tables in the database
>
> We might use it only for a few seconds - it is required only to
> *start* the scan (to ensure we will not miss anything in the table).
> After we may throw it away and ask GetLatestSnapshot a fresh one for
> next N pages. We just need to synchronize scan position in the table
> and logical decoding.
>
> The same is possible for CIC too. In that case we should do the same
> and just store all incoming tuples the same way as STIR does it.

I suppose you don't want to use logical decoding for CIC, do you? How can then
it be "the same" like in REPACK (CONCURRENTLY)? Or do you propose to rework
REPACK (CONCURRENTLY) from scratch so that it does not use logical decoding
either?

--
Antonin Houska
Web: https://www.cybertec-postgresql.com