A database that works well with 100,000 records may behave differently once it contains several million. Queries become more expensive, indexes take up more storage, and routine maintenance can take longer than expected.
Consider a transaction processing system that stores every completed transaction in the same database tables. Most users only need recent transactions, perhaps from the last three to six months. However, the application continues querying tables containing several years of historical data.
This is where data archiving becomes useful.
With Entity Framework Core (EF Core), we can implement an archiving process that moves older records out of active tables while keeping them available for reporting, auditing, or future reference.
Archiving Is More Than Moving Old Records
The basic idea is simple. Identify records that are no longer frequently accessed, copy them to an archive location, and remove them from the active database.
The implementation becomes more complicated when those records have relationships with other tables.
For example, a payment transaction might have related payment details, settlement records, and audit entries. Moving only the main transaction could leave incomplete data behind.
Before implementing archiving, we need to decide which records are eligible, how their relationships will be preserved, and how archived data can be retrieved.
A typical design might use these rules:
- Archive transactions older than 12 months.
- Include only completed or finalized transactions.
- Exclude records involved in unresolved disputes or investigations.
- Preserve related records and required audit information.
- Delete active records only after confirming that the archive operation succeeded.
The retention period should come from business and regulatory requirements, not simply from a developer’s preference.
Choosing Where to Store Archived Data
There are several ways to organize archived records.
Separate tables in the same database are relatively easy to implement and maintain. However, they still share database storage and infrastructure resources.
A separate archive database provides better isolation between active and historical data. It also allows different backup and maintenance policies, although reporting across databases becomes more complicated.
Database partitioning is another option. Depending on the database engine, partitions can help manage large tables and simplify certain data maintenance operations. EF Core can work with partitioned tables, but partition creation and management are generally database-specific.
For applications that need to reduce the size of their operational database, I would usually evaluate a separate archive database first. It introduces additional operational work, but provides a clearer separation between current and historical transactions.
Implementing Batch Archiving with EF Core
Suppose we have a Transactions table with millions of records. We want to archive completed transactions older than one year.
Instead of loading all eligible records into memory, we can process them in smaller batches.
The following example uses EF Core with two database contexts, AppDbContext and ArchiveDbContext.
The code examples in this article are simplified for demonstration purposes and are not taken from an actual production project.
var cutoffDate = DateTime.UtcNow.AddYears(-1);
const int batchSize = 500;
var transactions = await appDb.Transactions
.AsNoTracking()
.Where(t =>
t.Status == "Completed" &&
t.CreatedAt < cutoffDate)
.OrderBy(t => t.Id)
.Take(batchSize)
.ToListAsync();
var archivedTransactions = transactions.Select(t =>
new ArchivedTransaction
{
OriginalId = t.Id,
Amount = t.Amount,
CreatedAt = t.CreatedAt,
ArchivedAt = DateTime.UtcNow
}).ToList();
archiveDb.ArchivedTransactions
.AddRange(archivedTransactions);
await archiveDb.SaveChangesAsync();Using AsNoTracking() avoids unnecessary change tracking for records that we only need to read. The Take() method limits the number of records processed in one batch.
The example intentionally stops after copying records. It does not delete anything from the source database.
This is important because saving data to two different databases does not automatically make the entire archiving operation atomic.
Deleting Records Safely After Archiving
Deleting the source records is probably the most sensitive part of the process.
If the application crashes after copying records but before deleting them, the next execution might try to archive the same records again. If deletion happens before the archive is properly verified, we risk losing data.
A safer approach is to make the archiving process recoverable.
Each batch should have a unique identifier and a processing status. The archive should also enforce uniqueness using the original transaction identifier, preventing duplicate records during retries.
Once the records have been copied, verify that all expected records and their required related data exist in the archive. Only then should the source records become eligible for deletion.
EF Core 7 and later support ExecuteDeleteAsync(), which can delete matching records directly without loading them into memory. However, this operation must use the exact verified record identifiers, account for concurrent changes, and respect foreign key constraints.
For separate databases, we also need to consider that a local transaction in one database does not automatically protect operations performed in the other. A recoverable process with proper validation is often easier to operate than relying on distributed transactions.
Scheduling and Monitoring the Archiving Process
Archiving should normally run as a background process rather than as part of an API request.
For a .NET application, a hosted background service or a scheduled job can handle this work. The appropriate choice depends on the deployment environment and how much control is needed over scheduling and retries.
I would also record the following information for every execution: start and completion times, batch identifier, number of records copied, number verified and deleted, and any errors encountered.
These details become useful when a job fails halfway through processing.
Another important consideration is database performance. Even if archiving is intended to improve performance, processing too many records at once can create unnecessary load, generate transaction logs, and affect normal application operations.
Start with a manageable batch size, monitor the actual database impact, and adjust based on the results.
Final Thoughts
Data archiving is not simply a database cleanup activity. It is part of how we manage data throughout an application’s lifetime.
EF Core provides useful tools for querying, copying, and deleting records, but the framework does not automatically handle the operational risks of moving data between databases.
For me, the most important design decision is making sure the process can recover from failure without losing or duplicating records.
Moving old data is relatively straightforward. Making sure we can trust the archived data years later requires more careful planning.
