Pomysł optymalizacyjny

Zastanawiam się nad pewną optymalizacją w PostgreSQL.

Powiedzmy, że mamy tabelę produktów, gdzie bardzo często zmienia się cena, a pozostała część nie.

Jak wiadomo zmiana w bazie = tworzenie kopii rekordu na potrzeby transakcyjne.

Zastanawiam się w takim razie, czy nie lepiej by było oddzielić cenę od tabeli produktu do osobnej tabeli i w razie potrzeby dołączać do produktu JOINEM?

W tym momencie zmiana ceny = kopia rekordu, ale bardzo małego objętościowo, bo zawierającego de-facto 4 kolumny:

id produktu,
cena netto,
stawka vat,
cena brutto

z brutto nawet mozna zrezygnować.

Tymczasem zmiana ceny bez oddzielenia = kopia całego dużo większego rekordu produktu zawierającego np. długi tekstowy opis.

Czy to ma sens?

Dla mnie ten powód to za mało.

W sumie aktualizacji cen jest 100x mniej niż wierszy z transakcjami handlowymi, więc niekoniecznie ten punkt optymalizacji w ogóle ma sens.
Premature Optimization Is the Root of All Evil

ALE

Zwykle w systemach ERP jak wchodzimy w las, okazuje się że cen jest multum, wg klienta, wg grupy czegoś, fazy księżyca itd... w gruncie rzeczy czasem nie ma tej głównej ceny. Moze być algorytmiczna, wynegocjowana (ręczna), cena limitowana w czasie ....
O ile by zachodził taki przypadek, byłbym "za" tabelą, ale to inny jej projekt.

z brutto nawet mozna zrezygnować.

Przy tanich produktach byłbym na nie. Grosiki kopią po d...
Jak tam masz lokomotywy, OK.

Można zupełnie zgodnie z prawem liczyć FV w netto lub brutto. W intensywnym detalu (liczonym wraz z gotówką w brutto) inaczej, w innych branżach inaczej.
Biada komuś, kto będzie kalkulatorem sprawdzał po systemie.
Ja daję na stopce jakiś koment "Cena brutto(netto) podana tylko informacyjnie"albo coś w tym rodzaju

Jedna rzecz - wprawdzie to nie do końca odpowiedź na Twoje pytanie, ale ja bym nie trzymał jednocześnie cen netto oraz brutto.

