Databases

Concepts SQL NoSQL Database Architecture

Every production system has a database. The choice of which one — and how you use it — has more lasting impact on the system’s behavior than almost any other decision you make.

🟢 Junior

Relational databases (SQL)

A relational database stores data in tables with rows and columns. Tables relate to each other through foreign keys. SQL (Structured Query Language) is the standard interface.

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT   NOT NULL UNIQUE,
  name  TEXT   NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  total   NUMERIC(10, 2) NOT NULL,
  status  TEXT DEFAULT 'pending'
);

SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY spent DESC NULLS LAST;

The relational model gives you joins (data from multiple tables in one query), constraints (the database enforces rules like “user_id must exist in users”), and transactions (all-or-nothing groups of changes). These are not extras — they’re the reason relational databases exist.

The major players: PostgreSQL (the best general-purpose choice), MySQL/MariaDB (ubiquitous, less capable), SQLite (embedded, zero config, perfect for dev/testing), SQL Server, Oracle.

Document databases (NoSQL)

Document databases store data as JSON-like documents. There’s no schema imposed by the database — each document can have different fields.

// MongoDB
await db.collection('users').insertOne({
  name: 'Alice',
  email: 'alice@example.com',
  address: {
    city: 'Berlin',
    country: 'DE'
  },
  tags: ['premium', 'beta-tester'],
  createdAt: new Date()
});

await db.collection('users').find({
  'address.country': 'DE',
  tags: 'premium'
}).sort({ createdAt: -1 }).limit(10).toArray();

Documents map naturally to the objects in your application code, which reduces the translation layer. The trade-off: you lose joins (duplicating data instead), referential integrity (no foreign key constraints), and precise schema enforcement.

MongoDB is the most popular. Firestore is Firebase’s version, good for real-time mobile apps. CouchDB adds built-in replication.

Other NoSQL types

Key-value stores — the simplest model. A key maps to a value. Fast, limited querying. Redis is the dominant example — often used as a cache or message broker rather than a primary database.

Wide-column stores — rows can have different columns. Designed for massive write throughput across distributed nodes. Cassandra and DynamoDB fit here.

Graph databases — data is stored as nodes and edges. Best when relationships are the primary query target (social networks, recommendation engines, fraud detection). Neo4j is the main example.

Time-series databases — optimized for data points indexed by time (metrics, IoT, financial data). InfluxDB, TimescaleDB (PostgreSQL extension).

🟡 Medior

ACID transactions

ACID is the set of properties that guarantee a database transaction either fully completes or fully rolls back, leaving the database in a valid state.

Atomicity — the transaction is all or nothing. If you transfer money between two accounts and the debit succeeds but the credit crashes, the whole thing rolls back. Neither balance changes.

Without atomicity you get partial state — money leaves account A and never arrives in account B:

-- No transaction — dangerous
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Server crashes here
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- never runs
-- $500 is gone

With a transaction:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;
-- If anything fails between BEGIN and COMMIT, the database rolls back automatically.
-- Account 1 stays unchanged.

Consistency — a transaction takes the database from one valid state to another. Constraints, foreign keys, and check rules are enforced. If any rule is violated, the whole transaction fails.

ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- Account 1 has $200. This would set it to -$800.
-- PostgreSQL raises: ERROR: new row for relation "accounts" violates check constraint
-- Transaction is rolled back automatically.
COMMIT;

No application-level validation needed — the database enforces the rule at the storage layer regardless of which service is writing to it.

Isolation — concurrent transactions don’t see each other’s in-progress changes. A transaction sees a consistent snapshot: either the state before another transaction started, or after it committed — never the in-between.

Without isolation you get dirty reads (reading uncommitted data that may later be rolled back):

Transaction A                        Transaction B
BEGIN;
UPDATE orders SET status='shipped'
  WHERE id = 99;
                                     SELECT status FROM orders WHERE id = 99;
                                     -- sees 'shipped' (uncommitted!)
ROLLBACK;  -- oops, wrong order
                                     -- Transaction B acted on data that never existed

