How to choose the right database type for your project
Relational, document, columnar, graph, in-memory. The decision isn't which is best, but which matches the shape of your data and your queries.
Choosing the right database can make the difference in a project’s performance and success. With the variety of types available today, it’s worth understanding what each one does well before deciding, not the other way round.
The types and what they’re for
Relational. The backbone of data management. They store information in tables and establish connections through relationships. Ideal for projects with fixed structures and well-defined relationships. MySQL, PostgreSQL, SQL Server and Oracle are the usual choices in enterprise applications and websites.
Columnar. They store by column rather than by row, which makes them highly efficient for analytical queries over large volumes. Cassandra and HBase belong here.
Document. If your data is semi-structured and changes frequently, storing documents (JSON, XML) is a good option. Common in web and mobile applications: MongoDB, Couchbase.
Graph. Designed to model highly relational data. Ideal when the relationships themselves are the point: social networks, network analysis, recommendations. Neo4j, Amazon Neptune.
In-memory. They keep data in main memory rather than on disk, giving ultra-fast access. Suited to caching and temporary storage: Redis, Memcached.
Time series. Optimised for data generated in sequence over time: server logs, sensors, events. InfluxDB, OpenTSDB.
OLAP. Built for complex analysis and reporting rather than transactional operations. Useful in business intelligence: Microsoft Analysis Services, Oracle OLAP.
Distributed. They spread data across multiple servers to improve scalability and availability. For high loads that need to scale horizontally: Google Bigtable, Amazon DynamoDB.
The decision
Each type has its strengths and its natural territory. There’s no best one in the abstract: there’s one that matches the shape of your data and the kind of queries you’ll run against it.
Assess the project’s needs carefully, look at how the data is structured and choose accordingly. Getting it right at the start is what lets you sustain performance and scalability across the whole life of the project, and what saves you a painful migration two years later.