What It Actually Takes to Archive Billions of Financial Records Without Taking Production Down

Database archival sounds deceptively simple.

Move old rows somewhere cheaper. Delete them from the primary database. Reclaim the space.

At small scale, that description isn’t entirely wrong.

At billions of rows, thousands of writes per second, and a financial system that can’t simply stop while you clean things up, archival starts looking a lot more like a distributed systems problem.

The problem isn’t just that the database is large

Large transactional databases rarely become a problem overnight.

They grow gradually.

A table goes from millions of rows to hundreds of millions, and eventually billions. Indexes grow with it. Vacuum has more work to do. Maintenance becomes more expensive. Queries operate against increasingly large structures.

Eventually, a database designed primarily to serve current transactional workloads is also carrying years of historical state that is rarely accessed.

That’s where an interesting asymmetry appears.

Recent data is operationally important and frequently accessed. Historical data can be enormous but comparatively cold. Yet both continue occupying the same transactional infrastructure.

The records might have exactly the same schema, but their operational requirements are completely different.

Recent transactions might be involved in customer requests, reconciliation, retries, or active workflows. A five-year-old transaction might need to exist for compliance or an occasional audit.

Treating both identically forever is convenient.

At some point, it also becomes expensive.

Why not just delete the old rows?

The obvious solution looks something like this:

DELETE FROM transactions
WHERE created_at < cutoff;

Unfortunately, deleting a few billion rows from a live PostgreSQL database isn’t quite that simple.

PostgreSQL’s MVCC model means deleting a row doesn’t immediately remove its physical representation from storage.

Large deletion workloads can create huge numbers of dead tuples, increase vacuum pressure, generate WAL, increase replication load, and compete with the transactional workload for I/O and CPU.

And deleting the data isn’t even the hardest part.

Before removing anything, you need to know:

Has this record actually been archived safely?

That introduces a much stronger requirement.

Every record eligible for deletion must first exist durably in the archive, while the production system continues accepting writes.

Now we’re no longer talking about a cleanup script.

We’re talking about an online data migration.

The race between your snapshot and production

Imagine taking a snapshot of every row older than a particular cutoff.

While that snapshot is running, production doesn’t stop.

Records continue to change.

Some might be updated, retried, or otherwise modified around the boundary you’re trying to archive.

A naive workflow would be:

1. Copy historical rows.
2. Verify that the copy completed.
3. Delete those rows from production.

But what exactly does “completed” mean if the source is changing underneath you?

This is where archival starts intersecting with concepts we normally associate with distributed systems: snapshots, change streams, ordering, idempotency, replay, and consistency boundaries.

One useful architecture is to combine two views of the database.

A historical snapshot moves the bulk of the existing dataset.

A Change Data Capture (CDC) stream captures mutations happening while that snapshot is being processed.

Conceptually:

PostgreSQL → Historical Snapshot → Archive

while simultaneously:

PostgreSQL → CDC → Archive

The difficult part is reconciling those two paths without losing records or processing enormous numbers of them twice.

Deduplication can become a scaling problem of its own

Once you combine snapshots and CDC, overlap is inevitable.

A record can appear in both.

The obvious response is to maintain a set of IDs that have already been processed.

But at several billion records, the deduplication mechanism itself can become a substantial data structure.

You end up building a massive secondary system just to remember which records your massive primary migration system has already seen.

Bloom filters are one possible answer. They can provide extremely memory-efficient membership checks when their probabilistic nature works with the surrounding protocol.

But there’s another interesting possibility when the identifiers themselves have useful properties.

If IDs are monotonically increasing, ordering gives you information.

Instead of treating every identifier as an independent membership question, you may be able to use that ordering to dramatically reduce how much state needs to be remembered.

There’s a broader systems lesson here that I like:

Before adding infrastructure to solve a scale problem, look for invariants already present in the data.

Ordering, immutability, timestamps, lifecycle states, and monotonic identifiers can sometimes eliminate entire classes of coordination.

The Celebrity Partition Problem

There’s another problem hiding in the shape of large datasets: skew.

Suppose you decide to distribute archival work by merchant.

At first glance, this seems ideal. There are lots of merchants, so distribute them across workers and process everything in parallel.

Except transaction volume isn’t evenly distributed.

Most merchants might have relatively modest histories.

A tiny number can have enormous ones.

