Long-Running and Batch Jobs: Chunking, Checkpoints and Surviving Deploy Interruptions

Version note: The code in this post was built and run with .NET 10, Entity Framework Core (EF Core) 10 (Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3), PostgreSQL 17 and Hangfire 1.8.25; timings were measured with PostgreSQL running in Docker on a 10-core development machine.

Guess First

Bookstore is moving to a new price list: the price of 2 million books goes up 8 percent. Is it faster to do this in a single UPDATE or in chunks of 20,000? If customer service wants to fix one book's price while the update runs, how long does it wait with each method? And if the app is deployed to a new version while the work is at 40 percent, what happens: does it start over, carry on from the remaining 60 percent, or do some books get the price rise twice?

The Cost of a Single Transaction

For the experiment I filled PostgreSQL with a 2-million-row books table; each book has a price and a PriceListVersion column holding the version of its current price list. The shortest path is a single SQL (Structured Query Language) statement with EF Core's ExecuteUpdate:

db.Database.SetCommandTimeout(TimeSpan.FromMinutes(10));
int updated = await db.Books
    .Where(b => b.PriceListVersion < 7)
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.Price, b => Math.Round(b.Price * 1.08m, 2))
        .SetProperty(b => b.PriceListVersion, 7));

single statement: 2000000 rows in 6.8 s
single statement: 2000000 rows in 4.7 s
single statement: 2000000 rows in 3.9 s

Across three tries it took between 3.9 and 6.8 seconds. Looks fast, but time isn't the real cost. One statement means one transaction: all 2 million rows stay locked until commit. Two seconds after the update started, I updated a single book's price in the same table:

UPDATE books SET "Price" = 19.90 WHERE "Id" = 42;
UPDATE 1
Time: 1927.121 ms (00:01.927)

A one-row update waited 1.9 seconds, because the big transaction had already locked that row. On a real table this update takes minutes, and every request writing to the table waits the whole time; the API (Application Programming Interface) timeouts start firing. And if the work hits an error midway or the process dies, everything is rolled back: all the effort of a transaction that updated 1.9 million rows is lost, and the rollback itself takes time.

Chunks and Keysets

The alternative is to split the work into small pieces. Each chunk in its own transaction; locks are short, and an error only rolls back that chunk:

public sealed class PriceUpdateJob(
    IServiceScopeFactory scopeFactory,
    ILogger<PriceUpdateJob> logger) : BackgroundService
{
    private const string JobName = "price-list-v7";
    private const int TargetVersion = 7;
    private const int BatchSize = 20_000;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        long start = Stopwatch.GetTimestamp();
        long lastId = await LoadCheckpointAsync();
        logger.LogInformation("Starting {Job} from id {LastId}", JobName, lastId);

        while (!stoppingToken.IsCancellationRequested)
        {
            await using AsyncServiceScope scope = scopeFactory.CreateAsyncScope();
            var db = scope.ServiceProvider.GetRequiredService<BookstoreDbContext>();

            // the upper bound of the next chunk: keyset, not OFFSET
            long? upperId = await db.Books
                .Where(b => b.Id > lastId)
                .OrderBy(b => b.Id)
                .Skip(BatchSize - 1)
                .Select(b => (long?)b.Id)
                .FirstOrDefaultAsync(CancellationToken.None)
                ?? await db.Books.Where(b => b.Id > lastId).MaxAsync(b => (long?)b.Id, CancellationToken.None);
            if (upperId is null)
                break; // done

            // the chunk and the checkpoint in the same transaction: both or neither
            await using var tx = await db.Database.BeginTransactionAsync(CancellationToken.None);
            int updated = await db.Books
                .Where(b => b.Id > lastId && b.Id <= upperId && b.PriceListVersion < TargetVersion)
                .ExecuteUpdateAsync(s => s
                    .SetProperty(b => b.Price, b => Math.Round(b.Price * 1.08m, 2))
                    .SetProperty(b => b.PriceListVersion, TargetVersion), CancellationToken.None);
            await db.JobCheckpoints
                .Where(c => c.JobName == JobName)
                .ExecuteUpdateAsync(s => s
                    .SetProperty(c => c.LastProcessedId, upperId.Value)
                    .SetProperty(c => c.UpdatedAtUtc, DateTime.UtcNow), CancellationToken.None);
            await tx.CommitAsync(CancellationToken.None);

            lastId = upperId.Value;
            if (lastId % 200_000 == 0)
                logger.LogInformation("Checkpoint {LastId}, {Updated} rows in last chunk", lastId, updated);
        }

        logger.LogInformation("{Job} stopped at id {LastId} after {Elapsed:F1} s (cancelled={Cancelled})",
            JobName, lastId, Stopwatch.GetElapsedTime(start).TotalSeconds, stoppingToken.IsCancellationRequested);
    }
}

