SQLite oder PostgreSQL? Gar nicht so einfach!

Wir nehmen unseren Blog bei Twilio sehr ernst. Was gut angenommen wird und was nicht, sehen wir in einem Dashboard, das unsere Metadaten zu jedem Artikel, wie Autor, Team, Produkt, Veröffentlichungsdatum usw., mit den Traffic-Daten von Google Analytics zusammenführt. Benutzende können interaktiv Diagramme und Tabellen erstellen und ganz unterschiedliche Daten vergleichen.

Als ich Anfang 2021 dieses Dashboard entwickelt habe, erschien mir SQLite die perfekte Basis zur Optimierung unseres Blogs, zumal ich damals glaubte, dass diese Anwendung eher ein kleines Nischenprodukt bleiben würde. Ich konnte ja nicht ahnen, dass sie knapp eineinhalb Jahre später in den Blogs von Twilio und SendGrid den täglichen Traffic von fast 8.000 Artikeln nachverfolgen würde, mit etwa 6,5 Millionen Einzeldatensätzen pro Tag und einer Nutzergruppe von mittlerweile mehr als 200 Mitarbeitern.

Irgendwann merkte ich, dass manche Abfragen mehrere Sekunden brauchten, und ich begann mich zu fragen, ob eine robustere Datenbank wie PostgreSQL eventuell besser geeignet wäre. Da ich mich öffentlich zu meiner Abneigung gegen Leistungsbenchmarks bekannt habe, widerstand ich der Versuchung, nach einschlägigen Vergleichen im Internet zu suchen. Stattdessen begann ich in einer Reihe von Experimenten diese beiden Datenbanken einer präzisen Leistungsmessung für meinen spezifischen Anwendungsfall zu unterziehen. Im Folgenden finden Sie eine detaillierte Darstellung meiner Bemühungen, die Ergebnisse meiner Tests (einschließlich einer überraschenden Wendung!), meine Analyse und meine letztendliche Entscheidung, die sich als komplexer erwies als erwartet.

Portierung der Anwendung nach PostgreSQL

Für eine direkte Gegenüberstellung musste ich zunächst sicherstellen, dass ich die Anwendung immer wieder leicht auf SQLite oder PostgreSQL umkonfigurieren konnte. Ich installierte daher PostgreSQL auf meinem Mac-Laptop, um die Anwendung dort zum Laufen zu bringen.

Den Code so anzupassen, dass er mit PostgreSQL kompatibel ist, war relativ einfach, da diese Anwendung SQLAlchemy verwendet. Das ist eine ORM-Bibliothek für Python, mit der ich als Entwickler Schemata und Abfragen mithilfe von Python-Konstrukten erstellen kann, die im Wesentlichen von der Datenbank unabhängig sind. Außerdem erstellte ich mir eine konfigurierbare Datenbankverbindungs-URL, mit der ich sehr leicht zwischen postgresql:// und sqlite:// wechseln konnte. URLs. Den Python-Code brauchte ich nicht zu ändern, da die Unterschiede zwischen beiden Datenbanken intern von SQLAlchemy ausgeglichen wurden. SQLAlchemy hat mir sogar sämtliche Tabellen und Indizes für meine neue PostgreSQL-Datenbank erstellt, und zwar exakt so wie in SQLite.

Zum Abschluss musste ich dann nur noch die in der SQLite-Datenbank abgelegten Daten nach PostgreSQL migrieren. Das habe ich mit einem Open Source-Skript namens pgloader gemacht, das Daten aus mehreren Quellen in PostgreSQL-Datenbanken importieren kann. Zum Glück gehört SQLite zu den von diesem Tool unterstützten Datenquellen.

Die einfachste Möglichkeit, Daten mit pgloader zu importieren, besteht darin, eine Konfigurationsdatei zu erstellen, die die Quelle, das Ziel und die Optionen definiert. Meine sah so aus:

