Hacker News Reader

Comment by keithnz | Hacker News Reader

I think NoSql, especially things like Mongo, got popular because it is super easy to program with javascript. While scaling is one of the advantages, I'd be super surprised if many people actually need scaling capabilities ( other than because their design is super inefficient ).

Recently I've been inspired to play around with kicking out as many layers between a relational db and a REST Api, largely because I've been watching https://www.twitch.tv/nybblesio ( who I first came across here on HN on a thread about live coding via twitch streaming ). One of his projects he works on is a SASS product which is largely done in postgresql leveraging https://github.com/PostgREST/postgrest . Seems pretty good, going to be interesting to see how it turns out ( though I haven't seen him work on it lately )

I tend to like SQL Server, ASP.NET Core and found https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018... and I've been playing around with this kind of idea and using a lightweight mapper when needed ( https://github.com/StackExchange/Dapper which the stackoverflow people created )

I gotta say, I kind of like it

One of the problems of many stacks is that the frameworks wrap general purpose languages over SQL, which, is not really a good idea, SQL is a vastly more capable language for dealing with relational data and layers built over the top often dumb down the database.

The REAL problem SQL wrappers need to solve is that most languages don't have a good interface with SQL. So most often SQL is handled in strings and manipulated with string manipulation and there is no type safe way transitioning data from the DB to a general purpose language.

Microsoft at one stage had Linq to SQL which was quite good..... but they killed it :)

View on HN

> I think NoSql, especially things like Mongo, got popular because it is super easy to program with javascript.

NoSQL caught on because of what it doesn't do: attempt to implement the relational model.

A lot of people were using MySQL in infamous LAMP stacks to set up websites with virtually no business logic.

So you had:

1. An application that didn't need the relational model 2. A DBMS that had a very poor implementation of SQL, which itself is a poor implementation of the relational model 3. A large community who didn't know anything about database theory.

When you're using MySQL as a glorified hashtable, you get none of the benefits of the relational model, and all the downsides.

From their understanding of the technology, throwing all that out made a lot of sense. It's pretty reasonable to use a distributed hashtable if what you want is a distributed hashtable.

I think the backlash now is that NoSQL marketed themselves as a capable alternative to SQL DBMSs. But they had no query optimizer, no transaction support, no reliable backups, and no schemas. The last was supposedly a selling point, but the reality is that your data always has a schema, and code will crash if the data doesn't match it.

Great marketing, but people were sold half a car and then told building the rest themselves was a feature.

> One of the problems of many stacks is that the frameworks wrap general purpose languages over SQL, which, is not really a good idea, SQL is a vastly more capable language for dealing with relational data and layers built over the top often dumb down the database.

Another problem is that object-orientation is fairly broken, and ORMs tried to figure out how to shoehorn relational databases into inheritance hierarchies. If current notions like value classes had been around, a more straightforward mapping would have been possible.

MongoDB certainly was missing a lot of things when they first launched and were very hyped, but now pretty much everything you could want in a database is now in MongoDB.

Multi-document transactions + strong consistency: https://docs.mongodb.com/manual/core/write-operations-atomic...

Passes Jepsen test: https://www.mongodb.com/mongodb-3.4-passes-jepsen-test

Schema: https://docs.mongodb.com/manual/core/schema-validation/

Query Optimizer: https://docs.mongodb.com/manual/core/query-plans/

Backups: https://docs.mongodb.com/manual/core/backups/

Thanks for posting that; I haven't had a good reason to look at it in quite a while. It makes sense that they're adding those features as they all exist in SQL DBMS's because they're a need.

Do they have a scale/redundancy option for write scaling beyond shard + mirror set? I think my bigger issue with mongo, is you really need 6+ servers for a production deployment in many scenarios. It's great for getting a single developer instance up and running. But if I'm sticking to one instance or a couple mirrors for production, I may just reach for ms-sql or postgresql.

Aside: I'm sad that RethinkDB didn't get more of a foothold.

MongoDB supports a combination of shards and replica sets. Each replica set uses a protocol similar to raft for strong consistency. The replicas are a lot better than just active-secondary mirroring

https://docs.mongodb.com/manual/replication/

There's also Atlas which is the hosted version and makes it very easy to scale up https://www.mongodb.com/cloud/atlas

They also just added a free tier if you want to play with that https://docs.mongodb.com/manual/tutorial/atlas-free-tier-set...

