Data Expiration and Cleanup

Implementing Background Jobs for Batch Deletions

In this lesson, you will learn how to build background workers that efficiently remove stale data in batches to prevent performance degradation.

Why Batching Matters

While many caching systems use automatic TTL (Time-To-Live) settings, relying solely on them can lead to "cleanup spikes." If a large set of keys expires simultaneously, the database or cache engine may freeze while attempting to reclaim that memory. Furthermore, in persistent stores like relational databases, simply waiting for a record to be "old" isn't enough; you must proactively delete it to maintain index health and table performance.

Instead of running a massive DELETE statement that locks rows for seconds or minutes, we use background workers to perform batch deletions. This involves processing data in small, manageable chunks—typically 100 to 1,000 rows at a time—with a short sleep interval between cycles.

Implementation Strategy

A robust background job follows a "Find and Delete" pattern. By iterating through keys or primary keys in small windows, you minimize the impact on your read/write throughput:

-- Pattern for a background cleanup worker
-- 1. Identify the batch
-- 2. Delete the batch
-- 3. Loop until no more records are found

WHILE (1=1)
BEGIN
    DELETE TOP (500) FROM UserSessions
    WHERE LastAccessed < DATEADD(day, -30, GETDATE());

    IF @@ROWCOUNT = 0 BREAK;
    
    -- Sleep to allow other transactions to interleave
    WAITFOR DELAY '00:00:01'; 
END

Balancing Throughput and Stability

When implementing these jobs, your primary goal is to remain invisible to your users.

1. Throttling: If your metrics show that the cleanup task is increasing your cache hit ratio latency, increase the WAITFOR duration between batches.
2. Indexing: Always ensure the column used to identify "stale" data (e.g., LastAccessed) is indexed. Deleting based on a non-indexed column will cause a full table scan, effectively locking your entire database during the cleanup process.
3. Observability: Track the number of rows deleted per cycle. If your cleanup job consistently fails to keep up with the rate of new stale data, it is a clear signal that you need to scale your infrastructure or shorten your TTL intervals.

Key Takeaway: Batch your deletions using throttled background tasks to reclaim memory and storage without causing lock contention or performance spikes.