Add REPACK progress phases for logical decoding setup

Hi,

On Fri, Sep 18, 2026 at 2:19 PM Sami Imseih <samimseih.pg@gmail.com> wrote:

Hi,

While stress testing REPACK (CONCURRENTLY), I noticed that the current
"initializing" phase contains multiple operations that can wait indefinitely.
The transaction wait can be prolonged by long-running or prepared
transactions, or by another REPACK (CONCURRENTLY) operation that already has
an XID. Describing the whole phase as "expected to be very brief" [1] is
misleading.

Yeah, for REPACK (CONCURRENTLY), it could take a time before the scan starts.

To make these waits visible and distinguish the work immediately before and
after the old transaction wait, three progress phases between
"initializing" and
the heap scan should be added.

1/ enabling logical decoding

When wal_level is logical, or logical decoding is already enabled, this phase
is effectively instantaneous. Otherwise, REPACK waits for every running
process to acknowledge a ProcSignalBarrier. This normally finishes quickly,
but the barrier has no timeout and can take longer if a process does not
respond promptly.

2/ waiting for old transactions

The snapshot builder waits for transactions with assigned XIDs to finish
before it can reach a consistent point.

Right, these steps could take a time. Please note that if there is one
logical slot or wal_level is set to 'logical', (1) is no-op.

3/ building initial snapshot

REPACK obtains the initial snapshot from the decoding worker and prepares to
start copying the table. This separates the end of the transaction wait
from the start of the heap scan.

While it's true that the logical decoding might need to read WAL
records until the snapshot reaches a consistent state, I'm not sure
that it can justify having its own phase. I think we can merge it to
(2) and have something like a "initializing logical decoding" phase.

This gives the following sequence:

initializing (default, expected to be brief)
enabling logical decoding
waiting for old transactions
building initial snapshot
seq scanning heap / index scanning heap

The attached patch has the worker publish its setup stage through shared
memory and signal the existing condition variable.

Thoughts?

I've not looked at the patch in depth yet but I agree with the general
idea. Just to be clear, I think adding new phases would be a v20 item
rather than an open item for v19. For v19, we can revise the
documentation part only.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi Sami, Masahiko,

I tried this patch with the PROGRESS_DEBUG trace and test framework I
posted earlier today in a different thread [1]/messages/by-id/CA+bCEdBKvmoOd=ShLZA99FNHFOc5kjdPRzfOZLgSdcm07uy28g@mail.gmail.com, which logs every change
to a backend's progress state and checks it against rules that hold for
every command.

It applies cleanly on top of today's master, no warnings. With the
option, make check (239 tests) and the framework's own checks pass with
no violations, across 1.34 million logged progress changes. For REPACK
(CONCURRENTLY) the new phases show up in the order the patch documents,
9 -> 10 -> 11, and never for a plain REPACK, matching "skipped when not
in concurrent mode". I also ran a plain REPACK (CONCURRENTLY) 20 times
with wal_level already set to logical, the case the commit message calls
"effectively instantaneous": phase 9 was still captured every time, so
the backend's polling loop isn't racing past it in practice, at least
not in this setup.

Masahiko Sawada <sawada.mshk@gmail.com> wrote:

While it's true that the logical decoding might need to read WAL
records until the snapshot reaches a consistent state, I'm not sure
that it can justify having its own phase. I think we can merge it to
(2) and have something like a "initializing logical decoding" phase.

I tried to find a case where building the initial snapshot (phase 11)
takes meaningfully long on its own, independent of the old-transaction
wait (phase 10), since that would be the argument for keeping them
separate. Timing the phase changes from the trace:

- An open transaction holding an XID for 3s, no other activity:
phase 10 measured 2794 ms, phase 11 measured 2 ms.

- No open transaction, but a few hundred autocommitted inserts on an
unrelated table running while REPACK starts, to put some WAL in the
way: phase 10 measured 0 ms, phase 11 measured 1 ms.

In both cases, and in everything else I tried, phase 11 stayed under a
few milliseconds, and whatever was slow showed up in phase 10 instead.
I could not produce a case where phase 11 alone was slow. That lines
up with what you suspected, though a small, short-lived test like this
one obviously does not rule out phase 11 mattering under conditions I
did not try, such as a very large number of subtransactions or a
snapshot builder that is already far behind for unrelated reasons.

