TimescaleDB Columnar Compression Guide (segmentby & orderby Retention Math)
TimescaleOps technical dossier on TimescaleDB Gorilla XOR float encoding, Delta-of-Delta timestamps, ZSTD dictionaries, and 1,000-row compress_segmentby math.
Choosing a high-uniqueness column for compress_segmentby fragments TimescaleDB 1,000-row columnar batches into tiny arrays and destroys storage density. We detail how Gorilla XOR, Delta-of-Delta, and ZSTD codecs pack telemetry columns and how to verify exact per-chunk savings via SQL.
| Telemetry Scale | Chunk / Partition Interval | Server DDR5 RAM | Retained Disk Footprint | Matched Infrastructure Tier |
|---|---|---|---|---|
| 10K/s • 1 Year Retention | 3.6 TB Raw -> ~345 GB |
16 GB DDR5 | ~420 GB NVMe | Hostinger KVM 8 / InterServer |
| 10K/s • 5 Years Retention | 18.1 TB Raw -> ~1.41 TB |
16–32 GB DDR5 | 2 TB ZFS Pool | InterServer Ryzen Storage |
| 50K/s • 1 Year Retention | 18.1 TB Raw -> ~1.73 TB |
32 GB DDR5 | 2.2 TB NVMe / ZFS | InterServer Dedicated Ryzen |
| 50K/s • 5 Years Retention | 90.6 TB Raw -> ~7.1 TB |
64 GB DDR5 | 10 TB Price-Lock ZFS | InterServer Bare-Metal 2x10TB |
ALTER TABLE market_ticks_ohlcv SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'symbol_id, venue_id',
timescaledb.compress_orderby = 'ts DESC'
);
SELECT add_compression_policy('market_ticks_ohlcv', INTERVAL '7 days');
-- Audit exact uncompressed vs compressed byte totals on your hypertable
SELECT
pg_size_pretty(before_compression_total_bytes) AS raw_size,
pg_size_pretty(after_compression_total_bytes) AS compressed_size,
round((1 - after_compression_total_bytes::numeric / before_compression_total_bytes) * 100, 2) AS saved_pct
FROM hypertable_compression_stats('market_ticks_ohlcv');1. The TimescaleDB compress_segmentby Rule: >= 100 Rows Per Entity Per Chunk
TimescaleDB partitions each closed chunk by your compress_segmentby columns and packs up to 1,000 ordered values per column into a single compressed tuple. If a segment contains only 2 or 3 rows inside a chunk — for example, when someone mistakenly segments by a unique event UUID — PostgreSQL stores one metadata row per tiny group and compression collapses. Segment strictly by entity keys used in WHERE filters (device_id, meter_id, symbol_id) that emit at least 100 samples per chunk window.
2. How Gorilla XOR & Delta-of-Delta Codecs Compress TimescaleDB Hypertable Columns
Periodic sensor timestamps exhibit near-zero second-order deltas, enabling Delta-of-Delta encoding to represent 8-byte TIMESTAMPTZ values in a handful of bits. Adjacent float readings share leading IEEE-754 mantissa bits, allowing Gorilla XOR encoding and ZSTD dictionaries to condense numeric and status columns.
3. Isolating TimescaleDB Background Compression Workers on Dedicated CPU Cores
Compressing a multi-gigabyte 1-day chunk sorts millions of rows on CPU. Provisioning Dedicated VDS cores (UltaHost) or bare-metal Ryzen threads (InterServer) prevents background compression policies from stealing CPU time from live ingestion.
TimescaleOps Engineering Q&A: TS-06 // GORILLA-ZSTD Hypertable Operations
Can we run ALTER TABLE ADD COLUMN on a compressed TimescaleDB hypertable?
Yes. Supported TimescaleDB 2.x releases allow adding nullable or default-valued columns to compressed hypertables metadata-first without decompressing historical chunks.
Should timestamp column ts go inside TimescaleDB compress_segmentby or compress_orderby?
Never place timestamps in compress_segmentby. Always declare ts DESC (or ts ASC) in compress_orderby so Delta-of-Delta encoding and chunk min/max time skipping operate properly.
How many PostgreSQL background workers should TimescaleDB allocate for compression policies?
Configure timescaledb.max_background_workers to at least 8 (alongside max_worker_processes >= 16) in postgresql.conf so compression jobs and Continuous Aggregate refreshes never block one another.