Zawsze można jedno wyliczyć z drugiego. A teraz - wyobraź sobie, że powtarza się sytuacja z 2011 roku, kiedy "chwilowo" podniesiono VAT z 22 na 23% (https://www.money.pl/podatki/podwyzszony-tymczasowo-vat-zostaje-na-dluzej-wprowadzono-juz-dekade-temu-6552835664034752a.html). Jak masz ceny netto i brutto, to teraz musisz przeliczyć wszystkie artykuły wpisane do systemu.

Lepiej trzymać cenę netto oraz stawkę VAT. I ta stawka też nie powinna być wpisana na sztywno, tylko raczej jak masz w kasie fiskalnej - są zdefiniowane stawki VAT (A, B, C, D itp. - https://www.pit.pl/aktualnosci/nowa-matryca-stawek-vat-a-kasy-fiskalne-990053), a ich wartości trzymasz w osobnej tabeli. Jak później nastąpi zmiana VAT grupy C (bo się zmienił VAT na buty dla dzieci) to nie poprawiasz we wszystkich artykułach, tylko zmieniasz w tabeli ze stawkami. Zresztą trzymanie stawek VAT w tabeli artykułów jest także problematyczne z tego powodu, że może się zdarzyć, iż dwie różne grupy będą miały taką samą stawkę (np. 8%). I potem jedna grupa się zmieni na 9%. I co - teraz wszystkie towary, które mają VAT 8% przerobisz na 9% (łącznie z towarami przypisanymi do grupy, która nie została zmieniona)? A może ręcznie przejrzysz wszystkie pozycje i zadecydujesz?

I ostatnia sprawa w zakresie dygresji - nawet ten sam produkt może mieć inną stawkę VAT. Powiedzmy że okno - jak kupisz u mnie okno to naliczę Ci 23%. Ale jeśli zamówisz okno z montażem, to mogę zastosować stawkę obniżoną 8%. Czyli - ten sam indeks może być rozchodowany na innej stawce. Dlatego - jest szansa (aczkolwiek ciężko bez dokładnej wiedzy, co tam masz i co robisz) że w ogóle nie powinieneś mieć przypisanego VAT do produktu w kartotece asortymentowej, a jedynie operować nim w kontekście pozycji na zamówieniu/dokumencie sprzedażowym. Aczkolwiek - to tylko taka luźno rzucona myśl, ciężko coś więcej powiedzieć nie znając okoliczności. W kazdym razie - jeśli będziesz trzymać VAT w tabeli produktów, to rób to jako odnośnik do tabeli ze stawkami, a nie wpisane na sztywno.

A teraz koniec dygresji i sprawa właściwa. Moim zdaniem może to mieć sens, ale pod warunkiem, że ten join nie będzie swoim obciążeniem równoważył albo przewyższał oszczędności na lockowaniu tabeli przy update. Ale to najlepiej jakbyś sobie sam sprawdził - odpalić serię zapytań w aktualnym scenariuszu, potem klon bazy i wydzielenie ceny do osobnej tabeli i ponowny test. Ważny jest też sposób korzystania z bazy - jak często i jakiego typu operacje są wykonywane. Obawiam się, że takie rozbicie pod względem wydajności wiele nie zmieni, ale to raczej trzeba by było sprawdzić, a nie "czuć" ;)

Mam sytuacje, że tysiące produktów co kilka minut mają aktualizację ceny - związane jest to ze zmianami kursów EUR lub USD.

I to właściwie jedyna zmiana jeżeli chodzi o rekord produktów, bo po dodaniu, opis czy inne cechy zmieniają się niezmiernie rzadko.

Co do JOIN-a to jest jednak operacja czytania, a JOIN jest po indeksie, wydaje mi się że zysk powinien być duży.

AnyKtokolwiek napisał(a):

Dla mnie ten powód to za mało.

W sumie aktualizacji cen jest 100x mniej niż wierszy z transakcjami handlowymi, więc niekoniecznie ten punkt optymalizacji w ogóle ma sens.

Nie rozumiem tutaj sensu wypowiedzi.

Transakcje handlowe - czyli złożenie zamówienia w ogóle nie dotyka tabeli produktów, poza pobraniem ceny w momencie złożenia zamówienia.

Po złożeniu zamówienia zmiana ceny produktu nie wpływa na cenę w zamówieniu, to byłby jakiś absurd.

Premature Optimization Is the Root of All Evil

To nie jest żadne premature, bo system działa, i celem jest zmniejszenie obciążenia I/O przy zapisie.

A czy nie będziesz musiał robić update na cenniku po np numerze produktu, czyli i tak będziesz sięgał do tabeli produktów przy updacie? Tak naprawdę nie wiadomo jak jest to zaimplementowane w Postgresie i czy robi kopie wiersza, czy tylko trzyma w tempi zmienione wartości. Moim zdaniem jak nie robisz updatów kilku milionów produktów naraz, nie będzie miało to znaczenie. Jednym słuszny powód dlatego to istnienie różnych cenników jak wspomniał @AnyKtokolwiek .

tysiące produktów co kilka minut mają aktualizację ceny - związane jest to ze zmianami kursów EUR

A nie lepiej trzymać w takim razie niezmienne ceny wyrażone w euro, a jedynie aktualizować kurs? I potem - podczas wyświetlania produktów, pobierać 6,18E, mnożyć przez aktualny kurs 4,85 i wyświetlić cenę 29,97 zł?

UglyMan napisał(a):

A czy nie będziesz musiał robić update na cenniku po np numerze produktu, czyli i tak będziesz sięgał do tabeli produktów przy updacie? Tak naprawdę nie wiadomo jak jest to zaimplementowane w Postgresie

1. Wiadomo jak to jest zaimplementowane, to nie żadna tajemnica - CAŁY rekord jest kopiowany, nie ważna jak dużo w nim zmieniasz.

2. Sięganie po cenę - to operacja ODCZYTU, poza tym przecież i tak czy mam to oddzielone czy nie, to muszę pobrać cenę, i tutaj chyba lepiej ją pobrać z tabeli gdzie rekordy są bardzo małe, niż z głównej tabeli produktów.

cerrato napisał(a):

tysiące produktów co kilka minut mają aktualizację ceny - związane jest to ze zmianami kursów EUR

A nie lepiej trzymać w takim razie niezmienne ceny wyrażone w euro, a jedynie aktualizować kurs? I potem - podczas wyświetlania produktów, pobierać 6,18E, mnożyć przez aktualny kurs 4,85 i wyświetlić cenę 29,97 zł?

Nie, bo:

1. ja nie ustalam ceny w EUR, ta cena też się zmienia, ceny są pobierane przez REST z innego systemu - co kilka minut ten system zmienia ceny kilkunastu tysięcy produktów.
2. Wyświetalnie produktów na frontendzie to najmniejszy problem, jest bardzo wiele analiz i innych rzeczy, obliczanie teraz za każdym razem ceny z EUR na ZŁ dla dziesiątek tysięcy produktów to dopiero byłaby masakra.

TomRZ napisał(a):

UglyMan napisał(a):

A czy nie będziesz musiał robić update na cenniku po np numerze produktu, czyli i tak będziesz sięgał do tabeli produktów przy updacie? Tak naprawdę nie wiadomo jak jest to zaimplementowane w Postgresie

1. Wiadomo jak to jest zaimplementowane, to nie żadna tajemnica - CAŁY rekord jest kopiowany, nie ważna jak dużo w nim zmieniasz.

Nie znam aż tak dobrze algorytmów wewnętrznych, jakie są zaimplementowane w Postgresie. Ale skoro tak uważasz.

2. Sięganie po cenę - to operacja ODCZYTU, poza tym przecież i tak czy mam to oddzielone czy nie, to muszę pobrać cenę, i tutaj chyba lepiej ją pobrać z tabeli gdzie rekordy są bardzo małe, niż z głównej tabeli produktów.

No ale odczytać i tak musisz z dysku tabele produktów a, jak już będzie w pamięci to zrobienie kopii to raczej nie jest duży problem. W bazach największym problemem są operacje wejścia/wyjścia.

Wątek można zamknąć.

@cerrato:

Lepiej trzymać cenę netto oraz stawkę VAT.

Dla młodych padawanów programowania systemów wszelakich z kwotami - włącznie z wieluwalutowością; NIGDY TAK NIE RÓB na dokumentach finansowych typu faktura.
No chyba, że chcesz mieć problemy...
Katalog czy cennik produktów, to trochę inny problem i tam faktycznie lepiej trzymać wartość w walucie podstawowej i aktualny kurs, a liczyć to na żądanie - może być i przez bazę danych.
Narzut będzie pomijalny.

O liczeniu od netto lub od brutto padło już w komentarzach.
Do tego dodaj sobie obsługę walut, kursów i różnic kursowych - powodzenia @cerrato z ogarnięciem tego syfu.
Zapewniam Cię @cerrato, że nie dojdziesz do ładu z grosikami - nie ma mowy.
Zwłaszcza, jeśli przy okazji jakiś "as" użyje typów zmiennoprzecinkowych do wartości finansowych.

Nie chcesz mieć problemów, to zawsze używaj typów stałoprzecinkowych, licz to na aplikacji, a w bazie zapisuj wynik - każdej składowej z osobna, najlepiej ze wskazaniem czy obliczone wartości są od netto czy od brutto.
Brzmi paranoicznie?
No może, ale tak mi mówi doświadczenie.

Oczywiście, że na kwoty tylko i wyłącznie typ NUMERIC / DECIMAL, żadnego float.

Tak przy okazji co do różnych stawek VAT w zależności np. czy okno jest z montażem czy bez, to się robi po prostu osobny produkty, z inną stawką VAT, lub dostosowuje silnik sklepu do różnych wariantów VAT dla wybranych produktów, w wybranej opcji sprzedaży.

Przechodzenie produktów z jednej stawki na inną zdarza się bardzo rzadko, to jest głównie "spożywka" czyli niewielka część e-commerce, iw takim przypadku po prostu zmienia się dla takich produktów stawkę, z momentem wejścia nowej stawki - ustawiamy np. operację przez cron na godzinę 0:00 dla dnia wejścia nowej stawki, rekordy są pobierane z lockiem i następuje podmiana stawki, plus zawsze w momencie składania zamówienia weryfikacja koszyka - ktoś mógł włożyć produkt 15 minut wcześniej, ja w swoim silniku sklepu nie sprawdzam koszyka przy każdej odsłonie bo bym zabił serwer, ceny/stawki produktów w koszyku są weryfikowane co kilkanaście minut, i w momencie złożenia zamówienia.

Co do problemów z grosikami to najczęstszym błędem jest obliczanie nie tak jak kasa fiskalna robi (sprzedaż detaliczna) - każda sztuka osobno brutto, czyli obliczamy vat osobno dla każdej sztuki, tylko liczenie vat od sumy netto wszystkich sztuk danej pozycji w koszyku/zamówieniu :)

