Oximeter: research better ClickHouse compression

As a columnar database, clickhouse is good at compressing data, and performs well with minimal configuration. By default, we use lz4 compression:

oxz_clickhouse_aa646c82-c6d7-4d0c-8401-150130927759.local :) select value from system.settings where name = 'network_compression_method';

SELECT value
FROM system.settings
WHERE name = 'network_compression_method'

Query id: 36ddcb6a-f132-4079-ae85-56b03d37df4f

   ┌─value─┐
1. │ LZ4   │
   └───────┘

1 row in set. Elapsed: 0.003 sec.

However, we can plausibly do better for oximeter. Compression ratios for most measurement table columns is good, but timestamp is a notable exception:

SELECT
    name,
    type,
    compression_codec,
    formatReadableSize(data_compressed_bytes) AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS ratio
FROM system.columns
WHERE (database = currentDatabase()) AND (`table` = 'measurements_cumulativeu64')
ORDER BY data_compressed_bytes DESC

Query id: 0c7d31c8-9194-4040-9527-fda5c027fcb8

   ┌─name────────────┬─type─────────────────┬─compression_codec─┬─compressed─┬─uncompressed─┬──ratio─┐
1. │ timestamp       │ DateTime64(9, 'UTC') │                   │ 4.05 GiB   │ 4.43 GiB     │   1.09 │
2. │ datum           │ Nullable(UInt64)     │                   │ 1.71 GiB   │ 4.99 GiB     │   2.91 │
3. │ timeseries_name │ String               │                   │ 69.21 MiB  │ 16.12 GiB    │ 238.43 │
4. │ timeseries_key  │ UInt64               │                   │ 24.39 MiB  │ 4.43 GiB     │ 186.09 │
5. │ start_time      │ DateTime64(9, 'UTC') │                   │ 24.07 MiB  │ 4.43 GiB     │ 188.56 │
   └─────────────────┴──────────────────────┴───────────────────┴────────────┴──────────────┴────────┘

5 rows in set. Elapsed: 0.005 sec.

Note that clickstack uses CODEC(Delta(8), ZSTD(1)) for its metrics timestamp column: https://clickhouse.com/docs/use-cases/observability/clickstack/ingesting-data/schemas. Note also that clickstack uses LowCardinality(String) rather than plain String to represent series names; this might help a bit with compression, and potentially also with query latency.

We should empirically test the effects of different compression codecs, as well as LowCardinality, on compressed disk use, cpu use, and latency (read and write paths for cpu and latency). Seeing that other clickhouse-based observability applications use both, I'm hopeful that we can find a better point on the compression/cpu trade-off than the default for oximeter measurement tables.

Note: we probably can't tackle this anytime soon, and should possibly fold it into a larger project like https://rfd.shared.oxide.computer/rfd/0695. We don't have tooling to support clickhouse schema changes at the moment, and that tooling probably isn't worth building if we're going to revisit the underlying data model.

Last note: I'm labeling this as a postmortem issue because we talked about it during a postmortem related to clickhouse filling up its disk.

Start with the ClickHouse system.settings and system.columns queries shown in the issue, then read RFD 0695 for the broader data-model context. Compare codec and LowCardinality options across disk use, read/write CPU, and latency. Done means an empirical recommendation or a clear explanation of why this should wait for the larger project.