Re: speed up a logical replica setup

On Wed, Jul 17, 2024, at 11:37 PM, Amit Kapila wrote:
> I am thinking of transactions between restart_lsn and "consistent
> point lsn" (aka the point after which all commits will be replicated).
> You conclusion seems correct to me that such transactions won't be
> replicated by streaming replication and would be skipped by logical
> replication. Now, if we can avoid that anyway, we can consider that.

Under reflection what I proposed [1] seems more complex and possibly
error prone than other available solutions. The recovery step was slow
if the server is idle (that's the case for the test). An idle server
usually doesn't have another WAL record after creating the replication
slots. Since pg_createsubscriber is using the LSN returned by the last
replication slot as recovery_target_lsn, this LSN is ahead of the
current WAL position and the recovery waits until something writes a
WAL record to reach the target and ends the recovery.

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:

\timing
select * from pg_create_logical_replication_slot('pg_createsubscriber', 'pgoutput', true);
select pg_logical_emit_message(false, 'pg_createsubscriber', 'dummy');
select pg_log_standby_snapshot();
select pg_create_restore_point('pg_createsubscriber');

that results in the following output:

slot_name | lsn
---------------------+-----------
pg_createsubscriber | 0/942DD28
(1 row)

Time: 200.571 ms
pg_logical_emit_message
-------------------------
0/942DD78
(1 row)

Time: 0.938 ms
pg_log_standby_snapshot
-------------------------
0/942DDB0
(1 row)

Time: 0.741 ms
pg_create_restore_point
-------------------------
0/942DE18
(1 row)

Time: 0.870 ms

and generates the following WAL records:

rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/0942DCF0, prev 0/0942DCB8, desc: RUNNING_XACTS nextXid 3939 latestCompletedXid 3938 oldestRunningXid 3939
rmgr: LogicalMessage len (rec/tot): 75/ 75, tx: 0, lsn: 0/0942DD28, prev 0/0942DCF0, desc: MESSAGE non-transactional, prefix "pg_createsubscriber"; payload (5 bytes): 64 75 6D 6D 79
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/0942DD78, prev 0/0942DD28, desc: RUNNING_XACTS nextXid 3939 latestCompletedXid 3938 oldestRunningXid 3939
rmgr: XLOG len (rec/tot): 98/ 98, tx: 0, lsn: 0/0942DDB0, prev 0/0942DD78, desc: RESTORE_POINT pg_createsubscriber

The options are:

(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.

I don't have a strong preference but if I need to pick one I would
choose option (c) or option (d). The option (a) is out of the question.

Opinions?

[1] https://www.postgresql.org/message-id/b1f0f8c7-8f01-4950-af77-339df3dc4684%40app.fastmail.com
[2] https://www.postgresql.org/message-id/OSBPR01MB25521B15BF950D2523BBE143F5D32%40OSBPR01MB2552.jpnprd01.prod.outlook.com

--
Euler Taveira
EDB https://www.enterprisedb.com/