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.
Terms in this content
More in data
All in this category →Related posts
A PostgreSQL advisory lock for concurrent content initialization
Content initialization is one ordered pipeline: create tables, correct the schema, seed static data, and upsert notes and courses. Even idempotent steps can observe an intermediate state when an operator double-clicks o…
Make integration-test migration replay fail closed
If an integration test cannot create its schema but continues into entity tests, the delayed failures lose the original cause. A missing migration directory, zero SQL files, a read error, or one failed statement is a bo…
Scheduled jobs and APScheduler
Periodic tasks show up in any backend. Nightly aggregates, external data collection, expired-token cleanup. At small scale, cron or an in-process scheduler is enough; at larger scale, distributed queues and workers appe…
REST API introduction
When writing an API on top of HTTP, REST is the word that comes up most often. It stands for Representational State Transfer, organized in Roy Fielding's 2000 doctoral dissertation.