Designing a Data Retention and Archival System: TTL Policies, Cold Storage Tiers, and Compliance-Driven Data Lifecycle Management

Every production database grows until someone asks “how long do we keep this data?” Most teams answer with “forever” until storage costs spike or a compliance audit arrives. Then the conversation gets uncomfortable fast: nobody documented the retention policy, the deletion logic was never built, and now you have seven years of user events in a single Postgres table that nobody dares touch.

Data retention is not a cleanup task you schedule for later. It is a core system design concern with real tradeoffs between cost, compliance, query performance, and operational complexity. This article covers how to build a system that handles all of it.

The Problem Space

Three forces push you toward designing this properly:

Cost. Storage is cheap until it is not. A row-per-event analytics table at 10,000 events per second generates roughly 860 billion rows per year. Even at $0.023/GB (S3 standard), that adds up. Hot database storage (EBS gp3, managed Postgres) costs 10-50x more than cold object storage for the same bytes.

Compliance. GDPR requires you to delete personal data on request, and not store it longer than necessary. HIPAA mandates six years of medical record retention. SOC 2 asks you to demonstrate you have a defined retention policy. Financial regulations (SEC 17a-4, FINRA) require seven years of certain records in immutable, auditable storage. These requirements frequently conflict with each other.

Query performance. A 500GB Postgres table with data from 2018 to today has the same index size problem regardless of how often the old data is accessed. Retaining everything in hot storage for “just in case” queries degrades performance for current-data queries.

Defining Retention Policies

Before writing any code, you need a retention policy per data class. Every table or event type should have:

• A retention duration (how long to keep it)
• A storage tier schedule (when to move from hot to warm to cold)
• A deletion type (soft delete, hard delete, or anonymize-in-place)
• A compliance anchor (which regulation drives this rule)

type StorageTier = "hot" | "warm" | "cold" | "deleted";
type DeletionType = "soft" | "hard" | "anonymize";

interface RetentionPolicy {
  dataClass: string;
  hotDays: number;      // stay in primary database
  warmDays: number;     // move to read-replica or cheaper storage
  coldDays: number;     // move to object storage (S3, GCS)
  totalRetentionDays: number;  // delete after this
  deletionType: DeletionType;
  complianceAnchor: string;    // e.g., "GDPR Art. 5(1)(e)", "HIPAA §164.530"
}

const RETENTION_POLICIES: Record<string, RetentionPolicy> = {
  user_events: {
    dataClass: "user_events",
    hotDays: 30,
    warmDays: 90,
    coldDays: 365,
    totalRetentionDays: 730,
    deletionType: "hard",
    complianceAnchor: "GDPR Art. 5(1)(e) - storage limitation",
  },
  audit_logs: {
    dataClass: "audit_logs",
    hotDays: 90,
    warmDays: 365,
    coldDays: 2555,   // 7 years in cold
    totalRetentionDays: 2555,
    deletionType: "hard",
    complianceAnchor: "SOC 2 CC6.1 - audit trail",
  },
  medical_records: {
    dataClass: "medical_records",
    hotDays: 365,
    warmDays: 730,
    coldDays: 2190,   // 6 years total (HIPAA minimum)
    totalRetentionDays: 2190,
    deletionType: "anonymize",
    complianceAnchor: "HIPAA §164.530(j)(2)",
  },
};

The complianceAnchor field is not decorative. It is what you show the auditor when they ask why you deleted data at a specific time.

Hot/Warm/Cold Storage Tiers

The goal is transparent tiering: application code queries a single abstraction, and the system routes to the correct tier without the caller knowing where data physically lives.

Schema Design for Tiering

Add a storage_tier column and a tier_moved_at timestamp to any table that participates in lifecycle management:

ALTER TABLE user_events
  ADD COLUMN storage_tier TEXT NOT NULL DEFAULT 'hot',
  ADD COLUMN tier_moved_at TIMESTAMPTZ,
  ADD COLUMN archived_at TIMESTAMPTZ,
  ADD COLUMN archived_location TEXT;  -- e.g., "s3://archive-bucket/user_events/2024/01/"

