Postgres

This book follows one lecture, Mastering Databases with Postgres (youtube.com/watch?v=F7Vwp2Xo5Do, 2h45 of the Backend from First Principles playlist), and then keeps going where it stops.

The lecture is deliberately not a SQL course. It skips SELECT, GROUP BY and ORDER BY on the grounds that a hundred free courses already teach them, and spends its time on the decisions a backend engineer actually makes: which type, which constraint, which relationship, which index. That is the right scope, and this book keeps it.

Parts I to VI are the lecture, expanded. Parts VII and VIII are not in the lecture at all, and they are where interviews go once schema design is agreed: transactions and isolation, the N+1 problem, connection pooling, EXPLAIN, vacuum. A schema you can draw is table stakes. Knowing what happens when two requests hit the same row at the same time is the difference.

The five block types

The four highlighters

One running example

Everything is built on the same five-table schema, a project management platform, because a schema you keep returning to teaches more than twelve disconnected examples. By Part V you will be writing the queries that back real endpoints against it, and by Part VI indexing those exact queries.

  ┌──────────────────────┐
  │ USERS                │
  │ id            uuid PK│◀────────────┐
  │ email         text U │             │
  │ full_name     text   │             │
  │ password_hash text   │             │
  │ created_at    tstz   │             │
  └──────────┬───────────┘             │
             │ 1                       │
             │                         │ owner_id
             │ 1                       │ assigned_to
  ┌──────────▼───────────┐             │
  │ USER_PROFILES        │             │
  │ user_id  uuid PK, FK │             │
  │ avatar_url    text   │             │
  │ bio           text   │             │
  └──────────────────────┘             │
                                       │
  ┌──────────────────────┐             │
  │ PROJECTS             │─────────────┤
  │ id            uuid PK│◀──────┐     │
  │ name          text   │       │     │
  │ status  project_stat │       │     │
  │ owner_id      uuid FK│───────┼─────┘
  └──────────┬───────────┘       │
             │ 1                 │ project_id
             │                   │
             │ many              │
  ┌──────────▼───────────┐   ┌───┴──────────────────┐
  │ TASKS                │   │ PROJECT_MEMBERS      │
  │ id            uuid PK│   │ project_id  uuid FK ┐│
  │ project_id    uuid FK│   │ user_id     uuid FK ┘│  composite PK
  │ title         text   │   │ role      member_role│
  │ priority   int 1..5  │   └──────────────────────┘
  │ status  task_status  │      the linking table:
  │ assigned_to   uuid FK│      users ←many-to-many→ projects
  └──────────────────────┘

Three relationships, and they are the whole of relational modelling:

• one to one users to user_profiles, implemented by making the foreign key the primary key
• one to many projects to tasks, implemented by a plain foreign key on the many side
• many to many users to projects, implemented by a linking table with a composite primary key

Persistence means data survives the process that created it. Close the app, reopen it a week later on a different machine, and the state is what you left.

That single requirement forces a choice about where the bytes sit.

  ┌─────────┐
  │   CPU   │
  └────┬────┘
       │
       ├──▶ RAM, primary memory
       │       nanoseconds        8 to 128 GB       expensive per GB
       │       volatile: gone when the process dies
       │
       └──▶ DISK, secondary memory (SSD, NVMe)
               microseconds       512 GB to many TB  cheap per GB
               durable: survives the process, the reboot, the power cut

Two orders of magnitude of latency, two orders of magnitude of capacity, in opposite directions. That trade decides which tool goes where:

Store your records in a text file and parse them in application code. It works, right up until it does not, in three specific ways.

Parsing

Every lookup becomes: open the file, read every line, split every line, compare every field. That is O(n) in application code for a question the database answers in O(log n), and it is error-prone in the way string handling is always error-prone.

No structure

A text file will accept anything. There is no way to say "this field is a number", so nothing stops amount = "banana" from being written, and every reader must defend against every previous writer's mistakes.

No concurrency control

This is the one that actually matters, and it is worth walking through slowly because the same bug reappears in section 40 with a real database.

  amount = 40
 
  request A                          request B
  ─────────────────────────          ─────────────────────────
  read amount        ──▶ 40
                                     read amount        ──▶ 40
  compute 40 + 20    ──▶ 60
                                     compute 40 - 20    ──▶ 20
  write 60
                                     write 20
 
  final value: 20.   A's write is gone, silently.
  had the order flipped: 60, and B's write is gone, silently.

That is the lost update. Nothing errored. Both requests succeeded. The result depends on scheduling, which means it is not reproducible and will be reported as "the numbers are sometimes wrong".

Four responsibilities, and it is worth being able to name them:

Integrity is the one to dwell on, because it is the one people give away.

  price column, declared numeric(10,2)
 
  INSERT … VALUES ('banana')
       ──▶  ERROR: invalid input syntax for type numeric
 
  the check happened in the engine, once,
  and it holds for EVERY writer: your API, a migration,
  a background job, a colleague on psql at 2am.