Stored procedures and triggers
For some people, a database just stores data. For others, a database is more like an application server, with stored procedures, triggers, CSV exports to the file system, cleanup jobs and code to expire user accounts. I think a database should store data. Period. Embedding application logic in a database is bound to end up in a huge mess:
- Who maintains the stored procedures and triggers? The application on startup? A hacky collection of bash scripts? The sysadmin, manually? Nobody at all, because “we have a backup”? Any combination thereof?
- Are they kept under version control, or are they just floating around?
- How do you keep track of the versions of stored procedures and triggers? Not at all? “We add a version number to the name”? “We don’t change them”? And related to this:
- How do you run different versions of your application in parallel (during a rolling release), if they require different versions of your stored procedures and triggers?
- How do you track which ones are still needed? I bet the answer is: Not at all.
Of course, I’m trying to make two points here: First, if you’re stuck with a relational database, don’t use these features; you’ll make your life easier. Second, if your alternative database doesn’t have these features, don’t think of it as a negative. You’ll be forced to find better solutions right from the beginning.
Schema changes
Nobody likes schema changes. On larger tables, they may take tens of minutes or hours, during this time the database is often at least partially unavailable. This can be a complete show-stopper for deployments during the day, making continuous deployment impossible. Worse yet, in my experience it’s not even possible to figure out beforehand how long a schema change will take; you can try it on a development system with a copy of the production database, and it may take a completely different amount of time than on the production servers, even with equal hardware, cache warmup etc.
Also, during rolling releases, you may have multiple versions of the application trying to set up a different schema, or one of the applications may fail if it doesn’t like the current version of the schema.
There are some tools, most notably pt-online-schema-change by Percona to work around this. But they drastically increase the complexity of a deployment, and I even don’t want to think of the number of ways in which this can go wrong.
Failover and High Availability
Many relational databases don’t have good options for replication and failover. One example is PostgreSQL, which currently only offers simple replication without any sort of failover or HA. There are some tools that work on top of PostgreSQL which try to provide this functionality, but they are complex to set up, even more complex to manage and can fail in an even greater number of ways – very far off from anything one could call “high availability”.
A notable exception, kudos to them, is MySQL/MariaDB with its Galera cluster feature. While it’s implementation is very complex and not without issues, it mostly works well and is more or less easy to set up and administer. If you must go for a relational database, go for MySQL/MariaDB, because you can easily switch to a Galera cluster later! (Shameless plug: We offer Galera as a managed service, and we’ve got it all ready for you if you’re interested).
Normalization
So you’ve got some object in your application. This object is associated with a number of users, there are comments, and you can attach files to it. What do you do in a relational database? You create a table for that object, a table for the n:n relationship to your users, a table for the comments, a table for the file metadata (assume the file is stored elsewhere).
Now, you’ve got some heavy users of that feature. One of your objects has 50 comments, 140 user IDs attached to it and 15 files. Now your application needs to display this object.
The database needs to fetch one of your objects, 50 comments, 140 user IDs and 15 file metadata entries. That’s over 200 rows! Since the data was probably added over time, it’s not stored sequentially, hence typically your database server will have to fetch 200 random 4k blocks from your SSD, only to read a single one of your objects!
Wait, it gets worse. Let’s have a look at your indexes.
The n:n relationship between your object and your users contains #objects * #usersPerObject entries. Depending on how large usersPerObject is, the total number of rows in this n:n relationship can be huge, much bigger than the number of objects and the number of users.
You could, for instance, easily have 10 mio users, 10 mio objects and 200 mio entries in this n:n relationship.
This n:n relationship probably needs two indexes. These indexes are going to be huge, possibly bigger than all your object and user data combined. They occupy a lot of RAM, and they are just overhead, not actual user data.
Wouldn’t it be nice to store your entire object in one place, without the need to join all those tables? All of it could fit in a few sequential 4k blocks!
Oh. And don’t get me started on maintaining consistency in a normalized database. Yes, I, too, have written tools that iterate over the database, trying to find orphans and broken foreign keys.
SQL
Your application has to assemble what you want into a single string, and the database server has to parse that string and figure out what you wanted in the first place. That’s just wasteful, never mind all the injection issues. Why don’t you tell the database server directly what you want, in a structured form?
Ok, so relational databases are bad because of normalization and schema changes, some are bad in terms of HA, and I’m shooting myself in the foot if I’m using stored procedures. How can I do better?
Easy. Just store your object in one piece as JSON, without schema restrictions on the database.
- No more wasteful n:n relationships
- No more schema changes
- Far fewer or even no more orphans in the database
- Data much easier to navigate and understand
Now, even some relational database vendors have come to realize that this is the way to go, e.g. PostgreSQL has added such features in recent versions. But if you want it all, you’re probably better off with a database like MongoDB, which is built around these core concepts, and offers all the advantages.
Stored procedures and triggers
Stored procedures are not offered by MongoDB. It can execute server-side JavaScript, but it doesn’t store the code for you (hence none of the issues of stored procedures).
MongoDB doesn’t have triggers. Because triggers are often only used to ensure consistency in a normalized schema and MongoDB doesn’t have that, they’re not really needed anyway.
Schema changes
No schema, no schema changes.
(Of course your application has a schema, otherwise it couldn’t interpret the objects read from database, but the database doesn’t care how it looks).
Failover and High Availability
MongoDB supports easy-to-use and powerful replica sets. There can only be one writeable primary, though. If you need multiple primaries for performance (which you probably won’t since MongoDB’s write performance is excellent), you can use sharding (also supported out of the box).
Normalization
Typically, you put your entities into one document, therefore there is no normalization going on. However, don’t think you can just store anything you want in the database. Just because the database doesn’t enforce a schema doesn’t mean that you don’t have to put in some effort to find a good data layout, which may include spliting up data into multiple collections.
SQL
As the well-known term “NoSQL” suggests, MongoDB doesn’t support SQL. Instead you tell the server what you want in a structured form. This is something that seems alien at first, but you’ll get used to it.
Ok that’s great, but what about transactions?
MongoDB doesn’t support transactions per se. But, as long as you stay within one document, you can change any number of things atomically (“at the same time”) as you want, which kind of acts as a replacement for transactions. This means, in turn, that if your application needs atomicity for some use cases, you must make sure that all the data involved in this operation is living in a single document.
Does this sound like a problem to you? Think about this:
Relational databases have multiple levels of transaction isolation. You would need the highest levels of isolation to ensure that nothing can go wrong, but performance is so poor with that kind of isolation that even banking software doesn’t use it.
The second problem of transactions is: They don’t do what you want. You don’t want a user’s action to fail if another user changes something concurrently. Instead, you want the two changes to get merged, without loosing any user’s changes. Relational databases don’t offer any kind of semantics for that, this just sort of happens (or doesn’t) depending on the database layer implementation in your application. MongoDB offers update operations which don’t have that problem, because they only change selected parts of a document, and the change has very clear semantics, even in the presence of other, concurrent changes.
Help! I’m all for MongoDB, but I need to convince my boss!
Put these in your PowerPoint presentation (remove the comments in parentheses):
- Your application will have better performance, because there’s no need to normalize data (given a good implementation).
- Your application will have better availability, because replica sets can be set up easily and work very well.
- There is faster product development, because new features can be added more easily without the need for schema migrations.
- There will be lower system administration cost, because MongoDB is easier to set up, tune and maintain than relational databases.
Are there any downsides?
There are.
- MongoDB is in many ways very different from a relational database, among other things you have to re-learn how to create a good schema and how queries and updates work.This is asking a lot from your development team.
- A MongoDB document is a BSON (binary form of JSON with some extensions) blob. Each document repeats the keys, and that’s redundancy you don’t have with a relational database. This can be mitigated by choosing short key strings and using document level compression.
- Server-side Javascript execution was always kind of slow when I used it (which unfortunately includes the built-in map/reduce functionality). Things may have improved in the meantime, though. But if you stick to normal query/update operations and (if required) the aggregation framework, you’ll have great performance.
- Replica Sets only have one writeable primary node and sharding is complex to set up. Won’t be an issue for most applications, though, because write performance of a single primary is really good.
- There is no good PHPMyAdmin analog.
- If you want to migrate an existing application to MongoDB, it will be a very painful and tedious process, as you’ll have to rewrite all of your database layer and take care of data migration. It can easily take you years to do a full migration. Don’t underestimate this.
- While the original storage engine of MongoDB, mmapv1, was very robust, and its successor wiredTiger is faster and has better features, we’ve also seen cases in which a crashed wiredTiger database couldn’t recover on its own. This is not a problem in a replica set (you can just delete the data on the replica and let it re-sync), but it can be a problem on a single server. However, the MongoDB team is aware of these issues and we expect that they’ll get fixed.