@wloochacz: W sumie masz rację, trochę niejasno napisałem.

W moim wcześniejszym poście jest fragment nie powinieneś mieć przypisanego VAT do produktu w kartotece asortymentowej, a jedynie operować nim w kontekście pozycji na zamówieniu/dokumencie sprzedażowym.. I wydawało mi się, że co do netto/brutto też to było jasno napisane - ale po przeczytaniu mojego posta stwierdzam, że mam rację - wydawało mi się ;)

Dlatego - żeby była jasność:

• w kartotece trzymasz jeden rodzaj cen i sobie je na bieżąco przeliczasz
• w dokumentach trzymasz już wyliczone kwoty - zarówno brutto, jak i netto, podsumowania itp. W sensie - raz wyliczona kwota już ma taką pozostać na wieki wieków amen.

Aczkolwiek - nie uważam, żeby moja porada była błędna. Z tego co rozumiałem to OP pyta o aktualizowanie cen kartoteki Powiedzmy, że mamy tabelę produktów - czyli pisze o kartotece produktów, a nie bazie dokumentów księgowych. Tam są ceny używane jedynie w celu ich prezentacji razem z opisami produktów, nie muszą (a nawet nie powinny) posiadać obu wpisanych na sztywno, a jedynie jedną cenę plus mechanizm przeliczania na "drugą" cenę.

