Data Archival Policy & Lifecycle Management
The 3-Line Engineering Rule
Define automated data retention and archival policies from day one; tier old data from expensive NVMe tables into cold S3 storage.
A 5-year-old table with 500 million records causes slow backups, expensive cloud disks, sluggish indexes, and potential GDPR compliance fines.
Partition large tables by date range (monthly/yearly), detach old partitions instantly without table locking, and offload to compressed Parquet files on S3 Glacier.
Architectural Blueprint & Sequence
Hover over nodes to inspect state transitionsHOT: PostgreSQL NVMe
0 - 90 Days (Active OLTP)
Partition Rotation
ALTER TABLE DETACH PARTITION
WARM: S3 Iceberg/Parquet
90 - 365 Days (Athena/Analytics)
COLD: S3 Glacier
> 1 Year (Legal & Tax Compliance)
Key Engineering Takeaways
- Table partitioning allows dropping/archiving a billion rows in 10 milliseconds via `ALTER TABLE DETACH PARTITION`.
- Tier data across Hot (NVMe PostgreSQL), Warm (S3 Parquet/Athena), and Cold (S3 Glacier Deep Archive).
- Automate data deletion for compliance (GDPR Right to Be Forgotten) using TTL workers.
- Keep transaction indexes lean by keeping active tables scoped to recent data (e.g. last 90 days).
The Problem & Production Reality
Every successful system accumulates data. A database table that handles 50,000 orders a day will accumulate 18 million rows annually. If all historical data stays in the primary OLTP database, backups take 10 hours, VACUUM freezes disks, and indexes consume all available RAM. A structured archival policy keeps primary databases fast and lean.
Anti-Pattern: The Dangerous DELETE WHERE created_at < ... Query
Running a massive `DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '1 year'` on a 100-million row table.
// ❌ ANTI-PATTERN: Blows up transaction logs and locks the table
DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '365 days';
-- Generates millions of WAL records, locks rows, crashes replicas, and fragments disk space!Production Pattern: Declarative Range Partitioning & S3 Export
Partition tables by month. Detaching a partition is instantaneous (metadata operation), allowing painless archival to S3.
// ✅ PRODUCTION PATTERN 1: PostgreSQL Declarative Partitioning
CREATE TABLE audit_logs (
id BIGINT GENERATED ALWAYS AS IDENTITY,
user_id UUID NOT NULL,
action VARCHAR(50) NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
payload JSONB
) PARTITION BY RANGE (created_at);
-- Monthly partition tables:
CREATE TABLE audit_logs_2026_10 PARTITION OF audit_logs
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE audit_logs_2026_11 PARTITION OF audit_logs
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
// ✅ PRODUCTION PATTERN 2: Instant Detach & S3 Parquet Export
-- 1. Instantaneous metadata detach (Takes 2ms, zero table lock):
ALTER TABLE audit_logs DETACH PARTITION audit_logs_2025_10 CONCURRENTLY;
-- 2. Export detached partition to compressed Parquet on S3:
-- pg_dump or AWS DMS -> s3://company-archives/audit_logs/2025-10.parquet
-- 3. Drop detached partition table:
DROP TABLE audit_logs_2025_10;Trade-off Analysis & Staff Recommendations
| Aspect | Pros / Strengths | Cons / Hazards | Staff Recommendation |
|---|---|---|---|
| Partition Detach vs Soft Deletion Flag | Detaching a partition reclaims 100% of physical disk space immediately. | Queries spanning multiple years must query multiple partition tables or warm storage. | Use Partitioning for append-heavy high-volume tables (logs, events, orders). |
| S3 Glacier Retrieval Latency | S3 Glacier Deep Archive costs less than $1 per terabyte per month. | Retrieving archived files takes 3 to 12 hours. | Store data older than 1 year in Glacier if it is retained strictly for tax/legal compliance. |