Safe evolution — extended reference

This reference preserves the earlier detailed chapter and its citations. Start with the guided explanation, then use these sections for additional mechanisms and qualifications. References to the original lesson sequence are historical.

Part 7: Building and operating applications on these engines

Prerequisites: Lesson 40 and the preceding foundations.

Learning outcomes

• Plan schema and protocol evolution across mixed old and new versions.
• Trace online index construction, backfill, catch-up, cutover, and rollback under concurrent writes.
• Calculate migration load and retained compatibility obligations.

A schema change is a period of coexistence

A new application version writes a required field. During rolling deployment, old clients still omit it. A backfill fills existing rows, but old writers create new incomplete rows after the backfill passes their range. The final constraint fails, or worse, the application assumes a condition that never became true.

Safe evolution treats a migration as a protocol between versions, writers, readers, and background work. It includes compatibility, concurrent mutation, validation, resource limits, rollback, and eventual cleanup. A schema statement alone rarely describes the entire transition.

Expand, migrate, validate, contract

An expand phase introduces a representation compatible with old behavior. New code can read both forms or use defaults. A migration phase populates or transforms data while ordinary writes continue. Validation establishes that the required condition holds. A contract phase removes obsolete fields or behavior only after no supported client depends on them.

The sequence is not mechanical for every change. Some type changes require a new column, dual writing, and a cutover. Others can use an engine’s supported in-place or metadata-only operation. The design should use the documented safe facility while understanding its locking and resource behavior.

A rollback after data transformation may not be equivalent to rolling back application code. Old code can misinterpret newly written values. The migration plan must identify its reversible and irreversible boundaries before deployment.

Concurrent backfills

A backfill walks existing data and creates a new representation. It needs a rule for updates occurring during the walk. Reading an old value and writing its transformed version after a newer application update can overwrite correct new state.

Version checks, conditional updates, dual-write protocols, change capture, or engine-supported online migration can address the race. The chosen method must define ordering and retry behavior. Merely running the backfill faster does not prove the race impossible.

For an illustrative 900 million rows at 10,000 rows per second, processing takes 90,000 seconds, or 25 hours. Retries, throttling, validation, and replication can extend that. A migration expected to run for a day must coexist safely with a day of ordinary writes and possible failures.

Online index construction

Building an index needs a consistent representation of existing rows plus changes that occur during construction. An engine can use multiple scans, change tracking, or another protocol. The index should not become authoritative for queries or uniqueness until it is valid under the product’s rules.

PostgreSQL documents concurrent index construction with specific restrictions and failure behavior. An unsuccessful online build can leave state requiring cleanup. The presence of a catalog entry does not automatically mean the index is valid and ready for every purpose.

Unique indexes add a correctness obligation. Concurrent duplicates must be prevented or detected before the constraint is relied upon. Applications should not begin assuming uniqueness merely because the build has started. Validation and enforcement boundaries need to be explicit.

Locking and metadata changes

Some schema changes acquire locks that conflict with ordinary traffic. Even a fast metadata change can wait behind a long transaction, while new requests queue behind it depending on lock policy. The observed outage can therefore exceed the statement’s actual modification time.

A rollout should inspect the documented lock mode, expected wait behavior, and cancellation options. A lock timeout can prevent indefinite waiting but may leave an incomplete migration state that automation must handle. Retrying blindly can repeat disruption.

Catalog changes also invalidate plans and cached schema metadata. Clients may need to reprepare statements. A protocol version change can require compatibility negotiation or a staged server rollout. Schema and wire compatibility belong in the same operational picture.

Resource load and replication lag

Backfills and index builds consume CPU, I/O, log space, cache capacity, and replication bandwidth. A primary can remain responsive while followers fall behind. Retained logs may then grow until storage is threatened.

Rate limits should respond to relevant pressure, not only a fixed rows-per-second target. A wide row and a narrow row have different costs. Compaction and checkpoint schedules can overlap the migration. Headroom should include failure recovery during the transition, not assume every component remains healthy for its full duration.

