SQLite czy PostgreSQL? Wybór zależy od zapisów

Jeśli jeden proces aplikacji zapisuje do lokalnej bazy, zalecenia SQLite przemawiają za SQLite, o ile transakcje mogą wykonywać się kolejno. PostgreSQL lepiej odpowiada architekturze, w której niezależne instancje aplikacji potrzebują wspólnej bazy przez sieć albo zapisujące transakcje regularnie na siebie nachodzą.

O wyborze nie rozstrzyga sama liczba użytkowników. Internetowa usługa może obsługiwać wielu klientów i nadal trzymać bazę obok aplikacji na jednym hoście. Pytanie brzmi, gdzie działa kod wykonujący SQL, jak często zapisuje do wspólnych danych i czy oczekiwanie na zakończenie innego zapisu mieści się w wymaganym czasie odpowiedzi.

Lokalny plik czy wspólna baza przez sieć?

SQLite działa wewnątrz aplikacji i przechowuje dane w pliku. To wygodny układ dla programu desktopowego, narzędzia wewnętrznego lub usługi działającej na jednym hoście: nie trzeba uruchamiać osobnego procesu serwera bazy ani utrzymywać połączenia sieciowego między aplikacją a danymi. Użytkownik może łączyć się z usługą zdalnie; istotne jest to, że zapytania SQL wykonuje aplikacja znajdująca się obok pliku.

Gdy kilka serwerów aplikacyjnych ma pracować na jednym zbiorze danych, lokalny plik przestaje być prostym rozwiązaniem. Współdzielenie go przez sieciowy system plików uzależnia działanie bazy od opóźnień i poprawności blokad tego systemu. PostgreSQL udostępnia dane jako usługę: instancje aplikacji łączą się z serwerem bazy, zamiast bezpośrednio otwierać ten sam plik. Ta różnica w rozmieszczeniu aplikacji i danych może rozstrzygnąć wybór przed pomiarem szybkości pojedynczego zapytania.

Rozdzielenie warto ocenić również przy planowanym rozwoju usługi. Jeżeli kolejne instancje aplikacji mają obsługiwać ten sam stan, potrzebują uzgodnionego sposobu dostępu do wspólnych danych. Jeśli każda instalacja programu desktopowego ma własne dane na swoim urządzeniu, centralny serwer bazy dodaje zależność sieciową i pracę administracyjną, których ten model nie wymaga.

Co zmienia współbieżność zapisów?

SQLite dopuszcza wielu czytelników, lecz do konkretnego pliku bazy zapisuje w danej chwili tylko jeden klient. Następni zapisujący mogą poczekać, więc uruchomienie kilku procesów nie oznacza automatycznie konieczności zmiany silnika. Znaczenie ma długość transakcji i częstotliwość, z jaką próbują rozpocząć zapis: krótkie operacje mogą sprawnie przechodzić kolejno, natomiast długie utrzymują kolejkę dla następnych.

PostgreSQL stosuje kontrolę współbieżności MVCC, dzięki której odczyt nie blokuje zapisu, a zapis odczytu tylko dlatego, że operacje zachodzą jednocześnie. Serwer może obsługiwać niezależne transakcje zapisujące równolegle. Gdy zmieniają te same dane, nadal pojawiają się konflikty i blokady; wynik zależy od konkretnych zapytań oraz poziomu izolacji. Przewagą nie jest więc obietnica braku czekania, lecz brak ograniczenia całego pliku do jednego aktywnego zapisującego.

Załóżmy warunkowo, że usługa przyjmuje formularze przez jedną instancję aplikacji. Jeśli zapis pojedynczego formularza trwa krótko, SQLite może wystarczyć nawet wtedy, gdy formularze nadsyła wielu użytkowników. Gdy ta sama usługa stale aktualizuje wspólne rekordy z wielu instancji, ważniejsze stają się przepustowość pod równoczesnym obciążeniem i czas oczekiwania na zapis. Ruch widoczny na stronie może wyglądać podobnie, choć wymagania wobec bazy są inne.

Dlaczego benchmark na jednym hoście może faworyzować SQLite?