With READ COMMITTED or higher, Transaction B’s SELECT sees the pre-update value until Transaction A commits or rolls back.

Durability — once the database returns COMMIT, the data is safe. It survives process crashes, OS crashes, and power outages because it was written to durable storage (or the write-ahead log) before the acknowledgment was sent.

Most SQL databases offer full ACID. MongoDB added multi-document ACID transactions in version 4.0. Many NoSQL databases either don’t support transactions or offer “eventual consistency” instead.

Isolation levels

Full isolation (serializable) is expensive. Databases let you choose how much isolation you need:

Read Uncommitted — you can read data that another transaction hasn’t committed yet. Allows dirty reads. Almost never the right choice.

Read Committed — you only see committed data. PostgreSQL’s default. Prevents dirty reads but allows non-repeatable reads (the same query run twice in a transaction can return different results if another transaction commits between them).

Repeatable Read — the same query always returns the same results within a transaction. MySQL’s default. Prevents dirty and non-repeatable reads but allows phantom reads (new rows matching a query can appear).

Serializable — the strictest level. Transactions behave as if they ran one after another. No anomalies. The slowest.

For most OLTP workloads, Read Committed is sufficient. Use Serializable for financial transactions or any place where read anomalies cause incorrect business outcomes.

Indexing

A table scan reads every row. An index is a separate data structure (usually a B-tree) that maps column values to row locations, enabling the database to jump directly to matching rows.

-- Without index: scans all rows
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Seq Scan on orders (cost=0.00..1842.00)

-- Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- With index: jumps directly
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Index Scan using idx_orders_user_id (cost=0.43..8.46)

Index considerations:

Every index speeds up reads but slows down writes — every INSERT/UPDATE/DELETE must update the index. Don’t index every column.

Composite indexes are ordered — an index on (user_id, created_at) supports queries filtering on user_id alone or user_id + created_at, but not created_at alone.

Covering indexes include all columns a query needs, so the database never touches the main table. The query is served entirely from the index.

Partial indexes index only rows matching a condition — useful for filtering on a sparse status field: CREATE INDEX ON orders(id) WHERE status = 'pending'.

🔴 Senior

The CAP theorem

The CAP theorem states that a distributed database can guarantee at most two of three properties simultaneously:

Consistency — every read sees the most recent write (or an error). Availability — every request gets a response (not an error), though it may be stale. Partition tolerance — the system continues operating if network messages between nodes are lost.

In any real distributed system, network partitions happen — nodes get separated. So the real choice is between CP (consistency over availability) and AP (availability over consistency) when a partition occurs.

Here’s the same scenario — a user updates their account email — played out in a CP system and an AP system:

Setup: Node A (primary) and Node B (replica).
A network partition separates them.

CP behavior — return an error, never return stale data:

Client → Node A: write email = "new@example.com"
Node A → Node B: replicate... [timeout — partition]

Node A: "I can't confirm Node B got this. Refusing the write."
Client ← Node A: ERROR 503 Service Unavailable

Client → Node B: read email
Node B: "I can't reach Node A to verify I'm current. Refusing the read."
Client ← Node B: ERROR 503 Service Unavailable

The data is never wrong. But while the partition lasts, the system is effectively offline for writes.

AP behavior — return stale data, reconcile later:

Client → Node A: write email = "new@example.com"
Node A: "Write accepted. Will sync to Node B when partition heals."
Client ← Node A: 200 OK

Client → Node B: read email
Node B: "Partition is active, serving local state."
Client ← Node B: "old@example.com"   ← stale, but not an error

[Partition heals]
Node A → Node B: sync latest writes
Node B: email updated to "new@example.com"

Two clients reading at the same time — one from Node A, one from Node B — see different values. For a shopping cart this is acceptable. For a bank balance it is not.

CP systems (HBase, Zookeeper, etcd) refuse to serve stale reads. Used for leader election, distributed locks, configuration that must be exact.

AP systems (Cassandra, DynamoDB, CouchDB) serve stale reads and converge afterward. Used for high-write, high-read workloads where temporary inconsistency is tolerable.

