TimescaleDB Tick-to-OHLCV Continuous Aggregates Sizing Dossier
Architect 1m, 5m, and 1h OHLCV rollups from high-rate telemetry and tick streams using TimescaleDB Continuous Aggregates and TimescaleOps chunk equations.
Scanning hundreds of millions of raw hypertable tuples every time a dashboard requests a 30-day rollup wastes CPU cores and competes with live writers. TimescaleDB real-time Continuous Aggregates materialize closed time buckets incrementally and read only the open trailing bucket from the hot hypertable.
| Telemetry Scale | Chunk / Partition Interval | Server DDR5 RAM | Retained Disk Footprint | Matched Infrastructure Tier |
|---|---|---|---|---|
| 50 Streams • 1K events/s | 7d / 1d |
8 GB DDR5 | 128 GB Local NVMe | Vultr HF 8GB / Hostinger KVM 2 |
| 250 Streams • 10K events/s | 1d |
16 GB DDR5 | 384 GB Local NVMe | Vultr HF 16GB (3.5GHz+) |
| 1,000 Streams • 50K events/s | 6h |
32 GB DDR5 | 2 TB NVMe / ZFS | InterServer Ryzen 64GB |
| High-Density 150K events/s | 6h / 1h |
64 GB DDR5 | 6 TB ZFS Vault | InterServer Bare-Metal + Vultr Ingest |
CREATE MATERIALIZED VIEW ohlcv_1m
WITH (timescaledb.continuous, timescaledb.materialized_only = false) AS
SELECT
time_bucket('1 minute', ts) AS bucket,
symbol_id,
venue_id,
first(last_px, ts) AS open,
max(last_px) AS high,
min(last_px) AS low,
last(last_px, ts) AS close,
sum(last_qty) AS volume,
sum(last_px * last_qty) / NULLIF(sum(last_qty), 0) AS vwap
FROM market_ticks_ohlcv
GROUP BY bucket, symbol_id, venue_id
WITH NO DATA;
SELECT add_continuous_aggregate_policy('ohlcv_1m',
start_offset => INTERVAL '2 hours',
end_offset => INTERVAL '1 minute',
schedule_interval => INTERVAL '1 minute');1. Placing TimescaleDB Collectors on High-Clock Local NVMe Nodes
High-rate WebSocket and telemetry collectors benefit from single-thread CPU clock speed during COPY serialization and B-tree page splits. Deploying hot ingestion nodes on 3.5GHz+ local NVMe instances near your upstream data source minimizes TCP socket queuing.
2. Hierarchical TimescaleDB Continuous Aggregates: Cascading 5m, 1h & 1d Views
Since TimescaleDB 2.9+, Continuous Aggregates stack hierarchically. We recommend materializing 1-minute buckets from raw hypertable rows first, then defining 15-minute and 1-hour rollups atop ohlcv_1m so background refresh jobs scan 60 pre-aggregated rows per hour instead of raw tick streams.
3. Enabling TimescaleDB Columnar Compression on Both Raw Hypertables and Rollup Views
Continuous aggregate materialization tables are themselves hypertables under the hood. Enabling columnar compression on ohlcv_1m after 14 days (segmented by symbol_id) alongside a 7-day compression policy on raw rows keeps multi-year historical bars compact.
TimescaleOps Engineering Q&A: TS-02 // OHLCV-CAGG Hypertable Operations
Should TimescaleDB hypertables store ticker or device identifiers as TEXT or INTEGER keys?
Map repetitive string identifiers to a SMALLINT or INTEGER lookup table (symbol_id or device_id). Integer keys narrow composite TimescaleDB B-tree index entries and speed up compress_segmentby filtering.
How should Python or Rust services batch-insert 50,000 rows/sec into a TimescaleDB hypertable?
Buffer 2,000 to 10,000 rows in memory (or flush every 250ms) and stream batches over the PostgreSQL binary COPY protocol rather than issuing individual INSERT statements.
Does materialized_only = false slow down TimescaleDB Continuous Aggregate queries?
Only when end_offset spans hours of uncompressed rows. Keep end_offset near 1 minute so TimescaleDB scans at most 60 seconds of live hot tuples and reads pre-computed buckets for older ranges.