PaperTrail Archiving Strategy

PaperTrail's versions table grew into the largest table in the database — hundreds of gigabytes of audit rows that were written constantly and read almost never, dragging backups, migrations and storage costs with it.

Audit history in finance software is not optional; deleting anything was off the table. But less than 0.1% of version rows were ever read after 90 days.

Hot/cold split: recent versions stay in the primary table; a scheduled job moves older rows to cheap blob storage as compressed batches, with a manifest table for lookup. Reads fall through transparently — recent from SQL, older rehydrated on demand.

class ArchiveVersionsJob < ApplicationJob
  BATCH = 50_000

  def perform
    PaperTrail::Version
      .where("created_at < ?", 90.days.ago)
      .in_batches(of: BATCH) do |batch|
        ColdStore.write(batch)   # compressed, manifest-indexed
        batch.delete_all         # inside the same transaction
      end
  end
end

Cold reads are slower (seconds, not milliseconds) — acceptable for an audit trail viewed rarely. The real cost is operational: one more pipeline to monitor, so it ships with its own alerting.

Primary table shrank by ~80%, backup windows halved, and audit history remains complete and retrievable.

Data has a temperature — bill it accordingly.

'Keep everything' and 'keep everything in the expensive place' are different requirements; most stakeholders only mean the first.