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

Sign in and the whole notebook opens, all three editions with it. An account is what makes a download belong to someone.

Sign in with GitHub or Google

Start reading

How to use this book

Start reading

What is inside

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