Handling data migrations without anyone noticing

Data migrations look trivial until they run on production data. Jobs time out without a clean way to resume, workers hit rows that nobody expected, and throughput set too high causes lag across every other part of the system.

This article is a checklist I built over years of backend migrations and how I utilise it here at Preply. How to size data before running the migration query, make the workers resumable and observe jobs while they run. If you own a migration you are nervous about shipping, start here.

I have run migrations like this dozens of times, on user tables, payment records and event logs. Today I will use the most recent one as the running example.

A recent project needed a new table for segmenting users. It shipped in phases, and every phase added data points that had to be backfilled onto rows already in production and pushed to our CRM. So this was not one migration. It was the same migration run against different consumers with different shapes of data.

Because the project evolved over time, we could not design everything up front. Even though the exact details of each phase were still being finalised, the end goal was clear. That helped with the base design of the new segments table. Each phase extended the schema by a column or two.

For the first phase, where we built the table and started collecting data, things were simple. But by the time the second phase came, the table already had millions of rows, so we needed a tactical migration plan.

We broke the plan into four stages.

1. Adding a new column to the segments table
2. Populating new data on demand
3. Backfilling the remaining data in the segments table
4. Backfilling other consumers

1. Adding a new column to the segments table

We wanted to introduce a new field without triggering a huge transaction. A long-running ALTER TABLE would lock the table, forcing every other DB operation to wait until the lock was released. That was the exact problem we were trying to avoid.

ALTER TABLE student_segments
ADD COLUMN new_column VARCHAR(20) NULL;

The new field was added as nullable so that adding it would not rewrite all existing rows. Not touching existing rows was what made this table alteration run in milliseconds.

All new inserts then had new_column set to null, which allowed us to move to the next step and start populating it. The obvious next move would be to update all existing rows in one shot. That approach had two problems.

1. It would be a big operation that is risky and, most importantly, heavy on the database.
2. Rows inserted while (and after) the update ran would still have an empty new_column. This meant that follow-up migrations would be needed.

To avoid these 2 problems, we broke down populating that field into two further steps.

2. Populating new data on demand

Here we needed to identify all the entry points. These were the code paths writing to the segments table. All of them had to be identified and updated prior to populating the new column. These points either inserted new rows or updated existing ones. In our case they were GraphQL mutations after students finished their lessons, analytics events when they visited certain pages, and domain events when they renewed their subscriptions.

This on-demand approach gave us up-to-date data for students who were actively using the Preply website or mobile app. Our architecture specified that when a record of the segments table got updated, that update would also have to make its way into our CRM.

However, this approach alone did not fully solve our problem. CRM often needed to reach out to inactive students, e.g. to encourage them to open their Preply app again. These students had not visited Preply since we started the migration, so nothing had triggered a DB write for them and their new_column would still be empty. Only a small fraction of the table would have been populated.

3. Backfilling the remaining data in the segments table

Once step 2 was live, we needed to populate the remaining rows. This step is what is commonly referred to as the actual data migration.

The migration script entry point

We needed to run a background job that updated the rows where new_column was null. There are several strategies to follow here, such as:

1. Sort the table by the created_at or updated_at timestamp and work from the oldest record to the most recent.
2. If the table’s primary key is numeric, sort from the oldest to the newest id and update accordingly.
3. Filter only the rows where new_column is null and update them (not recommended if the new column is not indexed).

There are many more depending on each table’s design. Something I have learned after doing several migrations over the years is to pick a scripting strategy where if things go wrong, it is straightforward to stop and resume from where we left off.

The strategy we followed was based on the updated_at timestamp. First we had to find the migration cutover point. That was the timestamp of the first successful write to new_column, which was essentially the moment step 2 went live. Any row that was not updated since then still had new_column set to null. We sorted the data based on updated_at in ascending order and processed rows from the oldest until we reached the cutover point.

Sizing data and calculating the load

To finalise our migration strategy, we did not want to update millions of rows all at once. That would have exhausted the database’s resources, causing CPU and memory spikes that would affect other areas of the business.

Coordinating with the team that manages the infrastructure was crucial here. If things broke, that team would automatically be involved in an incident and would not be happy about it. We had to make them aware of the migration and agree on an acceptable load to keep things safe. That was not only the safe option for me executing the migration but also a better way of working together. Surprising them with a P1 incident caused by elevated DB load, with the site being down, was exactly what we wanted to avoid.

Once we had the acceptable load agreed, it was time to do some math and estimate how long the migration would take. As an example, with 1,000 writes per second and 46 million records:

1,000 writes/second * 3,600 seconds = 3,600,000 writes/hour. 46,000,000 / 3,600,000 = 12.8 hours. A bit over half a day.

The core idea is that instead of one massive update, the migration would run as a stream of small batches of 1,000 records each. Depending on your company’s load and DB size, these numbers could look very different.

Get Harris Geo’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To reduce risk further, we ran the migration in increasing chunks to confirm things behaved as expected. Started with 5,000 rows, then 10,000, 25,000, 100,000, 500,000, 5,000,000, 50,000,000. More details in the “Stopping, resuming and fine tuning the migration script” section.

4. Backfilling other consumers

The last step, and often the one requiring the most care, was to update the corresponding downstream systems. Because these consumers are often third-party services, rate limits become a major constraint.

As with the internal DB backfill, coordinating with the team that owned the downstream service was needed. In our case it was the CRM backend team and we agreed on an acceptable load before running this backfill.

The agreed load was 5,000 writes per minute, which was 300,000 writes per hour, 12x slower than the previous step. The math this time:

46,000,000 / 300,000 = 153.3 hours, which was roughly six and a half days.

