PostgreSQL czy MySQL? Benchmark zmienia zwycięzcę wraz z operacją

Wydajność PostgreSQL i MySQL zależy od wykonywanej operacji. W badaniu aplikacji desktopowej PostgreSQL uzyskał lepsze czasy dodawania, aktualizacji i usuwania danych, a MySQL — pobierania; przy 1000 usunięć średni czas wyniósł odpowiednio 5127,4 i 10095,2 ms. Zmiana rodzaju operacji zmieniła więc lidera, choć mierzono czas scenariusza uruchomionego w aplikacji, a nie sam czas wykonania SQL.

Przy przewadze zapisów PostgreSQL jest mocniejszym kandydatem na podstawie tego pomiaru. Przy przewadze odczytów warto uwzględnić MySQL, lecz wynik prostego pobierania nie przesądza o szybkości filtrów, złączeń ani zapytań do JSON. O wyborze decydują również indeksy, wielkość danych i proporcje operacji w konkretnej aplikacji.

Warunki testu: co obejmował zmierzony czas

Badano katalog filmów i seriali obsługiwany przez aplikację desktopową napisaną w Javie. Między aplikacją a bazami działały osobne REST API, do których wysyłano żądania w formacie JSON. Pomiar zaczynał się i kończył w aplikacji, z użyciem System.nanoTime(), więc obejmował drogę żądania przez warstwę pośrednią. Nie wydzielono w nim czasu pracy samego serwera bazy.

• Dane: operacje bez relacji wykonywano na encji filmu; warianty z relacjami dotyczyły serialu i sezonów.
• Operacje: dodawanie i pobieranie badano zarówno bez relacji, jak i z relacjami. Aktualizacja oraz usuwanie dotyczyły encji bez relacji.
• Serie: scenariusze obejmowały 1, 100 lub 1000 powtórzeń pętli. Każdy wariant wykonano 10 razy, a wyniki przedstawiono jako średnie z odchyleniami standardowymi.
• Stan danych: dla kolejnych zestawów operacji tworzono osobne, początkowo puste schematy. Po zakończeniu zestawu schemat zastępowano nowym.

To ważne rozróżnienie dla aplikacji internetowej. Wynik uzyskany na krótkich seriach żądań z jednego programu nie opisuje zachowania przy wielu równoczesnych użytkownikach, długotrwałym ruchu i rozrastających się tabelach. Ponieważ pomiar obejmował także REST API, udział wykonania zapytania SQL w całkowitym czasie mógł być różny dla poszczególnych operacji.

Zapisy i odczyty dały różne wyniki

W badanych scenariuszach przewaga PostgreSQL była najwyraźniejsza przy modyfikowaniu danych. Dla dodawania bez relacji rosła wraz z długością serii; przy większych seriach czas był w przybliżeniu o połowę krótszy niż dla MySQL. Aktualizacja i usuwanie również wypadły lepiej po stronie PostgreSQL. Wyniki pobierania wskazały MySQL, przy mniejszych różnicach niż w operacjach zmieniających dane.

Seria usuwania pokazuje, dlaczego jedna liczba nie wystarczy do wyboru bazy. Jeżeli aplikacja rzadko usuwa rekordy, nawet duża różnica w czasie tej operacji może mieć niewielki wpływ na jej codzienne opóźnienia. Jeżeli większość ruchu stanowią odczyty, ich udział ma większe znaczenie niż efektowna przewaga w mniej częstym zadaniu. Podobnie pobranie pojedynczego rekordu nie opisuje kosztu raportu, który filtruje i łączy wiele tabel.

Wyniki dotyczą całych badanych scenariuszy i zastosowanego modelu danych. Zmiana liczby rekordów, indeksów, liczby połączeń czy sposobu grupowania zapisów może zmienić czasy po obu stronach. Dlatego opublikowana kolejność jest użytecznym punktem odniesienia dla podobnego obciążenia, a nie stałą cechą silników.

JSON: gdzie przebiega droga do indeksu

Oba silniki obsługują JSON, ale różnie przygotowują dane do wyszukiwania. Dokumentacja PostgreSQL dotycząca typów JSON rozróżnia json, który zachowuje tekst wejściowy, oraz jsonb, przechowujący przetworzoną reprezentację binarną. Przy zapisie jsonb wymaga dodatkowego przetworzenia, za to przy odczycie nie trzeba ponownie parsować tekstu. Typ jsonb obsługuje indeksowanie GIN dla określonych operatorów wyszukiwania kluczy i zawierania danych.