Die einzige Option, die ich brauchte, war data only. Sie sagt pgloader, dass keine Tabellen oder Indizes erstellt, sondern nur die Daten von der Quell- zur Zieldatenbank verschoben werden müssen. Das war wichtig, denn mein Datenbankaufbau sollte ja dem von SQLite so ähnlich wie möglich sein. Das funktioniert am besten, wenn die Tabellen und Indizes von SQLAlchemy erstellt werden.

Bei der Datenmigration wird pgloader mit dem Namen der Konfigurationsdatei als einzigem Argument aufgerufen:

Nach ein paar Versuchen funktionierte der Import ohne Probleme. Die ersten Fehlversuche machten mir einige potenziell schwerwiegende Probleme im Design meiner Datenbank bewusst, die SQLite offensichtlich nicht berichtenswert fand:

• Für die Artikel-Slugs habe ich eine 256 Zeichen lange VARCHAR-Spalte definiert. Vier Artikel hatten längere Slugs, so dass die Datenmigration scheiterte. SQLite mit seinem ungewöhnlichen Datentyp-System war das egal und es speicherte die überlangen Slugs ohne Murren. PostgreSQL setzt Spaltentypen rigoros um, daher konnten diese vier Beiträge nicht migriert werden. Ich musste also die slug-Spalte auf 512 Zeichen vergrößern und einen erneuten Versuch starten.
• Eine meiner Tabellen hatte ursprünglich Integer-Primärschlüssel, was ich irgendwann auch im Zusammenhang mit der Typisierung in UUID-Zeichenfolgen geändert habe. Als ich SQLite mit dieser Änderung von Hand migrierte, übersah ich, das ich die Primärschlüsselspalte dieser Tabelle auf integer belassen hatte. Auch in diesem Fall speicherte SQLite meine UUIDs problemlos in dieser Spalte und indizierte sie sogar, aber für die Migration nach PostgreSQL musste ich das Schema korrigieren und diese Schlüssel als Zeichenfolgen deklarieren.

Ich konnte zu diesem Zeitpunkt aber bereits mit relativ geringem Aufwand zwischen gleichwertigen SQLite- und PostgreSQL-Datenbanken wechseln. Ich musste dazu lediglich die Datenbank-URL in einer Konfigurationsdatei bearbeiten und den Python-Server neu starten.

Ich testete ein paar Abfragen auf meinem Entwicklungslaptop und hatte den Eindruck, dass mit der PostgreSQL-Datenbank alles schneller ging, was ich für den Anfang sehr ermutigend fand.

Erstellen eines Leistungstests

Der nächste Schritt bestand darin, einen automatisierbaren und wiederholbaren Test zu entwickeln, um das System mit beiden Datenbanken testen und einen Sieger ermitteln zu können. Klingt ganz einfach, oder?

Aber ich hatte mein System so konzipiert, dass Nutzende möglichst viele verschiedene Abfragen stellen konnten, so dass ein Test, der alle Möglichkeiten des Systems abbildet, eine ziemliche Herausforderung darstellte. Nutzende können beispielsweise Traffic-Statistiken über einen Zeitraum von Tagen, Wochen oder sogar Jahren abrufen und die Ergebnisse nach Artikel, Autor, Team, Produkt, Computersprache, Schriftsprache oder Veröffentlichungsjahr sortieren. Und dann können sie das Ganze noch nach Ursprungsland aufteilen. Beim Abruf von Artikeln können Nutzende wählen, ob sie zusammen mit dem Original auch noch sämtliche Übersetzungen sehen möchten. Bei allen Abfragen können sie einzelne Ausschnitte des Datensatzes nach einer beliebigen Kombination von Autoren, Teams, Produkten, Computersprachen, Schriftsprachen oder Veröffentlichungsdaten filtern. Und als Datengrundlage können Sie entweder den Twilio- oder den SendGrid-Blog oder beide zusammen nutzen.

