> I think the most interesting question though is would they be able to get MVP and initial customers that set off this if they were moving (slightly) slower due to SQL and slight overhead that comes with?
I've used Postgres and Mongo pretty extensively, and for any reasonably seasoned developer, the startup overhead of an SQL system is a myth. There may upfront cost to learning how an RDMS and SQL work in the first place, but once you're familiar with them, they'll be faster than Mongo on any new project.
The schemaless concept of a document database seems to be the major selling factor in velocity of movement, but once you've got a good handle on a migration framework in the vein of ActiveRecord or other popular software, that's negated completely. It also really doesn't take long before schemaless starts to cause big problems for you in terms of data consistency -- it's not just the big players that get bitten by this.
The simplified query language is another one. SQL is a little bit obtuse, but it's not that bad once you have a handle on it, and a lot of people are familiar with it. Once you add in an ORM layer, the lazy-style access of a framework like Sequel or SQLAlchemy makes the developer experience quite a bit better than any Mongo APIs that I've seen. Also, after you get beyond trivial usage, SQL's flexibility so wildly outstrips Mongo's query documents that it's not even worth talking about.
Postgres on the other hand ships with a great management CLI, a very powerful REPL (psql), and features like data types/constraints/transactions that guarantee you correctness with zero effort on your part. I can only speak for myself, but I'd take Postgres to the hackathon any day of the week.
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'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
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).
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!
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.
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.
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.
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.
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.
> I've used Postgres and Mongo pretty extensively, and for any reasonably seasoned developer, the startup overhead of an SQL system is a myth. There may upfront cost to learning how an RDMS and SQL work in the first place, but once you're familiar with them, they'll be faster than Mongo on any new project.
> The schemaless concept of a document database seems to be the major selling factor in velocity of movement, but once you've got a good handle on a migration framework in the vein of ActiveRecord or other popular software, that's negated completely. It also really doesn't take long before schemaless starts to cause big problems for you in terms of data consistency -- it's not just the big players that get bitten by this.
I don't disagree with the overall sentiment, but this post is overstating things in a few places...
Schemas add friction in development. You can certainly minimize this friction with experience and tooling, but for a completely new project, no amount of experience or ORM magic is going to "completely negate" that additional friction vs a schemaless system where you can change the shapes of your data and the relationships between them without any restrictions whatsoever.
For rapid prototyping of new projects, it could be entirely reasonable to trade off long term data-consistency benefits of having a well-defined schema in return for faster iteration times. But taking that schemaless prototype to production directly is decidedly unwise, so the final step in a prototyping process like this should involve a refactoring of your data model to introduce a schema/migrations system before that point.
It would be fine to argue that that final refactoring step to make the schemaless prototype production-ready could completely negate the faster initial iteration times afforded by starting out with a schemaless DB (I too would generally agree with this assertion, but we must also remember not all prototypes will go to production, as prototypes are meant to prove out an idea, and the idea itself won't always work out). But to claim on a brand new project (where the data model is ill defined and prone to sweeping changes), one can iterate _faster_ while maintaining a schema and migrations system than on a schemaless system (given similar levels of proficiency with both systems) strikes me as hyperbolic.
> For rapid prototyping of new projects, it could be entirely reasonable to trade off long term data-consistency benefits of having a well-defined schema in return for faster iteration times. But taking that schemaless prototype to production directly is decidedly unwise, so the final step in a prototyping process like this should involve a refactoring of your data model to introduce a schema/migrations system before that point.
I don't get it. If you're not in production anyway, you're not working with real data. And if you're not working with real data, you don't have to migrate anything. The entire problem just straight-up disappears. Just blow away your database and recreate it with the new schema. Modify your test data insertion scripts to match and load them.
Or alter your schema in the client. Quite easy to right-click and 'Add New Column' or ADD COLUMN or whatnot. I'm trying this with a new project and it is resulting in a data model that feels cleaner and hand-built.
One can simply capture the schema when they've found a good structure, and then use that as the starting point for a migration system (like ActiveRecord).
The need to maintain an explicit schema and update it every time your data model changes is the point of friction here, not the migrations system. To be perfectly clear, I'm not suggesting maintaining a schema is onerous enough to outweigh the numerous other benefits having an explicit schema provides, even at the prototype stage, but when you're prototyping and don't need to worry about data migrations, having to maintain a schema is certainly not going to be _faster_ than not having to maintain a schema, as the original post claimed.
You always have to maintain an explicit schema -- it's just defined in your code instead of the database. And depending on your tooling, it's not easier/faster to maintain that schema in code vs. the database.
What is worse for schemaless databases is schema versioning. Your code will forever have to support every schema that has ever existed in production data. I have code to support schemaless data that was entered well over a decade ago. If I had simply used a relational database I would just have a single schema to deal with.
During development, schema changes are quite frictionless. It also provides extra safety and documentation that any friction is completely worth it.
> You always have to maintain an explicit schema -- it's just defined in your code instead of the database.
This is normally known as an implicit schema. It's implicit precisely because you don't explicitly maintain a schema separately from your application code.
> During development, schema changes are quite frictionless. It also provides extra safety and documentation that any friction is completely worth it.
The second sentence contradicts the first. And I'm not disagreeing with the second. The friction may be worth it, but calling it _frictionless_ was my issue with this post and the original (which went even further by asserting the friction somehow accelerated development).
> It's implicit precisely because you don't explicitly maintain a schema separately from your application code.
But that's not completely true. There is plenty of code necessary for managing the schema and just the schema in a document database. Default values, relationships, validation, and managing previous version of the schema. I have plenty of code that just exists to handle documents in the "old" format.
> The second sentence contradicts the first.
Maybe I should have said almost frictionless instead of quite. And honestly, some things are less work with a schema (like creating a new defaulted boolean column) in an RDBMS than in a schemaless design. So the friction is relative.
> The friction may be worth it, but calling it _frictionless_ was my issue with this post and the original
I have a similar issue with calling a document-store _schemaless_. It's not schemaless, it has a schema. In fact, it has as many schemas as there are changes to the structure of the data. And this, in my opinion, is the biggest negative to that kind of design.
I don't think we're really disagreeing on anything, just talking over each other a bit. Let me try to clarify myself:
> But that's not completely true. There is plenty of code necessary for managing the schema and just the schema in a document database. Default values, relationships, and managing previous version of the schema. I have plenty of code that just exists to handle documents in the "old" format.
This is only the case because you chose to _add_ an explicit schema on top of whatever schemaless DB you were using for maintainability reasons, which is certainly necessary if you're building a production app. However, in this case I was talking specifically about using schemaless DBs in the context of prototyping, and adding an explicit schema (that specifies things like default values, relationships, and migrations) is certainly not mandatory in the prototyping phase when you're not dealing with any real/past data or migrations.
MongoDB and other schemaless databases by default will happily accept whatever document you want to store in it without any care in the world, and this can be a desirable property for iterating as quickly as possible, but in production you definitely want to specify an explicit schema of some kind on top of Mongo as you have done, or just move off of Mongo altogether onto a proper relational database with a mandatory explicit schema.
As to which is the better long-term choice for a production app, I totally agree with everything you said. With schemaless databases, even if you add an explicit schema on top of it, there's always still the possibility to underspecify in your schema and re-introduce implicit data dependencies into your data model and application logic, which can lead to nightmarish bugs in production (I've experienced many instances of this first hand). It's much better to use a database designed around a mandatory explicit schema for relational data for the stronger data consistency guarantees they provide once you start handling real, persistent data.
When you're prototyping, you can be equally as sloppy with a relational database and make the same productivity gains. However, the sort of thing you describe doesn't even sound like a real prototype but rather something fairly trivial:
> by default will happily accept whatever document you want to store in it without any care in the world, and this can be a desirable property for iterating as quickly as possible
I fail to see how a database full of mismatched documents is valuable in prototyping. My own experience with prototyping in an RDBMS is just a constant evolution of the existing sample (sometimes real) data. Adding new columns is trivial, breaking up a single column to a one-to-many is a simple insert..select into a new table as one example. Similar transformation with a document-store involves writing a lot of throw away code.
But from another perspective entirely, I find actually designing the schema to be the best place start when I prototype an application. If I have the database design correct then designing the corresponding UI or API is almost trivial. Now obviously that's just one style of development but it's a no less valid one. And I don't have to do anything more to move to production.
> I fail to see how a database full of mismatched documents is valuable in prototyping.
Again, as you yourself even brought up, just because you don't have an explicit schema doesn't mean your data needs to be schemaless. There can still be an implicit schema to your data that depends on the shape of the documents you store in your database. And in a prototype without real data (I'm defining real data here as data that can't be trivially discarded without consequence), the documents that actually end up in your database will have a uniform shape simply because your code only operates on the current version of that implicit schema, and older versions can simply be deleted.
> But from another perspective entirely, I find actually designing the schema to be the best place start when I prototype an application. If I have the database design correct then designing the corresponding UI or API is almost trivial. Now obviously that's just one style of development but it's a no less valid one. And I don't have to do anything more to move to production.
That approach is certainly valid. I just wanted to make it clear that using a schemaless database doesn't mean you can't do any schema design up front, it just affords you the ability to skip the overhead of updating and adhering to an explicit schema at every step in the evolution of your prototype. In order words, you let your code and your product needs drive the changes and growth in your schema, and only look towards solidifying the changes in your schema into an explicit document to adhere strictly to once you have more concrete insights into how your data model needs to look, driven by the data needs of a working prototype.
I feel I've stated my position as clearly as I could, so I'll leave it at that. Feel free to reply if you still take issue with anything I said, but I probably won't be responding.
I think you have stated it well. And I agree that there is overhead in creating a schema. I think, however, where we might disagree is on whether or not that overhead is anything more than trivial.
I think ultimately this is similar to the debate on static vs. dynamic typing.
If you're using an ORM, you can generate the schema automatically. With Hibernate, you set the hibernate.hbm2ddl.auto property [1]. If you set it to 'create' or 'update', it will alter the database schema so that it matches your objects, which is handy for prototyping, and terrible for production. If you set it to 'validate', it will check that the database schema matches your objects, and refuse to start if it doesn't, useless for prototyping but ideal for production.
That's a neat feature, but as you may already know, ORMs come with their own baggage in the form of object-relational impedance mismatch. There's an entire rather-substantial Wikipedia article on it, so I won't go into it any further myself: https://en.wikipedia.org/wiki/Object-relational_impedance_mi...
ORMs are a solution to the problem of the impedance mismatch between an object model and its corresponding relational model. It's not that ORMs come with the problem.
I think it increasingly rare today to see real object domain models anyway - the original raison d'etre of ORMs. Instead it's common to see domain concepts modeled in the data layer and modified by logic (often limited to CRUD) in an application/service/controller layer. The problem with this of course is that as complexity increases, that app/service/controller layer increasingly approaches a big ball of mud.
Would you recommend some resource for learning to setup postgres? As a new grad I often times find myself frustrated with setting up postgres or other rdbms so I usually opt for easier systems like amazon RDS, firebase or mongo.
> As a new grad I often times find myself frustrated with setting up postgres or other rdbms so I usually opt for easier systems like amazon RDS, firebase or mongo.
Yeah, I know exactly what you mean. Everything in Postgres is supremely well documented, but there's just so much documentation, that getting the basics down is pretty hard. I definitely went through a period of building muscle that was a tad frustrating to say the least.
Unfortunately, I don't know a particularly succinct resource that I recommend right now, but I will suggest taking a look at official docs. Maybe the section on managing databases in particular [1]. In general, you should just get good at using createdb, dropdb, and psql (which is mostly just generic SQL).
If you're on a Mac, installation and setup might looks as easy as:
And, I've worked in a few that needed more... million+ simultaneous users, and over 380k requests per second, and 5 nines. There really are times you need more than an SQL server can keep up with.
Another, it was the shape of the data, all keyed off a single record, but ancillary tables with additional queries or joins meant over 30 joins, or 15 additional queries to render one resource, which fit into a single "nosql" record and mongo supported the additional indexes needed, it was a good fit for the use case.
I'm not advocating nosql for everything... I've seen a few that went mongo that would have been better served with sql. But that isn't to say that a document or column-store database is never a better fit from the start.
Docker works fine, but I prefer to run a vagrant machine with ansible to setup postgres + everything else for development. In production I just use RDS.
It's also a myth that devs choose NoSQL over SQL because "SQL is too hard."
What I see more of is situations where the developers "know" SQL but still just don't want to sit down and figure out their data models.
It's like, the temptation to reach for the nearest short-cut -- whether that means over-reliance on ORMs (or hodgepodge 'data access layers'); or continually munging stuff at the application layer for nearly every operation; or... Mongo -- is always just too great.
figuring out the data model as you build can be beneficial, you realize what you need as you work with the thing and can add it on the fly, at a certain point of course you need to do a cleanup. Getting people to accept the cleanup requirement is the difficult part.
Can't speak for anyone else... for me, it was the shape of the data I was interacting with was best represented as nested objects... think a classifieds site... primary record is the ad/entry, but different types of products will have differing ancillary data. That was my use case in first using MongoDB. Why, because otherwise there were up to 30+ joins in order to bring up all the data around any given classified entry.
Reshaping the data into a document store, with a handful of indexes made a lot of sense. Nearly effortless replication was also a nice feature, and needed for some extra endurance and scale. I'm pretty sad that RethinkDB didn't get farther, as their management interface and approach was a lot better than MongoDB.
To me it's about what does the data look like, what are the performance needs, and how much do you want to spend, cross-train or hire out expertise. Not to mention the lock-in options. There are lots of options, and it depends on the project and use. I've worked in enough applications where at least parts of the application needed to be moved off of SQL short of spending FAR more on licensing and/or servers that were insanely expensive for the need the value wasn't good.
The issue is absolutely not the overhead of schemas or managing them.
The issue is the overhead of systems administration when clustering. Clustering is a requirement to support high availability, and Postgres clustering is a real bear to set up. Mongo clustering is easy.
Postgres and most other SQL databases hail from the era when things ran on a "box." They're designed that way. Clustering is a bolt-on. It's that more than queries or schemas that drive NoSQL.
It would have been better to fork Postgres and create a cleaned-up easy to cluster version minus the obnoxious arcana, but that's no fun. It's more fun to reinvent the wheel.
Edit: We migrated from Postgres to RethinkDB for this reason and are mostly happy with it. We miss the data integrity guards of SQL but getting clustering without a full time DBA and Raft consensus fail-over without intervention is a worthy trade off. We still use Postgres for back-end warehousing and analytics but those are not live systems so they can live on a "box."
>I've used Postgres and Mongo pretty extensively, and for any reasonably seasoned developer, the startup overhead of an SQL system is a myth. There may upfront cost to learning how an RDMS and SQL work in the first place, but once you're familiar with them, they'll be faster than Mongo on any new project.
Exactly, I was very new to the RDBMS world and always I have preferred to go for MongoDB. Once in my side project, I thought of learning RDBMS and used Postgresql. Once I learnt how transactions, joins works then RDBMS was totally an easy topic.
I have used Jooq instead of ORM, which again helped me to learn the queries and underlying system of Postgresql!
I've used Postgres and Mongo pretty extensively, and for any reasonably seasoned developer, the startup overhead of an SQL system is a myth. There may upfront cost to learning how an RDMS and SQL work in the first place, but once you're familiar with them, they'll be faster than Mongo on any new project.
The schemaless concept of a document database seems to be the major selling factor in velocity of movement, but once you've got a good handle on a migration framework in the vein of ActiveRecord or other popular software, that's negated completely. It also really doesn't take long before schemaless starts to cause big problems for you in terms of data consistency -- it's not just the big players that get bitten by this.
The simplified query language is another one. SQL is a little bit obtuse, but it's not that bad once you have a handle on it, and a lot of people are familiar with it. Once you add in an ORM layer, the lazy-style access of a framework like Sequel or SQLAlchemy makes the developer experience quite a bit better than any Mongo APIs that I've seen. Also, after you get beyond trivial usage, SQL's flexibility so wildly outstrips Mongo's query documents that it's not even worth talking about.
Postgres on the other hand ships with a great management CLI, a very powerful REPL (psql), and features like data types/constraints/transactions that guarantee you correctness with zero effort on your part. I can only speak for myself, but I'd take Postgres to the hackathon any day of the week.