How to Choose the Right Database

If you do a quick Google search you will find hundreds of reviews about tons of different databases, all with in-depth topics about features the writer thought was important. This is an attempt of breaking down the database by the foundation and give some insights into what the primary strength is for each. Not in depth, just a quick look at each to help you narrow down the options.

While you read each section, keep asking yourself if this meets my project requirements.  How will I (or the business) support the maintenance of this database?   Is there a learning curve and how long will it take to get up to speed?  Lastly, cost. Both upfront cost and yearly maintenance and/or licensing.

 

 

SQL Server, Oracle, MySQL

Database Type: Relational Database

 

Cost: Pending the version, it can get expensive for licensing.

What it’s Great For

Relational data that holds true to data integrity, performance, many disaster recovery options, and strong security integration. Built-in encryption and ability to segment data for performance with meet compliance requirements. Support comes at an additional cost.

Strengths

  • Both SQL Server and Oracle rank in the top 3 most widely used databases.
  • Data compression is built in.
  • Granular security features.
  • Many options for Disaster Recovery.
  • You will find a lot of enterprise solutions are built using these technologies.

Weakness

  • Licensing can be tough to figure out as well as a costly purchase.
  • Changing schemas fall short here. Both SQL Server and Oracle are not great at changing schemas.
  • Usually takes a little longer for development as more thought needs to be in place before creation.
  • Horizontal scaling is not impossible but can be challenging.

 

 

Mongo, Cosmos

Database Type: Document Database

 

Cost: Free, except cloud.

What it’s Great For

Storing schema data that changes often. Each document is not bound to a certain schema.  Data is stored in JSON format and can be consumed easily by development.

Strengths

  • Horizontally Scalable by Sharding.
  • Stores data in JSON format
  • Flexible Schema
  • Disaster Recovery options are not bad, but not the greatest. 
  • Easy for developers to work with.
  • Integrates well with third-party tools to help in the development process.

Weakness

  • Limitations in size of document size can hinder overall storage expectations.
  • Querying across documents cannot be done.
  • Expert knowledge needed to squeeze top performance out of installations.  A basic performance with a very large data file is not great out of the box.

 

 

PostgreSQL

Database Type: Relational Database

 

Cost: Free, except cloud.

What it’s Great For

A relational database with table inheritance that holds true to data integrity, performance, many disaster recovery options, and strong security integration.

Strengths

  • Popular in regards to functionality, performance, and cost. Gaining traction within the community and expected to place in the top 5 best databases list.
  • Full-Text Search performs very well.
  • Disaster Recovery options
  • Extensive data type support that developers love like JSON.

Weakness

  • User interface pgAdmin is browser based.  However, you get to use other third-party tools that look and play better.
  • Documentation is not great, takes a bit of searching if you are stuck on something.
  • Configuration is not intuitive.
  • Performance suffers when large operations occur under a heavy load.

 

 

Redis

Database Type: Key-value Database

 

Cost: Free

What it’s Great For

Redis is an in-memory distributed key-value database with optional durability that supports several different data types.  Usage examples: Non-relational data such as session state, unstructured data like product reviews and blogs, user profiles, short video storage.  

Strengths

  • High performing with reads.
  • Lightweight, easy to use
  • Flexible and portable.

Weakness

  • Tough to query values as everything is returned as an object.
  • Pending the database size, Updates can be costly.

 

 

Prometheus

Database Type: Time Based Numerical System / Database

 

Cost:  Free

What it’s Great For

Used in conjunction with Kubernetes clusters and containers. This system is usually the data platform for Grafana dashboards.  Usually, a go to during an outage that will help in quickly identifying the issue.

Strengths

  • Multi-dimension data model with time series data identified by metric name and key-value pairs
  • The server is standalone and is not dependent on network storage or remote services.
  • Http time collection by pull
  • Has Alerting ability
  • Has Web UI for Data Visualization.

Weakness

  • Does not have long-term storage, anomaly detection or automatic horizontal scaling.
  • Does not have a dashboarding solution and requires an additional setup with Grafana or other solution

 

Summary

There are several databases to choose from (this article does not cover all of them) and sure you will find one that will fit your project. However, don’t rush this process. Take your time and make sure you have asked all the questions, kicked the tires and made sure every stone was turned.  This is an important first step that will make or break your initiatives.