MIME-Version: 1.0
References: 
 <CANtu0oiLc-+7h9zfzOVy2cv2UuYk_5MUReVLnVbOay6OgD_KGg@mail.gmail.com>
 <17483.1764262585@localhost>
 <CAEze2WigOH5Cyxo-oPjHebkfrohbe1H7sESKcDMi92vNUingWA@mail.gmail.com>
 <8837.1764320724@localhost>
In-Reply-To: <8837.1764320724@localhost>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Fri, 28 Nov 2025 10:51:16 +0100
Message-ID: 
 <CAEze2WjE=LJ_m_uWFDHgwXaxvqN-wna7A40=XWJ4727NpmTQ2w@mail.gmail.com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
To: Antonin Houska <ah@cybertec.at>
Cc: Michail Nikolaev <michail.nikolaev@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Alvaro Herrera <alvherre@2ndquadrant.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2WjE%3DLJ_m_uWFDHgwXaxvqN-wna7A40%3DXWJ4727NpmTQ2w%40mail.gmail.com>
Precedence: bulk

On Fri, 28 Nov 2025 at 10:05, Antonin Houska <ah@cybertec.at> wrote:
> Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:
> > 3. Having the index marked indisready before it contains any data is
> > going to slow down the indexing process significantly:
> > a. The main index build now must go through shared memory and buffer
> > locking, instead of being able to use backend-local memory
> > b. The tuple-wise insertion path (IndexAmRoutine->aminsert) can have a
> > significantly higher overhead than the bulk insertion logic in
> > ambuild(); in metrics of WAL, pages accessed (IO), and CPU cycles
> > spent.
> >
> > So, I don't think moving away from ambuild() as basis for initially
> > building the index this is such a great idea.
> >
> > (However, I do think that having an _option_ to build the index using
> > ambuildempty()+aminsert() instead of ambuild() might be useful, if
> > only to more easily compare "natural grown" indexes vs freshly built
> > ones, but that's completely orthogonal to CIC snapshotting
> > improvements.)
>
> The retail insertions are not something this proposal depends on. I think it'd
> be possible to build a separate index locally and then "merge" it with the
> regular one. I just tried to propose a solution that does not need snapshots.

I'm not sure we can generalize indexes to the point where merging two
built indexes is always both possible and efficient.

For example, the ANN indexes of pgvector (both HNSW and IVF) could
possibly have merge operations between indexes of the same type and
schema, but it would require a lot of effort on the side of the AM to
support merging; there is no trivial merge operation that also retains
the quality of the index without going through the aminsert() path.
Conversely, the current approach to CIC doesn't require additional
work on the index AM's side, and that's a huge enabler for every kind
of index.


Kind regards,

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