Skip to main content

8.6 TimescaleDB - priebežná agregácia údajov

V kapitole „8.1 Úvod do TSDB a predstavenie InfluxDB“ sme spomínali podzvorkovanie (downsampling). Mohli by sme si napríklad vytvoriť minútovú agregáciu údajov za posledný deň. Pre tento účel sa používajú pohľady (view), s ktorými sme sa už stretli:

CREATE VIEW ovzdusie_den AS
SELECT
	TIME_BUCKET('1 minute', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkost), 0) AS vlhkost,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdusie
WHERE cas >= TIME_BUCKET('1 minute', NOW()) - INTERVAL '1 day'
GROUP BY miesto, TIME_BUCKET('1 minute', cas)
ORDER BY cas DESC, miesto;

-- vypíšeme prehľad
SELECT * FROM ovzdusie_den;

Každé volanie pohľadu však vždy znovu spustí dopyt v ňom definovaný - a to nie je veľmi efektívne. Navyše by to neriešilo uvoľňovanie nadbytočných (príliš podrobných) starých údajov. Dá sa konštatovať, že tento pohľad je len kozmetický a reálne nič nerieši.

Materializovaný pohľad

Preto si vytvoríme špeciálny pohľad, nazýva sa materializovaný pohľad (z pojmu „materialized view“ v angličtine), čo si môžeme predstaviť ako predpočítanú verziu klasického pohľadu (VIEW), ktorá šetrí výkon tým, že výsledky dopytu fyzicky ukladá na disk:

-- predošlý obyčajný pohľad zmažeme
DROP VIEW IF EXISTS ovzdusie_den;

-- a vytvoríme materializovaný
CREATE MATERIALIZED VIEW ovzdusie_den WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkost), 0) AS vlhkost,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdusie
GROUP BY miesto, TIME_BUCKET('1 minute', cas)
ORDER BY cas DESC, miesto;

Čím sa líši materializovaný pohľad od bežného pohľadu? Na prvý pohľad vidno syntaktické rozdiely:

  • uviedli sme prívlastok „MATERIALIZED“, čo postačuje na vytvorenie štandardného materializovaného pohľadu v PostgreSQL;
  • za názov sme pridali TSDB výraz WITH (tsdb.continuous), čím sme ho povýšili na TimescaleDB pohľad s priebežnou aktualizáciou.

Čím sa však líši z funkčného hľadiska? Keď si zobrazíme tento pohľad ihneď po vytvorení, bude sa správať podľa očakávania - zobrazí nám aktuálne údaje zo zbernej tabuľky. No keď ho spustíme neskôr znovu, všimneme si zvláštne správanie - výsledky tohto pohľadu akosi zostali „primrznuté“ - zobrazujú sa stále tie isté, už neaktuálne:

-- môžeme sledovať 100 najaktuálnejších hodnôt
SELECT * FROM ovzdusie_den LIMIT 100;

Takéto správanie materializovaného pohľadu je v poriadku, môžeme ho chápať ako akúsi „cache“, do ktorej sa raz uložia údaje a zostanú tam. No ako tieto údaje zaktualizujeme?

Priebežná aktualizácia

Materializovaný pohľad potrebujeme pravidelne aktualizovať. Na toto slúžia pravidlá agregácie, ktoré nastavíme funkciou add_continuous_aggregate_policy() - v nej musíme uviesť názov materializovaného pohľadu a nastaviť parametre:

  • schedule_interval - časový interval aktualizácie (ako často sa má pohľad aktualizovať),
    • obvykle zodpovedá granularite dopytu v pohľade;
  • initial_start - čas, od ktorého sa počíta interval (nepovinný),
    • obvykle aktuálny čas „zaokrúhlený“ na interval aktualizácie;
  • start_offset - časový interval začiatku údajov, ktoré sa majú aktualizovať zo zdroja údajov,
    • musí byť aspoň 2-násobok intervalu aktualizácie,
    • môžeme ponechať celý interval plánovaného uchovávania údajov, aby sa zachytili aj „oneskorene vložené“ údaje,
    • NULL znamená, že prejde celý zdroj údajov od najstaršieho;
  • end_offset - časový interval konca údajov, ktoré sa majú aktualizovať zo zdroja údajov,
    • '0' znamená aktuálny čas, pričom ignoruje neukončený interval, čo väčšinou vyhovuje.
SELECT add_continuous_aggregate_policy(
	'ovzdusie_den',
	schedule_interval => INTERVAL '1 minute',
	initial_start => TIME_BUCKET('1 minute', NOW()),
	start_offset => INTERVAL '1 day', -- prípadne NULL
	end_offset => '0'
);

