When it comes to application development, choosing the right database is one of the single most important decisions that you’ll ever make. This post is designed to help you make the right decision.

When deciding on a database, the most important thing for you to understand is not how it works technically but rather what the different database types are best used for. So that’s what I’ll focus on here.

There are several major categories of databases, each with its own characteristics, use cases, and data models. Here are the biggest categories of databases:

1. Relational Databases (RDBMS):

Best for most apps in production today. Can be inherently difficult to scale beyond a point that 99.999% of all businesses applications never reach. Most probably the right choice for you project because they just work.

As a DBA, it’s important to force your frontend devs to either use your stored procedures or to educate them on how to query correctly. Otherwise no amount of backend optimization will prevent you from getting blamed for poor performance that your frontend devs cause 😉

Store data in tables with predefined schemas and relationships.
Use SQL (Structured Query Language) for querying and manipulating data.
Provide ACID (Atomicity, Consistency, Isolation, Durability) properties for data integrity.
Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.

2. NoSQL Databases:

a. Document Databases:

Suitable for most Apps, Games, IoT. Not as good for relational queries.

Store data in flexible, semi-structured documents (e.g., JSON, BSON).
Allow for schema flexibility and horizontal scalability.
Examples: MongoDB, Couchbase, Amazon DocumentDB.

b. Key-Value Stores:

Most often used for caching, for message queues, gaming leaderboards.

Store data as key-value pairs, where each key is unique and maps to a value.
Provide fast read and write operations and high scalability.
Examples: Redis, Amazon DynamoDB, Riak, Memcached.

c. Wide-Column Stores:

Popular use case is for scaling a large amount of time series data: Weather sensors records, history of different netflix shows watched, IoT device records.

Store data in tables with flexible columns, allowing for high scalability and performance.
Suitable for handling large amounts of structured data across distributed systems.
Examples: Cassandra, HBase, Google Bigtable.

d. Graph Databases:

Best for Graphs, Knowledge Graphs, Recommendation Engines. Also used for fraud detection.

Store data as nodes and edges, representing entities and their relationships.
Efficiently traverse and query complex relationships between data elements.
Examples: Neo4j, Amazon Neptune, Dgraph.

3. Time-Series Databases:

Optimized for storing and querying time-stamped data points.
Commonly used for monitoring, metrics, and IoT data.
Examples: InfluxDB, TimescaleDB, Prometheus.

4. Analytical Databases:

Designed for complex queries and data analysis over large datasets.
Optimize for read-heavy workloads and support OLAP (Online Analytical Processing) operations.
Examples: Amazon Redshift, Google BigQuery, Snowflake, Vertica.

5. In-Memory Databases:

Store data primarily in memory for extremely fast read and write operations.
Useful for caching, real-time analytics, and low-latency applications.
Examples: Redis (also a key-value store), VoltDB, MemSQL.

6. Columnar Databases:

Store data in columns rather than rows, enabling efficient compression and fast querying.
Well-suited for data warehousing, business intelligence, and analytical workloads.
Examples: Apache Cassandra (also a wide-column store), Vertica, ClickHouse.

7. NewSQL Databases:

Combine the scalability and flexibility of NoSQL databases with the ACID properties of relational databases.

Designed to handle high-volume OLTP (Online Transaction Processing) workloads.
Examples: Google Spanner, CockroachDB, TiDB.

This is a broad overview. You could add others, such as full-text-search-databases which are great for what the name suggests but do create a lot of overhead (a lot of indexes) or multi-model-databases which try to bring you the best of all worlds.

These categories are not always mutually exclusive, and some databases may fall into multiple categories or offer features from different categories. The choice of database depends on factors such as data structure, scalability requirements, querying patterns, consistency needs, and specific use cases of the application or system.

To get expert help in deciding what database is right for your business use case, contact us now and schedule a consultation with our database experts.