Good investigation! Software developers nowadays barely know about transactions, and definitely not about different transaction models (in my experience). I have even encountered "senior developers" (who are actually so called "CRUD developers"), who are clueless about database transactions.. In reality, transactions and transaction models matter a lot to performance and error free code (at least when you have volume…
Jepsen: Amazon RDS for PostgreSQL 17.4
131–140 of 153 posts
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#132It'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…
> We show that Amazon RDS for PostgreSQL multi-AZ clusters violate Snapshot Isolation
you kind of have to expect people to read
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#133I wish more writing in the software world was done this way: "Amazon RDS for PostgreSQL is an Amazon Web Services (AWS) service which provides managed instances of the PostgreSQL database. We show that Amazon RDS for PostgreSQL multi-AZ clusters violate Snapshot Isolation, the strongest consistency model supported across all endpoints. Healthy clusters occasionally allow..." Direct, to-the-point, unembellished and an…
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#134It'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…
it's the 2nd sentence in the article: > We show that Amazon RDS for PostgreSQL multi-AZ clusters violate Snapshot Isolation you kind of have to expect people to read
however, "multi-AZ" has been made ambiguous, because there are now multi-AZ instances and multi-AZ clusters.
...and your multi-AZ "instance", despite being not a multi-AZ "cluster" from AWS's perspective, is still two nodes that are "clustered" together and treated as one logical database from the client connection perspective.
see [0] and scroll down to the "availability and durability" screenshot for an example.
0: https://aws.amazon.com/blogs/aws/amazon-rds-multi-az-db-clus...
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#135I wish more writing in the software world was done this way: "Amazon RDS for PostgreSQL is an Amazon Web Services (AWS) service which provides managed instances of the PostgreSQL database. We show that Amazon RDS for PostgreSQL multi-AZ clusters violate Snapshot Isolation, the strongest consistency model supported across all endpoints. Healthy clusters occasionally allow..." Direct, to-the-point, unembellished and an…
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#136Good investigation! Software developers nowadays barely know about transactions, and definitely not about different transaction models (in my experience). I have even encountered "senior developers" (who are actually so called "CRUD developers"), who are clueless about database transactions.. In reality, transactions and transaction models matter a lot to performance and error free code (at least when you have volume…
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#137Earlier quoted context omitted.
> 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 trans…
Thank you matashii--this would definitely explain it. I've also received another email suggesting this anomaly is due to the difference in commit/visibility order between primary and secondary. Is there by chance a writeup of this available anywhere that I can link to? It looks like https://postgrespro.com/list/thread-id/1827129 miiight be related, but I'm not certain. If so, I'd like to update the report. My email i…
Core of the issue is that on the primary, commit inserts a WAL record, waits for durability, local and/or replicated, and then grabs a lock (ProcArrayLock) to mark itself as no longer running. Taking a snapshot takes that same lock and builds a list of running transactions. WAL insert and marking itself as visible can happen in different order. This causes an issue on the secondary where there is no idea of the apparent visibility order, so visibility order on secondary is strictly based on order of commit records in the WAL.
The obvious fix would be to make visibility happen in WAL order on the primary too. However there is one feature that makes that complicated. Clients can change the desired durability on a transaction-by-transaction basis. The settings range from confirm transaction immediately after it is inserted in WAL stream, through wait for local durability, all the way up to wait for it to be visible on synchronous replicas. If visibility happens in WAL order, then an async transaction either has to wait on every higher durability transaction that comes before it in the WAL stream, or give up on read-your-writes. That's basically where the discussion got stuck without achieving a consensus on which breakage to accept. This same problem is also the main blocker for adopting a logical (or physical) clock based snapshot mechanism.
By now I'm partial to the option of giving up on read-your-writes, with an opt-in option to see non-durable transactions as an escape hatch for backwards compatibility. Re-purposing SQL read uncommitted isolation level for this sounds appealing, but I haven't checked if there is some language in the standard that would make that a bad idea.
A somewhat elated idea is Eventual Durability, where write transactions become visible before they are durable, but read transactions wait for all observed transactions to be durable before committing.
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#138Earlier quoted context omitted.
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
#139Good investigation! Software developers nowadays barely know about transactions, and definitely not about different transaction models (in my experience). I have even encountered "senior developers" (who are actually so called "CRUD developers"), who are clueless about database transactions.. In reality, transactions and transaction models matter a lot to performance and error free code (at least when you have volume…
Makes me sad, since I work mostly in retail and and encounter systems that are infested with race conditions and simila errors: things where these isolation levels would be of great help.
However it's mostly engineers at startups, I have a very high opinion of typical Oracel/MSSQL developers at BigCos who at least have their fundamentals right.
Re: Jepsen: Amazon RDS for PostgreSQL 17.4
#140Good investigation! Software developers nowadays barely know about transactions, and definitely not about different transaction models (in my experience). I have even encountered "senior developers" (who are actually so called "CRUD developers"), who are clueless about database transactions.. In reality, transactions and transaction models matter a lot to performance and error free code (at least when you have volume…
This isn't confined just to senior developers. I have even encountered system architects who were clueless about Isolation levels. Some even confused "Consistency" in ACID with the "Consistency" in CAP. Makes me sad, since I work mostly in retail and and encounter systems that are infested with race conditions and simila errors: things where these isolation levels would be of great help. However it's mostly engineers…