Logical replication on postgres

PostgreSQL
1

Hey guys,

I have few questions enabling logical replications that I couldn’t extract answers from the doc.

  1. does postgres replicate the statements executed on the master to the subscription? or is this an over simplification? The doc mention a decoding of WAL changes which is little vague.

  2. if I did a single update transaction that touches a million rows, does postgres replicate the million rows or does it replicate the statement only?

Thank you!
Hussein

1 Like
2

Hi @hnasr ,

Regarding your questions.

does postgres replicate the statements executed on the master to the subscription?

Yes. A logical replication is all about running same/similar statement on the subscription. So the first step towards achieving that goal is extract all the logical changes happening on the Publisher. The decoding of WAL records is to find out what are the changes happened on the Publisher side

  1. if I did a single update transaction that touches a million rows, does postgres replicate the million rows or does it replicate the statement only?

The PostgreSQL WAL logs are very generic. The same WAL logs can be used for both physical replication as well as logical replication. It is the duty of the decoder to extract the information use the output pluggin. But all the million rows which are changed will be recorded in the WAL logs. The decoder and the output plugin can represent it in the way the subscriber needs it need not be even a “sql statement”. For example, WAL2JSON output plugin can give the changes as a JSON message which can be used for multiple purposes.

Best Regards,
Jobin

2 Likes