Managing NULL Constraints and Default Values During Schema Migration

Managing NULL constraints and default values during schema migration requires successful DDL within a staged sequence of changes. Automatic schema evolution for live CDC pipelines is safe only if destination schemas, in-flight events, and consumers stay compatible from start to finish.

Why the order of schema migration steps determines whether downstream systems break

A migration can succeed at the database level and still break downstream consumers. Adding a NOT NULL column with a default appears to protect everything at once. The statement completes, existing rows show valid values, and the table satisfies its constraint. Yet the CDC pipeline can present a different picture because database defaults and replication events do not change together.

The conflict is between enforcing integrity immediately and allowing older events to finish their journey. Those events may be missing a field that the destination now needs, even though the source table has no NULLs. Adding a nullable column or one with a default therefore has a different compatibility profile from adding a required column without a default. Staging resolves that conflict by preserving a permissive destination until the data and consumers are ready for stricter rules.

How database engines handle ADD COLUMN with defaults and NOT NULL, and why the version matters

Before PostgreSQL 11, adding a column with a non-null default caused a full table rewrite. Every existing row was rewritten, producing heavy I/O and a long lock on large tables. An Employees table with 50 million rows and heavy traffic makes that cost concrete. PostgreSQL 11 introduced fast ADD COLUMN with a constant default, storing the default in the catalog instead. Existing rows appear to contain it without physically storing it.

PostgreSQL 18 adds native NOT VALID semantics to NOT NULL constraints. The first statement enforces the constraint for new and updated rows without scanning the table. A separate statement validates existing rows under SHARE UPDATE EXCLUSIVE, eliminating the CHECK workaround. Enforcement and validation can therefore happen separately. That separation changes how teams plan the constraint operation, but it does not synchronize downstream schemas.

Teams need to know which version runs in production before they choose the order of migration steps. Older versions can impose rewrites and longer locks that newer versions avoid. Even when an operation runs faster, it can still be unsafe for replication. CDC compatibility remains constrained by the replication protocol, regardless of the cost of changing storage.

What CDC actually sees when DDL lands: the logical replication blind spot

PostgreSQL logical replication does not transmit DDL statements such as ALTER TABLE, ADD COLUMN, or DROP COLUMN. Its data-change stream is not a schema-deployment channel. A consumer cannot rely on that stream alone to learn and apply the corresponding schema change.

Catalog defaults deepen the distinction between table state and emitted changes. On PostgreSQL 11 and later, adding a constant default does not write that value into every existing row's WAL record. A subscriber-side default is likewise not a retroactive repair for existing replicated data. If the publisher starts sending a new column before the subscriber has the corresponding schema, the destination can reject the row or interpret it against the wrong mapping.

A CDC engine may detect a changed source schema and update its internal mapping, but that is a separate capability from logical replication itself. Events already in flight still have to get through as the schema changes. A Kafka topic can therefore contain older messages without the column and newer messages with it until consumers catch up. Automatic adaptation must handle both forms because a source-side DDL statement leaves the pipeline with a mix of old and new events.

The SQL Server CDC case: how the engine fills existing rows differently from the replication stream

Microsoft's SQL Server CDC documentation describes a direct divergence when a non-nullable column is added with a default constraint. Rows already in the table get the default, but CDC uses NULL for those rows instead. The live table and captured data disagree. The capture process sees the pre-DDL row image without a record of the default subsequently applied to existing rows, so the constraint cannot retroactively repair that captured view.

The same documentation identifies unsupported ALTER COLUMN conversions that can produce capture errors after the DDL succeeds. These include bigint to int and char/nvarchar to uniqueidentifier, DATE, or Numeric, with errors including 241, 245, 8114, 8115, and 8169. The CDC scan can fail later.

The documentation also warns against manually modifying CDC metadata. Setting start_lsn values in cdc.change_tables to NULL can cause Error 515, and changing metadata by hand can leave parts of the CDC configuration out of step with each other. The operational rule is to leave that metadata untouched. A discrepancy between table values and captured values requires a supported migration response.

The safe staged sequence for adding a NOT NULL column across a CDC pipeline

For a PostgreSQL logical replication pair, the subscriber must be ready before the publisher begins sending the new column. First, add the column on the subscriber as nullable, with no default and no NOT NULL constraint. It can then accept the new field without requiring it from older events.

Second, add the column on the publisher with its intended default and constraint, such as NOT NULL DEFAULT 'USD'. New publisher rows have a value in the column, and CDC events that follow begin to show it. The subscriber schema is already prepared to receive them. Reversing these operations would expose the destination to data it is not yet equipped to accept.

Third, backfill rows on the subscriber that have NULL because they arrived before the publisher changed. Use batched UPDATE operations with pauses to avoid saturating the write workload. Reads need not wait for the backfill to finish. A view in the application layer that uses COALESCE(new_column, default_value) can show the intended default while the stored values are being updated.

