You add a new derived field to your user profiles: “days since last purchase.” You can compute it for new events going forward. But existing users already have purchase history. You need to compute this field for all existing users retroactively. That’s a backfill.

Backfills happen constantly in production systems. They’re more disruptive than they look.

The Basic Problem#

You have a dataset of 500 million user records. You need to reprocess all of them to compute the new field. Running this as a single massive query on the production database is almost certainly a bad idea: it saturates I/O, slows down live queries, and might lock rows.

Better approaches:

Batch processing: run the backfill job in chunks. Process 100,000 users per batch, with a sleep between batches to give the database breathing room. Total time: hours to days depending on batch size and complexity.

Dedicated read replica: run the backfill against a read replica, not the primary. Keeps backfill load off the primary.

MapReduce or Spark job: export the data to a distributed processing system (HDFS), process in parallel, write results back. Keeps the backfill off the production database entirely.

graph TD A[New feature: days_since_last_purchase] --> B[Future: compute at event time, no problem] A --> C[Historical: 500M existing users need reprocessing] C --> D[Export user data to object storage] D --> E[Spark job: compute field for all users in parallel] E --> F[Write results back in batches] F --> G[Backfill complete: all users have field populated] style A fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style B fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style C fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style D fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style E fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style F fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style G fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff

Idempotency and Resumability#

Backfills run for hours and fail partway through. The job needs to be idempotent and resumable. Track which records have been processed with a progress marker. On restart, skip already-processed records.

For database backfills, process in sorted order (by user ID or created_at) and store the last processed ID. On restart, start from WHERE user_id > last_processed_id.

Dual Writing During Backfill#

The system is live during the backfill. New events arrive and new user records are created. You need to handle the case where a new user’s record is created while the backfill is running.

One approach: mark records that need backfilling with a flag at the start of the job. Process only flagged records. New records created after the backfill started won’t have the flag, and your new code will handle them going forward.

Another approach: compute the field for all records processed after a timestamp. The backfill processes records with created_at < backfill_start_time. New records are handled by the new code.

Reprocessing Event History#

A different flavor: you have an event stream and you want to reprocess historical events with new business logic. Maybe you added a new categorization rule and want to apply it to events from the last year.

Kappa architecture handles this well: replay the event stream from the desired historical point, applying the new logic. The stream processor produces a new output stream that can be used to update the current state.

At Oracle#

We changed our configuration scoring algorithm and needed to apply it to all 4000+ existing configurations. The production scoring ran in-process during API calls. We couldn’t run a background job without adding complexity. We built a maintenance endpoint: POST /admin/rescore-all that ran the scoring job in the background, processing 100 configs per second, writing results to a temporary table, then swapping the table at the end. Total time: 40 seconds. Risk: minimal. We used this pattern every time we changed the scoring algorithm.

What I’m Learning#

Backfills are underestimated in planning and overrun in execution. The failure modes are database load, partial completion, and race conditions with live writes. Designing systems with backfillability in mind from the start (idempotent writes, processing order by a monotonic key) makes backfills routine instead of painful.

Have you run backfills that took much longer than expected, and what made them harder than anticipated?