Horton

Re: speed up a logical replica setup

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: Euler Taveira <euler@eulerto.com>, Alexander Lakhin <exclusion@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
Date: 2024-07-30T05:31:25Z
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. pg_createsubscriber: Remove obsolete comment

  2. pg_createsubscriber: Fix an unpredictable recovery wait time.

  3. Fix unstable test in 040_pg_createsubscriber.

  4. Fix the testcase introduced in commit 81d20fbf7a.

  5. Further weaken new pg_createsubscriber test on Windows.

  6. Temporarily(?) weaken new pg_createsubscriber test on Windows.

  7. Make pg_createsubscriber warn if publisher has two-phase commit enabled.

  8. Make pg_createsubscriber more wary about quoting connection parameters.

  9. pg_createsubscriber: Remove failover replication slots on subscriber

  10. pg_createsubscriber: Remove replication slot check on primary

  11. pg_createsubscriber: Only --recovery-timeout controls the end of recovery process

  12. pg_createsubscriber: creates a new logical replica from a standby server

  13. Add some const decorations

  14. Add option force_initdb to PostgreSQL::Test::Cluster:init()

  15. Remove MSVC scripts

On Tue, Jul 30, 2024 at 9:26 AM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.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.