Jepsen: Amazon RDS for PostgreSQL 17.4

I'm so past wanting to read meme laden blog posts. Especially when all too often it's just stretching a paragraph of content. Security vulnerability stuff is probably the worst at it these days.

Jepsen is awesome, on so many levels!

Interesting why this magic would be needed. Vanilla Postgres does support quorum commit which can do this. You can also set up the equivalent multi-AZ cluster with Patroni, and (modulo bugs) it does the necessary coordination to make sure to promote primaries in a way that does not lose transactions or makes visible a transaction that is not durable.

There still is a Postgres deficiency that makes something similar to this pattern possible. Non-replicated transactions where the client goes away mid-commit become visible immediately. So in the example, if T1 happens on a partitioned leader, disconnects during commit, T2 also happens on a partitioned node, and T3 and T4 happen later on a new leader, you would also see the same result. However, this does not jive with the statement that fault injection was not done in this test.

Edit: did not notice the post that this pattern can be explained by inconsistent commit order on replica and primary. Kind of embarrassing given I've done a talk proposing how to fix that.

Have one question

So if snapshot violation is happening inside Multi-AZ instances, it can happen with a single region - multiple read replica kind of setup as well ? But it might be easily observable in Multi-AZ setups because the lag is high ?

I’ve noticed the lack of transaction awareness mostly in serverless/edge contexts where the backend architecture (if you can even call it that) is driven exclusively by the needs of the client. For instance, database queries are modelled as react hooks or sequential API calls.

I’ve seen this work out terribly at certain points in my career.

Was a single instance, one instance with a standby in another AZ or a multiaz cluster as tested here?

What's a "RDBMS stakeholder" ?

(Hats off to aphyr for sure!)

This isn't just stale data, in the sense of "a point-in-time consistent snapshot which does not reflect some recent transactions". I think what's going on here is that a read-only transaction against a secondary can observe some transaction T, but also miss transactions which must have logically executed before T.

This is a very good question! I do not understand AWS's replication architecture well enough to reimplement it with standard Postgres yet. This behavior doesn't happen in single-node Postgres, as far as I can tell, but it might happen in some replication setups!

I also understand there are lots of ways to do Postgres replication in general, with varying results. For instance, here's Bin Wang's report on Patroni: https://www.binwang.me/2024-12-02-PostgreSQL-High-Availabili...

> It's not entirely clear but this isn't an issue in multi instance upstream Postgres clusters?

No, it isn't an issue with single-instance PostgreSQL clusters. Multi-instance PostgreSQL clusters (single primary, plus streaming/physical replicas) are affected.

What they -too- discovered is that PostgreSQL currently doesn't have consistent snapshot behaviour between the primary and replicas. Presumably, read-only transaction T2 was executed on a secondary (replica) node, while T1, T3, and T4 (all modifying transactions) were executed on the primary.

Some background:

Snapshots on secondary PostgreSQL nodes rely on transaction persistence order (location of commit record in WAL) to determine which transactions are visible, while the visibility order on the primary is determined by when the backend that authorized the transaction first got notice that the transaction was completely committed (and then got to marking the transaction as committed). On each of these (primary and secondary) the commit order is consistent across backends that connect to that system, but the commit order may be somewhat different between the primary and the secondary.

There is some work ongoing to improve this, but that's still very much WIP.

What are multi instance upstream Postgres clusters for you? PostgreSQL has no official support for failover of a master instance, the only mechanism is Postgres replication which you can make synchronous. Then you can build your own tooling around this to build a Postgres cluster (Patroni is one such tool).

AWS patched Postgres to replicate to two instances and to call it good if one of the two acknowledges the change. When this ack happens is not public information.

My personal opinion is that filesystem level replication (think drbd) is the better approach for PostgreSQL. I believe that this is what the old school AWS Multi-AZ instances do. But you get lower throughput and you can't read from the secondary instance.

Yes its different. This is a deeper overview of what they did: https://youtu.be/fLqJXTOhUg4

Specially here: https://youtu.be/fLqJXTOhUg4?t=434

Folks on HN are often upset with the titles of Jepsen reports, so perhaps a little more context is in order. Jepsen reports are usually the product of a long collaboration with a client. Clients often have strong feelings about how the report is titled--is it too harsh on the system, or too favorable? Does it capture the most meaningful of the dozen-odd issues we found? Is it fair, in the sense that Jepsen aims to be an honest broker of database safety findings? How will it be interpreted in ten years when people link to it routinely, but the findings no longer apply to recent versions? The resulting discussions can be, ah, vigorous.

