# Optimizing storage for historic time-series data

**URL:** <https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762>\
**Category:** Tutorials\
**Tags:** performance, data-storage\
**Created:** [August 3, 2021, 1:03pm UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762 "2021-08-03T13:03:59Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [August 3, 2021, 1:03pm UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/1 "2021-08-03T13:03:59Z")

</div>

When dealing with time-series data, performance is crucial. Data that gets ingested should be available quickly for querying. To enable fast decision-making, analytical queries often need to return results in a fraction of a second.

As data ages, it is typically involved less often in real-time feedback loops, making query performance less of a concern. Instead, storage cost becomes more important as data accumulates. CrateDB is already very efficient in terms of storage usage (30 - 40% less than other time-series databases for equivalent schemas). In addition, CrateDB allows leveraging arrays to significantly reduce storage, which is described in this article.

## Initial table design

Let’s look at a typical table design where one row is stored per second and `sensor_id`:

```sql
CREATE TABLE sensor_readings (
   time TIMESTAMP WITH TIME ZONE NOT NULL,
   sensor_id TEXT NOT NULL,
   battery_level DOUBLE PRECISION,
   battery_status TEXT,
   battery_temperature DOUBLE PRECISION
);

```

For 120 million records, the table has a size of 6.3 GB. As CrateDB indexes all columns by default, analytical queries like the one below finish in less than a second:

```sql
-- count of occurrences per day/sensor_id on which the battery level was below 10%
SELECT DATE_TRUNC('day', time) AS day,
       sensor_id,
       COUNT(*)
FROM sensor_readings
WHERE battery_level < 10
GROUP BY 1, 2
ORDER BY 1, 2;

```

## Using arrays for time-based bucketing

Now we create a second table for historic data, optimizing storage consumption:

```auto
CREATE TABLE sensor_readings_historic (
   time_bucket TIMESTAMP WITH TIME ZONE NOT NOLL,
   time ARRAY(TIMESTAMP WITH TIME ZONE) INDEX OFF,
   sensor_id TEXT NOT NULL,
   battery_level ARRAY(DOUBLE PRECISION) INDEX OFF,
   battery_status ARRAY(TEXT) INDEX OFF,
   battery_temperature ARRAY(DOUBLE PRECISION) INDEX OFF
)
WITH (codec = 'best_compression');

```

The key changes are:

- `time` is now modeled as an array of timestamps. `time_bucket` is a truncated timestamp on day-level for easier querying. It allows selecting all rows for a particular day without having to inspect all array values.
- The metrics have also become arrays (`battery_*` columns)
- The compression was changed to the more aggressive `best_compression` at the cost of slightly slower lookups
- Indexes have been turned off for array columns

To maintain a good balance between storage efficiency and query performance, we limit the array size to 2880 elements when populating the table. If there are more values for a `time_bucket`, an additional row is inserted.

The size of the table is now 1.1 GB, which is a reduction of more than 80%.

### Copying data into the historical table

To copy data from `sensor_readings` to `sensor_readings_historic`, you can use a query like this:

```auto
-- copying data into historic table
INSERT INTO sensor_readings_historic
SELECT DATE_TRUNC('day', time) AS time_bucket,
       ARRAY_AGG(time),
       sensor_id,
       ARRAY_AGG(battery_level) AS battery_level,
       ARRAY_AGG(battery_status) AS battery_status,
       ARRAY_AGG(battery_temperature) AS battery_temperature
FROM sensor_readings
-- filtering for the previous month
WHERE DATE_TRUNC('month', time) = DATE_TRUNC('month', NOW()) - '1 month'::INTERVAL
GROUP BY 1, 3;

-- deleting data from the previous table
DELETE FROM sensor_readings WHERE DATE_TRUNC('month', time) = DATE_TRUNC('month', NOW()) - '1 month'::INTERVAL;

```

Please note that `DELETE` statements in CrateDB should always be based on a partition, so that a complete partition can be dropped rather than a set of rows. For simplicity, we excluded partitioning in this article, please see [Sharding and partitioning guide for time-series data](https://community.cratedb.com/t/sharding-and-partitioning-guide-for-time-series-data/737).

## Querying

Using the `UNNEST` table function, we can transform the table layout of `sensor_readings_historic` to match that of `sensor_readings`. A `UNION ALL` combines both tables, saved as a view:

```sql
CREATE VIEW sensor_readings_full_history AS
SELECT DATE_TRUNC('day', time) AS time_bucket,
       time,
       sensor_id,
       battery_level,
       battery_status,
       battery_temperature
FROM sensor_readings
UNION ALL
SELECT time_bucket,
       UNNEST(time) AS time,
       sensor_id,
       UNNEST(battery_level) AS battery_level,
       UNNEST(battery_status) AS battery_status,
       UNNEST(battery_temperature) AS battery_temperature
FROM sensor_readings_historic;

```

It is important to always query `sensor_readings_historic` with a `WHERE` condition on `time_bucket` to allow efficient row filtering. To keep both tables identical, we add a calculated `time_bucket` column to `sensor_readings` as well.

Instead of unnesting all arrays first and then applying analytical SQL functions, CrateDB also offers scalar functions directly on arrays. CrateDB 4.6.0 adds `ARRAY_SUM`, `ARRAY_MIN`, `ARRAY_MAX`, and `ARRAY_AVG` as new functions.

```auto
-- count days per sensor_id on which the battery level was below 10%
SELECT time_bucket,
       sensor_id,
       COUNT(*)
FROM sensor_readings_historic
WHERE ARRAY_MIN(battery_level) < 10
GROUP BY 1, 2;

```

## Using user-defined functions

A more flexible alternative to predefined scalar functions is user-defined functions. They allow processing arrays via JavaScript for more sophisticated aggregations.

The example below recreates the first analytical query from above, counting the exact number of occurrences where the battery level was below 10%:

```sql
CREATE OR REPLACE FUNCTION count_less_than(ARRAY(DOUBLE), DOUBLE) RETURNS DOUBLE
LANGUAGE JAVASCRIPT AS '
    function count_less_than(dataArray, threshold) {
        return Array.prototype.reduce.call(dataArray, function (counter, currentValue) {
            if (currentValue < threshold) {
                counter++;
            }
            return counter;
        }, 0);
    }';

-- count of occurrences per day/sensor_id on which the battery level was below 10%
SELECT time_bucket,
       sensor_id,
       SUM(count_less_than(battery_level, 10))
FROM sensor_readings_historic
GROUP BY 1, 2
ORDER BY 1, 2;

```

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [June 3, 2022, 4:18pm UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/2 "2022-06-03T16:18:31Z")

</div>

Few questions :

1. Can codec be set at partition level?
2. Can codec be changed once my data becomes old? e.g. I can keep my cold data partitions with codec=best compression instead of default to save on disk space if that is possible.
3. Is time\_bucket above case alternative to creating partitions ?

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [June 7, 2022, 6:16am UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/3 "2022-06-07T06:16:13Z")

</div>

Hi @vinayak.shukre,

You can run `ALTER TABLE table PARTITION (partition_key = partition_value) SET (codec = 'best_compression');` on an already existing partition. However, you will need to close the partition before doing that. While a partition is closed, its rows won’t be included in the output of any `SELECT` queries:

Example:

```sql
ALTER TABLE table PARTITION (ts_month = 1648771200000) CLOSE;
ALTER TABLE table PARTITION (ts_month = 1648771200000) SET (codec = 'best_compression');
ALTER TABLE table PARTITION (ts_month = 1648771200000) OPEN;

```

Whether `time_bucket` in the examples above qualifies as a replacement for partitioning depends on the data volume. As per [Sharding and Partitioning Guide for Time Series Data](https://community.cratedb.com/t/sharding-and-partitioning-guide-for-time-series-data/737), if the size of a shard exceeds 70 GB, it is recommended to use partitioning. Therefore, it can still make sense to use partitioning to avoid degrading index performance. For tables where performance plays a less critical role, you might choose to go for a higher shard size, like 100 GB instead of 70 GB.

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [June 7, 2022, 10:10am UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/4 "2022-06-07T10:10:59Z")

</div>

Thanks @hammerhead . I just fired above commands on one of my sample data partition. It was 2.2 TiB before. I am waiting for it to come again alive. All shards are in unassigned states after the above three commands were fired.

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [June 16, 2022, 12:47pm UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/5 "2022-06-16T12:47:55Z")

</div>

@hammerhead I tried above scenario on partition with 2.2TiB data. After the three commands were fired with a gap of 1 minute each, my shards went in unassigned state and did not recover at all for long time. Then I restarted the cluster and then my table including this partition recovered but size is still 2.2 TiB.

Then I ran it on a single node cluster where partition is just 35MB and data is around few hundred thousands. No error was seen in the log but size remained just 35 MB.

Then I left it for few days and again tried today on a different cluster with smaller partition size like 1MB etc and do not see any error and no effect is seen.

Not sure what I am missing.

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [June 20, 2022, 11:13am UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/6 "2022-06-20T11:13:04Z")

</div>

Hi @vinayak.shukre,

I tried the workflow on a bigger partition and also don’t see the expected size reduction.  
I opened an issue for our developers to provide feedback on this matter: [Changing `codec` on existing partitions appears to have no effect · Issue #12629 · crate/crate · GitHub](https://github.com/crate/crate/issues/12629).

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [June 21, 2022, 11:47am UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/7 "2022-06-21T11:47:23Z")

</div>

Hi @vinayak.shukre,

the outcome of the above-linked discussion with our developers is that changing `codec` only takes effect for newly written segments (i.e. newly inserted data). Hence, we didn’t see any change for already existing partitions.  
To force a rewrite of existing segments, please run `OPTIMIZE TABLE table PARTITION (partition_key = partition_key_value) WITH (max_num_segments = 1);`.  
Documentation is going to be updated shortly to include this information as well.

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [June 21, 2022, 1:38pm UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/8 "2022-06-21T13:38:31Z")

</div>

Thanks @hammerhead for following up on this for me. Is there any way, I can see codec set for each partition? For table, I can see that in show create table tablename output

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 21, 2022, 1:48pm UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/9 "2022-06-21T13:48:14Z")

</div>

```sql
SELECT table_name, table_schema, "values", partition_ident, settings['codec']
FROM information_schema.table_partitions;

```

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [June 22, 2022, 4:09am UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/10 "2022-06-22T04:09:17Z")

</div>

> [@hammerhead](#):
>
> `OPTIMIZE TABLE table PARTITION (partition_key = partition_key_value) WITH (max_num_segments = 1);`.

This worked fine on a smaller size partition. 33.9 MiB partition came down to 21 MiB.  
2 TB partition after closing and opening, has not recovered after 15 hours and is in critical condition.

Looks like this feature is not very much usable on real big data. 😔

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 22, 2022, 9:31am UTC](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762/11 "2022-06-22T09:31:04Z")

</div>

> [@vinayak.shukre](#):
>
> 2 TB partition after closing and opening, has not recovered after 15 hours and is in critical condition.

This might be a different problem after all. Even clusters with 100 TiB should recover in minutes, not hours rather quickly and `OPTIMIZE` is a task that runs in the background.