Database Synchronization: Detect and Fix Drift

Two databases that are supposed to hold the same data rarely disagree loudly. There is no error message when a customer's address is updated in one and not the other, or when a few hundred order rows fail to copy during a network blip. The difference sits there quietly until a report does not add up, a customer receives the wrong invoice or a support agent sees a different status from the one finance sees. Database synchronization keeps data consistent between systems, and doing it well means not only copying changes but detecting and repairing drift when copying fails. This guide covers the main ways to synchronise, why silent drift happens, how to detect it with reconciliation checks and how to repair it safely.

How database synchronization works

There are three broad approaches, each with different trade-offs:

• Native replication: the database copies changes itself. PostgreSQL's logical replication, for example, uses a publish and subscribe model; the documentation explains that it starts by copying a snapshot of the published tables, then sends changes continually, and the subscriber applies them in the same order so transactional consistency is kept within a subscription.
• Change data capture: a separate tool reads the database's change log and publishes each change as an event for other systems to consume. Debezium, for example, describes itself as an open source distributed platform for change data capture.
• Application-level sync: code reads changed records, often by a modified timestamp, and writes them to the other system through its API or database connection, on a schedule or triggered by events.

Native replication is the most reliable when both sides are the same database engine and the target is a copy. Change data capture suits feeding several systems or transforming data on the way. Application-level sync is common when the target is a different kind of system, such as a CRM, but it is also where drift is most likely.

Why silent drift happens

Drift appears when changes on one side fail to arrive, or arrive differently, on the other. Common causes:

• Missed changes: a timestamp-based sync skips records updated in the same second as its last run, or records changed by a bulk process that did not update the timestamp
• Partial failures: a batch writes most records, fails on a few and moves on without recording them
• Deletes: many syncs copy inserts and updates but not deletions, so removed records live on in the target
• Conflicting writes: both systems accept edits to the same field, and the last writer wins inconsistently
• Transformation errors: a mapping truncates, rounds or reformats a value differently than expected
• Manual fixes: someone corrects data directly in one database and not the other

Our guide to scheduled data sync covers how to design timestamp-based syncs that avoid the first two problems.

Decide which side is the source of truth

Before detecting drift, decide for each table, and sometimes each field, which system owns it. If the billing database owns invoice amounts and the CRM only displays them, any difference is the CRM's problem to fix. Where both systems legitimately edit the same data, define a conflict rule in advance, such as most recent change wins with a reliable timestamp, or one system always wins for certain fields. Without ownership, drift cannot be repaired consistently, only argued about.

Detect drift with reconciliation

Reconciliation is a regular comparison of the two sides to find differences that synchronisation missed. Approaches, from cheapest to most thorough:

• Counts: compare row counts per table, or per day of creation. Quick, but misses changed values.
• Aggregates: compare sums of key numeric fields, such as order totals per day.
• Checksums by bucket: compute a hash of the relevant columns for groups of rows, such as by ID range or day, and compare hashes. Only buckets that differ need row-by-row comparison.
• Full comparison: compare every row and field. Accurate but expensive on large tables, so usually reserved for buckets flagged by checksums.

Run lighter checks often and heavier ones less frequently. Record every difference found, with the table, key and fields involved, so patterns become visible.

Worked example: checksum reconciliation by day

The simplified, untested PostgreSQL query below computes a checksum of selected order columns for each day. Running the same query on both databases and comparing the results shows which days differ; only those days then need row-level comparison. Table and column names are placeholders.

-- Run on both source and target, then compare day by day.
SELECT
  date_trunc('day', created_at) AS day,
  count(*)                      AS row_count,
  md5(string_agg(
        id::text || '|' || status || '|' || coalesce(total_amount::text, ''),
        ',' ORDER BY id
      ))                        AS checksum
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

The columns included should be the ones that matter to the business and are expected to match exactly. Values that are legitimately transformed between systems, such as different date formats, need the same transformation applied before hashing.

Repair drift safely

When reconciliation finds differences, repair them through the same path that normal synchronisation uses, not by editing data by hand:

• Re-queue the affected records for synchronisation from the source of truth
• For missed deletes, apply deletes or mark records inactive according to the agreed rule
• For conflicts, apply the conflict rule and log the decision
• Re-run reconciliation for the affected buckets to confirm the fix

Investigate the cause of each drift pattern, not just the instances. If the same table drifts every week, the sync logic needs fixing. Our guide to Zoho CRM and PostgreSQL sync shows how this works between a CRM and a database in both directions.

Plan for schema changes

Schema changes are a frequent, avoidable source of drift. A new column added to the source but not mapped to the target, a field renamed on one side, or a change in data type can all break synchronisation quietly. Treat the sync mapping as part of every schema change: review it in the same change request, deploy target changes before source changes where possible, and run reconciliation on the affected tables straight after release.

Monitor synchronisation health

Track a few measures continuously: replication or sync lag, failed records waiting for retry, and differences found by each reconciliation run. Alert when lag grows or differences appear in tables that are normally clean. A short, stable list of differences is normal in application-level sync; a growing one is a warning.

Common mistakes in database synchronization

• Syncing inserts and updates but not deletes
• No agreed source of truth for shared data
• Batches that skip failed records without recording them
• No reconciliation, so drift is found by customers
• Manual fixes in one database that bypass the sync
• Fixing drift instances without fixing the cause

Frequently Asked Questions

What is database synchronization?

It is keeping data consistent between two or more databases or systems, using native replication, change data capture or application-level sync, along with checks that detect and repair differences.

What causes data drift between databases?

Missed changes, partial batch failures, unsynchronised deletes, conflicting writes, transformation errors and manual fixes made in only one system.

How do you detect drift?

With regular reconciliation: comparing counts, aggregates or checksums of grouped rows between systems, then comparing individual rows only where groups differ.

How should drift be repaired?

Through the normal sync path from the source of truth, applying agreed rules for deletes and conflicts, then re-running reconciliation to confirm.

Keep your systems in agreement

We design synchronisation between databases and business systems, with reconciliation and repair built in. See our real-time CRM and database synchronization service, or talk to us about your data sync.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.