The way I've threaded this needle, after several frustrating attempts, is to have a policy of titling all reports "Jepsen: <system> <version>". HN is of course welcome to choose their own link text if they prefer a more descriptive, or colorful, phrase. :-)

I emailed the mods and asked them to change it to this phrase copy-pasted from the linked article:

> Amazon RDS for PostgreSQL multi-AZ clusters violate Snapshot Isolation

And your comment also...In Multi-AZ clusters.

Well this is from Kyle Kingsbury, the Chuck Norris of transactional guarantees. AWS has to reply or clarify, even if only seems to apply to Multi-AZ Clusters. Those are one of the two possibilities for RDS with Postgres. Multi-AZ deployments can have one standby or two standby DB instances and this is for the two standby DB instances. [1]

They make no such promises in their documentation. Their 5494 pages manual on RDS hardly mentions isolation or serializable except in documentation of parameters for the different engines.

Nothing on global read consistency for Multi-AZ clusters because why should they.... :-) They talk about semi-synchronous replication so the writer waits for one standby to confirm log record, but the two readers can be on different snapshots?

[1] - "New Amazon RDS for MySQL & PostgreSQL Multi-AZ Deployment Option: Improved Write Performance & Faster Failover" - https://aws.amazon.com/blogs/aws/amazon-rds-multi-az-db-clus...

[2] - "Amazon RDS Multi-AZ with two readable standbys: Under the hood" - https://aws.amazon.com/blogs/database/amazon-rds-multi-az-wi...

Par for the course

Consider this: you leave a comment under a post. The user who posts first deserves a "first commenter badge". Now:

- User1 comments

- User2 comments

- User1 checks (in a separate tx) that there's only 1 comment, so User1 gets the badge

- User2 checks the same (in a separate tx) and also sees only 1 comment (his), and also receives the badge.

With Snapshot isolation this isn't possible. At least one of the checks made in a separate tx would see 2 comments.

The original article on the Parallel Snapshot is a good read: https://scispace.com/pdf/transactional-storage-for-geo-repli...

Consider a "git push"-like flow: begin a transaction, read the current state, check that it matches the expected, write the new state, commit (with a new state hash). In some unfortunate situations, you'll have a commit hash that doesn't match any valid state.

And the mere fact that it's hard to reason about these things means that it's hard to avoid problems. Hence, the easiest solution is likely "it may be possible to recover Snapshot Isolation by only using the writer endpoint", for anything where write is anyhow conditional on a read.

Although I'm surprised the "only using the writer endpoint" method wasn't tested, especially in availability loss situations.

I have actually been working on this (very slowly, in occasional nights and weekends!) Peter Alvaro and I reported on a safety issue in RDS for MySQL here too: https://jepsen.io/analyses/mysql-8.0.34#fractured-read-like-...

I think the ideal solution from AWS would be fixing the bug and actually providing the guarantees that the docs say that they do.

If I'm understanding the issue correctly, it probably doesn't.

From what I understand, multi-az has some setup with multiple semi synchronous replicas where only 1 replica needs to acknowledge the transaction.

Aurora doesn't use semi synchronous replication but uses clustered/shared storage with a different replication setup for cache invalidation

I wondered how Microsoft SQL Server fares, but not it's tested in the long list of databases:

https://jepsen.io/analyses

Aurora doesn't offer Postgresql 17 for now I think

This is not a single RDS or the single instance in another AZ. Its a more specific and advanced cluster setup.

In the beginning, when you read papers like this, it can be hard work. You can either give up or put some effort in to try to understand it. Maybe look at some of the other Jepsen reports, some may be easier. Or perhaps an introductory CS textbook. With practice and patience it will become easier to read and eventually write like this.

You may not be part of that world now, but you can be some day.

EDIT: forgot to say, i had to read 6 or 7 books on Bayesian statistics before i understood the most basic concepts. A few years later i wrote a compiler for a statistical programming language.

It uses a lot of very specific terminology, but the linked pages like the one on "G-nonadjacent" do a lot to clear up what it all means. It is a lot of reading.

Essentially: The configuration claims "Snapshot Isolation", which means every transaction looks like it operates on a consistent snapshot of the entire database at its starting timestamp. All transactions starting after a transaction commits will see the changes made by the transaction. Jepsen finds that the snapshot a transaction sees doesn't always contain everything that was committed before its starting timestamp. Transactions A an B can both commit their changes, then transactions C and D can start with C only seeing the change made by A and D only seeing the change made by B.

I empathize with the feeling of this being dense and unapproachable; I remember when I was first approaching these posts, and feeling the same.

