Backfill
Retroactively populating a new data store, index, or column with historical data. Typically done as a batch job when adding a new feature that needs past data.
What is Backfill?
In short
A backfill is the process of retroactively loading historical data into a new table, column, index, or data store so it contains the past records it would otherwise be missing. It is usually run as a one-time batch job when you add a feature, fix a bug, or change a schema and need old data to look as if the new logic had always existed.
What a backfill actually is
When you ship a new feature, the code only starts producing data from the moment it goes live. A backfill fills in everything before that point. If you add a 'country' column to a 50 million row users table, every existing row has NULL until you run a job that derives country from each user's IP or address and writes it back. That job is the backfill.
The same idea applies beyond a single column. Adding a new search index means you must index every document that already exists, not just new ones. Building a new analytics table means recomputing it from years of raw events. Fixing a bug that corrupted a value means recalculating it for every affected record. All of these are backfills.
The defining trait is that you are processing data that already happened. Normal application traffic handles new data going forward. A backfill is a separate, usually temporary, batch job aimed at the historical tail.
How it works under the hood
Almost every backfill is a loop over historical records in bounded chunks. You select a batch (say 5,000 rows or one day of events), transform each record with the new logic, write the result, record where you stopped, then move to the next batch. Chunking keeps memory flat and stops a single huge transaction from locking the table or blowing up the database.
Idempotency is the property you cannot skip. A backfill of millions of rows will get interrupted, retried, or run twice. Each write must produce the same end state no matter how many times it runs, which usually means UPSERT semantics or a 'process only rows where the new column is still NULL' filter. You also store a cursor or checkpoint (last id, last timestamp) so a restart resumes instead of starting over.
Throttling matters because the backfill shares the same database and message queue as live traffic. Teams cap the job's rate, run it during off-peak hours, target a read replica where possible, and sleep between batches so production latency does not spike. A common mistake is letting an unthrottled backfill saturate disk IO and take the live service down with it.
For very large jobs the work is fanned out across many workers using a framework like Apache Spark, a distributed queue, or a partitioned set of tasks, each owning a slice of the id or date range so they run in parallel without overlapping.
When to use it and the trade-offs
Reach for a backfill whenever new behavior needs to apply to old data: a new column or index, a new derived table, a corrected calculation, a migration to a new store, or onboarding a customer who expects their full history present on day one. If old data can stay missing or computed lazily on read, you may not need one at all.
The big trade-off is consistency during the run. While the backfill is in flight, some rows have the new value and some do not, so queries see a mixed state for hours or days. You handle this by making readers tolerant of NULLs, or by dual-writing new records with the new logic while the backfill catches up the old ones, then flipping reads over only once it completes.
Cost and time are the other constraints. Backfilling tens of billions of rows can run for days and burn real compute and database load. It is worth dry-running on a sample, measuring throughput, and estimating the full duration before you start, because a half-finished backfill that has to be aborted leaves you in the messy mixed state with no clean rollback.
A concrete example
Say a streaming service adds a 'watch_completion_percent' field to its viewing history so it can recommend shows people nearly finished. New plays record it immediately, but the table holds years of old plays with the field empty.
The team writes a Spark job that reads the raw playback event log partitioned by date, computes completion percent for each historical session, and writes it back to the viewing history table keyed by session id. It runs date range by date range, checkpoints after each day, and writes idempotently so a failed day can rerun safely.
They throttle it to a fraction of cluster capacity so live recommendation queries stay fast, and they keep the recommender tolerant of empty values until the backfill reaches the present. Once the last partition lands, every play old and new has the field, and the feature behaves as if it had existed all along.
Where it is used in production
Apache Spark
The default engine for large backfills, reading historical data partitioned by date or id and recomputing derived tables in parallel across a cluster.
Airbnb
Uses Airflow DAGs with explicit start dates so a new pipeline can backfill every past day of data on its first run before processing current data.
Stripe
Built tooling around online schema migrations where adding a column triggers a chunked, throttled backfill across huge tables without locking live payment traffic.
GitHub
Runs background backfill jobs to populate new columns and rebuild search indexes across hundreds of millions of repositories and issues.
Frequently asked questions
- What is the difference between a backfill and a migration?
- A migration changes the structure, like adding a column or table. A backfill populates that new structure with historical values. Adding the column is the migration; filling every existing row with the right data is the backfill. Many schema changes need both: first the structural migration, then the data backfill.
- Why does a backfill need to be idempotent?
- Long-running jobs over millions of rows will be interrupted and retried. If running the same batch twice produces a different result, you get double counting or corruption. Idempotent writes (UPSERT, or only touching rows that are still unprocessed) mean a retry safely lands the same end state, so you can resume after any failure.
- How do I keep a backfill from slowing down production?
- Process in small chunks, throttle the rate with sleeps between batches, run during off-peak hours, read from a replica when possible, and avoid one giant transaction that locks the table. The goal is to spread the load thin enough that live query latency does not spike while the job runs.
- How do I handle queries while a backfill is only half done?
- Make readers tolerant of the missing value, treating a NULL as 'not yet computed' rather than an error. A common pattern is to dual-write new records with the new logic immediately while the backfill works through old data, then switch reads to depend on the new field only after the backfill finishes.
- How long does a backfill take?
- It depends entirely on row count, per-row work, and how hard you throttle. Always dry-run on a sample to measure rows per second, then multiply by the total. Backfills of tens of billions of rows can run for days, which is why estimating duration up front is part of planning one.
Learn Backfill hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
Related lessons
Lessons that touch on Backfill as part of a larger topic.
See also
Related glossary terms you might want to look up next.
Batch Processing
Processing large volumes of data in scheduled chunks rather than in real time. Think nightly reports, ETL jobs, and data warehouse loads.
ETL
Extract, Transform, Load: a pipeline that extracts data from sources, transforms it into the desired format, and loads it into a destination like a data warehouse.
Change Data Capture
Capturing row-level changes in a database and streaming them to other systems in real time. Debezium reads the write-ahead log and publishes changes to Kafka.
Stream Processing
Processing data continuously as it arrives, rather than in batches. Powers real-time analytics, fraud detection, and live dashboards.
Exactly-Once Processing
A processing guarantee where each message is processed exactly one time, even in the face of failures. Achieved through idempotent consumers and transactional producers.
Checkpointing
Periodically saving the state of a stream processing job so it can recover from failures without reprocessing everything from the beginning. Flink and Spark use distributed checkpoints.