Re: speed up a logical replica setup

On Tue, Jul 30, 2024 at 9:26 AM Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> > Hayato already mentioned one of the solution in a previous email [2].
> > AFAICS we can use any solution that creates a WAL record. I tested the
> > following options:
>
> Yes, my point was that we should add an arbitrary record after the recovery_target_lsn.
>
> > (a) temporary replication slot: requires an additional replication slot.
> > small payload. it is extremely slow in comparison with the other
> > options.
> > (b) logical message: can be consumed by logical replication when/if it
> > is supported some day. big payload. fast.
> > (c) snapshot of running txn: small payload. fast.
> > (d) named restore point: biggest payload. fast.
>
> Another PoV is whether they trigger the flush of the generated WAL record. You've
> tested pg_logical_emit_message() with flush=false, but this meant that the WAL does
> not flush so that the wait_for_end_recovery() waits a time. IIUC, it may wait 15
> seconds, which is the time duration bgwriter works. If flush is set to true, the
> execution time will be longer.
> pg_create_restore_point() also does not flush the generated record so that it may
> be problematic. Moreover, the name of the restore point might be a conflict that
> users will use.
>
> Overall, I could agree to use pg_log_standby_snapshot(). This flushes the WAL
> asynchronously but ensures the timing is not so delayed.
>

The other minor benefit of using pg_log_standby_snapshot() is that
after the standby is converted to subscriber, the publisher will
process this record to see if the slot machinery can be advanced. So,
overall there won't be any harm in using it. I'll check the latest
patch Euler shared.

--
With Regards,
Amit Kapila.