CREATE INDEX idx_user_events_tier ON user_events(storage_tier, created_at);
CREATE INDEX idx_user_events_created ON user_events(created_at) WHERE storage_tier = 'hot';

The archived_location column lets you reconstruct a query against cold storage if needed, without full data rehydration.

The Archival Pipeline

The archival job runs on a schedule. It identifies rows eligible for tier transition, exports them to object storage, updates the database row to mark them archived, then optionally deletes the row content while preserving the shell.

import { S3Client, PutObjectCommand } from "@aws-sdk/client-s3";
import { Pool } from "pg";

interface ArchivalJob {
  dataClass: string;
  policy: RetentionPolicy;
  batchSize: number;
}

async function runArchivalBatch(
  db: Pool,
  s3: S3Client,
  job: ArchivalJob
): Promise<{ archived: number; errors: number }> {
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - job.policy.hotDays);

  // Fetch a batch eligible for warm/cold transition
  const { rows } = await db.query<{ id: string; created_at: Date; data: unknown }>(
    `SELECT id, created_at, row_to_json(t) AS data
     FROM ${job.dataClass} t
     WHERE storage_tier = 'hot'
       AND created_at < $1
     ORDER BY created_at ASC
     LIMIT $2
     FOR UPDATE SKIP LOCKED`,
    [cutoffDate.toISOString(), job.batchSize]
  );

  if (rows.length === 0) return { archived: 0, errors: 0 };

  // Group by year/month for efficient S3 partitioning
  const grouped = groupByPartition(rows);
  let archived = 0;
  let errors = 0;

  for (const [partition, records] of Object.entries(grouped)) {
    const key = `${job.dataClass}/${partition}/batch-${Date.now()}.jsonl`;
    const body = records.map((r) => JSON.stringify(r.data)).join("\n");

    try {
      await s3.send(
        new PutObjectCommand({
          Bucket: process.env.ARCHIVE_BUCKET!,
          Key: key,
          Body: body,
          ContentType: "application/x-ndjson",
          // Server-side encryption at rest
          ServerSideEncryption: "aws:kms",
          SSEKMSKeyId: process.env.ARCHIVE_KMS_KEY_ID,
        })
      );

      const ids = records.map((r) => r.id);
      await db.query(
        `UPDATE ${job.dataClass}
         SET storage_tier = 'cold',
             tier_moved_at = NOW(),
             archived_at = NOW(),
             archived_location = $1
         WHERE id = ANY($2)`,
        [`s3://${process.env.ARCHIVE_BUCKET}/${key}`, ids]
      );

      archived += records.length;
    } catch (err) {
      console.error(`Archival failed for partition ${partition}:`, err);
      errors += records.length;
    }
  }

  return { archived, errors };
}

function groupByPartition(
  rows: Array<{ id: string; created_at: Date; data: unknown }>
): Record<string, typeof rows> {
  return rows.reduce(
    (acc, row) => {
      const d = new Date(row.created_at);
      const key = `${d.getUTCFullYear()}/${String(d.getUTCMonth() + 1).padStart(2, "0")}`;
      acc[key] = acc[key] ?? [];
      acc[key].push(row);
      return acc;
    },
    {} as Record<string, typeof rows>
  );
}

The FOR UPDATE SKIP LOCKED clause prevents multiple archival workers from processing the same rows. This is safe to run in parallel across multiple workers as long as each worker operates on a different batch.

Deletion That Actually Works

Soft deletes, hard deletes, and anonymization each serve different purposes. Picking the wrong one causes compliance failures or data resurrection bugs.

Soft delete sets a deleted_at timestamp. The row stays in the database. This is useful when you need a brief grace period (accidental deletion recovery) but it does not satisfy GDPR erasure requirements. Data is still there.

Hard delete removes the row. Simple, but cascading deletes across services and foreign keys require careful orchestration.

Anonymize-in-place nulls or replaces personal fields while keeping the row for aggregate integrity. Required when you need statistical data but must remove PII.