A migration can also change query plans before it finishes. New statistics, index availability, or data distribution can alter performance. Observability should track endpoint latency and errors alongside migration progress and engine resources.

Versioned protocols and stored code

Old and new application versions may encode fields differently or interpret an enum differently. A tolerant reader can accept compatible additions, but silently ignoring a field that changes business meaning can be unsafe. Versioning should identify semantic changes, not just syntax.

Database-side functions or modules create another deployment boundary. Updating logic without changing data can be safe under the engine’s documented mechanism, while incompatible schema changes require a separate plan. A generated client interface reduces manual mismatch but does not eliminate old deployed clients.

Long-lived connections and subscriptions can survive a deployment. The server must define whether they continue with compatible behavior, receive a schema update, or reconnect. Disconnecting every client simultaneously can create a traffic spike, so even necessary reconnects may need controlled rollout.

Validation and cutover

Validation should examine the intended invariant, not just row counts. A new index can contain the right number of entries and still map some keys incorrectly. A copied column can be populated everywhere but contain stale transformations. Sampling is useful evidence but may not establish a strict invariant.

Parallel old/new reads can compare results under an aligned snapshot or known freshness boundary. Differences need interpretation when the systems observe different moments. A cutover should use an explicit authority generation or routing switch, with a way to prevent stale writers from continuing on the old representation.

Cleanup should wait until rollback and compatibility windows close. Retaining obsolete paths forever creates complexity and security risk. Removing them too early eliminates recovery options. The transition should include a documented completion condition.

Compatibility is a relation between readers, writers, and stored data

A schema change is safe only if every active code version can correctly interpret the states it may encounter. A new field can be optional for readers while old writers omit it. A new constraint can be invalidated by those old writers even after a backfill made all existing rows valid.

Consider a hypothetical customer record gaining a normalized email field. New readers prefer it but can derive a value from the original email when absent. New writers maintain both representations. Old writers still modify only the original field. During the overlap, a one-time backfill is insufficient because an old writer can make the derived field stale afterward.

The design must either prevent old writers from making incompatible changes, maintain the new field at a shared database boundary, or track and reconcile concurrent modifications. The mechanism follows the actual writer population, including scheduled jobs, imports, administrative scripts, and offline clients.

A deployment inventory should therefore identify producers as carefully as consumers. Updating the main web application does not mean the old format has stopped being written.

An expand-and-contract trace

Suppose an application renames display_name to preferred_name without losing data. A safe staged design can add the new field, deploy code that reads both and writes compatible state, backfill existing records under concurrency protection, validate coverage, migrate all readers and writers, and only later remove the old representation.

old schema
  -> add compatible representation
  -> deploy compatible readers/writers
  -> backfill with concurrent-write protection
  -> verify and monitor
  -> remove old dependencies
  -> contract schema after rollback window

Figure: Compatibility stages separate adding capability from removing the old contract. The arrows are gated by evidence, not only elapsed deployment time.

The exact dual-write strategy matters. Two independent application requests can fail between old and new writes. Updating both in one transaction or deriving one at a shared authoritative boundary avoids that gap. If asynchronous propagation is used, readers need an explicit freshness rule.

Rollback remains possible only while old code can interpret the resulting state. Dropping the old column or writing values the old version cannot parse can make a code rollback unsafe even if the binary is still available.

Backfills are concurrent algorithms

A backfill scans old records and populates a new representation while ordinary traffic continues. Its central race is overwriting a newer value with work computed from an older version.

A generic safe pattern reads the source and version, computes the result, and conditionally updates only if the source version remains unchanged. If the condition fails, it retries or leaves the record for a later pass. An engine-side expression executed under an appropriate transaction can avoid moving stale computation outside protection.

read source fields and version v
compute derived representation
update only where record.version = v and target still needs migration
if no row changed:
    re-read or defer; do not overwrite blindly
record durable progress only after the chunk outcome is known

This pseudocode does not prescribe a particular SQL dialect. It describes the required relationship between the read assumptions and the write. Version counters must themselves change on every relevant source update; a counter that some writer forgets to increment provides false protection.

