Partitioning and Retention Strategy

Scope

High-volume tables: - usage_records - ledger_entries - audit_logs

Partition Strategy

• Partition by month on event time (start_time or created_at).
• Optional sub-partition by tenant/org when scale requires.

Index Strategy

• Composite indexes with (org_id, time) and (user_id, time).
• Keep hot indexes on active partitions only where possible.

Retention Policy (Initial)

• usage_records: 24 months online, then archive.
• ledger_entries: 84 months online minimum (finance/audit driven).
• audit_logs: 24 months online, then archive.

Archival Policy

• Monthly export to immutable object storage.
• Archive metadata catalog retained in DB.

Operational Rules

• Partition creation automated monthly.
• Partition pruning and vacuum jobs scheduled.
• Recovery tests include partitioned-table restore.