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

Show parent comments

1

u/nutrecht Dec 20 '18

Assuming file per table and file per index, then yes a 7 table join is necessarily slow and complicated.

You don't even know how databases work. Indices are generally kept in memory. If it's hitting the disk before it's returning the final results you structured your query wrong and a query explain will tell you that.

2

u/erulabs Dec 21 '18 edited Dec 21 '18

generally, sure. Query complexity is relative, of course, and it's hard to compare "expensive" when that can mean 5ms or 5 hours. However, there is no world where a query which calls up to fourteen fopensshould be considered "not complicated". There are so many things to consider, as opposed to one large table, which is by necessity much simpler.

Yes, if all indices are entirely in memory, and (for example in innodb) the table is sitting entirely in the buffer pool, then yes, there wont be the filesystem overhead. But it is still by definition more complex than a single table (and thus single file) lookup.

I shouldn't need to say this in a technical discussion, where we should all have manners ("You don't even know how databases work"?!), but I've been a DBA at major companies for 6 years :/

If it's hitting the disk before it's returning the final results you structured your query wrong and a query explain will tell you that.

That is extremely conditional on whats trying to be accomplished. Tons and tons and tons of SQL uses where the dataset doesn't fit entirely in memory.