Hot and Cold Storage for Analytics Without a Data Warehouse

Two access patterns, one dataset

Analytics queries are not uniform. Most hit the last thirty days: dashboards, real-time views, week-over-week comparisons. A small minority reach back a year for a trend or an annual report.

Storing both the same way means paying for the fast path across the whole dataset, forever. Tiering fixes that.

The split

Hot — the last 30 days in a local DuckDB file. Milliseconds, sits on the instance's disk.

Cold — older data as Parquet on S3, R2 or MinIO. Slower to reach, an order of magnitude cheaper, and effectively unbounded.

Archiving

Run periodically:

1. Select rows older than the retention window.

2. Write them as Parquet, partitioned by month — events/year=2026/month=03/.

3. Verify the file is readable and the row count matches.

4. Only then delete from the hot store.

That order matters. Deleting before verifying the archive is how data disappears.

COPY (
  SELECT * FROM events
  WHERE timestamp < now() - INTERVAL 30 DAY
) TO 's3://bucket/events/year=2026/month=03/data.parquet'
  (FORMAT PARQUET, COMPRESSION ZSTD);

ZSTD over Snappy: roughly 20% smaller for a small CPU cost, and storage is the thing being optimised.

Querying across the boundary

DuckDB's httpfs extension reads Parquet over S3 directly, so a single query can span both tiers:

SELECT date_trunc('month', timestamp) AS month, COUNT(*) AS events
FROM (
  SELECT timestamp FROM events                          -- hot
  UNION ALL
  SELECT timestamp FROM read_parquet('s3://bucket/events/**/*.parquet')  -- cold
)
WHERE timestamp >= ? AND timestamp < ?
GROUP BY 1 ORDER BY 1;

Partition pruning is what makes this viable — a query for March 2026 reads one directory, not the archive.

Partitioning is the whole game

Get this wrong and cold queries scan everything. Partition by the column you filter on, which for events is almost always time. Month is usually right: day creates too many small files, year creates files too large to prune usefully.

Honest expectations

Hot queries stay in the milliseconds. Cold queries are seconds, dominated by network round-trips to object storage.

That is the correct trade. Someone running an annual report will wait three seconds. Someone loading a dashboard will not. Tiering matches the cost to the expectation.