Dual-Write & Shadow Mode: Zero-Risk Database Migration

Why Database Migrations Fail in Production

The failure mode is always the same: a team spends months migrating data from the old database to the new one, runs reconciliation queries that look clean in staging, schedules a maintenance window, cuts over — and discovers within hours that production data was different from what they tested. The customer data was modified between the last staging refresh and go-live. The stored procedures had undocumented side effects on audit tables. The ETL job that everyone forgot about was writing directly to the old schema and now has nowhere to go.

At a KSA bank, the stakes are higher than at a startup. SAMA expects continuous availability of payment channels. The PDPL requires audit trail continuity — you cannot have a gap in transaction records because your migration tool missed a schema-mapped column. Core banking data is a regulatory asset, not just application state. A two-hour maintenance window to cut over the accounts table is an operational incident, not an upgrade procedure.

The root cause is treating database migration as a one-time operation. It is not. It is a state synchronisation problem — keeping two databases consistent while the world keeps writing to both, for long enough to be confident the new one is correct, and then ending that synchronisation by making the new one primary. Every technique in this article is a consequence of that framing.

Migration Approach Trade-offs

There are four broad approaches to database migration. The choice is not purely technical — it is a risk management decision that determines what can go wrong and how quickly you can recover. At a regulated bank, “recovery” from a corrupted accounts database is not a rollback script; it is a SAMA incident report.

The trade-off of dual-write is operational cost. You are running two databases, two write paths, and a validation layer simultaneously for weeks or months. Write latency increases because the secondary write, even if async, adds a network hop and error handling path. The reconciliation infrastructure must itself be reliable enough not to produce false positives that erode team confidence in the new database. These are real costs — but they are bounded and recoverable. A big-bang cutover failure on the accounts database of a KSA bank is neither bounded nor recoverable.

The Dual-Write Pattern

Dual-write means every write operation is applied to both databases: the legacy database (primary, authoritative) and the new database (secondary, under validation). Reads are served exclusively from the primary until validation confidence is high enough to begin shifting read traffic.

The implementation lives in the repository layer, not in the service layer. This is intentional: the service layer should not know which database is primary at any given moment. The dual-write repository wraps both implementations and applies the write strategy transparently.

Key design decisions for the dual-write repository:

• Primary write is synchronous; secondary write is async. The calling code waits for the primary write to complete before returning. The secondary write is dispatched to an executor service and its failure does not propagate to the caller. This ensures that a PostgreSQL outage does not affect the payment flow.
• Secondary write failures are logged, not thrown. A failed write to PostgreSQL emits an error metric and a compensation event to Kafka, but does not roll back the DB2 write. The reconciler picks up the compensation event and retries.
• Idempotent writes on the secondary. Because the Kafka reconciler may replay events, every write to PostgreSQL must be idempotent: INSERT ... ON CONFLICT DO UPDATE (upsert), never a bare INSERT. The primary key from DB2 is preserved as the natural key in PostgreSQL to make this possible.
• Write amplification monitoring. Track the secondary write latency, failure rate, and queue depth as first-class metrics. A growing queue depth means the executor cannot keep up with the write rate — a warning before the lag becomes a consistency problem.

@Repository
@Primary
public class DualWriteAccountRepository implements AccountRepository {

    private final Db2AccountRepository      db2Repo;
    private final PostgresAccountRepository  pgRepo;
    private final ExecutorService            asyncWriter;
    private final MigrationEventPublisher    eventPublisher;
    private final MeterRegistry              meterRegistry;

    @Override
    public Account save(Account account) {
        // Primary write — synchronous, throws on failure
        Account saved = db2Repo.save(account);
        meterRegistry.counter("migration.write.primary", "store", "db2").increment();

        // Secondary write — async, isolated from caller
        asyncWriter.submit(() -> {
            try {
                pgRepo.upsert(saved);   // idempotent: INSERT ... ON CONFLICT DO UPDATE
                meterRegistry.counter("migration.write.secondary", "store", "pg", "status", "ok").increment();
            } catch (Exception e) {
                meterRegistry.counter("migration.write.secondary", "store", "pg", "status", "error").increment();
                // Publish compensation event — Kafka reconciler will retry
                eventPublisher.publishCompensation(
                    new CompensationEvent(saved.getAccountId(), saved.getVersion(), e.getMessage())
                );
            }
        });

        return saved;   // always return the DB2-saved entity
    }

