Why are there so many database systems?

Why Different Databases Exist

Your banking app, Instagram feed, Netflix recommendations, and Amazon checkout all run on databases.

But they don’t run on the same kind of database.

Some databases are built for:

• millions of tiny transactions
• massive analytical queries
• lightning-fast caching
• full-text search
• real-time event streams

Which raises a question: Why are there so many database systems

OLTP vs OLAP

Some systems are built for fast user actions.

Others are built for large-scale analysis.

This split gives us two major database workloads:

• OLTP — Online Transaction Processing
• OLAP — Online Analytical Processing

OLTP — Built for User Requests

In the early days of computing, databases mostly handled business transactions like sales, orders, salaries, and inventory updates.

In this context, transaction usually meant a real business event, often involving money.

Over time, databases expanded far beyond banking and accounting.

They now power social media feeds, video games, streaming platforms, messaging apps, and recommendation systems.

But the access pattern stayed similar.

Applications still need to read, write, update, and save user actions.

So the word transaction evolved.

In OLTP systems, transactions usually mean fast, low-latency reads and writes triggered by user actions — clicking, typing, buying, posting, or scrolling.

This fast, interactive style of database usage became known as:

• OLTP — Online Transaction Processing

OLTP systems are optimized for fast, continuous user activity:

• low-latency reads and writes
• small, focused queries
• many concurrent users

For example:

SELECT * FROM users WHERE id = 42;

OLAP — Built for Analytics

Later, databases were also used for analysis.

Instead of looking up one record, analytical systems scan large amounts of data to answer questions like:

• What was the total revenue of each store this month?
• Which products sold unusually well during a promotion?
• Which movies are trending this week?

These queries often scan millions or even billions of rows.

SELECT region, SUM(revenue)
FROM orders
GROUP BY region;

This analytical style of database usage is known as:

• OLAP — Online Analytical Processing

OLAP systems may still use SQL, but their internals are often designed very differently.

They are optimized for:

• large scans
• aggregations
• historical analysis
• analytical throughput

The goal is different:

Find insights inside massive datasets.

Common OLTP and OLAP Databases

System Type	Typically Optimized For
OLTP — PostgreSQL, MySQL, MongoDB	fast transactions and low-latency user requests
OLAP — ClickHouse, Snowflake, BigQuery, Redshift	large analytical queries and aggregations

Row-Oriented vs Column-Oriented Storage

As OLTP and OLAP workloads evolved, databases also began storing data differently.

Tables still have rows and columns, but internally the data can be organized in two main ways:

• row-oriented storage
• column-oriented storage

Row-Oriented Storage

Row-oriented databases store an entire record together.

Row records stored together

SELECT * FROM users WHERE id = 7

id name balance

scanning rows

5Ivy780

6Liam330

7Alice250

8Bob900

This works well for OLTP systems because applications usually need the entire record at once.

For example:

• loading a user profile
• updating an account balance
• checking login information

A query like this: SELECT * FROM users WHERE id = 7; can fetch row efficiently.

That’s why traditional OLTP databases like PostgreSQL and MySQL are row-oriented.

Column-Oriented Storage

Column-oriented databases store values from the same column together.

Columns stored together

SELECT SUM(balance) FROM users

id

5 6 7 8

name

Ivy Liam Alice Bob

balance

scanning balances

780 330 250 900

Total balance 0 780 1110 1360 2260

This works well for OLAP systems because analytical queries often scan only a few columns across millions of rows.

For example: SELECT SUM(balance) FROM users;

The database only reads the balance column and skips unrelated columns like names or emails.

That’s why many analytical databases like ClickHouse and BigQuery use column-oriented storage.

Storage Engines Evolved Too

As OLTP and OLAP workloads diverged, storage engines started evolving in different directions too.

A storage engine is the part of the database responsible for storing, retrieving, and organizing data underneath the database system itself.

Different databases use different storage engines depending on the workload they are optimized for.

Database / Engine	Optimized For
PostgreSQL PostgreSQL Engine	transactional OLTP workloads
MySQL InnoDB	reliable row-oriented transactions
MongoDB WiredTiger	flexible document storage
ClickHouse ClickHouse Engine	column-oriented analytics
RocksDB RocksDB Engine	high write throughput
Redis In-Memory Engine	ultra-fast caching and lookups

One Database Couldn’t Optimize Everything

As applications grew more complex, databases started specializing for different workloads:

• transactions
• analytics
• caching
• search
• event streaming
• document storage

This led to many specialized systems.

System	Best Known For
PostgreSQL	reliable transactions
Redis	ultra-fast caching
Elasticsearch	full-text search
ClickHouse	analytical queries
MongoDB	flexible document storage
Kafka	event streams

Each system makes different trade-offs.

There is no perfect database.

Only databases designed for different workloads.

Learnings

OLTP

Optimized for fast user actions and transactions.

OLTP. Optimized for fast user actions and transactions.

---

Quiz

86% of people love quizzes after learning. Are you one of them?

★

Question 1 of 12 🏆 0 / 120 ⚡ Attempt 1 of 2

Question text

Next →