Horton

Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements

Mihail Nikalayeu <michail.nikolaev@gmail.com>

View thread
From: Michail Nikolaev <michail.nikolaev@gmail.com>
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andrey Borodin <amborodin86@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>
Date: 2025-01-18T14:18:00Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Revert changes to CONCURRENTLY that "sped up" Xmin advance

  2. VACUUM: ignore indexing operations with CONCURRENTLY

  3. Avoid spurious waits in concurrent indexing

Attachments

Hello, everyone!

This is an updated version, contains some optimization into STIR index
access method, related to the fact it is never used with WAL.

>  The locking in stirinsert can probably be improved significantly if
>  we use things like atomic operations on STIR pages. We'd need an
>  exclusive lock only for page initialization, while share locks are
>  enough if the page's data is modified without WAL. That should improve
>  concurrent insert performance significantly, as it would further
>  reduce the length of the exclusively locked hot path.

Mathias, you were proposed to use just shared locking to writes, but how is
it possible if it is required to mark page as dirty, and it requires
exclusive lock?

> It occurs because the first phase is slow, and many tuples need to be
inserted during the validation phase.
> For each tuple, heapam_index_fetch_tuple is called, even for those on the
same page.
> It might be possible to implement a batched version of
heapam_index_fetch_tuple to handle multiple tuples on the same page and
mitigate this issue.

It was a wrong assumption. It looks like it is happening because of
prefetching. I'll try to add it in the validation phase.

Best regards,
Mikhail.