It's a bad strawman to say that PostgreSQL is ACID and MongoDB isn't. Here's the thing: even PostgreSQL isn't ACID when you throw replicas into the mix. And PostgreSQL as it stands "doesn't scale" past a single box, not if you want to do (most kinds of) replication or sharding.
The impression I get is that at scale, you don't really "need joins" and multi-table ACID for the kind of workload that MongoDB is targeting, i.e. massive throughput embarrassingly-parallel low-latency OLTP short requests that do point queries and point writes. It seems to be atomic, consistent and durable enough for that [1].
For analytical queries that really do need expressive SQL features, you generally wouldn't want to run them on the same database anyways, not when you're "at scale" and your seven-way join could impact other request latencies. You export it to Redshift or HDFS/S3, and do all the reporting/BI/analytics on it.
Second of all, Mongo marketing and ecosystem is foremost about making dashboard crud apps easy with not a lot of engineering. dashboards are not about point reads. Whether or not mongo is engineered for it, this is what the customers are using it for.
Third, I just wrote a proposal today for a client on Mongo, not at scale, who experiences silent database failures due to lack of acid. they only noticed because Mongo sometimes tells them there are -2 people left in the room.
Nobody at scale uses mongo because somewhere along the way you spend your giant bags of scale-money to hire a real engineering team to solve the silent errors and failwhales you've been suffering by migrating to an architecture that actually lets you reason about failure a little better than "it looks like something bad happened but we have no idea when how or why"
Repeat after me: PostgreSQL isn't ACID in a distributed system.
PostgreSQL isn't actually ACID when your database is multiple machines. PostgreSQL isn't actually ACID when you set up your first read replica on RDS. PostgreSQL isn't actually ACID when you use streaming or logical replication. PostgreSQL isn't actually ACID when you start sharding your users table. PostgreSQL isn't actually ACID in a world in which people need to scale out from one big box. It's eventually consistent, which is even less than I can say about MongoDB on a single-document level.
Personally, PostgreSQL is my favorite DBMS. It's well-engineered, extremely mature, and has lots of great features. Working seamlessly and providing strong guarantees outside a single box, unfortunately, isn't one of them. Not yet, at least.
To address the rest of your points: those people are using MongoDB wrong. PostgreSQL basically stops working well too, once you start mixing transaction processing and business reporting and BI in the same box at scale.
If you're going to claim that MongoDB is bad because it's not ACID or somesuch, you better hold PostgreSQL to the same standard, and understand precisely why it is that the two systems were built so differently.
Postgresql with read replicas is ACID-Consistent transactions because single writer, and CAP-AP eventually consistent because distributed reads.
It is easy to agree with you here: "PostgreSQL basically stops working well too, once you start mixing transaction processing and business reporting and BI in the same box at scale." It's worse than that - the app has to be coded before it can be scaled so you get fucked by complexity-scale via ORM a lot sooner than bigdata-scale. But as you sort of say, the point of a data warehouse is to be cap-AP (eventually consistent) through read-replicas without breaking ACID-consistent transactions for the system as a whole.
as to the argument "everything is bad at scale" i think that has merit too, I just gave a talk at ClojureNYC about building real apps with Postgres (tables) vs Neo4J (graph) vs Mongo (documents) vs Datomic (eavt): https://github.com/hyperfiddle/hypercrud.browser/issues/4 (Did I say real "apps"? I meant "balls of mud")
IMO, as I've said elsewhere on this thread, I really don't think it's useful to talk about the C-in-ACID and the C-in-CAP as if they were completely different things. The original research (both Gray's and Brewer's) clearly talks about it in terms of interface guarantees on your data, and the latter is directly built upon the former. As a user, all I care about is that the new data shows up once the system accepts it, no buts.
What good is my Postgres single-node ACID if I have to do all this work to deal with replication lag?
What good is ACID writes with AP reads? It doesn't lose data! Don't like eventual consistency? You have options! Check out Datomic - ACID transactions and strongly consistent + distributed reads. http://www.datomic.com/
Git is another ACID single-writer system with strong consistency and distributed reads.
"ACID" isn't a term that used to describe distributed systems— that's physically impossible— only systems on a single machine. To say PostgreSQL isn't ACID is incorrect and disingenuous.
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.
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.
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?
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.
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.
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-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?
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".
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.
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.
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.
I'm afraid we're in violent agreement. I never said "Postgres isn't ACID", that's never been in dispute. I said that you can't treat Postgres with a read replica as an ACID database, because as you and I both pointed out, it's then a distributed system (no self respecting, single-node databases is eventually consistent, so my use of that term should have implied a distributed system).
The impression I get is that at scale, you don't really "need joins" and multi-table ACID for the kind of workload that MongoDB is targeting, i.e. massive throughput embarrassingly-parallel low-latency OLTP short requests that do point queries and point writes. It seems to be atomic, consistent and durable enough for that [1].
For analytical queries that really do need expressive SQL features, you generally wouldn't want to run them on the same database anyways, not when you're "at scale" and your seven-way join could impact other request latencies. You export it to Redshift or HDFS/S3, and do all the reporting/BI/analytics on it.
[1] https://jepsen.io/analyses/mongodb-3-4-0-rc3