DuckDB + Parquet: Cheap Cold Storage for Analytics Data

The cost curve nobody plans for

Event data grows linearly and gets read logarithmically. Yesterday's pageviews are queried constantly; the same rows from fourteen months ago are queried once a quarter, if that. Keeping both in the same hot storage means paying hot prices for cold data forever.

The fix is tiering: keep recent data in a fast local table, push older data to compressed files, and make the split invisible to queries.

Why Parquet is the right cold format

Parquet is a columnar file format with per-column compression and embedded statistics. Two properties matter here:

• Compression. Event data has low-cardinality columns — country, browser, path — that compress extremely well. 5–10× reduction over raw rows is typical.
• Predicate pushdown. Each row group stores min/max per column. A query filtered to March skips every row group whose timestamp range does not overlap March, without decompressing it.

DuckDB reads Parquet natively, including over HTTP and S3-compatible object storage.

Exporting cold partitions

Export by month so each file is a natural pruning unit:

COPY (
  SELECT * FROM events
  WHERE timestamp >= DATE '2026-01-01'
    AND timestamp <  DATE '2026-02-01'
)
TO 'cold/events_2026_01.parquet'
(FORMAT PARQUET, COMPRESSION ZSTD);

Or let DuckDB write a partitioned directory tree in one statement:

COPY (
  SELECT *, year(timestamp) AS y, month(timestamp) AS m
  FROM events WHERE timestamp < current_date - INTERVAL 30 DAY
)
TO 'cold/events'
(FORMAT PARQUET, PARTITION_BY (y, m), COMPRESSION ZSTD);

Hive-style partitioning means a query filtered on a date range only opens the directories it needs.

Querying object storage directly

DuckDB's httpfs extension reads S3, Cloudflare R2, and any S3-compatible endpoint:

INSTALL httpfs; LOAD httpfs;
SET s3_endpoint = 'ACCOUNT.r2.cloudflarestorage.com';
SET s3_access_key_id = '...';
SET s3_secret_access_key = '...';

SELECT count(*) FROM read_parquet('s3://analytics/cold/events/*/*/*.parquet');

R2 is a common choice for this because it does not charge egress, which matters when a query scans historical files.

Stitching hot and cold into one view

The important part: queries should not know about the tiering.

CREATE OR REPLACE VIEW events AS
  SELECT * FROM events_hot
  UNION ALL
  SELECT * FROM read_parquet('s3://analytics/cold/events/**/*.parquet');

Now SELECT count(*) FROM events WHERE timestamp > current_date - 7 reads only the hot table — DuckDB prunes the cold side on the timestamp predicate — while a two-year trend query transparently spans both.

Two rules keep this correct:

1. The tiers must not overlap. Export a window, verify the file, then delete from hot inside a transaction. An overlap double-counts every metric.

2. Schemas must match exactly, column order included. Adding a column means either rewriting old files or using union_by_name = true in read_parquet.

What this looks like in practice

InsightsTrack's v2 storage layer runs exactly this shape: the last 30 days in a DuckDB hot table, older months exported to Parquet on S3 or R2, and a UNION ALL view named events that every analytics query reads from. Dashboards stay fast on recent windows, historical queries still work, and storage cost stops tracking data volume linearly.

Frequently asked questions

Why Parquet rather than compressed CSV?

Parquet is columnar and self-describing, so a query touching three columns reads only those three, and it carries row-group statistics that let the reader skip whole blocks. Gzipped CSV must be decompressed end to end before anything can be filtered.

Can DuckDB query Parquet on S3 or R2 directly?

Yes, via the httpfs extension. With range requests and row-group statistics it fetches only the byte ranges a query needs, so scanning a partitioned archive does not mean downloading it.

How do I query hot and cold data as one table?

A view built from UNION ALL over the hot table and the cold Parquet glob. Callers query the view and never need to know where a row lives; adding a partition means re-creating the view, not rewriting queries.

What partition size works best?

Monthly partitions suit most analytics workloads — large enough that row groups compress well and file counts stay manageable, small enough that a date filter prunes most of the archive. Daily files become a metadata problem at scale.

When is tiering not worth it?

Below roughly a year of data on one node. The UNION ALL view, the export job, and the object-storage dependency all cost real complexity, and a few gigabytes of local DuckDB does not need any of it.

When not to bother

If your entire dataset is under a few gigabytes, skip this. A single DuckDB file handles it, and tiering only adds failure modes. Reach for it when cold data is meaningfully larger than hot, or when object storage is genuinely cheaper than the disk you are paying for. See also running a warehouse on one server and DuckDB performance tuning.