Binary Semaphorelearn
sign in

Study Notebook / 04

Postgres

Schema, queries and indexes for people who have to keep the thing running

Types, constraints, relationships and indexes, then the part most courses skip: transactions and isolation, the N+1 problem, connection pooling, EXPLAIN, and vacuum. A schema you can draw is table stakes.

pages
71
sections
50
editions
Reading, Print, Tablet

Sign in to read it

The whole notebook is free for 7 days. No card, no payment. We ask for an account so the download link belongs to someone.

Sign in with GitHub or Google

What is inside

The full table of contents. Nothing here is hidden: if a section you need is not in this list, the notebook is not the right one and you should not spend a week on it.

  1. The schema on one page
  2. 1. Persistence, and where data lives
  3. 2. What a text file cannot do
  4. 3. What a DBMS promises
  5. 4. Relational or not
  6. 5. Why Postgres
  7. 6. Numbers, and the money rule
  8. 7. Text, and why the answer is `text`
  9. 8. Time, and the one that bites
  10. 9. Identity: serial, identity, uuid
  11. 10. JSON and JSONB
  12. 11. Enums, and the argument that actually wins
  13. 12. Why you never touch the database by hand
  14. 13. Up, down, and the version table
  15. 14. Migrations that do not lock the table
  16. 15. The columns every table has
  17. 16. `NOT NULL` is the default you want
  18. 17. Keys: primary, foreign, composite
  19. 18. One to one, and when to split a table
  20. 19. One to many
  21. 20. Many to many, and the linking table
  22. 21. Referential integrity on delete
  23. 22. `CHECK`, `UNIQUE`, and pushing rules down
  24. 23. Naming: plural, lower, snake
  25. 24. Write `FROM` before `SELECT`
  26. 25. `JOIN`, and why `LEFT` is usually right
  27. 26. Nested JSON in one round trip
  28. 27. Parameterised queries, and SQL injection
  29. 28. Dynamic filters and sorts, without opening a hole
  30. 29. Pagination: `OFFSET`, and why it dies
  31. 30. Insert and update
  32. 31. The N+1 problem
  33. 32. What an index actually is
  34. 33. What to index, and what Postgres already did
  35. 34. What an index costs
  36. 35. Reading `EXPLAIN ANALYZE`
  37. 36. Beyond B-tree
  38. 37. Transactions, and what ACID buys
  39. 38. Isolation levels
  40. 39. Row locks and `SELECT FOR UPDATE`
  41. 40. The lost update, revisited
  42. 41. Connection pooling
  43. 42. Triggers, and when not to
  44. 43. Vacuum, bloat and wraparound
  45. 44. Backups, and the difference that matters
  46. 45. The delivery script
  47. 46. Follow-up question bank
  48. Appendix A. Defaults to memorise
  49. Appendix B. Glossary
  50. Appendix C. Self-test

How to read it

Read with a pen. Every notebook opens with a question to answer before you start and asks you to redo the answer at the end, and the margin in the Print edition exists so you have somewhere to be wrong first. The Tablet edition is 16:9 with vector text, so note apps draw on it rather than treating it as a photograph.

more notebooks