type ErasureStrategy = "hard_delete" | "anonymize";

interface ErasureRequest {
  userId: string;
  requestedAt: Date;
  regulation: "GDPR" | "CCPA";
}

async function executeErasure(
  db: Pool,
  request: ErasureRequest
): Promise<void> {
  const client = await db.connect();
  try {
    await client.query("BEGIN");

    // Anonymize user_events (keep for aggregate analytics)
    await client.query(
      `UPDATE user_events
       SET user_id = NULL,
           ip_address = NULL,
           user_agent = NULL,
           metadata = metadata - 'email' - 'name'
       WHERE user_id = $1`,
      [request.userId]
    );

    // Hard delete PII-heavy tables
    await client.query(
      `DELETE FROM user_profiles WHERE user_id = $1`,
      [request.userId]
    );
    await client.query(
      `DELETE FROM payment_methods WHERE user_id = $1`,
      [request.userId]
    );

    // Record the erasure in the audit log (the audit log itself is exempt
    // from erasure under GDPR Recital 65 - legal obligation basis)
    await client.query(
      `INSERT INTO erasure_audit (user_id, requested_at, completed_at, regulation, tables_affected)
       VALUES ($1, $2, NOW(), $3, $4)`,
      [
        request.userId,
        request.requestedAt.toISOString(),
        request.regulation,
        ["user_events", "user_profiles", "payment_methods"],
      ]
    );

    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

One thing many teams miss: GDPR erasure must also reach archived data in cold storage. You need a process to rewrite or delete S3 objects containing the subject’s data. Because S3 objects are immutable, this means scanning the relevant partitions, filtering out the subject’s rows, and re-uploading a new object. Track which S3 prefixes need reprocessing using the archived_location column you wrote during archival.

Compliance-Driven Retention: What Each Regulation Actually Requires

Regulation	Minimum Retention	Maximum Retention	Deletion Required	Notes
GDPR	None specified	”No longer than necessary”	Yes, on request	Purpose limitation is the binding rule
HIPAA	6 years from creation or last effective date	None specified	No mandate, but good practice	Covers designated record sets
SOC 2	Auditor-defined (typically 1 year)	None specified	No mandate	Evidence retention for audit cycles
SEC 17a-4	3 years accessible, 6 years total	None	No	Must be WORM-compliant
FINRA	3-6 years depending on record type	None	No	Broker-dealer specific
PCI DSS	1 year cardholder data logs	None	Yes, after retention period	Scope reduction via tokenization preferred

The conflict between GDPR (delete when no longer needed) and financial regulations (keep for 7 years in WORM storage) is real. The resolution is data class separation: financial transaction records are kept under the legal obligation basis, which overrides the storage limitation principle. Personal preference data, browsing history, and marketing profiles have no such exemption and must be deleted.

Running Retention Jobs at Scale

A retention job that locks tables or runs for 12 hours is worse than no retention job. Production constraints:

Batch size matters. Deleting 10 million rows in a single transaction creates a massive write-ahead log entry and blocks vacuum. Use batches of 1,000-5,000 rows with a short sleep between iterations.

Run during off-peak hours, but throttle. Even at off-peak, a retention job saturating your primary database causes replication lag.

Use a cursor, not OFFSET. OFFSET scans are O(n). Use a keyset cursor:

async function deleteExpiredBatch(
  db: Pool,
  dataClass: string,
  expirationDate: Date,
  lastId: string | null,
  batchSize: number
): Promise<{ deletedCount: number; nextCursor: string | null }> {
  const { rows } = await db.query<{ id: string }>(
    `DELETE FROM ${dataClass}
     WHERE id IN (
       SELECT id FROM ${dataClass}
       WHERE created_at < $1
         AND ($2::uuid IS NULL OR id > $2)
       ORDER BY id ASC
       LIMIT $3
     )
     RETURNING id`,
    [expirationDate.toISOString(), lastId, batchSize]
  );

  const deletedCount = rows.length;
  const nextCursor = deletedCount === batchSize ? rows[rows.length - 1].id : null;

  return { deletedCount, nextCursor };
}

async function runExpirationJob(
  db: Pool,
  dataClass: string,
  policy: RetentionPolicy
): Promise<void> {
  const expirationDate = new Date();
  expirationDate.setDate(expirationDate.getDate() - policy.totalRetentionDays);

  let cursor: string | null = null;
  let totalDeleted = 0;

  do {
    const { deletedCount, nextCursor } = await deleteExpiredBatch(
      db,
      dataClass,
      expirationDate,
      cursor,
      1000
    );
    totalDeleted += deletedCount;
    cursor = nextCursor;

    // Throttle: avoid overwhelming the primary
    if (cursor !== null) {
      await new Promise((resolve) => setTimeout(resolve, 50));
    }
  } while (cursor !== null);

  console.log(`Expired ${totalDeleted} rows from ${dataClass}`);
}

Cascading deletes across services require an event-driven approach rather than database-level ON DELETE CASCADE. When you delete a user record, publish a user.deleted event. Each downstream service subscribes and deletes its own data. This is slower but avoids tight database coupling and gives each service control over its own erasure logic.

Tradeoffs

Approach	Query simplicity	Storage cost	Compliance	Operational complexity
Keep everything in hot storage	Simple (no routing)	Very high	Risky (over-retention)	Low
Partition + delete old partitions	Moderate	Medium	Good for TTL-based rules	Medium
Hot/warm/cold with transparent routing	Complex routing layer	Low	Good with policy enforcement	High
Anonymize-in-place only	Simple	Medium	Partial (PII removed, data stays)	Low
Full deletion on expiry	Simple	Low	Best for GDPR	Medium (cascade coordination)

The transparent routing approach has the best cost profile over time but requires the most upfront investment. For most teams, starting with partition-based expiration (Postgres table partitioning with DROP PARTITION for old ranges) and graduating to object storage archival when monthly storage costs justify it is the practical path.

Production Considerations

Idempotency. Archival jobs will fail partway through. Design them to be rerun without duplicating data in S3 or re-archiving already-archived rows. The storage_tier = 'hot' filter in the archival query handles this if you update the column atomically with the S3 write.

Monitor deletion lag. Track the gap between when data became eligible for deletion and when it was actually deleted. GDPR requires responding to erasure requests within 30 days. If your batch job runs weekly and your queue depth grows, you will miss that window.

Test your erasure path. Most teams test the happy path for writes and never test deletion. Write a test that creates a user, exercises all the tables that reference them, runs erasure, and asserts no PII remains. Run it in CI.

Cold storage retrieval costs. AWS S3 Glacier Instant Retrieval costs $0.004/GB for storage but $0.03/GB for retrieval. If your “cold” queries happen more than once a month per dataset, Glacier is probably the wrong tier. S3 Standard-IA at $0.0125/GB storage with $0.01/GB retrieval may be more economical for warm-tier data.

Legal holds. Compliance teams sometimes need to freeze deletion for specific records during litigation. Build a legal_hold flag that any retention or erasure job checks before proceeding. A job that deletes a legally held record creates significant liability.

Schema evolution in cold storage. JSONL files in S3 from 2022 will not match your 2026 schema. Store a schema_version field in every archived record and maintain a registry of how to transform each version forward. Trying to rehydrate data without this leads to parse failures that are hard to debug years later.

Encryption key rotation. If you encrypt archived data with KMS, document and test the process for re-encrypting old objects after key rotation. Some regulations require this on a defined schedule.

Closing

The hard part of data retention is not the technology. The hard part is agreeing across legal, engineering, and product on what data you actually need and for how long. Engineers tend to save everything because deleting feels risky. Legal tends to say “forever” when they mean “for as long as required.” Product wants flexibility. Getting alignment on a written policy per data class, with a regulation citation, makes the implementation straightforward. Without it, you are building a system with no clear requirements, and the compliance audit will expose that.

Build the policy first. Then build the pipeline to enforce it.