When compression is enabled, TimescaleDB converts data stored in many rows into an array. This means that instead of using lots of rows to store the data, it stores the same data in a single row. Because a single row takes up less disk space than many rows, it decreases the amount of disk space required, and can also speed up some queries.
As a simplified example, you might have a table that looks like this to start with:
When compression is applied, the data is converted to a single row containing an array, like this:
|[12:00:02, 12:00:02, 12:00:01, 12:00:1]||[1, 2, 1, 2]||[88.2, 300.5, 88.6, 299.1]||[0.8, 0.9, 0.85, 0.95]|
orderbyparameters during compression.
This section explains how to enable native compression, and then goes into detail on the most important settings for compression, to help you get the best possible compression ratio.
You can enable compression on individual hypertables, by declaring which column
you want to segment by. In this procedure, we are using this example table,
example, and we are going to segment it by the
device_id column. We
want every chunk that is more than seven days old to be automatically
pgslprompt, alter the table:
ALTER TABLE example SET ( timescaledb.compress, timescaledb.compress_segmentby = 'device_id' );
SELECT add_compression_policy('example', INTERVAL '7 days');
Data is usually compressed after an interval of time has elapsed, and not immediately. In the "Enabling compression" procedure, we used a seven day compression interval. Choosing a good compression interval can make your queries more efficient, and also allow you to handle data that is out of order.
From our research and experience we know that when data is newly ingested, the queries are more likely to be shallow in time, and wide in columns. Generally, they are debugging queries, or queries that cover the whole system, rather than specific, analytic queries. An example of the kind of query more likely for new data is "show me the current CPU usage, disk usage, energy consumption, and I/O for a particular server". When this is the case, the uncompressed data will have better query performance, so the native PostgreSQL row-based format is the best option.
However, as your data ages, your queries are likely to change. They become more analytical, and involve fewer columns. An example of the kind of query run on older data is "calculate the average disk usage over the last month." This type of query runs much faster on compressed, columnar data.
To take advantage of this and increase your query efficiency, you want to run queries on new data that is uncompressed, and on older data that is compressed. Setting the right compression policy interval means that recent data is ingested in an uncompressed, row format for efficient shallow and wide queries, and then automatically converted to a compressed, columnar format after it ages and is more likely to be queried using deep and narrow queries. Therefore, one consideration for choosing the age at which to compress the data is when your query patterns change from shallow and wide to deep and narrow.
Trying to change chunks that have already been compressed can be inefficient.
You can always query data in compressed chunks, but the current version of
compression does not support
DELETE actions on compressed chunks.
This limitation means you really only want to compress a chunk at a time when it
is unlikely to be modified again. How much time this requires is highly
dependent on your individual setup. Choose a compression interval that minimizes
the need to decompress chunks, but keep in mind that you want to avoid storing
data that is out of order.
A chunk can be in one of three states:
Compression candidateand uncompressed
Active chunks are uncompressed and able to ingest data. Due to the nature of the compression mechanism, they cannot effectively ingest data while compressed. As shown in this illustration, as active chunks age, they become compression candidates, and are eventually compressed when they become old enough according to the compression policy.
When you compress data, you need to select which column to segment by. Each row in a compressed table must contain data about a single item. The column that a table is segmented by contains only a single entry, while all other columns can have multiple arrayed entries. For example, in this compressed table, the first row contains all the values for device ID 1, and the second row contains all the values for device ID 2:
|[12:00:02, 12:00:01]||1||[88.2, 88.6]||[20, 25]||[0.8, 0.85]|
|[12:00:02, 12:00:01]||2||[300.5, 299.1]||[30, 40]||[0.9, 0.95]|
Because a single value is associated with each compressed row, there is no need
to decompress to evaluate the value in that column. This means that queries with
WHERE clauses that filter by a
segmentby column are much more efficient,
because decompression can happen after filtering instead of before. This avoids
the need to decompress filtered-out rows altogether.
Because some queries are more efficient than others, it is important to pick the
correct set of
segmentby columns. If your table has a primary key all of the
primary key columns, except for
time, can go into the
segmentby list. For
example, if our example table uses a primary key on
(device_id, time), then
segmentby list is
Another method is to determine a set of values that can be graphed over time.
For example, in this EAV (entity-attribute-value) table, the series can be
metric_name. Therefore, the
segmentby columns are useful, but can be overused. If you specify a lot of
segmentby columns, the number of items in each compressed column is reduced,
and compression is not as effective. A good guide is for each segment to contain
at least 100 rows per chunk. To achieve this, you might also need to use
By default, the items inside a compressed array are arranged in descending order
according to the hypertable's
time column. In most cases, this works well,
provided you have set the
segmentby option appropriately. However, in some
more complicated scenarios, you want to manually adjust the
commpression_orderbysetting as well. Changing this value can improve the
compression ratio, as well as query performance.
Compression is most effective when adjacent data is close in magnitude or exhibits some sort of trend. Random data, or data that is out of order, compresses poorly. This means that it is important that the order of the input data causes it to follow a trend.
In this example, we haven't set any
segmentby columns, so the data is sorted
time column. If you look at the
cpu column, you can see that it might
not be able to be compressed, because even though both devices are outputting a
value that is a float, the measurements have different magnitudes, with device 1
showing numbers around 88, and device 2 showing numbers around 300:
|[12:00:02, 12:00:02, 12:00:01, 12:00:01 ]||[1, 2, 1, 2]||[88.2, 300.5, 88.6, 299.1]||[20, 30, 25, 40]||[0.8, 0.9, 0.85, 0.95]|
To improve the performance of this data, you can order by
device_id, time DESC
instead, using these commands:
ALTER TABLE example SET (timescaledb.compress, timescaledb.compress_orderby = 'device_id, time DESC');
Using those settings, the compressed table now shows each measurement in consecutive order, and the
cpu values show a trend. This table will compress much better:
|[12:00:02, 12:00:01, 12:00:02, 12:00:01 ]||[1, 1, 2, 2]||[88.2, 88.6, 300.5, 299.1]||[20, 25, 30, 40]||[0.8, 0.85, 0.9, 0.95]|
Putting items in
segmentby columns often achieves similar
results. In this same example, if you set it to segment by the
column, it will have good compression, even without setting
orderby. This is
because ordering only matters within a segment, and segmenting by device means
that each segment represents a series if it is ordered by time. So, if
segmenting by an identifier causes segments to become too small, try moving the
segmentby column into a prefix of the
You can also use ordering to increase query performance. If a query uses similar
ordering as the compression, you can decompress incrementally and still return
results in the same order. You can also avoid a
SORT. Additionally, the system
automatically creates additional columns to store the minimum and maximum value
orderby column. This way, the query executor looks at this additional
column that specifies the range of values in the compressed column, without
first performing any decompression, in order to determine whether the row could
possibly match a time predicate specified by the query.