Re: pgsql: reindexdb: Add the index-level REINDEX with multiple jobs

On Sun, Mar 9, 2025 at 4:53 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Sat, Mar 8, 2025 at 12:49 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > On Fri, Mar 7, 2025 at 8:20 PM Álvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > >
> > > On 2024-Mar-25, Alexander Korotkov wrote:
> > >
> > > > reindexdb: Add the index-level REINDEX with multiple jobs
> > > >
> > > > Straight-forward index-level REINDEX is not supported with multiple jobs as
> > > > we cannot control the concurrent processing of multiple indexes depending on
> > > > the same relation.  Instead, we dedicate the whole table to certain reindex
> > > > job.  Thus, if indexes in the lists belong to different tables, that gives us
> > > > a fair level of parallelism.
> > >
> > > I tested this, because of a refactoring suggestion [1] and I find that
> > > it's rather completely broken.
> >
> > The code was written with assumption that running
> > run_reindex_command() with async == true can schedule a number of
> > queries for a connection.  But actually that's not true and everything
> > is broken.
>
> The draft patch for revert is attached.  Could you, please, check.

After second thought it's not so hard to fix.  The attached patch does
it by putting REINDEX commands related to the same table into a single
SQL statement.  Possibly, that could be better than revert given we
need to handle compatibility.  What do you think?

------
Regards,
Alexander Korotkov
Supabase