Schema Design
4 engineering logs on Schema Design. Newest first.
- Your JSONB column became the schemaless disaster you migrated away from
Why a metadata column with no schema accumulates forty shapes in two years, what TOAST does to read cost when you fetch one key from a large document, and how to promote the keys that turned out to be load bearing.
- Foreign keys are not overhead, and the thing you measured was a missing index
Why dropping foreign keys for performance usually removes a cheap index lookup and leaves the expensive sequential scan in place, and what actually replaces the guarantee once it is gone.
- ADD COLUMN is not always free, and the lock queue is what takes you down
Why a metadata-only migration still took the site offline for forty minutes, how a waiting ALTER blocks every query behind it, and why lock_timeout is the setting that turns a migration into a retry.
- Soft deletes are a schema decision that breaks every query you write afterwards
Why deleted_at IS NULL leaks into every index, breaks unique constraints in a way that only shows up when a user re-registers, and moves referential integrity out of the database and into whichever service remembers to filter.