Chunk progress also needs care. Marking a range complete before its writes commit can skip data after a crash. Repeating a committed chunk should be safe, using idempotent conditions or stable identities. The backfill is a resumable workflow, not merely a loop around UPDATE.

Choosing chunk boundaries

A keyset scan using a stable ordered identifier often avoids the cost and instability of repeatedly skipping an offset. However, new inserts can occur behind or ahead of the cursor, and key changes can move records. The migration must account for those possibilities through writer compatibility, a snapshot, repeated reconciliation, or another justified method.

Suppose a backfill handles 10,000 rows/s and there are 900 million rows. The execution floor is 90,000 seconds, or 25 hours. If it runs only at half rate during peak traffic, wall time increases. Retries, validation, replication, and maintenance add work beyond the row count.

Chunk size trades overhead against lock duration, log bursts, and restart granularity. A million-row transaction can be efficient in one dimension while retaining large versions and making rollback expensive. Smaller commits can keep latency predictable but require more progress tracking.

A good controller adjusts rate from measured resource pressure while guaranteeing progress. Pausing forever whenever load rises can leave the system in a prolonged dual-format state. The migration plan should include a deadline, safe degradation options, and an operational owner.

Online index construction has its own visibility protocol

An index built while writes continue must include changes that happen during construction. A naive scan followed by “index ready” can miss rows modified after the scanner passed. The engine needs a supported protocol that catches up concurrent changes and only exposes the index when it is valid under the query contract.

PostgreSQL documents distinct behavior and restrictions for concurrent index creation compared with ordinary creation. Those details are engine-specific and should be used directly rather than replaced with a hand-built imitation. CREATE INDEX

Even an online operation can consume substantial I/O, CPU, temporary space, and log bandwidth. “Online” usually means selected access remains possible, not that foreground latency is unaffected. Capacity planning must include the extra representation while old and new structures coexist.

An interrupted build can leave an invalid or incomplete artifact that needs explicit cleanup or retry under the engine’s rules. Monitoring should track the operation’s validity state, not merely the presence of a named index.

Constraint introduction needs two checks

A new constraint must address existing data and future writes. Validating only current rows leaves old writers free to reintroduce invalid state. Enforcing only new writes can leave historical violations that surprise readers assuming universal validity.

A staged process can enforce compatible write behavior, repair existing violations, validate the complete relevant dataset, and then allow application code to rely on the stronger invariant. Engine facilities for deferred validation or phased constraint addition vary and should be checked for the selected version.

A uniqueness change can reveal normalization issues. Two emails that differ in case or Unicode representation may become equal under a new rule. The migration needs a business resolution for conflicts. Silently deleting one record to satisfy an index is not a neutral technical operation.

Constraint validation can also race with writes if not protected by the engine’s protocol. A custom “SELECT to check then enable” sequence needs proof that no incompatible mutation occurs between those steps. Supported DDL mechanisms exist to manage exactly this class of transition.

Protocol compatibility outlives the main deployment

Clients can remain old for days or months. Mobile apps, embedded devices, scheduled jobs, and third-party integrations may not update with the server. Removing a response field or changing its meaning can break them even when the database migration succeeded.

Compatibility concerns meaning as well as syntax. Changing a timestamp from seconds to milliseconds while keeping its numeric type is syntactically compatible and semantically destructive. Reusing an enum value for a different state can make old clients perform the wrong action.

A protocol can add versioned fields, preserve old meanings, and negotiate capability where needed. Unknown fields should be handled according to the encoding’s contract. Unknown enum values need a safe fallback, not an assumption that every future value means success.

Retry identities must survive upgrades. If old and new clients construct operation namespaces differently, one logical action can be duplicated during a rolling transition. A migration should preserve the identity and result lookup contract across versions.

Large migrations can exhaust history retention

Backfills generate log and replica work. They can increase old-version retention when long snapshots are used. A migration that seems like a background CPU task can fill storage through log archives, replication lag, or delayed cleanup.

Assume an illustrative backfill updates 10,000 rows/s and creates 1 KB of log per row. That is 10 MB/s of logical log, or 864 GB over 24 hours before compression and replication. If archives retain every change for seven days, the temporary migration can significantly change retained capacity.

