Implementing Data Archiving Strategies
1. Identifying Archive Candidates
| Signal | Detail |
|---|---|
| Age | Rows older than X (orders > 7 years) |
| Access frequency | Untouched in 90 days |
| Status | Closed/cancelled/terminated |
| Regulation | Hold for compliance, archive after |
| Size | Largest tables yield biggest wins |
2. Designing Archive Schemas
| Pattern | Detail |
|---|---|
| Parallel archive table | Same shape, slower storage |
| Compressed columnar | Parquet/ORC on object storage |
| Wide JSON blob | Single denormalized row |
| Drop indexes | Archive is rarely queried |
| Partition by date | Fast drop of expired data |
3. Implementing Tiered Storage
| Tier | Latency | Storage |
|---|---|---|
| Hot | ms | NVMe SSD, primary DB |
| Warm | sub-second | SATA SSD, slower DB / read replicas |
| Cold | seconds | HDD, S3 Standard |
| Frozen | minutes-hours | S3 Glacier, tape |
4. Creating Archive Policies
| Policy Element | Example |
|---|---|
| Trigger | row.status='closed' AND closed_at < NOW() - INTERVAL '2 years' |
| Schedule | Nightly batch, low-traffic window |
| Batch size | 10k rows, throttled |
| Destination | archive schema or external store |
| Verification | Row count + checksum before delete |
5. Managing Archive Retention Periods
| Regulation | Typical Retention |
|---|---|
| SOX (US financial) | 7 years |
| HIPAA (US health) | 6 years |
| GDPR (EU) | Necessary period only; right-to-erasure |
| PCI-DSS | 1 year (logs), card data minimized |
| Custom business | Define in data governance policy |
6. Implementing Data Purging Procedures
| Approach | Detail |
|---|---|
| Soft delete first | Mark deleted_at, then purge later |
| Batched DELETE | LIMIT-based loops to avoid bloat |
| TRUNCATE partition | O(1) when using time partitions |
| Tombstone tables | Track what was deleted for audit |
| VACUUM after | Reclaim space (PG) |
7. Querying Archived Data
| Mechanism | Detail |
|---|---|
| UNION view | SELECT FROM hot UNION ALL archive |
| Foreign data wrapper | PG FDW to read S3/Parquet |
| External query engine | Athena, BigQuery, Trino |
| Rehydrate on demand | Copy back to hot for active use |
8. Handling Compliance Requirements
| Requirement | Mechanism |
|---|---|
| Right to erasure (GDPR) | Purge from primary + archive + backups |
| Legal hold | Suspend deletions for specific records |
| Immutability (SEC 17a-4) | WORM storage (S3 Object Lock) |
| Audit trail | Log every archive/purge event |
| Encryption | KMS keys; per-tenant where required |
9. Implementing Archive Compression
| Method | Detail |
|---|---|
| Columnar (Parquet/ORC) | 5–10× compression on analytical data |
| zstd / gzip / lz4 | General-purpose dump compression |
| DB-native page compression | InnoDB PAGE_COMPRESSED, Oracle Hybrid Columnar |
| Dedup | ZFS / fs-level dedup for blobs |
10. Testing Archive Restore Procedures
| Test | Detail |
|---|---|
| Spot restore | Pull random record from cold tier |
| Full bucket restore | Time to recover by date range |
| Schema compatibility | Restore old data into current schema |
| Documentation | Runbook + tooling versions |