[1]: /messages/by-id/CA+bCEdBKvmoOd=ShLZA99FNHFOc5kjdPRzfOZLgSdcm07uy28g@mail.gmail.com

Regards,
Manu

Thank you both for the review!

To make these waits visible and distinguish the work immediately before and
after the old transaction wait, three progress phases between
"initializing" and
the heap scan should be added.

1/ enabling logical decoding

When wal_level is logical, or logical decoding is already enabled, this phase
is effectively instantaneous. Otherwise, REPACK waits for every running
process to acknowledge a ProcSignalBarrier. This normally finishes quickly,
but the barrier has no timeout and can take longer if a process does not
respond promptly.

2/ waiting for old transactions

The snapshot builder waits for transactions with assigned XIDs to finish
before it can reach a consistent point.

Right, these steps could take a time. Please note that if there is one
logical slot or wal_level is set to 'logical', (1) is no-op.

Good point. I added a point about effective_wal_level in the docs.

3/ building initial snapshot

REPACK obtains the initial snapshot from the decoding worker and prepares to
start copying the table. This separates the end of the transaction wait
from the start of the heap scan.

While it's true that the logical decoding might need to read WAL
records until the snapshot reaches a consistent state, I'm not sure
that it can justify having its own phase. I think we can merge it to
(2) and have something like a "initializing logical decoding" phase.

Sure. I also was not sure about this one. So I went ahead and
put it under the "initializing logical decoding" step.

I've not looked at the patch in depth yet but I agree with the general
idea. Just to be clear, I think adding new phases would be a v20 item
rather than an open item for v19. For v19, we can revise the
documentation part only.

Agreed, so I split it that way.

v2-0001 is documentation only and is what I propose for the v19 open
item. It adds more documentation about the "initializing" and "catch-up"
phases for the steps that could be unbounded. It covers enabling logical
decoding and initializing logical decoding, and the ACCESS EXCLUSIVE lock
that is taken to perform the swap in the "catch-up" phase.

v2-0002 is for v20 and adds only the "enabling logical decoding" and
"initializing logical decoding" phases, moving the descriptions above into
their own rows. It needs a catversion bump, which is not included.

--
Sami Imseih
Amazon Web Services (AWS)

Hi Sami,

I checked v2. Both patches apply and build clean, and the two claims
the new documentation makes that a reader cannot easily verify are
true. Measurements below are on the tree with PROGRESS_DEBUG, which
logs every phase value a backend emits, so the sequences are recorded
rather than sampled.

1. "This phase is skipped when not in concurrent mode"

REPACK t phases emitted: 1 6 7 8
REPACK (CONCURRENTLY) t phases emitted: 9 10 1 7 5 6 8

9 and 10 appear only in the concurrent case, and they come before the
heap scan, as the table now describes.

2. catch-up "also covers waiting for the ACCESS EXCLUSIVE lock needed
to swap the files, so a command blocked by a conflicting lock stays
in this phase"

With a session holding the table open in a transaction and a REPACK
(CONCURRENTLY) behind it:

pg_stat_progress_repack.phase catch-up
pg_stat_activity Lock / relation

So it does stay in catch-up, and that is where an operator will find a
blocked REPACK.

3. The catversion bump

Confirmed missing, as you noted: catversion.h is still 202609152 in
v2-0002. Only flagging it so it does not get lost.

4. One thing the documentation still does not say

The phase table lists the phases in an order that is not the order
they happen in. From the run above, the concurrent sequence is

9 (enabling logical decoding)
10 (initializing logical decoding)
1 (seq scanning heap)
7 (rebuilding index)
5 (catch-up)
6 (swapping relation files)
8 (final cleanup)

but the table lists "rebuilding index" after "swapping relation
files", near the end. This is pre-existing - build_new_index() sets
REBUILD_INDEX and REPACK (CONCURRENTLY) reaches it before catch-up -
and not something v2 introduces. Since 0001 is specifically about
making this table less misleading for someone watching a long REPACK,
it might be worth either ordering the rows the way the phases occur or
saying that the order of the rows is not the order of execution.

