Quick Recap: Databases

There are different types of databases, each suited for different use cases.

Types of Databases

Relational Databases (RDBMS)

Use structured tables with rows and columns. Follow ACID properties and use SQL for queries. Ideal for structured data and strong consistency.

Examples: MySQL, PostgreSQL, Oracle, SQL Server

Best when data relationships and integrity are critical.

NoSQL Databases

Designed for scalability and flexible schemas. Best suited for unstructured or rapidly changing data. They often sacrifice strict consistency for speed and scale (CAP theorem trade-offs).

Types include:

Key-Value (Redis, DynamoDB) → Fast lookup using a key, similar to a dictionary/map.
Document (MongoDB, CouchDB) → Stores JSON-like documents with flexible schema.
Column-Based (Cassandra, HBase) → Optimized for large-scale writes and reads across columns.
Graph (Neo4j, JanusGraph) → Best for relationships and network-style data (nodes & edges).

In-Memory Databases

Store data in RAM for extremely fast access. Used for caching, session storage, leaderboards, etc.

Examples: Redis, Memcached

Speed over durability — used when performance matters most.

NewSQL Databases

Provide SQL-like queries with NoSQL-level scalability.

Examples: CockroachDB, TiDB, Google Spanner

Combine ACID guarantees with horizontal scalability.

ACID Properties (RDBMS)

Atomicity – All steps in a transaction succeed or none do.
Consistency – Data always moves from one valid state to another.
Isolation – Parallel transactions don’t affect each other.
Durability – Once committed, data is not lost.

CAP Theorem (NoSQL)

A distributed system can provide only two of the following three:

Consistency – All nodes see the same data at the same time
Availability – System is always responsive
Partition Tolerance – Must work even if network fails

Examples:

Cassandra → AP
MongoDB → CP (configurable)
DynamoDB → AP

Indexing

Indexes speed up read operations by avoiding full table scans.

Common types:

B-Tree Index
Hash Index
Composite Index

Trade-off: Faster reads but slower writes + more storage.

Transactions

Used to group multiple operations as a single logical unit.

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

When to Choose Which?

Use Case	Recommended DB
Strong consistency	PostgreSQL / MySQL
High scalability	Cassandra / DynamoDB
Real-time analytics	ClickHouse / BigQuery
Caching	Redis
Relationships between data	Neo4j (Graph DB)

DEV Community