4 ms·

This is completely incorrect. ACID was defined upon Jim Gray's formalization of the terms "transaction", "atomicity", "consistency", and "durability" [1]. It a

This is completely incorrect. ACID was defined upon Jim Gray's formalization of the terms "transaction", "atomicity", "consistency", and "durability" [1]. It applies regardless of however many nodes a system consists of. It cares about a system holistically, not what it's physically made up of. Gray defined them acknowledging the preexisting work on fault tolerance and high availability with replication and hot standbys, literally situations where it's worth talking about C-as-in-CAP consistency. A transaction is literally defined upon an agreement protocol like two-phase commit. Similarly, Eric Brewer literally defined the C in CAP following the C in ACID [2]. The two concepts are clearly related, if not identical. It's not like I magically can't ever have ACID transactions once a network is involved, as long as the network heals. To come at this from another angle: ACID transactions are an abstraction. They serve only to make it easier to reason about concurrency and synchronization. If you're telling me that this abstraction falls apart inherently because of the network, then frankly it's not a very good abstraction at all. PostgreSQL is ACID when it's a system that encompasses a single node. PostgreSQL is not ACID when it's a system that encompasses more than one node. Simple as that. I think it is even more disingenuous to lull people into a false sense of security just because Postgres is technically "consistent" on a single node. It's 2017 -- your data getting fsync'ed to a single disk, in a single machine on a single rack in a single datacenter in a single region -- is simply not enough anymore. [1] http://jimgray.azurewebsites.net/papers/thetransactionconcept.pdf http://jimgray.azurewebsites.net/papers/thetransactionconcep... [2] https://pdfs.semanticscholar.org/5015/8bc1a8a67295ab7bce0550886a9859000dc2.pdf https://pdfs.semanticscholar.org/5015/8bc1a8a67295ab7bce0550...

empthought 9y ago
> PostgreSQL is not ACID when it's a system that encompasses more than one node. Simple as that. Pretty sure Postgres has supported two-phase commit since at least version 8.
- elvinyung 9y ago
  And there's also quorum commits too! Point is, these features aren't on by default, so you really have to know what you're doing, and not just assume you're safe because of the magic word "ACID".
  - empthought 9y ago
    Vs. MongoDB, which doesn't support any of that; I think it's fair to draw a distinction even if people think they're using it when they're not.
    - elvinyung 9y ago
      MongoDB is actually strongly consistent in a single document, though. It uses Raft by default to replicate changes to all replicas. In practice, it has the A, C, and D of ACID for point writes.
      - empthought 9y ago
        Those are really some low expectations. The whole point of the ACID terminology is to apply it to transactions of arbitrary complexity with respect to the underlying data store. It's not saying much to say that a database management system is ACID as long as you constrain your updates to a single record in a single table.
        elvinyung 9y ago
        Sure. I actually agree, and like I said elsewhere in this thread, Postgres is my favorite database, and using it is something that I'd like to do for as long as possible. MongoDB's main selling point, after all, is expediency. What I'm tired of is the rhetoric built upon the dichotomy that Postgres is ACID and MongoDB/NoSQL is not. As it stands, neither is, in any scalable fashion. And in no way is Postgres a panacea for every possible kind of data problem. And like you said. It's not saying much to say that a database management system is ACID as long as you constrain your updates to a single machine. There are better things on the horizon, databases that can actually do it all.
dustingetz 9y ago
Postgres will NEVER lose an increment if two transactions try to swap the same counter at the same time, even if denormalized or distributed reads. ACID means "doesn't lose data" Postgresql with read replicas is ACID-Consistent transactions because there is a single writer process to prevent data loss. Only the reads are distributed and thus cap-AP eventually consistent.
- elvinyung 9y ago
  What happens if a user makes an update to their data, and then my "single writer" master crashes, and I failover to a hot standby that doesn't have the new transaction? And then what happens if the user tries to update their data again?
  - dustingetz 9y ago
    the D in ACID addresses this. Your standby transactor process is attached to the same storage as the previous transactor (EBS block or dynamodb or whatever cloud HA storage people use these days). If they don't share storage, it's not a single writer.
    - elvinyung 9y ago
      You're not actually saying that PostgreSQL itself is ACID. You are essentially asserting that a precondition for ACID in Postgres is that the underlying storage medium (or at least the bits on them) will never suffer permanent failures, and has infinite 9's of availability everywhere in the world, instantly. Then, and only then, can PostgreSQL provide anything resembling seamless consistency. Sorry, no dice. This is not how distributed systems work in the world outside the walled gardens. I'm a huge proponent of the cloud, but I'm not a huge proponent of things that only work if you rely on the cloud.
      - dustingetz 9y ago
        a black hole might swallow the solar system and thus our ACID writer process lost a write? No, you get to choose how durable you would you like your D. Don't acknowledge the transaction as successful until you successfully commit to storage orbiting alpha centauri. non-ACID writers like Mongo lose writes routinely, every day, often without anybody noticing. There are no guarantees whatsoever about data integrity. ACID writers lose data in 6-sigma catastrophic failures only, and you can control how many sigmas by tuning durability. You asked a question about HA configurations so I answered in terms of HA configurations. Serving durable HA storage is literally what dynamo/riak is for. "The write operation is durable. dw (durable write quorum) is a configuration setting used to specify how many replicas to commit to durable storage before returning a successful response." https://stackoverflow.com/questions/22736821/riak-database-and-acid https://stackoverflow.com/questions/22736821/riak-database-a... Anyway I'm interested in continuing to discuss this if you are because this is a subject I am beginning to write about as part of my startup. so this conversation helps me. Perhaps there is a better medium though if you would like to continue?