Our own progress test module (the invariant checker from the debug
framework thread) passes on v2 without any change: it derives the
phase values from progress.h by prefix, so it picked up 9 and 10 on
its own.

Regards,
Manu

Thanks!

9 (enabling logical decoding)
10 (initializing logical decoding)
1 (seq scanning heap)
7 (rebuilding index)
5 (catch-up)
6 (swapping relation files)
8 (final cleanup)

I think the ordering here does not matter and we don't make
guarantees, so I will keep it that way.

v3- just has a minor change: I was using "REPACK (CONCURRENTLY)"
instead of using the existing "REPACK CONCURRENTLY" style. Fixed

--
Sami

On Tue, Sep 22, 2026 at 2:20 PM Sami Imseih <samimseih.pg@gmail.com> wrote:

Thank you both for the review!

To make these waits visible and distinguish the work immediately before and
after the old transaction wait, three progress phases between
"initializing" and
the heap scan should be added.

1/ enabling logical decoding

When wal_level is logical, or logical decoding is already enabled, this phase
is effectively instantaneous. Otherwise, REPACK waits for every running
process to acknowledge a ProcSignalBarrier. This normally finishes quickly,
but the barrier has no timeout and can take longer if a process does not
respond promptly.

2/ waiting for old transactions

The snapshot builder waits for transactions with assigned XIDs to finish
before it can reach a consistent point.

Right, these steps could take a time. Please note that if there is one
logical slot or wal_level is set to 'logical', (1) is no-op.

Good point. I added a point about effective_wal_level in the docs.

3/ building initial snapshot

REPACK obtains the initial snapshot from the decoding worker and prepares to
start copying the table. This separates the end of the transaction wait
from the start of the heap scan.

While it's true that the logical decoding might need to read WAL
records until the snapshot reaches a consistent state, I'm not sure
that it can justify having its own phase. I think we can merge it to
(2) and have something like a "initializing logical decoding" phase.

Sure. I also was not sure about this one. So I went ahead and
put it under the "initializing logical decoding" step.

I've not looked at the patch in depth yet but I agree with the general
idea. Just to be clear, I think adding new phases would be a v20 item
rather than an open item for v19. For v19, we can revise the
documentation part only.

Agreed, so I split it that way.

v2-0001 is documentation only and is what I propose for the v19 open
item. It adds more documentation about the "initializing" and "catch-up"
phases for the steps that could be unbounded. It covers enabling logical
decoding and initializing logical decoding, and the ACCESS EXCLUSIVE lock
that is taken to perform the swap in the "catch-up" phase.

v2-0002 is for v20 and adds only the "enabling logical decoding" and
"initializing logical decoding" phases, moving the descriptions above into
their own rows. It needs a catversion bump, which is not included.

Thank you for updating the patches!

I've reviewed the 0001 patch first as it's an open item. There are
some minor comments:

-       expected to be very brief.
+       expected to be very brief, except for
+       <command>REPACK (CONCURRENTLY)</command>, where it also covers enabling
+       logical decoding and then initializing it.  Enabling has nothing to do
+       if <xref linkend="guc-effective-wal-level"/> is already
+       <literal>logical</literal>; otherwise it waits for all backends to
+       acknowledge that they started writing the additional WAL information
+       that logical decoding requires.  Initializing then waits for the

The logical decoding activation waits for all processes to acknowledge
it, not only backends.

+       transactions that had already been assigned a transaction ID to end, so
+       this phase can last as long as the longest of those transactions.

During the logical decoding initialization, the snapshot builder also
has to wait for transactions that started after the initialization
began (i.e., those that started while it was in the BUILDING_SNAPSHOT
state). So this phase can last longer than the longest of the
transactions that
were running when it started. How about rephrasing it as follows?

expected to be very brief, except for
<command>REPACK (CONCURRENTLY)</command>, where it also covers enabling
logical decoding and then initializing it. Enabling has nothing to do
if <xref linkend="guc-effective-wal-level"/> is already
<literal>logical</literal>; otherwise it waits for all processes to
acknowledge that they started writing the additional WAL information
that logical decoding requires. Initializing then waits for the
transactions that have been assigned a transaction ID to end,
including ones that started while this wait was in progress, so this
phase can last longer than the longest transaction running when the
command started.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