Um das Ganze nicht ausarten zu lassen, beschloss ich, mich auf die gängigsten Anwendungsfälle zu beschränken, und erstellte eine entsprechende Liste von API-Anfragen. Dabei kam diese Liste heraus:

Die URLs müssen dann mit zusätzlichen Query-String-Argumenten vervollständigt werden. Die Argumente start und end sind in allen Abfragen erforderlich, da sie den abgefragten Zeitraum festlegen. Da ich festgestellt hatte, dass sie die Antwortzeit erheblich beeinflussen, wollte ich vor allem unterschiedlich lange Zeiträume ausprobieren.

Es gibt zahlreiche Query-String-Argumente, mit denen sich Filter implementieren lassen. Das Argument blog, das ich in die URLs oben eingefügt habe, wählt den Twilio Blog aus, der den meisten Traffic hat. Ich wollte meinen Test nicht mit weiteren Filtern verkomplizieren, zumal ich in den Nutzungsprotokollen gesehen hatte, dass die meisten Abfragen keine Filter enthielten.

Anhand dieser Liste erstellte ich eine kurze Python-Funktion, die diese Abfragen abarbeitet und im Wörterbuch results festhält, wie lange die einzelnen Abfragen dauern:

Die Funktion test() erstellt eine Kopie der Liste requests, sortiert sie völlig zufällig neu und führt dann die Abfrage mit curl als Unterprozess aus. Die Zeit bis zur Zurückgabe einer Antwort wird mithilfe der Funktion timeit() aus der Python-Standardbibliothek gemessen und anschließend unter der entsprechenden URL in das Wörterbuch results eingefügt.

Das Zufallsprinzip verwende ich deshalb auf die Liste, weil ich mehrere Instanzen dieser Funktion parallel laufen lassen möchte, um gleichzeitige Clients zu simulieren. Außerdem gewährleistet die zufällige Reihenfolge der Abfragen, dass die Datenbank zu jedem Zeitpunkt eine Vielzahl von Abfragen bearbeiten muss, und nicht mehrere Instanzen derselben Abfrage.

Die Funktion erhält den Domänenanteil der URL als server-Argument. Ich wollte die Konfiguration einfach halten, damit ich zwischen meinem Entwicklungs- und meinem Produktionssystem hin- und herschalten kann. In dieser Funktion wird auch ein API-Schlüssel übergeben, um die Anfragen zu authentifizieren. Und schließlich werden noch die erforderlichen Query-String-Argumente start und end übergeben und der URL hinzugefügt, bevor die Abfrage gestellt wird.

Das Wörterbuch results nutzt die URLs aus der Liste requests als Schlüssel. Für jeden Schlüssel speichere ich ein Array von Ergebnissen und nicht nur einen einzelnen Wert. Dadurch kann ich mehrere Durchläufe einer Abfrage aufzeichnen und einen Durchschnitt ermitteln.

Zur Vervollständigung meines Testskripts habe ich dann noch eine run_test()-Funktion und einen Befehlszeilenparser hinzugefügt:

Entsprechend dem Argument num_threads startet die Funktion run_test() einen oder mehrere Threads. Sämtliche Threads sind so konfiguriert, dass sie die test()-Funktion parallel ausführen. So lassen sich Lasten von mehreren gleichzeitigen Clients simulieren, die allesamt die Liste der URLs in einer eigenen zufälligen Reihenfolge abarbeiten, was ein Element der Unvorhersehbarkeit einführt.

Sobald alle Threads beendet sind, druckt run_test() die durchschnittliche Abfragezeit für jede einzelne Abfrage-URL und für alle Abfragen zusammen. Das ist jedenfalls der Messwert, für den ich mich in meiner Analyse entschieden habe.

Mithilfe der Befehlszeilenargumente kann ich das Server-Stammverzeichnis, den API-Schlüssel sowie das Start- und Enddatum der Abfrage und die Gleichzeitigkeit übergeben. So kann ich mit diesen Steuerelementen eine Vielzahl von Szenarien testen.

