TimescaleDB Compression: Hypercore and Columnar Storage with up to 98% Ratio in PostgreSQL
Pangram verdict · v3.3
We believe that this document is a mix of AI-generated, and human-written content
AI likelihood · overall
MixedArticle text · 1,392 words · 6 segments analyzed
TimescaleDB can achieve compression of up to 98% for typical time-series data. Compressing time-series data requires a fundamentally different approach than the general-purpose algorithms used in OLTP databases. In TimescaleDB this is handled by the hypercore engine — a hybrid row-columnar engine that uses specialized algorithms: delta encoding, delta-of-delta, Gorilla XOR and run-length encoding. This article explains how it works and how to configure compression so that you actually achieve that ratio.TimescaleDB compression - how it differs from PostgreSQL TOASTPostgreSQL has a built-in mechanism called TOAST (The Oversized-Attribute Storage Technique), but TimescaleDB compression solves a fundamentally different problem. TOAST deals with individual large values (long strings, jsonb, bytea), whereas TimescaleDB compression optimizes cross-row patterns in time-series data. The two mechanisms are complementary, not competing — TimescaleDB even uses TOAST internally as a fallback for certain data types. PostgreSQL uses a fixed “page size”, typically 8 kB, and does not allow tuples to span multiple pages. For that reason, when field values are very large, the data must be compressed and/or split across multiple physical rows.FeatureTOAST (vanilla PostgreSQL)TimescaleDB hypercoreDesign goalIndividual values > 2 KBCross-row patterns in time-seriesTriggerRow exceeds TOAST_TUPLE_THRESHOLD (~2 KB)Per-chunk policy (e.g. older than 7 days)Supported typesVariable-length only (text, jsonb, bytea, numeric)All data typesAlgorithmspglz (default), lz4 (since PG14, opt-in)Combination: delta encoding, delta-of-delta, simple-8b, run-length encoding, XOR-based, dictionary compressionCompression granularityPer value
(1 value = 1 byte stream)Per batch (~1000 rows together)Exploiting data structureNo - treats values as opaque bytesYes - exploits numeric structure, monotonicity, repetitionTypical ratio for sensor floats~1.0× (no compression)10-20×Typical ratio for timestamps~1.0× (no compression - fixed-length type)50-100× (delta-of-delta for regular intervals)Typical ratio for text2-3× (general-purpose LZ)5-10× (dictionary + RLE if repetitive)The table shows the scale of the difference. For a typical IoT workload with floats and timestamps — i.e. the columns TOAST does not compress at all — TimescaleDB reaches a ratio of 10-100×, because it is built for this type of data.The Hypercore engine and columnar compressionIn TimescaleDB, compression is handled by an engine called hypercore — a hybrid row-columnar engine in which new data lands in Postgres row-based chunks (fast INSERTs and UPDATEs), while older chunks are automatically converted to a columnar, compressed format. Analytical queries that read this compressed data read fewer bytes and run faster. This conversion enables compression of up to 98%, which significantly lowers storage costs in projects with long data retention. Unlike traditional row-based storage, where data is stored sequentially by row, columnar storage organizes and compresses data by column. As a result, queries can fetch only the necessary fields in batches instead of scanning entire rows.What happens to the rowsConverting a chunk groups rows into batches of up to 1000 and each batch becomes a single row in the compressed table, in which the columns are arrays.Each compressed batch:Encapsulates columnar data in compressed arrays of up to 1000 values per column, stored as a single entry in the compressed table.Uses a column-major format inside the batch, which enables efficient scans by colocating values of the same column and lets you select individual columns without reading the entire batch.Applies advanced column-level compression techniques — run-length encoding, delta encoding, Gorilla compression — reducing storage and improving I/O.Source: https://www.tigerdata.com/docs/learn/deep-dive/whitepaper#data-modelAn example of compression using delta
encoding:timemachine_idsensor_typevalue12:00:00MACHINE_001temp72.512:00:00MACHINE_001speed2.012:00:05MACHINE_001temp72.712:00:05MACHINE_001speed2.112:00:10MACHINE_001temp72.412:00:10MACHINE_001speed2.4With delta encoding you only need to store how much each value changed relative to the previous data point, which means smaller values to store. After the first row, you can represent the following rows using less information, for example:timemachine_idsensor_typevalue12:00:00MACHINE_001temp72.50 secondsMACHINE_001speed2.05 secondsMACHINE_001temp+0.20 secondsMACHINE_001speed+0.15 secondsMACHINE_001temp-0.30 secondsMACHINE_001speed+0.3In time-series data it is often the case that certain values repeat for some period. For example, if you have a temperature sensor that reads 72.5 degrees for 10 minutes, then suddenly rises to 73.0 degrees and stays there for another 10 minutes, you can use delta-of-delta encoding. If the interval is constant (e.g. always 5 seconds), the delta-of-delta is 0 and can be stored in a very small number of bits.timemachine_idsensor_typevalue12:00:00MACHINE_001temp72.5+5 secondsMACHINE_001temp+0.20 secondsMACHINE_001temp-0.30 secondsMACHINE_001temp+0.30 secondsMACHINE_001temp-0.1Delta encoding works great for numeric values that change by small amounts, but time-series data also often contains columns in which the same value repeats across many consecutive rows — for example machine_id, sensor_type or device status.
In such cases run-length encoding (RLE) is used, which instead of storing the same value repeatedly stores it once together with the number of repetitions.Data before compression:timemachine_idsensor_typevalue12:00:00MACHINE_001temp72.512:00:05MACHINE_001temp72.712:00:10MACHINE_001temp72.412:00:15MACHINE_001temp72.612:00:20MACHINE_001temp72.5After applying RLE to the machine_id and sensor_type columns:machine_idsensor_typeMACHINE_001 × 5temp × 5Instead of five copies of the string MACHINE_001 (~55 bytes) we store a single value plus a counter (~15 bytes). With millions of rows sharing the same machine_id value, the savings are enormous.In the end it will look like this:columntechniquerepresentation after compressiontimedelta-of-delta12:00:00, +5s, 0, 0, 0machine_idrun-length encodingMACHINE_001 × 5sensor_typerun-length encodingtemp × 5valuedelta encoding72.5, +0.2, -0.3, +0.2, -0.1There are many other methods used in TimescaleDB, which you can read about in the official documentation.Compression is not “one size fits all” — TimescaleDB picks the algorithm per column type, which is key to understanding why the ratio varies so much between schemas:Integers, timestamps, booleans and integer-like types — a combination of delta encoding, delta-of-delta, simple-8b and run-length encoding. Delta-of-delta produces small numbers (for regular intervals — all zeros), and simple-8b then physically packs those small numbers into a few bits per value. A similar approach (delta-of-delta for timestamps) is used by Facebook’s Gorilla algorithm.Columns without many repetitions (e.g. floats from temperature and vibration measurements) — XOR-based compression (based on Gorilla) with a touch of dictionary compression.
XORing neighboring floats yields a result with a long run of leading and trailing zeros when the values are similar — then you only need to store the middle “significant” bits instead of the full 64.JSONB — two layers: first dictionary (when values repeat), and if there are no repetitions, a fallback to PostgreSQL TOAST (pglz by default, lz4 if configured).Everything else (strings, more unusual types) — dictionary compression. The dictionary indexes also go through simple-8b + RLE, so the compression is two-stage.That is why sensor_type in the form 'TEMPERATURE'/'SPEED'/'PRESSURE' compresses brilliantly (a 3-element dictionary plus RLE on the indexes), monotonically increasing time drops to almost zero bytes per value, while a high-entropy column such as a per-row UUID will be much worse — a dictionary helps little, because every value is unique, so the dictionary is just as large as the original data. TimescaleDB detects this case and simply does not use a dictionary then.segmentby and orderby — the most important parametersThese are the two parameters you have to choose deliberately, because they determine how rows are grouped into batches before compression.segmentby — the column whose values are shared across an entire batch (e.g. machine_id or sensor_id).
The value is stored once per batch, not as an array. In addition, the planner uses segmentby metadata to skip entire batches that do not match the WHERE clause.orderby — the sort order inside the batch (usually time DESC). Sorting by time gives delta encoding and delta-of-delta their maximum advantage — neighboring values are close to each other, so the differences are small and pack into a few bits.ALTER TABLE iot_sensor_data SET ( timescaledb.orderby = 'time DESC', timescaledb.segmentby = 'machine_id' ); Queries with a WHERE machine_id = '...' AND time BETWEEN ... filter on a table configured this way can be an order of magnitude faster than without segmentby, because the planner skips other machines’ batches based on metadata — without touching the data itself.TimescaleDB packs rows into batches of ~1000 and compresses each batch separately. If segmentby has too high a cardinality (e.g. segmentby = sensor_id with thousands of sensors in IoT, where each sensor has only a few rows per chunk), then each “segment” in the chunk has too few rows, the batches are underfilled and compression is ineffective — the delta/XOR encoders need a series of similar values to compress anything.The official rule from the documentation: each segment should contain at least 100 rows in a chunk, and optimally 100–10,000 unique segmentby values per chunk.What does compression do to query performance?A common question: does compression slow down queries?Short answer: for typical time-series queries — it speeds them up.Queries that speed up (the majority of the workload):Range scans over time with aggregation (SUM, AVG, MAX per time bucket)Queries with a filter on the segmentby columnSequential scans over large rangesColumnar compression reduces I/O by 10-20×. A query reading 1 GB uncompressed vs 100 MB compressed = fewer disk reads, less memory, less CPU for deserialization.