TimescaleDB Compression: Hypercore and Columnar Storage

TimescaleDB achieves compression ratios of up to 98% for time-series data by utilizing the hypercore engine. Unlike general-purpose compression, hypercore employs a hybrid row-columnar approach that leverages specialized algorithms tailored to the mathematical properties of time-series data, such as monotonicity and repetition.

Hypercore vs. PostgreSQL TOAST

TimescaleDB compression is complementary to PostgreSQL's built-in TOAST (The Oversized-Attribute Storage Technique) rather than a replacement. While TOAST manages individual large values (e.g., long strings or JSONB) that exceed a specific threshold, hypercore optimizes cross-row patterns.

Feature TOAST (vanilla PostgreSQL) TimescaleDB hypercore
Design Goal Individual values > 2 KB Cross-row patterns in time-series
Trigger Row exceeds TOAST_TUPLE_THRESHOLD Per-chunk policy (e.g., older than 7 days)
Supported Types Variable-length only (text, jsonb, etc.) All data types
Algorithms pglz, lz4 Delta, Delta-of-Delta, Simple-8b, RLE, XOR-based, Dictionary
Granularity Per value Per batch (~1000 rows)
Data Structure Treats values as opaque bytes Exploits numeric structure and repetition
Sensor Floats Ratio ~1.0× 10-20×
Timestamp Ratio ~1.0× 50-100×
Text Ratio 2-3× 5-10×

How Columnar Compression Works

Hypercore converts older data chunks from a row-based format (optimized for fast INSERTs) into a columnar format. In this process, rows are grouped into batches of up to 1,000. Each batch is stored as a single row in a compressed table, where the columns are represented as arrays.

Specialized Compression Algorithms

TimescaleDB selects the compression algorithm based on the column data type to maximize efficiency:

  • Integers, Timestamps, and Booleans: Uses a combination of delta encoding (storing the difference between values), delta-of-delta (storing the change in the difference, which is 0 for regular intervals), simple-8b, and run-length encoding (RLE).
  • Floats (e.g., temperature/vibration): Employs XOR-based compression (based on the Gorilla algorithm). By XORing neighboring floats, the engine stores only the significant bits, ignoring long runs of zeros.
  • JSONB: Uses a two-layer approach: first a dictionary for repeating values, falling back to PostgreSQL TOAST if no repetitions exist.
  • Strings and Other Types: Uses dictionary compression, where the dictionary indexes themselves are further compressed using simple-8b and RLE.

Example: Delta and Run-Length Encoding

For a machine_id that repeats across many rows, RLE stores the value once along with a counter (e.g., MACHINE_001 × 5) instead of repeating the string five times. For timestamps at regular intervals, delta-of-delta encoding can reduce the storage requirement to nearly zero bytes per value.

Optimizing Compression with segmentby and orderby

Two parameters are critical for determining how rows are grouped into batches and how effectively they are compressed:

  • segmentby: Defines the column whose values are shared across a batch (e.g., machine_id). The value is stored once per batch. The query planner uses this metadata to skip entire batches that do not match the WHERE clause.
  • orderby: Defines the sort order within the batch (typically time DESC). Sorting by time maximizes the effectiveness of delta and delta-of-delta encoding because neighboring values are more likely to be similar.

Configuration Example:

ALTER TABLE iot_sensor_data SET (
  timescaledb.orderby = 'time DESC',
  timescaledb.segmentby = 'machine_id'
);

Best Practice: Each segment should contain at least 100 rows per chunk, with an optimal range of 100–10,000 unique segmentby values per chunk.

Impact on Query Performance

For most time-series workloads, compression increases query speed by reducing I/O requirements by 10–20×.

Performance Gains

  • Range scans over time with aggregations (SUM, AVG, MAX).
  • Queries filtering on the segmentby column.
  • Sequential scans over large ranges.

Performance Trade-offs

  • Point lookups of a single row may be slower.
  • UPDATE/DELETE operations on compressed chunks require a decompress–modify–recompress cycle.
  • Queries without a segmentby filter on high-cardinality columns may see performance degradation.

Real-World Benchmark

In a production test using MQTT sensor data, a point read by id and a narrow time range showed a 28× speed-up in execution time (10.2 ms down to 0.36 ms) and a 42.8× compression ratio (308 MB reduced to 7.2 MB) when moving from a rowstore to a columnstore chunk.

Why Columnstore is Faster

  1. Sparse MinMax Index: TimescaleDB automatically builds an index on (segmentby_col, _ts_meta_min_1, _ts_meta_max_1). This allows the engine to eliminate entire batches without reading the actual data.
  2. Native Filtering: Because rows with the same id are physically grouped, the engine hits the correct segment immediately without needing a large B-tree index on (id, time).
  3. Vectorized Execution: Operations on time ranges are processed in batches of 1,000 rows rather than row-by-row, reducing CPU overhead.

Sources