Sukcesywnie zaprojektowaliśmy strukturę naszej bazy PostgreSQL do przechowywania korespondencji, minął rok, użytkownicy intensywnie ją zapełniają, a w niej już miliony rekordów, i... coś zaczęło zwalniać.
- Część 2: sekcjonowanie "na żywo"
Chodzi o to, że wraz ze wzrostem objętości tabeli rośnie także "głębokość" indeksów — choć w sposób logarytmiczny. Ale z czasem zmusza to serwer do przetwarzania znacznie większej ilości stron danych niż na początku.Tu z pomocą przychodzi
sekcjonowanie Zaznaczam, że mowa będzie nie o sharding, czyli rozdzielaniu danych między różne bazy lub serwery. Bo nawet, jeśli podzielisz dane na.
kilka serwerów, nie pozbędziesz się problemu "rozrostu" indeksów z czasem. Oczywiste jest, że jeśli możesz sobie pozwolić na codzienne wprowadzanie nowego serwera, to twoje problemy będą leżeć w zupełnie innej płaszczyźnie niż konkretna baza danych. Nie rozważymy jednak konkretnych skryptów do realizacji sekcjonowania "w sprzęcie", ale samą koncepcję — co i jak należy "pokroić na kawałki", i do czego takie pragnienie prowadzi.
Koncepcja
Ponownie określmy nasz cel: chcemy, aby zarówno dziś, jak i jutro, oraz za rok liczba odczytywanych danych PostgreSQL przy każdej operacji odczytu/zapisu pozostała mniej więcej taka sama.
Dla każdego
danych gromadzonych chronologicznie (wiadomości, dokumenty, logi, archiwa, …) naturalnym wyborem jako klucz do sekcjonowania jest data/godzina zdarzenia . W naszym przypadku takim zdarzeniem jestmoment wysłania wiadomości Zauważmy, że użytkownicy prawie zawsze.
pracują tylko z "najnowszymi" danymi — czytają ostatnie wiadomości, analizują ostatnie logi,… Nie, oczywiście, mogą przewijać wstecz w czasie, tylko robią to bardzo rzadko. Z tych ograniczeń staje się jasne, że optymalnym rozwiązaniem dla wiadomości będą
"codzienne" sekcje — bo niemal zawsze nasz użytkownik będzie czytać to, co dotarło do niego "dzisiaj" lub "wczoraj". Jeśli przez dzień piszemy i czytamy praktycznie tylko w jednej sekcji, to daje nam to także
bardziej efektywne wykorzystanie pamięci i dysku. bardziej efektywne wykorzystanie pamięci i dysku — ponieważ wszystkie indeksy sekcji mieszczą się łatwo w pamięci operacyjnej, w przeciwieństwie do „dużych i grubych” w całej tabeli.
krok po kroku
Generalnie, wszystko, co zostało powiedziane powyżej, brzmi jak jeden wielki zysk. I jest osiągalne, ale żeby to zrealizować, musimy się dobrze postarać — ponieważ decyzja o podziale jednej z encji prowadzi do konieczności 'cięcia' również powiązanych z nią.
Wiadomość, jej właściwości i projekcje
Skoro zdecydowaliśmy się na dzielenie wiadomości według dat, to również powiązane z nimi encje-właściwości (załączniki, lista odbiorców) są sensowne do podziału, a także według daty wiadomości..
Ponieważ jednym z typowych naszych zadań jest przegląd rejestrów wiadomości (nieprzeczytane, przychodzące, wszystkie), to również sensownie jest je 'wciągnąć' w podział według dat wiadomości.
Dodajemy klucz podziału (datę wiadomości) do wszystkich tabel: odbiorcy, plik, rejestry. Do samej wiadomości nie trzeba go dodawać, można użyć istniejącego DataCzas.
Tematy
Ponieważ temat jest jeden dla wielu wiadomości, nie można go 'pokroić' w tym samym modelu, musimy opierać się na czymś innym. W naszym przypadku idealnie nadaje się data pierwszej wiadomości w korespondencji. — czyli moment utworzenia, samego tematu.
Dodajemy klucz podziału (datę tematu) do wszystkich tabel: temat, uczestnik.
Jednak teraz napotykamy od razu dwa problemy:
- w której sekcji szukać wiadomości po temacie?
- w której sekcji szukać tematu wiadomości?
Oczywiście można kontynuować szukanie we wszystkich sekcjach, ale to będzie bardzo smutne i zniweczy wszystkie nasze zyski. Dlatego, aby wiedzieć, gdzie dokładnie szukać, zrobimy logiczne linki/wskaźniki na sekcje:
- dodajemy do wiadomości pole z datą tematu,
- do tematu dodajemy zbiór dat wiadomości tej korespondencji (można osobną tabelą, a można również tablicą dat)
Ponieważ modyfikacji listy dat wiadomości dla każdej konkretnej korespondencji nie będzie wiele (prawie wszystkie wiadomości wpadają w 1-2 sąsiednie dni), zatrzymam się właśnie na takim wariancie.
Podsumowując, struktura naszej bazy przyjęła następujący kształt z uwzględnieniem podziału:
Tabele: RU, przy odrzuceniu cyrylicy w nazwach tabel/pól lepiej nie patrzeć.
-- sekcje według daty wiadomości
CREATE TABLE "Wiadomość_YYYYMMDD"(
"Wiadomość"
uuid
PRIMARY KEY
, "Temat"
uuid
, "DataTematu"
date
, "Autor"
uuid
, "DataCzas" -- używamy jako daty
timestamp
, "Tekst"
text
);
CREATE TABLE "Adresat_YYYYMMDD"(
"DataWiadomości"
date
, "Wiadomość"
uuid
, "Osoba"
uuid
, PRIMARY KEY("Wiadomość", "Osoba")
);
CREATE TABLE "Plik_YYYYMMDD"(
"DataWiadomości"
date
, "Plik"
uuid
PRIMARY KEY
, "Wiadomość"
uuid
, "BLOB"
uuid
, "Nazwa"
text
);
CREATE TABLE "RejestrWiadomości_YYYYMMDD"(
"DataWiadomości"
date
, "Właściciel"
uuid
, "TypRejestru"
smallint
, "DataCzas"
timestamp
, "Wiadomość"
uuid
, PRIMARY KEY("Właściciel", "TypRejestru", "Wiadomość")
);
CREATE INDEX ON "RejestrWiadomości_YYYYMMDD"("Właściciel", "TypRejestru", "DataCzas" DESC);
-- sekcje według daty tematu
CREATE TABLE "Temat_YYYYMMDD"(
"DataTematu"
date
, "Temat"
uuid
PRIMARY KEY
, "Dokument"
uuid
, "Nazwa"
text
);
CREATE TABLE "UczestnikTematu_YYYYMMDD"(
"DataTematu"
date
, "Temat"
uuid
, "Osoba"
uuid
, PRIMARY KEY("Temat", "Osoba")
);
CREATE TABLE "DatyWiadomościTematu_YYYYMMDD"(
"DataTematu"
date
, "Temat"
uuid
PRIMARY KEY
, "Data"
date
);
Oszczędzamy grosz
A jeśli nie używamy na podstawie rozkładu wartości pola (przez wyzwalacze i dziedziczenie lub PARTITION BY), ale „ręcznie” na poziomie aplikacji, można zauważyć, że wartość klucza partycjonowania jest już przechowywana w nazwie samej tabeli.
Dlatego jeśli tak bardzo martwisz się o objętość przechowywanych danych, to można zrezygnować z tych „zbędnych” pól i zwracać się bezpośrednio do konkretnych tabel. Prawda, że wszystkie zapytania z wielu sekcji w tym przypadku należy już zrealizować po stronie aplikacji.
Źródło: habr.com
