ADR 0021 — Trace Storage: Timescale vs Plain Postgres vs ClickHouse vs Others

Status: Accepted (conditional — see open question in §Consequences)
Date: 2026-04-29
Deciders: software-architect
Refs: workflow-uuid-tracing.md, ADR-0003

Context

The workflow-UUID tracing design requires a storage backend that can:

1. Append events at ~20–50 per user-session without write contention.
2. Answer "reconstruct this user's day" queries efficiently as history grows across years.
3. Compress repetitive, append-only rows (many rows share user_id, workflow_id, short action_type strings).
4. Support a cold-storage tier so years of trade-affecting events (7-year retention per ADR-0003) do not keep hot-tier costs linear.
5. Integrate with Heroku (Raptor's current platform) without introducing a new ops paradigm.
6. Survive pre-launch on a budget — no $500/month infra commitment before the first paying customer.

Candidates evaluated: Timescale (Postgres + extension), plain Postgres (partitioned), ClickHouse, InfluxDB, AWS Timestream, SQLite (4-week MVP path).

Decision

Use Timescale (TimescaleDB extension on Postgres) as the production trace store, with SQLite as a valid 4-week MVP shortcut.

Why Timescale over plain Postgres with partitioning

Postgres declarative partitioning on ts_emitted works but requires manual partition creation per time range, manual index management per partition, and a separate housekeeping job to detach old partitions for cold storage. Timescale's create_hypertable automates all of this:

• Automatic chunk creation per configured interval (weekly chunks recommended for this volume).
• Built-in compression policy: add_compression_policy('trace_events', INTERVAL '90 days') compresses old chunks in-place with ~10–20× ratio on repetitive event rows. No separate ETL job.
• add_tiering_policy (Timescale Cloud) moves compressed chunks to S3 transparently, keeping hot Postgres storage bounded.
• time_bucket queries and first()/last() aggregate functions are purpose-built for event reconstruction.
• No new query language: everything is Postgres SQL. Existing ORM layers, migrations tools, and observability work unchanged.

Why not ClickHouse

ClickHouse is purpose-built for OLAP on append-only data and outperforms Postgres at hundreds of millions of rows. But:

• Heroku has no native ClickHouse add-on. Self-hosting on a Heroku dyno is unsupported. An external ClickHouse Cloud instance adds a new vendor, a new billing line, and a new ops surface.
• ClickHouse does not support UPDATE or DELETE in the traditional sense (mutations are expensive async operations). This is fine for append-only trace data but complicates GDPR pseudonymization, which requires modifying user_id fields on erasure. The workaround (soft-delete + view layer) adds complexity.
• At pre-launch volume (<1M rows/month), ClickHouse's performance advantage is not observable. It is the right choice at 100M+ rows/month. The Timescale design is migrateable to ClickHouse at that threshold if needed.

Why not InfluxDB

InfluxDB's data model (measurements, tags, fields) fits infrastructure metrics well but is a poor fit for Raxx's event schema, which is a heterogeneous record with varying context fields. The Flux query language is a separate skill. InfluxDB Cloud has a separate billing model and does not compose with the existing Postgres-centric Raptor codebase. Rejected.

Why not AWS Timestream

AWS Timestream is managed, serverless, and cheap at low volume. But it couples the trace store to AWS permanently — a platform change from Heroku to Fly.io or Railway would require a migration path. Timestream queries use a custom SQL dialect. No Heroku add-on. Rejected for pre-launch; acceptable as a v2 option if Raxx migrates fully to AWS infrastructure.

Why SQLite is valid for the 4-week path

At MVP volume (<10 users, <10,000 events total), SQLite with a composite index on (user_id, ts_emitted) is entirely sufficient. The schema is identical to the Timescale schema minus the create_hypertable call. When Raptor migrates from SQLite to Postgres (a planned step in the backend roadmap), the trace table migrates with it and create_hypertable is called on the existing data. No data is lost; Timescale applies partitioning retroactively.

If Raptor is already on Heroku Postgres (not SQLite), the SQLite path is moot — go straight to Timescale.

Consequences

Positive

• One storage technology for both primary data and trace data (Postgres). Ops team does not need to learn a second system.
• Compression and tiering are declarative (policy-driven), not procedural (job-written). Less code to maintain.
• The 7-year retention requirement for trade-affecting events is handled by tiered storage at very low cost.
• GDPR pseudonymization is standard Postgres UPDATE on pseudonymization fields — no special ClickHouse-style mutation needed.
• Path to ClickHouse or BigQuery exists if volume demands: dump Timescale parquet + load into ClickHouse. Data format is plain Postgres; no proprietary encoding.

Negative

• Timescale compression mutates the physical storage in-place. A compressed chunk cannot be directly updated row-by-row; GDPR pseudonymization requires decompressing the chunk, updating, recompressing. At low volume (<100 DSR requests/year), this is acceptable. At high volume, it becomes a bottleneck. Mitigation: process pseudonymization before chunks are compressed (within the 90-day hot window, where most DSR requests will arrive).
• Timescale Cloud is a separate billing entity from Heroku Postgres. If Raxx uses a managed Timescale instance, that's a second database vendor. Alternative: Heroku Postgres with the timescaledb extension installed manually (requires a Heroku Private Space for custom buildpacks). This is the recommended path until managed Timescale is justified by volume.
• The Timescale add_tiering_policy (cold storage to S3) is only available on Timescale Cloud, not the open-source self-hosted version. If Raxx self-hosts, cold storage requires a custom job. Feature-developer must choose between managed (cost) and self-hosted (complexity) when implementing SC-11.

Open question

This ADR's database path decision is conditional on the answer to Open Question 1 in workflow-uuid-tracing.md §13: is Raptor on SQLite or Heroku Postgres today? If Postgres, the recommendation is Timescale immediately. If SQLite, the recommendation is SQLite for MVP, Timescale at the first Postgres migration.

Alternatives Considered (summary)