How to Choose the Right Database?
Author: Michael Agarwal | 5 min read | June 13, 2023
If you asked me back in 2008 which database to use for a given use case, I would have said pick SQL Server, Oracle, or MySQL. That was the answer back then. These days, we start by thinking outside of the relational box to ask, “Is it the right database?”
At Data Summit 2023 in Boston, MA, I presented during the Latest Trends in Database Cloud Modernization track. My presentation, “Are You Picking the Right Database?,” covered the latest trends in database modernization, and I detailed tips and best practices around choosing the right database for your modern application needs.
Here are several highlights from this session.
Relational Database Management Systems (RDBMS)
Relational databases remain the most common solution and include Oracle, SQL Server, MySQL, PostgreSQL, MariaDB, IBM Db2, and many others. I recommend choosing RDBMS when you need referential integrity at the database engine level, with Atomicity, Consistency, Isolation, and Durability (ACID) transactions. Typical use cases include Enterprise Resource Planning (ERP) software, banking transactions, airline reservations, and retail purchases.
One of the biggest drawbacks of relational databases comes from horizontal scaling being limited to read replicas, for the most part.
NoSQL Databases
NoSQL databases continue to rise in popularity and offer a wide range of purpose-built solutions. Examples of NoSQL database solutions include MongoDB, Cassandra, Amazon DynamoDB, Amazon DocumentDB, Amazon Keyspaces, Azure CosmosDB, and Azure Managed Instance for Cassandra.
I’ll go into more detail about specific types of NoSQL database technologies in a moment, but overall, these solutions shine when it comes to horizontal scaling through techniques like sharding and replication. They’re a perfect match for high throughput and distributed applications.
However, they do often settle for eventual consistency. You can still get referential integrity, but you need to apply that through the app layer rather than the database engine.
Common use cases for NoSQL databases include online gaming scores, real-time bidding, and global scale applications.
More on NoSQL Databases
Here’s a quick reference on some of the most popular NoSQL databases:
- MongoDB: A document database that developers love because it supports JSON, and it is the most popular NoSQL database overall.
- Cassandra: A wide-column database that supports reading and writing to every node, capable of processing massive data volumes at scale.
- Amazon DynamoDB: A key-value database that offers single-digit millisecond response times.
- Azure CosmosDB: A Swiss Army knife of NoSQL databases, supporting many database types including MongoDB, Cassandra, and now it even the supports the relational PostgreSQL.
Other NoSQL database types I covered in my presentation included:
- Graph databases: These databases use a graph structure that puts the relationship between the data front and center. Amazon Neptune, Neo4j, and Azure CosmosDB are examples of this database type, with use cases that include social media networking, fraud detection, and recommendation engines.
- In-memory databases: These caching databases provide microsecond response times, and include database engines like Redis, Memcached, and Amazon MemoryDB. Since data is stored in-memory rather than on disk, there is a risk of data loss upon process or server failure. Typically, these databases are used alongside a main database. Use cases include product catalogs, user profiles, and session data.
- Time series databases: These databases are optimized for time-stamped or time series data, with an append-only ingestion pattern. Amazon TimeStream and InfluxDB are two examples of these solutions. The use cases include IoT data, application monitoring, observability, and vital sign monitoring.
Database Modernization Rationalization
When evaluating your database options for a particular use case and workload, it’s important to have a comprehensive plan in place on whether to deploy on-premises, migrate to the cloud, adopt hybrid infrastructure, or leverage multi-cloud. With proper planning, you can avoid increased costs, potential project failures, and performance issues.
The benefits of database modernization include:
- Better performance
- Less management overhead
- License cost savings
- Improved horizontal scaling and distributed processing
- Harnessing fit-for-purpose databases
