AuditTrail.Adapters.TimescaleDB (audit_trail v0.1.0)

Storage adapter for TimescaleDB — PostgreSQL with automatic time partitioning.

Functionally identical to AuditTrail.Adapters.Postgres for all read/write operations. The difference is in the migration: mix audit_trail.gen.migration generates a migration that also calls create_hypertable and optionally enables chunk compression.

Setup

1. Configure

config :audit_trail,
  storage_adapter: {AuditTrail.Adapters.TimescaleDB, repo: MyApp.Repo}

2. Generate and run the migration

mix audit_trail.gen.migration
mix ecto.migrate

Why TimescaleDB over plain Postgres?

Feature	Postgres	TimescaleDB
Time-range queries	Table scan (unless indexed)	Chunk pruning — only relevant chunks scanned
Storage for old data	Full row size always	Compressed chunks (4–20x compression typical)
Continuous aggregates	Manual materialized views	Built-in, auto-refreshed
Partition management	Manual	Automatic by time interval

TimescaleDB is the better choice when:

You expect more than a few million log rows
You want automatic data tiering or compression
You run range queries across time frequently (the default pattern for audit logs)

TimescaleDB is standard Postgres-compatible — your existing mix ecto.migrate and all Ecto queries work without modification.

Compression (optional, run after the migration)

After creating the hypertable, you can enable compression on older chunks to reduce storage. Run these directly in your database:

ALTER TABLE audit_logs SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'event_type, actor_id'
);

SELECT add_compression_policy('audit_logs', INTERVAL '7 days');

This compresses chunks older than 7 days. The compress_segmentby columns are the ones you filter on most — queries on those fields stay fast even against compressed chunks.