There are three design decisions. The first is the keyset: each chunk is defined as "the first 20,000 rows with an id greater than lastId", not with OFFSET. OFFSET 1900000 means the database reads and skips 1.9 million rows; toward the end of the work every chunk is slower than the one before. A keyset jumps straight to the right place through the primary key index.

The second is an idempotent WHERE: PriceListVersion < TargetVersion. If a chunk runs twice, the second run changes no rows; the 8 percent rise isn't applied twice. The rule from the seventh post: make a job that writes "increase by this much" conditional as "bring to this version".

The third is using CancellationToken.None inside the chunk. That's deliberate: when the shutdown signal arrives we don't want to cut the chunk in half, we want to stop at the top of the loop after the chunk finishes and commits. Cutting the chunk would also be safe (the transaction is rolled back), but the work would be wasted.

A Checkpoint Table: Remembering Where It Stopped

The loop keeps lastId in memory; if the process restarts, that's gone. So after each chunk we write to a checkpoint table:

public sealed class JobCheckpoint
{
    public required string JobName { get; set; }
    public long LastProcessedId { get; set; }
    public DateTime UpdatedAtUtc { get; set; }
}

// inside PriceUpdateJob
private async Task<long> LoadCheckpointAsync()
{
    await using AsyncServiceScope scope = scopeFactory.CreateAsyncScope();
    var db = scope.ServiceProvider.GetRequiredService<BookstoreDbContext>();
    JobCheckpoint? checkpoint = await db.JobCheckpoints.FindAsync(JobName);
    if (checkpoint is null)
    {
        db.JobCheckpoints.Add(new JobCheckpoint { JobName = JobName, UpdatedAtUtc = DateTime.UtcNow });
        await db.SaveChangesAsync();
        return 0;
    }
    return checkpoint.LastProcessedId;
}

The critical point is that the checkpoint is updated in the same transaction as the chunk. In separate transactions, a process dying between the two commits would leave either a chunk that's updated but not recorded in the checkpoint (harmless thanks to the idempotent WHERE, it's just read again) or one that's recorded in the checkpoint but not updated (a silent data bug: those 20,000 books stay at the old price). The same transaction guarantees the two always go together.

I ran the whole job this way and tried the same one-row update again at the same time:

05:23:30.317 info: Bookstore.Pricing.PriceUpdateJob[0] Starting price-list-v7 from id 0
05:23:30.881 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 200000, 20000 rows in last chunk
05:23:31.358 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 400000, 20000 rows in last chunk
[...]
05:23:35.184 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 2000000, 20000 rows in last chunk
05:23:35.197 info: Bookstore.Pricing.PriceUpdateJob[0] price-list-v7 stopped at id 2000000 after 5.2 s (cancelled=False)

UPDATE books SET "Price" = 19.90 WHERE "Id" = 42;
UPDATE 1
Time: 4.279 ms

The one-row update finished in 4 ms instead of 1.9 seconds; at worst it waits for the duration of one chunk. The total time is 5.2 seconds, roughly the same order as the single statement; chunks add extra queries and commits, but at this scale that isn't decisive.

Cut Off During a Deploy, and Carrying On

The real experiment. I reset the table, started the job, and 2.3 seconds later sent the process SIGTERM (terminate signal), i.e. what a deploy would do:

05:24:02.979 info: Bookstore.Pricing.PriceUpdateJob[0] Starting price-list-v7 from id 0
05:24:03.516 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 200000, 20000 rows in last chunk
05:24:03.967 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 400000, 20000 rows in last chunk
05:24:04.440 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 600000, 20000 rows in last chunk
05:24:04.893 info: Microsoft.Hosting.Lifetime[0] Application is shutting down...
05:24:04.927 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 800000, 20000 rows in last chunk
05:24:04.927 info: Bookstore.Pricing.PriceUpdateJob[0] price-list-v7 stopped at id 800000 after 2.3 s (cancelled=True)

version 6: 1200000 books
version 7: 800000 books

