Idempotent migrations and partial-failure recovery
An operational database cannot be changed in one shot like a fresh database. Preserve existing rows, converge to the same state on repeated runs, and expose enough failure context for a safe retry.
Table of contents
An operational database cannot be changed in one shot like a fresh database. Preserve existing rows, converge to the same state on repeated runs, and expose enough failure context for a safe retry.
1. Expand/contract order
- Add new columns and indexes in a compatible shape.
- Deploy code that can read and write both old and new paths.
- Backfill and verify, then switch the default path.
- Remove obsolete contracts and constraints in a separate cleanup step.
The content schema applies the same approach to content_kind=note by repairing the legacy blog/edu CHECK. Existing data is preserved, the new contract is added, and the console rolls the transaction back if the migration fails.
2. Idempotent seeding
Declare stable keys such as (language, content_kind, category_slug, slug) and (series_slug, language, slug), then use ON CONFLICT. Preserving publication dates and user-entered duration during an upsert is also part of idempotency. If one file fails, return the error list instead of reporting the whole run as successful.
3. Design indexes with queries
Public lists filter and order by language, kind, publication state, and time. Category, popular, and keyword reads need indexes that match their real predicates. Array keyword search pairs a GIN index with keywords @> ARRAY[$1]::text[]. Compare representative queries with EXPLAIN (ANALYZE, BUFFERS) on staging data after adding an index.
4. Recovery criteria
- Running the same migration twice is a no-op without errors.
- A file failure is observable through status, path, and retry guidance.
- After a failed transaction, the same command can normalize the database.
- After restore, the order remains schema repair → reference data → file seed.
Deleting a database or recreating a volume is not a recovery test. Use a separate restore target and read-only checks.
Rollback by phase
| Phase | Compatible state | Failure action | Do not remove |
|---|---|---|---|
| Expand | Old and new code both work | Disable only new objects | Existing columns and constraints |
| Backfill | Both representations coexist | Resume from checkpoint | Source values |
| Switch | New path is preferred | Return via feature flag | Rollback code |
| Contract | New path is proven | Execute restore plan | Unverified source data |
INSERT INTO target (stable_key, value)
VALUES ($1, $2)
ON CONFLICT (stable_key) DO UPDATE SET value = EXCLUDED.value;
ON CONFLICT alone does not make a seed idempotent. Separate user-owned edits that must survive replay from derived values that should follow the source, column by column.