W teście aplikacji CISO Assistant SQLite w trybie WAL wygrał większość mierzonych wzorców odczytu na pojedynczym hoście. Aplikacja Django i obie bazy działały na tej samej maszynie; zapytania do SQLite wykonywały się wewnątrz procesu, natomiast PostgreSQL był osiągany przez TCP w sieci kontenerów. Test obejmował krótki przebieg z wirtualnymi użytkownikami, a liczba żądań dla poszczególnych punktów API była mała.

Przy rozbudowanych odpowiedziach aplikacja wykonywała wiele zapytań pomocniczych. Pominięcie połączenia z osobnym serwerem jest wiarygodnym wyjaśnieniem części przewagi SQLite, ale autor testu nie zmierzył osobno kosztu TCP. PostgreSQL wypadał lepiej przy części prostych, ograniczonych odczytów. Zapisy w tym pomiarze wykonywano kolejno, więc wynik nie pokazuje, co stanie się z kolejką przy wielu równoczesnych transakcjach zapisujących.

Inny test zapisów z dostępnym kodem podał 23 403 sekwencyjne operacje INSERT na sekundę dla SQLite oraz 7 740 dla PostgreSQL przy pojedynczym połączeniu. Obie bazy korzystały z tego samego komputera, dysku i schematu tabeli. Konfiguracje różniły się jednak sterownikiem oraz ustawieniami trwałości: SQLite pracował w WAL z synchronous=NORMAL, a PostgreSQL z synchronous_commit=on. Repozytorium mierzy także wzrost przepustowości PostgreSQL przy większej liczbie połączeń, lecz nie zawiera równoważnego pomiaru równoczesnych zapisów SQLite.

Te wyniki odpowiadają na różne pytania: czas obsługi zapytań konkretnej aplikacji oraz przepustowość określonej operacji w wybranych konfiguracjach. Szybki INSERT z jednym połączeniem nie przesądza o czasie odpowiedzi usługi z wieloma zapisującymi klientami. Dla takiej usługi miarodajny pomiar powinien uwzględniać jej rzeczywiste zapytania, długość transakcji, liczbę klientów, sposób połączenia i wymagany poziom trwałości danych.

Kopie zapasowe i dostępność zmieniają koszt wyboru

SQLite ogranicza liczbę elementów wdrożenia, ale lokalny plik wymaga planu tworzenia spójnych kopii i odtwarzania danych. Trzeba też zdecydować, gdzie przechowywać kopie, jeśli urządzenie z aplikacją ulegnie awarii. Dla programu działającego na pojedynczym urządzeniu albo usługi, którą można zatrzymać na czas odtworzenia, taki model bywa prostszy w utrzymaniu niż osobny serwer bazy.

Przy wymaganiu krótszej przerwy lub odtworzenia stanu z wybranego momentu PostgreSQL oferuje mechanizmy do budowy bardziej rozbudowanej architektury. Dokumentacja archiwizacji WAL opisuje połączenie kopii bazowej z zapisami dziennika, odzyskiwanie do wskazanego momentu oraz utrzymywanie serwera zapasowego. Ich użycie oznacza dodatkową konfigurację, miejsce na archiwum, monitorowanie i sprawdzanie procedury odtworzenia. Sama instalacja PostgreSQL nie tworzy gotowego systemu wysokiej dostępności.

Drzewo decyzji dla aplikacji

Jeżeli dane należą do jednej instalacji programu albo jednej usługi na jednym hoście, a transakcje zapisujące są krótkie i mogą czekać na swoją kolej, zacznij od SQLite. Dostęp użytkowników do aplikacji przez internet nie zmienia lokalnego położenia bazy.
Jeżeli niezależne instancje aplikacji mają korzystać ze wspólnych danych przez sieć, wybierz architekturę klient–serwer z PostgreSQL. Ten sam kierunek ma sens, gdy kolejka równoczesnych zapisów przekracza akceptowany czas odpowiedzi.
Jeżeli potrzebujesz serwera zapasowego albo odzyskiwania do określonego momentu, uwzględnij możliwości PostgreSQL razem z kosztem ich uruchomienia i utrzymania. Dla obu rozwiązań określ wymagany czas przywrócenia danych.
Jeżeli oba silniki pasują do rozmieszczenia aplikacji i wymagań dostępności, rozstrzygający będzie pomiar własnego obciążenia. Porównaj je przy podobnych wymaganiach trwałości i liczbie klientów odpowiadającej planowanemu wdrożeniu.

Przeczytaj także: