Databases: the four mistakes I made so you don't have to

Every database mistake I've made has been made before, usually by everyone. Here are my four most expensive, so your tuition is cheaper than mine was.

1. No indexes until everything was slow

The app worked at 100 rows and collapsed at 10,000. Every query was a full-table scan because I added indexes "later". Later is a lie. Index the foreign keys and the columns you filter by, from day one — then measure, then add more.

2. Multi-step writes without transactions

Money left one table, and the row that should have arrived in another didn't, because the server restarted between the two writes. If two writes must happen together, wrap them in a transaction. Databases have this feature; "hope" is not an alternative.

3. Timestamps without timezones

Two servers wrote times in two zones. Reports quietly compared noon to noon and meant different noons. Store UTC, convert at the edge, name the column so nobody forgets.

4. Deleting without a way back

A bad migration wiped data that had no backup. Now: backups run automatically, and destructive changes are soft deletes first, hard deletes never without a ceremony.

The pattern across all four: each one worked fine right up until it didn't, and none of them were visible in the demo. Databases reward paranoia. The paranoia is cheaper than the incident.