Why Database Schema Changes Become Irreversible at Scale

Learn how schema, primary key, partitioning, and collation decisions create long-term operational risks—and how engineering teams can make safer database changes.

The Product Leader’s Guide to Effective Messaging (Sponsor)

Notifications extend your product beyond its interface. They keep users informed, bring them back to your product, and help them stay on track to accomplish their goals.

But messaging systems rarely stay coordinated for long.

Product, engineering, growth, and customer success teams start adding communications independently. Each message may make sense on its own, while the combined experience becomes noisy, repetitive, and difficult to control.

An effective strategy, therefore, requires careful consideration and thoughtful decisions to keep users engaged and subscribed.

In this deep dive, learn how to build a more effective product messaging strategy using behavioral data, event-based triggers, cross-channel orchestration, batching, personalization, and preference centers.

Read the product leader’s guide to effective messaging →

Application teams are accustomed to reversibility.

When a new release causes errors, engineers can restore the previous container image, disable a feature flag, redirect traffic to the old deployment, or revert the code that introduced the problem.

Databases do not behave the same way.

A database migration changes the persistent state shared by applications, analytics systems, event pipelines, reporting tools, and external integrations. Once applications begin writing data according to a new schema, restoring an older application version may no longer restore the system to its previous state.

The application can move backward. The data usually cannot.

This is the database rollback illusion: the belief that because a migration can be expressed as code, it can be reverted as easily as code. At small scale, that assumption may survive. At enterprise scale, it can turn one seemingly simple schema decision into years of operational cost.

Why database reversibility is different

A code rollback replaces one implementation with another. The underlying state generally remains intact.

A database migration can change the structure, meaning, representation, or physical organization of that state.

Consider an application that originally stored an order using three statuses:

• pending

• complete

• cancelled

A later release introduces a more detailed workflow:

• submitted

• validated

• processing

• settled

• failed

• cancelled

Once the new application begins writing these values, the previous application version may no longer understand the database. Rolling back the binary could cause errors, incorrect status displays, failed background jobs, or invalid business decisions.

The schema might still exist. The old application might still start. But the overall system is no longer compatible with its previous state.

Database irreversibility generally appears in four forms.

Transactional irreversibility

PostgreSQL supports transactional DDL for many operations. A migration executed inside a transaction can often be rolled back before it commits.

That protection ends at the transaction boundary.

Once the migration has committed and production traffic has resumed, reversing it becomes another production migration. The reverse operation must acquire locks, preserve valid writes, account for downstream dependencies, and remain compatible with every application version still running.

The ability to roll back an uncommitted transaction should not be confused with the ability to reverse a production state change.

Physical irreversibility

Some database changes rewrite tables, rebuild indexes, copy data, or reorganize rows on disk.

Changing a column type, modifying a primary key, repartitioning a table, or changing physical storage decisions may require processing every row in a large dataset. The SQL statement might be one line long, but the operational work grows with the amount of data already stored.

A migration affecting a small internal table may finish in seconds. The same logical change against a multi-terabyte transaction table may generate enormous amounts of write-ahead log, consume available disk space, delay replicas, and compete with production traffic for I/O.

The schema definition may be reversible. The physical work is not free.

Semantic irreversibility

The most difficult migrations change what the data means.

Suppose a system converts monetary values from a floating-point representation into an integer number of cents. A reverse migration cannot necessarily recreate the exact original values after rounding rules have been applied.

The same problem appears when teams:

• Merge several fields into one representation

• Split one entity into multiple entities

• Replace free-form values with an enumeration

• Introduce a new state machine

• Deduplicate customer records

• Add stricter uniqueness rules

• Change tenant ownership models

• Delete information considered obsolete

Once information has been normalized, rounded, merged, truncated, or removed, recreating the original meaning may be impossible.

Ecosystem irreversibility

A production database rarely belongs to one service.

Its tables, identifiers, and business concepts can spread into data warehouses, caches, APIs, event streams, dashboards, machine-learning pipelines, audit systems, customer exports, and third-party integrations.

A primary key introduced in one PostgreSQL table may eventually appear in:

• Foreign keys across dozens of tables

• Kafka events

• Elasticsearch documents

• Redis keys

• Object-storage paths

• Data warehouse dimensions

• Customer-facing URLs

• Billing exports

• Audit logs

