Horton

Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements

Mihail Nikalayeu <michail.nikolaev@gmail.com>

View thread
From: Michail Nikolaev <michail.nikolaev@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andrey Borodin <amborodin86@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>
Date: 2025-01-01T16:16: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!

I’ve added several updates to the patch set:

* Automatic auxiliary index removal where applicable.
* Documentation updates to reflect recent changes.
* Optimization for STIR indexes: skipping datum setup, as they store only
TIDs.
* Numerous assertions to ensure that MyProc->xmin is invalid where
necessary.

I’d like to share some initial benchmark results (see attached graphs).
This involves building a B-tree index on (aid, abalance) in a pgbench setup
with scale 2000 (with WAL), while running a concurrent pgbench workload.

The patched version built the index in 68 seconds, compared to 117 seconds
with the master branch (mostly because of a single heap scan).
There appears to be no effect on the throughput of the concurrent pgbench.
The maximum snapshot age remains near zero.

I am going to continue to benchmark with different options: different HOT
setup, unique index, different index types and DB size (100+ GB).
If someone has some ideas about possible benchmark scenarios - please share.

Best regards,
Mikhail.

[image: image.png]

> [image: image.png]