Das Testskript ist damit fertig, fehlen nur noch ein paar Messwerte.

Testausführung am Entwicklungssystem

Das Entwicklungssystem, mit dem ich arbeite, ist ein Mac-Laptop mit 6 Hyperthreading-Kernen und 16 GB RAM. Die Produktionsumgebung für dieses Dashboard ist ein virtueller Linode-Server mit 1 vCPU und 2 GB RAM.

Aus meiner Erfahrung mit Vergleichen weiß ich, dass Ergebnisse von schnelleren und langsameren Systemen nicht immer übereinstimmen. Deshalb möchte ich letztendlich das Produktionssystem testen und meine Entscheidungen auf Basis dieser Plattform treffen.

Aber vorher wollte ich eine erste Runde „Praxistests“ auf meinem Laptop durchführen. Dies diente einerseits dazu, die Funktionsfähigkeit des Testskripts sicherzustellen, und zum anderen, weil mich interessiert hat, wie diese beiden Datenbanken auf einer relativ leistungsstarken Plattform abschneiden.

Dabei habe ich mich für die folgende Testmethode entschieden: Ich teste das System unter den beiden Datenbanken mit Abfragen in einem Zeitraum von einer Woche, einem Monat, einem Quartal und einem Jahr, wobei das Startdatum für alle Abfragen der 01.01.2023 ist. Außerdem wiederhole ich die Tests mit 1, 2 und 4 gleichzeitigen Clients. Bei jedem Test läuft das Skript drei Mal und der beste Durchgang wird aufgezeichnet. Als Messwert nutze ich den Gesamtdurchschnitt aller Abfragen.

Damit erhalte ich 24 Datenpunkte (2 Datenbanken x 4 Abfragezeiträume x 3 Gleichzeitigkeitsebenen). In der folgenden Abbildung ist die Antwortzeit für PostgreSQL (blau) und SQLite (rot) mit einem einzigen Client dargestellt.

Hier zeigt sich sehr deutlich, dass PostgreSQL schneller Ergebnisse zurückgibt und gerade bei kürzeren Abfragen effizienter ist. Die Antwortzeiten von PostgreSQL steigen mit zunehmender Zeitspanne mehr oder weniger linear an. Interessanterweise verläuft die Kurve für SQLite bei den ersten drei Tests nahezu flach und bricht dann beim letzten stark ein.

Unten sehen Sie die Darstellungen für denselben Test mit 2 und 4 gleichzeitigen Clients.

Ich fand interessant, dass eine Änderung der Gleichzeitigkeit bei den meisten Tests die Antwortzeiten nicht wesentlich beeinflusst. Vermutlich liegt das aber daran, dass ich mehrere Kerne auf meinem Laptop habe. Der einzige Fall, der davon abweicht, ist die einjährige Abfrage, wo PostgreSQL proportional schlechter abschneidet als SQLite.

Noch anschaulicher wird dies in der Abbildung unten mit den Antwortzeiten in den PostgreSQL-Tests, in der die drei Ebenen der Gleichzeitigkeit in den einzelnen Abfragezeiträumen verglichen werden.

Der Test bestätigt, dass bei Abfragen mit einer kleinen oder mittleren Anzahl von Zeilen eine Gleichzeitigkeit von bis zu vier Clients keine wesentlichen Auswirkungen auf die Antwortzeiten hat. Mit zunehmender Zahl an beteiligten Zeilen führt hingegen eine höhere Gleichzeitigkeit dazu, dass sich die Abläufe verlangsamen.

Hier dieselbe Übersicht für SQLite:

Und das ist die eigentliche Überraschung: SQLite bewältigt gleichzeitige Lasten viel besser als PostgreSQL. Zwar sind die Antwortzeiten hier länger, aber dass sich die Gleichzeitigkeit weniger auf diese Antwortzeiten auswirkt, ist sehr interessant.

