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-30T01:00: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!

> It was a wrong assumption. It looks like it is happening because of
prefetching. I'll try to add it in the validation phase.
This is an updated patch set, now prefetching is implemented.

Not validation works that way:
1) TIDs which are present in STIR auxiliary index but not present in target
index are loaded into tuplestore in sorted way
2) Then tuples from tuplestore are fetched one by one, but with underlying
prefetching of corresponding pages

Benchmark setups are the same as in [0].
Results show it works really well (see attachments).
I was unable to achieve consistent results for a few tests on the AWS (io2)
environment (and it was costly :) )

So, my next plan is:
1) wait a little bit for some comments from someone who still watches that
1-year going mainly solo thread :)
2) prepare a fresh new letter with patches, explanation, benchmark results
and so on.

Best regards,
Mikhail.

[0]:
https://www.postgresql.org/message-id/flat/CANtu0ojHAputNCH73TEYN_RUtjLGYsEyW1aSXmsXyvwf%3D3U4qQ%40mail.gmail.com#b18fb8efab086bc22af1cb015e187cb7

>