    @Override
    public Optional<Account> findById(String accountId) {
        // Read routing — controlled by MigrationReadPolicy (feature flag per account ID range)
        if (readPolicy.serveFromNew(accountId)) {
            return pgRepo.findById(accountId);
        }
        return db2Repo.findById(accountId);
    }
}

Shadow Reads and Validation

Shadow mode means reading from the new database on every request, comparing the result to the primary database result, and discarding the new database result without serving it. The consumer sees the primary result as if shadow mode does not exist. The comparison runs asynchronously and emits a diff event if the results diverge.

Shadow reads answer the question the team needs answered before they can trust the new database: Does PostgreSQL return the same account state as DB2, for every account, under real production load? A test suite in staging cannot answer this question because it does not have production data characteristics, production concurrent modification patterns, or production legacy batch job interactions.

The shadow comparator must handle several categories of “expected” differences before it can produce actionable divergence alerts:

• Replication lag differences. The async secondary write means PostgreSQL may be milliseconds behind DB2 for recently-modified accounts. A shadow read immediately after a write will see a stale PostgreSQL value. Suppress lag-driven diffs by checking the account’s last_modified_at: if the DB2 value was modified within the past 30 seconds and the PostgreSQL value matches a slightly earlier version, log as expected lag, not divergence.
• Data type normalisation differences. DB2 DECIMAL columns have different trailing-zero behaviour compared to PostgreSQL NUMERIC. A balance of 1000.00 in DB2 may appear as 1000 in PostgreSQL depending on the JDBC driver configuration. Normalise all numeric fields to a canonical decimal format before comparison. The same applies to timestamp precision: DB2 timestamps carry microseconds; PostgreSQL carries microseconds too but the JDBC driver may truncate to milliseconds.
• Arabic character encoding differences. DB2 z/OS historically stores Arabic text in EBCDIC-encoded columns. The COBOL data mapping layer converts these to UTF-8 before writing to PostgreSQL. Verify that the EBCDIC→UTF-8 conversion is lossless and handles all Arabic Extended-A code points used in customer names. A single wrong conversion will produce divergence for all accounts with that character in their name.

@Component
public class ShadowReadComparator {

    private static final Duration LAG_WINDOW = Duration.ofSeconds(30);

    public void compareAsync(String accountId, Account primaryResult) {
        executor.submit(() -> {
            try {
                Optional<Account> pgResult = pgRepo.findById(accountId);

                if (pgResult.isEmpty()) {
                    // Account not yet replicated — expected during initial backfill
                    eventPublisher.publishShadowDiff(
                        new ShadowDiffEvent(accountId, "MISSING_IN_NEW", null));
                    return;
                }

                Account pg = pgResult.get();

                // Suppress lag-driven diffs: PG version may be behind DB2
                boolean lagExpected = primaryResult.getLastModifiedAt()
                    .isAfter(Instant.now().minus(LAG_WINDOW));
                if (lagExpected && pg.getVersion() == primaryResult.getVersion() - 1) {
                    meterRegistry.counter("shadow.diff.suppressed", "reason", "lag").increment();
                    return;
                }

                List<String> diffs = AccountComparator.deepEquals(primaryResult, pg);
                if (!diffs.isEmpty()) {
                    meterRegistry.counter("shadow.diff.real").increment();
                    eventPublisher.publishShadowDiff(
                        new ShadowDiffEvent(accountId, "FIELD_MISMATCH", diffs));
                }
            } catch (Exception e) {
                meterRegistry.counter("shadow.diff.error").increment();
            }
        });
    }
}

Kafka as the Migration Synchronisation Bus

The async secondary write in the dual-write layer is best-effort: it fails silently and emits a compensation event. Something must pick up those compensation events and retry them. Kafka is the natural choice because it provides ordered, durable event delivery, replay capability, and a single place to observe the migration state.

The migration Kafka bus serves three distinct functions simultaneously:

1. Initial backfill pipeline. Before dual-write is enabled, Debezium reads the DB2 change log and emits all historical account records to a Kafka topic. A Kafka Streams backfill consumer reads from this topic and writes to PostgreSQL. This populates the new database with the current state of DB2 before live writes begin.
2. Compensation event replay. When the async secondary write fails, the dual-write layer publishes a MigrationCompensationEvent to a dedicated Kafka topic. The reconciler consumer retries the write with exponential backoff and idempotent upsert semantics. The consumer group lag on this topic is a real-time measure of the PostgreSQL write backlog.
3. Shadow diff event stream. The shadow comparator publishes divergence events to migration.shadow.diffs. A Grafana dashboard consuming this topic shows the real-time divergence rate, the most common diverging fields, and the accounts with the most divergences — giving the team a clear signal of whether validation confidence is improving or worsening over time.