Changing that key is no longer a local DDL operation. It is a distributed systems migration.

Not every difficult migration is truly irreversible

Engineering teams should distinguish between three categories of database change.

The first category contains changes that are both technically and operationally easy to reverse. These may include adding a view, changing a session parameter, creating an unused index, or introducing a nullable column that no application depends on yet.

The second category contains changes that are technically reversible but operationally disruptive. Examples include changing a heavily used column type, rebuilding a major index, validating a large constraint, or rewriting a high-traffic table.

The third category contains decisions that become deeply embedded in the system. Primary key strategies, partitioning models, sharding keys, tenant boundaries, event identifiers, and collation assumptions can all fall into this group.

The risk does not come from the SQL syntax alone.

A more useful model is:

Migration risk = data volume × write rate × dependency fan-out × compatibility duration × business criticality

A column change on a 50,000-row reporting table is not equivalent to the same change on a payment ledger receiving thousands of writes per second.

The DDL may be identical. The production consequences are not.

Column changes become infrastructure projects

Changing a column from INTEGER to BIGINT sounds routine. The new type simply supports a larger range.

On a sufficiently large table, however, the change may require PostgreSQL to rewrite stored data and rebuild related indexes. It may create substantial write-ahead log traffic, increase replica lag, require additional disk capacity, and contend with application workloads.

Lock acquisition can be just as dangerous as the physical rewrite.

Many ALTER TABLE operations require strong table locks. Even when the migration itself is fast, it may wait behind a long-running transaction. Once the lock is granted, application queries can begin queuing behind it.

This produces a familiar failure pattern:

1. A migration waits for a lock.

2. Application queries continue arriving.

3. The migration finally obtains the lock.

4. New application requests begin waiting.

5. Connection pools fill.

6. Latency rises across unrelated services.

7. The database appears unavailable.

The safest response is rarely to execute one large alteration directly against the existing column. Mature teams use an expand-and-contract migration.

The new column or table is introduced without deleting the old one. Applications temporarily write both formats. Historical data is backfilled in controlled batches. Engineers compare old and new representations, move reads gradually, and retain the previous structure until the new path has proven stable.

This approach takes longer, but each stage is observable, interruptible, and easier to recover from.

Constraints are safer when introduced in stages

Adding a NOT NULL, foreign key, or check constraint can appear harmless. In reality, validating an existing table may require PostgreSQL to inspect a large volume of historical data.

A safer method separates enforcement for new writes from validation of existing rows.

For example:

ALTER TABLE orders
ADD CONSTRAINT orders_reference_nn
CHECK (reference IS NOT NULL) NOT VALID;

ALTER TABLE orders
VALIDATE CONSTRAINT orders_reference_nn;

ALTER TABLE orders
ALTER COLUMN reference SET NOT NULL;

ALTER TABLE orders
DROP CONSTRAINT orders_reference_nn;

The first step prevents new invalid data without immediately performing a full historical scan. The existing rows can then be validated separately under controlled conditions.

This illustrates an important principle: the final schema is only part of the design. The sequence used to reach it determines whether the migration is safe.

Primary keys spread farther than teams expect

Primary keys are among the most consequential database decisions because they rarely remain inside their original tables.

A team may initially choose between a BIGINT sequence and a UUID based on developer convenience. Years later, that identifier may appear across thousands of indexes, events, logs, and API calls.

Random UUIDs, particularly UUIDv4 values, can create less-localized B-tree insertion patterns than sequential integers. They are also wider than BIGINT values, increasing the size of primary indexes and every foreign-key index that stores them.

But the conclusion should not be that UUIDs are always a mistake.

UUIDs provide decentralized identifier generation, reduce coordination between writers, and are useful when records are created across regions or outside the primary database. Time-ordered identifiers such as UUIDv7 can also provide better index locality than purely random UUIDs.

The right choice depends on the system:

• BIGINT is compact and efficient for database-local identity.

• UUIDv4 supports decentralized generation but creates random insertion patterns.

• UUIDv7 provides decentralized generation with time ordering.

• A system can use a compact internal key and a separate public identifier.

• Composite identifiers may be appropriate when tenant isolation is central to the data model.

The dangerous decision is not choosing one particular format. It is choosing an identity model without considering how widely it will propagate.

Before selecting a primary key, teams should evaluate:

• Index width

• Foreign-key fan-out

