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 ovzdušie_deň AS
SELECT
	TIME_BUCKET('1 minute', čas) AS čas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkosť), 0) AS vlhkosť,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdušie
GROUP BY miesto, TIME_BUCKET('1 minute', čas)
ORDER BY čas DESC, miesto;

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:

DROP VIEW IF EXISTS ovzdušie_deň;

CREATE MATERIALIZED VIEW ovzdušie_deň WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', čas) AS čas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkosť), 0) AS vlhkosť,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdušie
GROUP BY miesto, TIME_BUCKET('1 minute', čas)
ORDER BY čas 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í (SELECT * FROM ovzdušie_deň LIMIT 10), 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.

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(
	'ovzdušie_deň',
	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('ovzdušie_deň');

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 ovzdušie_deň

Ú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 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(
	'ovzdušie_deň',
	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('ovzdušie_deň');

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.

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:

  • ovzdušie: 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);
  • ovzdušie_týždeň: pohľad na posledný týždeň v 1-minútových súhrnoch (10080 hodnôt);
  • ovzdušie_mesiac: pohľad na posledný mesiac v 5-minútových súhrnoch (8640 hodnôt);
  • ovzdušie_rok: pohľad na posledný rok v 1-hodinových súhrnoch (8760 hodnôt);
  • ovzdušie_25rok: pohľad na posledných 25 rokov v denných súhrnoch (9131 hodnôt).

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. Vytvoríme si teda jednotlivé materializované pohľady a nastavíme im pravidlá agregácie a mazania. Prvý pohľad (týždňový) bude brať údaje priamo zo zdrojovej tabuľky:

CREATE MATERIALIZED VIEW ovzdušie_týždeň WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', čas) AS čas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	MIN(teplota) AS teplota_min,
	MAX(teplota) AS teplota_max,
	ROUND(AVG(vlhkosť), 2) AS vlhkosť,
	MIN(vlhkosť) AS vlhkosť_min,
	MAX(vlhkosť) AS vlhkosť_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.ovzdušie
GROUP BY miesto, TIME_BUCKET('1 minute', čas)
ORDER BY čas DESC, miesto;

SELECT add_continuous_aggregate_policy(
	'ovzdušie_týždeň',
	schedule_interval => INTERVAL '1 minute',
	initial_start => TIME_BUCKET('1 minute', NOW()),
	start_offset => INTERVAL '1 week',
	end_offset => '0'
);

SELECT add_retention_policy(
	'ovzdušie_týždeň',
	drop_after => INTERVAL '1 week',
	schedule_interval => INTERVAL '1 hour',
	initial_start => TIME_BUCKET('1 hour', NOW())
);

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

CREATE MATERIALIZED VIEW ovzdušie_mesiac WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('5 minutes', čas) AS čas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	MIN(teplota_min) AS teplota_min,
	MAX(teplota_max) AS teplota_max,
	ROUND(AVG(vlhkosť), 2) AS vlhkosť,
	MIN(vlhkosť_min) AS vlhkosť_min,
	MAX(vlhkosť_max) AS vlhkosť_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 ovzdušie_týždeň
GROUP BY miesto, TIME_BUCKET('5 minutes', čas)
ORDER BY čas DESC, miesto;

SELECT add_continuous_aggregate_policy(
	'ovzdušie_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(
	'ovzdušie_mesiac',
	drop_after => INTERVAL '1 month',
	schedule_interval => INTERVAL '4 hours',
	initial_start => TIME_BUCKET('4 hours', NOW()) + INTERVAL '7 seconds'
);

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

Podobne vytvoríme pohľad ročný (z mesačného) a 25-ročný (z ročného). Na záver by sme mali nastaviť aj pravidlá mazania samotnej zdrojovej tabuľky:

SELECT add_retention_policy(
	'ovzdušie',
	drop_after => INTERVAL '1 day',
	schedule_interval => INTERVAL '15 minutes',
	initial_start => TIME_BUCKET('15 minutes', NOW())
);

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

SELECT '0: ovzdušie' AS tabuľka, COUNT(*) AS riadkov, COUNT(DISTINCT miesto) AS miest, MIN(čas) AS od FROM monitorovanie.ovzdušie
UNION SELECT '1: týždeň po minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(čas) FROM ovzdušie_týždeň
UNION SELECT '2: mesiac po 5 minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(čas) FROM ovzdušie_mesiac
UNION SELECT '3: rok po hodinách', COUNT(*), COUNT(DISTINCT miesto), MIN(čas) FROM ovzdušie_rok
UNION SELECT '4: 25 rokov po dňoch', COUNT(*), COUNT(DISTINCT miesto), MIN(čas) FROM ovzdušie_25rok
ORDER BY tabuľka;
Back to top