Improve storage by normalizing the current metrics table

[surister]

As pointed out by @proddata in some private conversations, we could further reduce the storage usage by normalizing how we store the data in CrateDB.

I did some testing on my local prometheus setup and we currently we store the same information, label_set many times, e.g:

SELECT
  count(*)
FROM
  "doc"."metrics"
WHERE
  labels_hash = '478d4639912fc742' LIMIT 10
-- 36_558

In my 33M dataset, this particular label hash is written 36k times, that means that we are writing (in this specific case) the following object, (36.000 - 1) times unnecessarily.

{
  instance: "some_domain"
  __name__: "probe_http_content_length"
  job: "blackbox"
}

Also, in my test dataset, there are 939 unique label hashes, so potentially we could stop writing objects like that (33x10^6-939) times.

[amotl]

Hi. If you see room for improvements in this area, I think it would be very much worth to explore them. 💯 🍀
Maybe you could create some little momentum around it together with @proddata, in order to submit a sketch?

[surister]

Another disadvantage of the current table definition is that the more exporters you add, the bigger the column count gets, with just node exporter and blackbox my metrics table definition looks like:

CREATE TABLE IF NOT EXISTS "doc"."metrics" (
   "timestamp" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   "labels_hash" TEXT NOT NULL,
   "labels" OBJECT(DYNAMIC) AS (
      "job" TEXT,
      "__name__" TEXT,
      "instance" TEXT,
      "phase" TEXT,
      "subject" TEXT,
      "subjectalternative" TEXT,
      "issuer" TEXT,
      "fingerprint_sha256" TEXT,
      "version" TEXT,
      "name" TEXT,
      "type" TEXT,
      "quantile" TEXT,
      "cpu" TEXT,
      "device" TEXT,
      "core" TEXT,
      "package" TEXT,
      "mode" TEXT,
      "governor" TEXT,
      "major" TEXT,
      "minor" TEXT,
      "fstype" TEXT,
      "mountpoint" TEXT,
      "goversion" TEXT,
      "goarch" TEXT,
      "goos" TEXT,
      "branch" TEXT,
      "revision" TEXT,
      "tags" TEXT,
      "chip" TEXT,
      "sensor" TEXT,
      "label" TEXT,
      "chip_name" TEXT,
      "product_family" TEXT,
      "product_version" TEXT,
      "bios_release" TEXT,
      "bios_date" TEXT,
      "chassis_asset_tag" TEXT,
      "bios_version" TEXT,
      "product_name" TEXT,
      "board_name" TEXT,
      "bios_vendor" TEXT,
      "chassis_vendor" TEXT,
      "product_sku" TEXT,
      "board_version" TEXT,
      "board_asset_tag" TEXT,
      "chassis_version" TEXT,
      "board_vendor" TEXT,
      "system_vendor" TEXT,
      "broadcast" TEXT,
      "address" TEXT,
      "operstate" TEXT,
      "adminstate" TEXT,
      "duplex" TEXT,
      "serial" TEXT,
      "firmware_revision" TEXT,
      "model" TEXT,
      "state" TEXT,
      "pretty_name" TEXT,
      "version_codename" TEXT,
      "id" TEXT,
      "id_like" TEXT,
      "version_id" TEXT,
      "collector" TEXT,
      "queue" TEXT,
      "ip" TEXT,
      "clocksource" TEXT,
      "zone" TEXT,
      "time_zone" TEXT,
      "cause" TEXT,
      "nodename" TEXT,
      "machine" TEXT,
      "release" TEXT,
      "sysname" TEXT,
      "domainname" TEXT,
      "code" TEXT
   ),
   "value" DOUBLE PRECISION,
   "valueRaw" BIGINT,
   "day__generated" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('day', "timestamp") NOT NULL,
   PRIMARY KEY ("timestamp", "labels_hash", "day__generated")
)

[surister]

Running a small experiment:

Query ran at 2025-07-02T08:35:27.868Z on CrateDB 5.10.9

SELECT
  table_name,
  SUM(num_docs) as records,
  sum(size / (1024.0 * 1024.0)) / count(*) as avg_mb_per_shard,
  sum(size) / (1024.0 * 1024.0) as total_mib
FROM
  sys.shards
WHERE
  primary
  AND table_name LIKE 'metrics%'
GROUP BY
  1

3 record(s) returned in 0.0562s, showing 3 row(s).

table_name	records	avg_mb_per_shard	total_mib
"metrics_value"	8509	0.773587147394816	9.283045768737793
"metrics_labels"	975	0.04306332270304362	0.2583799362182617
"metrics_snapshot"	10393	0.9853454430898031	11.824145317077637

Where metrics_snapshot is a snapshot of the metrics with ~10K rows and metrics_labels and metrics_value are a normalized version of metrics

Now the labels are only written 975 times, the reduction of storage is around ~90% with this exporters.

1 - ((9.28 + 0.25) / 11.82) = 0.193 -> 19%

Script to replicate experiment: https://github.com/surister/mylab/blob/master/prometheus/main.py

[amotl]

Where metrics_snapshot is a snapshot of the metrics with ~100K rows and metrics_labels and metrics_value are a normalized version of metrics.

Now the labels are only written 975 times, the reduction of storage is around ~90% with this exporters.

That sounds excellent, thanks for sharing your results. Does your experiment include a suggestion to adjust the database schema / logic of storing metrics sustainably for the better?

[surister]

Not yet, this is just a validating experiment to see if it makes sense to go forward. That'd come later together with a migration advice, and breaking change release.

[surister]

I had my suspicions that maybe it wasn't that much data to get a meaningful percentage. I waited, gathered more metrics and run the experiment a few times. At some point it flats out at 4-5% storage reduction, I guess the column store is very efficient with repeated values, so while a 5% reduction is nice, I do not think we should work on this now -- at least not for storage reduction reasons.

Query ran at 2025-07-04T08:59:05.258Z on CrateDB 5.10.9

SELECT
  table_name,
  SUM(num_docs) as records,
  sum(size / (1024.0 * 1024.0)) / count(*) as avg_mib_per_shard,
  sum(size) / (1024.0 * 1024.0) as total_mib
FROM
  sys.shards
WHERE
  primary
  AND table_name LIKE 'metrics%'
GROUP BY
  1

4 record(s) returned in 0.0558s, showing 15 row(s).

table_name	records	avg_mib_per_shard	total_mib
"metrics_value"	19305178	92.10378053453233	1657.868049621582
"metrics_labels"	2750	0.11170101165771484	0.6702060699462891
"metrics_snapshot"	19305178	96.43601253297594	1735.848225593567
"metrics"	19095783	160.31292242474026	2885.6326036453247

1 - (1657.86 + 0.67) / 1735.84 = 0.044

If anyone is affected by the column count issue or has a performance problem, we can revisit this in the future. For now I will leave it as good first issue, if any gopher feels like tackling this.

[amotl]

Thank you for conducting them and for sharing the results of your experiments.