SQLite of PostgreSQL: lokale snelheid tot schrijfdruk groeit

Voor een webapp op één machine met vooral leesverzoeken en korte schrijfacties is SQLite vaak een praktische keuze: de keuzehulp van SQLite noemt lokale opslag passend en vermeldt dat per databasebestand slechts één schrijver tegelijk actief kan zijn. Moeten meerdere applicatieservers dezelfde gegevens bijwerken, of wachten schrijvers geregeld op elkaar, dan past PostgreSQL doorgaans beter.

De keuze hangt minder af van het bezoekersaantal dan van de plaats van de database en de verdeling van het schrijfwerk. Een afzonderlijke lokale query kan snel zijn terwijl gelijktijdige verzoeken zich voor dezelfde schrijflock opstapelen. Omgekeerd hoeft een webapp met veel lezers en korte, verspreide schrijfacties geen aparte databaseserver te gebruiken.

Wat de lokale benchmark wel en niet vergelijkt

In de benchmark van André Casal verwerkte SQLite op een Apple M2 Pro met één verbinding 23.403 opeenvolgende inserts per seconde, tegenover 7.740 voor PostgreSQL; in een afzonderlijke proef haalde PostgreSQL met zestien verbindingen 35.370 inserts per seconde. Beide databases stonden op dezelfde machine en SSD. SQLite gebruikte een bestand in WAL-modus met synchronous=NORMAL, PostgreSQL een lokale installatie met synchronous_commit=on; ook de gebruikte stuurprogramma’s verschilden.

Het hogere SQLite-cijfer beschrijft dus afzonderlijke lokale bewerkingen onder die instellingen. Het hogere PostgreSQL-cijfer beschrijft gezamenlijke capaciteit bij gelijktijdige verbindingen: de proeven hadden een andere belasting en vormen geen rechtstreekse wedstrijd tussen evenveel schrijvers. Een PostgreSQL-server op afstand, schrijfacties vanaf meerdere applicatieservers en complexe joins zaten niet in deze meting. Daardoor geeft de proef een bruikbaar voorbeeld van het verschil tussen latentie en doorvoer, maar geen vaste verkeersgrens waarop iedere webapp van database moet wisselen.

Eén applicatieserver: lokale toegang zonder extra dienst

Wanneer applicatiecode en databasebestand op dezelfde machine staan, voert SQLite SQL uit binnen het applicatieproces. Er is geen afzonderlijke databaseserver of verbinding met zo’n dienst nodig voor iedere bewerking. Dat vereenvoudigt installatie en beheer en kan de tijd per verzoek drukken. De winst telt vooral wanneer schrijftransacties kort blijven en verzoeken elkaar zelden in de weg zitten.

Een druk bezochte pagina is op zichzelf daarom geen argument tegen SQLite. Leesverzoeken kunnen naast elkaar plaatsvinden; de relevante vraag is hoe vaak een verzoek de schrijver bezet houdt en hoe lang dat duurt. Een transactie die wacht op een externe dienst of veel werk doet voordat zij afrondt, houdt die plek langer bezet dan een korte wijziging. Ook back-ups en herstel blijven onderdeel van het beheer, al hoeft er geen aparte databaseserver beschikbaar te blijven.

Meerdere applicatieservers: één gedeelde waarheid vraagt een ontwerp

Bij een tweede applicatieserver ontstaat een ander probleem. Een lokaal SQLite-bestand op elke server betekent twee afzonderlijke datasets. Heeft de toepassing onmiddellijk dezelfde actuele gegevens nodig, dan is een centrale schrijfdienst of een vorm van replicatie en coördinatie nodig. De eenvoud van het ene lokale bestand verdwijnt dan deels, ook wanneer de database zelf nog klein is.

Een gedeeld bestand op een netwerkschijf is geen eenvoudige voortzetting van de gebruikelijke WAL-opzet. Volgens de WAL-documentatie van SQLite moeten alle processen die zo’n database gebruiken op dezelfde host draaien; WAL laat lezers naast een schrijver werken, maar geen gelijktijdige schrijvers. Langlopende leestransacties kunnen bovendien een checkpoint ophouden. PostgreSQL biedt applicatieservers een centrale database via verbindingen met een databaseserver. Daarvoor zijn bereikbaarheid, verbindingen en back-ups te beheren, maar de applicatieservers hoeven niet elk hun eigen actuele kopie te onderhouden.

Veel schrijvers: reken met bezetting en wachttijd

Voor aanhoudende schrijfdruk telt de totale tijd dat SQLite’s enige schrijver bezet is. PostgreSQL gebruikt multiversieconcurrentie volgens de PostgreSQL-documentatie: gewone leesacties blokkeren schrijfacties niet en omgekeerd. Het systeem kan meerdere verbindingen tegelijk verwerken. Transacties die dezelfde rij willen wijzigen kunnen nog steeds op elkaar wachten; meer verbindingen betekenen dus niet automatisch meer nuttige doorvoer.

Een zuiver hypothetische berekening laat zien waarom een snelle losse transactie toch een wachtrij kan veroorzaken. Stel dat een schrijftransactie de enige SQLite-schrijver gemiddeld 15 milliseconden bezet. Bij 40 van zulke transacties per seconde is samen 600 milliseconden schrijftijd nodig. Bij 80 transacties is dat 1.200 milliseconden werk voor iedere seconde beschikbare schrijftijd. Houdt die belasting aan, dan komen verzoeken sneller binnen dan de schrijver ze kan afwerken.

Dit is een bezettingsmodel, geen gemeten limiet voor SQLite: echte transacties duren niet allemaal even lang en verkeer komt vaak in pieken. De bruikbare signalen zijn daarom de duur van schrijftransacties, de tijd die verzoeken op een schrijflock wachten en de wachttijd tijdens pieken. Korter schrijfwerk kan ruimte scheppen. Blijven veel onafhankelijke schrijfacties tegelijk aankomen, dan wordt PostgreSQL aantrekkelijker, mits de toepassing ook daadwerkelijk meerdere verbindingen kan benutten.

De overstap later heeft meer gevolgen dan een nieuw adres

SQLite kiezen voor een compacte opzet kan verstandig zijn, maar een latere verhuizing naar PostgreSQL raakt ook gegevens en applicatiecode. Het overzicht van SQLite’s typegedrag beschrijft hoe een kolom soms een waarde accepteert die een strikter getypeerde database zou afwijzen. SQLite heeft bovendien geen afzonderlijke BOOLEAN- of DATETIME-datatypen; de toepassing bepaalt hoe zulke waarden worden opgeslagen. Bij migratie kunnen bestaande waarden, schema’s en aannames in queries daarom aanpassing vragen.

Wie meer applicatieservers verwacht, kan die toekomstige kosten meewegen voordat de lokale snelheidswinst de keuze bepaalt. Welke gegevens moeten overal direct actueel zijn? Kunnen schrijfacties via één dienst lopen, of moeten meerdere servers zelfstandig kunnen schrijven? Als gedeelde toegang vanaf het begin een harde eis is, sluit PostgreSQL directer aan op die architectuur. Blijft de toepassing op één machine en is de wachttijd voor schrijven beperkt, dan behoudt SQLite zijn voordeel van lokale toegang en eenvoudig beheer.