0

The MariaDB vs PostgreSQL debate continues to rage in my firm :-( and its fuelled by this somewhat famous uber blog post. I found some interesting discussions about write amplification mitigations here. My question is, did anything like this get release as part of PostgreSQL 10 or 11? I would search the documentation, but I'm not sure what it would be called.

0

No, nothing major along these lines has been released.

-1

Main answer: Per this presentation, Postgres 10's introduction of a logical-replication option (as opposed to the byte-replication we had natively * before) reduces write-amplification on replicas to a degree, though – as of my most recent edit to this post – the fundamental complaint of Postgres tying indexes directly to CTIDs (which requires all indexes associated with a row to be updated when that row has a change, an op that must also flow to replicas) still stands.

* Logical replication has been available as a simple plugin since 9.4, and in some form or other since long before then.

Aside: Apparently, write-amplification was not the only reason for Uber's switch according to this thread (apparently backed up by some messages which appeared on the mailing list); difficulties with performance were only part of the reason they decided to switch.

A presentation discussing the legitimacy of Uber's complaints against Postgres can be found here. It contends that some of the problems Uber's post highlighted either had solutions or were self-caused.

  • 1
    How can logical replication reduce write amplification on the source database? I don't think it can, but maybe a more detailed explanation would convince me. – Laurenz Albe Jul 3 at 6:46
  • @LaurenzAlbe Thanks for your followup. I asked around and got this answer: "non-committed writes... would not be applied to a logical replica" (as opposed to a byte-level replica). I also learned that changes driven by VACUUM would be applied more cleanly on logical replicas versus byte-level ones. This is info per xzilla on irc://irc.freenode.net/postgresql. That first advantage might have been especially be relevant to Uber's case since they had some problems with connections being left open for long periods. – koyae Jul 3 at 20:56
  • I'm not sure whether Uber already had this in place, but I also learned that enabling heap-only-writes (HOT, available since 8.3) is another way to reduce some of this write-overhead. I'll also note now that I've edited my answer to remove the part about CTID-changes (and the associated changes to indexes) not cascading to slaves; apparently these updates are considered logical under Postgres and will flow through replicas as well. – koyae Jul 3 at 20:59
  • You have got a point with the uncommitted writes, but that would only be effective if there were lots of rollbacks (a write that gets committed will be replicated eventually). HOT was long before the Uber gripe, and it didn't work for them, probably because of indexes (I don't remember). I don't see what VACUUM has to do with write amplification, and how the VACUUM workload would be less on logical standbys (except, again, if there are lots of rollbacks), but maybe I just am dense. – Laurenz Albe Jul 4 at 5:26
  • @LaurenzAlbe The argument that VACUUM-overhead is a (delayed) form of write-amplification would be that write-amplification is basically extra i/o associated with performing writes; if you never do a write, then you don't need VACUUM. You don't have to agree with this definition. As for how VACUUM-ops are different on logical replicas, I'll just state that I got it on good authority that VACUUMs aren't identical on logical replicas; I didn't seek a deep explanation of the minutiae. For details, I'd recommend asking the Postgres devs or consulting PG's source if you prefer. – koyae Jul 4 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.