This page in other versions: 0.9 / 1.0 / 1.0.3

1.2. Differences between logical (row level) and physical (block level) replication

BDR/UDR use PostgreSQL's logical decoding feature to implement a low overhead logical replication solution. It has significant advantages - and some disadvantages - when compared to PostgreSQL's older physical (block-based) streaming or archive-based replication with warm or hot standby

Logical replication has a different set of trade-offs to physical block-based replication. It isn't clearly better or worse. Physical replication is a lot simpler, has less lag for big transactions, is supported by older versions and may require less disk I/O, but generally consumes more network bandwidth, can't replicate a subset of databases or tables, and can't support multi-master or cross-version/cross-arch replication. Which solution you should use depends on what you need to do.

The major differences between physical replication and logical replication as implemented by BDR/UDR are:

Most users will want to use physical replication and/or WAL archiving for redundancy, high availability, backup and PITR. Logical replication is well suited to data integration, data movement and data aggregation (often as an alternative to or combined with ETL), for scale-out and for distributed multi-master deployments.

It's possible to replicate between different PostgreSQL versions, operating systems and/or processor architectures using logical replication because it can fall back to sending data in text form - just like SQL. Where the servers are compatible it can use the faster-to-process binary representation or an intermediate form. Logical replication cannot prevent all possible incompatibilities though - for example, it isn't possible to replicate a type added in PostgreSQL 9.5 to PostgreSQL 9.4 because 9.4 has no way to store and represent it.

Unlike physical replication, which replicates all databases on a PostgreSQL install, logical decoding permits (and requires) separate replication of each database. It can also replicate a subset of tables within a database. It is not possible to configure wildcard replication of all databases on a server in logical replication. You can replicate multiple databases, but each database must be configured separately.

Temporary tables may always be created on all nodes, even if they are also receiving replicated data. There's no prohibition against doing so like it exists for PostgreSQL's block-level replication features.

Local writes are not limited to temporary tables. UNLOGGED tables may be created even on nodes that are receiving changes from upstream/peer nodes. Additionally, replication sets allow changes to only a subset of tables to be replicated if desired, so some normal tables may be excluded from replication. This makes BDR/UDR very useful for use cases where significant work is done on nodes that also receive replicated data from elsewhere.

Logical replication doesn't start replicating a transaction until it commits. This can cause longer replication delays for big transactions than physical replication, where the transaction's changes get replicated as soon as they're written. It also lets logical replication entirely skip replication of writes by aborted transactions. Future enhancements to logical decoding may permit streaming of transactions before they're committed.

Because logical replication is only supported in streaming mode (rather than WAL archiving) it isn't suitable for point-in-time recovery. Logical replication may be used in conjunction with streaming physical replication and/or PITR, though; it is not necessary to choose one or the other.