Szybki select po dane z relacją many to many - SQL

Cześć, mam takie wymaganie, że mam sobie kolekcję userów i dokumentów. Logiczna relacja jest many to many. Baza pod spodem musi być SQL (takie wymaganie). Dane zasilane są eventami.
Klasyczne zamodelowanie tabel przy many to many to 3 tabele (user, document i łącząca), ale najważniejsze wymaganie jest takie, aby zapytania po userów zwracały też dokumenty dla każdego z userów i to z jak największą wydajnością.
Nie chcę zatem modelować standardowo na 3 tabele i robić joinów.

Wstępny pomysł jest taki, żeby przy odebraniu eventu każdemu userowi zapisywać kolekcje dokumentów jako JSONa do kolumny. Uniknę wtedy tabeli łączącej i faktycznie może i zapis będzie dłuższy, ale to nie jest problem - chodzi o jak najwydajniejszy odczyt bo ruch będzie naprawdę spory.

Znacie lepsze rozwiązania?

SQL musi być jak co ..

Jaki silnik bazodanowy?
Jaka jest logiczna relacja między dokumentami? Co decyduje o tym, że dokumentX jest powiązany z user1,user2 ale już nie user3?

No ale jak masz tabele łącząca i odpowiednie indeksy to może się okazać, że łączenie tabeli będzie szybsze niż prasowanie JSONa. Dodatkowo przysypać to wszystko RAMem i wrzucić tabelki do pamięci, żeby nie musiały lecieć z dysku i będzie szybsze niż światło. Bazy danych najbardziej opóźniają na operacjach wejścia/wyjścia, czyli na odczycie z dysku, a nie operacjach logicznych na danych.
Za mało jest informacji o ilości danych - ile będzie dokumentów do uzywkownika - kilka, kilka set, kilka tysięcy? Co znaczy duży ruch?

Silnik to postgres.
Logiczna relacja między dokumentami? Żadnej nie ma. Dokumenty to niezależne byty.
O relacji między dokumenty i userami decyduje logika biznesowa, ale nie w naszym systemie. My po prostu dostajemy event, że jest nowy user z takimi i takimi dokumentami, albo ze istniejacy user ma zmiane w dokumentach.

No właśnie tez jednak myślałem, że może ten join okaże się szybszy. Pewnie do sprawdzenia jakimiś testami wydajnościowymi.
Userów ma być coś ok miliona, dokumentów kilkadziesiąt tysięcy. Dla usera 1-20 mniej więcej,

O przysypaniu RAMem masz na myśli Cache?

Ruch z tego co wiem to 100-1000 req/s

Bambo napisał(a):

Z wymaganiami jest spory problem. Wstępnie wiemy, że ma być many to many i mega wydajne ;p

Ten wątek powinien służyć za przykład jak nie zaczynać tworzyć oprogramowania. Podsumowując jedyne co wiadomo, to, że ma być szybko i SQL.

@Bambo: a możesz podać kontekst? Chodzi o to do czego ten system ma służyć i co oferować?
No bo z jednej strony dostajecie eventa, o jakichś zmianach a z drugiej strony co? Ktoś do waszego sytemu zagląda po jakimś API? Pobiera te dokumenty i coś z nimi robi zwracając je znowu?

Brakuje mi tutaj logiki biznesowej działania waszego systemu - jaki ma on rozwiązywać problem i jaką pełnić funkcję w całym łańcuchu.

@Bambo: Jak masz 1 mln użytkowników to każda zmiana w dokumentach któreś użytkownika będzie wymagały operacji na tej tabeli, a nie prostego dopisania kolejnego wiersza w jakiejś małej tabelce.
Jak baza będzie maiła dostępne dużo pamięci to nie będzie musiała doczytywać danych z dysku co znacznie poprawi wydajność.

@Bambo: podsumujmy:

[ SYSTEM ZEWNĘTRZNY X ] ====== [ WASZ SYSTEM] [API] ===== [ INNE SYSTEMY]

Czyli wasz system jest czymś w rodzaju jakiegoś proxy do innego systemu ale tylko do odczytu?

Czyli wróżenie z fusów. Okaże się, że budujecie kombajn do koszenia trawnika raz w miesiącu ;)

Testy wydajnościowe prawdopodobnie nie wykażą niczego sensownego :

• nie wiadomo jaki ruch ma być obsłużony
• jakie czasy odpowiedzi per request ma zapewniać rozwiązanie
• jakie będą rozmiary dokumentów
• na jakim sprzęcie

