Applications often need the same data in more than one place: a reporting database, a search index, a cache, a second system after an acquisition or a new database during a migration. Keeping copies consistent is a classic integration problem. There are several approaches, and the right one depends on how fresh the copy must be, how much data changes and how much complexity you can support.
Full batch copies
The simplest approach is to copy entire tables on a schedule, for example nightly, replacing the target's contents. It is easy to build and reason about, and it naturally handles deletes because the target is rebuilt each time.
The downsides are latency and cost. The target is only as fresh as the last run, and copying large tables repeatedly puts load on the source and network. Full copies work well for small reference tables and for reports that tolerate data from the previous day.
Incremental batches using timestamps or versions
A more efficient batch approach copies only rows changed since the last run, using an updated_at timestamp or an increasing version column. Each run records a high-water mark and selects rows above it.
This method has well-known pitfalls:
- Deleted rows disappear from the source and are never seen; soft deletes or a separate deletion log are needed.
- Rows updated without changing the timestamp are missed, so the column must be maintained reliably, ideally by the database.
- Transactions that commit late can carry timestamps earlier than the high-water mark; an overlap window helps.
Triggers
Database triggers can write each insert, update and delete into a change table, which a separate process then reads and applies to the target. Triggers capture every change, including deletes, without changing application code.
They add work to every write on the source, can complicate schema changes and are easy to forget during troubleshooting. For moderate write volumes they are a practical option, especially when log-based capture is not available.
Change data capture (CDC)
Log-based change data capture reads the database's own transaction log, such as the PostgreSQL write-ahead log or the MySQL binary log, and turns committed changes into a stream of events. Tools such as Debezium publish these events, often to a message broker, from which consumers update the target.
CDC captures inserts, updates and deletes in commit order with low latency and little extra load on the source. It requires more infrastructure and operational knowledge: log retention settings, connector monitoring, handling schema changes and replaying events after failures.
Dual writes from the application
An application can write to both databases directly. This looks simple but is risky: if one write succeeds and the other fails, the copies diverge, and there is no shared transaction across two systems. A safer variant is the transactional outbox pattern, where the application writes the change and an outbox record in the same local transaction, and a separate process publishes outbox records to the other system.
Choosing an approach
| Need | Reasonable choice |
|---|---|
| Small tables, daily freshness | Full batch copy |
| Large tables, hourly freshness, few deletes | Incremental batch with overlap and soft deletes |
| Every change including deletes, moderate volume | Triggers with a change table |
| Near real-time, high volume, many consumers | Log-based CDC |
| Application events for other services | Transactional outbox |
Practices that apply to every method
- Make the target update idempotent, so applying the same change twice does no harm.
- Use stable primary keys to match rows between systems.
- Monitor lag and alert when the copy falls behind.
- Run periodic reconciliation, such as counts or checksums, to detect drift.
- Document which system is the source of truth for each table.
Key takeaways
- Batch copies are simple; incremental batches need care with deletes and timestamps.
- Triggers and CDC capture every change, at the cost of extra complexity.
- Avoid naive dual writes; use an outbox if the application must publish changes.
- Whatever the method, design for idempotency, monitoring and reconciliation.