PostgreSQL with synchronous replication is CP within a cluster. It blocks on writes until all replicas confirm — no stale reads, but a single slow replica can stall the whole cluster.

Note: CAP is a limit theorem for partition events, not a description of normal operation. In practice, “eventual consistency” is the AP trade-off — reads may be stale by milliseconds or seconds, not by hours.

NewSQL

NewSQL databases attempt to give you the full SQL model (ACID, joins, schema, constraints) with the horizontal scaling of NoSQL.

Google Spanner — globally distributed, externally consistent SQL. Uses TrueTime (GPS+atomic clocks) to order transactions across the world. Available as Cloud Spanner.

CockroachDB — open-source Spanner-inspired. PostgreSQL-compatible wire protocol. Survives node failures, distributes data across regions automatically.

TiDB — MySQL-compatible distributed SQL. Strong in the HTAP (hybrid transactional/analytical) space.

PlanetScale — MySQL-compatible, built around Vitess. Schema migrations without locking, horizontal sharding, popular in the Rails/serverless world.

The trade-off vs. plain PostgreSQL: NewSQL adds complexity and latency (cross-node consensus is slower than local writes). For most applications that don’t need multi-region distribution, PostgreSQL with read replicas handles growth well into the tens of millions of rows.

Query planning and optimization

The query planner takes your SQL and generates a physical execution plan. Understanding it prevents slow queries.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > now() - interval '30 days'
GROUP BY u.id;

Read the plan bottom-up. Look for:

Seq Scan on a large table — missing index. Check what columns are in the WHERE clause.

Hash Join vs Nested Loop — the planner chooses based on estimated row counts. Wrong row estimates (from stale statistics) lead to wrong plan choices. Run ANALYZE table to refresh.

High actual vs estimated rows — the planner’s statistics are stale or the data distribution is unusual. Consider a partial index or a statistics target adjustment.

Sorts on large result sets without an index — if you ORDER BY a column frequently, index it.

Data modeling anti-patterns

Storing arrays of IDs in a column. It feels natural but kills you at query time. Querying “all orders containing product 42” on a product_ids TEXT[] column requires a full table scan. Use a junction table.

Entity-Attribute-Value (EAV). Storing dynamic attributes as rows (entity_id, attribute_name, attribute_value). Flexible but slow — every attribute lookup is a join, aggregations require pivoting, no type safety. Use JSONB in PostgreSQL instead for truly dynamic schemas.

Not thinking about write amplification. Every non-normalized piece of data you duplicate means two writes to keep it consistent. Denormalization for read performance is sometimes worth it — but the writes must be atomic, or you get inconsistency.

Premature sharding. Sharding (splitting data across multiple database servers) adds enormous complexity. PostgreSQL on a modern server handles thousands of transactions per second and petabytes of data with proper indexing and connection pooling. Don’t shard until you’ve exhausted vertical scaling, read replicas, caching, and query optimization.

Picking the right database

There is no best database — only the right tool for the access patterns. Some guidelines:

Use PostgreSQL for anything that doesn’t have a specific reason not to. It handles relational, JSONB (semi-structured), time series (with TimescaleDB), full-text search, and geographic data (PostGIS). Most applications never need anything else.

Use Redis for caching, rate limiting, session storage, pub/sub, and leaderboards. Not for durability-critical primary storage.

Use Cassandra or DynamoDB when you need write throughput that no single node can handle — IoT event streams, audit logs, user activity at scale. Design your access patterns before your schema; these databases don’t support ad-hoc queries well.

Use MongoDB when your data is genuinely document-shaped, you don’t have complex relationships, and schema flexibility is more valuable than constraint enforcement. Don’t use it just because it feels simpler than SQL — the flexibility will cost you in data quality.

Use Neo4j or similar when the queries are relationship traversals — “friends of friends,” “shortest path,” “which users are connected through these events.” Relational databases can model this, but graph databases do it orders of magnitude faster for highly connected data.

Next Generic Programming →