Data Sync Strategy 1: Dual Writes

Two Writes Are Harder Than One

The next approach is modifying your application code so that whenever it saves data, it writes to the old database and the new database.

# Pseudo-code: Dual Write approach
def update_user_profile(user_id, new_data):
    # Write to legacy database
    legacy_db.execute(
        "UPDATE users SET name=?, email=? WHERE id=?",
        new_data.name, new_data.email, user_id
    )

    # Write to new database
    new_db.users.update_one(
        {"_id": user_id},
        {"$set": {"name": new_data.name, "email": new_data.email}}
    )

This looks straightforward. The problem is that distributed transactions are fundamentally hard. Consider what happens when:

1. The write to the legacy database succeeds
2. The network blips for 50 milliseconds
3. The write to the new database fails

Now your databases are out of sync. The legacy DB has the updated name, the new DB has the old name. Which one is correct? How do you detect this happened? How do you fix it?

You could wrap both writes in a distributed transaction (two-phase commit), but this:
- Dramatically increases latency
- Requires both databases to support the same transaction protocol
- Creates a coupling where one database's downtime blocks writes to both

You could add retry logic and reconciliation jobs, but now you've built a complex, custom data synchronization system — exactly the kind of infrastructure that has subtle bugs and fails at 3 AM.

Key Point: Dual writes are acceptable only when eventual consistency is tolerable AND you build a reconciliation process that periodically compares both databases and fixes drift. For financial data, healthcare records, or anything where inconsistency has legal or safety implications, dual writes are a liability. Use CDC instead. The hidden cost of dual writes is not the implementation — it's the ongoing operational burden of detecting and repairing divergence.