Data Lifecycle, IDs & Safe Migrations

Begin with ownership and lifecycle

A record has more than a schema: someone owns it, creates it, changes it, reads derived copies and eventually deletes or archives it. Document the authoritative store, retention clock, backup policy and downstream copies. An expired cache entry is not proof that the underlying personal record was deleted.

For a learning portal, separate public course metadata, private student progress, operational logs and billing records. Give each its own lifecycle rather than putting every payload into one permanent event stream. A durable business record and a disposable diagnostic trace should not share the same retention assumption.

Choose IDs for the operations they support

The UUIDv7 format contains a millisecond Unix timestamp and additional bits whose generation follows the standard's requirements. Implementation ordering behavior must be checked rather than inferred from the name.

A resource ID answers which record; an idempotency key answers which attempted operation. One user can legitimately create two applications with different operation keys. Conversely, retrying one creation must reuse its operation key. Public unpredictability can reduce casual enumeration, but ownership checks remain mandatory.

Work a collision estimate

Suppose a short-link service chooses seven base62 characters uniformly. Its space is about 3.52 trillion values. With one million generated links, the birthday approximation for collision probability is 1 - exp(-n(n-1)/(2M)), about 13.2%. A large space does not mean collisions are impossible.

Keep a unique index, retry a collision with a newly generated code and cap attempts. If links grant access, separately analyze guessing risk, expiry and rate limits. A predictable sequence encoded as base62 is compact but still predictable.

Expand, migrate, contract

A zero-downtime change must survive old and new application versions running together. To replace full_name with structured name fields:

1. Expand: add nullable fields while retaining the old field and old readers.
2. Deploy compatible writers; define which representation wins if both are supplied.
3. Backfill existing rows in bounded batches.
4. Verify missing rows, mismatches and ongoing writes.
5. Switch reads, observing a canary before the whole fleet.
6. Stop old writes only after old deployments and background workers are gone.
7. Contract: remove the legacy field after the rollback window.

A deploy rollback cannot resurrect a dropped column. Delay destructive cleanup until recovery no longer depends on the old representation. Event consumers, exported files and mobile clients may outlive the web deployment.

Backfills without lost updates

A naive job reads version 7, calculates a new value and overwrites version 8 written by a live request. Use an atomic version predicate, or derive and update within an appropriate transaction. Conflicts should be retried or recorded for repair rather than silently counted as completed.

Persist a keyset checkpoint, batch size, code version, success count and conflict count. Limit the backfill's concurrency and I/O. Pause when replica lag, lock waits or user latency exceed the operational budget. Avoid offset pagination over a changing table: a deleted row can shift the next batch.

For ten million rows at a hypothetical 500 rows/second, the raw minimum is about 5.6 hours. Index writes, replication, retries and throttling increase elapsed time. Calculate these separately before promising a maintenance window.

Online indexes and constraints

A schema change can be logically small and operationally disruptive. Inspect its lock mode, table rewrite behavior, transaction duration and replica effects. PostgreSQL concurrent index creation reduces write blocking but performs additional work, has restrictions and can leave an invalid index after failure. It is not a general promise that every migration is lock-free.

Check duplicate values before a unique index. Validate a new constraint against existing data and coordinate its enforcement with application writes. Do not add every possible index: each consumes storage and increases write and maintenance work. Rehearse changes on representative data with lock and statement timeouts.

Move a shard while writes continue

Source snapshot at position P
        -> destination copy
Source changes after P -> catch-up stream -> destination
Routing generation G -> coordinated cutover -> generation G+1
Old owner fenced -> destination authoritative

Record a snapshot/change-stream boundary. Compare counts and checksums, catch up changes and cut over ownership with a versioned routing decision. An old client's write must be rejected or redirected after the ownership transfer. Two independent databases accepting authoritative writes during a migration create reconciliation work.

Dual reads can diagnose discrepancies, but a blind fallback to the old shard can reintroduce stale data after cutover. Define the exact transition and rollback point. Keep the old copy read-only until verification completes, then remove it according to retention policy.

Deletion reaches derived data

Deleting a user from the primary may leave search documents, objects, cached pages, analytics extracts and events. Publish an authorized deletion intent, track each subsystem's completion and retry incomplete work. A restore procedure must reapply deletion decisions before recovered data becomes visible.

Tombstones prevent an older replicated value from resurrecting a deletion. Retain them long enough for the repair protocol and disconnected replicas; garbage-collecting them prematurely can bring records back. Backup expiry, legal retention and operational deletion are different policies that must be documented.

Exercise and review

Design a migration from single-region progress storage to tenant-partitioned storage. Produce the old/new schema, checkpoint algorithm, conflict rule, cutover generation, rollback boundary and deletion inventory. Inject a crash halfway through a batch and a live update during a copy. Success means users retain their latest state, not merely that the batch finished.

Continue to multi-region design and transactions.