When does your database choice matter?.

We recently took a project over from another developer. For one reason or another, they'd chosen what's known as a NoSQL database: MongoDB. The reasons why could be endless, and when it was chosen, it might have seemed like a good idea. It turned out not to be.

On the flip side, we've recently built a site using mongodb, probably for the same reasons that the original developer of the first project chose it - ease of development, lack of constraints, malleable schema (or complete lack of one) amongst others.

What we're saying is that it's not the database in question that's good or bad, but the database choice based on your business requirements and future possible requirements.

That first project has since been migrated in its entirety to PostgreSQL, a robust RDBMS (Relational Database Management System). We could have chosen Oracle, MySQL or MSSQL to name a few others, but we chose PostgreSQL.

Firstly, we chose it because it provides a means to query using complex relationships - the whole point of an RDBMS. MongoDB doesn't offer that, but the data our client has, and is building, is full of these complex relationships.

Secondly, PostgreSQL provides us with a few nice extras that we haven't found as capable in other systems to date, discounting the non-open-source platforms (Oracle, MSSQL). Namely, really great spatial and geographical object management by way of PostGIS, an extension to PostgreSQL (pg from here on in), and another extension called HStore, which allows arbitrary hashes of data to be stored, much like you would in a NoSQL database, and also to query on it.

All of this is a little bit technical, but the gist is this: the database you choose now could determine whether your app will need rebuilding in months or years to come. Choosing the right one isn't necessarily following what everyone else is doing, but assessing a whole range of options against your current data and data that you perceive to have in the future.

Also, this isn't a task to be decided without developer input - your developer should have experience working with a number of databases (we've used MongoDB, MySQL, MSSQL, Oracle, Neo4j, PostgreSQL, a number of the various options in the cloud, and a few others to boot) so that they can provide the right advice based on your requirement, not their skillset.

Of course, as we've proven, it is possible to migrate data between storage formats, but it's a dangerous process, and can sometimes lead to hours if not days of downtime depending on how big your schema is, and how widely it varies if you're starting with a NoSQL type database.

For this particular piece of work, the results were astounding. A query using MongoDB and some code to cobble things together used to take upwards of 40 seconds. By moving the data into PostgreSQL, we reduced that down to a couple of hundred milliseconds.