Indeks GIN na całym dokumencie daje elastyczność, gdy zapytania dotyczą różnych kluczy. Jeżeli aplikacja stale filtruje po jednej ścieżce, można rozważyć indeks na odpowiednim wyrażeniu; będzie zawierał dane potrzebne temu zapytaniu, zamiast wartości z całego dokumentu. O skuteczności decyduje zgodność operatora i postaci zapytania z wybranym indeksem.

W opisie typu JSON dla MySQL 8.4 dane również mają wewnętrzny format binarny, pozwalający odczytywać elementy bez ponownego parsowania tekstu. Kolumny JSON nie indeksuje się bezpośrednio jak zwykłej kolumny: indeks można utworzyć na kolumnie generowanej, która wyodrębnia potrzebną wartość. MySQL może też częściowo aktualizować zapisany dokument, ale optymalizacja wymaga spełnienia warunków dotyczących użytej funkcji, aktualizowanej kolumny i zastępowanej wartości.

Przy filtrowaniu wartości tekstowej liczy się nawet postać wyrażenia. Zasady optymalizatora MySQL wymagają zgodności wyrażenia w zapytaniu z definicją kolumny generowanej oraz zgodności typu wyniku. Gdy funkcja JSON zwraca tekst z cudzysłowami, JSON_UNQUOTE() w definicji kolumny pozwala dopasować indeks także do porównania z niecytowanym ciągiem. Samo istnienie indeksu nie gwarantuje więc, że zapytanie z niego skorzysta.

Dlaczego wynik testu nie rozstrzyga o kolumnach JSON

W opisanym eksperymencie JSON służył do przesyłania żądań do REST API. Nie podano osobnego pomiaru wyszukiwania w kolumnach jsonb PostgreSQL ani w kolumnach JSON MySQL. Nie można zatem przypisać przewagi w zapisach binarnemu formatowi dokumentów lub konkretnemu sposobowi ich indeksowania. Są to odrębne właściwości silników, które mają znaczenie dopiero przy zapytaniach korzystających z takich kolumn.

Różnica między formatem wiadomości a formatem danych w bazie jest praktyczna. Aplikacja może wysyłać żądanie JSON, podczas gdy rekordy przechowuje w zwykłych kolumnach relacyjnych. Może też filtrować wewnątrz dokumentu JSON; wtedy kształt ścieżek, operatorów i indeksów staje się częścią obciążenia. Wynik testu katalogu filmów nie pozwala wyliczyć kosztu takiego filtra.

Wybór według obciążenia i zespołu

• Dominują dodawanie, aktualizacja i usuwanie: opisany pomiar przemawia za uwzględnieniem PostgreSQL, szczególnie gdy aplikacja przypomina badany scenariusz krótkich operacji. Znaczenie mają jednak rzeczywiste transakcje, indeksy i rozmiar danych.
• Dominują odczyty: MySQL wypadł lepiej w pobieraniu objętym testem. Przy wyborze trzeba odróżnić pobrania po kluczu od filtrów, sortowania, złączeń i raportów, ponieważ wykonują inną pracę.
• Istotne są zapytania do JSON: w PostgreSQL można rozważyć GIN na jsonb lub indeks na wyrażeniu. W MySQL należy wskazać wydobywaną wartość i zadbać o to, by zapytanie pasowało do indeksu kolumny generowanej.
• Różnica w docelowym obciążeniu jest mała: znajomość silnika przez zespół, istniejące migracje oraz umiejętność diagnozowania wolnych zapytań stają się rozsądnymi kryteriami wyboru.

Dla systemu o mieszanym ruchu najwięcej mówi porównanie tych samych danych i tej samej proporcji operacji, z indeksami przygotowanymi odpowiednio dla każdego silnika. Osobny pomiar czasu SQL oraz całego żądania pokaże, czy opóźnienie powstaje w bazie, czy w warstwie aplikacji. Wtedy przewaga zaobserwowana w jednym rodzaju operacji może zostać odniesiona do jej faktycznego udziału w pracy systemu.

Przeczytaj także:

• Pinecone czy pgvector? Filtry potrafią zmienić zwycięzcę
• ChatGPT czy Claude do kodowania? Wynik zależy od rodzaju zadania
• Lokalny model czy API? Oszczędność znika przy złym wykorzystaniu