How do you archive old data and safely purge partitions?

Question

IT Interview Practice · Accepted Answer

Start by deciding **which data can leave the active system and how it will be retrieved afterward**. This determines what can safely be deleted.

Archiving preserves a usable historical copy. Purging removes the active copy. Treat them as separate decisions: an export finishing successfully does not prove that deletion is safe.

## 1. Define the data that is eligible

Suppose an order system keeps completed orders from the last twelve months online. This is an illustrative policy. An older order with an unresolved dispute may still need to remain active.

Agree on the cutoff, time zone, exceptions and historical lookup needs. If the business needs to retrieve an old invoice, the archive must preserve the identifiers and relationships required to do that.

**Partitioning** divides one logical table into physical pieces, such as a piece for each month. Removing a monthly partition makes sense only when every record inside is eligible. Otherwise, move the exceptions safely or choose a more selective deletion method.

## 2. Prevent changes from escaping the archive

Imagine copying an order and then correcting its amount before deleting the original. The exported copy now contains the wrong amount.

Choose how to close this gap:

- Stop writes to the selected range before taking a consistent snapshot.
- Or capture changes during copying and apply them through an agreed final cutoff before deletion.

Decide where late records and later corrections will go. Merely selecting old timestamps does not prevent another process from changing those rows.

## 3. Prove that the saved copy is usable

```text
[Eligible range] -> [Consistent export] -> [Archive + manifest]
                                                |
                                      [Restore and verify]
```

A manifest describes what was exported: range, schema version, counts and file checksums. It helps identify the archive and detect incomplete or damaged files.

Verify more than the presence of a file:

- Compare record counts and meaningful totals, such as amounts grouped by currency.
- Check integrity, identifiers and relationships.
- Restore representative data in an isolated environment and retrieve the historical orders or invoices users need.

Record the verified source cutoff and who checked it. Protect archive access and define its retention too. The original can be removed only after the required recovery path has been demonstrated.

## 4. Choose how to remove the active copy

| Situation | Approach | What to verify |
| --- | --- | --- |
| A whole partition is eligible | Detach or drop it through a planned maintenance procedure | Archive verification, locks and dependent objects. |
| Eligible rows are mixed with active rows | Delete in small, resumable batches | Stable selection, transaction size and replica lag. |

In PostgreSQL, detaching a partition leaves a standalone table that can be archived and checked before dropping. This can be a useful sequence if historical access is routed to that detached table while verification runs.

Partition removal avoids deleting rows individually, but still needs locks. Concurrent detach has restrictions, so check the deployed version and dependencies. For batched deletion, also monitor transaction logs and cleanup work. [PostgreSQL partition maintenance](https://www.postgresql.org/docs/current/ddl-partitioning.html).

## 5. Make retries safe

Persist progress as exported, verified, ready for deletion and purged. After a restart, the job must know the exact verified range rather than recomputing a broader cutoff.

Before deleting, recheck that no unarchived changes entered that range. Afterward, confirm that active queries still work and the archived records remain retrievable.

**Remember:** define eligibility → control changes → prove restoration → choose deletion method → verify the result. The goal is less active data with a reliable historical access path.

Situation	Approach	What to verify
A whole partition is eligible	Detach or drop it through a planned maintenance procedure	Archive verification, locks and dependent objects.
Eligible rows are mixed with active rows	Delete in small, resumable batches	Stable selection, transaction size and replica lag.

1. Define the data that is eligible

2. Prevent changes from escaping the archive

3. Prove that the saved copy is usable

4. Choose how to remove the active copy

5. Make retries safe

Related questions