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ňovzdusie_den AS
SELECT
	TIME_BUCKET('1 minute', čas)cas) AS čas,cas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkosť)vlhkost), 0) AS vlhkosť,vlhkost,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdušieovzdusie
WHERE cas >= TIME_BUCKET('1 minute', NOW()) - INTERVAL '1 day'
GROUP BY miesto, TIME_BUCKET('1 minute', čas)cas)
ORDER BY čascas 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 ovzdušie_deň;ovzdusie_den;

-- a vytvoríme materializovaný
CREATE MATERIALIZED VIEW ovzdušie_deňovzdusie_den WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', čas)cas) AS čas,cas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkosť)vlhkost), 0) AS vlhkosť,vlhkost,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdušieovzdusie
GROUP BY miesto, TIME_BUCKET('1 minute', čas)cas)
ORDER BY čascas 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),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.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(
	'ovzdušie_deň'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('ovzdušie_deň'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 ovzdušie_deň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(
	'ovzdušie_deň'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('ovzdušie_deň'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:

  • ovzdušie: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);
  • ovzdušie_týždeň:ovzdusie_tyzden: pohľad na posledný týždeň v 1-minútových súhrnoch (10080 hodnôt);
  • ovzdušie_mesiac:ovzdusie_mesiac: pohľad na posledný mesiac v 5-minútových súhrnoch (8640 hodnôt);
  • ovzdušie_rok:ovzdusie_rok: pohľad na posledný rok v 1-hodinových súhrnoch (8760 hodnôt);
  • ovzdušie_25rok: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.hodnoty (toto podrobne rozoberieme nižšie). Vytvoríme si teda jednotlivé materializované pohľady a nastavíme im pravidlá agregácie a mazania.

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

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

SELECT add_continuous_aggregate_policy(
	'ovzdušie_týždeň'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(
	'ovzdušie_týždeň'ovzdusie_tyzden',
	drop_after => INTERVAL '1 week',
	schedule_interval => INTERVAL '1 hour'day',
	initial_start => TIME_BUCKET('1 hour'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ý pohľadprehľad (mesačný) však už nebude brať údaje zo zbernej tabuľky, ale z denného pohľadu:prehľadu:

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

SELECT add_continuous_aggregate_policy(
	'ovzdušie_mesiac'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(
	'ovzdušie_mesiac'ovzdusie_mesiac',
	drop_after => INTERVAL '1 month',
	schedule_interval => INTERVAL '41 hours'day',
	initial_start => TIME_BUCKET('41 hours'day', NOW()) + INTERVAL '735 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ľadprehľad ročný (z mesačného) a 25-ročný (z ročného). Na- záverto byuž smeiste malizvládne nastaviťkaždý aj pravidlá mazania samotnej zdrojovej tabuľky:sám.

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)cas) AS od FROM monitorovanie.ovzdušieovzdusie
UNION SELECT '1: týždeňtýžden po minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(čas)cas) FROM ovzdušie_týždeňovzdusie_tyzden
UNION SELECT '2: mesiac po 5 minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(čas)cas) FROM ovzdušie_mesiacovzdusie_mesiac
UNION SELECT '3: rok po hodinách', COUNT(*), COUNT(DISTINCT miesto), MIN(čas)cas) FROM ovzdušie_rokovzdusie_rok
UNION SELECT '4: 25 rokov po dňoch', COUNT(*), COUNT(DISTINCT miesto), MIN(čas)cas) FROM ovzdušie_25rokovzdusie_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