TomRZ napisał(a):

Przechodzenie produktów z jednej stawki na inną zdarza się bardzo rzadko, to jest głównie "spożywka" czyli niewielka część e-commerce, iw takim przypadku po prostu zmienia się dla takich produktów stawkę, z momentem wejścia nowej stawki - ustawiamy np. operację przez cron na godzinę 0:00 dla dnia wejścia nowej stawki, rekordy są pobierane z lockiem i następuje podmiana stawki, plus zawsze w momencie składania zamówienia weryfikacja koszyka - ktoś mógł włożyć produkt 15 minut wcześniej, ja w swoim silniku sklepu nie sprawdzam koszyka przy każdej odsłonie bo bym zabił serwer, ceny/stawki produktów w koszyku są weryfikowane co kilkanaście minut, i w momencie złożenia zamówienia.

Z tego co tu czytam, to cały ten system oparty jest o bazę danych bez warstwy pośredniej na poziomie domeny czy nawet cache... Przy dynamice zmian powyżej przeciętnej i sporym obciążeniu przy odczytach to jest prędzej czy później słaby pomysł. Wiem, bo sam to przerabiałem. Wszelkie patenty z cron, lock itp. to tylko i wyłącznie proteza. Niestety, ale to prawda.

Może inaczej; nigdy nie powinieneś np. sprawdzać koszyka przy każdej odsłonie. Nigdy.
Koszyk powinien się aktualizować, kiedy wymaga zmiany - nie wcześniej i nie później i nieważne z jakiego powodu (zmiana ceny, vat czy po prostu zawartości koszyka).
Ale to wymaga backendu i np. WebSocket do obsługi reaktywnego powiadamiania o zmianach w tym koszyku (tak wiem, napisałem od końca, bez początku - ale mądrej głowie dość po słowie :)).

Tak wiem, to jest zupełnie inne podejście.
Ale masz dwa wyjścia - albo zmienisz podejście, albo coraz głębiej i głębiej będziesz brnął w bagno baz danych, a z systemem dynamicznym i od pewnej skali to nigdy nie jest dobre rozwiązanie.

PS.
Mi się wydaje, że ten problem to jest po prostu tzw. transactional outbox - doczytaj, jeśli nie wiesz o co chodzi.

wloochacz napisał(a):

Z tego co tu czytam, to cały ten system oparty jest o bazę danych bez warstwy pośredniej na poziomie domeny czy nawet cache...

Pierwszy sklep internetowy stworzyłem w 1996, na własnym silniku, w Perlu, dla dużego wydawnictwa. Obecny silnik pracuje pod dużym obciążeniem (w świątecznym szczycie dochodzi do 200 req/sek), na sporym wolumenie danych (kilkadziesiąt GB baza produkcyjna), więc wybacz, ale nie potrzebuję rad jak optymalizować frontend, zaoszczędź swój czas, chyba, że to piszesz dla innych ludzi.

Natomiast narasta problem częstych aktualizacji cen, więc nawet jeżeli jest cache (używam Memcached) i inne rozwiązania, to po co zamulać serwer i I/O, jeżeli zamiast kopiowania całego rekordu produktu przy aktualizacji małej cząstki danych, będziemy trzymać ceny w osobnej tabeli.

Natomiast ta zmiana wymaga sporo pracy, jeszcze nie miałem czasu robić testów, żeby zobaczyć czy to się w ogóle opłaca, jak zrobię to się tutaj podzielę wnioskami.

