En serie hendelser i tid

Publisert 03.05.2023

Hvorfor lære seg et nytt spørrespråk og innføre en umoden tidsserie-database bare fordi du har tidsseriedata?

I motsetning til andre, og av en eller annen grunn mer populære tidsserie-databaser, som f.eks. InfluxDB, har ikke TimescaleDB funnet opp sitt eget spørrespråk. TimescaleDB er en extension til PostgreSQL som gir god støtte og ytelse for tidsseriedata. Dette betyr at den er bygget på et solid fundament, og at du ikke trenger å lære deg et helt nytt spørrespråk og paradigme. SQL og alle databaseverktøy du bruker i dag kan du også bruke på dine tidsserie-data. TimescaleDB tilbys som en skytjeneste, eller du kan deploye selv på en rekke forskjellige måter, f.eks. i Docker.

¶Tidsseriedata

Tidsseriedata har noen typiske karakteristikker som skiller det fra “vanlig CRUD”. Som navnet antyder er tid et viktig konsept - alle dataene har et tidsstempel. Ofte gir ikke enkelt-målinger i seg selv så mye verdi, man er heller interessert i å se trender over tid. Man legger alltid til nye data (append only). Det er sjeldent man endrer eller legger til ny data tilbake i tid eller sletter enkelt-målinger. Ferske rå-data er mer verdifulle - eldre data kan man downsample og slette for å spare plass og bedre ytelsen. Typiske eksempler på tidsseriedata kan være metrics, værdata, aksjekurser eller strømforbruk.

¶Strømforbruk

Vi har de siste årene fått et nærmere forhold til strømpriser og -forbruk, så vi kan bruke det som eksempel videre. De aller fleste av oss - med noen få hederlige unntak blant medlemsmassen i Folkets Strålevern - har fått installert en “smart” strømmåler hjemme. Ved å koble til en dingseboms i HAN-porten på måleren kan vi få ut data om strømforbruket vårt ganske hyppig. En melding kan f.eks. se slik ut:

{"ListId":"AIDON_V0001",
 "Power":3.7509999999999999,
 "PowerExport":0.0,
 "ReactivePower":0.16700000000000001,
 "ReactivePowerExport":0.0,
 "CurrentL1":8.0,
 "CurrentL3":10.100000000000001,
 "VoltageL1":237.0,
 "VoltageL2":237.20000000000002,
 "VoltageL3":238.10000000000002}

Power er effekten, altså hvor mange kW som forbrukes akkurat når målingen tas.

¶Hello world

Hvis vi vil lagre dette som tidsserie-data kan vi lage en enkel tabell

CREATE TABLE IF NOT EXISTS smart_meter_power(
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
power double PRECISION NULL);

Foreløpig er det en helt ordinær PostgreSQL-tabell. Kjernen i TimescaleDB er hypertable, som lages ved å kjøre create_hypertable på en vanlig tabell:

SELECT create_hypertable('smart_meter_power','time');

Dette ser litt rart ut, og er en konsekvens av at man har valgt å ikke finne opp sitt eget spørrespråk, men heller bruke SQL. Så det som sies her er altså at tabellen smart_meter_power skal gjøres om til en hypertable, og feltet som inneholder tiden er time. Da gjør Timescale noen smarte ting som hensyntar karakteristikkene ved tidsserie-data. Spesifikt så lagres data i “chunks”, slik at data partisjoneres på tid.

Tabellen oppfører seg som en helt ordinær tabell, og gjør man en select på denne så får man ut alle målingene som er registert i databasen - akkurat som forventet.

SELECT * from smart_meter_power;

        time         | power
---------------------+--------
 2023-04-22 03:35:41 |  4.914
 2023-04-22 03:35:42 |  4.922
 2023-04-22 03:35:45 |  4.925
 2023-04-22 03:35:47 |  4.916
 2023-04-22 03:35:51 |  4.914
 2023-04-22 03:35:52 |  4.913
 2023-04-22 03:35:55 |  4.924
 2023-04-22 03:35:57 |   4.92
 2023-04-22 03:36:01 |  4.929
 2023-04-22 03:36:02 |  4.922
 2023-04-22 03:36:05 |  4.915
 2023-04-22 03:36:07 |  4.914
 2023-04-22 03:36:11 |  4.914

¶Time buckets

Man betaler imidlertid ikke for strømforbruket akkurat nå, men for hvor mange kWh man har brukt en klokketime. Og gjennomsnittet av alle samplingene for hver enkelt time skulle tilsvare ca kWh forbruket den timen. Timescale har et begrep som heter time-bucket. Disse er intervaller med målinger som vi kan utføre forskjellige matematiske funksjoner på.

SELECT
time_bucket('1 hour', time) AS hour,
avg(power) as estimated_kwh
FROM smart_meter_power
GROUP BY hour;

Her lages timebuckets på en time, og så beregnes gjennomsnittet av alle målingene i hver av dem. Hver rad i resultatet tilsvarer (ca) kWh-forbruket for den gitte timen:

        hour         |  estimated_kwh
---------------------+------------------
 2023-04-22 03:00:00 | 4.50787607573151
 2023-04-22 04:00:00 | 4.95415625000001
 2023-04-22 05:00:00 | 3.68524166666666
 2023-04-22 06:00:00 |  4.1403763888889
 2023-04-22 07:00:00 | 6.03381736111112
 2023-04-22 08:00:00 | 3.61466736111112
 2023-04-22 09:00:00 | 3.99170535093815
 2023-04-22 10:00:00 | 3.79229166666667
 2023-04-22 11:00:00 | 3.34735694444444
 2023-04-22 12:00:00 | 2.60874097222223
 2023-04-22 13:00:00 | 3.52494305555556
 2023-04-22 14:00:00 | 3.46674652777778
 2023-04-22 15:00:00 | 2.67269930555556
 2023-04-22 16:00:00 | 3.78137986111111
 2023-04-22 17:00:00 | 3.39719236111111
 2023-04-22 18:00:00 | 4.23978387769284
 2023-04-22 19:00:00 | 4.15920347222222
 2023-04-22 20:00:00 | 4.13142638888889
 2023-04-22 21:00:00 | 4.37184305555555
 2023-04-22 22:00:00 | 4.07072291666667
 2023-04-22 23:00:00 | 3.73996805555555
 2023-04-23 00:00:00 |  3.8366935371786
 2023-04-23 01:00:00 | 5.08253541666667
 2023-04-23 02:00:00 |  5.0007894371091
 2023-04-23 03:00:00 | 3.71155347222221
 2023-04-23 04:00:00 | 5.00301805555556
 2023-04-23 05:00:00 | 4.44710763888889
 2023-04-23 06:00:00 | 4.33788055555555
 2023-04-23 07:00:00 | 4.75782569444445

¶Retention

Det er ikke så interessant å vite effekten kl 03:12:53 den andre torsdagen i forrige måned. Så gamle målinger kan slettes. Ved å sette en retention policy på tabellen sørger Timescale automatisk for å slette gamle data på en effektiv måte. Som nevnt så lager Timescale data i chunks. En chunk har data for et gitt tidsintervall. Så når en chunk faller utenfor den fastsatte retention policyen så slettes hele chunken. Dermed kan Timescale slette gammel data mer effektivt enn ved å kjøre delete-statements på en tabell.

SELECT add_retention_policy('smart_meter_power', INTERVAL '7 days');

Her sier jeg altså at jeg vil at målingene skal tas vare på i 7 dager.

¶Downsampling

Men vi ønsker jo å vite estimated_kwh som er eldre enn en uke. Og spørringen mot time-buckets over er ikke så mye verdt etter at dataene spørringen kjører mot har blitt slettet.

Heldigvis finnes det noe som heter materialized view i Postgres, som er akkurat som et view, bare at resultatet av spørringen i viewet persisteres. Slik at spørringen ikke kjøres hver gang.

CREATE MATERIALIZED VIEW hourly_average_power WITH (timescaledb.continuous)
    AS SELECT time_bucket('1 hour', time) as hour,
              avg(power) as estimated_kwh
       FROM smart_meter_power
       GROUP BY hour;

Så her lages altså et materialized view av forrige spørring. Og TimescaleDB sørger for at viewet oppdateres automatisk når det kommer inn nye data. Dette løser to problemer - for det første så vil dataene i viewet leve etter at rådataene er slettet. Og, siden resultatet av spørringen er persistert, så går det mye raskere å gjøre spørringer på dette viewet enn å kjøre spørringen direkte på rådataene hver gang.

SELECT * from hourly_average_power;

        hour         |  estimated_kwh
---------------------+------------------
 2023-04-22 03:00:00 | 4.50787607573151
 2023-04-22 04:00:00 | 4.95415625000001
 2023-04-22 05:00:00 | 3.68524166666666
 2023-04-22 06:00:00 |  4.1403763888889
 2023-04-22 07:00:00 | 6.03381736111112
 2023-04-22 08:00:00 | 3.61466736111112
 2023-04-22 09:00:00 | 3.99170535093815
 2023-04-22 10:00:00 | 3.79229166666667
 2023-04-22 11:00:00 | 3.34735694444444
 2023-04-22 12:00:00 | 2.60874097222223
 2023-04-22 13:00:00 | 3.52494305555556
 2023-04-22 14:00:00 | 3.46674652777778
 2023-04-22 15:00:00 | 2.67269930555556
 2023-04-22 16:00:00 | 3.78137986111111
 2023-04-22 17:00:00 | 3.39719236111111
 2023-04-22 18:00:00 | 4.23978387769284
 2023-04-22 19:00:00 | 4.15920347222222
 2023-04-22 20:00:00 | 4.13142638888889
 2023-04-22 21:00:00 | 4.37184305555555
 2023-04-22 22:00:00 | 4.07072291666667
 2023-04-22 23:00:00 | 3.73996805555555
 2023-04-23 00:00:00 |  3.8366935371786
 2023-04-23 01:00:00 | 5.08253541666667
 2023-04-23 02:00:00 |  5.0007894371091
 2023-04-23 03:00:00 | 3.71155347222221
 2023-04-23 04:00:00 | 5.00301805555556
 2023-04-23 05:00:00 | 4.44710763888889
 2023-04-23 06:00:00 | 4.33788055555555
 2023-04-23 07:00:00 | 4.75782569444445

¶Oppsummering

Vi har her bare skrapet overflaten av hva TimescaleDB har å by på. Det finnes mange funksjoner for å finne andre statistiske verdier som f.eks. median, maks og min. Eller å beregne delta for forskjellige tidsintervaller for en aggregert verdi, for å nevne noe.

Men det er iallefall en liten introduksjon, som kanskje får deg til å vurdere om det finnes andre alternativer enn en rendyrket tidsseriedatabase. TimescaleDB(/PostgreSQL) støtter nemlig “vanlig CRUD” også.