Why Uber dropped PostgreSQL

Posted Aug 5, 2016 12:10 UTC (Fri) by brong (guest, #87268)
In reply to: Why Uber dropped PostgreSQL by niner
Parent article: Why Uber dropped PostgreSQL

[citation needed]

can you please enumerate the sort of corruptions that occur with statement based replication?

The only sort I can think of are cases where the transactions get re-ordered in the statement log compared to the order they were actually applied on the master due to concurrency, and hence the replica falls out of sync.

Or cases where you flat out allow the two ends to be out of sync by manually fiddling replication log position so that you skip transactions. You can't really call that a bug in statement based replication though.

Why Uber dropped PostgreSQL

Posted Aug 5, 2016 15:11 UTC (Fri) by paulj (subscriber, #341) [Link] (1 responses)

Well, we're talking about bugs. Anything is possible, right?

With the low-level binary log replication, bugs that lead to corruption can replicate.

With the logical level replication, bugs that lead to logical level corruption can also cause inconsistent state. E.g., an update doesn't get applied to slaves because it isn't accepted, which could affect application consistency. Bugs at the binary log level may not replicate of themselves, but could cause a logical level replication to fail to replicate and cause inconsistent state.

Isn't it the case that the logical layer replication system has _two_ layers at which bugs can strike and cause significant problems? You now have two layers that need to be robust? And bugs in the lower layer can still take down the upper layer?

Why Uber dropped PostgreSQL

Posted Aug 5, 2016 21:58 UTC (Fri) by brong (guest, #87268) [Link]

If the response to a logical update failing to apply is rejecting the update, then you know that your replication is broken, and you haven't lost anything except the most recent changes - and you can skip that update and apply something manually to fix it while you fail over to a replica and bring it as close to up-to-date as possible.

If your low level data structures are corrupted - better have a good fsck and/or good backups, because you have have no replica with consistent state any more.

Why Uber dropped PostgreSQL

Posted Aug 7, 2016 16:43 UTC (Sun) by krakensden (subscriber, #72039) [Link]

MySQL has a nice list in their documentation- statement based replication causes corruption if you use nondeterministic functions like now() or random(). If everyone in your org is aware of this, things can work, but I have definitely seen it not work.

Why Uber dropped PostgreSQL

Posted Aug 11, 2016 7:50 UTC (Thu) by ringerc (subscriber, #3071) [Link]

Simple statement-based replication is overwhelmingly flawed. Most importantly, it's completely broken with respect to "volatile" functions, sequence generation, etc. It's utterly hopeless. It can produce different results to what it did on the master in concurrent execution. AUTO_INCREMENT, NOW() and SYSDATE() etc would be very broken.

MySQL works around this somewhat by special-casing some functions, like now(). It evaluates them on the master and stores the results in the binlog, then ensures the invocations on the replica(s) return the same results as the master.

PgPool-II for PostgreSQL does something similar in statement based replication mode.

Clever, but solves only narrow cases. For example, in MySQL SYSDATE() still doesn't work safely. So you have to code very carefully to avoid breakage. (See https://dev.mysql.com/doc/refman/5.7/en/replication-featu...) .

By contrast, PostgreSQL's block-level replication leaves the replica an identical copy.

That's why in practice the most practical MySQL replication option is row-based replication or hybrid row/statement based replication. Many people who are talking about "statement based" replication here are really thinking of row-based replication, or the MIXED replication mode that MySQL can use to hybridize the two. Rather cleverly, I must say. ( https://dev.mysql.com/doc/refman/5.7/en/replication-forma..., https://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.... ).

That's what I'm involved in working on for PostgreSQL too, at 2ndQuadrant, in the form of BDR and pglogical. There's ongoing work to get this into PostgreSQL core. Though we're not planning on any sort of mixed replication mode at this point.