I've reviewed the 0001 patch first as it's an open item. There are
some minor comments:

Thanks!

-       expected to be very brief.
+       expected to be very brief, except for
+       <command>REPACK (CONCURRENTLY)</command>, where it also covers enabling
+       logical decoding and then initializing it.  Enabling has nothing to do
+       if <xref linkend="guc-effective-wal-level"/> is already
+       <literal>logical</literal>; otherwise it waits for all backends to
+       acknowledge that they started writing the additional WAL information
+       that logical decoding requires.  Initializing then waits for the

The logical decoding activation waits for all processes to acknowledge
it, not only backends.

Fair. I updated it.

+       transactions that had already been assigned a transaction ID to end, so
+       this phase can last as long as the longest of those transactions.

During the logical decoding initialization, the snapshot builder also
has to wait for transactions that started after the initialization
began (i.e., those that started while it was in the BUILDING_SNAPSHOT
state). So this phase can last longer than the longest of the
transactions that
were running when it started. How about rephrasing it as follows?

expected to be very brief, except for
<command>REPACK (CONCURRENTLY)</command>, where it also covers enabling
logical decoding and then initializing it. Enabling has nothing to do
if <xref linkend="guc-effective-wal-level"/> is already
<literal>logical</literal>; otherwise it waits for all processes to
acknowledge that they started writing the additional WAL information
that logical decoding requires. Initializing then waits for the
transactions that have been assigned a transaction ID to end,
including ones that started while this wait was in progress, so this
phase can last longer than the longest transaction running when the
command started.

Good point. I used what you provided and I realized that in my v1 I was also
specifically calling out that "waits for the transactions that have
been assigned a transaction ID to end"
also includes other REPACK CONCURRENTLY operations. This may not be obvious,
so it's worth a callout.

--
Sami Imseih
Amazon Web Services (AWS)

On Wed, Sep 23, 2026 at 1:25 PM Sami Imseih <samimseih.pg@gmail.com> wrote:

I've reviewed the 0001 patch first as it's an open item. There are
some minor comments:

Thanks!

-       expected to be very brief.
+       expected to be very brief, except for
+       <command>REPACK (CONCURRENTLY)</command>, where it also covers enabling
+       logical decoding and then initializing it.  Enabling has nothing to do
+       if <xref linkend="guc-effective-wal-level"/> is already
+       <literal>logical</literal>; otherwise it waits for all backends to
+       acknowledge that they started writing the additional WAL information
+       that logical decoding requires.  Initializing then waits for the

The logical decoding activation waits for all processes to acknowledge
it, not only backends.

Fair. I updated it.

+       transactions that had already been assigned a transaction ID to end, so
+       this phase can last as long as the longest of those transactions.

During the logical decoding initialization, the snapshot builder also
has to wait for transactions that started after the initialization
began (i.e., those that started while it was in the BUILDING_SNAPSHOT
state). So this phase can last longer than the longest of the
transactions that
were running when it started. How about rephrasing it as follows?

expected to be very brief, except for
<command>REPACK (CONCURRENTLY)</command>, where it also covers enabling
logical decoding and then initializing it. Enabling has nothing to do
if <xref linkend="guc-effective-wal-level"/> is already
<literal>logical</literal>; otherwise it waits for all processes to
acknowledge that they started writing the additional WAL information
that logical decoding requires. Initializing then waits for the
transactions that have been assigned a transaction ID to end,
including ones that started while this wait was in progress, so this
phase can last longer than the longest transaction running when the
command started.

Good point. I used what you provided and I realized that in my v1 I was also
specifically calling out that "waits for the transactions that have
been assigned a transaction ID to end"
also includes other REPACK CONCURRENTLY operations. This may not be obvious,
so it's worth a callout.

Thank you for updating the patch! I've pushed the 0001 patch as it
looks good to me.

I'll review 0002 patch and send review comments.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Thank you for updating the patch! I've pushed the 0001 patch as it
looks good to me.

I'll review 0002 patch and send review comments.

Thanks!

--
Sami