Database Data Archiving and Lifecycle Management

Topic Description
Data archiving is the process of migrating historical data that is no longer frequently accessed from a database to low-cost storage systems. Its core objectives are to balance performance, storage costs, and data availability. Lifecycle management defines the complete set of rules for data from creation to archiving or deletion. In interviews, one often needs to address applicable scenarios, design steps, technical implementations, and considerations for archiving.

I. Why is Data Archiving Needed?

1. Performance Optimization: Reduces the volume of active data, improving query speed and lowering index maintenance overhead.
2. Cost Control: Moves cold data to inexpensive storage (e.g., object storage, tape libraries), saving high-performance storage resources.
3. Compliance Requirements: Certain industries (e.g., finance, healthcare) require long-term data retention without needing real-time access.

II. Core Steps of Data Archiving
Step 1: Define Archiving Strategy

• Determine Archiving Criteria: Based on time (e.g., 3 years after order completion), business status (e.g., expired contracts), or data heat (access frequency below a threshold).
• Choose Storage Medium: Select offline storage (e.g., AWS Glacier) or nearline storage (e.g., read-only database instances) based on access requirements.

Step 2: Design Archiving Solution

• Logical Archiving: Only migrates data copies; the original database retains soft delete markers or summary information for traceability.
  • Example: Archive order table data to an orders_archive table, keeping order_id and archive timestamp in the original table.
• Physical Archiving: Directly moves data files, often combined with partitioned tables (e.g., by time) to directly unload old partitions.

Step 3: Implement Archiving Process

1. Extract Data:
   • Use INSERT INTO archive_table SELECT ... FROM active_table WHERE <condition>.
   • Avoid table locking: Operate in batches (e.g., processing 1000 rows at a time) or use cursor loops based on primary keys.
2. Verify Consistency: Compare checksums (e.g., MD5 hash) or record counts between source and archive tables.
3. Clean Up Original Data: Delete archived data during low business hours, paying attention to foreign key constraints (e.g., archive child tables before parent tables).

Step 4: Design Access Mechanism for Archived Data

• Transparent Access: Use database federated queries (e.g., MySQL's FEDERATED engine) or views to query both archive and active databases uniformly.
• Application Layer Routing: Dynamically select data sources in code based on query conditions.

III. Key Technical Implementation Example (Using MySQL Partitioned Tables)

1. Partition by Time:

   -- Create partitioned table (partitioned by year)  
   CREATE TABLE orders (  
     id INT,  
     order_date DATE  
   ) PARTITION BY RANGE (YEAR(order_date)) (  
     PARTITION p2020 VALUES LESS THAN (2021),  
     PARTITION p2021 VALUES LESS THAN (2022)  
   );  
   

2. Archive Old Partition:

   -- Export 2020 partition data to a file  
   ALTER TABLE orders EXPORT PARTITION p2020 TO '/tmp/p2020.sql';  
   -- Remove partition from original table (fast deletion)  
   ALTER TABLE orders DROP PARTITION p2020;  
   

IV. Considerations

1. Business Impact: Archiving may lock tables; plan maintenance windows or use online tools (e.g., pt-archiver).
2. Data Consistency: Ensure transaction consistency before and after archiving (e.g., avoid archiving uncommitted data).
3. Compliance and Security: Encrypt archived data and regularly test recovery procedures.

V. Extended Considerations

• Automated Management: Integrate scheduling tools (e.g., Airflow) to trigger archiving tasks periodically.
• Cloud Database Integration: Leverage cloud services (e.g., AWS DMS) for fully managed archiving.

Following these steps enables systematic implementation of data archiving, balancing efficiency and security.