Skip to content

Databases

~12 min

In short

A database stores data so that many users can read and change it safely and quickly. This page covers the relational model, SQL, indexes, and transactions. Learn these first, because most software you will write or maintain depends on one. The site's Postgres and Redis material goes deeper once you are ready.

What the subject is about

A relational database stores data in tables. Each table has columns with types and rows with values. You describe what you want with SQL, and the database decides how to find it. This separation is the main idea: you say what, and the engine picks how.

Good design matters. Normalisation splits data into tables so that each fact is stored once. This avoids update anomalies, where a change has to be made in several places and one is missed. Keys identify rows. Foreign keys link tables.

Indexes make lookups fast. PostgreSQL creates B-tree indexes by default, which keep keys sorted so a lookup takes a few steps instead of a full scan. The price is extra storage and slower writes.

Transactions group several changes so that they succeed or fail together. The usual guarantees are atomicity, consistency, isolation, and durability (ACID). Isolation levels control how much one transaction can see of another's work in progress.

What to learn first

  1. Tables, rows, columns, and primary keys.
  2. Basic SQL: SELECT, WHERE, ORDER BY, LIMIT.
  3. INSERT, UPDATE, and DELETE, and what they change.
  4. Joins: inner join and left join, with a small example.
  5. Aggregates: COUNT, SUM, AVG, and GROUP BY.
  6. Normalisation up to the third normal form, at the level of examples.
  7. Foreign keys and referential integrity.
  8. Indexes and why a query can be slow without one.
  9. Transactions: BEGIN, COMMIT, ROLLBACK.
  10. Isolation levels, starting with read committed and serializable.

Start with the SQLite lesson in the site's backend part: ../../python/08-backend/02-sqlite-crud.md. It uses a real database file from Python. For the conceptual side, ../../python/03-data/04-dictionaries.md is a useful comparison with key-value lookup.

What you can skip at first

  • Writing database engines or storage internals. Read the overview first.
  • Performance tuning with query planner output. Learn to read a plan later, after you have written many queries.
  • NoSQL systems in depth. Learn when they exist and what trade-off they make. Learn the relational model well first.
  • Replication, sharding, and multi-region setups.
  • Vendor-specific SQL extensions. Stick to standard SQL first.

How it connects to the other subjects

Indexes are trees and hash tables from data structures and algorithms. Query speed depends on the memory hierarchy, covered in computer architecture.

Transactions use locks and the file system. The operating system provides the file, memory, and process mechanisms underneath. See operating systems.

Multiple clients sending queries use network connections. See computer networks.

The site has deeper material on these topics: ../../systems-engineering/postgres/index.md covers PostgreSQL in depth, including its concurrency and index internals, and ../../systems-engineering/redis/index.md covers Redis, a key-value store with different trade-offs. Read them after you can write joins and explain a transaction.

Free resources

  • CMU 15-445/645, Intro to Database Systems (https://15445.courses.cs.cmu.edu/fall2024/). Lectures, projects, and notes from Carnegie Mellon, with the course's Fall 2024 materials. It suits a student who wants the full internals of a relational engine.
  • PostgreSQL documentation, Concurrency Control (https://www.postgresql.org/docs/current/mvcc.html). The official explanation of how PostgreSQL handles concurrent transactions. It suits anyone who wants the exact rules of one real engine.
  • PostgreSQL documentation, Transaction Isolation (https://www.postgresql.org/docs/current/transaction-iso.html). The official page that lists the isolation levels and explains read committed as the default. It suits someone writing code that runs transactions.
  • PostgreSQL documentation, SQL (https://www.postgresql.org/docs/current/sql.html). The official reference for the SQL language as PostgreSQL implements it. It suits anyone who needs exact syntax.

Practice

  1. Create a table students with an id, name, and city. Insert five rows, then write a query that returns the students from one city in alphabetical order.
  2. Create two tables, courses and enrolments, with a foreign key. Write a join that lists each student's course titles.
  3. Write a query that counts enrolments per course and shows only courses with more than three students.
  4. Create an index on a column with 100000 generated rows. Time a lookup before and after creating the index, and write down both times.
  5. Write a transaction that moves 100 units from one account row to another. Show that a ROLLBACK in the middle leaves both rows unchanged.

Sources

  • Carnegie Mellon University, 15-445/645 Intro to Database Systems, Fall 2024, https://15445.courses.cs.cmu.edu/fall2024/
  • PostgreSQL Global Development Group, PostgreSQL Documentation, https://www.postgresql.org/docs/current/mvcc.html
  • PostgreSQL Global Development Group, Transaction Isolation, https://www.postgresql.org/docs/current/transaction-iso.html
  • PostgreSQL Global Development Group, SQL Language, https://www.postgresql.org/docs/current/sql.html