Most workers finish quickly while a handful keep processing these giant partitions. The entire migration is now effectively waiting for a few extreme outliers.

I recently came across a production study by Tulika Manek, Lifecycle-Aware Archival for Asymmetric Financial Datasets: A Production Study, which describes this behavior as the Celebrity Partition Problem.

I like the term because it captures something that’s surprisingly easy to miss when designing large data-processing systems:

High cardinality doesn’t guarantee uniform distribution.

Having millions of possible values for a partition key doesn’t mean those values represent equal amounts of work.

A partitioning strategy that looks perfectly reasonable from the schema can behave very differently against real production data.

Archiving something and deleting it aren’t the same thing

Another useful distinction is separating these two statements:

This record has been successfully archived.

from:

This record is now safe to delete from production.

They sound almost equivalent.

Operationally, they’re very different.

Archival is concerned with moving data durably and completely.

Deletion is destructive.

The confidence threshold for the second operation should therefore be significantly higher.

This also means the archival pipeline and purge process don’t necessarily need to move at the same speed.

The archival pipeline can continue making progress while deletion happens conservatively — in controlled batches, with verification, throttling, and safeguards around production health.

This distinction becomes particularly important with financial data.

Keeping a record in the primary database a little longer has a storage cost.

Deleting it before you’re certain it exists safely elsewhere can have a much larger cost.

Production still has to win

There’s another trap with large migrations.

Suppose the archival process is taking too long.

You increase the batch size.

Add more workers.

Increase concurrency.

Parallelize more aggressively.

The migration gets faster.

Then PostgreSQL CPU increases. Replication starts falling behind. Vacuum gets fewer resources. Transaction latency increases.

You’ve successfully optimized the migration while making the actual product worse.

That’s the wrong optimization target.

The goal can’t simply be:

Archive the dataset as quickly as possible.

It has to be:

Archive the dataset as quickly as production safely allows.

Those are fundamentally different objectives.

CPU, I/O pressure, replication lag, vacuum behavior, transaction latency, and application error rates effectively become constraints on the archival system.

A production migration should behave less like a batch job racing toward a deadline and more like a background workload that understands it’s a guest on the database.

Deleting data doesn’t necessarily give you the disk space back

There’s one more PostgreSQL detail that’s easy to overlook.

Successfully deleting billions of rows doesn’t necessarily mean the operating system immediately gets all of that storage back.

Logical deletion and physical storage reclamation are different things.

That’s especially important if infrastructure cost is one of the reasons you’re doing archival in the first place.

You can successfully archive and delete a huge fraction of a table while seeing surprisingly little immediate reduction in allocated storage.

So storage reclamation can’t simply be assumed to happen as a consequence of deletion.

It needs to be considered as part of the overall archival strategy.

Data has a lifecycle, not just a schema

This is probably the idea I find most interesting about the entire problem.

We spend a lot of time designing databases around schema.

What entities exist?

How are they related?

What indexes do we need?

How should we partition them?

At sufficient scale, there’s another question worth asking:

How does this data age?

Some data remains operationally hot.

Some becomes effectively immutable.

Some needs to exist primarily for reconciliation or compliance.

Some may only be queried occasionally.

These records can all share exactly the same schema while having radically different storage and performance requirements.

A transaction created five seconds ago and one created five years ago might look identical from the database’s perspective.

From the application’s perspective, they’re not identical at all.

That’s what makes lifecycle-aware architecture an interesting way of thinking about large datasets.

Instead of treating archival as periodic database housekeeping, you treat aging as a property of the data itself.

And the question changes from:

“How do we delete billions of old rows?”

to:

“What infrastructure should this data live on at each stage of its lifecycle?”

For sufficiently large transactional systems, that might be just as important as deciding how the data should be modeled in the first place.

Reference

A number of the production archival ideas discussed here are explored in much greater depth in:

Manek, Tulika. “Lifecycle-Aware Archival for Asymmetric Financial Datasets: A Production Study.” arXiv preprint arXiv:2608.12367 (2026).

Paper: [Link to paper]

The paper presents the underlying production study and goes deeper into the architecture, algorithms, handling of asymmetric financial datasets, deduplication, partition skew, consistency considerations, and empirical results.

If you’re dealing with PostgreSQL at scale, CDC pipelines, database archival, or large financial datasets, it’s worth reading the original work.