Ak sa pomýlime pri vytváraní pravidiel agregácie, môžeme ich poľahky zmazať:
SELECT remove_continuous_aggregate_policy('ovzdusie_den');

Takto nastavená aktualizácia vždy na prelome minút doplní údaje za práve uplynulú minútu. To však prináša nový problém - údaje sa stále pridávajú, ale žiadne neodbúdajú! Môžeme sa o tom presvedčiť:

SELECT COUNT(*) FROM ovzdusie_den;

Automatická aktualizácia údajov cez pravidlá agregácie neodstraňuje staré údaje!

Údaje sa budú každú minútu stále znovu prepisovať zo zdroja za posledný deň?

Nie, server si značí, ktoré údaje sú nové a pridá len tie. Preto dlhší interval v start_offset nepredstavuje zdržiavanie, a to ani pri hodnote NULL.

Priebežné mazanie starých údajov

Pre automatické mazanie starých údajov musíme definovať pravidlá mazania, ktoré nastavíme funkciou add_retention_policy() - v nej musíme opäť uviesť názov materializovaného pohľadu (alebo zdrojovej hypertabuľky) a nastaviť parametre:

  • drop_after - interval uchovávania údajov;
  • schedule_interval - časový interval mazania (ako často sa má mazanie vykonať),
    • postačuje aj násobne dlhší interval oproti aktualizácii;
  • initial_start - čas, od ktorého sa počíta interval (nepovinný),
    • obvykle aktuálny čas „zaokrúhlený“ na interval mazania.
SELECT add_retention_policy(
	'ovzdusie_den',
	drop_after => INTERVAL '1 day',
	schedule_interval => INTERVAL '1 hour',
	initial_start => TIME_BUCKET('1 hour', NOW())
);

Aj pravidlá mazania môžeme zmazať: SELECT remove_retention_policy('ovzdusie_den');

Teraz môžeme skontrolovať stav monitorovania - aktuálne počty údajov:

SELECT COUNT(*) FROM ovzdusie_den;

SELECT
	miesto,
	COUNT(teplota) AS teploty,
	COUNT(vlhkost) AS vlhkosti,
	COUNT(tlak) AS tlaky,
	COUNT(vzduch) AS vzduchy,
	COUNT(co2) AS CO2ky,
	COUNT(*) AS SPOLU
FROM ovzdusie_den
GROUP BY miesto
ORDER BY miesto;

Je pekné, že mažeme staré údaje v materializovanom pohľade, ale naša zberná tabuľka sa stále plní. Rovnakým spôsobom však môžeme nastaviť automatické mazanie starých údajov aj pre ňu.

Nastavil som mazanie údajov, no staré údaje nemiznú, počet zostáva rovnaký. V čom je problém?

Spomeňme si na organizovanie údajov v TimescaleDB do blokov (chunks). Predvolene je tabuľka rozdelená na bloky po 7 dňoch. Databáza z dôvodu extrémneho výkonu nemaže jednotlivé riadky, ale maže (zahadzuje) celé bloky naraz. Blok však môže zahodiť až vtedy, keď sú úplne všetky údaje v ňom staršie ako náš nastavený limit. Preto sa môže stať, že hoci požadujeme mazať po 1 dni, dáta reálne zmiznú až po tom, čo „zostarne“ celý 7-dňový blok. V produkcii sa preto veľkosť blokov prispôsobuje pravidlám mazania. Ak nastavíme blok na 1 deň a mažeme po každom dni, reálne zostanú dostupné údaje z aktuálneho dňa + celého predošlého dňa.

Praktický príklad

V našom scenári s monitorovaním ovzdušia by sme pre každé miesto mohli požadovať prehľady údajov:

  • ovzdusie: zberná tabuľka, všetky údaje (nanajvýš každých 5 sekúnd) za posledných 24 hodín (do 17280 hodnôt, reálne cca 5760 hodnôt);
  • ovzdusie_tyzden: pohľad na posledný týždeň v 1-minútových súhrnoch (10080 hodnôt);
  • ovzdusie_mesiac: pohľad na posledný mesiac v 5-minútových súhrnoch (8640 hodnôt);
  • ovzdusie_rok: pohľad na posledný rok v 1-hodinových súhrnoch (8760 hodnôt);
  • ovzdusie_25rok: pohľad na posledných 25 rokov v denných súhrnoch (9131 hodnôt).

Prehľady pre jednotlivé časové obdobia

Chceli by sme však poznať nielen priemery hodnôt v daných časových oknách, ale aj najmenšie a najväčšie hodnoty (toto podrobne rozoberieme nižšie). Vytvoríme si teda jednotlivé materializované pohľady a nastavíme im pravidlá agregácie a mazania.

