SKILL.md
Database Archival System
Overview
Implement automated data archival pipelines that move historical records from primary database tables to archive storage (archive tables, S3, Azure Blob, or GCS) based on age, status, or access frequency criteria.
Prerequisites
- Database credentials with SELECT, INSERT, and DELETE permissions on source and archive tables
- Cloud storage credentials (AWS S3, Azure Blob, or GCS) if archiving to cold storage
psqlormysqlCLI for executing archival queriesaws s3,az storage, orgsutilCLI for cloud storage uploads- Understanding of data retention requirements and compliance policies (GDPR, HIPAA, SOX)
- Current table sizes:
SELECT pg_size_pretty(pg_total_relation_size('table_name'))to identify archival candidates
Instructions
-
Identify archival candidates by finding large tables with time-based data:
SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10- Focus on tables where historical data is rarely queried: logs, audit trails, events, old orders, expired sessions
-
Define archival criteria for each table:
- Age-based: Records older than N days/months (
WHERE created_at < NOW() - INTERVAL '1 year') - Status-based: Records in terminal state (
WHERE status IN ('completed', 'cancelled', 'expired')) - Combined: Old AND terminal (
WHERE created_at < NOW() - INTERVAL '6 months' AND status = 'completed') - Calculate the expected volume:
SELECT COUNT(*), pg_size_pretty(pg_column_size(t.*)) FROM table_name t WHERE <criteria>
- Age-based: Records older than N days/months (
-
Handle referential integrity by archiving in dependency order: