I. Wyjasnienie
Błędem z mojej strony było podawanie maksymalnych możliwych zakresów. W swoim 1 poście uprościłem zagadnienie, ponieważ mocno wierzyłem że moje marne doświadczenia w zarządzaniu indexami na kolumnach jsonb jest tutaj problemem.
Zacznijmy od początku i z większą ilością szczegółów.
Żeby uniknąć posługiwania się specjalistycznymi / technicznymi pojeciami przedstawie projekt w formie projektu medycznego aby nazwy kluczy JSON były powszechnie zrozumiałe. Polskie nazwy parametrow/kluczy mieszaja sie z angielskimi. Jest to celowe i tylko na potrzeby tego wpisu.
II. Dodawanie danych
-
Do systemu trafiają dane z urządzeń pomiarowych i zachowywane sa w tabeli
-
Po każdym dniu pomiarowym są przetwarzane i otrzymujemy:
- patient_id
- data_wykonania_obliczen
- 180 kolumn JSONB (wcześniej pisałem o 250 bo zalozylem mozliwość rozbudowy). Nie tworzylem jeszcze tych 180 kolumn tylko dane z wszysktich 180 kolumn wrzucilem do jednej kolumny JSONB, zeby zobaczyc ile zajmuja dane.
data_wykonania_obliczen - to dzien, nie ma znaczenia dokladna godzina. Myslalem aby w celu zwiekszenia wydajnosci daty slownikowac. np 2000-01-01 -> 1.
III. Skąd wzieło mi się 20TB?
1 dnia wpadaja dane 1000 pacjentów (z bazy ponad 25k)
1 rekord opisany powyzej zajmuje srednio 20MB (lekko zawyzone dla bezpieczenstwa)
Dane z 1 dnia zajmuja 20GB
Dane z 1 miesiaca 600 GB
Dane z 1 roku 7TB
Dane z wymaganego okresu zajmuja 21 TB
Jeszcze tutaj doprecyzuje, zalezalo mi na tym aby dane z 1 dnia (wyjasnienie pozniej) pobraly sie z bazy w <0.5s.
IV. Przykladowa struktura JSONB dla pojedynczej kolumny
Aktualizacja: Wcześniej pisałem o 3 kolumnach int uzywanych w klauzuli WHERE zapytan SELECT. Mialem na mysli ze beda nimi 3 pierwsze poziomy ponizszej struktury JSON tj: period->place->activity.
JSONB dla kolumny AKTYWNOSC_KOMOREK_X001:
{
"obecny_okres": { <-- period
"w_domu": { <-- place
"podczas_aktywnosci": { <-- activity
"1": { <-- days - ilosc dni pomiarowych ( dla wartosci 4 -> dane z ostatnich 4 dni pomiarowych) MAX 100. Nie każdy dzien w swiecie rzeczywstym to dzien pomiarowy
"0.1": { <-- wartość FLOAT_VALUE - kazda kolumna ma swoj zestaw wartosci FLOAT_VALUE
"o": 1, <-- dni powyzej wartosci FLOAT_VALUE
"u": 0, <-- dni ponizej wartosci FLOAT_VALUE
"e": 0 <-- dni z dokladnie wartosc FLOAT_VALUE
},
"1": {
"o": 1,
"u": 0,
"e": 0
},
"1.4": {
"o": 0,
"u": 1,
"e": 0
},
... i tak max 60
},
"2": {
"0.1": {
"o": 1,
"u": 0,
"e": 1
},
"1": {
"o": 1,
"u": 0,
"e": 1
},
"1.4": {
"o": 0,
"u": 2,
"e": 0
},
... i tak max 60
},
}, <-- END podczas_aktywnosci
"podczas_odpoczynku": {
taka sama struktura jak podczas_aktywnosci
},
"podczas_snu": {
taka sama struktura jak podczas_aktywnosci
},
}, <-- END w_domu
"poza_domem": {
taka sama struktura jak w_domu
},
"gdziekolwiek": {
taka sama struktura jak w_domu
},
.....
},
"wszystkie_okresy": {
tutaj jest ta sama struktura ktora widnieje w obecny_okres
}
System zaklada istnienie innych wzorcow JSON ale zmiany jesli bedziemy tylko pobierac dane beda malo istotne. Ponizej lista modyfikacji we wzorcach:
- np. w kolumnie AKTYWNOSC_KOMOREK_AVG001 trzymane sa srednie. Sciezka wiec wyglada tak: AKTYWNOSC_KOMOREK_AVG001 -> period -> place -> activity -> days -> WARTOSC AVG FLOAT.
- zamiast o/u/e w niektorzych strukturach JSONB moze wystepowac y/n (yes/no)
INFO: To logiczne, ale dla jasności inny format JSONB moze byc tylko w innej kolumnie w ktorym jest tylko ten inny format.
Pseudo mapa:
PERIOD
obecny_okres
wszystkie_okresy
PLACE
w_domu
poza_domem
gdziekolwiek
ACTIVITY
podczas_aktywnosci
podczas_odpoczynku
podczas_snu
DAYS - MAX 100, w obecny_okres jest ich mniej, ok 30-40. W wszystkie_okresy srednio 55.
FLOAT_VALUE - MAX 60. W innych wzorcach JSON moga byc inne wartosci / nazwy kluczy FLOAT_VALUE ale nie bedzie ich wiecej niz 60 w kazdym DAY
O
U
E
period -> place -> activity zawsze mamy takie same. Ewentualnie ktoregos obiektu moze nie byc w jakims polu gdyz np. nie bylo danych.
V. Jak korzystamy z danych.
Dane JSONB uzywane sa do kolejnych operacji lub do filtrowania.
Przyklad jak to powinno funkcjonowac:
-
z kilku kolumn JSONB pobieramy potrzebne dane dla wybranych pacjentow (patient_id) gdzie "data_wykonania_obliczen" miesci sie w załozonym okresie.
Komentarz: Chcac miec obraz pacjenta na dzien 2000-01-10 szukamy obliczen ktore byly wykonane najblizej tej daty w przeszlosci, ale nie sa starsze niz np X dni. Jak nie ma nic z X dni to nas taki pacjent nie intresuje. Ilosc rekordow spelniajacych warunek bedzie wynosila 1-2k
-
przekazujemy dane do workerow ktore cos z tymi danymi robia
Komentarz: Najczesciej wymaganie jest takie aby przekazac do workerow dane z ostatnich 10-12 miesiecy.
Myslalem wiec aby przekazywac do workerow dzien po dniu lub jakies wieksze partie, np z calego tygodnia czy miesiaca w zaleznosci od wydajnosci.
Kolejne elementy systemu musza miec mozliwosc przetwarzania zbiorow danych z okreslonego data_wykonania_obliczen wiec takie przekazywanie danych dzien po dniu ma sens.
W innym przyadku nalezaloby modyfikowac zapytania i na poziomie bazy danych filtrowac wartosci. Bardziej skomplikowane filtrowanie / operacje na danych chce przerzucic na inne serwery, a baze chce miec tylko do "wydania danych" do workerow.
Najczesciej potrzeba pobrac z jednego pola JSONB 4 wartosci ktorych pierwsze 5 elementow sciezki sa takie same:
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 0.1 -> o
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 0.1 -> e
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 1.4 -> u
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 1.4 -> e
VI. Przykład z dokładnym opisem tego co zamierzano osiągnąć
SELECT
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 0.1 -> o
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 0.1 -> e
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 1.4 -> u
AKTYWNOSC_KOMOREK_X001 -> obecny_okres -> w_domu -> podczas_snu -> 2 -> 1.4 -> e
chcemy wiedziec ile razy w okresie 2 dni pomiarowych od daty 2000-01-10 (klauzula WHERE) odnotowano pomiary >(o) lub =(e) od FLOAT_VALUE = 0.1
oraz ile razy w okresie 2 dni odnotowano pomiary <(u) lub =(e) od FLOAT_VALUE = 1.4
AKTYWNOSC_KOMOREK_X025 -> obecny_okres -> gdziekolwiek -> podczas_snu -> 10 -> 3 -> o
AKTYWNOSC_KOMOREK_X025 -> obecny_okres -> gdziekolwiek -> podczas_snu -> 10 -> 8 -> u
chcemy wiedziec ile razy w okresie 10 dni pomiarowych od daty 2000-01-10 (klauzula WHERE) odnotowano pomiary >(o) od FLOAT_VALUE = 3
oraz ile razy w okresie 10 dni odnotowano pomiary <(u) od FLOAT_VALUE = 8
WHERE
data_wykonania_obliczen = 453 AND (tutaj dacie odpowiada wartosc ze slownika zeby szybciej przetwarzac)
patient_id IN (id,id,id)
ORDER BY data_wykonania_obliczen DESC <-- to zapytanie tego nie potrzebuje ale dodalem aby pamietac ze ta kolumna bedzie przeprowadzac taka operacje
Slownik dat:
453 -> 2000-01-10
460 -> 2000-01-17
Uwagi:
-
robiac SELECT zawsze mamy patient_id(lub array z id), konkretna data_wykonania_obliczen lub zakres
-
w powyzszym zapytaniu oczywiscie trzeba zrobic aby w przypadku WHERE data_wykonania_obliczen >= 453 AND data_wykonania_obliczen <= 460 pobieralo dane pacjenta tylko te najblizsze daty 2000-01-17. Jesli w przedziale bedzie wiecej rekordow (patient_id, data_wykonania_obliczen) to bierzemy tylko ten najnowszy.
-
dane pobierane sa najczesciej z nie wiecej niz 10 roznych kolumn JSONB (np z kolumn AKTYWNOSC_KOMOREK_X001, AKTYWNOSC_KOMOREK_X022, AKTYWNOSC_KOMOREK_X109 ... )
VII. Wczoraj wspominales o slownikowaniu + trzymaniu danych w formacie
int_col
int_col
level1
level2
...
value
I teraz policzmy:
period (2)
place (3)
activity (3)
days (50) - srednia dla obu period
FLOAT_VALUE (300) - jest ich po max 60 na jeden DAY ale w roznych wzorcach JSON moga byc inne wartosic klucza wiec przyjmijmy ze mamy ich roznych 300
o/u/e/y/n (5)
1.35mln rekordow w slowniku o ile czegos nie pominalem
Dla proponowanego formatu przy insercie tylko jednego zestawu danych(pacjent|data_obliczen|wszystkie_powiazania) do tabeli to jak dobrze licze masz 162 tys rekordow.
Dziennie wpada takich zestawow danych 1000 wiec mamy 162mln dziennie, 5mld miesiecznie, 60mld rocznie a musimy przechowyawc 3lata danych. Chyba ze Ciebie zle zrozumialem co do formatu przechowywania danych.