Databases from First Principles

1. Why databases?

A running process lives in RAM. The moment it exits, every byte it held vanishes. Databases exist for one fundamental reason: persistence — data must survive process restarts, machine reboots, and hardware failures.

Secondary to persistence comes access. It is not enough to store data; you need to retrieve, update, and delete it efficiently and correctly, even when multiple clients are touching the same records at the same time.

2. Storage hierarchy

Not all storage is equal. Every engineering decision about where to put data is a trade-off between speed and cost.

Most databases (PostgreSQL, MySQL) are disk-based: durable by default, with buffer pools in RAM for hot pages. Redis is memory-first: blindingly fast, but data size is bounded by available RAM and durability requires explicit configuration.

3. DBMS

Storing bytes on disk is trivial. A plain text file can technically be a database. The question is: what do you lose without a proper Database Management System?

A plain text file handles none of this well. Parsing is manual, there is no concurrency model, and every application reimplements the same low-level plumbing. DBMS is that plumbing, done once and done right.

4. Relational vs NoSQL

Two dominant families, each with a different answer to the schema-flexibility trade-off.

The practical heuristic: if your data has a well-defined, stable shape and relationships between entities matter, use relational. If your schema is genuinely dynamic (a CMS where each article type looks different), NoSQL makes sense — but be deliberate, because flexibility often becomes inconsistency in production.

5. Why PostgreSQL

When in doubt, start with PostgreSQL. Four reasons:

• Open source — no licensing cost, no vendor lock-in, enormous community.
• SQL + extensions — a 1400-page spec, plus PostGIS for geospatial, pgvector for embeddings, and hundreds more.
• JSONB — you get document-store flexibility when you need it, without switching databases.
• ACID guarantees — transactions are correct by default. You opt into relaxed consistency, not into strictness.

6. Data types

numeric vs float: numeric is exact (arbitrary precision), float is a fast approximation. Use numeric for any financial value. Use float when you need speed and can tolerate rounding (e.g., confidence scores).

7. Migrations

A migration is a versioned SQL file that describes a single incremental change to your schema. The migration runner applies them in sequence and tracks which have been applied.

Why migrations over ad-hoc ALTER TABLE?

• Schema changes are version-controlled alongside code.
• Every environment (dev, staging, prod) applies the same sequence.
• Rollbacks are explicit and tested, not improvised under pressure.

Tool of choice: dbmate — zero dependencies, works with PostgreSQL, MySQL, SQLite.

8. Relationships

Referential integrity

Foreign keys are not just documentation — they enforce that related data stays consistent. Two key behaviours on delete:

• ON DELETE RESTRICT — prevents deleting a user if they own projects. The database blocks the operation.
• ON DELETE CASCADE — deleting a user also deletes all their projects. Useful, but destructive — be explicit.

Referential integrity is the first line of defence against data corruption at the database layer, independent of application code.

9. Queries

A real-world query for a user list with their profile embedded as JSON — useful for APIs that need to return a single object per user:

SELECT
  u.*,
  to_json(up.*) AS profile
FROM users u
LEFT JOIN user_profile up ON u.id = up.user_id
ORDER BY u.created_at DESC;

LEFT JOIN ensures users with no profile still appear. to_json(up.*) collapses the joined row into a single JSON column — avoids N+1 queries in the application layer.

10. Indexing

An index is a separate data structure that maps column values to their row locations — a lookup table that trades write overhead and storage for faster reads.

-- PostgreSQL creates this automatically for PKs:
CREATE INDEX ON users (id);

-- Add manually for frequently filtered columns:
CREATE INDEX ON projects (owner_id);
CREATE INDEX ON events (created_at DESC);

11. Patterns worth knowing