Werden die Ergebnisse auf der Produktionsplattform ähnlich aussehen? Meine Erwartung ist, dass bei einer einzelnen CPU, im Vergleich zu den 6 auf meinem Laptop, die Gleichzeitigkeit eine viel größere Rolle spielt.

Testausführung am Produktionssystem

Gewappnet mit einem inzwischen gut getesteten Benchmarking-Skript, startete ich einen neuen Linode mit einem Klon des Produktionssystems und migrierte die SQLite-Datenbank auf PostgreSQL. Und wieder achtete ich darauf, dass ich problemlos zwischen den Datenbanken wechseln konnte, indem ich die Konfigurationsdatei bearbeitete und den Dienst neu startete.

Ich beschloss außerdem, das Testskript auf meinem eigenen Laptop laufen zu lassen, damit alle Latenzen, die mit dem Datenverkehr über das Internet, der Verschlüsselung und dem Proxying verbunden sind, in die Ergebnisse eingehen. Um Kosten zu sparen, hätte ich die Tests auch auf demselben Host wie den Dienst laufen lassen können. Aber letzten Endes ist es mein Ziel, den Service für die Benutzenden zu verbessern und die kommen um all diese scheinbar unzusammenhängenden Schichten nicht herum.

Hier das Ergebnis bei einer Gleichzeitigkeit von eins:

Und das kam völlig unerwartet. Die Kurven schneiden sich!

PostgreSQL schlägt SQLite bei kurzen und mittellangen Abfragen, hat aber erhebliche Probleme bei der Ein-Jahres-Abfrage, die fast doppelt so lange braucht wie bei SQLite. Und falls Sie das für einen Zufall halten: Ich habe diese Tests mehrmals wiederholt und das Ergebnis war immer dasselbe.

Hier noch die Abbildungen für 2 und 4 Clients auf diesem Produktionsserver.

Auch wenn die Kurven bei allen Stufen der Gleichzeitigkeit noch ziemlich identisch verlaufen, müssen Sie sich nur die Skala der Y-Achsen ansehen. Dann sehen Sie, dass auf dieser Plattform die Antwortzeiten viel stärker von der Gleichzeitigkeit abhängen als auf meinem Laptop. Bei der Ein-Jahres-Abfrage mit vier gleichzeitigen Clients waren die Antwortzeiten mit etwa 14 bzw. 8 Sekunden bei PostgreSQL und SQLite extrem lang.

Ist der Effekt der Gleichzeitigkeit bei PostgreSQL und SQLite identisch? Schauen wir uns die beiden getrennt an. Die nächste Abbildung zeigt, wie sich PostgreSQL bei den vier Zeiträumen mit 1, 2 und 4 gleichzeitigen Clients geschlagen hat.

Für diese Datenbank sind lange Abfragen offensichtlich ein Problem, aber die Antwortzeiten für kleine und mittelgroße Abfragen sind relativ stabil.

Unten dieselbe Übersicht für SQLite:

Hier sieht es ganz anders aus. Bei nur einer CPU spielt in SQLite die Gleichzeitigkeit selbst bei kleinen und mittelgroßen Abfragen durchaus eine Rolle. Die Antwortzeiten steigen weniger als linear, nehmen aber selbst bei kleineren Tests stetig zu.

Um diese Unterschiede weiter zu verdeutlichen, habe ich noch eine weitere Auswertung vorgenommen. Die folgende Abbildung zeigt jeden getesteten Abfragezeitraum in jedem Gleichzeitigkeitspunkt der beiden Datenbanken im Vergleich.

Hier sieht man deutlich, dass PostgreSQL die kleinen und mittelgroßen Abfragen viel besser verarbeiten kann als SQLite. Selbst bei einer Steigerung der Gleichzeitigkeit bleibt die Auswirkung auf die Antwortzeiten gering, was großartig ist. SQLite hingegen ist bei allen Abfragegrößen direkter von der Gleichzeitigkeit betroffen. Und während SQLite bei kleinen und mittelgroßen Abfragen schlechter abschneidet, liegt es bei längeren Abfragen deutlich vor PostgreSQL.

