What Are Common Archival Strategies for Old Database Data?
Learn how time-based partitioning and archival pipelines move old data to cheap storage while keeping it retrievable.
Expected Interview Answer
Archival strategies move old, rarely accessed data out of the primary operational database into cheaper long-term storage — via time-based partition archiving, export-to-object-storage pipelines, or dedicated archive tables — while keeping the data retrievable for compliance, auditing, or occasional historical queries.
Rather than letting an operational table grow indefinitely with data nobody actively queries, teams typically partition by a time column (like created_at), then periodically detach old partitions and either move them to a separate archive database, export them as compressed files to object storage, or migrate them to a data warehouse optimized for infrequent analytical queries. This keeps the primary table small and fast for day-to-day operations, since indexes and the buffer pool no longer have to account for years of dormant rows, while retention policies and legal/compliance requirements determine how long archived data must be kept and in what retrievable form.
- Keeps the operational database small, fast, and cheap to maintain
- Satisfies compliance/retention requirements without bloating primary tables
- Reduces backup and index maintenance time on the active dataset
- Moves storage cost from expensive operational disks to cheap archival storage
AI Mentor Explanation
A cricket board keeps this season’s match scorecards in the active clubhouse filing cabinet for quick reference by coaches and commentators, but every off-season it boxes up scorecards older than five years and ships them to an offsite archive warehouse, still retrievable if a historian ever needs them. Keeping every scorecard since the club’s founding in the clubhouse cabinet would make it impossible to find anything current. Database archival strategies do the same thing: old partitions are periodically moved out of the active table into cheaper, separate long-term storage.
Step-by-Step Explanation
Step 1
Define retention policy
Decide how long data must stay in the operational table versus how long it must be retained overall (compliance, business need).
Step 2
Partition by time
Structure the table with time-based partitions (e.g. monthly) so old ranges can be isolated cleanly.
Step 3
Detach and export old partitions
Periodically move partitions past the retention threshold into an archive database, warehouse, or compressed object storage.
Step 4
Keep archived data retrievable
Ensure archived data can still be queried or restored, e.g. for audits, even though it is no longer in the fast operational path.
What Interviewer Expects
- Understanding of time-based partitioning as the enabling mechanism
- Distinction between archival (moved but retrievable) and deletion (gone)
- Awareness of compliance/retention drivers for archival policy
- Mention of keeping the operational table lean for performance
Common Mistakes
- Confusing archival with simple deletion of old data
- Not partitioning by time, making archival detach operations expensive
- Ignoring compliance retention requirements when designing the policy
- Assuming archived data becomes permanently unqueryable
Best Answer (HR Friendly)
“Archival strategies move old, rarely used data out of the main operational database into cheaper long-term storage, usually by partitioning the table by date and periodically detaching old partitions. This keeps the live database fast and lean while still letting the business retrieve old data later for audits, compliance, or historical analysis.”
Code Example
-- Table partitioned by month
CREATE TABLE logs (
log_id BIGINT,
created_at TIMESTAMP,
payload TEXT
) PARTITION BY RANGE (created_at);
-- Detach a partition older than the retention window
ALTER TABLE logs DETACH PARTITION logs_2020_01;
-- The detached partition can now be exported and
-- copied to cheap archival storage before being dropped
-- from the operational database entirely.
COPY logs_2020_01 TO '/archive/logs_2020_01.csv' WITH CSV;Follow-up Questions
- How does time-based partitioning make archival easier?
- What is the difference between archiving data and deleting it?
- How do compliance requirements shape a retention/archival policy?
- What format or system would you archive old data into, and why?
MCQ Practice
1. What is the primary purpose of an archival strategy?
Archival relocates dormant data to cheaper storage but keeps it retrievable, unlike deletion which removes it permanently.
2. Which table design most simplifies archiving old data?
Partitioning by a time column lets old partitions be detached and moved as a unit instead of deleting rows individually.
3. A key driver for defining a data retention/archival policy is often:
Regulatory and business retention requirements commonly dictate how long data must be kept and in what retrievable form.
Flash Cards
What is data archival? — Moving old, rarely accessed data out of the operational database into cheaper, retrievable long-term storage.
How does partitioning help archival? — Time-based partitions can be detached and moved as a unit instead of deleting rows one by one.
Archival vs deletion? — Archival keeps data retrievable in cheaper storage; deletion removes it permanently.
What drives retention policy? — Compliance, legal, and business requirements dictate how long data must be retained and in what form.