Yeah, sorry for using mirrored instead of replica. I've worked with 3+ replica sets where I needed read scaling, and that worked well enough. But there are cases where I want read scaling and don't want to have a full copy of all data on all servers, but do want redundancy.

I really like, by comparison, how RethinkDB, Cassandra and others do it with clustering that has sharding + replication. It seems to be a better model in a lot of ways when you want to spread things out (read/write) but also want some replication of data. It's just a different way of working with things.

Thanks for pointing out Atlas.

This is exactly how I feel.

I am currently working on a project for a client where they expect to have about 3,000 users each with some basic profile information, and the ability to upload some documents etc - nothing too crazy. I don't think they ever have to worry about scaling to 50,000 users and performing any data-intensive database queries. Why not use something like Mongo in this situation?

Because your data is probably relational and data being consistent is probably beneficial?

NoSQL caught on because our industry is like the fashion industry and we need to follow the fads year after year. Plus a lot of FUD about relational databases not scaling (it's true that NoSQL will scale certain problems way better than relational, but the vast majority of people were/are not writing applications at that scale).

> The REAL problem SQL wrappers need to solve is that most languages don't have a good interface with SQL. So most often SQL is handled in strings and manipulated with string manipulation and there is no type safe way transitioning data from the DB to a general purpose language.

For me this is (was) the main problem. When building Android apps and there was need to store some data locally, I always used Realm (https://realm.io/blog/realm-for-android/) because their API was way easier to use, other than the default SQLite database. Of course this made the apk significantly big, but at least i knew how to manage the data. SQLite on the other hand, wasn't easy to work with, and worse when it came to versioning / updating the local database schema / definition without messing things.

I say (was) because late last year, I started using Room (https://developer.android.com/topic/libraries/architecture/r...), and working with SQLite on Android has never been way easier. So yes, this has been a problem, but the solutions if built well, are great.

> it is super easy to program with javascript.

It would be more honest to say that the weird js design, its lack of features and ridiculously small stdlib made it harder to do anything complicated at the time (no ES6 or webpack, remember ?).

Hence, not good tooling existed for relational databases: no decent abstraction layer, migration lib or ORM. Lost of languages had a 2 liner to pop an sqlite db without needing to install anything, but JS didn't even have that.

So here you are, with thousands of front end devs that were just learning to declare their variable correctly, coming to the server. They had the choice between a technology that needed a lot of boiler plate to get anything done, and provided a painful experience with many early errors.

Or something that just seemed to work out of the box. You just dump your thoughts in it. You don't need to think. No error back. It just says "ok".

Seemed is, however, important here. The first years of mongo, it failed miserably, but you only saw it down the road of the projects using it. And of course the inexperience of the css wizards using it as a "dump-my-data" store prove to be a terrible curse.

At the time, if you said any of those things, people would have called you an old grumpy looser that didn't want to live in the future.

But they learned that, just like "var" was important to avoid bugs instead of using your variable whenever you felt like it, deciding of a formal schema was, even for document data store, an important thing to do.

Fast forward, the JS community has now matured and include modern constructs, a rich ecosystem and people with way more experience. Mongo has changed its design and has now sweet spots use cases.

But we still don't have good tooling for relational DB in JS.

And it's still important to have an explicit document stating which schema you use for your document store. Schema less means you can do whatever you want. It doesn't mean, however, that you should.

True that Node.JS didn't have, and still doesn't have a 2 liner to pop an sqlite db. But what it did have(a few years after its launch) was a pure-JS sqlite client which can leverage V8's event loop and libuv to make parallel execution of db-queries a trivial operation.

There is a lot of mettle in Node.js for server side programming.

PS: have you looked at TypeORM(http://typeorm.io/)

NodeJS event loop is fantastic.

However, it's not that relevant for most db workload. We had the same debate in Python: do we async our db libs or keep using threads ?

Experts say: http://techspot.zzzeek.org/2015/02/15/asynchronous-python-an...

And this is despite the way the Python community adopted libuv with the uvloop lib.

TypeORM does look super nice, thanks. Does it handle well introspection ?

> Microsoft at one stage had Linq to SQL which was quite good..... but they killed it :)

I think that might be like when rock stars die at 26 instead of hanging around long enough to become the subject of a VH1 "Where are they Now?" episode.

I was on a project that used LINQ to SQL long after Microsoft stopped development on it, and consequently fell out of love with it. The caching mechanism was flaky, and could cause really weird problems that manifested in non-obvious ways. Also, changing the database schema in a LINQ to SQL project could be a chore.

I ended up migrating us away from it and toward Dapper. We ended up bypassing it way too often to be able to straight-facedly claim it was saving us from having to understand SQL (if that was ever possible), It was the warts, but even more than that, it was the many situations where, to get the query right, we needed to use a recursive CTE, or a temp table, or a table-valued parameter, or any of the oodles of other things that are awkward or impossible with an ORM.

Dapper is great IMHO. However, the Microsoft-recommended solution after Linq to SQL ended up being Entity Framework... which was an even heavier ORM.

My personal opinion is akin to the parent: the more you abstract away from thinking like your data store, the easier it is to write poorly performing code, because some of the fundamentals get lost in the process.

For instance, in EF and other ORMs, one classic pitfall is "N+1" queries. Even though it's a well known issue to avoid, I still run into poorly performing loops quite often. Here, I feel the abstraction is partially responsible. Most for loops run in code (in memory at least) very quickly, even against a lot of records. However, those who develop against SQL know that you avoid cursors etc. as much as possible, and that for loops / cursors against a SQL database will bite you the instance you are dealing with thousands of records to loop through. I don't think it's always "obvious" to, say, a relatively new C# developer with less experience writing SQL, that this for loop against a SQL database is going to literally translate into thousands of select statements that will perform poorly.

I think it's possible to use a heavy ORM like Entity Framework and write well performing code, but there has to be some awareness of when the task is too complex for the ORM to perform well, and you need to switch to a stored procedure. And there has to be at least a little bit of understanding on how to write well performing interactions with the DB.

From a performance perspective, N+1 queries are spectacular, but the much more pervasive problem is simply that they encourage poor discipline about hitting the database.

Most ORM code bases I've looked at do a pretty decent job with avoiding N+1 queries. But they've all been guilty of doing things like incurring orders of magnitude more network transfer than is necessary by pulling down a collection of objects just to calculate the sum over a single field.

I think that stuff ends up being so common because it's exactly what heavyweight ORMs are encouraging you to do. To an approximation, their entire point is to make it easy to forget that you're talking to a remote resource.

And, for that matter, by hiding the subtlety of the relational model, they also hide the power of the relational model.

This just comes down to caring about the work you do and putting in the effort to ensure you are delivering quality/performant code. If your devs can't handle that then you have a lot more problems than a heavyweight ORM. No developer worth their salt would be "encouraged" by a library to write bad code.

> kicking out as many layers between a relational db and a REST Api

Cool, is there anything we can see? I pretty much stumbled upon something along those lines back in 2003, and developed it into what I now call "In-Process REST"[1][2] and "Polymorphic Identifiers"[3][4].

The idea being that you can stay within a "storage-oriented vocabulary" for as much as possible, and that can include seamlessly all the way from provided interface to various storage mechanisms.

What's neat is that it reduces friction a lot, greatly enhances composability and reusability and just makes things blatantly obvious.

[1] https://link.springer.com/chapter/10.1007/978-1-4614-9299-3_...

[2] https://github.com/mpw/SoftArchLon2016-In-Process-REST/blob/...

[3] https://www.hpi.uni-potsdam.de/hirschfeld/publications/media...

[4] http://objective.st/URIs/

Not only easy in Javascript, it is easy in any language that has easy serliazation to JSON. I always use it for my own hobby projects because I dont have that much SQL knowledge. Which makes Nosql easy choice for me, just serialize your object into json and push it to your DB.

That's not a choice. To make a choice you need to be aware of alternatives. You use NOSQL because it was easy to achieve your goal.

Dont mean to troll. But how choosing for the easiest way to achieve your goal not a choice?

That's a different choice.

You might be "choosing" not to bother understanding the alternatives, but you're not making a choice between the alternatives because you don't know what they are.

Pushing a serialized object into a distributed hash definitely solves some problems.

And you have finite time, so if the choice is between that solution that you can do now and searching for another solution that may or may not be better, it's often reasonable to go with the known solution. In economics, this is known as "rational ignorance."

The value proposition of learning will change, though. You will find it's worth it, especially if you move beyond hobby projects and have to deal with any kind of business logic, to spend some time learning how databases work, and maybe even the math behind them, the relational model.

There are a lot of mediocre or awful references out there. If you need a good text on the subject, I recommend CJ Date's "Introduction to Database Systems." Note, though, I said good, not easy.

This is similar to the problem of macro programming by eval'ing a string (in dynamic languages). Basically you are trying to construct a tree (AST) by glueing together linear strings of text (instead of working natively with trees)

Linq to SQL is still a thing, you may need to click it’s package when you install visual studio 2017+, but it’s still there and it’s still the best SQL interface they have build in my opinion.

We rarely use anything else, but I can see the value of entity if you’re changing your DB a lot. We typically don’t do that though, and to be honest, working with changes with linq to sql, typically goes smoother than entity migrations.

Disclaimer: it’s entirely possible that we simply suck at entity.

LINQ to SQL is a dead end. Look into LINQ to DB https://github.com/linq2db/linq2db which is way faster than LINQ to SQL ever was (faster than even Dapper). If you must you could give EF a try, but stay away from LINQ to SQL.

The value of Dapper IMHO is not the speed. It is precisely the interface. With dapper I can copy paste code between C# and SQL contexts freely. Exploring the database directly in SQL and then use the result directly in the program without a translation step.

LINQ to DB has always seemed interesting, but I don't like it's use of attributes for POCO properties. It's not clear from the introduction whether non-attribute properties will work if the names and types are compatible, or even if the attributes are custom or come from the standard System.ComponentModel.DataAnnotations. The attributes aren't discussed at all in the introductory examples actually.

Same here, using Mongo helped us a lot to iterate fast, JSON is much more agile than SQL.

We now migrate to PG using 'core' attributes in tables and 'flexible' attributes in JSONB to get the best of two worlds.

I agree to the API problem, JDBC and PG/SQL is not as nice as a interace compared to Mongo libs.

When you say JSON is more agile is that because you don't need to write migrations? Do you use a framework that supports migrations like active record or south?

Writing a load of consistency checks in the application layer sounds the opposite of agile to me. Unless you just want sloppy data.

We do not use migrations with JSON.

Adding and removing happens on the application layer with using Option[_] monads in the data model. Add a field means add Option[_], removing a field means making e.g. String an Option[String] - or fill with default values on read. If we drop a field over time we ignore it during writes.

   case class Person(name:String)

adding a job field after some time becomes

   case class Person(name:String, job:Option[String])

and application code needs to deal with it.

Works for us, compared to my previous jobs where we used SQL and migrations.

Its been mentioned here before, but check out Rezoom.SQL https://github.com/rspeele/Rezoom.SQL

All the power with static typing

Dapper is a great tool for getting an app started and up to speed, and even later once the app gets bigger if sometimes it makes sense to bypass the ORM and hit the DB straight-on.

> Microsoft at one stage had Linq to SQL which was quite good..... but they killed it :)

They also had a rest API directly into the db server at one point, but killed that also.

>I think NoSql, especially things like Mongo, got popular because it is super easy to program with javascript.

I chose Mongo for a project recently. I have many years experience with SQL databases, and I think SQL will become more of a niche in the future.

Here are the reasons:

Everybody uses an ORM with a SQL database. You can pretend they don't and everyone is writing raw sql, but they aren't. This is basically a big, complicated, and slow piece of software that tries to make a SQL database into something else. A NoSQL database is like a native version of that. It starts off with you being able to define collections and schemas in code and so on.

SQL is a shit way to query a database. I used to write massive queries that took an hour or so to write just so that the non-programming people could put them into analytics software that didn't support any other methods of input. While I was writing these queries, I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more". The fact is that programming languages are much better at querying databases than SQL.

Joins are slow and complicated, and ORMs are slow and complicated, so you can never actually use any of these things in a high-traffic environment. As you say, not many people will reach these levels of traffic, but it is still a factor.

Most of the comments about "I moved from mongo to postgres" lately are first-time programmers who didn't know any of the basic concepts of databases. They then discovered patterns that the SQL database forced on them and declared that mongo sucks when in reality, they just didn't know what they were doing.

>One of the problems of many stacks is that the frameworks wrap general purpose languages over SQL, which, is not really a good idea, SQL is a vastly more capable language for dealing with relational data and layers built over the top often dumb down the database.

This is not true at all. Programming languages are vastly superior at querying a database. Just go write a complex query and compare it to the one in linq or whatever you use.

>Microsoft at one stage had Linq to SQL which was quite good..... but they killed it :)

They have entity framework, which uses linq and is the same thing. It is very slow though.

> This is basically a big, complicated, and slow piece of software that tries to make a SQL database into something else.

Just to nitpick a little, but I think it's a worthwhile distinction, ORMs don't try to make a SQL database something else... either literally or philosophically. To use them in any more than a trivial way you still to understand RDBMSs. They just make the queries less verbose and the output more convenient to work with.

You are just defining the limits of "something else" and then saying it doesn't do that. If your data looks completely differently to how the database outputs it, I don't think you can say it is just a trivial difference. Both the structure of a query and its output use different concepts to sql.

Using linq, a query will look like:

db.Products.Include(p => p.manufacturers).Include(p => p.parts).where(p => p.name == 'bike').ToList();

in sql, it is SELECT * from products, manufacturers, parts, JOIN ...... ON .........

The linq query syntax makes it seem like you are just plucking a Product out of a database that has manfufacturer and a list of parts as part of its object. It is an object, not a row and table based structure.

Then the output itself is also an object, in a completely different structure to what the database gave to you.

People can use an ORM and not really know how SQL works if they never bother to learn. It is presenting them with an object-based database.

In a NoSQL database like Mongo, you either have to literally store the Product with its parts and manufacturer, or you take them separately from the database and put them back together in code. This requires no abstraction. It is exactly what is happening.

Could you please stop creating new accounts for every few comments you post? We ban accounts that do this, and it's in the site guidelines: https://news.ycombinator.com/newsguidelines.html. It's particularly abusive that you used multiple accounts in this same thread.

HN is a community. Obviously you don't have to use your real name, but if users don't have some consistent identity for others to relate to, we may as well have no usernames and no community at all. That would be quite a different kind of forum. Anonymity is fine, and throwaways for a specific purpose are ok—just not routinely.

https://hn.algolia.com/?sort=byDate&dateRange=all&type=comme...

> ORMs don't try to make a SQL database something else

This is simply not true.

Many ORMs are designed to abstract away SQL and RDBMS concepts entirely. They deal in objects and object graphs and output SQL which can be quite disjointed from the object model e.g. many-many relationships.

I'm becoming increasingly convinced that there is only one good ORM and the rest are fit only for CRUD operations. ActiveRecord. The abstraction it provides is amazingly flexible. You can be as close to the SQL as you want to be.

With other ORMs, any time I need something not CRUD, I wind up hand-generating SQL. With ActiveRecord, I never need to. Looking at generated SQL, I do all the time with AR, happily it makes that very easy for me. Including my own SQL snippets in scopes? Easy. Hand-generating joins? Almost never.

> This is simply not true.

Providing a wrapper around a thing is not the same as wanting to turn a thing into something else. I would argue that ORMs offer cosmetic change. Convenience, not substantive change.

> Many ORMs are designed to abstract away SQL and RDBMS concepts entirely.

Which ones? Look at their docs and you'll see where conditions, joins, columns, ordering, aggregations, etc.

ActiveRecord is probably the most abstract I've seen and the docs still refer endlessly to tables, foriegn keys, etc and show SQL equivalents http://guides.rubyonrails.org/active_record_basics.html#crea...

Here's Hibernate: http://docs.jboss.org/hibernate/orm/5.3/userguide/html_singl...

Here's Sequelize: http://sequelize.readthedocs.io/en/v3/docs/querying/#basics

Here's SQLAlchemy: https://docs.sqlalchemy.org/en/latest/orm/loading_relationsh...

I would say that ORMs as a class rely heavily on SQL and RDBMS concepts... particularly for anything beyond simple CRUD

Linq works for trivial left join project style of queries. But for anything actually using the features of modern sql-databases it comes short quickly.

As a example try to express something like “sum(x) over (partition by y order by z)”

I do agree that SQL the syntax leaves a lot to be desired, and a proper relational language with a syntax optimized for actual development, and even better, optimized for 6NF style databases, would be awesome, but linq is not by any stretch sufficient to be that

My understanding is everyone uses 3NF in practice though, so what would a 6NF database get you practically?

I see SQL the way I see things like Linux: it's got some weird things that you'd design differently if you could do it over again, but overall it's pretty good and probably not worth the effort to switch.

6NF gives flexibility since every thing is decoupled until you bring it in. It makes evolving the schema easier, makes optimizations easier. I also have a hunch that with such a focus it would be easier to find an interesting design space for new relational programming models since it’s, in a sense, “purer”.

But I guess it depends on the use case. For normal oltp style save/fetch entity 3NF makes sense. But it kind of makes sense in the way an ORM would make sense. Which makes you question if you really need relational database at all.

> Everybody uses an ORM with a SQL database.

I'm ripping ORMs out of any backend code and using things like jOOQ.

> I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more".... > Joins are slow and complicated

You know what's really slow? Creating joins in code.

> This is not true at all. Programming languages are vastly superior at querying a database. Just go write a complex query and compare it to the one in linq or whatever you use.

It makes zero sense to pull a bunch of records back from the db using multiple network calls to join and then filter. Let the db do its job.

> It makes zero sense to pull a bunch of records back from the db using multiple network calls to join and then filter. Let the db do its job.

But then it wouldn't be distributed processing! :)

Seriously, though, consider it for a moment.. this pattern has similar features to something like Hadoop. The data comes from storage nodes (database server and, hopefully, their read replicas) and goes to processing nodes (app server) to have the work done and is then new data is written back out over the network to storage nodes and replicated across the network (to the replica/slave database servers).

If the data volume is particularly low or the compute load (CPU and/or RAM) is particularly high, the distributed method would make intuitive sense. I haven't seen it yet, however.

Touché. You're right, at some point the data must be joined and filtered. My point was to let the tool do its job :)

Doing distributed joins correctly requires an architectural/technical capability that most distributed database engines don't have: decentralized parallel orchestration. If you have this, you can do joins even with very high data volumes efficiently given good parallel scheduling algorithms. Most databases are designed such that there is a single point of control that declaratively schedules all data flows required to execute the query; this scales poorly for operations like joins, never mind recursive joins, which is why you don't see it.

Letting individual database nodes dynamically schedule and orchestrate their own data flows with each other, essentially allowing each node in the parallel system construct its own execution plan in relation to other nodes as it goes along, does not fit within the "giant distributed file system" paradigm that most distributed systems are based on. People who design codes for supercomputers are often familiar with parallel orchestration idioms that work at extremely large scales but it hasn't crossed over into ordinary distributed database engines. (This is also a good litmus test for what makes a database "parallel" as distinct from "distributed".)

Most distributed database architectures are much more centralized than they need to be, particularly around control of execution planning, and this limits their expressiveness. It is quite difficult to hack together a distributed join that performs better than a centralized one without good support for parallel orchestration.

Thanks for that detail. I knew there were more interesting, technical reasons than my oversimplification for why I had never seen it.

> People who design codes for supercomputers are often familiar with parallel orchestration idioms that work at extremely large scales but it hasn't crossed over into ordinary distributed database engines. (This is also a good litmus test for what makes a database "parallel" as distinct from "distributed".)

My understanding was that this is primarily (or also) due to the lack of low-latency (and high-bandwidth) interconnects in the ordinary distributed environments, as those enable that orchestration.

I admit to wanting that to be true, as it supports a mantra I like to say to managers with programming-only (no Ops/sysadmin or even DBA) backgrounds: not all problems can be solved with software.

Could you point out any open source supercomputing / data products that get this right (ie have decentralized data flows?).

I'm obviously not the parent commenter and have no inside knowledge of the HPC world, but my guess is the main open source supercompting projects would be from NASA, the US national labs, and CERN.

You might search HN for the recent announcements about new clusters, especially top500, and look for the comments discussing using MPI (versus something custom, I think?), as my recollection is that those topics would yield further pointers to the actual examples you're looking for.

Take a look at parallel simulation codes (for example, LAMMPS in chemical physics).

>You know what's really slow? Creating joins in code.

Ahh, you have combined two separate points into a new point that I didn't make and took it out of context. As I mentioned, I was writing queries for analytics software. This software needed fresh data once per day. There was no requirement for speed. I was referring to speed of development, not speed of the query.

As for the other point, yes joins are slow, and yes, multiple queries can scale better, even if they are not the fastest when you time them in an isolated one-off scenario. Multiple queries are simpler in code and scale better. Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database.

Then there is the argument that "well you won't be operating at that scale". Then in that case, you don't have to worry about the minute differences between a single network call and multiple calls. There are so many approaches and realities that make your argument just a theoretical argument rather than a practical one. There is caching, denormalizing, and then there is the fact that people use a vast array of languages from slow as hell like python and upwards.

As for joins in code in particular, they are slower than in a database that is often written in c++ sure. It depends on the specific situation as to how fast things need to be. Did you know that many ORMs join in code? This is because of all the redundant data that the join creates. So you have to factor in whether the larger amount of data is slower to send than smaller data in multiple network calls.

From django:

However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one.

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python.

> Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database.

I've used joins "at scale" in multiple jobs in (conventional relational) databases up to a few TB with tens of thousands of transactions per second and never experienced any performance problem that was the result of a join, unless you count cross joins with no filters. I have never seen an instance where doing a join was faster in code and I don't see how it could be except perhaps in some unusual edge cases.

I'm sure things change at Google or Facebook scale, but almost nobody is Google or Facebook scale.

I've never understood the "joins are slow" meme or where it came from.

I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail? In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. Once you have that, the only thing slowing you down is your typing speed, and I don't think a more compact syntax would really make a difference.

>I've never understood the "joins are slow" meme or where it came from.

Well SQL databases generally don't support joining across a sharded database, which is usually necessary to scale unless you try to scale vertically with high powered machines and your data fits into memory and so on.

They are also obviously slow compared to denormalizing and querying without a join. Then there is the other fact I mentioned that they contain redundant data so if the query you need to pull into code is large enough, it is a lot of data that has to get sent over the network.

>I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail?

Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures.

I am talking about queries that require joins, self-joins, sub-queries, multiple types of joins, group bys layered on top of each other and so on. They are horseshit and terrible compared to nice programming languages and maybe using a couple of queries instead of one.

>In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means.

This takes a while, but so does writing the query. My non-programming coworker, while good with SQL, spent entire days trying to write the query to a query that he already knew in concept (as in, he knew what he wanted). So I don't agree with your point that understanding what you need is going to take so much time.

> Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures.

I guess this is where we differ. I've written many SQL queries many hundreds of lines long taking advantage of all kinds of SQL features. I don't see how I could make them "nicer" by writing them in Javascript: SQL has plenty of warts, but well-formatted and organized SQL is hard to beat for expressing exactly what you want without all the cruft associated with how you;re getting it. Once you know what you want, it comes out pretty quick (IME), only your typing speed is the limit. I find you have to think much more carefully about what you're doing in other languages because you have to think more about how to do it without the db abstracting all of that away.

IME loops in SQL are a huge code smell - everything should almost always be done using set logic to be clean and performant.

Yes well it would be ideal if I had code to show you and compare it to the SQL, but all of that is at my old workplace and I will get PTSD if I ever look at it again.

I agree that loops in SQL are not great. Loops in programming languages are fine obviously. There are just many more and nicer constructs in a programming language to manipulate data.

Maybe you have a root table that is anchoring your query, say a Staff table. The Staff table has a Manager column, which is another row in the Staff table. You then need to do a bunch of aggregate stuff. So in a programming language, you can maybe query the database 3 times, once for the staff you need, and then again for maybe shifts completed and so on.

You can then easily put the staff into a dictionary with virtually no code. Then you loop over the non-dictionary staff array, and you have something like:

  for (var staff in shittyStaff) {
    processedStaff.add({
      manager: staffDic[staff.manager],
      shiftsCompleted: shifts[staff.id].length,
      shiftsWithManager: shifts[staff.id].filter(s => s.coworker == staff.manager.id).length
  });

Or if it is setup with entity framework/c# stuff, it is just:

  var staff = db.Staff
    .Include(s => s.Manager)
    .Include(s => s.Shifts)
    .Select(s => new {
      Manager: s.Manager,
      ShiftsCompleted: s.Shifts.Count(),
      ShiftsWithManager: s.Shifts.Where(shift => shift.CoWorker == s.Manager).Count()
    });

Probably a terrible and not particularly complex example because I made it up, but to do that in SQL requires a lot more stuff, a self-join, group by with count etc, etc.

I use both raw SQL and the entity framework on a regular basis. That Linq to entities query gets translated pretty directly to SQL. Each of those includes translates directly to a left join on whatever column is specified as the key. You would need a group by, but no self joins. Assuming that you are familiar with the database schema and are proficient in SQL, it shouldn't be any slower to write the SQL version than the C# version.

It depends a little on the exact columns you need, but it would look something like this, which is a supper common form for a SQL query:

    select 
    m.id Manager,
    count(sh.id) as ShiftsCompleted,
    sum(iif(sh.coworker = m.id,1,0) as ShiftsWithManager
    from staff s
    left join manager m
    on m.id = s.manager_id
    left join shifts sh
    on sh.id = s.shifts_id
    group by s.id, m.id

The SQL version has the advantage that it's more intuitive to specify the columns that you need so if your query is running slow because you're pulling too much data (something that's happened to me a bunch,) you can omit unneeded columns pretty easily.

I think a big point of the article was that these more recent relational dbs (like memsql) figured out how to make distributed joins across multiple shards scale really well - that's one of their core value adds. So you can shard your for example customer and order data stored across multiple nodes and partitions and do distributed joins, aggregations etc. Scaling to use hardware resources on multiple machines is a crucial aspect of these systems.

Disclaimer, I work at Memsql, speaking for myself.

> never experienced any performance problem that was the result of a join, unless you count cross joins with no filters.

An unindexed join will have major performance problems, and look exactly like an indexed join.

> I have never seen an instance where doing a join was faster in code and I don't see how it could be except perhaps in some unusual edge cases.

Faster to do the same thing? No. Easier to see which things are fast and which things are slow? Yes. Also easier to avoid the deadlocks that traditional databases' overzealous application of ACID can easily lead to.

> Can you go into more detail? In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. Once you have that, the only thing slowing you down is your typing speed, and I don't think a more compact syntax would really make a difference.

I find the pseudo-English syntax of SQL is always very hard to follow - it slips into a kind of uncanny valley - the grammar of what goes where can be backwards from what I'd expect. Tooling is also rather limited compared to a "real" programming language. Just basic things like unit testing your queries are much harder than they should be.

>An unindexed join will have major performance problems, and look exactly like an indexed join.

Then add an index.

Its not exactly difficult in a database. Using a database without indexes is kind of stupid. Why would you do that?

And personally I find SQL to be one of the easiest languages to read. I agree that it's kind of back to front in many ways, but its way easier than trying to work out what happening in some nested loops that someone else has written.

> Then add an index.

Then you're adding work to your writes, and your database will block your live write transactions until the corresponding index updates are done.

> Its not exactly difficult in a database. Using a database without indexes is kind of stupid. Why would you do that?

You wouldn't intend to, but you might do it by accident. The failure modes can be pretty bad, since an SQL database will take whatever nonsense query you give it and try to run it, even if doing so impacts your live operations. Whereas in many NoSQL systems if you try to use an index that doesn't exist it'll fail fast.

SQL is also not very composeable. Queries and their constituent parts are not first class concepts in SQL. There is no way to, for instance, pass a query to a piece of code and have that code add part of a WHERE clause to that query. That results in a lot code dynamically generating SQL queries.

To the last point linq/ef actually generates one SQL query for whatever you are doing if you use it properly.

> You know what's really slow? Creating joins in code.

It really isn't. In a good programming language expressing a join should be just as easy as in SQL.

> It makes zero sense to pull a bunch of records back from the db using multiple network calls to join and then filter. Let the db do its job.

Agreed that you need a way to do aggregate queries on the server, but plenty of NoSQL systems give you ways to do that. I'd far rather write a map-reduce in javascript/erlang/... than express the operation in SQL and hope that the query planner does what I think it's going to do (which ends up a bit like trying to program in Prolog - it's magic until the day it doesn't work and you then have no idea what's gone wrong or how to fix it).

>It really isn't. In a good programming language expressing a join should be just as easy as in SQL.

I don't think they were saying it wasn't easy, just that it isn't as performant as letting the DB do the join. Which should be true in pretty much all cases.

> The fact is that programming languages are much better at querying databases than SQL.

They really are not though. Sure, perhaps for simple single table scans, bu nothing beats the pure optimizing potential of SQL. Try joining three tables in JS neatly and fast.

It's a bit terse, but you're lying to yourself if you think hodgepodge mess of ad-hoc JavaScript written to poorly replicate a single specific query is in any way better than what we have now.

>Everybody uses an ORM with a SQL database.

That's not true.

>While I was writing these queries, I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more"

Well, no, because SQL is a 4th generation language of higher level than most general-purpose languages. It is a domain-specific language focused on database handling.

A simple SELECT with a few joins and indexes involved encompasses a query execution plan that would be a few hundreds of code in a regular programming language.

>I chose Mongo for a project recently.

Storing relational data on a document store is a bad idea.

And if you need a document store, Mongo is pretty much a bad option.

Why is Mongo a bad option for a document store? Serious question.

> Joins are slow and complicated, and ORMs are slow and complicated, so you can never actually use any of these things in a high-traffic environment. As you say, not many people will reach these levels of traffic, but it is still a factor.

I have to clean up crap written by people like you. Go learn how to use a relational database properly instead of writing tosh like this and crap buggy code in the application layer.

Reply on news.ycombinator.com