Facts: Native vs pglogical Logical Replication in PostgreSQL

Here are some of the facts about Native vs pglogical Logical Replication in PostgreSQL 

1. For Physical Replication (streaming WAL replication) pg_replication_slots.slot_type is 'physical' . For Logical Replication pg_replication_slots.slot_type is 'logical', but the pg_replication_slots.plugin will have different values based on the type of logical replication used.

2. pg_replication_slots.plugin is 'pgoutput' for the Native (Pub/Sub model available from v10+) Logical replication. It is 'pglogical_output' when using the pglogical extension.

3. Database Migration Services for various CSP uses Logical replication for migration.

4. Some other CDC Tools come with different logical replication plugins.

5. We cannot perform DMLs on PostgreSQL Physical standbys/read replicas which are created using physical streaming replication.

We will get the below error-"cannot execute INSERT in a read-only transaction"

6. We can perform DMLs on Logical standbys.

7. Logical standbys can be a source for Database Migration Services provided by AWS/GCP. 

8. The physical standby/read replicas created in AWS/GCP/Azure cannot act as a source for DMS Jobs.

9. pg_is_in_recovery() will return 'f' for Logical replica/standby instances and pg_is_wal_replay_paused() will return an error.

10. pg_is_in_recovery() will return 't' for Physical standby instances and pg_is_wal_replay_paused() will return 'f' if streaming replication is going on without any errors. Also pg_stat_wal_receiver will return data.

11. Pglogical replicates sequences but Native Replication does not.

Pglogical.replication_set_add_sequence and pglogical.replication_set_add_all_sequences can be used to add sequence/s and synchronize sequence data.

12. Pglogical allows Selective replication of table rows at either publisher or subscriber side (row_filter) , whereas Native replication supports row filter at publisher side from Postgres version 15 onwards.

13. Pglogical allows Selective replication of table columns at publisher side

14.Pglogical allows data gather/merge from multiple upstream servers and use of pglogical.conflict_resolution to resolve conflicts when a subscriber node is subscribed to multiple providers and/or local writes are allowed in the subscriber node.

15. Pglogical does not allow to replicate UPDATEs and DELETEs for tables that do not have Primary Keys, only INSERTs are replicated. Native logical replication allows tables without primary key to be replicated which have a REPLICA IDENTITY FULL set.

16. Pglogical copies the schema(using synchronize_structure of pglogical.create_subscription) at the subscriber node but in Native replication database schema and DDL commands are not replicated automatically.

17. Pglogical gives the option to re-syncronize a single table which has fallen behind using pglogical.alter_subscription_resynchronize_table but in Native replication the entire subscription needs to be refreshed.