empthought 9y ago

> PostgreSQL is not ACID when it's a system that encompasses more than one node. Simple as that. Pretty sure Postgres has supported two-phase commit since at least version 8.

elvinyung 9y ago
And there's also quorum commits too! Point is, these features aren't on by default, so you really have to know what you're doing, and not just assume you're safe because of the magic word "ACID".
- empthought 9y ago
  Vs. MongoDB, which doesn't support any of that; I think it's fair to draw a distinction even if people think they're using it when they're not.
  - elvinyung 9y ago
    MongoDB is actually strongly consistent in a single document, though. It uses Raft by default to replicate changes to all replicas. In practice, it has the A, C, and D of ACID for point writes.
    - empthought 9y ago
      Those are really some low expectations. The whole point of the ACID terminology is to apply it to transactions of arbitrary complexity with respect to the underlying data store. It's not saying much to say that a database management system is ACID as long as you constrain your updates to a single record in a single table.
      - elvinyung 9y ago
        Sure. I actually agree, and like I said elsewhere in this thread, Postgres is my favorite database, and using it is something that I'd like to do for as long as possible. MongoDB's main selling point, after all, is expediency. What I'm tired of is the rhetoric built upon the dichotomy that Postgres is ACID and MongoDB/NoSQL is not. As it stands, neither is, in any scalable fashion. And in no way is Postgres a panacea for every possible kind of data problem. And like you said. It's not saying much to say that a database management system is ACID as long as you constrain your updates to a single machine. There are better things on the horizon, databases that can actually do it all.

dustingetz 9y ago

Postgres will NEVER lose an increment if two transactions try to swap the same counter at the same time, even if denormalized or distributed reads. ACID means "doesn't lose data" Postgresql with read replicas is ACID-Consistent transactions because there is a single writer process to prevent data loss. Only the reads are distributed and thus cap-AP eventually consistent.

elvinyung 9y ago
What happens if a user makes an update to their data, and then my "single writer" master crashes, and I failover to a hot standby that doesn't have the new transaction? And then what happens if the user tries to update their data again?
- dustingetz 9y ago
  the D in ACID addresses this. Your standby transactor process is attached to the same storage as the previous transactor (EBS block or dynamodb or whatever cloud HA storage people use these days). If they don't share storage, it's not a single writer.
  - elvinyung 9y ago
    You're not actually saying that PostgreSQL itself is ACID. You are essentially asserting that a precondition for ACID in Postgres is that the underlying storage medium (or at least the bits on them) will never suffer permanent failures, and has infinite 9's of availability everywhere in the world, instantly. Then, and only then, can PostgreSQL provide anything resembling seamless consistency. Sorry, no dice. This is not how distributed systems work in the world outside the walled gardens. I'm a huge proponent of the cloud, but I'm not a huge proponent of things that only work if you rely on the cloud.
    - dustingetz 9y ago
      a black hole might swallow the solar system and thus our ACID writer process lost a write? No, you get to choose how durable you would you like your D. Don't acknowledge the transaction as successful until you successfully commit to storage orbiting alpha centauri. non-ACID writers like Mongo lose writes routinely, every day, often without anybody noticing. There are no guarantees whatsoever about data integrity. ACID writers lose data in 6-sigma catastrophic failures only, and you can control how many sigmas by tuning durability. You asked a question about HA configurations so I answered in terms of HA configurations. Serving durable HA storage is literally what dynamo/riak is for. "The write operation is durable. dw (durable write quorum) is a configuration setting used to specify how many replicas to commit to durable storage before returning a successful response." https://stackoverflow.com/questions/22736821/riak-database-and-acid https://stackoverflow.com/questions/22736821/riak-database-a... Anyway I'm interested in continuing to discuss this if you are because this is a subject I am beginning to write about as part of my startup. so this conversation helps me. Perhaps there is a better medium though if you would like to continue?