Mehr Leistung durch Feinabstimmung

Bis hierher habe ich mir keine Gedanken um die Konfiguration der Datenbanken gemacht. Ich bin einfach davon ausgegangen, dass die Standardkonfiguration gut genug ist. Im Falle von SQLite gibt es nicht viel zu konfigurieren, PostgreSQL dagegen bietet ein paar Stellhebel.

In den Konfigurationsoptionen für die Speichernutzung bin ich auf die Option work_mem gestoßen. Sie legt fest, ab welchem Speicherverbrauch einer Abfrage Daten in temporären Dateien abgelegt werden. Diese Option ist standardmäßig auf 4 MB festgelegt. Ich habe den Wert auf 16 MB geändert:

Bei den Tests für Woche, Monat und Quartal waren die Antwortzeiten nahezu unverändert. Was sich verbessert hat, sind die längeren Abfragen. Unten sehen Sie die Darstellung für 1, 2 und 4 Clients, nachdem ich die PostgreSQL-Tests für Jahreszeiträume wiederholt habe:

Mit dieser Anpassung schneiden sich die Kurven nicht mehr. Und die Antwortzeiten von PostgreSQL werden selbst bei langen Abfragen kürzer als bei SQLite. Bei einem umfangreicheren Test mit vier Clients und einer Abfrage für ein Jahr sanken die Antwortzeiten mit dem speicheroptimierten Datenbankserver von 14 auf 6 Sekunden. Ich hätte es zwar gern noch kürzer, aber für eine Low-End-Plattform finde ich das durchaus akzeptabel.

Fazit

Sie können sich sicher denken, dass ich nach diesen Ergebnissen auf PostgreSQL umgestiegen bin. Nach dem Upgrade des Produktionssystems befragte ich inoffiziell einige Benutzende zur Leistung und alle fanden das System spürbar schneller als zuvor. Ich hatte also mein Ziel erreicht.

Ich hoffe, dass Sie davon mitnehmen, dass Vergleiche nur dann sinnvoll sind, wenn Sie sie auf Ihrer eigenen Plattform, mit Ihrem eigenen Stack, eigenen Daten und Ihrer eigenen Software durchführen. Und selbst dann müssen Sie unter Umständen noch individuell optimieren, um die bestmögliche Leistung herauszuholen.

Was die weitere Leistungssteigerung betrifft, sehe ich nach diesem Test zwei Möglichkeiten:

• Wenn das Budget ausreicht, kann ich durch zusätzliche CPUs diese längeren Tests vermutlich noch weiter optimieren. Ein dedizierter Server mit 4 CPUs dürfte noch einiges bringen und Antwortzeiten wie die auf meinem Laptop liefern.
• Ohne zusätzliche Investition steckt das größte Potenzial in den längeren Abfragen. Sie lassen sich vereinfachen, wenn die Datenbank zusätzlich zu den Tagesdaten auch vorab berechnete Monatsdaten speichert. Der Traffic für ein Jahr könnte dann aus 12 Zeilen pro Artikel statt aus 365 abgerufen werden. Ohne Ausrichtung an den Monatsgrenzen geht zwar ein Teil der Einsparungen an die komplexere Logik. Aber insgesamt sollten sich die längeren Abfragezeiten damit reduzieren lassen.

Ich hoffe, dass ich Sie mit diesem Artikel inspiriert habe, Ihre eigenen Projekte zu vergleichen und zu optimieren.

Miguel Grinberg ist Leiter der Softwareentwicklung für technische Inhalte bei Twilio. Kontaktieren Sie ihn unter mgrinberg [at] twilio [dot] com, wenn Sie ein interessantes Projekt haben, das Sie in diesem Blog gerne teilen würden.