A Postgres Perspective on MongoDB
bonesmoses.org
bonesmoses.org
In the database world, we are still in the second phase, which is exemplified by databases such as Mongo, which have started from scratch and thrown out many of the good ideas embodied in older databases. I suppose that the new JSON capabilities of Postgres are an attempt to move database design into the third phase, which combines the ease of use of NoSQL with the many superior qualities of traditional relational databases.
Sometimes you have payloads that can't fit in a traditional RDBMS, and SQL/Relational don't cluster/shard well.
NoSQL bring convenient clustering/sharding to your data. Anecdote: an old team chose Cassandra solely for its admin-tools (which are really cool), even if we didn't actually need a NoSQL; I lost that battle, but I least we all learned something.
Test suites guard against most things type safety does, so if you have a test suite anyway, the type safety becomes a net burden.
Perl was one of the first hugely successful dynamic languages, though I will politely defer to Shell and TCL folks for a rigorous discussion of that claim. (:
Perl has been really serious about testing since early on, and, to my understanding, remains one of the most test-centric major languages out there.
ACID and schemas are not actually coupled. You can have strict schemas without ACID and vice versa.
Relational stores without ACID are very hard to reason about. Any associations that are created with the primary entry may or may not be there at lookup. This is solved via documents.
I agree with your prediction that e will have document schemas enforced in the DB.
> Relational stores without ACID are very hard to reason about. Any associations that are created with the primary entry may or may not be there at lookup. This is solved via documents.
This is only solved via. documents for applications which don't need relational consistency or transactions, which is a minor percentage of web-applications.
Most applications of Mongodb that I've seen do not fall into that category, further MongoDB is marketed as a generic, not as a very application specific kind of solution..
As soon as you find your application does need transactional consistency between entries, you'll be up the creek making your own paddle - since unlike with SQL databases where ACID is at least supported MongoDB just gives you:
> $isolated does not work with sharded clusters. [0]
[0] https://docs.mongodb.com/manual/core/write-operations-atomic...
I should rephrase: "This is solved by modeling your data as documents whether or not that is its natural structure."
Well, somewhat. The C in ACID (consistency) is impossible without schema's, because they contain the data validation rules. Without schema's you'll have to do all data validation in the application layer. Which is probably what you want, given that you're going schemaless, but it's AID at the database layer, not ACID.
SparkSQL in particular can "run all 99 TPC-DS queries, which require many of the SQL:2003 features".
That is probably the most apt description of MongoDB I've ever read.
I think I understand what the author means by the statement. But technically it doesn't make much sense.
Based on the schema he writes at the beginning, it looks as though he works mostly with time-series. My understanding is that Cassandra is particularly well-suited for these types of workloads (definitely more so than MongoDB?). If the write throughput is such that a single server can handle it, how would Postgres compare with Cassandra? What are the distinctions in read-latencies? Storage space?
Perhaps for the next article..?
You create a collection for each measurement, JSON document for each day and then preallocate the fields for each hour/minute/second. With batching you can update 100K documents a second pretty easily off one node. For example: https://blog.serverdensity.com/using-mongodb-as-a-time-serie...
I've messed around with Cassandra, but not in much depth. I ran into the way it treats NULL, and that all searchable fields must be indexed, and put it on the backburner for later.
https://pragprog.com/book/rwdata/seven-databases-in-seven-we...
- Deployments get much simpler when migrations for additive changes are unnecessary. Hundreds of deployments a week!
- JSON queries easy to pass around and decorate in application code. Hydration from a dictionary is straightforward. Combined, an ORM isn't really necessary.
You don't need an ORM, but they are handy. I'm pretty sure it wouldn't be an issue to load rows from a RDBMS, using a language like Python, as a dictionary, and just decorate the snot out of that dictionary, to make it look like an object.
Anyway, regarding migrations, I sort of agree. Migrations can be terrifying and slow things, but at least you're allow to make certain assumptions about your data afterwards. Assumptions like "this field will always be there".
With MongoDB, and any other NoSQL database I've used, you need to handle your "migrations" in the application code. So now you have block of code that checks if a given attribute exists in your JSON document.
It's not necessarily a problem, but it is something you need to consider. Personally I much prefer RDBMS, because I actually like SQL and the ability to make ad hoc queries easily. If JavaScript is advertised as the query language for a database I'm not terribly interested, unless the pay off is extremely high.
NoSQL is less rigid but your code pretty much has to be as rigid, right?
In the process of pushing it up to the app, you lose ACID guarantees. Without ACID guarantees, your ability to view/update a snapshot in time of all your data becomes a very difficult problem to solve. As such, it's hard to say the two methodologies are equivalent.
When you start putting relational data into a non-relational DB, you are going to have a bad time.
Compare this to something like Elasticsearch, which is well-known (and well-documented) to have data loss in certain situations.
Frankly I think it's still an awful choice given it's broken replication model.
How well does MongoDB support this need, and how hard is it to achieve equivalent results with Postgres? What are the drawbacks?
I'm guessing that other databases have similar implementations.
Though no API to hook into it, as far as I know.
isn't a collection pretty much like a table
No, it isn't. A table (or relation) has predefined columns, and each row adheres to the data type and constraints for each column. If you were to model a NoSQL "collection" as a table, you'd end up with a sparse matrix where the number of columns could be equal to number_of_rows * number_of_distinct_attributes of the original collection.
you have to tie two collections together with a common ID
Nothing in SQL requires you to join collections based on a common ID (though it is the most useful). But still, an SQL join has a predefined result structure regardless of the actual table contents or join condition. There is no predicting the data structure of a NoSQL join.
I think there is a difference, especially in practice. Relations are a first class citizen in relational databases. Integrity is enforced. Most types of operations can be bundled up in transactions.
That abstraction differs from non relational data stores and you have to design your application to fit it.
However, if you need such a thing, there's always things such as Mongoose[1] that can do this trivially for you.
There are plenty more problems with emulating schema in app code (Mongoose or anything else), so it's not that trivial.
The database's only job is to keep persistence between server restarts. On server start I fetch the a collection from the database and populate a variable with it, which I use for any direct access.
When a user updates something it will update the variable as well as updating the collection in the database.
Something like this:
var foo = readFromDB();
socket.on('connection', function() {
this.emit(foo);
});
socket.on('update', function(data) {
foo[data.id] = data.val;
updateDB(data.id, data.val);
});
What should I use instead of MongoDB for this? Or is my approach fundamentally flawed?
What is the simplest tool to do the job? Or maybe better yet: Use something that's already (or will be), in your technology stack to avoid having one more thing in it.
> What is the simplest tool to do the job?
For me it's definitely MongoDB but I'm afraid of data loss etc. My example was simplified. In reality I want to update a sub-array within the collection, like this: collection['some_identifier']['items'][3] = 123. Overwriting the whole 'some_identifier' document seems wasteful. With MongoDB it's easy though to update nested sub-data.
When you need anything more (which is probably "soon" if not "already"), it'll bite one way or the other.
If you batch operations it is extremely fast at updates across multiple documents.
Compare aggregation framework to what SQL has, for example:
https://docs.mongodb.com/manual/reference/sql-aggregation-co...
Or even within Mongo, querying and aggregating are astonishingly different things, that you need to learn separately.
And then there are JOINS. And stale reads with other serialization issues.
Then if you wanted to make it 456 you would only be updating "someIdentifier_items_3".
I guess using a plain file would mean I have to overwrite everything on each write which seems wasteful.
Also I think updating a sub array in a collection triggers a whole write to that collection in mongodb. In any case I would avoid mongodb if you can, it seems redis might be a better fit for you.
Using Redis could be a solution (if Redis can accommodate your read pattern), or you'll need a pub/sub system (like that of redis) to propagate db changes from one process to the other...
It also can read from and write to Redis using Foreign Data Wrappers. Pretty neat, if you want your DB to be single place you store data, but something faster for reads or cache.
And yeah, anything's better than Mongo :)
Yeah I guess keeping to their comfort zone while keeping a sense of superiority is easier :)
Whenever I encounter Mongo nowadays, it's either simulating multi table joins on app level, or inventing locking with Redis.
Part of the issue is people storing relations in NoSQL just because, but I can't think of any use cases where Mongo wins over other NoSQL solutions. Perhaps you have some?
For small deployments, and when you don't have to worry about (many) relations between tables it should be ok to use Mongo
Maybe Cassandra is better in some cases, or PostgreSQL with JSON
And of course Redis in a lot of different situations
I don't know what you mean by "inventing locking with Redis" but it doesn't seem like anything I would advise doing either with Mongo or Redis
* all you "OMGZORS USE A FILE SYSTEM!" can keep your mouth shut. This is drastically faster and much more manageable for the use-case.
I've recently become interested in using PouchDB (a CounchDB implementation) for syncing offline content between a user's browser and server where the content is based on a set of fixed documents. I believe that MongoDB can provide similar replication tricks. Once on the server I would expect to extract the data from the documents into a relational warehouse database (probably PostgreSQL as it's one of the databases I know best).
A document store _seems_ like the right tool for the job but could expand on why something like Mongo or Couch is a bad idea for situations where you're just storing/syncing documents?
Couch is fine, other NoSQL is fine. It's just Mongo I don't like:
- weak, if any, guarantees on data consistency and safety;
- poor API. If you need anything more complicated than `findOne`, there are at least 3 unrelated syntaxes with major differences that are not compatible;
- performance is not what marketing team says it is. Even at home JSON turf, Postgres is pretty comparable.
If you have data and queries that will benefit from document store, go for it. But don't choose only based on marketing.
There are many more rant on the web, I like this one:
http://cryto.net/~joepie91/blog/2015/07/19/why-you-should-ne...
It's not a bad idea, MongoDB has a lot of undeserved hate (some of it deserved, but the minority), however I'm not sure it would do syncing as you want automatically (CouchDB is probably closer out-of-the-box to what you want)
But it's something you're less likely to need if you structure your data, in an RDBMS. If you know your SQL, then optimizations should be preferred before scaling out. Not just because you need to, but because it can improve user experience (server response latency).
Almost every major organisation will have a big data analytics program doing feature extraction, modelling, machine learning etc. Usually this starts with Hadoop/Spark with data in HDFS but then a point comes when you want this in a database.
PostgreSQL is awful at this which is why almost no uses it in this space. Apart from the lack of native drivers it is simply too difficult to do basic clustering. And the fact that it isn't built into the product doesn't give you a lot of confidence that it is (a) going to work and (b) is going to be supported.
And yes for those that scale out NoSQL is a magic solution. That's why they are so popular. I can deploy a 40 node Cassandra cluster in 30 mins and guarantee it works. Likewise MongoDB replica sets are ridiculously easy.
I tend to disagree that Postgres does partitioning well; partioning table resolution is rather dumb, there is no hinting (e.g. there is no way to tell postgres that there is a unique mapping for each query to a table) and no caching of CHECK results.
Everyone organization that has a database wants to scale out to at least a 2 node cluster just for not having a single point of failure. Scaling is not always about performance, at first, it's more about availability.
1. In many cases, there is a ~30ms floor on operations if a server is non-local. That's true of virtually all services on AWS -- whether SQS, SNS, SES, RDS (MySQL/PostgreSQL), etc.
2. In many cases, rows are missing. A table has a million rows. Ten are deleted. It's not compacted. So it's not a simple size lookup. A lot of this depends on how the data is stored/indexed. That's something which is naively pretty slow, but easily optimized.
Few operations have this particular limitation, and it's been a sticking point in the Postgres world for years. Unfortunately there's no easy way around it.
If your application is big enough that you really need to scale postgres, I assure you, you will have a whole lot of tedious problems - not just sharding.