PS. tworzenie wielu pośrednich warstw, mikroserwisów, rozproszenia na wiele serwerów to jest coś czego wolę uniknąć tak długo jak się da, na razie wolę "modularny monolit". To jest po prostu bardzo drogie i pracochłonne, już bardziej wolę takie rozwiązania jak replikacja, partycjonowanie, loadbalancing modularnego monolitu, archiwizacja starszych danych do bazy archiwum niż grzebanie z mikroserwisami.

Mikro są fajne na papierze, i kiedy chodzi o coś małego, natomiast przełożenie dużej skomplikowanej aplikacji na setki mikroserwisów to są ogromne koszta. Allegro zatrudnia ponad tysiąc programistów (takie dostałem info sam nie mogę w to uwierzyć), tak dużo potrzeba, żeby utrzymać i rozwijać ich aplikację REST + różne poboczne rzeczy związane z tym systemem.

PPS. Allegro na LinkedIn, ponad 2 tys. pracowników: https://www.linkedin.com/company/allegro-pl/

@TomRZ: jak na moje to trzymanie cen w osobnej tabeli to jak najbardziej dobre rozwiązanie. Dlaczego? Dlatego, że normalizujemy wtedy ładnie oraz trzymamy całą listę zmian (możemy mieć wtedy w tabeli pola sice TIMESTAMP NOT NULL oraz till TIMESTAMP) co pozwala nam na śledzenie jak się zmieniała cena wewnątrz naszego systemu (na to można nałożyć sobie jakiś ładny widok by mieć od razu wyfiltrowane tylko obecne ceny).

Dzięki temu masz również lepsze możliwości analityczne oraz audyt:

• kto ustawił jaką cenę i na jak długo
• kto i ile kupił za daną cenę
• czy dana cena była promocyjna (możesz mieć dodatkowe pole discount, które informuje, że to jest promocja i jakiej wysokości)
• masz możliwość ustawienia promocji czy zmian cen "w przyszłości", dzięki czemu obsługa systemu może być prostsza podczas przygotowań do dużego ruchu
• jeśli się spiąć, to można nawet ustawić A/B testy na cenach (dodatkowe pole w postaci np. ratio, które określa jak często ma się dana cena pojawiać użytkownikom)

Ogólnie jak na moje to same zalety względem praktycznie pomijalnych wad.

• zakładamy, że SQLe masz juz zoptymalizowane - pytanie jak zmieni sie wydajnosc SQLi jesli wydzielisz kolumne do innej tabeli, mozesz zawsze sprawdzic
• czy mechanizm, ktory wykonuje update X tys rekordow, co Y min, nie moglby robic aktualizacji np 4 razy czesciej i za kazdym razem po 4 razy mniej rekordow ? Moze warto rozdzielic to na mniejsze etapy

pozdr

pvw napisał(a):

• czy mechanizm, ktory wykonuje update X tys rekordow, co Y min, nie moglby robic aktualizacji np 4 razy czesciej i za kazdym razem po 4 razy mniej rekordow ? Moze warto rozdzielic to na mniejsze etapy

Jeżeli już to raczej podział na bloki transakcji - czyli zamiast każda zmiana ceny to osobna transakcja, to np. jedna transakcja na 1000 zmian cen.

Tylko jest problem z transakcjami używającymi blokad (klauzula FOR UPDATE) na większą ilość niż jeden rekord - groźba deadlocka.

Jednak przeniesienie tego do osobnej tabeli znacznie zmniejsza prawdopodobieństwo wystąpienia deadlocka ponieważ:

• zamiast blokować cały rekord produktu (klauzula FOR UPDATE), blokujemy tylko rekord ceny, czyli mniejszą ilość danych które podlegają edycji - jakiś inny proces masowo zmieniający np. w nazwie produktów, czy działania adminów w panelu administracyjnym które dotykają innych danych w produkcie niż ceny, nie będą powodować deadlocka

• można zbudować usługę do zmiany cen, z metodą np. "bulkChangePrice", i tylko i wyłącznie jej używać do zmian cen, ta usługa będzie blokować rekordy w tabeli na ceny zawsze w tym samym kierunku / sortowaniu (np. w id produktu rosnąco), to praktycznie gwarantuje brak deadlocka

Generalnie coraz bardziej skłaniam się ku tej optymalizacji, ale czas na testy i wdrożenie tego nastąpi nieprędko, prawdopodobnie w ciągu kilku najbliższych miesięcy. To nie jest palący problem, ale stopniowo będzie się zwiększał.