A long-running scan may also hold an old snapshot, preventing removal of versions created by normal traffic. Chunked snapshots can reduce retention but change the consistency model of the scan. The backfill algorithm must be designed for that model rather than casually switching isolation to save space.

Replicas need headroom to keep up. Throttling based only on primary CPU misses downstream saturation. Measure log lag, device queues, free space, and recovery margin as the migration progresses.

A failure-and-rollback scenario

A new application writes a compressed binary payload into a field old code expects as plain text. The deployment appears healthy, but a later unrelated problem triggers rollback. Old code now cannot read newly written rows. Rolling back the binary does not roll back persistent state.

A safe plan would retain a backward-readable representation, use a new versioned field, or declare the transition irreversible with a tested forward-repair path. The choice depends on data volume and compatibility requirements, but it must be made before writing the new format.

Now suppose dual-write code has a bug and the two representations diverge. Recovery needs an authority rule: which field is correct for each version and time interval? If both are edited independently, automated repair may lack enough evidence. Version tags and audit history can make reconciliation possible.

The lesson is that rollback is a data compatibility property. A deployment tool’s ability to run an old container is only one prerequisite.

Comparing engine-managed and application-managed evolution

An engine-managed schema change can centralize locking, catalog visibility, and validation. The application still needs to coordinate semantic changes and older clients. An application-managed new table or projection can offer more control but requires copying, catch-up, cutover, and cleanup protocols.

Neither choice makes the migration free. The relevant comparison includes temporary storage, log volume, read/write amplification, rollback, and operational visibility. PostgreSQL’s ALTER TABLE documentation is a concrete source for its particular behaviors and lock implications. ALTER TABLE

For an integrated application database, module updates may combine code and schema deployment. That can simplify packaging while making compatibility of persistent data even more important. A new reducer implementation must understand records created by older code, and client bindings must remain compatible with active clients under the chosen rollout contract.

Migration acceptance as evidence

A completed migration has more evidence than “the job exited.” It shows that all relevant writers use compatible behavior, the target representation is complete and valid, reads use the intended path, resource debt has returned to normal, and rollback or forward repair remains defined for the chosen window.

Only then should obsolete fields, indexes, and compatibility code be removed. Keeping them forever adds write and maintenance cost, while deleting them prematurely removes recovery options. A staged contract turns that timing into an explicit engineering decision rather than a guess.

A compatibility matrix exposes unsafe cutover

Table: Compatibility is explicit at each stage. Reaching the final row requires evidence that old producers and consumers can no longer act.

The matrix prevents a common planning omission: assuming that backfill completion is the same as writer migration. A record can be valid at the instant it is scanned and become invalid after an old writer updates it. Fencing or compatible shared maintenance closes that race.

It also clarifies rollback. Before contraction, old code may still be safe if new writes preserve its interpretation. After contraction, restoring old code alone is insufficient. The recovery path may require a forward fix or a data transformation, whose time and risks should be known.

The table does not require elaborate version negotiation for every small change. Adding a truly optional field can be simple. Its value is making the actual compatibility relation visible when persistent semantics, constraints, or external clients change.

A migration is complete when the new invariant is both established and continuously maintained. Deleting compatibility code is then a useful simplification because it removes duplicated writes and confusing authority. Doing so before that evidence exists exchanges a temporary complexity cost for a correctness failure.

Case connection and design conclusion

PostgreSQL’s ALTER TABLE and CREATE INDEX documentation provide concrete examples of operations with different locking, validation, and concurrent-build behavior. The course’s expand/migrate/contract scenario is architectural analysis around those implementation surfaces, not a claim that one generic sequence fits every database change.

A safe evolution plan names compatible versions, concurrent-write handling, validation, cutover authority, resource budgets, and rollback boundaries. Lesson 42 explains the observations and verification methods needed to know whether the running system still satisfies its contract during such changes.

Sources and further reading

• PostgreSQL 18 ALTER TABLE
• PostgreSQL 18 CREATE INDEX

Course index · Lesson plan