Databases
Choosing a database is choosing which problems you want to be easy. Every engine optimises for some access patterns and consistency guarantees at the expense of others. This page maps the territory and indexes the detailed notes.
Start with a default
For a new application whose requirements are not yet clear, start with PostgreSQL. It is relational, transactional, mature, and stretches surprisingly far: JSON documents, full-text search, geospatial data, time series and vector search all work in one engine. You can run it anywhere.
Move to something specialised when you can name the specific limit you have hit — not in anticipation of scale you do not have. Operating two data stores well is more than twice the work of operating one.
The families
| Family | Data model | Good at | Weak at | Examples |
|---|---|---|---|---|
| Relational | Tables, rows, joins, SQL | Transactions, integrity, ad hoc queries | Horizontal write scaling | PostgreSQL, MySQL, SQL Server |
| Distributed SQL | Relational, sharded and replicated | SQL with horizontal scale and multi-region consistency | Latency per transaction, cost, complexity | Spanner, CockroachDB, YugabyteDB, Aurora DSQL |
| Key-value | Key → opaque value | Very fast lookups by key, simple scaling | Queries by anything other than the key | Redis, DynamoDB, etcd |
| Document | JSON-like documents | Flexible schemas, aggregates read and written whole | Joins, multi-document invariants | MongoDB, Firestore, DocumentDB |
| Wide-column | Rows with sparse, dynamic columns | Huge write volumes, known query patterns | Ad hoc queries; data must be modelled per query | Cassandra, Bigtable, ScyllaDB |
| Columnar / OLAP | Data stored by column | Aggregations over billions of rows | Single-row lookups and updates | ClickHouse, BigQuery, Redshift, Snowflake |
| Search | Inverted indexes | Full-text search, relevance, faceting | Being the system of record | OpenSearch, Elasticsearch |
| Time series | Timestamped measurements | Append-heavy ingest, downsampling, retention | General-purpose queries | Prometheus, InfluxDB, TimescaleDB |
| Graph | Nodes and edges | Traversing relationships many hops deep | Bulk analytics, simple CRUD | Neo4j, Neptune |
| Vector | High-dimensional embeddings | Similarity search | Everything else | pgvector, Pinecone, Qdrant, Milvus |
| Log / stream | An append-only, replayable log | Decoupling producers and consumers, event history | Random access and queries | Kafka, Kinesis, Pulsar |
The trade-offs underneath
OLTP or OLAP. Transactional workloads read and write a few rows at a time and need low latency. Analytical workloads scan millions of rows and aggregate. Row stores serve the first, column stores the second — see columnar databases. Running heavy analytics on the transactional database is a classic cause of outages; replicate to an analytical store instead, for example with change data capture.
Consistency. A single-node relational database gives ACID transactions. Once data is replicated across machines, the CAP theorem applies: during a network partition a system either refuses some requests or serves possibly stale data. Know which your database does, and what its default settings actually guarantee.
Scaling. Scale up first; it is simple and modern machines are very large. Then add read replicas for read-heavy workloads. Sharding — splitting data across independent nodes — is the last resort, because it pushes complexity into the application: cross-shard queries, transactions and rebalancing. Background in scalability and hashing.
Schema. “Schemaless” databases still have a schema; it just lives in application code instead of the database. Enforcing it in the database catches bad data at write time; enforcing it in code makes changes faster and failures later.
A selection method
- Write down the access patterns: the ten most important reads and writes, with expected rates and sizes.
- State the consistency needs per pattern. Money and inventory need transactions; a view counter does not.
- Estimate volume and growth honestly — capacity planning.
- Check whether the default handles it. It usually does.
- Consider operations: who runs it, backs it up, upgrades it and is paged for it. Prefer a managed service unless there is a reason not to.
- Plan the exit: how would data be migrated out? See data migration.
Common combinations that work well: PostgreSQL as the system of record, Redis for caching and ephemeral state, Kafka to move data between systems, and a columnar store for analytics.
Notes in this section
Relational fundamentals
- Normalization — normal forms, and when to denormalise
- Indexing — how indexes work and how to design them
- Transactions — ACID and isolation levels
- Foreign keys and constraints
- Referential actions —
CASCADE,SET NULL,RESTRICT - Views and materialised views
- To ORM or not
Engines
- PostgreSQL — architecture, MVCC, vacuum, indexes, connections
- PostgreSQL replication and high availability
- psql — A cheat sheet for psql, the PostgreSQL command-line client: connecting, meta-commands, output formatting and scripting.
- Foreign data wrappers
- Redis — data structures, persistence, caching patterns
- MongoDB and BSON
- Columnar databases
Around the database
- Caching
- Event-driven architecture
- Expand and contract — changing a schema without downtime
- Vector databases
Managed services
| Provider | Relational | Key-value / cache | Document / wide-column / other |
|---|---|---|---|
| AWS | RDS, Aurora | DynamoDB, ElastiCache | DocumentDB, Neptune, Redshift |
| Azure | Azure SQL | Azure Cache for Redis | Cosmos DB |
| GCP | Cloud SQL, Spanner | Memorystore | BigQuery, Firestore, Bigtable |
Running databases on Kubernetes is possible with operators and persistent volumes — see StatefulSets and EBS on EKS — but a managed service is the better default unless you have the operational depth.