Goodbye MongoDB, Hello PostgreSQL (2015)(developer.olery.com)
developer.olery.com
Goodbye MongoDB, Hello PostgreSQL (2015)
http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/
171 comments
I learned a framework several years ago and its default db was MongoDB. So I worked with MongoDB for a few years. Then at my job I used PostgreSQL for a several months. I will probably never use MongoDB again.
I'm sorry you had to go through that.
Oh please, quit the bullshit elitism.
I think postgres is a way more robust solution than mongo, but I've had my worst db experiences having to deal with a postgres db with almost all the business logic in triggers. That was a huge pain in the ass to maintain and understand.
Mongo is defnitely a limited solution, but for certain cases it just works pretty well, with less complexity than postgres. At one of my jobs, I've setupped and maintained a Mongo cluster that did its job without causing any fuss for years.
I think postgres is a way more robust solution than mongo, but I've had my worst db experiences having to deal with a postgres db with almost all the business logic in triggers. That was a huge pain in the ass to maintain and understand.
Mongo is defnitely a limited solution, but for certain cases it just works pretty well, with less complexity than postgres. At one of my jobs, I've setupped and maintained a Mongo cluster that did its job without causing any fuss for years.
You suffered because of idiot co-workers.
Let me tell you, your idiot co-workers can also do some evil things in Mongo pipelines.
Very, very evil things.
Let me tell you, your idiot co-workers can also do some evil things in Mongo pipelines.
Very, very evil things.
Previous discussion from 2015: https://news.ycombinator.com/item?id=9178773
The top comments of this old thread are retrospectively hilarious because sone frontend guys argues about wether SQL is not easily composable and exchanges trick about how you can securely and dynamically concatenate SQL statements.
But nowhere to be found is the concept of prepared statements that would gracefully help then write nicer code. https://www.postgresql.org/docs/current/sql-prepare.html
And that is a lightweight in-code solution for those who are repelled by database procedures which is yet another way to solve theirs issue with SQL.
But nowhere to be found is the concept of prepared statements that would gracefully help then write nicer code. https://www.postgresql.org/docs/current/sql-prepare.html
And that is a lightweight in-code solution for those who are repelled by database procedures which is yet another way to solve theirs issue with SQL.
I was involved in those original discussions and it was just the standard “how to manipulate sql” discussion.
Prepared statements don’t help you here, right? My understanding is that they’re purely for performance as the parser doesn’t need to be rerun each time. You need to recreate them for every dB session.
Prepared statements don’t help you here, right? My understanding is that they’re purely for performance as the parser doesn’t need to be rerun each time. You need to recreate them for every dB session.
No sql injection, can set permissions for each proc, isolates app from DB implementation so you don’t have to redeploy app. Eg you can migrate older data to an archive table and query it on certain circumstances. App doesn’t need to know.
That’s stored ptocedures you’re talking about - I think. We’re talking about prepared statements.
I'm not familiar enought with the execution context, i could be wrong and i totally get that theses are examples while final code would probably include escaping checks. But that being said some of the proposed solutions could probably lead to a little bobby table situation...
https://xkcd.com/327/
https://xkcd.com/327/
Correct me if I'm wrong, but I think you missed the point of the discussion? It is not connected to frontend at all. It talks about the difference between using an ORM versus constructing SQL statements directly, as strings.
In that light I don't think `PREPARE` and stored procedures would help much (or at all).
I also don't find them hilarious, though they do represent very extreme views on the subject (imho) while to me it is essentially a tradeoff (I use both direct SQL and ORMs, depending on the needs of the project).
In that light I don't think `PREPARE` and stored procedures would help much (or at all).
I also don't find them hilarious, though they do represent very extreme views on the subject (imho) while to me it is essentially a tradeoff (I use both direct SQL and ORMs, depending on the needs of the project).
My point is precisely that they compare two subpar approach of SQL while prepared or stored procedure are the real contender here.
And as far as I understand depending on your use case prepared or stored is better.
Prepared mean less code base inside the db and still more composability with less sql injection risks.
Stored add even more composability and security but at the cost of managing code inside the db.
And as far as I understand depending on your use case prepared or stored is better.
Prepared mean less code base inside the db and still more composability with less sql injection risks.
Stored add even more composability and security but at the cost of managing code inside the db.
MongoDB is like snapchat for database
I'm starting to realize all the people complaining about data loss in Mongodb just didn't understand the tradeoffs between weak consistency and strong consistency.
Would've been funnier and timely about a decade ago.
MongoDB would have been timely about a decade ago.
Make a joke similar to this a decade ago (as I did) and developers would jump on you for being "old" or "not getting it".
So, not really.
But I agree that this joke is an old one and doesn't really have a place in a constructive discussion on hackernews.
So, not really.
But I agree that this joke is an old one and doesn't really have a place in a constructive discussion on hackernews.
this was funny, but I think its a lot better now. Imo rolling their own consensus protocol was a terrible idea, but they are using Raft now I believe.
I used both NoSQL and SQL databases. Proper replication topologies (e.g. multi-master configurations) and JSON columns killed the NoSQL movement. I'm glad the NoSQL movement existed and forced the SQL camp into out-of-the-box thinking, but I wouldn't go for any NoSQL solution today on a greenfield project.
What if you didn't need relational queries and was dealing with a large volume of data that needed to be sharded?
In what scenarios does one work on data without any relations?
Honest question - perhaps I'm too used to think in SQL but even with e.g. a completely flat bunch of documents one usually needs at least access control (i.e. document owners, which is a relation).
Honest question - perhaps I'm too used to think in SQL but even with e.g. a completely flat bunch of documents one usually needs at least access control (i.e. document owners, which is a relation).
Most data in large enterprises e.g. telcos, banks, insurance etc are not stored in a single data warehouse with well-defined relationships. They are in multiple disparate silos of which their EDW is just one.
It's why Data Lakes become so commonplace because it was an easy way to just get all of the data out of the silos into one place so that the business could attempt to join between them.
And it's why MongoDB (and other unstructured friendly storage systems) have remained so popular because the reality is that those datasets are not well governed, no-one knows the schemas and no-one knows how to join between them.
It's why Data Lakes become so commonplace because it was an easy way to just get all of the data out of the silos into one place so that the business could attempt to join between them.
And it's why MongoDB (and other unstructured friendly storage systems) have remained so popular because the reality is that those datasets are not well governed, no-one knows the schemas and no-one knows how to join between them.
If an enterprise wants to "get all the data out of the silos", unstructured storage is only "friendly" to the temptation to be sloppy in such an export.
Not only missing or corrupted individual columns, but completely incoherent and arbitrary complex document structures. If "no-one knows the schemas and no-one knows how to join between them" the data lake project has already failed to provide value.
Not only missing or corrupted individual columns, but completely incoherent and arbitrary complex document structures. If "no-one knows the schemas and no-one knows how to join between them" the data lake project has already failed to provide value.
The secret is that it doesn't matter, because many, many tech businesses aren't business but investment ponzi schemes / "moonshots" on other people's money, who are mostly arm's length idiots.
Say you have a pile of garbage in silos: user metrics, analytics, usage stats, logs, sales, revenue across three different systems, etc.
You copy the garbage out of the silos and into a lake. It's a mess. No one could tell you anything real with that data.
So you write queries that ignore the errors, and don't show what you say they show, but look nice and you say they show what you know people want to hear. You know you have sales, right? You know things are going well, right? Or they will be soon, for sure. So you're making the numbers tell the story -- any problems in the data are just bumps in the measurement.
You show investors your fabrications, they show their investors your fabrications, and so on. You get another funding round for ~1.5x one year of salary for your employees + office costs. Life goes on, you have meetings. You make presentations.
Rinse. Repeat. You sit on boards, talk about innovation, sponsor a charity. Maybe make some investments! Be a local tech celebrity. 5 years go by, your business is huge and bleeding cash, and somehow... no one's willing to invest more.
So you sell what you can for what you can get. Some investors broke even, some lost the farm, some maybe even made 3-4x. No hard feelings overall! "Our Amazing Journey" blog post. The employees move on. You move on.
Rinse, and repeat.
Garbage in, garbage out.
It doesn't matter, because you were never really running a business.
Say you have a pile of garbage in silos: user metrics, analytics, usage stats, logs, sales, revenue across three different systems, etc.
You copy the garbage out of the silos and into a lake. It's a mess. No one could tell you anything real with that data.
So you write queries that ignore the errors, and don't show what you say they show, but look nice and you say they show what you know people want to hear. You know you have sales, right? You know things are going well, right? Or they will be soon, for sure. So you're making the numbers tell the story -- any problems in the data are just bumps in the measurement.
You show investors your fabrications, they show their investors your fabrications, and so on. You get another funding round for ~1.5x one year of salary for your employees + office costs. Life goes on, you have meetings. You make presentations.
Rinse. Repeat. You sit on boards, talk about innovation, sponsor a charity. Maybe make some investments! Be a local tech celebrity. 5 years go by, your business is huge and bleeding cash, and somehow... no one's willing to invest more.
So you sell what you can for what you can get. Some investors broke even, some lost the farm, some maybe even made 3-4x. No hard feelings overall! "Our Amazing Journey" blog post. The employees move on. You move on.
Rinse, and repeat.
Garbage in, garbage out.
It doesn't matter, because you were never really running a business.
I'm sorry that you are involved with this kind of toxic "business" and with funding round beggars. I've only worked with real users of real data for real process integration, real marketing, real monitoring and auditing, under real management, in real companies.
Thanks! I appreciate that you bothered at all to respond to my many-days-later comment. :) I'm glad you've respectively had good luck in this regard.
I should be clear that I'm not speaking in particular about a present or past employer, nor do I really mean to denigrate businesses operating as I described. My rhetorical cynicism might be misread as just cynicism, so I'd like to try again, hopefully more charitably:
Not many top decision makers are (or closely employ) good stats people. So pretty optimistic non-stats folks try to figure out what to measure, how to measure it, what is meaningful, and how to extrapolate that into good forecasts -- not a great start for accurate measurement and forecasting.
Next, data is often bad: because of adblockers, brief app outages, known workarounds, bugs, gaps, measurement-method changes over time, differing cohort assumptions, etc. There always ends up being some hand waving. Another strike against accurate measurement and forecasting.
Finally, businesses must survive, and many are not cash-rich enough for safety or for growth, so in the near term they may be oriented more toward investment than revenue. Some good hand waving can then be the essential survival activity, and good hand-waving may require treating some fictions as real. The dreaded third strike falls against accurate measurement and forecasts.
So in a sense, these aren't traditional businesses. Their measurements and forecasts may not be very accurate, but for now it doesn't really matter. What matters more is usually the story.
I should be clear that I'm not speaking in particular about a present or past employer, nor do I really mean to denigrate businesses operating as I described. My rhetorical cynicism might be misread as just cynicism, so I'd like to try again, hopefully more charitably:
Not many top decision makers are (or closely employ) good stats people. So pretty optimistic non-stats folks try to figure out what to measure, how to measure it, what is meaningful, and how to extrapolate that into good forecasts -- not a great start for accurate measurement and forecasting.
Next, data is often bad: because of adblockers, brief app outages, known workarounds, bugs, gaps, measurement-method changes over time, differing cohort assumptions, etc. There always ends up being some hand waving. Another strike against accurate measurement and forecasting.
Finally, businesses must survive, and many are not cash-rich enough for safety or for growth, so in the near term they may be oriented more toward investment than revenue. Some good hand waving can then be the essential survival activity, and good hand-waving may require treating some fictions as real. The dreaded third strike falls against accurate measurement and forecasts.
So in a sense, these aren't traditional businesses. Their measurements and forecasts may not be very accurate, but for now it doesn't really matter. What matters more is usually the story.
Yeah, it's kicking the can down the road!
If you can't or won't do the work to understand and define the schema at the time of ingestion, you're asking consumers of that data to do the work for you later on, and then you're looking at an archeology project to try and figure out what the heck people were thinking in the past. It's only going to get worse as you keep shoving new data in there with its own incompatible poorly defined schema.
It probably ticks a compliance box but good luck making use of it...
If you can't or won't do the work to understand and define the schema at the time of ingestion, you're asking consumers of that data to do the work for you later on, and then you're looking at an archeology project to try and figure out what the heck people were thinking in the past. It's only going to get worse as you keep shoving new data in there with its own incompatible poorly defined schema.
It probably ticks a compliance box but good luck making use of it...
I've only ever witnessed consumer-driven ad-hoc integration initiatives, where the team that needs data coerces the team that has data into providing it in a usable form, and people notice stale or incorrect feeds because they are actually in use. Why should a company invest server resources and development effort on "ingesting" data that nobody cares about in a way that doesn't fit any purpose? Attempting to collect "all" data is meaningless.
I'm horrified by the notion of banks in particular storing their data where "those datasets are not well governed, no-one knows the schemas". This is people's money you're talking about.
A lot of applications can fail with an "eh, whatcha gonna do?" shrug when records fall out because their schemas drifted or two different developers interpreted them differently. So somebody's preferences stop working or their early "likes" get forgotten; so what? Move fast, break stuff, etc.
But when your data actually matters, I'd much rather pay the expense of having some discipline about it. The idea that my money might become the stuff that gets "broken" is kinda terrifying.
A lot of applications can fail with an "eh, whatcha gonna do?" shrug when records fall out because their schemas drifted or two different developers interpreted them differently. So somebody's preferences stop working or their early "likes" get forgotten; so what? Move fast, break stuff, etc.
But when your data actually matters, I'd much rather pay the expense of having some discipline about it. The idea that my money might become the stuff that gets "broken" is kinda terrifying.
All this talk of "relationships" overlooks the fact that the "relational" part of an RDMS is the mathematical concept of relations.
When you get piles of JSON data from somewhere else (e.g. an API response from a provider you subscribe to) and want to store all of it intact, query it, and that somewhere else may insert new fields at any time in the future without telling you.
When you have multiple types of objects with differently populated fields that you want to store in a single collection and query across them in currently unknown ways, and where you need flexibility for the unknown.
When you have multiple types of objects with differently populated fields that you want to store in a single collection and query across them in currently unknown ways, and where you need flexibility for the unknown.
That sounds like a great use case for OLAP and fuzzy search solutions.
Eg. Dump all of the data to S3 and search it in parallel via AWS Athena.
If you want fast fuzzy search, then elasticsearch is built for this.
If the data is small enough that you want B-tree indexes (with the accompanying slower writes and lower query latencies), then any SQL database with a JSON column will work.
Eg. Dump all of the data to S3 and search it in parallel via AWS Athena.
If you want fast fuzzy search, then elasticsearch is built for this.
If the data is small enough that you want B-tree indexes (with the accompanying slower writes and lower query latencies), then any SQL database with a JSON column will work.
> Eg. Dump all of the data to S3 and search it in parallel via AWS Athena.
Too proprietary. Can't be run locally, on robots, etc. Can't be transferred to other cloud providers. Also S3 is horribly slow to write or delete thousands of records at once.
Too proprietary. Can't be run locally, on robots, etc. Can't be transferred to other cloud providers. Also S3 is horribly slow to write or delete thousands of records at once.
> Too proprietary.
Every major cloud provider has a blob store. Most are S3-interface compatible, or can be fronted by something which is, like Minio.
> Can't be run locally, on robots, etc.
If the contents of your S3 keys are just line-delineated JSON files, you can easily download those files and run scripts or process them locally.
> Can't be transferred to other cloud providers.
Again, not true — a tool like rclone handles this case in a single command. Depends on the amount of data, but if anything it's easier to move a bunch of flat files between providers than copying a database backup around. You have to pay for egress bandwidth, though, of course.
> Also S3 is horribly slow to write or delete thousands of records at once.
If you're keeping one JSON record per S3 key, that is a blatant misuse of the tool and performance will be terrible. On the other hand, if you batch records into files of appropriate sizes, it's very cost-effective to store and query via Athena, Spark on EMR, etc.
If you need individual record-level access as identifiable by a key, then you'd probably be better off with Redis or Memcached. (though, those will not be as good for bulk offline processing) It's all about your access patterns.
Every major cloud provider has a blob store. Most are S3-interface compatible, or can be fronted by something which is, like Minio.
> Can't be run locally, on robots, etc.
If the contents of your S3 keys are just line-delineated JSON files, you can easily download those files and run scripts or process them locally.
> Can't be transferred to other cloud providers.
Again, not true — a tool like rclone handles this case in a single command. Depends on the amount of data, but if anything it's easier to move a bunch of flat files between providers than copying a database backup around. You have to pay for egress bandwidth, though, of course.
> Also S3 is horribly slow to write or delete thousands of records at once.
If you're keeping one JSON record per S3 key, that is a blatant misuse of the tool and performance will be terrible. On the other hand, if you batch records into files of appropriate sizes, it's very cost-effective to store and query via Athena, Spark on EMR, etc.
If you need individual record-level access as identifiable by a key, then you'd probably be better off with Redis or Memcached. (though, those will not be as good for bulk offline processing) It's all about your access patterns.
> On the other hand, if you batch records into files of appropriate sizes
Too much work. If you're a startup that's too much stuff to maintain. Much easier to get a hosted MongoDB and launch tomorrow.
And when you only have 2 months rent in the bank and investors want demo after demo in order to give you cash, tools like MongoDB do make a difference. And yes, I've been in that situation before.
Too much work. If you're a startup that's too much stuff to maintain. Much easier to get a hosted MongoDB and launch tomorrow.
And when you only have 2 months rent in the bank and investors want demo after demo in order to give you cash, tools like MongoDB do make a difference. And yes, I've been in that situation before.
Your ability to turn the smallest of technical hurdles into an insurmountable problem is truly impressive.
> Also S3 is horribly slow to write or delete thousands of records at once.
I'll add that S3 is only slow to delete things sequentially. You can delete or write an effectively infinite amount of items in parallel.
I'll add that S3 is only slow to delete things sequentially. You can delete or write an effectively infinite amount of items in parallel.
That's what I'm using postgresql for, among other things. The only drawback I've ran into so far is that it sometimes doesn't pick the proper index for a specific query.
> and that somewhere else may insert new fields at any time in the future without telling you.
That's what interface contracts and API versioning is for - and new fields shouldn't be any problem at all unless you're parsing the JSON by hand.
That's what interface contracts and API versioning is for - and new fields shouldn't be any problem at all unless you're parsing the JSON by hand.
Search engines
Search indices, at least in my experience, are denormalised views of relational data.
Financial data
In basically every case you are querying all data for a symbol in a given timeframe and then process it further outside of the database.
In basically every case you are querying all data for a symbol in a given timeframe and then process it further outside of the database.
But a relational database is great for that.
The only way to beat a relational database here would be in performance. Because of course, for a specific use-case, you can always write a specialised data store that is faster than a general-purpose one. But the performance advantage might not be enough to justify the cost. If you're talking about storing every tick on an exchange, it might be, but if you're storing close prices, probably not.
select *
from prices
where symbol = any (?)
and timestamp >= ?
and timestamp < ?
The only way to beat a relational database here would be in performance. Because of course, for a specific use-case, you can always write a specialised data store that is faster than a general-purpose one. But the performance advantage might not be enough to justify the cost. If you're talking about storing every tick on an exchange, it might be, but if you're storing close prices, probably not.
Mongo is obviously not competitive with KDB though, if you are talking of timeseries data as you suggest.
I would think it is in fact very much the wrong tool for the job. Time series data screams structured storage, not unstructured.
I would think it is in fact very much the wrong tool for the job. Time series data screams structured storage, not unstructured.
What financial systems are you designing?
Redis is schemaless and make perfect sense that s an high availability storage in memory storage. And most (web) projects use something like Redis.
Redis is a key value store and not a database. Also I'd strongly disagree that it's used in most web projects.
I have never, not a single time had the need to use Redis in 17 years of web development
Microservices / DDD
"Relations" are stored as an object, not a separate table.
See: AggregateRoot
Eg.
Document: { metadata: {}, owner: {}, sharedWith:{}}
"Relations" are stored as an object, not a separate table.
See: AggregateRoot
Eg.
Document: { metadata: {}, owner: {}, sharedWith:{}}
I've got a project where one part of the application is just for editing a medium-sized, hierarchical object. So it's easiest to just get it from the database as a whole, and update it like that. It's also very easy to understand for the frontend programmers. The other part gets data from users and dumps it in its entirety in another table. Then the analysis part simply retrieves them all (at most a few thousand objects), and can build graphs, or whatever visualization with them. There's not a single JOIN involved, and updating is simple. If it goes beyond that, it's time to look at a relational database, either for performance reasons or because the API becomes cumbersome.
I will pedantically note that in database theory, "relation" is a name for what we usually call a table. You're talking about relationships.
https://en.wikipedia.org/wiki/Relation_(database)
If your data is completely flat, and fits in one table, that's still a relation!
https://en.wikipedia.org/wiki/Relation_(database)
If your data is completely flat, and fits in one table, that's still a relation!
TIL, thanks :)
If you just need a key-value store, a database like MongoDB is rather oversized. Redis or and a bunch of object storage system (now that they actually include locking) are much easier to operate.
As soon as you're back to needing complex queries (with or without relations, Postgres it is again.
As soon as you're back to needing complex queries (with or without relations, Postgres it is again.
Redis is an in-memory datastore first and foremost (with optional persistence). With that said, it's probably not the right tool if you need to store and persist TB-worth of data even if it is key-value. Something like Cassandra is probably a better fit if you have a lot of non-relational data that needs to be persisted and you need it to scale.
But you need more than a few TiB of stored data for Cassandra to be a net win. I've been part of two inhouse software projects with database in the single digit TiB scale based on Cassandra. Both projects where switched to PostgreSQL and the performance improved for almost everything. Plus all the other advantages of mature relational database. Hosting Cassandra is a whole different league of complication compared to PostgreSQL. So you should be very sure that you actually gain something before choosing it over PostgreSQL.
Absolutely - pick the right tool for the job. Generally speaking I'd go for PostgreSQL in most cases but there are definitely scenarios where Cassandra would perform better and be more appropriate.
As a former Oracle DBA, and general skeptic of most of the use cases the NoSQL ends up getting chosen for, I’ll gladly say that document databases are pretty good at storing documents. I’ve seen them used very well in content management applications, and the document-database-as-a-materialized-view use case can make a lot of sense. Just like graph databases can be very good at storing graphs. As far as the bandwagoning goes though, I think Mongo has taught a lot of people about the strengths of the relational model.
There are plenty of modern, distributed RDBMSes that make sharding transparent to the user (E.g. cockroachDB, yugabyte, vitess, many cloud offerings, etc.). Most NoSQL databases end up adding transactions because they are important, and thus the scale advantages for NoSQL systems over relational databases are diminishing, if remaining at all.
Latency at p99 is substantially better at scale with a key value lookup in nosql over postgresql.
Thing is most people don’t have scale these days. You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd. Until you exceed that you don’t necessarily have scale.
Thing is most people don’t have scale these days. You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd. Until you exceed that you don’t necessarily have scale.
> You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd
And then that box falls over because the entire region fails like just happened yesterday with OVH. Or it just randomly fails like has happened to me with AWS dozens of times.
Vertically scaling a database on your own cloud instances is amateur hour. Either use a cloud-managed database or one that is highly available.
And then that box falls over because the entire region fails like just happened yesterday with OVH. Or it just randomly fails like has happened to me with AWS dozens of times.
Vertically scaling a database on your own cloud instances is amateur hour. Either use a cloud-managed database or one that is highly available.
Sure. And those services support some fairly beefy instance types. Such as https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rd...
Well it depends what you consider scale. When you look at the high scale deployments like Slack, YouTube, GitHub, Square, etc they are all using Vitess and MySQL.
YouTube doesn’t use just mysql or vitess these days they migrated to other google databases.
Slack, GitHub both are stupidly shardable. I doubt it’s one RDBMS handling every customer, chat room, git repo. And instead they’ve segmented the workload across multiple instances.
That doesn’t work for every use case
Slack, GitHub both are stupidly shardable. I doubt it’s one RDBMS handling every customer, chat room, git repo. And instead they’ve segmented the workload across multiple instances.
That doesn’t work for every use case
Of course they run on multiple instances. It doesn't work for every use case but it works for more than people realize. Too many people vertically scale, or add read replicas which is a ticking time bomb.
To me, "warm" (not hot) data falls into one of two boxes:
- schema on write: You know what future queries you will ask. Use SQL.
- schema on read: You don't know what future queries you will ask. Use object storage.
Of course, in practice, part of your data will be schema on write and part on read.
- schema on write: You know what future queries you will ask. Use SQL.
- schema on read: You don't know what future queries you will ask. Use object storage.
Of course, in practice, part of your data will be schema on write and part on read.
Can you explain this more? I’d argue that if you don’t know the patterns you’ll use to access your data, relational is the way to go. That’s the point, no? Flexibility over how you access your data.
With an object store you mostly have to have decided upfront which data is collocated together.
With an object store you mostly have to have decided upfront which data is collocated together.
Maybe I explained it badly. :)
SQL essentially forces you to first define your data schema, then write data according to that schema. This works great for reading a user profile given an email; or read all comments of all users who are born before 1995. Since you have a schema on write, you can add indexes to optimise these queries.
In contrast, if you need to do "anomaly detection" over an opaque dataset (where the definition of "anomaly" moves faster than you can rewrite your schemas), then it's better to just do a full dataset scan and create a schema on read.
My 2 cents.
SQL essentially forces you to first define your data schema, then write data according to that schema. This works great for reading a user profile given an email; or read all comments of all users who are born before 1995. Since you have a schema on write, you can add indexes to optimise these queries.
In contrast, if you need to do "anomaly detection" over an opaque dataset (where the definition of "anomaly" moves faster than you can rewrite your schemas), then it's better to just do a full dataset scan and create a schema on read.
My 2 cents.
Still not sure I buy it. I can extract a document representation from my relational data that matches your document store at read time. I can effectively choose my schema when I read, there’s no need to rewrite it at the storage layer.
Maybe I do not get your point but I have a complete opposite opinion. If you do not know about your clients' queries, you are better off with SQL since it enables clients to query exactly the data they need. If you know about future queries, you can shape your object or document storage or db beforehand to match these queries.
I agree with you. SQL allows for a much larger set of possible unknown-at-design-time queries as the data is broken down into atomic units.
With a document store, you often find yourself de-normalising and copying data to new collections on-write to deal with the limited query language.
This means your writers often implicitly encode the possible queries.
As an example, in Redis if you need a 3 way join/filter, you will either a) have your application read all the data and filter it or b) maintain the filtered collection via writers copying the data.
With a document store, you often find yourself de-normalising and copying data to new collections on-write to deal with the limited query language.
This means your writers often implicitly encode the possible queries.
As an example, in Redis if you need a 3 way join/filter, you will either a) have your application read all the data and filter it or b) maintain the filtered collection via writers copying the data.
I had a project where I just needed to be saving a constant flow of data (id + 100 bytes records) somewhere, no persistence needed, no sharding (single machine) - but lots of it. I first turned to Redis and it worked nicely, but since I had PostgreSQL running, I made a performance test with it too. To my surprise, once I optimized both (memory only, pg WAL disabled, persistent connections,...) there was almost no difference in insert rate, so I ended up using PgSQL only.
This is anecdotal, of course, and it is possible that I simply didn't optimize Redis enough (though I did give it my best!). Let me also say that Redis is great otherwise, it was just in this specific case that PostgreSQL surprised me and fulfilled the need itself.
This is anecdotal, of course, and it is possible that I simply didn't optimize Redis enough (though I did give it my best!). Let me also say that Redis is great otherwise, it was just in this specific case that PostgreSQL surprised me and fulfilled the need itself.
Your data-saving test was probably limited by how fast you could save to disk a roughly equal amount of data with the two databases; queries might work and perform more differently.
That's what I meant by "no persistence needed". In both cases, I wasn't actually saving anything to disk. I needed an in-memory storage for a never-ending stream of incoming data that would be read (and purged) by a separate process, no biggie if the data got lost occasionally.
I also simply inserted and read the data, no complex queries or anything. Key-value store is all I needed and for this particular case PostgreSQL was as good at it as Redis.
I also simply inserted and read the data, no complex queries or anything. Key-value store is all I needed and for this particular case PostgreSQL was as good at it as Redis.
Why did you use a database instead of just creating a global map and running in memory? Sharding?
No - different processes were reading and writing.
And once you implement a global map which is accessible from multiple processes, you might as well use an existing solution, like Redis. Or, as I've learned, pgSQL - which gave me the same performance with less moving parts (because I was already using it) when I disabled WAL.
And once you implement a global map which is accessible from multiple processes, you might as well use an existing solution, like Redis. Or, as I've learned, pgSQL - which gave me the same performance with less moving parts (because I was already using it) when I disabled WAL.
Is there a proper replication topology that guarantees all writes survive single node failures e.g. in a 3 node cluster and and reads also as expected? i.e. any sequence of reads and writes execute correctly in any single node failure scenario?
I'm less sure about this. One advantage of NoSQL is that the serverless offerings blow away any SQL DB-as-a-service offering that I've seen. I think that's because NoSQL databases give you minimal, but easy-to-reason-about scaling properties whereas SQL databases do more for you, but at the cost of greatly increased complexity. I think pretty highly of AWS Aurora Serverless and the UX is still garbage in comparison to S3 or DynamoDB.
Now if you start trying to add transactions to a key-value store based architecture, NoSQL gets pretty ugly, but that's misusing the tool IMO.
Most of my projects still rely on NoSQL DBs (usually S3) because they have really excellent UX and I have plenty of data where I don't need relational properties.
Now if you start trying to add transactions to a key-value store based architecture, NoSQL gets pretty ugly, but that's misusing the tool IMO.
Most of my projects still rely on NoSQL DBs (usually S3) because they have really excellent UX and I have plenty of data where I don't need relational properties.
Frankly it sounds like you just don't need a relational database. Their whole selling point is the strong guarantees they provide about your data, and if you aren't using those then you're basically just writing files to disk in a very convoluted way.
You're absolutely right, I don't need a relational database. There are many use cases that don't need relations and avoiding the complexity introduced by RDBMS features I don't need is where NoSQL shines.
I think when most people say NoSQL they don't mean S3 (even though it is like a database). In general I think
- S3
- Redis/Memcached
- Elasticsearch
aren't really NoSQL databases in the Marketing sense since they fulfil such a specific purpose.
- S3
- Redis/Memcached
- Elasticsearch
aren't really NoSQL databases in the Marketing sense since they fulfil such a specific purpose.
Postgres doesn't have multi-master. That's the problem.
Pedantically; it does, but you pay for it: https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndqua...
I believe CockroachDB can do that. Would be a suitable as it's mostly postgres wire compatible.
I am stuck maintaining a mongo monstrosity.
No SQL database has the anywhere near the search capabilities of Elasticsearch/Lucene. I also wouldn't choose Postgres for storing logs, either, but I would use a document store (i.e. ELK stack).
ClickHouse suits well for string logs, especially if a log message have some fixed structure which can be mapped to separate columns. And most logs have some number of predefined fields which are easy to map to columns (e. g. timestamp, IP, request time, e. t. c.). You can store free-form JSON which doesn't have per-defined schema in a string column, but ELK would be probably better in this case.
And then you can query logs using all power of SQL.
And then you can query logs using all power of SQL.
This is from 2015. I wonder if it is the same in 2021. Anyone running high throughput/low latency services using MongoDB?
MongoDB and Postgres have very different strengths and weaknesses. It sounds like they didn't consider the needs of their read and write patterns before they decided to go with MongoDB and then blamed it for not being a good fit for their use case. The article doesn't give any insight at all into the cause of the performance issues, just that they happened. It could've been that they were doing something inefficiently. They also don't give any reasons for the performance increase when switching to Postgres. It could've been anything like they were doing application layer joins with Mongo and things got faster when switching to a relational database because they could do joins in one process without serially fetching data over the network. They also touch on schema on read vs schema on write but instead of talking about the tradeoffs and use cases of both patterns, they just claim schema on read is bad.
The article doesn't give any insight into the cause of the performance issue because they coudn't work out what that cause was:
In another instance we noticed degraded performance of our applications and managed to trace it to our MongoDB cluster. However, upon further inspection we were unable to find the actual cause of the problem. No matter what metrics we installed, tools we used or commands we ran we couldn’t find the cause.
In another instance we noticed degraded performance of our applications and managed to trace it to our MongoDB cluster. However, upon further inspection we were unable to find the actual cause of the problem. No matter what metrics we installed, tools we used or commands we ran we couldn’t find the cause.
True, but it seems like they blamed it on MongoDB just being bad without justification and proposed Postgres as the solution.
No, it seems like the diagnostics tools they were provided didn't help them get to the bottom of the very bad performance issue they were actually having with MongoDB, and then they proposed Postgres as the solution. As most companies do, eventually.
Without knowing the context it's impossible to say if it was user error or application error. I don't see it as a fair write up.
It's a fair write up in that they were unable to diagnose the actual problem with MongoDB. Which leads to one of the problems with the product - it is hard to know what is causing the issue when it has problems.
Hey, I was one of the developers on the team at that time. It's been fun re-reading that write up. I learned a lot since then, and I think the whole industry has learned a lot regarding NoSQL databases, their strengths and weaknesses and how to deal with those in your applications.
You are correct that the biggest issue was that we couldn't diagnose problems when they occurred, and therefore we couldn't learn. Some of those problems were for sure "user error", others were absolutely problems with MongoDB at the time. It was a "young" database back in 2015.
You are correct that the biggest issue was that we couldn't diagnose problems when they occurred, and therefore we couldn't learn. Some of those problems were for sure "user error", others were absolutely problems with MongoDB at the time. It was a "young" database back in 2015.
Is there a good table charting the unique strengths and weaknesses of databases (or database types), so that people that are looking to build things but don't have the background could choose the right tool for the job?
If not, that may make a popular HN post!
Cassandra, CouchDB or Mongo? Redis? SQLite? Mysql/Maria/Percona or Postgres? Cockroach? Timescale or Influx? Why in the world do all these tools exist?
If not, that may make a popular HN post!
Cassandra, CouchDB or Mongo? Redis? SQLite? Mysql/Maria/Percona or Postgres? Cockroach? Timescale or Influx? Why in the world do all these tools exist?
What surprises me is that so far none shoehorned Postgres on top of FoundationDB.
I'm not familiar with FoundationDB. What benefit would that give Postgres?
It would become distributed DB on top of super solid foundation that powers snowflake, icloud etc.
I'm a designer and front-end developer. I'm building a fullstack project by myself in Node and I'm using MongoDB.
Can someone tell me why is it so bad?
I've searched for concrete answers but I got none for the moment yet from time to time people say these kind of things and I worry.
Can someone tell me why is it so bad?
I've searched for concrete answers but I got none for the moment yet from time to time people say these kind of things and I worry.
These days MongoDB is mostly a subset of what SQL servers do. If all you need is a document store you can simply make a table with a JSONB column and get that. If you later discover you do need relations you can do that with e.g. Postgres but not with MongoDB.
Historically MongoDB has been known for dropping or mangling data in some unfortunate ways, I'm not entirely sure what the story there is these days but personally I wouldn't trust it ever again. Data integrity is too important.
Historically MongoDB has been known for dropping or mangling data in some unfortunate ways, I'm not entirely sure what the story there is these days but personally I wouldn't trust it ever again. Data integrity is too important.
A single point for Mongo over Postgre JsonB is WiredTiger is very good at compression. In times of Mongo:4.0 vs Postgre:10.5 it was like 20Gb vs 50Gb. Though could depend on documents structure. Not sure about current state.
Querries were also somewhat faster in Mongo.
> A single point for Mongo over Postgre JsonB is WiredTiger is very good at compression
Granted, the compression story in postgres isn't currently amazing.
> Querries were also somewhat faster in Mongo.
If you really want fast queries it's not super hard to tune postgres to be fast and drop data if it crashes. It's a bit trickier to tune it to be quite that fast if you want to keep all your data, but for a single table of json documents it should be doable if you're okay with not having transactions.
Granted, the compression story in postgres isn't currently amazing.
> Querries were also somewhat faster in Mongo.
If you really want fast queries it's not super hard to tune postgres to be fast and drop data if it crashes. It's a bit trickier to tune it to be quite that fast if you want to keep all your data, but for a single table of json documents it should be doable if you're okay with not having transactions.
It's not that bad. I have used both and while I wouldn't choose MongoDB anymore, it did its job as needed.
For me, the main problem with MongoDB is that it is "schemaless" - which doesn't mean that there is no schema, just that it is not explicitly stated and enforced. This leads to problems later when schema changes and you need to be extra careful not to end up with inconsistent data (good luck with that). But this is solveable, it just takes more effort than necessary.
If you are using MongoDB for a small-ish project you should be ok. And if your project becomes big, you will need people who know the difference and will be able to migrate (if needed) anyway. So no worries, and good luck with your project! :)
For me, the main problem with MongoDB is that it is "schemaless" - which doesn't mean that there is no schema, just that it is not explicitly stated and enforced. This leads to problems later when schema changes and you need to be extra careful not to end up with inconsistent data (good luck with that). But this is solveable, it just takes more effort than necessary.
If you are using MongoDB for a small-ish project you should be ok. And if your project becomes big, you will need people who know the difference and will be able to migrate (if needed) anyway. So no worries, and good luck with your project! :)
If it fits your use case it's fine, the problem is most CRUD apps don't fit into the document store use case, namely storing and only operating on exactly one document per request.
It is not bad now, I would say now it is a good key value store, which is fine, a lot of data can be modeled this way.
But for relational data, PostgreSQL or MySQL will be a better choice (and I can't see this changing).
But for relational data, PostgreSQL or MySQL will be a better choice (and I can't see this changing).
In 2021 it isn’t bad anymore, but that’s a really low bar to set. You may find that using Postgres or MySQL is easier to maintain, faster to find performance issues, quicker to fix if it breaks, etc.
It's not bad, its just not the best.
If you are happy with your stack and it works for you that is all that matters.
However, you will have a lot more features available to you by using Postgres and tools like hasura to automate your api generation.
The migration from mongo to postgres isn't that bad at all, but if it ain't broke don't fix it comes to mind.
If you are happy with your stack and it works for you that is all that matters.
However, you will have a lot more features available to you by using Postgres and tools like hasura to automate your api generation.
The migration from mongo to postgres isn't that bad at all, but if it ain't broke don't fix it comes to mind.
As your data complexity and relationships grow, you will end up using SQL, just that it will be in your NodeJS function calls. (Aggregating, Joins, etc)
If you have highly un-related pieces of data, Mongo should be fine.
But the finer point is that you can store and retrieve JSON documents very efficiently with PostgreSQL too. So, as an architect, It makes sense for me to choose PgSQL, because down the line, I have features such as relational data available without having to switch-over to other databases.
If you have highly un-related pieces of data, Mongo should be fine.
But the finer point is that you can store and retrieve JSON documents very efficiently with PostgreSQL too. So, as an architect, It makes sense for me to choose PgSQL, because down the line, I have features such as relational data available without having to switch-over to other databases.
I still feel somewhat that Mongo is blamed for things that it is not supposed to do. Removing millions of records surely is not a usecase that Mongo is comfortable with. Or if you absolutely need a schema to protect developer doing silly things, then traditional relational database is surely way to go. Mongo is superbly quick to serve read-heavy applications. I did one project with Mongo & Sinatra and the app was able to reach high transaction/second rates compared to the hardware stack it was running on. Of course we faced issues to design trustworthy design for very high scaling. I think it is one culprit with Mongo, sharding and things. But anyway, we as developers and architects, just need to have right tools for the task we try to solve!
> Or if you absolutely need a schema to protect developer doing silly things, then traditional relational database is surely way to go.
Then you should use a relational database every time, because all developers "do silly things".
I've seen this same argument against strong types: I don't need them because I don't make mistakes.
You do. So does everyone else.
Then you should use a relational database every time, because all developers "do silly things".
I've seen this same argument against strong types: I don't need them because I don't make mistakes.
You do. So does everyone else.
These articles never get old. Hey we picked solution A and realized that it has some cons not only pros and now we are moving to solution B that only has pros. Few years later there is a new article, hey we are moving to solution A|C because...
I was one of the developers on the team that wrote that article. The main point of the article was (and still is) not that PostgreSQL is better than MongoDB, but that PostgreSQL suited us better at that point. In hindsight I think it suited us better because MongoDB was relatively new tech, PostgreSQL was more mature. Especially if it came to tooling and diagnosis on -what- is going wrong when things go wrong, as well as searchable help on the internet.
The extra maturity of PostgreSQL allowed us to learn faster and evolve our software faster with less surprises. As a small team in a start-up this was essential to us.
Considering other articles on that "picked a solution and didn't understand it well" - I do thing they are very valuable.
They provide an insight on how well frameworks and technology support a learning curve. It's not about having only Pro's. it's about how easy it is to get yourself unstuck if you engineered yourself in a corner where you don't want to be.
The extra maturity of PostgreSQL allowed us to learn faster and evolve our software faster with less surprises. As a small team in a start-up this was essential to us.
Considering other articles on that "picked a solution and didn't understand it well" - I do thing they are very valuable.
They provide an insight on how well frameworks and technology support a learning curve. It's not about having only Pro's. it's about how easy it is to get yourself unstuck if you engineered yourself in a corner where you don't want to be.
I've never seen an article about switching from Postgres to something else. Obviously Citus, Timescale, and other Postgres forks don't count.
Uber did one a while ago, but tl;dr they didn't really understand Postgres very well and were dealing with unique problems.
Uber did one a while ago, but tl;dr they didn't really understand Postgres very well and were dealing with unique problems.
I'm not sure they understood the problem they were trying to solve let alone understood Postgres.
I have seen Uber ping-ponging between Postgres and MySQL. My approach to this problem is, can we use Postgres? If the answer is yes (90% of the cases) we use Postgres. If the answer if no investigate what is good out there. For k:v I have used Riak with much satisfaction. For distributed databases we are experimenting with TiDB. It looks very promising so far.
There is only one problem with Postgres that I run into. Client connection scalability. This is why many users use a connection proxy in the front of it.
There is only one problem with Postgres that I run into. Client connection scalability. This is why many users use a connection proxy in the front of it.
I find them quite useful. Especially if I've never used technology A. After a while you start to see a pattern of people with similar problems to yours moving away from a certain tech. If it weren't for articles like these, we'd all be making the same silly mistakes over and over again.
Collective hindsight is a powerful thing and we should use it to our advantage to short-circuit certain problems.
If only politics worked that way :/
Collective hindsight is a powerful thing and we should use it to our advantage to short-circuit certain problems.
If only politics worked that way :/
And I wish they never get old. Every few years, new paradigms come to the forefront and debates like these certainly help many developers choose one way or the other.
In what paradigm are you referring to exactly with Postgres and Mongodb?
Relational turning 51 years old in 2021 while key-value stores category (which MongoDB falls into) is at least 40.
So in a sense this article is about which technology should we use, a 40 or a 50 years old one?
Relational turning 51 years old in 2021 while key-value stores category (which MongoDB falls into) is at least 40.
So in a sense this article is about which technology should we use, a 40 or a 50 years old one?
The paradigm of which type of database is best suited for large scale data, distributed in different locations.
MongoDB is not a key-value store. It's a document database.
"Document-oriented databases are inherently a subclass of the key-value store, another NoSQL database concept."
https://en.wikipedia.org/wiki/Document-oriented_database
https://en.wikipedia.org/wiki/Document-oriented_database
here is one from Last year where HSBC bank has moved all its 65 SQL databases into a single MongoDB.
https://diginomica.com/hsbc-moves-65-relational-databases-on...
https://diginomica.com/hsbc-moves-65-relational-databases-on...
I use MongoDB as a front-end that does full-stack prototypes from time to time in Node, and it's great using javascript from start to finish, you don't have to switch mental gears, so creating an MVP it's fast... but.
Sometimes I feel like I'm just avoiding SQL for no reason, not using any of the advantages of NoSQL, and only using Mongo as a SQL-wannabe with a shoehorn like mongoose.
I wonder how many MongoDB projects out there are just using Mongo because it's the trend (something like MEAN / MERN) and not because makes sense. Like there are out there a lot of SQL ORMs for node that, do the same job and better.
Don't get me wrong, I love MongoDB for a lot of weird stuff (like web scraping, CSV files, and archive data), but I feel a lot of the hate, it's because a lot of devs (myself included) have developed projects, that could be working better with a old fashion relational DB.
Sometimes I feel like I'm just avoiding SQL for no reason, not using any of the advantages of NoSQL, and only using Mongo as a SQL-wannabe with a shoehorn like mongoose.
I wonder how many MongoDB projects out there are just using Mongo because it's the trend (something like MEAN / MERN) and not because makes sense. Like there are out there a lot of SQL ORMs for node that, do the same job and better.
Don't get me wrong, I love MongoDB for a lot of weird stuff (like web scraping, CSV files, and archive data), but I feel a lot of the hate, it's because a lot of devs (myself included) have developed projects, that could be working better with a old fashion relational DB.
When you are creating MVPs time matters the most. You are anyway going to throw it away and build something better.
I was using Mongo and NodeJS a lot but then switched to Python. Now I find the world of ORMs to be onerous. I just want to define tables in MySQL and just run raw SQLs. The best compromise I have found so far. I mean it does involve boiler plate code but ultimately it's an MVP.
And you are right, most of MongoDBs uptick was because of the reasons you state. E2E Javascript and in addition the ability to query nested JSON natively.
I was using Mongo and NodeJS a lot but then switched to Python. Now I find the world of ORMs to be onerous. I just want to define tables in MySQL and just run raw SQLs. The best compromise I have found so far. I mean it does involve boiler plate code but ultimately it's an MVP.
And you are right, most of MongoDBs uptick was because of the reasons you state. E2E Javascript and in addition the ability to query nested JSON natively.
I also moved away from NoSQL for prototyping; as I recount in the sibling comment, I think the touted increase in dev speed is a myth, as the only penalties in dev speed for SQL come from (a) learning the tech and (b) thinking of your data model ahead of time, both fixed costs which dilute over time and eventually make you faster.
> Now I find the world of ORMs to be onerous
Funnily enough, I've also moved to Python, Django in particular. However, I'm having no problem whatsoever with the Django ORM and it's serving me extremely well for prototyping - I feel it is very easy to operate and that I can move at the same speed that I ever could with NoSQL after just a couple of months of practice.
Have you tried the Django ORM in particular? And what about the ORMs you've tried makes them onerous?
> Now I find the world of ORMs to be onerous
Funnily enough, I've also moved to Python, Django in particular. However, I'm having no problem whatsoever with the Django ORM and it's serving me extremely well for prototyping - I feel it is very easy to operate and that I can move at the same speed that I ever could with NoSQL after just a couple of months of practice.
Have you tried the Django ORM in particular? And what about the ORMs you've tried makes them onerous?
I have not tried the Django ORM. Most of my backend is REST services so I use FastAPI with SQLAlchemy. SQLAlchemy + pydantic is a bit onerous I feel.
The idea of "just dumping JavaScript objects in there" is one of the very pitfalls of the technology.
I also regularly used NoSQL (Mongo, Firebase) on the premise that I was prototyping and it was faster. I thus built an enterprise app which has grown within a large company, and there's barely a day I don't feel insecure about the NoSQL db powering it, because the underlying data model is actually relational.
What field did I use as a "foreign key"? Are these "foreign keys" which are replicated in several places in sync? Will a change in one of these fields break the entire application? What is even the schema, and how do I "migrate" it as requirements change?
These are all questions which I now ask myself regularly when maintaining and extending the app, and which clearly hinder my development speed. The data model is not even that complex - around four main entities - but on the other hand the application code has grown significantly, and the effects of technical debt are thus compounding.
So, in a nutshell, any speed I gained at the beginning was undone later on.
And in line with your reflection, we often conflate the dev speed of NoSQL with an inherent property of the technology, when in fact what's happening is that we're simply more familiar with it. I'm all for "build in what you know", but if you want to build anything semi-serious, you should probably know SQL.
Granted that SQL forces you to think more deeply about your abstract data model ahead of time and how that materializes into a concrete schema. But I don't think one should ever shy away from this exercise, as it is a fixed time investment that will pay huge dividends down the line.
In other words: SQL is slower in that you have to devote some time planning and designing your data model at the beginning of the project. This may be five, twenty or a hundred hours depending on the complexity of what you're building. But this is largely a fixed investment which will dilute and make you faster over time, as operations on data thereafter actually become much easier and not harder. Other reasons why this is a worthwhile investment is because it can be quite fun, and because I believe it generally makes you a better application developer: data design should precede the implementation of logic, no matter what you're building.
> Don't get me wrong, I love MongoDB for a lot of weird stuff (like web scraping, CSV files, and archive data
PostgreSQL (and possibly other RDB technologies) allow you to dump JSON objects in the DB too - and in the case of Postgres this a mature API as far as I know. So you can opt for a mixed data model where the clearcut parts (eg, users and ownership) are relational, while others (eg, results of scraping, or other ever-shifting unstructured data) can be schemaless.
In summary: if your data is relational, go for a relational database technology, even for prototypes.
I also regularly used NoSQL (Mongo, Firebase) on the premise that I was prototyping and it was faster. I thus built an enterprise app which has grown within a large company, and there's barely a day I don't feel insecure about the NoSQL db powering it, because the underlying data model is actually relational.
What field did I use as a "foreign key"? Are these "foreign keys" which are replicated in several places in sync? Will a change in one of these fields break the entire application? What is even the schema, and how do I "migrate" it as requirements change?
These are all questions which I now ask myself regularly when maintaining and extending the app, and which clearly hinder my development speed. The data model is not even that complex - around four main entities - but on the other hand the application code has grown significantly, and the effects of technical debt are thus compounding.
So, in a nutshell, any speed I gained at the beginning was undone later on.
And in line with your reflection, we often conflate the dev speed of NoSQL with an inherent property of the technology, when in fact what's happening is that we're simply more familiar with it. I'm all for "build in what you know", but if you want to build anything semi-serious, you should probably know SQL.
Granted that SQL forces you to think more deeply about your abstract data model ahead of time and how that materializes into a concrete schema. But I don't think one should ever shy away from this exercise, as it is a fixed time investment that will pay huge dividends down the line.
In other words: SQL is slower in that you have to devote some time planning and designing your data model at the beginning of the project. This may be five, twenty or a hundred hours depending on the complexity of what you're building. But this is largely a fixed investment which will dilute and make you faster over time, as operations on data thereafter actually become much easier and not harder. Other reasons why this is a worthwhile investment is because it can be quite fun, and because I believe it generally makes you a better application developer: data design should precede the implementation of logic, no matter what you're building.
> Don't get me wrong, I love MongoDB for a lot of weird stuff (like web scraping, CSV files, and archive data
PostgreSQL (and possibly other RDB technologies) allow you to dump JSON objects in the DB too - and in the case of Postgres this a mature API as far as I know. So you can opt for a mixed data model where the clearcut parts (eg, users and ownership) are relational, while others (eg, results of scraping, or other ever-shifting unstructured data) can be schemaless.
In summary: if your data is relational, go for a relational database technology, even for prototypes.
Just did some POCs with Hasura, a service on top of PG that provides:
* GraphQL interface (I think even a REST one in the latest release)
* authorization (role based)
* API gateway (put other API behind your GraphQL interface)
* a UI for managing the schema
* a way to do mirgations
Now GraphQL provides a schema (that includes the types of of the values), that can be used to generate clients libs in many languages. This effectively provides me a type safe interface + authorization to my db, while I have not programmed anything yet. Slowly SPAs start to make sense (if there barely is any self written server-side code running).
* GraphQL interface (I think even a REST one in the latest release)
* authorization (role based)
* API gateway (put other API behind your GraphQL interface)
* a UI for managing the schema
* a way to do mirgations
Now GraphQL provides a schema (that includes the types of of the values), that can be used to generate clients libs in many languages. This effectively provides me a type safe interface + authorization to my db, while I have not programmed anything yet. Slowly SPAs start to make sense (if there barely is any self written server-side code running).
I've been using hasura in production for about a year now and am very happy with it.
Little recommendation, use their typescript metadata sdk instead of the client ui if you want to be able to build your metadata as part of your ci pipeline.
Little recommendation, use their typescript metadata sdk instead of the client ui if you want to be able to build your metadata as part of your ci pipeline.
While Hasura is nice, it's too much blackbox, too much all-in, too much risk, we decided against it.
2021 is great because of TypeScript, types and decorators (just put here any other typed lang with decoraters if you do not like TS). If your db is your leading system for your schema, everything else is derived and "code-generated" with all the restrictions SQL has. Which is not great if you want to decorate and enhance. Yes, you could use interfaces but it's not the same, you can't decorate code-generated type definitions but your architecture wouldn't stay DRY either.
If you think TS' types as the leading schema you do not need a type checked db like Postgres anymore, anything which can store data works just fine you have all schema definitions at one place which is not the DB. Good examples enabling such an architecture are TypeORM and TypeGraphQL. However most stick to SQL and 'my schema must be in my DB, there is no other way to enforce a schema' (but there is!) you just need a good architecture (types and decorators!) and not another mainstream-from-the-shelf-solution.
2021 is great because of TypeScript, types and decorators (just put here any other typed lang with decoraters if you do not like TS). If your db is your leading system for your schema, everything else is derived and "code-generated" with all the restrictions SQL has. Which is not great if you want to decorate and enhance. Yes, you could use interfaces but it's not the same, you can't decorate code-generated type definitions but your architecture wouldn't stay DRY either.
If you think TS' types as the leading schema you do not need a type checked db like Postgres anymore, anything which can store data works just fine you have all schema definitions at one place which is not the DB. Good examples enabling such an architecture are TypeORM and TypeGraphQL. However most stick to SQL and 'my schema must be in my DB, there is no other way to enforce a schema' (but there is!) you just need a good architecture (types and decorators!) and not another mainstream-from-the-shelf-solution.
Interesting. My db and it's types have whole migration procedures to ensure all works on all environments, Hasura also does this.
How would that work with just-TS? The schema changes but the documents in my KV-store (I assume that what you mean by "no types on the storage") have not changed accordingly. How to prevent this obvious trouble?
How would that work with just-TS? The schema changes but the documents in my KV-store (I assume that what you mean by "no types on the storage") have not changed accordingly. How to prevent this obvious trouble?
Depends on the schema changes. If you (1) change/remove existing keys from existing types, you need as well a migration which runs before the app starts. If you (2) add new keys to existing types or add new types you don't need migrations. That's the beauty of NoSql or in particular Mongo which most forget, you just write to the db and Mongo creates key, collections and dbs on the fly if not there yet. But again, only do this if you have validations before in place. Otherwise you end up will all these people complaining without having a full understanding of type safety and where to deal with it.
Case 1 should be always prepared well or better avoided if possible. It doesn't matter if you have sql or nosql, they're just more complex and with a big production database some longer downtime is to be expected if the changes/migrations affect large parts.
Case 1 should be always prepared well or better avoided if possible. It doesn't matter if you have sql or nosql, they're just more complex and with a big production database some longer downtime is to be expected if the changes/migrations affect large parts.
Early in my career, I worked for a company that stored tons of data with Mongo, and their databases looked like poor man's SQL (lots of collections with foreign key relationships between them, etc.). Please don't do this, Mongo doesn't care about your imaginary SQL system actually working.
For most use cases, PostgreSQL will suffice. And I mean, you can solve a lot of problems with beefing the hardware and RAM.
For issues beyond that, you can exploit latest sharding features in it.
For issues, even beyond that, you can do for Cloud offerings.
For issues beyond that, you can exploit latest sharding features in it.
For issues, even beyond that, you can do for Cloud offerings.
There were many questions asking about recent experience with MongoDB. I have some recent experience so wanted to share with everyone.
My background:
2007-2010: Used MySQL to run a social network for 50M+ users. Would send 2B messages on peak days. All powered by Mysql.
2015-2020: Used Mongo to power a top 5k site. Zero downtime and dataloss in 5 years. Used Postgres for internal non user facing database
So I have familiarity with all of these databases at a decent scale. I am going to list pros and cons of each and why I would use. Again a tool is a tool. Just because I love a wrench, doesn't mean that I am going to use it instead of a hammer when I need to put some nails.
MongoDB:
They have come a long way since I started using them in 2012. If your use case is CRUD, you would be fine. There is still a lot of marketing fluff about sharding, multi document transactions etc which might not be fully reliable. I stay away from those things. But if you just use CRUD and you data can fit in a single db you are going to be fine at decent scale.
Following are some reasons I prefer Mongodb. It's possible that Postgres/MySQL has some of them even though I couldn't find them:
1. I don't like to manage schema migrations. During development you constantly add columns and it's a pain to make sure that this column is added to the sql databases on development, testing, staging, prod etc. With mongo, you just add a column and you are done.
2. No downtime on migrations: You can change you server type or mongo version with zero downtime. Did that since 6 years. Couldn't figure out how to do that with postgres (even with Aurora).
3. If you data structure fits document and subdocument schema, mongo is really easy to use. I tried jsonb with postgres and didn't find it as easy to use at mongo. If you data fits this paradigm, you can essentially get atomicity for multiple updates because they are all in a single document.
4. The admin interface of MongoDB Atlas is really development friendly. You can easily add read only replicas. You can easily add replicas in different regions. I shudder at the thought of managing Postgres myself and even Amazon Aurora is not as easy to admin as Atlas.
At this point I would consider Postgres only in the following case: If my data structure is like a non-tree graph. In that case I would expect to do multiple complex join and expect transaction consistency between tables.
If I am building a financial product which directly handles money or money instruments (bank, stock trading) I would definitely not use mongo for that.
Happy to answer followup questions.
My background:
2007-2010: Used MySQL to run a social network for 50M+ users. Would send 2B messages on peak days. All powered by Mysql.
2015-2020: Used Mongo to power a top 5k site. Zero downtime and dataloss in 5 years. Used Postgres for internal non user facing database
So I have familiarity with all of these databases at a decent scale. I am going to list pros and cons of each and why I would use. Again a tool is a tool. Just because I love a wrench, doesn't mean that I am going to use it instead of a hammer when I need to put some nails.
MongoDB:
They have come a long way since I started using them in 2012. If your use case is CRUD, you would be fine. There is still a lot of marketing fluff about sharding, multi document transactions etc which might not be fully reliable. I stay away from those things. But if you just use CRUD and you data can fit in a single db you are going to be fine at decent scale.
Following are some reasons I prefer Mongodb. It's possible that Postgres/MySQL has some of them even though I couldn't find them:
1. I don't like to manage schema migrations. During development you constantly add columns and it's a pain to make sure that this column is added to the sql databases on development, testing, staging, prod etc. With mongo, you just add a column and you are done.
2. No downtime on migrations: You can change you server type or mongo version with zero downtime. Did that since 6 years. Couldn't figure out how to do that with postgres (even with Aurora).
3. If you data structure fits document and subdocument schema, mongo is really easy to use. I tried jsonb with postgres and didn't find it as easy to use at mongo. If you data fits this paradigm, you can essentially get atomicity for multiple updates because they are all in a single document.
4. The admin interface of MongoDB Atlas is really development friendly. You can easily add read only replicas. You can easily add replicas in different regions. I shudder at the thought of managing Postgres myself and even Amazon Aurora is not as easy to admin as Atlas.
At this point I would consider Postgres only in the following case: If my data structure is like a non-tree graph. In that case I would expect to do multiple complex join and expect transaction consistency between tables.
If I am building a financial product which directly handles money or money instruments (bank, stock trading) I would definitely not use mongo for that.
Happy to answer followup questions.
> With mongo, you just add a column and you are done.
No, usually you are not done. Your data doesn't have the column. So either you write your code in a way that it can handle the column being optional or you write code to read each document and add the column manually.
I've started out with mongodb on Fit Analytics originally, until we noticed at some point that our data was an inconsistent mess and we'd implement constraints in our back-end code to make sure the data is consistent. Eventually it became just easier to have an SQL database taking care of all those constraints, and we luckily migrated away.
No, usually you are not done. Your data doesn't have the column. So either you write your code in a way that it can handle the column being optional or you write code to read each document and add the column manually.
I've started out with mongodb on Fit Analytics originally, until we noticed at some point that our data was an inconsistent mess and we'd implement constraints in our back-end code to make sure the data is consistent. Eventually it became just easier to have an SQL database taking care of all those constraints, and we luckily migrated away.
Thanks for raising this good point. I didn't get a chance to elaborate on this in my original post because it was getting too long and it was end of a long day.
The cost of adding a column is zero, in terms of schema migration. So that helps with developer velocity. We used an ORM (mongoose), so that we can have added constraints including default values. But Mongo did help us avoid the friction of adding columns.
The cost of adding a column is zero, in terms of schema migration. So that helps with developer velocity. We used an ORM (mongoose), so that we can have added constraints including default values. But Mongo did help us avoid the friction of adding columns.
We also used mongoose, which is awesome. In retrospect, I actually wish we had kept mongodb as a read-only database and used postgres as the source of truth. Our data was mostly read-only, with some rather complex relations behind the scenes.
For quick prototypes, I still prefer mongodb.
For quick prototypes, I still prefer mongodb.
> our data was an inconsistent
Because you didn't put strict validation before data entered Mongo and I guess you didn't use a typed lang which makes such a validation like a no-brainer and quite natural.
SQL gives you type checking for free, Mongo does not. However, if you have a typed lang and have a proper validation in place you just have to define those at one place and not in the database and in your app (DRY).
Because you didn't put strict validation before data entered Mongo and I guess you didn't use a typed lang which makes such a validation like a no-brainer and quite natural.
SQL gives you type checking for free, Mongo does not. However, if you have a typed lang and have a proper validation in place you just have to define those at one place and not in the database and in your app (DRY).
> Happy to answer followup questions.
This thread is like a piñata that gets pulled out every 6 months or so take a wack at MongoDB and talk about how everyone should just use PostgreSQL for every database problem no matter whether it's relational or not.
No-one cares about whether MongoDB is useful or not.
This thread is like a piñata that gets pulled out every 6 months or so take a wack at MongoDB and talk about how everyone should just use PostgreSQL for every database problem no matter whether it's relational or not.
No-one cares about whether MongoDB is useful or not.
I have been watching these threads forever. I personally like Mongo and wanted to added some real life perspective to it.
In the end it is a tool which fits some scenarios.
In the end it is a tool which fits some scenarios.
"This thread is like a piñata"
Cudos for having master class control of the word. My favorite punch bag are play stores, root of all evil.
A list of cycling subjects that split opinions about 50-50 in this news group would be hilarious.
Cudos for having master class control of the word. My favorite punch bag are play stores, root of all evil.
A list of cycling subjects that split opinions about 50-50 in this news group would be hilarious.
>You can change you server type or mongo version with zero downtime.
How so? All the documentation on upgrading say you need to shutdown each instance to then step up to the next minor level.
For instance 3.4 -> 3.6 -> 4.0 etc.
https://docs.mongodb.com/manual/release-notes/4.0-upgrade-st...
How so? All the documentation on upgrading say you need to shutdown each instance to then step up to the next minor level.
For instance 3.4 -> 3.6 -> 4.0 etc.
https://docs.mongodb.com/manual/release-notes/4.0-upgrade-st...
If you're using replica set, as you should for anything other than development (and even in development, sometimes you need a replica set for some features), you just sequentially upgrade each one. It goes down, clients figure out the new topology and continue working.
I'm also running on MongoDB, top 6k site and while I wouldn't say there weren't any issues, uptime is wonderful, I sleep well, and have zero problems with modeling the domain as a document collections instead of relational. Can easily imagine that it would not be great fit for many other use cases. Not using Atlas as GP, so it's more work, but I'm trying to be scrappy.
I'm also running on MongoDB, top 6k site and while I wouldn't say there weren't any issues, uptime is wonderful, I sleep well, and have zero problems with modeling the domain as a document collections instead of relational. Can easily imagine that it would not be great fit for many other use cases. Not using Atlas as GP, so it's more work, but I'm trying to be scrappy.
You are correct. This was in the case of replica set.
Are you running Mongo yourself. I use to be at MLab, but Mongo Atlas is really good. Give it a shot!
Are you running Mongo yourself. I use to be at MLab, but Mongo Atlas is really good. Give it a shot!
> During development you constantly add columns and it's a pain to make sure that this column is added to the sql databases on development, testing, staging, prod etc. With mongo, you just add a column and you are done.
I have found liquibase (https://www.liquibase.org/) to be extremely helpful for this.
I have found liquibase (https://www.liquibase.org/) to be extremely helpful for this.
Thanks, I will take a look at it. It looks really expensive though!
> If I am building a financial product which directly handles money or money instruments (bank, stock trading) I would definitely not use mongo for that.
My friend used to work for citi bank, his team managed high value transactions (in billions). I was _stunned_ to hear that they were storing all their data in MongoDB.
My friend used to work for citi bank, his team managed high value transactions (in billions). I was _stunned_ to hear that they were storing all their data in MongoDB.
I am not sure what is the exact use case. They could be using it for a non-critical data warehouse which could easily be recreated.
If you can deterministically determine your use case and are confident that mongo will fit it - it might be a good idea.
My concern which mongo is that is if you push the feature set, it will start falling apart at the seams.
Even though Postgres/MySql have some issues which can reduce the velocity of your team, if you absolutely want to be sure about data integrity and want full flexibility of evolving use case, they might serve better than mongo.
If you can deterministically determine your use case and are confident that mongo will fit it - it might be a good idea.
My concern which mongo is that is if you push the feature set, it will start falling apart at the seams.
Even though Postgres/MySql have some issues which can reduce the velocity of your team, if you absolutely want to be sure about data integrity and want full flexibility of evolving use case, they might serve better than mongo.
> I am not sure what is the exact use case. They could be using it for a non-critical data warehouse which could easily be recreated.
They decided to port the existing project to .NET and used MongoDB (just because the .NET team thought it's cool) to store _all the data_, I'm pretty sure of it. My friend was not part of the .NET team.
They decided to port the existing project to .NET and used MongoDB (just because the .NET team thought it's cool) to store _all the data_, I'm pretty sure of it. My friend was not part of the .NET team.
Citibank is a leading expert in losing your money, MongoDB is a perfect fit for their business model.
Why do you consider sharding to be marketing fluff? If your dataset was big enough wouldn't that be a requirement for your database?
I meant mongo support for sharing might be marketing fluff. As in they promise it works but it might be unreliable.
I have come to trust Mongo with CRUD on a document. Rest of the features might not work as well. Looks at the recent Jespen test for mongo ( https://jepsen.io/analyses/mongodb-4.2.6 )
I have come to trust Mongo with CRUD on a document. Rest of the features might not work as well. Looks at the recent Jespen test for mongo ( https://jepsen.io/analyses/mongodb-4.2.6 )
Is there a situation where I SHOULDN'T use Postgres?
Postgres is incredibly flexible and scales beyond what most projects will ever need. We use it both as a very large scale CMDB store for https://turbot.com and as an small embedded DB for https://steampipe.io.
Use cases where we hit road bumps earlier were:
1. High churn data. Postgres copies data for each rite update and then vacuums to clean the old versions. These use cases dominated our DB load and moving them out helped a lot.
2. Large JSON blobs. Postgres automatically compresses and will pull large (~>8kB) columns into a separate “toast” table. This makes sense but also means they are often a real bottleneck if you expect to work with larger JSON blobs as you core model. pg is great for JSON, but if you are using it at scale do pre-work and testing of this factor and how it plays with your model.
3. Of course, long term archive of data objects is much more cost efficient and scalable in an object store like S3.
Bet on Postgres. Whatever you use case it will work great until you are large enough to really understand your data and optimize for your business.
Use cases where we hit road bumps earlier were:
1. High churn data. Postgres copies data for each rite update and then vacuums to clean the old versions. These use cases dominated our DB load and moving them out helped a lot.
2. Large JSON blobs. Postgres automatically compresses and will pull large (~>8kB) columns into a separate “toast” table. This makes sense but also means they are often a real bottleneck if you expect to work with larger JSON blobs as you core model. pg is great for JSON, but if you are using it at scale do pre-work and testing of this factor and how it plays with your model.
3. Of course, long term archive of data objects is much more cost efficient and scalable in an object store like S3.
Bet on Postgres. Whatever you use case it will work great until you are large enough to really understand your data and optimize for your business.
Even in cases for regional replication?
Because this is the use case I am looking for, something like Spanner but cheaper.
Because this is the use case I am looking for, something like Spanner but cheaper.
Cross-region replication of data is always significantly harder, definitely consider if cross-AZ would be sufficient for your case (which it often is). Postgres can definitely be configured for a cross-region scenario, but the approach varies a lot depending on your recovery needs.
@dang, I found this in the guidelines: Please don't post shallow dismissals, especially of other people's work.
I see good critical comments here but also many shallow dismissals from people who just sneer at the work of all software engineers who created MongoDB. Why is such behavior tolerated in any Mongo thread and why do these people never face any consequences?
I see good critical comments here but also many shallow dismissals from people who just sneer at the work of all software engineers who created MongoDB. Why is such behavior tolerated in any Mongo thread and why do these people never face any consequences?
> Why is such behavior tolerated in any Mongo thread and why do these people never face any consequences?
I think you answered your own question: "guidelines."
Those that believe in this community, and want adherence to the guidelines will vote on submissions and comments so as to reflect that belief. Shallow dismissals tend to get voted down, and they may be refuted if someone is inspired to put the effort in to explain why such a shallow dismissal is either unwelcome or missing important information. Seeing as these are guidelines, there is no swift and absolute retribution for light infractions, when the community can largely self-correct such things.
I think you answered your own question: "guidelines."
Those that believe in this community, and want adherence to the guidelines will vote on submissions and comments so as to reflect that belief. Shallow dismissals tend to get voted down, and they may be refuted if someone is inspired to put the effort in to explain why such a shallow dismissal is either unwelcome or missing important information. Seeing as these are guidelines, there is no swift and absolute retribution for light infractions, when the community can largely self-correct such things.
That doesn't work here. You have to send an email to [email protected]
MongoDB does have support for schemas, but didn't in 2015
https://docs.mongodb.com/realm/mongodb/document-schemas/
https://docs.mongodb.com/realm/mongodb/document-schemas/
"I don't know what was wrong" articles like this come up really often with Mongo. My team manages thousands of Mongo nodes and we have issues, but few are directly due to Mongo. Mongo's documentation could be friendlier to the operator/maintainer, however the tools for debugging Mongo tend to be sufficient (metric counters, various status/state dumps, general Linux tooling).
You need to understand databases folks. There is no way around it. "Porting to Postgres" does not replace understanding of database internals and figuring out how to debug bad access patterns and explain why they are bad. Blindly migrating data seems like one of the worst things you could ever do - you're not fixing the problem that you don't understand how databases work. I get it - ain't nobody got time for that - but it will come back and bite you with your new shiny fancy tool too if you aren't careful
Anyone who brings up something like master-master hopefully realizes they are talking about a completely different problem now with completely different constraints. Expecting the same access pattern will perform just as well in master-master is wishful thinking - it requires careful design to avoid coordination problems. The meat of the story with databases is: coordination is expensive, distributed coordination is even more expensive
You need to understand databases folks. There is no way around it. "Porting to Postgres" does not replace understanding of database internals and figuring out how to debug bad access patterns and explain why they are bad. Blindly migrating data seems like one of the worst things you could ever do - you're not fixing the problem that you don't understand how databases work. I get it - ain't nobody got time for that - but it will come back and bite you with your new shiny fancy tool too if you aren't careful
Anyone who brings up something like master-master hopefully realizes they are talking about a completely different problem now with completely different constraints. Expecting the same access pattern will perform just as well in master-master is wishful thinking - it requires careful design to avoid coordination problems. The meat of the story with databases is: coordination is expensive, distributed coordination is even more expensive
What the article fails to mention is that you can use as a disk backed in memory database/cluster.
In a replicaset you can have e.g. 2 in memory nodes and 3 disk backed nodes.
Postgres doesn't have that. I wish it did. Mysql has in memory tables. Postgres doesn't.
If I'm wrong please correct me.
If I'm wrong please correct me.
I personally really like using Mongo for my side projects. Most of the time my side projects are just little utility things like link shorteners, paste bins etc. and I find it really easy to just make a basic schema and use something like mongoose to do all the queries for me. SQL is nice when i’m doing things that require a relational model, but in node.js I find it takes a lot more setup than mongo.
If you find yourself reaching for EAV because you have schema-less data (custom fields, for example), nosql can make your life so much easier, as long as you understand the trade-offs.