Schema evolution, online indexing, soft delete, and ticket servers
Changing the database while it serves — the four topics grouped because they're all "database under traffic."
Concept → your projects
Schema evolution rules (Postgres):
- Additive changes (new nullable column, new table, new index) are safe and lock-light.
ALTER TABLE ADD COLUMN ... DEFAULT— Postgres 11+ stores the default as a catalog note, no table rewrite; old rows read the default virtually. Rewrites only happen on type changes/altered volatility.NOT NULLon a populated column: add as CHECK (NOT VALID) → validate separately (takes only SHARE UPDATE EXCLUSIVE, doesn't block writes) → attach as constraint. The NOT VALID/VALIDATE two-step is the interview answer for safe constraint addition.- Renames are metadata-only (but break every client — coordinate).
Online indexing: CREATE INDEX CONCURRENTLY — two table scans, no exclusive lock; must run outside a transaction; can leave an INVALID index on failure (drop and retry). Contrast: zoth's goose migrations run at startup inside a transaction — fine for its scale (tables are small), but at production scale, index creation moves out of migration files into concurrent sessions. Know which one your projects chose and why: zoth = bootstrap-time migrations (embedded, advisory-locked), traverse = prisma migrate deploy at container start (same philosophy). Both are "stop-the-world-lite" — acceptable because deploys are already gated.
Traverse's scheduler_evaluation migration is a live example: adding predictedAt, predictedIntervalDays, predictedStability, modelVersion, predictionFeatures columns to Revision — additive, nullable, zero downtime, existing rows read as NULL (historical data = 'not predicted'). Schema evolution as feature flag: the harness columns exist before the calibration data does.
Soft delete: deleted_at timestamp; every query must filter it (the tax), unique constraints fight ghost rows (username taken by a deleted account? — traverse solves it by 7-day grace + recover flow; the general fixes: partial unique indexes WHERE deleted_at IS NULL, or composite uniques including a deletion epoch). Traverse uses soft delete only where the product needs undo (User.deletedAt, 7-day recovery with password/flow); zoth uses hard delete + audit chain. The rule: soft delete is a product feature (undo), not a default — and if you adopt it, adopt the partial-index discipline with it.
Ticket servers: centralized monotonic ID issuing (Flickr's famous two-ticket-machines with interleaved parity: odd/even ranges so each survives the other's death). Purpose: strictly increasing IDs without coordinating every insert. You don't run one — traverse uses Postgres sequences (autoincrement), zoth uses UUIDs — and the honest comparison is exactly Day 41's topic. The reason ticket servers exist at all: autoincrement doesn't survive sharding (two DBs generate colliding sequences). Ticket server = "sequence as a service" so shards stay ordered.
Migration tooling comparison (yours)
| zoth (goose) | traverse (Prisma Migrate) | |
|---|---|---|
| When | API startup, embedded FS, advisory lock | container start, migrate deploy |
| Verbatim | SQL files — the migration IS SQL | generated SQL from schema diffs, sha-pinned |
| Failure mode | fatal before ListenAndServe | container exit 1 |
| Drift protection | sqlc reads migrations (partial) | edited applied migration = deploy failure |
Both: migrations at boot, fail fast, never serve on drift.
Bulletproof your resume
Say these answers aloud before moving on
These are the follow-ups an interviewer will actually ask about your projects.
How do you add a NOT NULL constraint to a table with live traffic?
Three steps: (1) ALTER TABLE ... ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID — takes a brief lock, doesn't scan; (2) ALTER TABLE ... VALIDATE CONSTRAINT — scans but only SHARE UPDATE EXCLUSIVE, writes continue; (3) ALTER TABLE ... ALTER COLUMN SET NOT NULL — after validation it's a catalog flip. My projects didn't need this (both run migrations at boot with downtime-gated deploys), but that's the honest boundary: the moment zero-downtime deploys matter, migrations move from boot-time to online procedures, and expand/contract becomes the law.
What is expand/contract?
The zero-downtime schema-change protocol: expand (add new column/shape, dual-write), migrate (backfill in batches), switch reads, contract (remove old shape) — each step independently deployable and reversible. My practical taste of it: traverse's topic column is set-once and AI-populated — new column, old rows NULL, backfill via natural traffic rather than a migration job. At scale that backfill becomes a batched worker with primary-key cursoring — the same shape as my broadcast fanout cursor.
When is soft delete the wrong choice?
When it's adopted as a safety blanket: every query pays the WHERE deleted_at IS NULL tax, uniques fight ghost rows, and 'deleted' data still occupies indexes and compliance scope (GDPR erasure isn't a flag). Zoth deliberately has zero soft deletes — the audit chain preserves history, hard deletes keep queries honest. Traverse soft-deletes exactly one entity (users, 7-day recover window) because undo is a product requirement there. The rule: soft delete per-feature, never per-schema.