Safe Deletion Pattern: Archive-Then-Delete for Compliance

advanced

Stripe needs to delete old payment records older than 7 years for storage optimization, but must maintain compliance with financial regulations requiring 7-year retention. Traditional approach: DELETE old records directly. Problem: Cannot recover if deletion goes wrong, violates compliance if wrong date used. Solution: Archive-then-delete pattern with verification.

-- Step 1: Create archive table (one-time setup)

CREATE TABLE payments_archive (

payment_id BIGINT PRIMARY KEY,

customer_id BIGINT,

amount DECIMAL(10,2),

payment_date DATE,

status VARCHAR(20),

archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- Step 2: Archive old payments (7+ years old)

INSERT INTO payments_archive (payment_id, customer_id, amount, payment_date, status)

SELECT payment_id, customer_id, amount, payment_date, status

FROM payments

WHERE payment_date < CURRENT_DATE - INTERVAL '7 years'

AND status = 'completed';

-- Inserted 50 million rows in 300 seconds

-- Step 3: Verify archive matches source

SELECT

'Source' as table_name,

COUNT(*) as row_count,

MIN(payment_date) as oldest_date,

MAX(payment_date) as newest_date,

SUM(amount) as total_amount

FROM payments

WHERE payment_date < CURRENT_DATE - INTERVAL '7 years'

AND status = 'completed'

UNION ALL

SELECT

'Archive' as table_name,

COUNT(*) as row_count,

MIN(payment_date) as oldest_date,

MAX(payment_date) as newest_date,

SUM(amount) as total_amount

FROM payments_archive;

-- Verify counts and totals match exactly

-- Step 4: Delete from source in batches (prevent long locks)

DO $$