When "Application is shutting down" arrived, the job was in the middle of a chunk. It finished the chunk, wrote 800,000 to the checkpoint, saw at the top of the loop that the token was cancelled and exited. In the database exactly 800,000 books are at the new price, and the checkpoint is at 800,000. Then I started the job again:

05:24:06.134 info: Bookstore.Pricing.PriceUpdateJob[0] Starting price-list-v7 from id 800000
05:24:06.689 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 1000000, 20000 rows in last chunk
[...]
05:24:08.947 info: Bookstore.Pricing.PriceUpdateJob[0] Checkpoint 2000000, 20000 rows in last chunk
05:24:08.955 info: Bookstore.Pricing.PriceUpdateJob[0] price-list-v7 stopped at id 2000000 after 3.2 s (cancelled=False)

version 7: 2000000 books

It carried on from where it stopped and every book was updated exactly once. Since the cut happened at a chunk boundary, no row was processed twice; even if one had been, the idempotent WHERE would have prevented a second rise.

When ShutdownTimeout Isn't Enough

In this experiment a chunk took about half a second and finished comfortably inside the host's 30-second shutdown budget. That's exactly what you need to measure when choosing the chunk size: the worst-case duration of a single chunk must be clearly shorter than the ShutdownTimeout we saw in the second post of the series and Kubernetes' terminationGracePeriodSeconds. If not, time runs out while the host waits for the chunk, the process is killed and the chunk's transaction is rolled back. The data stays consistent (the checkpoint is in the same transaction), but that chunk's work is wasted on every deploy, and in a bad case the job never makes progress.

The rule is simple: design the work as if it could be killed at any moment. A durable progress record, short and atomic steps, logic that tolerates steps being repeated. For such a job, a deploy isn't a disaster, just a short pause.

Long Jobs in Hangfire: Sliding Invisibility

If you run this work as a Hangfire job, there's one more mechanism to know. When a worker takes a job from the queue, the job becomes "invisible" to the other workers. I printed Hangfire.SqlServer 1.8's default: SlidingInvisibilityTimeout=00:05:00. The sliding part is that the worker renews this window regularly while the job runs; Hangfire's 1.6.14 release notes say it was added so you no longer have to set very large timeouts for long jobs. As long as the worker lives, the job can run for hours; if the worker dies, renewal stops and another worker takes the job once the window passes.

To see this, I lowered the window to 30 seconds and started a 60-second catalog export job, killed the process with kill -9 after 8 seconds and restarted it right away:

.UseSqlServerStorage(builder.Configuration.GetConnectionString("Hangfire"), new SqlServerStorageOptions
{
    SlidingInvisibilityTimeout = TimeSpan.FromSeconds(30) // 5 minutes by default
})

public sealed class CatalogExportJob(ILogger<CatalogExportJob> logger)
{
    public async Task ExportAsync(CancellationToken ct)
    {
        logger.LogInformation("Catalog export started (pid {Pid})", Environment.ProcessId);
        await Task.Delay(TimeSpan.FromSeconds(60), ct);
        logger.LogInformation("Catalog export finished (pid {Pid})", Environment.ProcessId);
    }
}

05:25:58.450 info: Bookstore.Jobs.CatalogExportJob[0] Catalog export started (pid 28490)
05:26:05.976 kill -9
05:26:34.546 info: Bookstore.Jobs.CatalogExportJob[0] Catalog export started (pid 28547)
05:26:57.506 warn: Hangfire.Server.Worker[0] Worker stop requested while processing background job '3'. It will be re-queued.

The process died at 26:05.976, and the new process picked the job up at 26:34.5: about 30 seconds. With the default 5 minutes, it would have been 5 minutes; the job runs nowhere during that time. And the job started from scratch: ExportAsync doesn't know where it stopped. Hangfire jobs need the checkpoint logic above too.

Graceful shutdown is different. When I stopped the new process with SIGTERM, Hangfire logged: "Worker stop requested while processing background job '3'. It will be re-queued." The CancellationToken we gave the job was cancelled, the job was put back in the queue, and the next server can take it right away; there's no need to wait out the invisibility window. One more reason to put a CancellationToken parameter on job methods.

Throughput: Batch Size and Parallelism

You don't have to process chunks one after another. If the id ranges are independent, you can run several chunks at once with the Parallel.ForEachAsync from the C# Async series post that covers parallelism including PLINQ (Parallel Language Integrated Query), "C# Parallelism: Parallel.For, Parallel.ForEachAsync, PLINQ and When to Use Task.Run":