{
  "name": "db2-accounts-source",
  "config": {
    "connector.class": "io.debezium.connector.db2.Db2Connector",
    "database.hostname": "corebanking-db2.internal",
    "database.port": "50000",
    "database.user": "${file:/opt/kafka/secrets/db2-creds.properties:username}",
    "database.password": "${file:/opt/kafka/secrets/db2-creds.properties:password}",
    "database.dbname": "CBCORE",
    "database.cdcschema": "ASNCDC",
    "table.include.list": "CBCORE.ACCOUNTS,CBCORE.ACCOUNT_LIMITS,CBCORE.ACCOUNT_FLAGS",
    "topic.prefix": "migration",
    "snapshot.mode": "initial",
    "snapshot.isolation.mode": "read_committed",
    "decimal.handling.mode": "precise",
    // preserve exact DB2 DECIMAL precision — critical for financial amounts
    "tombstones.on.delete": "true",
    "key.converter": "io.confluent.kafka.serializers.KafkaAvroSerializer",
    "value.converter": "io.confluent.kafka.serializers.KafkaAvroSerializer",
    "key.converter.schema.registry.url": "https://schema-registry.infra.svc:8081",
    "value.converter.schema.registry.url": "https://schema-registry.infra.svc:8081",
    "transforms": "unwrap,dropNull",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.add.fields": "op,ts_ms",
    "transforms.dropNull.type": "org.apache.kafka.connect.transforms.Filter",
    "transforms.dropNull.filter.condition": "${value.op} != 'd'"
    // suppress delete tombstones during migration — handle deletes via dual-write layer
  }
}

Schema Evolution Rules Under Dual-Write

Dual-write constrains how you can evolve the database schema during the migration period. Because both databases must remain consistent with each other and with the application code, schema changes must follow strict backward-compatible rules for the duration.

The rule is simple but has significant consequences: every schema change to DB2 must also be applied to PostgreSQL, in the same transaction window, before any application code that uses the new column is deployed. This is the expand/contract pattern applied to a two-database scenario.

• Additive changes only during dual-write. Add columns, add tables, add indexes. Never drop a column or rename one — the other database will immediately diverge and shadow reads will produce meaningless diffs.
• Column renames must expand-and-contract. Add the new column, dual-write to both old and new column names, backfill the new column from the old, migrate readers to the new column name, then drop the old column — but only after dual-write ends and the old database is decommissioned.
• Liquibase manages both databases. Maintain a single Liquibase changelog with profiles for DB2 (using DECIMAL, CHAR FOR BIT DATA) and PostgreSQL (using NUMERIC, BYTEA). This ensures the two schemas stay in sync through the migration and drift cannot be introduced by a manual SQL hotfix.
• Type promotions require explicit mapping. DB2’s SMALLINT is 2 bytes; PostgreSQL’s SMALLINT is also 2 bytes. But DB2’s INTEGER is 4 bytes; PostgreSQL’s INTEGER is also 4 bytes. The dangerous case is VARCHAR length semantics: DB2 VARCHAR(100) means 100 bytes in EBCDIC for many legacy schemas; PostgreSQL VARCHAR(100) means 100 characters in UTF-8. An Arabic name that fits in 100 EBCDIC bytes may not fit in 100 UTF-8 characters after conversion. Audit all VARCHAR columns in the account schema before migration.

Traffic Shifting: Progressive Migration

Traffic shifting is the mechanism for moving read traffic from the old database to the new one incrementally, controlled by a feature flag or configuration, while validation is still running. It is not a binary switch; it is a gradual shift that can be paused or reversed at any point.

The MigrationReadPolicy bean controls which accounts are read from which database. The policy is driven by a configuration property that can be changed at runtime via Spring Boot’s @RefreshScope or a configuration management tool like Vault or Kubernetes ConfigMap, without redeployment.

Three shifting strategies are available:

• Canary slice (recommended first step). Route a percentage of accounts by modulo of the account ID to the new database. Start at 1%, watch shadow diffs and error rates for 48 hours, then increase. The deterministic routing ensures a given account always reads from the same database — no read-your-own-writes anomalies from toggling between stores per request.
• Feature flag per account group. Route specific account types (e.g., savings accounts only, or accounts opened after a certain date) to the new database. Useful when validation showed higher confidence for one account type than another.
• Read from both, serve from new. For a brief window before cutover, read from both stores, serve the new database result, and log divergences. This is shadow mode in reverse: the new database is serving responses, but the old is still being read to detect any regression.

The decision to increase the traffic percentage should be driven by metrics, not by schedule. The criteria: shadow divergence rate below 0.01% of reads, compensation event queue depth below 100, secondary write error rate below 0.001%, and no open production incidents related to the migration. If any criterion fails, freeze the percentage at its current level until it recovers.

Cutover Execution: The Zero-Downtime Switch

Cutover is the moment when the new database becomes primary — all reads and writes go to PostgreSQL, DB2 becomes a read-only backup. It is not the same as the traffic-shifting steps above; those shifted reads. Cutover shifts writes. When writes shift, dual-write ends and the synchronisation cost goes away.

The cutover sequence assumes you are already at 100% reads from PostgreSQL with zero divergence over the past 72 hours. If you are not at that point, do not cut over.

1. Pre-cutover gate check (T-24 hours). Verify: shadow divergence rate = 0.00% over last 24 hours; compensation event queue depth = 0; secondary write error rate = 0.000% over last 6 hours; DB2 Debezium lag = 0; all batch jobs have been reviewed and confirmed to have a PostgreSQL-compatible equivalent. If any gate fails, postpone. Post the gate check result to the change management ticket.
2. Freeze application deployments (T-1 hour). Prevent any application deployment during the cutover window. New deployments could change the dual-write behavior mid-cutover and introduce hard-to-diagnose inconsistencies. The freeze applies to all services that touch the accounts schema, not just the one being migrated.
3. Drain the compensation queue (T-30 min). Stop the dual-write executor’s secondary write path. Allow all in-flight async writes to complete. Verify the compensation event Kafka topic consumer group lag reaches zero. This ensures PostgreSQL is fully caught up with DB2 at the moment you switch writes.
4. Promote PostgreSQL to primary (T-0). Update the MigrationReadPolicy and MigrationWritePolicy configuration atomically via a Kubernetes ConfigMap update: read.primary: postgres, write.primary: postgres. The @RefreshScope picks this up within 30 seconds without restart. From this moment, DB2 is no longer receiving writes. Monitor the DB2 write rate metric: it must drop to zero within 60 seconds. If it does not, the configuration refresh has not propagated; roll back immediately.
5. Run 15-minute hot observation. Watch the key metrics for 15 minutes after promotion: PostgreSQL write error rate, API p99 latency, payment success rate (via downstream SARIE callback metrics), and customer-facing error rate. A single payment failure that can be attributed to the migration is a rollback trigger. Define the rollback trigger criteria in the change management ticket before the cutover.
6. Decommission DB2 writes (T+15 min if clean). Update the dual-write repository to skip the DB2 write path entirely. This can be done as a flag change, not a deployment. Continue monitoring DB2 as a read-only archive for 30 days before decommissioning the DB2 schema objects.
7. Post-cutover reconciliation run (T+24 hours). Run a full-table reconciliation query comparing DB2 and PostgreSQL on the primary key and version column. Any rows that diverged during the cutover window (unlikely but possible due to in-flight transactions) will appear here. Resolve manually and document in the incident log. This reconciliation run is the evidence of migration completeness for the SAMA audit trail.

SAMA Audit Trail Continuity During Migration

SAMA’s Information Security Framework and its Open Banking Technical Standards both require that transaction and audit records are complete, unaltered, and accessible for examination. A database migration is a risk event that SAMA examiners may ask about during a review. The migration must not create gaps in the audit log and must be documentable as a controlled, validated change.

Four specific SAMA requirements apply during a database migration:

