oxidecomputer / omicron

Omicron: Oxide control plane
Mozilla Public License 2.0
252 stars 40 forks source link

Push OxQL `align` table operations for gauges into the database #6887

Open bnaecker opened 1 month ago

bnaecker commented 1 month ago

See #6480. That issue started as a high-level investigation into pushing many kinds of table operations into the DB, but it's too general. This issue tracks specifically pushing align operations for gauge types into the DB, which is both the easiest kind of operation and also the most valuable (since the environmentals like temperature constitute a huge fraction of the data in the DB now).

bnaecker commented 5 days ago

One of the nice things about doing alignment in the DB directly is that we can pretty easily add new kinds of alignment methods. For example, given a query like:

SELECT
    timeseries_key,
    toStartOfInterval(timestamp, INTERVAL 1 SECOND) AS timestamp,
    avg(datum) AS datum
FROM measurements_f32
WHERE
    (timeseries_name = 'hardware_component:temperature') AND (timeseries_key = 3019922429078673785)
GROUP BY timeseries_key, timestamp

which does the align mean_within(1s) part of an OxQL query, we'd only need to switch the avg(datum) bit to min(datum) to align using the minimum within the time window rather than the mean.

One really nice thing to add on top of this is the ability to derive more than one quantity from a datum. I've avoided doing that so far because it makes the points multi-dimensional, which is complicated. But ignoring that for now, one could imagine aligning with min(datum), max(datum), mean(datum), or any other supported aggregate function. This would be pretty slick, since we can do that all in one DB query.

I do think this might mean we want to change the OxQL syntax for alignment. Right now, we attach the period to the alignment method, like align mean_within(1s). But if we have more than one alignment method, they all have the same period, and so we might want to write that as: align 1s, min, max, mean or something like that.