Prvý prehľad (týždňový) bude brať údaje priamo zo zdrojovej tabuľky:

CREATE MATERIALIZED VIEW ovzdusie_tyzden WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	MIN(teplota) AS teplota_min,
	MAX(teplota) AS teplota_max,
	ROUND(AVG(vlhkost), 2) AS vlhkost,
	MIN(vlhkost) AS vlhkost_min,
	MAX(vlhkost) AS vlhkost_max,
	ROUND(AVG(tlak), 4) AS tlak,
	MIN(tlak) AS tlak_min,
	MAX(tlak) AS tlak_max,
	ROUND(AVG(vzduch), 2) AS vzduch,
	MIN(vzduch) AS vzduch_min,
	MAX(vzduch) AS vzduch_max,
	ROUND(AVG(co2), 2) AS co2,
	MIN(co2) AS co2_min,
	MAX(co2) AS co2_max
FROM monitorovanie.ovzdusie
GROUP BY miesto, TIME_BUCKET('1 minute', cas)
ORDER BY cas DESC, miesto;

SELECT add_continuous_aggregate_policy(
	'ovzdusie_tyzden',
	schedule_interval => INTERVAL '1 minute',
	initial_start => TIME_BUCKET('1 minute', NOW()),
	start_offset => INTERVAL '1 week',
	end_offset => '0'
);

SELECT add_retention_policy(
	'ovzdusie_tyzden',
	drop_after => INTERVAL '1 week',
	schedule_interval => INTERVAL '1 day',
	initial_start => TIME_BUCKET('1 day', NOW()) + INTERVAL '30 seconds'
);

Všimnite si odloženie mazania o 30 sekúnd - cieľom je, aby sa nediali všetky operácie naraz.

Druhý prehľad (mesačný) však už nebude brať údaje zo zbernej tabuľky, ale z denného prehľadu:

CREATE MATERIALIZED VIEW ovzdusie_mesiac WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('5 minutes', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	MIN(teplota_min) AS teplota_min,
	MAX(teplota_max) AS teplota_max,
	ROUND(AVG(vlhkost), 2) AS vlhkost,
	MIN(vlhkost_min) AS vlhkost_min,
	MAX(vlhkost_max) AS vlhkost_max,
	ROUND(AVG(tlak), 4) AS tlak,
	MIN(tlak_min) AS tlak_min,
	MAX(tlak_max) AS tlak_max,
	ROUND(AVG(vzduch), 2) AS vzduch,
	MIN(vzduch_min) AS vzduch_min,
	MAX(vzduch_max) AS vzduch_max,
	ROUND(AVG(co2), 2) AS co2,
	MIN(co2_min) AS co2_min,
	MAX(co2_max) AS co2_max
FROM ovzdusie_tyzden
GROUP BY miesto, TIME_BUCKET('5 minutes', cas)
ORDER BY cas DESC, miesto;

SELECT add_continuous_aggregate_policy(
	'ovzdusie_mesiac',
	schedule_interval => INTERVAL '5 minutes',
	initial_start => TIME_BUCKET('5 minutes', NOW()) + INTERVAL '5 seconds',
	start_offset => INTERVAL '1 month',
	end_offset => '0'
);

SELECT add_retention_policy(
	'ovzdusie_mesiac',
	drop_after => INTERVAL '1 month',
	schedule_interval => INTERVAL '1 day',
	initial_start => TIME_BUCKET('1 day', NOW()) + INTERVAL '35 seconds'
);

Podobne vytvoríme prehľad ročný (z mesačného) a 25-ročný (z ročného) - to už iste zvládne každý aj sám.

Môžeme sa pozrieť na štatistiky, postupne sa budú plniť údajmi:

SELECT '0: ovzdušie' AS tabuľka, COUNT(*) AS riadkov, COUNT(DISTINCT miesto) AS miest, MIN(cas) AS od FROM monitorovanie.ovzdusie
UNION SELECT '1: týžden po minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_tyzden
UNION SELECT '2: mesiac po 5 minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_mesiac
UNION SELECT '3: rok po hodinách', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_rok
UNION SELECT '4: 25 rokov po dňoch', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_25rok
ORDER BY tabuľka;

Na záver by sme mali nastaviť aj pravidlá mazania samotnej zdrojovej tabuľky:

SELECT add_retention_policy(
	'ovzdusie',
	drop_after => INTERVAL '1 day',
	schedule_interval => INTERVAL '1 day',
	initial_start => TIME_BUCKET('1 day', NOW())
);

Minimálne a maximálne hodnoty

...

Back to top