• No audit record loss. The ACCOUNTS_AUDIT table that tracks every balance change must be migrated with full fidelity. If the legacy DB2 schema wrote audit records in a different format (CHAR timestamps, EBCDIC customer IDs) than the new PostgreSQL schema, the conversion must be lossless and every converted record must be verified by the reconciler. A missing audit record is not a data integrity issue — it is a regulatory compliance issue.
• Dual-write for audit tables during cutover. Even after the main accounts table is promoted to PostgreSQL, continue dual-writing the audit table to DB2 for 30 additional days. This gives the SAMA examination team a consistent source of truth if they request records from the migration period, without requiring them to query across two databases.
• Change management documentation. File the migration as a significant system change in your IT change management system. The CAB (Change Advisory Board) approval for the cutover must include the gate check results, the shadow validation report, and the rollback procedure. SAMA may ask for this documentation during a subsequent examination.
• PDPL data residency during backfill. The Debezium CDC pipeline and the Kafka migration bus topics contain personal data (customer names, IBANs, national IDs). Ensure the Kafka cluster is within the KSA regulatory perimeter and that the migration topics have appropriate ACLs. The backfill pipeline should be treated as a data processing activity under PDPL, requiring the same data protection controls as the production systems it serves.

-- Post-cutover reconciliation: find rows that diverged between DB2 and PostgreSQL
-- Run via Debezium JDBC sink or IBM Data Replication; not via application layer

SELECT
    db2.account_id,
    db2.balance           AS db2_balance,
    pg.balance            AS pg_balance,
    db2.version           AS db2_version,
    pg.version            AS pg_version,
    db2.last_modified_at  AS db2_modified,
    pg.last_modified_at   AS pg_modified,
    CASE
        WHEN db2.balance <> pg.balance         THEN 'BALANCE_MISMATCH'
        WHEN db2.version <> pg.version         THEN 'VERSION_MISMATCH'
        WHEN db2.status <> pg.status           THEN 'STATUS_MISMATCH'
        WHEN db2.account_limit <> pg.account_limit THEN 'LIMIT_MISMATCH'
        ELSE 'OTHER'
    END                   AS divergence_type
FROM      db2_link.cbcore.accounts db2
FULL JOIN pg_accounts              pg
    ON db2.account_id = pg.account_id
WHERE
    db2.balance          <> pg.balance
    OR db2.version       <> pg.version
    OR db2.status        <> pg.status
    OR db2.account_limit <> pg.account_limit
    OR db2.account_id    IS NULL    -- row in PG only
    OR pg.account_id     IS NULL    -- row in DB2 only
ORDER BY divergence_type, db2.last_modified_at DESC;

Production Checklist

Zero-risk database migration phases

Each phase gate must pass before proceeding to the next. No exceptions for schedule pressure.

Phase 1 — Backfill & validate (weeks 1–3)

1. Debezium DB2 connector deployed and emitting CDC events to Kafka.
2. Backfill consumer completed; PostgreSQL row count matches DB2 row count within 0.001%.
3. Full-table reconciliation SQL shows zero divergences on non-in-flight rows.
4. Arabic character encoding validated: spot-check 1 000 account names for lossless round-trip.
5. DECIMAL precision validated: spot-check 10 000 account balances at maximum precision.

Phase 2 — Dual-write & shadow reads (weeks 4–8)

1. DualWriteAccountRepository deployed with write.secondary: enabled; primary = DB2.
2. Shadow comparator deployed and emitting to migration.shadow.diffs Kafka topic.
3. Grafana dashboard showing: secondary write error rate, compensation queue depth, shadow divergence rate.
4. Shadow divergence rate below 0.1% after week 1; below 0.01% after week 2.
5. Batch job inventory reviewed; all batch jobs writing directly to DB2 assessed for migration.

Phase 3 — Traffic shifting (weeks 9–11)

1. Read traffic shifted to PostgreSQL at 1%, 5%, 25%, 50%, 100% — each step held for 48 hours with zero regression.
2. Shadow divergence rate = 0.00% at 100% read traffic.
3. All batch jobs either migrated to PostgreSQL or confirmed to operate correctly in dual-write mode.
4. Rollback procedure tested in staging: read.primary: db2 config change takes effect within 60 seconds.

Phase 4 — Cutover (week 12)

1. 72-hour zero-divergence window confirmed; gate check documented in change ticket.
2. CAB approval received; cutover window communicated to all consuming teams.
3. Compensation queue depth = 0 at T-30 min; DB2 Debezium lag = 0.
4. PostgreSQL promoted to write primary; DB2 write rate drops to zero within 60 seconds.
5. 15-minute hot observation clean; no rollback trigger events.
6. Post-cutover reconciliation SQL run at T+24 hours; results documented.
7. SAMA change management documentation filed.