int dop = int.Parse(builder.Configuration["Dop"] ?? "4");
var factory = host.Services.GetRequiredService<IServiceScopeFactory>();
long maxId = await db.Books.MaxAsync(b => b.Id);
var ranges = Enumerable.Range(0, (int)Math.Ceiling(maxId / 20_000d))
    .Select(i => (From: i * 20_000L, To: (i + 1) * 20_000L));

long start = Stopwatch.GetTimestamp();
int total = 0;
await Parallel.ForEachAsync(ranges, new ParallelOptions { MaxDegreeOfParallelism = dop }, async (range, ct) =>
{
    // each parallel branch with its own DbContext: DbContext isn't thread-safe
    await using AsyncServiceScope s = factory.CreateAsyncScope();
    var chunkDb = s.ServiceProvider.GetRequiredService<BookstoreDbContext>();
    int n = await chunkDb.Books
        .Where(b => b.Id > range.From && b.Id <= range.To && b.PriceListVersion < 7)
        .ExecuteUpdateAsync(u => u
            .SetProperty(b => b.Price, b => Math.Round(b.Price * 1.08m, 2))
            .SetProperty(b => b.PriceListVersion, 7), ct);
    Interlocked.Add(ref total, n);
});
Console.WriteLine($"dop={dop}: {total} rows in {Stopwatch.GetElapsedTime(start).TotalSeconds:F1} s");

I reset the table and ran VACUUM before every measurement:

dop=1: 2000000 rows in 3.1 s
dop=2: 2000000 rows in 2.3 s
dop=4: 2000000 rows in 2.1 s
dop=8: 2000000 rows in 2.2 s

Going from parallelism 1 to 2 brought the time from 3.1 down to 2.3 seconds, 4 gave 2.1 seconds, and 8 no longer helps: 2.2 seconds. The bottleneck isn't the app but the database; branches writing to the same table compete for disk, WAL (Write-Ahead Log) and locks. In production, that ceiling is also shared with the capacity the database must keep for the API; wearing out the whole database for a nightly job slows down the customer placing an order at that hour. Pick the degree of parallelism not by the fastest value but by the side load the database can take.

Two details to watch. Each parallel branch opens its own scope and its own DbContext; DbContext isn't thread-safe. And with parallel chunks a single "last processed id" checkpoint isn't enough, because chunks don't finish in order; either record each chunk's completion in its own row, or, as here, rely on the idempotent WHERE and walk all ranges again on restart.

In Production

Make progress visible. The checkpoint table already answers "where is the job"; wire it to a health check or a dashboard. A job sitting at the same checkpoint for 2 hours is a job that isn't running.

Pick the chunk size by measuring. A small chunk means shorter locks and a shorter shutdown, but more queries and commits. 20,000 was a good value for this table and this machine; indexes, triggers and replication on your table may give a different number. Log a single chunk's duration and watch its p99.

Watch replication lag. A big bulk update can raise read replicas' lag for minutes. A short pause between chunks gives the replicas a chance to catch up.

Common Pitfalls

Updating 2 million rows in one transaction. Long locks, a big log, and everything rolled back on failure.

Advancing chunks with OFFSET. Every chunk gets slower toward the end of the table; and if rows are inserted or deleted during the run, chunks drift. Use a keyset.

Writing the checkpoint in a separate transaction. A process dying between the two commits can mark a chunk that wasn't updated as "done".

Relative updates and repeats. Price = Price * 1.08, written unconditionally, applies a second rise to every chunk that runs again. Make it conditional on a version column or a target value.

Relying on the default invisibility window. In Hangfire, if the process dies, a long job runs nowhere for 5 minutes; and when it does run, it starts from scratch.

A Small Experiment

★☆☆ Set BatchSize to 1,000, 20,000 and 200,000 and measure the total time. How does the one-row update's wait time change with the chunk size?

★★☆ Add a sturdy checkpoint to the parallel version: write each completed range to a table keyed by (JobName, RangeStart) and process only the missing ranges on restart. Try it by cutting it off with SIGTERM and starting again.

★★★ Make CatalogExportJob checkpointed: write progress to a table every 1,000 books and have the job carry on from there when it restarts. Kill it with kill -9 and verify that after the invisibility window the job resumes where it left off.

In the next post we learn to see all of this work: opening the Hangfire Dashboard safely in production, why the Quartz 4 dashboard won't start without authorization, a "last successful run older than 26 hours" health check, and job metrics and traces with OpenTelemetry.