What does it mean to backfill data into a new microservice, and why does a one-off copy fail?
When you extract a service from a shared database, the new service needs its own copy of the data it will own, such as orders, customer profiles or inventory counts. That data already exists, spread across tables that other parts of the monolith keep reading and writing. To backfill data into a new microservice is to populate its store with that history and then keep it current until the new service becomes the source of truth.
The tempting approach is a one-off copy: dump the relevant tables, load them into the new store, and switch over. This fails because the shared database does not pause while you copy. A copy of a large table can take hours. During that window, rows you already copied get updated, rows you have not reached yet get deleted, and new rows are inserted behind your cursor. When the copy finishes, it does not match any single point in time, and it falls further behind with every write that follows. It is stale on arrival.
So the backfill cannot be treated as a separate task from replication. You design the historical load and the live change stream as one process that converges, where every write is either captured in the snapshot or replayed afterward, and replaying a write twice does no harm.
This applies to teams splitting a service out of a shared schema while the old system stays in production. A greenfield service has no history to move. A read-only replica copies the whole database without changing its shape, so the database engine's own replication already handles this. The hard case is moving a subset of the data, in a new shape, while that data keeps changing.
Who should use this approach?
Use the snapshot-plus-change-stream backfill when all of the following are true:
- You are extracting a service from a shared database that stays live. The monolith or other services keep writing to the tables during the migration, so you cannot take a maintenance window long enough to copy everything and switch.
- The new service will own a subset of the data in a different shape. You are moving some tables or columns, renaming fields, splitting enums or changing keys, so database-native replication of the whole schema is not enough.
- You cannot afford silent divergence. The data matters enough, for billing, fulfillment, compliance or customer-facing reads, that you need to prove the two stores match before cutover.
The people who do this work are usually the team building the new service, together with whoever operates the shared database. The database operators matter because the approach needs read access to a replica, and log-based capture also needs a replication slot and control over log retention.
You probably do not need it in these cases:
- The data is small and the writes can be paused. A brief write freeze plus a single copy and a verification pass is simpler.
- The new service starts empty. There is no history to move.
- The data is derived and can be rebuilt from another source of truth. For example, a search index or cache can be regenerated instead of migrated.
- You need an exact replica of the whole database. The engine's built-in replication already covers this.
How does the backfill process work end to end?
At a high level, the process is a pipeline with one entry point for data and one exit, and every part of it is designed so that it can run more than once without harm.
- Pick a starting position. Record a position in the shared database's change log. Everything before it will come from the snapshot, and everything after it will come from the change stream.
- Start capturing changes. Begin reading change events from that position, either from the commit log or from a transactional outbox. Buffer or apply them as they arrive.
- Run the bulk backfill. Read existing rows from a replica in primary-key order, in small throttled batches, checkpointing after each one so the job can resume after a crash.
- Translate in one place. Both the backfill rows and the change events pass through the same pure mapping function that converts the shared schema into the new service's model.
- Write idempotently. Every row is applied as an upsert guarded by an ordering value, such as a log position or version. Duplicates and late arrivals become no-ops, and deletes are recorded as tombstones.
- Verify continuously. Compare the two stores with range counts, range checksums, sampled field diffs and shadow reads, and track the mismatch rate as a metric.
- Cut over in reversible steps. Shift reads first, then move write ownership, with reverse sync or a freeze on the old tables, then decommission the old tables once nothing reads them.
The rest of this article covers each of these steps in detail, starting with how the snapshot and change stream converge.
How does snapshot plus change data capture converge two stores?
The pattern has three steps. First, record a consistent starting point: a position in the database's change log, or a snapshot taken at a known log position. Second, bulk-copy the rows that existed at that point into the new service. Third, start a change data capture stream from that same position and apply every insert, update and delete that happened afterward. When the stream catches up to the head of the log, the two stores hold the same data, and from then on the stream keeps them in sync.
The stream and the snapshot must overlap. If capture starts even slightly after the snapshot was read, any write made in that window is lost, and nothing will later reveal that it is missing. It is safer to start the stream at or before the snapshot point and accept that some changes will arrive twice: once in the bulk copy and once as an event.
Duplicates are only harmless if writes are idempotent. Upsert by primary key, and apply a row only when its ordering value is newer than the one already stored. That ordering value can be a monotonic version column, an updated_at timestamp with a tiebreaker, or the log position itself. With this check in place, a slow backfill batch that carries an old copy of a row cannot overwrite a newer change event.
Prefer log positions or version columns over timestamps. Timestamps fail in two ways:
- Clock skew: application servers with drifting clocks can write timestamps that run backward.
- Same-millisecond updates: two writes in the same millisecond compare as equal, so the order between them is lost.
A version column is more reliable, but adding one may require a schema change in a database your team does not own.
-- Target table in the new service (PostgreSQL syntax).
-- source_lsn holds the change-log position the row was read at.
-- Snapshot rows carry the snapshot's log position; CDC events carry their own.
CREATE TABLE customers (
id bigint PRIMARY KEY,
email text,
name text,
deleted boolean NOT NULL DEFAULT false, -- tombstone, so a late backfill row cannot resurrect a deleted record
source_lsn bigint NOT NULL
);
-- The same statement is used by the bulk backfill and by the CDC consumer.
-- Overlapping or duplicate deliveries are harmless: a row is written only
-- if its log position is newer than the one already stored.
INSERT INTO customers AS c (id, email, name, deleted, source_lsn)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,
name = EXCLUDED.name,
deleted = EXCLUDED.deleted,
source_lsn = EXCLUDED.source_lsn
-- Stale writes (e.g. a slow snapshot batch arriving after a newer event)
-- match no row here and become no-ops.
WHERE c.source_lsn < EXCLUDED.source_lsn;
-- A CDC delete event is applied as an upsert with deleted = true,
-- using the same ordering check, rather than as a hard DELETE.
An idempotent PostgreSQL upsert keyed on primary key and guarded by the source log position, so overlapping snapshot rows and change events converge and a stale backfill row never overwrites a newer change.
How do you implement a chunked, resumable backfill without hurting the shared database?
Paginate by key range, not OFFSET
Read the source table in primary-key order with a predicate like WHERE id > :last_id ORDER BY id LIMIT :batch. With OFFSET, the database still walks and discards every skipped row, so each batch gets slower as the job advances. A keyset query seeks directly through the index, which keeps the cost of every batch roughly the same. It also stays correct when rows are inserted mid-run. Any row that lands behind the cursor is picked up by the change stream instead.
Throttle and checkpoint
Keep batches small enough that each transaction stays short and holds few locks. Pause between batches, and adjust the pause using signals from the shared database, such as replica lag or p99 query latency. After each batch commits to the target, persist the last processed key. A crashed job then resumes from that checkpoint rather than starting over. Target writes must be idempotent upserts, because the last batch before a failure may be replayed.
Handle deletes explicitly
A range scan never sees rows that no longer exist. Apply delete events from the change stream as tombstones. Keep those tombstones long enough that a late-arriving backfill batch cannot resurrect a deleted row.
The throttling trade-off
Illustrative estimate: assume 60 million rows. At 2,000 rows/s, the backfill takes about 8.3 hours. If you throttle to 500 rows/s to protect production latency, it takes about 33 hours.
The slower run is safer for production, but it widens the drift window in two ways. The change stream has to retain and replay more events, and the target spends longer in a partially consistent state.
import time
import psycopg2
BATCH, BASE_PAUSE_S, MAX_LAG_S = 1000, 0.2, 5.0
src = psycopg2.connect("host=shared-replica.internal dbname=shared user=backfill")
dst = psycopg2.connect("host=orders-db.internal dbname=orders user=backfill")
src.autocommit = True # each batch read is its own short transaction
LAG_SQL = "SELECT COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()), 0)"
UPSERT = """INSERT INTO orders (id, customer_id, total, updated_at) VALUES (%s, %s, %s, %s)
ON CONFLICT (id) DO UPDATE SET customer_id = EXCLUDED.customer_id,
total = EXCLUDED.total, updated_at = EXCLUDED.updated_at
WHERE orders.updated_at < EXCLUDED.updated_at""" # idempotent; never overwrite newer CDC data
with src.cursor() as s, dst.cursor() as d:
d.execute("SELECT last_id FROM backfill_checkpoint WHERE job = 'orders'")
last_id = d.fetchone()[0] # resume point; seeded with 0 before the first run
while True:
# Keyset seek on the PK index: constant cost per batch, unlike OFFSET.
s.execute("SELECT id, customer_id, total, updated_at FROM orders"
" WHERE id > %s ORDER BY id LIMIT %s", (last_id, BATCH))
rows = s.fetchall()
if not rows:
break
# Drop rows the change stream has already tombstoned so we don't resurrect them.
d.execute("SELECT id FROM tombstones WHERE id = ANY(%s)", ([r[0] for r in rows],))
dead = {r[0] for r in d.fetchall()}
d.executemany(UPSERT, [r for r in rows if r[0] not in dead])
last_id = rows[-1][0]
d.execute("UPDATE backfill_checkpoint SET last_id = %s WHERE job = 'orders'", (last_id,))
dst.commit() # batch and checkpoint commit together; a replay is harmless
# Adaptive throttle: slow down as replica lag grows, back off hard past the limit.
s.execute(LAG_SQL)
lag = float(s.fetchone()[0])
time.sleep(30 if lag > MAX_LAG_S else BASE_PAUSE_S * (1 + lag))
A resumable PostgreSQL backfill loop that pages by primary key, upserts idempotently, skips tombstoned rows, commits the checkpoint with each batch, and throttles on replica lag.
Why are application-level dual writes risky, and what are the alternatives?
Dual writes look like the cheapest way to keep the target current during that window. The application commits to the shared database, then calls the new service. But the two writes share no transaction. If the process crashes, the second call times out, or a deploy kills the request between the two writes, one store has the change and the other doesn't, and nothing records the gap. Retries don't close it reliably either, because a retried write can land after a newer update and overwrite it. Dual writes therefore need a reconciliation job that diffs both stores and repairs drift. Someone has to build that job, tune it against the shared database's load, and respond to its alerts for the entire migration. The code is cheap on day one, but over the life of the migration it often costs more than the alternatives.
The transactional outbox inserts an event row in the same local transaction as the business change, and a relay publishes those rows. The change and its event commit or fail together. It is an acceptable substitute when you own every code path that writes the affected tables. It cannot see batch jobs, other teams' services, or manual SQL.
Log-based CDC reads the commit log, so it captures writes from every source. Avoid it if you can't obtain a replication slot or control log retention on a shared or managed database. If the consumer stalls, the database keeps log segments for it until the disk fills, so alert on slot lag.
| Option | Development cost | Ongoing operational cost | Main failure mode |
|---|---|---|---|
| Naive dual writes | Low | High: reconciliation runs for the whole migration | Silent divergence on partial failure |
| Transactional outbox | Medium: change every write path, build a relay | Medium: outbox table growth, relay lag | Writes that bypass the application |
| Log-based CDC | Medium: connector setup, schema mapping | Medium: slot and retention monitoring | Retained logs fill disk if the consumer stalls |
How should you translate the shared schema into the new service's model?
Put the mapping from old rows to new entities in one place: the consumer that applies both snapshot rows and change events. If the backfill job and the CDC consumer each have their own translation, they will drift, and the two paths will write different values for the same record.
Write the transformation as a pure function. It takes a source row plus a fixed mapping version and returns the target record. It does not read the clock, generate random IDs, or look up mutable reference data. This lets you rerun a chunk, replay events from an earlier offset, or rebuild the whole store and get byte-identical output, and that is what makes the verification step meaningful. If you need a surrogate key, derive it from the source primary key, for example a namespaced hash. Do not mint a new one on each run.
Columns that change meaning need explicit handling. A status integer may split into two enums. A nullable deleted_at may become a lifecycle state. A price may move from cents to a decimal with a currency. Encode each rule in the mapping, and treat values outside the known domain as errors. Do not coerce them to a default.
Until cutover, the pipeline is coupled to a schema it doesn't own. An upstream team adding, renaming or repurposing a column mid-migration can break the stream or, worse, corrupt it silently. Guard against this:
- Check the source schema against an expected definition on startup and per batch.
- Fail loudly on unknown columns or enum values, and halt the consumer rather than skipping the record.
- Ask owners of the shared tables to freeze or announce schema changes for the migration window.
import uuid
from decimal import Decimal
MAPPING_VERSION = "orders-v3"
NAMESPACE = uuid.UUID("6f1c2a4e-9b7d-4e1a-8c3f-2d5e7a9b1c40")
EXPECTED_COLUMNS = {"id", "status", "deleted_at", "price_cents", "currency"}
KNOWN_CURRENCIES = {"USD", "EUR", "GBP"}
STATUS_MAP = {
0: ("pending", "unpaid"),
1: ("pending", "paid"),
2: ("shipped", "paid"),
3: ("cancelled", "refunded"),
}
class MappingError(Exception):
"""Raised to halt the consumer. Callers must not catch it and skip the record."""
def check_schema(columns):
# Call this on startup and once per batch, before mapping any rows.
unknown, missing = set(columns) - EXPECTED_COLUMNS, EXPECTED_COLUMNS - set(columns)
if unknown or missing:
raise MappingError(f"schema drift: unknown={sorted(unknown)} missing={sorted(missing)}")
def map_order(row: dict) -> dict:
"""Pure: no clock, no randomness, no lookups. Used by both the snapshot and CDC paths."""
if row["status"] not in STATUS_MAP:
raise MappingError(f"unknown status {row['status']!r} for order {row['id']}")
if row["currency"] not in KNOWN_CURRENCIES:
raise MappingError(f"unknown currency {row['currency']!r} for order {row['id']}")
fulfillment, payment = STATUS_MAP[row["status"]]
return {
"order_id": str(uuid.uuid5(NAMESPACE, f"legacy-order:{row['id']}")),
"legacy_id": row["id"],
"fulfillment_state": fulfillment,
"payment_state": payment,
"lifecycle": "archived" if row["deleted_at"] is not None else "active",
"price": {"amount": str(Decimal(row["price_cents"]).scaleb(-2)), "currency": row["currency"]},
"mapping_version": MAPPING_VERSION,
}
A deterministic mapping function shared by the backfill and CDC paths: it derives surrogate keys with uuid5, encodes columns whose meaning changes, and raises on schema drift or unknown values so the consumer halts instead of corrupting the target.
How do you verify the new service's data before cutover?
Verification works best in layers, where each check is cheaper than the next and narrows down where the next one should look.
- Row counts per key range. Split the primary key space into fixed ranges and compare counts on both sides. This is cheap and catches missing or duplicated batches, but it misses wrong values.
- Checksums over key ranges. Hash a canonical serialization of each row, then aggregate the hashes per range. A mismatched range points you to a few thousand rows to examine instead of the whole table.
- Sampled field-level comparisons. Pull random rows and diff them field by field after applying the translation rules. This catches mapping bugs that a checksum only reports as "different."
- Shadow reads. Send real requests to both the old path and the new service, compare the responses, and serve only the old one. This tests the data the way callers actually use it.
| Method | Cost on shared DB | Catches | Misses |
|---|---|---|---|
| Range counts | Low | Missing or duplicate rows | Wrong values |
| Range checksums | High (full scan) | Any divergence | Which field differs |
| Sampling | Low to moderate | Mapping errors | Rare edge-case rows |
| Shadow reads | Production traffic | Behavioral differences | Cold, rarely read data |
Full checksums give certainty but scan everything, so schedule them off-peak or run them against a replica. Sampling is cheap but probabilistic. Teams usually checksum the tables where a wrong value has financial or legal consequences, sample the rest, and record which residual risk they accepted.
Emit the mismatch rate as a metric and treat cutover as blocked until it holds at zero over a defined window. Automated checks can be rerun after every fix, which manual spot checks cannot. They also find divergence while both stores still exist, before you have to reconstruct it from a post-cutover incident.
import hashlib
import json
RANGE_SIZE = 10_000 # keys per range; smaller ranges narrow down where a mismatch is
def canonical_row(row: dict) -> bytes:
# Sorted keys and fixed separators so identical data always serializes identically
return json.dumps(row, sort_keys=True, default=str, separators=(",", ":")).encode()
def range_digest(rows) -> tuple[int, str]:
# Rows must arrive ordered by primary key, or the same data gives different digests
h = hashlib.sha256()
count = 0
for row in rows:
h.update(hashlib.sha256(canonical_row(row)).digest())
count += 1
return count, h.hexdigest()
def compare_ranges(fetch_legacy, fetch_new, translate, max_id):
# fetch_* yield dict rows WHERE id >= start AND id < end ORDER BY id.
# Point fetch_legacy at a replica so full scans stay off the shared primary.
mismatched, total = [], 0
for start in range(0, max_id + 1, RANGE_SIZE):
end = start + RANGE_SIZE
total += 1
# Apply the backfill's translation rules so both sides use the new schema
legacy_count, legacy_hash = range_digest(translate(r) for r in fetch_legacy(start, end))
new_count, new_hash = range_digest(fetch_new(start, end))
if legacy_count != new_count or legacy_hash != new_hash:
# A count mismatch means missing or duplicate rows; a hash-only mismatch means wrong values
mismatched.append({"start": start, "end": end,
"legacy_rows": legacy_count, "new_rows": new_count})
rate = len(mismatched) / total if total else 0.0
# Emit rate as a metric; cutover stays blocked until it holds at 0.0 over the agreed window
return mismatched, rate
A repeatable per-key-range checksum comparison that hashes canonically serialized rows from both sides, reports which ranges diverge, and returns a mismatch rate to gate cutover.
How can this approach save engineering time?
The convergent design costs more to set up than a one-off copy or a dual-write patch. It saves time later, where migrations usually lose it: reruns, debugging divergence, and recovering from partial failures.
- Failures resume instead of restarting. Because the backfill checkpoints after every batch and every write is idempotent, a crashed job, a deploy or a paused run picks up where it stopped. Nobody has to work out which rows were already copied or clean up a half-loaded table before trying again.
- Fixing a mapping bug is a replay, not a forensic project. The translation is a pure function used by both paths, so once you correct it, you can rerun the affected key ranges or replay the change stream from an earlier position and get deterministic output. Without that property, every fix needs a custom repair script.
- There is no reconciliation system to build and staff. Dual writes need a diff-and-repair job that runs, alerts and needs tuning for the whole migration. Capturing changes from the log or an outbox removes most of that ongoing work, leaving monitoring of slot or relay lag.
- Divergence is found early and located precisely. Range checksums point to the specific key ranges that differ, so engineers inspect a few thousand rows instead of a whole table. Running the checks before cutover means problems are fixed while both stores still exist, which is far cheaper than reconstructing lost writes from a post-cutover incident.
- Cutover decisions are mechanical. A mismatch-rate metric gating cutover replaces long manual sign-off meetings and repeated spot checks with a rerunnable check.
- Schema drift halts the pipeline instead of corrupting it. Failing loudly on unknown columns or values costs a short stoppage. Silently skipping or defaulting records can cost days of tracing bad data back to its source.
- The pipeline is reusable. The checkpointed loop, the guarded upsert, the mapping-function pattern and the verification harness carry over to the next table or the next service extraction with mostly configuration changes.
Illustrative estimate: assume a migration where a one-off copy has to be redone three times because of mid-run failures and mapping fixes, and each redo takes an engineer one day to clean up the target, rerun and spot-check. With checkpointed, idempotent loads, each of those reruns becomes a resume or a targeted replay of the affected ranges. The assumed saving is most of those three days, plus the time not spent building and operating a dual-write reconciliation job. Your actual figures depend on table sizes, failure frequency and how often the mapping changes.
What is a safe cutover and decommissioning sequence?
Cut over in three ordered steps, each reversible until the next one starts.
| Step | What changes | Rollback path |
|---|---|---|
| 1. Shift reads | A feature flag routes reads to the new service, ramped by percentage or tenant | Flip the flag back; the old tables are still authoritative |
| 2. Move write ownership | The new service accepts writes; the old write path is disabled | Only possible if step 3 is already flowing; otherwise new writes exist in one store only |
| 3. Reverse sync or freeze | CDC now runs new→old, or the old tables become read-only | With reverse sync, re-point writes to the old path; with a freeze, there is none |
Rollback gets hard at step 2. Until then, the shared database holds every write, so undoing the change is just a routing decision. Once the new service owns writes, every insert and update after that moment exists only in its store. Rolling back means losing those changes or replaying them into a schema you've stopped maintaining. Reverse sync keeps the old tables current so that writes can return to them. Skip it, and the cutover is one-way the moment the first write lands.
Decommission in a fixed order. First revoke other services' grants on the old tables. Then watch for what breaks: nightly reports, batch jobs, database triggers, cross-service joins in someone else's queries, and analysts' ad-hoc SQL. Database access logs and query statistics show these consumers better than any service registry. Drop the data only after a full business cycle passes with no access.
Each hidden reader belongs to another team, which has to agree to migrate it on its own schedule. Treat that as coordination work. Inventory consumers when the migration starts, not after cutover, and plan for it to take longer than the backfill itself.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to backfill data new microservice, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.