Fourth, confirm that no NULLs remain on the subscriber, set its default, and then apply NOT NULL. Applying the constraint earlier would make events from the pre-change window vulnerable to rejection. The destination's final integrity rule belongs after the data verification that justifies it.

Deploying the application follows the same expand-contract steps. Additive changes, including nullable columns, new tables, and new indexes, ship in their own migration before code starts using the new column. Destructive or tightening changes follow only after old code is gone. For schema-managed events, backward compatibility is checked before ALTER TABLE, and events are inspected afterward for the new field. Consumers must be able to read both event shapes while the change is underway. Artie, a managed CDC-as-a-service platform that streams row-level changes from operational databases to warehouses and adapts to source schema changes without pipeline downtime or manual intervention, is one option built around that sequencing requirement.

How AWS DMS handles NOT NULL and LOB columns, and what that requires practitioners to do explicitly

AWS DMS does not replicate secondary indexes, data defaults, or non-primary-key constraints by default. Its target-table creation behavior preserves source NOT NULL declarations for non-LOB columns, but LOB columns are an explicit exception. They are made nullable on the target. This means that a tool-created destination cannot be assumed to reproduce every source constraint or default.

AWS DMS Best Practices explains why LOB migration requires that exception. The process writes the containing row first without the LOB value, then updates the row with the LOB. A NOT NULL constraint would obstruct that intermediate state. Practitioners must therefore reapply the LOB constraint after migration completes rather than treat temporary nullability as the intended final schema.

Full Load + CDC introduces a separate sequencing restriction. DMS does not replicate source DDL changes made during the full-load phase. An ALTER TABLE during that time can keep CDC from working correctly after cutover. The required response is to freeze source DDL throughout full load.

Azure Database Migration Service presents the inverse risk in its documented SQL Server to Azure SQL Database offline migration behavior. Columns with default constraints and source NULL values arrive with their defined defaults substituted on the target. Instead of relaxing a constraint, this changes historical values. The distinction is consequential for audit records, so the specific toolchain's handling of NULLs and defaults must be established separately from its ability to move data.

Schema Registry compatibility modes as the enforcement layer for CDC schema changes

Schema Registry turns event compatibility into a gate before database DDL is applied. BACKWARD compatibility meets a common CDC requirement: optional fields can be added while newer readers can still read older events. A new required field without a default fails that requirement. The compatibility check protects the event contract, while the staged database sequence protects stored data and destination constraints.

Submit the candidate Avro schema to the registry's compatibility endpoint before running ALTER TABLE. A response reporting is_compatible as true permits proceeding with the schema-compatible change while retaining the remaining migration controls. A false response requires correcting the schema definition first. Database success cannot override an incompatible event contract.

For a nullable string column, the Avro representation is the union ["null", "string"] with a null default. This gives older events a valid interpretation when the field is absent. It is the serialization counterpart of adding the database column permissively before tightening its use. Compatibility still needs to be tested against the candidate schema itself, since the SQL operation's name alone cannot show that the change is safe.

FORWARD and FULL modes have different rules for which readers and writers must be compatible. FULL can block practical changes because compatibility must work in both directions. NONE disables the checks entirely and should not be used on production CDC topics. Registry enforcement is part of automatic schema evolution alongside decisions about which changes the pipeline can safely accept.

Replica identity, NOT NULL correctness, and the replication behaviors that depend on constraint accuracy

In PostgreSQL, a unique index can serve as replica identity when there is no primary key, allowing replication to identify rows for UPDATE and DELETE events. All indexed columns must be declared NOT NULL, and the index must not be deferrable. Nullability therefore has a structural role beyond data quality.

Temporarily relaxing NOT NULL on a participating column can remove that eligibility. What appears to be a permissive migration step can instead undermine the row identification on which downstream updates and deletes depend. The risk reaches beyond whether an individual value is valid.

The practical rule is to check replica identity before relaxing any existing NOT NULL constraint. If the affected column belongs to that index, an alternative replica identity must be in place before the constraint is dropped. Expand-contract sequencing must preserve the replication dependencies of existing columns as well as compatibility for newly added ones.

What compliance-sensitive migrations require beyond correct sequencing

A technically correct migration is incomplete for compliance purposes if its decisions cannot be reconstructed. SOC 2 and HIPAA audit reviews require documentation of what changed, when, why, which controls were validated, and what rollback path was available. A migration file alone is not enough. The record must explain how the change was kept under control, including why nullability was allowed for a time and when that ended.

Success criteria belong before execution. They include zero null violations in critical fields after migration, finishing within the approved cutover window, and a record showing that compliance controls were checked at each stage. The backfill batching strategy, temporary COALESCE view, and timing of constraint application also need a recorded rationale. Those decisions support future schema changes and rollback planning as well as the immediate audit response.

A passthrough architecture, in which the replication layer never stores customer data, avoids an intermediate data store requiring its own audit controls for the migration. Source and destination controls remain necessary. The final evidence should show not merely that NOT NULL was applied, but that the intervening defaults, captured events, and historical values were handled under the approved process.