Wednesday, January 16

Why Logical Replication?

PostgreSQL has built-in streaming replication. Why do we need new replication?

Well, in some cases, we do need more. Which is why we have pglogical.

The existing replication is more properly known as Physical Streaming Replication since we are streaming a series of physical changes from one node to another. That means that when we insert a row into a table we generate change records for the insert plus all of the index entries. When we VACUUM a table we also generate change records.

Also, Physical Streaming Replication records all changes at the byte/block level, making it very hard to do anything other than just replay everything.

Logical Streaming Replication sends changes in a more flexible form, sending only the logical change. So when we do an insert we send only the insert record, not various other records as well. Even better, the logical records work across major releases, so we can use this to upgrade from one release to another.

Other than that, much of the technology to send data is the same between logical and physical streaming replication.

pglogical is an extension for PostgreSQL, working for 9.4 and 9.5. Plus its submitted to PostgreSQL core as a future inclusion, hopefully for 9.6.

Leave a Reply

Your email address will not be published. Required fields are marked *