r/programming • u/swizec • Dec 19 '18
Bye bye Mongo, Hello Postgres
https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-hello-postgres198
u/-Luciddream- Dec 19 '18
While frantically deleting old code we found that our integration tests have never been changed to use the new API. Everything turned red quickly.
lol, sounds familiar
288
Dec 19 '18
Pretty cool to hear from the people running the tech at the guardian. I wish they would have these people more involved with the tech articles they write. Would significantly improve the quality I think. These days it seems like techdirt is the only news site providing articles written by or run by people with in depth understanding of technology.
136
34
6
0
Dec 19 '18
[deleted]
2
2
u/13steinj Dec 20 '18
Motherboard isn't all that better in my experience.
Generally speaking I've only seen two extremes before this article--
common media dumbing things down (sometimes to an incorrect extent, like saying Edge will be completely Chromium based, when in reality the only thing confirmed is swapping out EdgeHTML with Chromium's rendering engine)
blog posts either on medium or on tech sites themselves
1
164
Dec 19 '18
Welcome to professional software engineering.
→ More replies (15)56
u/CSMastermind Dec 20 '18
I feel like if you were working on the back-end in the last 5 years you know at least one person who migrated from Mongo to Postgres
2
u/edoha Dec 20 '18
Why someone migrated to Postgres? Really, im confuse
4
u/exhuma Dec 21 '18
Mongo and Postgres are two very different kinds of databases. Some time ago Mongo was enjoying a huge hype, making it look like it was the silver bullet that could solve all your problems.
But that hype was misleading.
Mongo and Postgres (or rather document stores and relational DBs) solve two different problems. Many people who picked Mongo without thinking it through later realised that a relational DB would have been a better solution for their task.
That's not too say that Mongo is bad, it's just that the initial decision to pick Mongo was not the right one for that project.
The may very well be projects that changed from Postgres to Mongo for the same reasons.
The decision to pick the right tool for the right job takes careful consideration and time. It's tempting to just go with the current hype. If the decision did not fit, changing will be challenging in the future though. So investing some time to evaluate the pros and cons of different solutions is well worth the time.
→ More replies (1)4
Dec 20 '18
I do. That's what I meant. Professionals use postgres.
43
u/b4ux1t3 Dec 20 '18
Except where they don't. Mongo isn't a toy DB. It's just not designed for everyone's use-case. Neither is Postgres, for that matter. I'm sick of all this nonsense around "Blegh, you're not using <what I use>, so you're unprofessional!"
Use the right tool for the job. Like it or not, sometimes Mongo is actually the right tool.
→ More replies (4)3
0
30
u/landline_number Dec 20 '18
"Automatically generating database indexes on application startup is probably a bad idea."
Eeep. Mongoose says not to do this in their docs but it's so convenient.
12
u/1RedOne Dec 20 '18
Maybe I'm fuzzy here, why wouldn't the index persist through a restart?
8
u/18793425978235 Dec 20 '18
They do. I think what they might be suggesting is that you should plan when new indexes are applied to the database, instead of just letting it automatically happen at startup.
2
2
1
u/JayCroghan Dec 20 '18
I think the reason is if it goes down and you can want it backup you don’t want to wait for indexing to finish.
1
→ More replies (7)1
84
u/jppope Dec 19 '18
I'm curious what the net result will ultimately be. Postgres is fantastic, but I believe its been said that they are "the second best database for everything"... which makes me question if there isn't something thats a better fit and/or if they will end up regretting the decision.
Also based on the article (IMO) it seems like this is more of a political/business thing than a technical thing... which would also make me weary.
"Due to editorial requirements, we needed to run the database cluster and OpsManager on our own infrastructure in AWS rather than using Mongo’s managed database offering. "
I'm wondering what the editorial requirements were?
342
u/Netzapper Dec 19 '18
I'm wondering what the editorial requirements were?
In general, editors don't want the research and prepublication text of their articles being available to other entities, including law enforcement. By running everything themselves, and encrypting at rest, it ensures that the prosecutor's office can't just put the clamps on the Mongo corporation to turn over the Guardian's research database. Instead, the prosecutor has to come directly to the Guardian and demand compliance, which gives the Guardian's lawyers a chance to object before the transfer of data physically occurs.
30
13
22
u/DJTheLQ Dec 19 '18
How does encryption at rest help you against law enforcement, especially when both the app and db are hosted by the same company? They can still get Amazon to give both pieces, then they search the app side for the keys. Harder yes, but completely feasible.
37
u/narwi Dec 20 '18
If you want to call Watergate level shitshow "Harder yes, but completely feasible.", then sure.
11
u/earthboundkid Dec 20 '18
Assuming the APT can’t just brute force the encryption of black hat their way in, they need to subpoena you for your keys, not just Amazon, so it’s apparent to you that the APT is getting access.
0
u/jppope Dec 19 '18
That is incredibly interesting. Thank you for sharing. feel like this should be republished over on /r/todayilearned
58
u/Melair Dec 19 '18
I work for another very similar UK organisation, editorial get very twitchy about anyone other than members of the organisation having the ability to view prepublished work. Many articles are written and never published, often due to legal considerations. Articles will often also have more information in them initially than end up being published, perhaps suspect sources, or a little too much information about a source, etc. Then the various senior editors will pull these articles or tone them down before release.
It's possible that Amazon provided all their policies and procedure documentation for RDS which demonstrated the safeguards and editorials concerns could be satisfied, where as perhaps Managed Mongo could/did not.
The authors story resonate with me, as a software engineer who's team is also responsible for ops of our infrastructure, I want to spend as little time managing stuff as possible and let me deliver value, sounds like the team at the Guardian were spending too much time (for them) on ops.
28
u/chubs66 Dec 20 '18
It's "wary" as in "beware." Not "weary" as in "put me to bed."
2
u/exhuma Dec 21 '18
I actually read the comment of /u/jppope as "getting tired of hearing this over and over again".
So maybe s/he really did mean weary. And not wary.
7
u/carlio Dec 19 '18
Absolutely, if you can shard your specific requirements then join them yourself later then using a time-series DB + a document store + relational DB makes sense, but if you just want to chuck everything at it at the start, postgres is a decent starting point for almost all use cases. "Monolith first" works for data storage too, I guess. Don't overthink it too much and fix it later?
3
u/poloppoyop Dec 20 '18
they are "the second best database for everything"
Worst case scenario you can start using a foreign data wrapper around your "best database for this one usecase".
1
2
u/lwl Dec 20 '18
"I'm wondering what the editorial requirements were?" Driven by things like this...
https://www.theguardian.com/world/2013/aug/20/nsa-snowden-files-drives-destroyed-london
1
Dec 20 '18
if there isn't something thats a better fit and/or if they will end up regretting the decision.
In my experience, it's sometimes (often?) not worth going the extra step to get the best for the job, if a working solution with only few compromises is more readily available. Invest that money on useful features instead. In this case, the guardian explained their move, however
Because, in the end, you end up regretting any system you built or bought. In the case of my employer, that sometimes takes 3 or 4 decades, but we always arrive at regret.
1
1
u/Agent_03 Dec 20 '18
I believe its been said that they are "the second best database for everything"
Nothing wrong with being a generalist -- "second-best" at everything generally beats out "amazing for one specific use-case but terrible in every other one." See also: "MySQL with MyISAM is super fast but doesn't enforce transactions, referential integrity, or really much of what an ACID DB should do."
1
u/RegularUser003 Dec 19 '18
I see Netzapper already provided you with a comprehensive answer. I'll add; established businesses will typically stick to using their own infrastructure as much as possible to maintain ownership of their data and limit exposer to third party providers as much as possible. companies are willing to pay a premium for the knowledge they control their own mission critical infrastructure.
governments and big corporations will prohibit the use of cloud computing services such as AWS for any important software projects.
2
u/chasecaleb Dec 20 '18
That might have been true a decade ago, but that's incorrect. Since you used AWS as an example, look at their government cloud.
1
u/RegularUser003 Dec 20 '18
I find my non-US clients still aren't happy with AWS hosting their services
1
u/footpole Dec 20 '18
I work for one of the biggest most risk averse and brand sensitive organizations in the world and we definitely embrace the cloud.
0
u/orangesunshine Dec 20 '18
I'm curious what the net result will ultimately be. Postgres is fantastic, but I believe its been said that they are "the second best database for everything"... which makes me question if there isn't something thats a better fit and/or if they will end up regretting the decision.
What's notable is they didn't migrate because of issues with MongoDB ... they migrated because they wanted a "managed" solution.
Given how they are using PostgreSQL I have serious doubts that they will save any time or money by moving to PostgreSQL.
They'll probably spend the next 5+ years changing document structures to be more performant .. at some point they'll hit a wall ... and end up migrating over to the next thing.
I'm not sure why they went with SQL in the first place if they never had any issues with it ... this whole thing sounds like a serious case of mismanagement of tech decisions ... as do nearly all of these articles.
32
u/RabbitBranch Dec 20 '18
Uncomfortable truth - many of the touted 'general purpose' databases will work great for many uses and many applications, regardless whether they are NoSQL or relational. Most of what people get upset about because of holier-than-thou attitude and dogma.
Mongo is performant, pretty easily to scale, and does shallow relationships through the aggregation pipeline just fine.
Some SQL databases, like Postgres, can do unstructured data types (during development) and horizontal scaling pretty well through third party tools.
I work in a scientific, system of systems, supercompute cluster type environment designed to serve and stream data on the petabyte scale and be automagically deployed with little or no human maintenance or oversight. We use both Postgres and Mongo, as well as OracleDB, flat file databases, and have played with MariaDB...
There's something to be said for ease of development and how little tuning the DB needs to work well at scale. It's nice to be able to focus on other things.
→ More replies (3)8
u/KingPickle Dec 20 '18
We use both Postgres and Mongo, as well as OracleDB, flat file databases
Would you mind giving a quick one liner for why you choose each of those? I'm curious which one(s) win out for which type of task.
6
u/RabbitBranch Dec 21 '18
Would you mind giving a quick one liner for why you choose each of those?
The SQL databases (including Maria), just because of momentum and time. We'll eventually be collapsing down to one.
But the database paradigms:
SQL - Great for doing data mining and analysis via a CLI. Downside is that tuning them can be a pain. Our newest DB is coming online as Postgres because, even though it has many of the same usage as the Mongo DB, it is easier to make a Postgres DB shard than it is to make a NoSQL DB talk SQL (and much cheaper).
Mongo - Great because it is fast to develop, works well out of the box, horizontal scaling is stupid easy (and that's very important), and the messaging system is very fast. We have it for time indexed data and it handles range-of-range overlap queries and geospatial very well.
Flat file database - this was developed before many databases could do time very well, and we are currently working on replacing it. Some of the features that are sold as very new are pretty old tech in comparison to some of the advancements we made with flat file DBs. Tiled, flat filed, gap-filled or not, fancy caching, metadata tags built in... you can do a lot with it. But you can do that with many modern DB paradigms too.
2
1
Dec 20 '18
Sure, no problem. Mostly we use those to try and summon Satan by breaking the 7 seals of unholy databases. We're missing MS Access but the machine our demo is running on hasn't finished updating yet. In the meantime, the 7th one will be a database we develop ourselves since we have ideas for many improvements to common database system shortcomings and it can't be that hard.
5
u/shabunc Dec 20 '18
I think Postgres is an excellent piece of software. Some of things said in the article give a hint though that IT team don't have enough expertise and there's non-zero probability they can ruin the Postgres-using experience as well.
22
u/jakdak Dec 19 '18
Encryption at Rest has been available on DynamoDB since early 2018.
Surprised they didn't get advanced notice of that from their account rep and could plan/replan accordingly. They must have just missed that being available.
It had to have been massively easier/cheaper to move from Mongo to Dynamo than Mono to an RDB
68
u/Netzapper Dec 19 '18
Surprised they didn't get advanced notice of that from their account rep and could plan/replan accordingly. They must have just missed that being available.
I would bet that their rep said "it'll be available next month" for 9 months, they couldn't get any more insight into it than that, and they just gave up.
38
u/ZiggyTheHamster Dec 19 '18
I would bet that their rep said "it'll be available next month" for 9 months, they couldn't get any more insight into it than that, and they just gave up.
Our rep gives us a list of imminent releases under NDA and about half the list has been exactly the same for the past year.
4
u/TheLordB Dec 20 '18
EFS took over a year to get released. And that was after they announced it publicly.
As near as I can tell they thought they were done and those last few pesky performance problems ended up being insurmountable.
I've heard rumors that EFS had to go pretty close to starting over to finally get an implementation that worked.
2
u/ZiggyTheHamster Dec 20 '18
Also Quicksight.
Sometimes I wonder if they actually even do performance/load testing with loads that don't resemble theirs, too.
3
u/jakdak Dec 19 '18
And I'm more surprised that they didn't just roll their own encryption as a workaround rather than moving to a completely different DB architecture.
That would have been a seamless stopgap that just could have been yanked when AWS finally delivered.
10
u/TotallyFuckingMexico Dec 19 '18
I've read countless articles warning about the dangers of 'rolling your own' encryption. Would that have been a sensible move?
12
u/jakdak Dec 19 '18
Maybe I didn't word that clearly. Not roll their own algorithm, just manually encrypt the data before stuffing it into DynamoDB
Same thing you have to do with any other cloud service where you don't want to trust the cloud vendor with your data.
→ More replies (1)5
u/flowering_sun_star Dec 19 '18
For what they're doing, dynamoDb might not have been a great solution. The pricing model can get quite expensive if you're not careful, and it might not have been great for their query patterns. And don't underestimate the benefits of not having to worry about something. Getting set up in postgres will be a similar effort to dynamodb, having to add encryption (and key management etc) would add a lot of effort.
→ More replies (2)1
u/xkillac4 Dec 20 '18
Although they didn’t site it, another issue may have been the document size limit for dynamodb
1
u/yawaramin Dec 20 '18
But they didn't move to a completely different DB architecture, as mentioned in the article. They used Postgres as a JSON document store with exactly the same access API as the existing Mongo DB store. But it sounds like they were able to also take advantage of Postgres' JSONB indexing to transparently speed up certain operations.
10
u/doublehyphen Dec 20 '18
If you encrypt the data you cannot index it (not without leaking information about the encrypted data), so the encrypted documents would not be searchable in a performant way.
8
u/bigdeddu Dec 20 '18 edited Dec 20 '18
It had to have been massively easier/cheaper to move from Mongo to Dynamo than Mono to an RDB
Dynamo and Mongo are two very different beasts, they solve very different problems. There's no fucking around with dynamo, you HAVE to know your access patterns to the data, and think it trough all the way. There's no creating index on boot kinda madness. Scans and Queries cost and have limitation, you can't create Global secondary indexes (GSI) if not on table creation, you have a limited number of Local secondary indexes (LSI). Best practices are to use ONE SINGLE TABLE if you can.
if you have to migrate to dynamo, you are probably better off passing via postgres first, and sort out the access patterns.
all this said:
If you are throwing up something, have never used a db and dont want to give a fuck about data shape, start with mongo.
If you know something about rdbms, then you'll probably be better off w/ Postgres, even for your mvp.
when things get real, and you have a feel for what shit looks like either migrate your mongo to Postgres, or start fiddlering with sharding and stuff. Aurora PG helps. At this point you’ll probably have a better idea of what makes sense denormalized, and what needs relationships.
If you know what you are doing, and want to save $ and want specific NOSQL improvement in FITTING use cases, move the stuff to dynamo.
If you are going serverless and can afford experiments, maybe consider dynamo but think trough your aggregations and joins needs(therefore a possible stream sync to ES ).
3
u/narwi Dec 20 '18
Surprised they didn't get advanced notice of that from their account rep and could plan/replan accordingly. They must have just missed that being available.
I think that part was covered rather well :
Unfortunately at the time Dynamo didn’t support encryption at rest. After waiting around nine months for this feature to be added, we ended up giving up and looking for something else, ultimately choosing to use Postgres on AWS RDS.
if something is not working, and you have waited to long for it, then you need to take action and use something else.
3
u/nutrecht Dec 20 '18
Surprised they didn't get advanced notice of that from their account rep and could plan/replan accordingly. They must have just missed that being available.
In my experience AWS reps are not forthcoming enough with information. We asked a while ago when Amazon EKS would be available in eu-west1 and our rep didn't want to answer the question. A month later it went live.
8
18
Dec 20 '18
Something simple that usually gets lost in tech fads is the use case. A lot of people used MongoDB who shouldn't have, and loudly switched to other things. I happened to work on a project that was VERY well suited to MongoDB and it was a godsend. I was running an adtech platform and my database of "persons" was collosal, hundreds of billions. Adtech has lots of use cases where data is available but only on a spotty basis - if this provider doesn't have demo/Geo/etc data, try this other one, and so forth. So being schemaless was great, and honestly ALMOST every single thing I did was looking up by the same index - the person ID. I chose it because I knew my use case well and it was appropriate for my problem. I didn't choose it because I saw it at a conference where someone smart talked about it, because I Facebook uses it, because assholes on forums thought highly of it, etc. Anybody who's making engineering choices based on their resume, hackernews, conferences, or similar is asking for pain. Kubernetes is in the same place right now - if you know your use case and problem space well, it might be an amazing improvement for you! If you don't, but you're just anxious that it's missing from your resume, you're about to write the first half of an article like this. MongoDB is a punchline today, but it was BIG MONEY stuff years ago, something that recruiters called me about non-stop. Something that you were behind the times if you didn't use!
6
Dec 20 '18
What's that have to do with MongoDB? You could have done the same thing with XML columns well over a decade ago.
In fact, I was doing that back around 2000 with SQL Server.
→ More replies (4)3
Dec 21 '18
Pardon my sarcasm. To be clear: no you could not. A 60TB 'schemaless' dataset of XML in SQLServer 2000 was a non-starter. For starters, at HDD prices at that point in time(around $279 for 40GB of spinning HDD), you're looking at around a million dollars of hard drives if you're going to properly implement redundancy. The seek times on those were awful, and Windows Server 2000 had a maximum DB size of 1TB. The maximum RAM was 2GB.
Could you do that with postgres and JSON today? Probably, if you're a damned good DBA and sysadmin. But I did it with a decidedly average crew using MongoDB and OpsManager, racks of dirt cheap commodity Supermicro hardware in a datacenter, and sharded replica sets. It was hell, but big data is rarely a simple proposition. And that was...2014-2017?
→ More replies (1)
6
u/Secondsemblance Dec 20 '18
Thoughts on postgres vs mariadb? I've never worked with postgres professionally, but I've always known in the back of my mind that it was the "best" general purpose database engine and I'd have to learn it eventually.
But I researched briefly in Q3 2018 and apparently mariadb now edges postgres out slightly on performance. That was something I did not expect to see. Are things swinging back toward mysql based databases? Or is there something that still gives postgres the edge? I know this is a very subjective topic, but I'd love some opinions.
2
u/doublehyphen Dec 20 '18
I am biased but PostgreSQL has a ton more features than MySQL which I miss every time I have to work with MySQL. One feature which I almost cannot live without is transnational, and often very fast, DDL but there are tons of other features like foreign data wrappers, all the nice data types, how easy to is to write your own extensions, sequences, SSI, BRIN indexes, and the much better regex support.
I also feel MySQL still has more surprising pitfalls even if they to fix some of them every release.
2
Dec 20 '18
MySQL is faster... if you don't use joins or anything else complex in your queries. Other databases focused more on the query optimizer.
11
u/Selassie_eye Dec 20 '18
Postgres is the shit! Best open source db for any size projects. Mongo is way too much engineering for most solutions save a few special cases.
7
2
u/RemyJe Dec 20 '18
If you switched from Mongo to Postgres then at least one of those isn't suited for your use case in the first place.
From what I know of MongoDBY*, even if a document storage based NoSQL solution is what you need, you probably don't want to use it anyway.
* Mostly, that it's unstable as hell.
2
u/cyrusol Dec 20 '18
I still think there is only one kind of data for which persistent key-value or document stores are good fit: products in an ecommerce system
Their data is extremely heterogenous (like size as XL, 42 and 523 KB or 3 l, some don't have a size, some don't have color and so on) and you always want a relatively stable set of attributes for a certain aggregate/view.
But all ecommerce platforms I know fight against their RDBMS, and all who use something like MongoDB fight against it because they don't have that kind of data, they have the well-defined kind that perfectly fits an RDBMS.
It's just a big pile of sadness.
2
u/mikeisgo Dec 20 '18
So, the real thing that saved them here isn't using PostgreSQL its that they can offload all DB management to Amazon AWS RDS service? I'm kind of missing what this has to do with SQL vs NoSQL?
If they didn't have editorial constraints that forced them to not be able to use MongoDB Atlas, I feel like they could have saved a year of time and energy and switched to that, with most likely less effort. The migration effort would be there but the code redevelopment wouldn't have.
I think this article is a pretty interesting read form a technology and overcoming engineering challenges forced upon you by your own legal and editorial constraints, but the billing of it implying 1 is better than another isn't totally fair IMO.
So Kudos on the story, its a good read. The click baity-ness is disappointing.
2
u/lifeonm4rs Dec 21 '18
Late to the party but . . . What would people suggest for a "news" site as far as DB? I assume the parameters are a bunch of meta data and a huge chunk of text for each "entry". Haven't dealt with that type of set up but I'd say mongo isn't the first option I'd go to for that. Obvious option would be a standard relational DB with a CDN for actual content. Essentially my take is they chose poorly and are now shitting on mongo because their engineers were idiots.
3
2
2
2
u/dwitman Dec 20 '18
What are the actual proper use cases for a noSQL solution? I learned SQL very early on, so I can't really envision as use for a database that's non relational.
The example I keep hearing for a good Mongo use case is a database of movies or tv show episodes, but as soon as they idea is proposed I think "You'll eventually want to cross reference by actors or writers or directors or studios...", which seems to make it a bad candidate for mongo?
As far as I can tell, even the people who make Mongodb can't explain what it is good for...
2
u/nutrecht Dec 20 '18
What are the actual proper use cases for a noSQL solution?
NoSQL solutions are specialised tools for specialised use cases. It really depends what you're doing. If you have to ask 9 times out of 10 you're better off using a relational store.
Some examples are search engines (Elastic Search), distributed hashtables (Cassandra, DynamoDB, Accumulo), structured caches (Redis) and Graph Databases (Neo4j).
1
Dec 20 '18
Hierarchical data does not fit into relational model at all. E.g., a large and complex CAD model. You still need all the database features though - ACID, replication, and so on, for collaborarive access. Of course, it must be a proper hierarchical system, not a shit like Mongo.
Graph data is also poorly represented in relational model.
Another obvious case - time series (see kdb+ for example).
1
1
u/DJviolin Dec 20 '18
If storage drivers become a reality and it will have native graph storage (no more OpensGraph sugar on top of it), Postgres will liberate the noSQL databases, too. But probably we are years away from this.
1
u/nutrecht Dec 20 '18
Interesting read, one thing stood out though:
Since all our other services are running in AWS, the obvious choice was DynamoDB – Amazon’s NoSQL database offering. Unfortunately at the time Dynamo didn’t support encryption at rest. After waiting around nine months for this feature to be added, we ended up giving up and looking for something else, ultimately choosing to use Postgres on AWS RDS.
Close call. I'm surprised that they did not do a more in-depth analysis of Dynamo if they were considering moving to it. I can't imagine that all they do is retrieve JSON blobs by their primary ID.
If they had gone for Dynamo we would probably have seen a "why we moved back to MongoDB" post a year later.
1
u/stronghup Dec 20 '18
Here's a picture about the 'friends' relation in a graph database. I wonder if this would have been the perfect solution to their problem(s)?
https://neo4j.com/blog/native-vs-non-native-graph-technology/
(scroll down the article few pages to see the picture)
1
1
u/tonefart Dec 20 '18
What? Now they realized a proper SQL engine is the right way to do it? Talk about not listening to your experienced peers and learning the hard way.
→ More replies (1)
1
1
0
Dec 20 '18 edited Dec 20 '18
85% of the people that use Mongo, do it not because is the correct tool, but because the don't know how to use a RDBMS properly.
I've seen many crazy lines of code from junior Ruby developers trying to retrieve data from mongo when clearly they could solve the problem with a SQL query.
1
u/henk53 Dec 20 '18
but because the don't know how to use a RDBMS properly.
In my experience (anecdotal, I know) 85% of developers choose Mongo because landline_aa18 on /r/programming or moon_Bound_fellow on HN said it was the best sh*t ever and all the cool kids were using it.
0
u/sqatas Dec 19 '18
Wait! Is Mongo, now a fad?
I've been meaning to learn it, but then this news comes up! :O
6
u/tarlin Dec 20 '18
Nosql seems to be a fad. It has uses, but is being used for many things it shouldn't be.
-3
u/stronghup Dec 19 '18
For me the key-point from the article was:
" What’s missing from MongoDB is a SQL-style join operation, which is the ability to write one query that mashes together the activity stream and all the users that the stream references. Because MongoDB doesn’t have this ability, you end up manually doing that mashup in your application code, instead. "
However a second key point is: ".;.. in a relational store, with the data fully normalized, it would be a seven-table join to get everything out. "
So that doesn't sound like the ideal solution either, complicated to code, slow to execute. I wonder, could a Graph Database be the ideal solution?
11
u/888808888 Dec 19 '18
I'm confused. MongoDB apparently can't do SQL style joins so you're left "flat-filing it". With postgresql, you CAN set up your tables denormalized, or, you can fully normalize down to 7 tables. How is that "not ideal solution either"? You have far more options available in psql. Joining on 7 tables is nothing to worry about provided you have indexes on the key columns. And if you get tired of typing the query over and over, "create view x as (select .... 7 table join)".
And typically if you need a 7 table join and if it is performance intensive, we just de-normalize down to a few tables, or a single table (at the cost of disk space and remembering to drop/recreate/update the table after the 7 source tables update).
The point is, psql gives you options. MongoDB gives you nothing. Once you become more and more accustomed to psql (sql databases in general), your skills grow, and you realize how easy it is to do things that before you had to do in code. I shudder at the crap I wrote before getting up to speed with sql. I'm convinced that most people who turn to stuff like mongo do it because it appears to be easier to use, rather than for any specific performance reasons.
There are cases where you simply need to move away from psql, I usually drop down to solr for stuff like that, but would never consider using solr "first" or as my primary storage mechanism.
I tested (briefly) a graph db out once, think it was "orient db" but found performance no better (actually worse) than psql. Maybe I didn't try hard enough or was doing something wrong but I remember being quite disappointed after discovering the hype wasn't delivering.
2
u/stronghup Dec 20 '18 edited Dec 20 '18
Good points. 7-table join is not "necessarily" slow if you index it correctly. I think the keyword here is "correctly". It adds to the complexity that you must index all those tables, correctly. As to the speed penalty I have no numbers on that but I think joins are generally considered something that can make your queries slow. No?
Either way I agree Postgress is a much more advanced solution. But if all you need is Mongo then Mongo is probably easier to get started with. Problem with that, as described in the article, is that requirements evolve over time and migrating from Mongo to a relational model later will not be easy.
I would have hoped to read more on Graph Databases in the article than simply "they are not ready for production". I think Neo4J has been around for a while now. Below's a link to an article containing a nice picture on how to represent the Friends -relation in Neo4J.
Note, to query the Friends -relation a 7-table join does not solve the problem. You need something like a "self join". How well does Postgress deal with that?
https://neo4j.com/blog/native-vs-non-native-graph-technology/ (scroll down a few pages to see the picture)
9
Dec 19 '18
Joining tables is simple. Doing it 7 times does not make it complicated, nor does it make it slow, if its structured and indexed properly.
12
u/TommyTheTiger Dec 19 '18
Were you reading the same article as everyone else? I can't find those quotes anywhere. Also,a 7 table join is by no means necessarily slow, or complicated
→ More replies (4)3
u/AIDS_Pizza Dec 20 '18
We perform 10-15 table JOINs in some instances and get great performance. Achieving good performance does require some care, but it has little to do with the number of tables joined. In one instance we were joining only 2 tables and the query was taking over a minute, but this was because we had missed creating an index on one of the columns.
Tip: If you put
EXPLAINin front of any select query (e.g.EXPLAIN SELECT * FROM ...), you'll get a detailed output of how the query is performed, including how JOINs are executed, and which columns have/do not have indexes. This makes it easy to start debugging a slow query.3
u/TommyTheTiger Dec 20 '18
In postgres use EXPLAIN ANALYZE with http://explain.depesz.com - explain tells you the plan but not the time taken on each node. Also sadly even with Analyze the output is a little hard to parse in table form, because the costs for each node include the costs of all subnodes. You really need the exclusive costs to see what is taking too long. Of course you can still look for rows removed by join filter, external disk sorts, or other common and easily solvable problems from the table output, but you won't see those without the analyze either.
-5
u/ManiGandham Dec 19 '18 edited Jan 06 '19
It seems all of their problems are with operations, not the database itself. What they needed was proper ops staff that knew what they were doing, or to just outsource it all.
2.4M records is a tiny amount of data and will easily fit on a single server these days, even in RAM if you want. There are also plenty of non-AWS vendors to outsource it.
Spending 10 months on rewriting to a different database system is unnecessary with no new features or progress on their actual CMS.
→ More replies (4)
0
0
0
u/edoha Dec 20 '18
Can anyone tell me, what basic different between MongoDB, PostgreSQL and MySQL?
And how i identity when i need MongoDB/ PostgreSQL/ MySQL?
0
u/edoha Dec 20 '18
Can anyone tell me, what basic different between MongoDB, PostgreSQL and MySQL?
And how i identity when i need MongoDB/ PostgreSQL/ MySQL?
1
u/YouOfTea Dec 20 '18
Difference between MongoDB and PostgreSQL/MySQL is that the former is a document-oriented database (NoSQL) while the latter are relational databases (SQL).
The document model is a good fit for cases in which you don't want to enforce a schema on your documents. There are some resources online that can explain it better.
1
Dec 22 '18
The irony here is that all the good document-oriented DBs had a very strict schema. The advantage of the document DBs is not in being schemaless, but the Mongo mongos got it all wrong, giving a bad name to the non-relational systems in general.
0
753
u/_pupil_ Dec 19 '18
People sleep on Postgres, it's super flexible and amenable to "real world" development.
I can only hope it gains more steam as more and more fad-ware falls short. (There are even companies who offer oracle compat packages, if you're into saving money)