• Public exposure

• Distributed generation

• Insert locality

• Enumeration risk

• Sharding requirements

• Cross-system interoperability

• Expected migration path

Changing the answer later may require rewriting nearly every system that handles the entity.

Partitioning locks the database into a workload model

Partitioning is often introduced to solve growth, retention, or maintenance problems.

A transaction table might be partitioned by created_at because most queries filter by date and old data must be removed regularly. PostgreSQL can then prune irrelevant partitions and drop expired data efficiently.

That strategy performs well only while the workload continues to align with time.

Suppose the application later evolves and most requests become:

SELECT *
FROM transactions
WHERE customer_id = $1;

If the query does not constrain the partition key, PostgreSQL may need to examine many partitions. Local indexes on customer_id can help, but the system may still perform repeated planning and index operations across a large number of partitions.

The partitioning strategy has become a workload contract.

Changing the partition key later may require creating a new table, copying historical data, duplicating writes, rebuilding indexes, validating row counts, and switching applications to the new structure.

Partitioning decisions should therefore consider not only today’s queries, but also plausible product changes.

Teams should ask:

• Which predicates dominate current workloads?

• Will customer, tenant, region, or status become a primary access path?

• How will the table be queried during incidents?

• What retention operations must remain efficient?

• Will global uniqueness be required?

• How many partitions will exist in three years?

• Can the application tolerate cross-partition operations?

• What would repartitioning require at projected scale?

A partition key is not just a storage decision. It is a forecast about the future behavior of the product.

Collation decisions can surface years later

Collation controls how text is compared and sorted. It can affect ordering, case conversion, pattern matching, uniqueness, and index behavior.

Many systems operate for years before discovering that their text assumptions do not match the needs of new regions, languages, or customer datasets.

A collation problem may emerge when:

• The application expands into a new country

• Case-insensitive uniqueness is required

• Database and operating-system collation versions diverge

• Sorting differs between application and database layers

• Indexes no longer match updated collation behavior

• A previously ASCII-focused workload becomes multilingual

Modern PostgreSQL installations can apply explicit collations to columns and expressions, which provides more flexibility than relying entirely on the database default. Even so, correcting deeply embedded collation assumptions may require rebuilding indexes, creating new columns, migrating databases, or replacing a cluster.

The problem is rarely visible when the first table is created. It appears later, once customer expectations and data diversity have grown.

Backups do not provide an application-safe rollback

Point-in-time recovery is essential for disaster recovery. It allows operators to restore a database from a base backup and replay write-ahead logs to a chosen point.

It does not provide a clean rollback for every failed migration.

Imagine that a database migration is deployed at 10:00 a.m. and discovered to be faulty at 11:00 a.m. During that hour, the platform receives valid payments, customer updates, support messages, and new orders.

Restoring the entire database to 9:59 a.m. would remove the faulty migration, but it would also remove one hour of legitimate business activity.

The recovery system does not know which writes were caused by the migration and which writes must be preserved.

Backups protect against catastrophic loss. They do not replace application compatibility, staged migrations, or repair procedures.

For a failed database deployment, the safer recovery path is usually forward-moving:

1. Stop the faulty application behavior.

2. Preserve all writes made during the incident.

3. Restore compatibility through a patch or feature flag.

4. Identify records affected by the migration.

5. Repair the data using an auditable process.

6. Complete or unwind the schema transition through another controlled migration.

Database recovery frequently means moving forward from a bad state rather than returning the entire organization to an earlier one.

Why migration linting is no longer enough

Many engineering teams use migration linters to detect unsafe DDL.

These tools can flag operations that:

• Acquire strong locks

• Rewrite tables

• Add columns with problematic defaults

• Build blocking indexes

• Validate constraints immediately

• Drop structures still referenced by applications

This is valuable, but static analysis sees only the migration statement.

It may not know that the affected table receives 20,000 writes per second. It may not know that the proposed partition key appears in only 4% of production queries. It may not know that a supposedly unused column still feeds an executive dashboard or a change-data-capture pipeline.

The next stage of database governance is workload-aware migration analysis.

This is where platforms such as DeepSQL fit into the database change process.

DeepSQL combines schema analysis with production workload information, including query patterns collected through mechanisms such as pg_stat_statements. Rather than evaluating a migration only as SQL, it can assess the change against how the database is actually used.

