I wonder how Aurora fares on this?
Aurora doesn't offer Postgresql 17 for now I think
[deleted]
Jepsen: Amazon RDS for PostgreSQL 17.4
111–120 of 153 posts
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#112I wonder how Aurora fares on this?
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
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#113Earlier quoted context omitted.
A synchronous replica via WAL shipping is a well-worn feature of Postgres. I’d expect RDS to be using that feature behind the scenes and would be extremely surprised if that has consistency bugs. Two replicas in a “semi synchronous” configuration, as AWS calls it, is to my knowledge not available in base Postgres. AWS must be using some bespoke replication strategy, which would have different bugs than synchronous re…
This kind of replication can be configured in vanilla Postgres with something like ANY 3 (s1, s2, s3, s4) in synchronous_standby_names? Doc: https://www.postgresql.org/docs/current/runtime-config-repli...
I don't think it's possible with ANY set up. All you get is that some replicas are more outdated than others. But they won't return 2 conflicting states when ReplicaA says tx1 wrote (but not tx2), while ReplicaB says tx2 wrote (but not tx1). Which is what Long Fork and Parallel Snapshot are about.
So Amazon Multi-cluster seems to replicate changes out of order?
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#114It's not entirely clear but this isn't an issue in multi instance upstream Postgres clusters? Am I correct in understanding either AWS is doing something with the cluster configuration or has added some patches that introduce this behavior?
> 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.
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#115What safety or application-level bugs could arise if developers assume Snapshot Isolation but Amazon RDS for PostgreSQL is actually providing only Parallel Snapshot Isolation, especially in multi-AZ configurations using the read replica endpoint?
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...
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#116Earlier quoted context omitted.
This kind of replication can be configured in vanilla Postgres with something like ANY 3 (s1, s2, s3, s4) in synchronous_standby_names? Doc: https://www.postgresql.org/docs/current/runtime-config-repli...
I don't think it's possible with ANY set up. All you get is that some replicas are more outdated than others. But they won't return 2 conflicting states when ReplicaA says tx1 wrote (but not tx2), while ReplicaB says tx2 wrote (but not tx1). Which is what Long Fork and Parallel Snapshot are about. So Amazon Multi-cluster seems to replicate changes out of order?
Kinda. I think it's "just" PostgreSQL behaviour that's to blame here: On replicas, transaction commit visibility order is determined by the order of WAL records; on the primary it's based on when the backend that wrote the transaction notices that its transaction is sufficiently persisted.
See also my comment https://news.ycombinator.com/item?id=43843790 elsewhere in this thread
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#117In my reading of this, it looks like the practical implication could be that reads happening quickly after writes to the same row(s) might return stale data. The write transaction gets marked as complete before all of the distributed layers of a multi AZ RDS instance have been fully updated, such that immediate reads from the same rows might return nothing (if the row does not exist yet) or older values if the column…
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.
To provide a simple (although contrived) example of the type of thing that can happen. Imagine that you have a table with three columns `gps_coordinate`, `postal_code` and `city`. The way these are set is that the new coordinate gets posted to the API and `gps_coordinate` is updated. This then kicks off a background task that uses the new coordinate to lookup and update `postal_code`. Then another background task uses the postal code to look up and set `city`.
Since these happen sequentially, for a single update of `gps_coordinate` you would only expect to be able to observe one of:
1. Nothing updated yet, all columns have the previous value.
2. `gps_coordinate` updated, with `postal_code` and `city` still having the previous values.
3. `gps_coordinate` and `postal_code` updated with `city` still having the previous value.
4. All fields updated.
But the ordering that aphyr proved is possible allows you to see "impossible" states such as
1. `postal_code` updated with `gps_coordinate` and `city` still having the previous values.
2. `city` updated with `gps_coordinate` and `postal_code` still having the previous values.
Basically since these transactions happen in order and depend on one another you would expect that you can only see the "left to right" progression. But actually you can see some subset of the transactions applied even if that isn't a possible logical state of the database.
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#118It's not mentioned in the headline and not made super clear in the article: This is specific to multi-AZ clusters, which is a relatively new feature of RDS, and differ from multi-AZ instance that most will be familiar with. (Clear as mud.) Multi-AZ instances is a long-standing feature of RDS where the primary DB is synchronously replicated to a secondary DB in another AZ. On failure of the primary, RDS fails over to…
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.
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#119It's not mentioned in the headline and not made super clear in the article: This is specific to multi-AZ clusters, which is a relatively new feature of RDS, and differ from multi-AZ instance that most will be familiar with. (Clear as mud.) Multi-AZ instances is a long-standing feature of RDS where the primary DB is synchronously replicated to a secondary DB in another AZ. On failure of the primary, RDS fails over to…
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 t…
Link the talk video
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#120Earlier quoted context omitted.
"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." i was intuitively wondering the same but i'm having trouble reasoning how the post's example with transactions 1, 2, 3, 4 exhibits this behavior. in the example, is transaction 2 the only read-only transaction and therefore the only…
Yeah, that's right. It may be that the (apparent) order of transactions differs between primary and secondary.
The Write-Ahead Logging (WAL) is single-threaded and maintains consistency at a specific point in time in each instance. However, there can be anomalies between two instances. This behavior is expected because the RDS Multi-AZ cluster does not wait for changes to be applied in the shared buffers. It only waits for the WAL to sync.
This is similar to the behavior of PostgreSQL when synchronous_commit is set to on. Nothing unexpected.