Comment by martinald | Hacker News Reader

Comment by martinald | Hacker News Reader

Parent Full thread

I totally agree with you, and started writing something about how understanding a good ORM takes nearly all the headache away.

I think the thing people do find slow is a lot of 'documents within documents' in SQL. It turns out this is usually a bad development pattern long term but it is super fast being able to just add docs inside docs with no configuration. It feels very slow writing foreign keys, navigation props and schemas for this in SQL vs JSON, where you can just dump your object in and you're done.

Basically; I think with noSQL you get some very short term gain for a lot of long term pain, and you're right, ORMs and other tooling solves this mostly.

I myself fell for this trap, and while it was a nightmare it actually matured me more as a professional more than anything I've ever done recently. Regardless of crazy hype, I don't think I'll ever fall for a solution so easily without evaluating it properly.

I think I assumed the "crowd" had done the tech due diligence on this stuff and it definitely wasn't the case.

I agree that Postgres trumps Mongo for most use cases, but if ORM is the answer, you might be asking the wrong question.

I love that Fowler discusses[1] how to avoid ORMs and offers only two possibilities:

> Either you use the relational model in memory, or you don't use it in the database.

He clearly has a blind spot. The correct answer, at least in some cases, is don't use objects in the first place.

[1]: https://www.martinfowler.com/bliki/OrmHate.html

I've been quite skeptical of the anti-ORM sentiments, since I always found the ORM handy, not a problem etc.

Two things recently started to change my mind

One is, after profiling some views in our Django app I found that a significant portion of the time in list views (i.e. returning many objects) was spent instantiating ORM model objects. Not on fetching them from the db, but turning them into objects whose imminent destiny is to get turned back into dicts for serializing to JSON.

So we switched to values() queries which return plain dicts, but then the ORM is just a query engine.

The second was, in a large app, finding that many of the tables start to require a degree of care to only perform 'safe' queries, i.e. ones supported by appropriate indexes. The integration of the ORM into the framework provides many conveniences, but this tends to involve a lack of awareness/control over exactly which queries get made. Most of the patterns that are 'idiomatic' to the framework also tend to involve a lot of ORM querying logic pushed down in the view layer... while our need to perform only a restricted set of 'safe' queries pushed us in the other direction, towards a repository layer with carefully curated query code.

I'm seriously considering exploring ORM-less development in future

There's no single technology that's perfect for all situations. An ORM is really useful for a lot of very common tasks. Most apps include a lot of reading/writing of single rows and basic list queries for which ORMs are a superior way of working.

But for anything involving complex querying, I typically just go straight to SQL. Most ORMs provide plenty of ways to handling different levels of query need from full ORM queries, to queries into entity objects, to queries into flat dictionaries.

I don't think ORM-less development is the answer to the wisdom that an ORM isn't a complete and sufficient interface to the database. That's just throwing the baby out with the bathwater.

"The integration of the ORM into the framework provides many conveniences, but this tends to involve a lack of awareness/control over exactly which queries get made."

I think that the lack of visibility is the problem, more than ORMs as such - one of the things that Rails got right is the logging.

In development, Rails will show you every SQL query as it happens, so you can see the blizzard of SQL queries that your request-response cycles are generating, and can spot crazy stuff once you have conditioned yourself to actually read the logs. Conversely, I've found that not being able to easily see what the ORM for your platform is doing can be really disempowering.

I suspect that nicer graphical tools would make this much more accessible: a lot of developers seem to either treat logs as noise, and I've seen some junior devs appear almost afraid of them.

It's easy to get logs, it'd be better not to deploy code with unsafe queries and then have to scan through the logs to see why your database is on its knees though

I haven't thought of this before, ORMs probably use 2-3x as much memory as regular structs/dicts. I've started using only the query builder in all my side projects and I've had a good experience so far.

yes, not just the memory... for models with lots of fields, in Python+Django, the time overhead of instantiating these complex heavyweight objects can really add up if you have a whole 'page' of them

I always figured the main overhead was doing I/O with the db, but in these cases it's not always so

interesting blog post about this from the creator of SQLAlchemy: http://techspot.zzzeek.org/2015/02/15/asynchronous-python-an...

The correct answer, at least in some cases, is don't use objects in the first place.

That at least fits my interpretation of what Fowler meant by "use the relational model in memory," though--if you're using a relational database, don't try to directly map objects to relational tables, but take advantage of what SQL offers instead. That's consistent with not using objects in the first place (although I suppose it doesn't require it).

I may indeed have interpreted that uncharitably.

Why do people always trot out the SQL relational databases?

If I had to do it all over again, I'd use a graph database. They work better and are faster for most web apps and social stuff. Including reports and joins.

And in my experience, I have avoided joins and used the database mostly as a key value store. Which makes it perfect for when we need to scale. We just make a CockroachDB adapter and that's it!

What's a good resource in getting introduced to "the postgre of graph databases", including it's value proposition over regular relational databases?

https://neo4j.com/book-learning-neo4j/