For example, a traditional linter might confirm that a proposed partitioned table is syntactically correct. A workload-aware system can ask whether real production queries frequently include the proposed partition key.

A more complete migration review should consider:

• Table and index size

• Estimated lock type and duration

• Whether the change rewrites stored data

• Write rate during the migration window

• Replication and WAL impact

• Query predicates observed in production

• Foreign-key and application dependencies

• Data-growth projections

• Long-running transactions

• Available disk headroom

• Mixed-version application compatibility

• Staged alternatives to the proposed DDL

This turns database review from a static code check into an operational risk assessment.

Workload-aware tools still require engineering judgment

A tool can analyze historical queries, but it cannot perfectly predict future product behavior.

Production workload data may be:

• Biased toward recent traffic

• Missing seasonal workloads

• Incomplete because statistics were reset

• Dominated by one large customer

• Unrepresentative of upcoming features

• Missing emergency and administrative queries

• Aggregated in ways that hide tenant-specific behavior

A recommendation based on observed queries should therefore be treated as evidence, not unquestionable truth.

Teams evaluating DeepSQL or a similar platform should also consider:

• What database privileges the tool requires

• Whether raw query text leaves the environment

• How credentials are stored and rotated

• Whether sensitive query parameters are collected

• How recommendations are explained

• Whether engineers can reproduce the analysis

• How the product handles incomplete workload data

• What happens when historical behavior conflicts with planned architecture

Workload-aware review improves decision quality, but it does not replace capacity planning, architecture review, or product forecasting.

Database migrations need their own control plane

Database changes should be governed more like infrastructure deployments than ordinary code commits.

A mature migration process should produce at least five artifacts.

1. A risk classification

Every migration should be classified according to:

• Lock behavior

• Rewrite requirements

• Affected data volume

• Write rate

• Dependency fan-out

• Replication impact

• Recovery complexity

• Business criticality

This allows routine additive changes to move quickly while directing high-impact operations into deeper review.

2. A compatibility matrix

Teams should document which application versions can operate against each schema state.

A rollback is not safe merely because the old application can connect to the database. It must still understand data written by the newer version.

The matrix should identify:

• Which versions can read the old schema

• Which versions can read the expanded schema

• Which versions write the new representation

• When dual writes begin and end

• When the old structure becomes unused

• When destructive cleanup becomes safe

3. A staged execution plan

High-risk migrations should separate:

• Additive schema changes

• Application deployment

• Dual writes

• Historical backfill

• Data validation

• Read switching

• Observation

• Destructive cleanup

Each phase should be independently measurable and safe to pause.

4. Explicit abort conditions

A migration plan should define when automation or operators must stop the operation.

Useful signals include:

• Database CPU

• I/O saturation

• Lock wait duration

• Application latency

• Error rates

• Connection-pool usage

• WAL generation

• Replica lag

• Backfill throughput

• Data-parity mismatches

• Dead tuples and table bloat

“Stop if the database becomes slow” is not an operational rule. Thresholds must be defined before the migration begins.

5. Evidence before destruction

The old column, table, index, or code path should not be removed merely because the new version has been deployed.

Cleanup should require evidence that:

• No production reads use the old structure

• No writes depend on it

• Backfill validation has passed

• Downstream consumers have migrated

• Rollback windows have expired

• Observability shows stable performance

• Recovery procedures have been tested

Destructive changes should be the final step, not the first sign that a migration succeeded.

The database is not the liability

The database is not inherently a liability. It is the system preserving the organization’s most important state.

Its durability is exactly why careless decisions become expensive.

The real liability is allowing high-impact state changes to pass through the same process as ordinary application code. A pull request review that is sufficient for a stateless API change may be dangerously inadequate for a migration affecting years of customer data.

At scale, the safest database migration is rarely the most concise SQL statement. It is the migration that:

• Maintains compatibility across application versions

• Limits locking and physical work

• Exposes progress through metrics

• Can be interrupted without corrupting state

• Validates data before switching traffic

• Preserves legitimate writes during recovery

• Delays destructive cleanup until there is evidence

Code can often move backward.

Production data systems usually have to move forward—carefully.

Today’s newsletter is also brought to you by:

Wispr Flow — The voice-to-text AI that turns speech into clear, polished writing in every app.

CodeRabbit — Cut code review time & bugs in half, instantly