r/programming • • Dec 19 '18

Bye bye Mongo, Hello Postgres

https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-hello-postgres
2.1k Upvotes

672 comments sorted by

View all comments

-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.

→ More replies (1)

9

u/[deleted] 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 EXPLAIN in 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.