Data and databases playbook

A data and databases playbook for any app that stores information. It covers schema design, queries and indexes, transactions, migrations, backups, personal data, day-to-day operation and caches.

The examples use PostgreSQL, MySQL and SQLite. Where document stores such as MongoDB work differently, the rule says so.

Data outlives code. A bug in a page is fixed with the next deploy, but a bad row can stay wrong for years. So these rules put as much of the checking as possible inside the database itself.

Chapters

1. Schema design

   Let the database guard its own data. Use constraints, exact types and stable keys, so no bug in any app can store a row that makes no sense.

   • Required 3
   • Recommended 4

2. Queries and indexes

   Ask the database only for what you need, in one round trip where you can. Index for the queries you run, and read the plan before you guess.

   • Required 3
   • Recommended 3

3. Transactions and concurrency

   Make each change all or nothing, and safe when two requests arrive at once. Send side effects such as email only after the data is safely stored.

   • Required 3
   • Recommended 3

4. Migrations

   Change the schema only through numbered migrations in version control. Run them in the deploy, test them on a copy, and keep old and new code working.

   • Required 4
   • Recommended 1

5. Backups and recovery

   Encrypt every database backup, keep point-in-time recovery where data changes all day, and prove each backup restores. Schedules and targets live in operations.

   • Required 2
   • Recommended 1

6. Personal data

   Store as little personal data as the job needs, delete it on a schedule and on request, and keep it out of test copies. Know who read it and when.

   • Required 3
   • Recommended 2

7. Operations

   Keep the database healthy day to day. Pool connections, set time limits, watch slow queries and growth, and let routine maintenance run.

   • Required 4
   • Recommended 2

8. Caches and copies

   Treat every cache, replica and search index as a copy that can be stale or lost. Say how fresh it must be, and keep the source of truth in one place.

   • Required 2
   • Recommended 2

All 42 rules on one page