7 types of modern databases: purpose, advantages and disadvantages
Tags: database, programming, webdev, architecture
Published: 2021-03-13 17:55:32

There are hundreds of SQL and NoSQL databases. Some are popular, others are ignored. Some are simple and well documented, and some are difficult to use. Some are open source, while others are proprietary. Perhaps most importantly, some are scalable, optimized, highly available, and some are difficult to scale or maintain.

A natural question arises: which database to choose from? To answer it, we need to decide what we want to achieve with the database. To form an idea, you need to answer the following questions::

1. Do we need analytical access to the database?
2. Do we need to write or read in real-time?
3. How many tables/records do we want to keep?
4. What kind of accessibility do we need?
5. Do we need columns?

Will we be able to access tables filtered by columns or by rows?
When making a decision, you need to remember what a particular database can offer. The specifics of each database may differ, but in general, there are only a few types within which we can achieve basically the same goals. Let's look at them in more detail.

# Relational SQL databases

If you've ever worked with databases, most likely you started with this type, because it's the most popular and common. Such databases allow you to store data in relational tables with certain columns of a certain type. Relational tables are good for normalization and merging.
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l6ersynwmdn0jjpeqglv.png)

**Advantages:**

- SQL support
- ACID transactions (atomicity, consistency, isolation, and durability)
- Indexing and partitioning support

**Disadvantages:**

- Poor support for unstructured data / complex types
- Poor event handling optimization
- Complex / expensive scaling

**Examples:** Oracle DB, MySQL, PostgreSQL.

# Document-oriented databases

If we don't want to combine multiple tables to get the data we need, we can look at document-oriented databases. They allow you to store records in JSON format. In this format, we can create a complex value for any key and immediately include the entire data structure in a single record.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ek0kq7st3spwmtpqrk1s.png)

**Advantages:**

- The scheme is free of charge
- There is no need to always write all the fields in each record
- Good support for complex types
- Suitable for OLTP

**Disadvantages:**

- Poor transaction support
- Weak analytical support
- Complex / expensive scaling

**Examples:** MongoDB.

# In-memory databases

Databases of this type can provide a real-time response for selecting and inserting specific records. Most of them mostly store data in RAM, but in some cases, they also offer persistent storage on hard drives or solid-state drives. Most of these databases work with key-value records, so the values can be stored in a document-oriented format. But some databases also work with columns and allow secondary indexing of the same table. Using RAM allows you to process data quickly, but makes it more unstable and expensive.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ix15f6fhdru884bw0650.png)

**Advantages:**

- Quick Writing
- Quick Read

**Disadvantages:**

- High reliability
- Expensive scaling

**Examples:** Redis, Tarantool, Apache Ignite.

# Databases with wide columns

These databases store data as key/value records on a hard disk or solid-state drive. These solutions are designed to scale well enough to manage petabytes of data on thousands of shared servers in a distributed system. They represent Stable architecture. This architecture was designed for two use cases: fast key access and fast write with high availability. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ubxlycx3ul3p6q1gfft1.png)

**Advantages:**

- Quick entry line by line
- Quick Key Reading
- Good scalability
- High availability

**Disadvantages:**

- Key-value format»
- No analytics support

**Examples:** Cassandra, HBase.

# Columnar databases

Sometimes we need to quickly access data not with specific keys, but with specific columns. In this case, it is better to abandon the line-by-line insertion and switch to the batch recording. Batch insertion allows columnar databases to prepare data for fast column-by-column reads. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c0sscm293hfzrhg7f7sb.png)

**Advantages:**

- Quick read column by column
- Good analytical support
- Good scalability

**Disadvantages:**

- Only suitable for batch inserts

**Examples:** Vertica, Clickhouse.

# Search Engine

If we want to access the data by filtering by any value and even by any word in the column, we need to remember the search engines. These databases index each word in columns and allow a full-text search. They are ideal for storing and analyzing logs or large text values. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xxere1sr14qqxpkkflih.png)

**Advantages:**

- Quick access by any word
- Good scalability

**Disadvantages:**

- Only suitable for batch inserts
- Poor analytical support

**Examples:** Elastic.

# Graph databases

For some cases, graph data structures are suitable. If your tasks require working with graphs, there are special databases that will meet your needs. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9mm5vi6d439n7e9vt2v0.png)

**Advantages:**

- Graph data structure
- Managed relationships between entities
- Flexible designs

**Disadvantages:**

- Special query language
- Difficult to scale

**Examples:** Neo4j.

# Conclusion

Almost any task can be performed with almost any type of database. The question is how expensive and optimized it will be. Choosing a tool that you are used to can reduce your time to market. But it can also cost you a huge amount of money to maintain and expand your equipment, which can be used inefficiently. Always try to use the database as intended. Perhaps a solution that meets your needs already exists.