The issue is that if you can avoid joins long term, then you really either don't have enough data, enough complexity, or very rarely managed to have a data set that doesn't need joins.

If it is option 1 or 2, you just didn't need joins YET. When the time comes that 1 and 2 no longer apply it will be VERY hard to add them.

Sure you can use both types of databases where it makes the most sense but at that point you might as well just use the "NoSql" functionality in the relational db. PSQL does a JSON doc store VERY well and even allows deep joins when time comes.

I personally never saw much benefit to the graph databases. Sure the are great for a few data structures but most of the speed differences came them from being in memory more than anything else. Pretty much everything is fast with a good data structure design and everything in memory.

Actually major graph databases store data on disk as well. That's not the reason they are faster. The reason is that all the pointers point directly to the data.

Consider how you'd get the "top 10 books, and their related authors, and their related biographies".

1. A graph database would literally just look at an index of books, and then grab the book records. A relational database would do the same with an index, so far so good.

2. Now comes the difference. For each book, the graph database would just load the list of pointers to related authors and load those. And for each author, it would load the list of pointers to related biographies, which have pointers to related pictures etc. And in O(jk) where j is the number of books to return, and k the maximum number of things to get per book, it's done.

3. Now consider the same step for a relational database. After getting the books, it has to load the authors, and search it for each author. This takes O(k log N) where N is the total number of entries, and grows (albeit slower and slower) with increasing amounts of data. Then once every author record is loaded, it has to enumerate all their ids into a giant list and do it again. The list can be stored incrementally and the searches can be parallelized but at the end of the day ALL JOINS have an extra O(log N) factor, which is what slows down the database, usually by a factor of 10 for data that's in the millions or billions of rows.

4) The design of a graph database naturally encourages using indexes. In relational databases you have to remember to add them. And even after you do, the relational database takes log N longer to do all the joins. And joins are done very often in social networks, fetching related stuff, and other things in a normalized schema.

You can do all the relational algebra stuff while walking a graph, too.

In fact any relational database can be turned into a graph database by just adding a variable-length list of "pointers to related data" to each record which would point to the actual location of the rows in the related table's index. And then manage all relations between rows not as joins but as entries in these lists. And finally, implement support for a graph database language alongside SQL. However, I have not seen any such extensions to InnoDB or Postgres etc. which turn them into graph databases.

I understand that they do persist to disk and store data differently but the ones I've seen used in practice are in memory during runtime. It as been a few years but so maybe this has changed. Any examples?

Graph databases also are only fast when doing graph transversal lookups. They all have index just like RDB. Sure you got from parent->child->grandchild quickly but how to do you find the parent in the first place? An index normally.

But the parent is one lookup and the children are 1000. Thats why.

This is only if the 1000 children are already in memory. Otherwise it is still 1000 lookups, by index. This is why the Graph Dbs on the market tend to be in memory.

I'm not saying Graph DBs don't have their place. They do, but they are the wrong answer for MOST datasets.

NO. Each lookup thereafter is NOT by index. That's where the savings come from. The pointers already contain the exact block where to load the info from.

I have noob questions about what you've written here. I apologize for asking them here but you seem like you think clearly and know what your are talking about.

If you are in case 3, then is that when NoSQL allegedly starts paying off? If so, is the payoff in actual performance or just not having to write SQL?

I know you said that you could just use the NoSQL features of the relational DB. If you really truely know for certain that you will never need to join your data, then isn't using an entire relational DB software package a lot more overhead?

I case 3 you get get some real speedups, but you usually have to design the db layout for the use case. Step one is "How am I going to lookup this data" then design the data structures for that use case. Often(but not always) in these cases the NoSql db is just a lookup db with the system of record being a somewhere else and data is transformed into multiple data sets for lookup.

IMHO, most of the NoSQL hype was developers not wanting to learn sql/ddl along with a lot of resume padding. It is also important to note that NoSQL no longer means "No SQL" but really is now "Not Relational and/or ACID" (https://en.wikipedia.org/wiki/ACID). The only thing worse than SQL is everything else created to replace it. Many "NoSQL" products use a limited SQL like syntax as their only query format.

As for relational DB overhead, there is no reason for it be more then NoSQL(see sqlite) unless you are willing to give up part of ACID. Special cases like this are another use case for NoSQL but you probably want ACID unless you have data/throughput to make it nonviable. Otherwise you have it just ignore issues (normally what happens) or code for them in your app.

From what I've observed, the "crowd" is easily seduced by performance above all other concerns - correctness, security, science, etc. NoSQL was invented and gained popularity b/c it originally was easier to scale via sharding, and that was seductive enough [1] to give it industry momentum. While performance is a feature, it seemed many folks originally advocating NoSQL did not understand or appreciate the mathematical foundations of the relational model. A decade of experience seems to be driving zeitgeist back to scientific fundamentals.

[1]:https://www.youtube.com/watch?v=b2F-DItXtZs

Reply on news.ycombinator.com