For this particular one, the graph under "Results" is the most approachable portion, I think. (Don't skip the top two sections, though … and they're so short.) In the graph, each line is a transaction, and read them left-to-right.

Hopefully I get this right, though if I do not, I'm sure someone will correct me. Our database is a set of ordered lists of integers. Something like,

  CREATE TABLE test (
    id int primary key,
    -- (but specifically, this next column holds a list of ints, e.g.,
    --  a value might be, '1,8,11'; the list of ints is a comma separated
    --  string.)
    list text not null
  );

The first transaction:

  a 89 9

This is shorthand; means "(a)ppend to list #89 the integer 9" (in SQL, crudely this is perhaps something like

  UPDATE test SET list = CONCAT(list, ',9') WHERE id = 89;

… though we'd need to handle the case where the list doesn't exist yet, turning it into an `INSERT … ON CONFLICT … DO UPDATE …`, so it would get gnarlier.[2]); the next:

  r 90 nil    # read list 90; the result is nil
  r 89 [4 9]  # read list 89; the result is [4, 9]
  r 90 nil    # read line 90; the result is (still) nil

I assume you can `SELECT` ;) That should provide sufficient syntax for one to understand the remainder.

The arrows indicate the dependencies; if you click "read-write dependencies"[1], that page explains it.

Our first transaction appends 9 to list 89. Our second transaction reads that same list, and sees that same 9, thus, it must start after the first transaction has committed. The remaining arrows form similar dependencies, and once you take them all into account, they form a cycle; this should feel problematic. It's that they're in a cycle, which snapshot isolation does not permit, so we've observed a contradiction in the system: these cannot be obeying snapshot isolation. (This is what "To understand why this cycle is illegal…" gets at; it is fairly straightforward. T₁ is the first row in the graph, T₂ the second, so forth. But it is only straight-forward once you've understood the graph, I think.)

> This is in such a thick academic style that it is difficult to follow what the problem actually might be and how it would impact someone.

I think a lot of this is because it is written with precision, and that precision requires a lot of academic terminology.

Some of it is just syntax peculiar to Jepsen, which I think comes from Clojure, which I think most of us (myself included) are just not familiar with. Hence why I used SQL and comma-sep'd lists in my commentary above; that is likely more widely read. It's a bit rough when you first approach it, but once you get the notation, the payoff is worth it, I guess.

More generally, I think once you grasp the graph syntax & simple operations used here, it becomes easier to read other posts, since they're mostly graphs of transactions that, taken together, make no logical sense at all. Yet they happened!

> This style of writing serves mostly to remind me that I am not a part of the world that writes like this, which makes me a little sad.

I think Jepsen posts, with a little effort, are approachable. This post is a good starter post; normally I'd say Jepsen posts tend to inject faults into the system, as we're testing if the guarantees of the system hold up under stress. This one has no fault injection, though, so it's a bit simpler.

Beware though, that if you learn to read these, that you'll never trust a database again.

[1]: https://jepsen.io/consistency/dependencies

[2]: I think this is it? https://github.com/jepsen-io/postgres/blob/225203dd64ad5e5e4... — but this is pushing the limits of my own understanding.

It's maximal information communication. Use LLM to distill to your own knowledge level. It is trivial with modern LLM. Very good output in general.

> such a thick academic style

Why? Because it has variables and a graph?

What sort of education background do you have?

Have you tried using an LLM? I’ve found good results getting at the underlying concepts and building a mental model that works for me that way. It makes domain expertise - that often has unique terminology for concepts you already know or at least know without a specific name - more easily accessible after a little bit of a QA round.

Then you haven't read enough jepsen reports. Distributed system guarantees generally can't be trusted

I think zookeeper is still the only distributed system that got through jepsen without dataloss bugs, though at high cost: https://aphyr.com/posts/291-jepsen-zookeeper

Do people still use MongoDB in production?

I was quite surprised to read that Stripe uses MongoDB in the early days and still today and I can't imagine the sheer nightmares they must have faced using it for all these years.

Those memes are 10 years old, you know that some very tech company use MongoDB right? We're talking billions a year.

Postgres does indeed promise perfect snapshot isolation, and Amazon does not (to the best of my knowledge) document that their managed Postgres service weakens Postgres’ promises.

However, Aurora DSQL is trying to compete with both CDB and Spanner, and they explicitly promise snapshot isolation.

Cockroach doesn't offer strict serializability. It has serializability with some limits depending on clock drift. Also CockroachDB does not provide linearizability over the entire database.