Migrations that take several days require patience, but the process itself is exactly the same as in the previous step. All we had to do was run it and occasionally check the migration logs to see how it was performing.

Migration script best practices

A migration can fail for several reasons. Network errors, edge cases in the code, DB load issues among others. A few mechanisms need to be in place to handle them.

Parallelisation

This is the practice that often makes the biggest difference in the performance of a migration. When dealing with thousands of writes per second, a single worker might struggle to keep up, and a stuck batch would stall the entire migration.

It is good practice to split the load across several workers running in parallel. With our 1,000 writes per second target and five workers, each worker needed to write at most 200 records per second, which was far more manageable. Parallel workers also moved the bottleneck from the code level to the infrastructure level, and a worker that got stuck no longer held the entire migration hostage. The other workers finished their batches and new ones were spun up.

That being said, parallelisation introduces its own trade-offs. It cannot scale to infinity, because each worker requires its own DB connection, and exhausting the connection pool causes side effects in other services or parts of the system.

Running workers in parallel also carries an infrastructure cost per worker. Depending on the worker setup (for example EC2 or ECS tasks in AWS), this cost is usually trivial, but it is an additional parameter to consider.

Stopping, resuming and fine tuning the migration script

The migration script was designed with an explicit starting and finishing point.

def run_student_segment_new_column_migration(
  starting_date: datetime,
  finish_date: datatime,
  limit: int,
  batch_size: int,
  writes_per_second: int = _WRITES_PER_SECOND,
  dry_run: bool = True
) -> None:

Because our strategy processed rows sorted by updated_at in ascending order, resuming was simple. If the migration stopped, we reran the script with the updated_at of the earliest failed record as the new starting_date. For isolated failures scattered across the table, we could rerun the script for each one individually by setting starting_date and finish_date to that record’s exact timestamp.

In some edge cases we also required a mechanism to forcefully stop the migration script. We used kill switches, which made this fast and easy, as they were one click in our admin system.

if not student_segment_migration_killswitch.is_active:
  logger.info("backfill_new_column_to_student_segments: kill switch %s is off - skipping %s rows. Resume the dispatcher with starting_date=%s",
    student_segment_migration_killswitch.name,
    len(students),
    students[-1].updated_at
)
return

As described in step 3, applying limits also let us run the migration in small, controlled increments. That limit can be applied in several ways. In this script the limit was passed into the SQL query itself.

SELECT user_id FROM student_segments
WHERE updated_at >= {starting_date}
AND updated_at < {finish_date}
ORDER BY updated_at ASC
LIMIT {limit}

In cases where grouping of records is needed (e.g. lesson_completed events per student), the limit would apply to the group instead of individual records.

Another valuable technique we followed was to always do a dry run first before running the real migration. Passing the dry_run argument as true would prevent any writes, so running it once gave us an idea of how much data the script would see. It is not so uncommon for estimates made outside the script and counts returned by the ORM to disagree. We also ensured that the dry_run argument defaulted to true to avoid accidentally running the actual migration with all default values. We had to be explicit about setting it to false so that we could run the real migration.

All of the above are really helpful, but they often are a black box. We should never blindly trust that things will work as expected. Production data can often be unpredictable so we needed to have a way to keep track of how the migration was doing.

Observability is a migration’s best friend

Good observability is essential not only for understanding when things go wrong but also when they go right. When migrating millions of records, logging every row is not a sensible option. Nobody can read through millions of log lines to see how things are going or even search for a specific one. A needle in the haystack. Also, depending on where the logs are shipped, such a migration can easily produce a huge bill.

What we wanted instead was a summary per batch, showing the counts of successful and failed records.

backfill_new_column_to_student_segments: batch 39 completed — processed=1000 skipped=0 failed=0 total=1000

In migrations this size there are thousands of batch results, so it also helps to log an overall summary once the whole operation completes.

backfill_new_column_to_student_segments: Full operation completed — processed=46784200 skipped=0 failed=11 total=46784211

The final log above showed that out of 46 million records, 11 failed. From there we could locate the failing batches and dig into the error logs. They ended up being legacy accounts that had some dates in the far future.

For migrations that take days (like the CRM example in step 4), it was also significant to log daily checkpoints. Numbers are illustrative.

backfill_new_column_to_student_segments: Day 2 check 2026–09–03 12:00:00 GMT+1 completed — processed=1800000 skipped=0 failed=0 total=1800000
extra_data: {"last_processed_record": {"student_segment_id":1534783, "updated_at":"2026–09–03T11:01:12Z"}

Apart from these, we found it really useful to log some extra data like the last processed record’s student_segment_id along with its updated_at timestamp in each batch. That told us which record to resume from when a run hit its limit.

Conclusion

The outcome: roughly 46M rows were backfilled twice, once into our DB and once into the CRM. 11 failures, zero incidents, zero rollbacks and no pages to infra or CRM teams.

The metric I care most about is silence so “no news is good news”. Users noticed nothing. No support tickets opened and no non-technical stakeholders chased a timeline for a fix. No dashboards drifted, no CRM campaigns misfired. Flat metrics are the ultimate success story.

Data migrations are technically demanding, but the lessons that mattered most were about communication, not code. Talking to the people who get paged when things go wrong and agreeing with them on acceptable loads is the step most engineers skip. Pulling these teams in early and keeping them informed benefits everyone, rather than involving them only once there is a fire to put out. Taking heroics out of the picture protects people from burnout.

This recipe is battle-tested, but there is still room for improvement. Next up is automating the resume so nobody has to dig through logs to find the restart point.

All in all, data migrations at Preply have become boring. That is the highest compliment I can give a process.