Pesymistycznie potrzebujesz, żeby system udźwignął operacje I/O generowane przez bazę.
1000 req/s * 20 dokumentów/req * rozmiar dokumentu (kb per dokument) / rozmiar bloku I/O (kb) (przeważanie 4kb) => tyle IOPS na same odczyty w peeku
(na same tylko dokumenty)

Dorzucając RAM sprawisz, że system odciąży dyski (będzie trzymał odczytane bloki danych w pamięci). Może się jednak okazać, że cachowanie będzie nieefektywne,
tzn. dokumenty będą zajmowały dużo więcej przestrzeni niż dostępnego RAMu, wówczas wpadasz w ten pesymistyczny scenariusz, że podsystem dyskowy musi unieść
określoną ilość operacji I/O na sekundę.

Od strony bazy można nieco optymalizować strukturę fizyczną, która pozwoli na uniknięcie kolizji.
np. tabele X -> dyski1..10
tabelaY,Z -> dyski11..20
logi bazodanowe -> dyski 21..25
itp.

Można partycjonować tabele z dokumentami, użytkownikami. Ale to zależy od całościowego obrazu rozwiązania (np. czy będziesz miał częste update, czy częste dostawianie nowych obiektów)

@Bambo: aż tak na inżynierii się nie znam ale co jest złego w tym aby np. baza danych pełniła rolę trzymania powiązań (tutaj trzeba podpytać jak najoptymalniej to zaprojektować) a pliki sobie leżały na takim minio?
Z jednej i drugiej strony będzie jakieś API, to z lewej do systemu który do was strzela danymi, a to z prawej do systemów które strzelają po dane.

btw, dlaczego nie można tego puścić bez żadnego pośrednika?

@.andy:

Mamy być wydajnym proxy dla starych systtemów, które trzymają te dane od kilkunastu lat, ale są mega powolne i niegotowe na wielki ruch. My mamy być magazynem danych i nasłuchiwać eventy z wielu takich legacy obszarów.

Brzmi jak idealny przypadek dla Casandry lub innego Wide-column store. Szukając Wide-column czy Column Families dla PostgreSQL trafiłem na CockroachDB - Column Families. CockroachDB jest bazą SQLową, ale zawiera bardzo dużo dodatków NoSQLowych ułatwiających życie. Spradziłbym czy można to użyć.

Jak poprawnie zbudujesz indeksy, to m2m z tabelą pośredniczącą ma szansę działać szybko. Wrzucenie dokumentu w postaci JSON ma tę wadę, że ciężej będzie się do niego odwoływać.

@Bambo: tak jak pisałem: Z jednej i drugiej strony API. W środku jakaś aplikacja która do trzymania samych powiązań używa Postgresa a do plików coś ala Minio.
Do tego jakieś cache, load balancery i inne cuda.

Ale to można rozwiązać na wiele sposobów, łącznie z osobną bazą do zapisu i osobną do odczytu, i do tego nie używając SQL (nie żeby było coś w tym złego). Bez wiedzy jak wszystko ma działać to jak wróżenie z fusów. MOże najprościej zrobić MVP z 3 tabelami i zobaczyć jak działa i poprawiać w trakcie? Zwłaszcza że z tego co mówisz wymagania są mgliste jak u Janusza który chce "sklep, taki jak allegro", a konkretów brak. No i dziś nie ma sensu trzymać dokumentów(w sensie np plików pdf itp) w bazie danych SQL. Są lepsze rozwiązania jak np trzymanie metadanych w SQL i np url, a pliki sobie leżą na jakimś serwerze. No i ponadto są rozwiązania kombajnowe typu M-Files gdzie już pliki i metadane można trzymać razem. Ale to wielki kombajn.

Jak macie dane z istniejącego serwisu legacy to możecie sobie podpatrzyć w tym systemie takie rzeczy jak:

• rozkład ilości dokumentów per użytkownik
• rozkład ilości użytkownika per dokument
• rozkład rozmiarów dokumentów
• ilość zapytań podobnego typu jakie macie wy obsłużyć

Na tej podstawie zrobić sobie jakiś dataset pod performance testy, sztuczny traffic, taki bardziej pesymistyczny niż zwykle (bo nie pracujecie na rzeczywistych danych), zdefiniować sobie jakieś SLO i na tej podstawie coś robić.

Łatwiej wyekspensować flaszkę dla admina starego systemu i wbić się na istniejące bazy niż robić takiego waterfalla na założeniach z d**y. Problem tutaj nie jest techniczny tylko organizacyjny.