A database is an organised collection of data stored and accessed electronically. Databases are fundamental to nearly all software applications, from simple websites to complex distributed systems.
Types of Databases
Relational (SQL)
Store data in tables with rows and columns, using structured query language (SQL) for queries. Data is organised with strict schemas and relationships between tables via foreign keys.
Examples: PostgreSQL, MySQL, MariaDB, SQLite, Microsoft SQL Server, Oracle
Document
Store data as semi-structured documents (typically JSON or BSON). Flexible schemas allow different fields per document within the same collection.
Examples: MongoDB, CouchDB, Amazon DocumentDB
Key-Value
Simple storage model mapping keys to values. Extremely fast for lookups but limited query capabilities. Often used for caching and session storage.
Examples: Redis, Valkey, Amazon DynamoDB, Memcached, etcd
Wide-Column (OLTP)
Store data in tables with rows and dynamic columns, optimised for operational (OLTP) workloads with high write throughput and fast key-range lookups (not analytical OLAP scans).
Examples: Apache Cassandra, ScyllaDB, Apache HBase, Google Cloud Bigtable
Columnar (OLAP)
Store data column-by-column on disk for high compression and fast aggregations across huge datasets. Designed for analytical (OLAP) queries, reporting, and data warehousing.
Examples: ClickHouse, DuckDB, Google BigQuery, Snowflake
Distributed SQL (NewSQL)
Combine relational SQL and ACID transactions with horizontal scaling and distributed consensus (typically Raft or Paxos) across nodes or regions.
Examples: CockroachDB, Google Cloud Spanner, TiDB
Graph
Optimised for storing and querying relationships between entities. Use nodes, edges, and properties to represent and traverse connected data.
Examples: Neo4j, Amazon Neptune, ArangoDB, JanusGraph
Time-Series
Optimised for time-stamped data with efficient storage and queries over time ranges. Used for metrics, monitoring, and IoT data.
Examples: InfluxDB, TimescaleDB, Prometheus, QuestDB
Vector
Store and search high-dimensional vectors for similarity queries. Essential for AI/ML applications, semantic search, and recommendation systems.
Examples: Pinecone, Weaviate, Milvus, Chroma, pgvector (PostgreSQL extension)
See: Vector Databases
Key Concepts
ACID Properties
Guarantees for reliable database transactions:
- Atomicity - Transactions are all-or-nothing
- Consistency - Data remains valid after transactions
- Isolation - Concurrent transactions don’t interfere
- Durability - Committed data survives failures
CAP & PACELC Theorems
See System Design for an overview. Default trade-offs for common distributed databases during network partitions:
| Profile | Examples | Notes |
|---|---|---|
| CP / CP-leaning | MongoDB (majority concern), HBase, CockroachDB, Spanner, TiDB, etcd, AWS DynamoDB | DynamoDB uses Multi-Paxos leader replication groups per partition — CP-leaning for writes and strongly consistent reads, with optional eventually consistent reads |
| AP / Tunable | Cassandra, ScyllaDB, CouchDB | Cassandra and ScyllaDB support tunable per-query consistency |
Nuances:
- Redis (Sentinel and Cluster) uses asynchronous replication by default, so acknowledged writes can be lost during failover — it is not strictly CP.
- PACELC extends CAP by recognising that even without a network partition (Else), a system still trades off Latency against Consistency (for example,
PC/ECfor Spanner and CockroachDB,PA/ELfor Cassandra by default, andPC/ELfor DynamoDB with default eventually consistent reads).
Classification depends on deployment and configuration; most modern databases offer tunable consistency.
Indexing
Data structures that speed up read queries at the cost of write performance and storage. Common types:
- B-tree - General-purpose, good for range queries
- Hash - Fast exact-match lookups
- GIN/GiST - Full-text and complex data types
- Bitmap - Low-cardinality columns
Transactions
Logical units of work that group multiple operations. Support varies:
- Full ACID transactions (PostgreSQL, MySQL, and Distributed SQL engines such as CockroachDB, Spanner, TiDB)
- Multi-document / multi-item ACID transactions (MongoDB and DynamoDB — both have supported multi-document/multi-item ACID transactions since 2018, though single-document operations remain faster and more idiomatic)
- Tunable / eventual consistency with optional lightweight transactions (Cassandra, ScyllaDB)
Replication
Copying data across multiple nodes for fault tolerance and read scaling:
- Primary-replica - One write node, multiple read nodes
- Multi-primary - Multiple write nodes with conflict resolution
- Synchronous - Wait for replicas before confirming writes
- Asynchronous - Confirm writes immediately, replicate later
Sharding
Horizontal partitioning of data across multiple servers to scale beyond single-node limits. Strategies include:
- Hash-based - Distribute by hash of shard key
- Range-based - Distribute by value ranges
- Geographic - Distribute by location
When to Use What
| Use Case | Recommended Type | Examples |
|---|---|---|
| Complex queries, joins, transactions | Relational | PostgreSQL, MySQL |
| Global scale with SQL and ACID | Distributed SQL (NewSQL) | CockroachDB, Spanner, TiDB |
| Flexible schema, rapid iteration | Document | MongoDB, CouchDB |
| Caching, sessions, real-time data | Key-Value | Redis, Valkey, Memcached |
| High write throughput, key-range OLTP | Wide-Column | Cassandra, ScyllaDB, Bigtable |
| Analytics, aggregations, data warehousing | Columnar (OLAP) | ClickHouse, DuckDB, BigQuery, Snowflake |
| Metrics, monitoring, IoT streams | Time-Series | InfluxDB, TimescaleDB, QuestDB |
| Relationship-heavy data | Graph | Neo4j |
| AI/ML, semantic search | Vector | Pinecone, pgvector |
| Serverless, auto-scaling | Managed NoSQL | DynamoDB |
Performance Considerations
Query Optimisation
- Use
EXPLAIN/EXPLAIN ANALYZEto understand query plans - Index columns used in WHERE, JOIN, and ORDER BY clauses
- Avoid
SELECT *- fetch only needed columns - Use connection pooling (PgBouncer, ProxySQL)
Scaling Strategies
- Vertical - Increase CPU, RAM, storage (simpler but limited)
- Horizontal - Add more nodes via sharding/replication (complex but scalable)
- Read replicas - Offload read traffic from primary
- Caching layer - Redis/Memcached for frequently accessed data
Common Pitfalls
- N+1 query problem - Use eager loading or batch queries
- Missing indexes on foreign keys
- Over-indexing (slows writes)
- Not using prepared statements (SQL injection risk, no query plan caching)
Popular Databases Compared
| Database | Type | Best For | Licence |
|---|---|---|---|
| PostgreSQL | Relational | General purpose, complex queries, extensibility | PostgreSQL (permissive) |
| MySQL | Relational | Web applications, read-heavy workloads | GPL / Commercial |
| MongoDB | Document | Flexible schemas, rapid development | SSPL |
| Redis | Key-Value | Caching, sessions, real-time features | Tri-licensed AGPLv3 / RSALv2 / SSPLv1 (since Redis 8, May 2025; BSD-3 dropped March 2024) |
| Valkey | Key-Value | Open-source Redis-compatible caching and data structures | BSD-3 (Linux Foundation fork) |
| DynamoDB | Key-Value/Document | Serverless, auto-scaling workloads | Proprietary (AWS) |
| Cassandra | Wide-Column (OLTP) | High availability, massive write scale | Apache 2.0 |
| ClickHouse | Columnar (OLAP) | High-speed real-time analytics | Apache 2.0 |
| Neo4j | Graph | Social networks, recommendations, fraud detection | GPL / Commercial |
Related Pages
External Resources
- DB-Engines Ranking - Database popularity rankings
- Use The Index, Luke - SQL indexing and tuning
- PostgreSQL Documentation
- MongoDB Manual
- Redis Documentation