Der Teil von PostgreSQL, den wir am meisten hassen (2023)

 (cs.cmu.edu)
4 Punkte von GN⁺ 2024-10-21 | 1 Kommentare | Auf WhatsApp teilen
  • PostgreSQL gilt in letzter Zeit fast als die Standardwahl im Internet, aber nach den Erfahrungen von Carnegie Mellon und OtterTune verursacht die MVCC-Implementierung mehr Betriebsaufwand als bei MySQL, Oracle oder Microsoft SQL Server
  • Kerndesign sind ein append-only-Speichermodell, bei dem beim Aktualisieren einer Zeile die bestehende Zeile nicht überschrieben, sondern eine neue physische Version erzeugt wird, sowie eine O2N-Kette, die von alten zu neuen Versionen führt
  • Diese Struktur führt zu vollständigem Tupelkopieren, zur Anhäufung von dead tuples, zur Aktualisierung aller Indizes und zur Abhängigkeit von autovacuum; nur wenn HOT-Updates möglich sind, lassen sich einige Kosten vermeiden
  • In der Analyse von PostgreSQL-Systemen bei OtterTune nutzten im Schnitt nur etwa 46 % der Updates die HOT-Optimierung, und die Standardkonfiguration von autovacuum kann die Bereinigung bei großen Tabellen lange verzögern
  • PostgreSQL bleibt dennoch ein attraktives DBMS, aber bei schreibintensiven Workloads und großen Tabellen müssen Speicherplatz, I/O, Arbeitsspeicher, Index-Wartung und der Betrieb von vacuum aktiv selbst gemanagt werden

Warum PostgreSQL-MVCC problematisch ist

  • Datenbanken gibt es viele — laut DBDB waren im April 2023 ganze 897 registriert —, aber je nach Zeit gab es faktisch jeweils eine Standardwahl
    • In den 2000ern war MySQL, das von Google und Facebook genutzt wurde, die konventionelle Wahl
    • In den 2010ern bekam MongoDB mit seinem „webscale“-Image viel Aufmerksamkeit
    • In den letzten fünf Jahren wurde PostgreSQL wegen Stabilität, Funktionsvielfalt, Erweiterbarkeit und Eignung für operative Workloads breit bevorzugt
  • Der Fokus des Artikels liegt auf der Multi-Version Concurrency Control (MVCC)-Implementierung von PostgreSQL
    • Laut Forschung von Carnegie Mellon und Erfahrungen bei der Optimierung von Amazon RDS PostgreSQL ist die MVCC-Implementierung von PostgreSQL schlechter als die von MySQL, Oracle und Microsoft SQL Server
    • Amazon Aurora PostgreSQL hat dieselben strukturellen Probleme

Zweck von MVCC und die Designentscheidung von PostgreSQL

  • MVCC ist ein Verfahren, damit mehrere Queries möglichst gleichzeitig aus der Datenbank lesen und in sie schreiben können, ohne sich gegenseitig zu blockieren
    • Das DBMS überschreibt bestehende Zeilen nicht, sondern hält mehrere physische Versionen pro logischer Zeile vor
    • Queries lesen abhängig von einer Versionsreihenfolge, etwa dem Erstellungszeitpunkt, die für sie passende Version
    • Dadurch wird Snapshot Isolation möglich, bei der man den Datenbankzustand zum Startzeitpunkt einer Transaktion sieht
  • Leseoperationen geraten dadurch seltener wegen Schreiboperationen, die denselben Eintrag aktualisieren, an explizite Record-Locks
  • Ein MVCC-DBMS muss im Wesentlichen drei Dinge entscheiden
    • Wie Updates bestehender Zeilen gespeichert werden
    • Wie zur Laufzeit die passende Zeilenversion für eine Query gefunden wird
    • Wie abgelaufene Versionen entfernt werden, die nicht mehr sichtbar sind
  • Wegen der ersten Entscheidung, die PostgreSQL in den 1980ern getroffen hat, trägt es auch in den beiden anderen Bereichen bis heute Ballast mit sich

append-only-Versionsspeicherung

  • PostgreSQL wurde von Anfang an mit Unterstützung für mehrere Versionen entworfen; bei einem Update wird eine bestehende Zeile nicht überschrieben, sondern kopiert und die Änderung auf die neue Version angewendet
  • Das lässt sich als append-only-Versionsspeicherung verstehen
    • Wenn ein bestehendes Tupel aktualisiert wird, reserviert das DBMS einen freien Slot für die neue Zeilenversion in der Tabelle
    • Der Inhalt der aktuellen Zeile wird in die neue Version kopiert
    • Die Änderungen werden auf den neu angelegten Versionsslot angewendet
  • Die Beispieltabelle ist eine movies-Tabelle mit Filminformationen
    • id ist der Primärschlüssel, dazu kommen die Spalten name, year und director
    • Es gibt den Primärindex movies_pkey sowie die sekundären B+Tree-Indizes idx_name und idx_director
  • Bei einem Update, das das Erscheinungsjahr von "Shaolin and Wu Tang" von 1985 auf 1983 ändert, wird das ursprüngliche Tupel kopiert und das geänderte Jahr auf die neue Version angewendet
    • Wenn auf der bestehenden Page kein Platz ist, kann die neue Version auf einer anderen Tabellen-Page angelegt werden

Versionsketten und das O2N-Modell

  • Wenn mehrere physische Versionen dieselbe logische Zeile repräsentieren, muss das DBMS die Beziehung zwischen diesen Versionen festhalten
    • Ein MVCC-DBMS bildet dafür Versionsketten als einfach verkettete Listen
    • Die Kette ist nur in eine Richtung verlinkt, um Speicher- und Wartungskosten zu reduzieren
  • Es gibt zwei mögliche Ordnungen von Versionsketten
    • N2O: Die neueste Version zeigt auf die vorherige Version, und der Kopf der Kette ist immer die neueste Version
    • O2N: Jede Version zeigt auf die neuere Version, und der Kopf der Kette ist die älteste Version
  • Die meisten DBMS, darunter Oracle und MySQL, implementieren N2O
  • PostgreSQL verwendet — mit Ausnahme der In-Memory-OLTP-Engine von Microsoft SQL Server — ungewöhnlicherweise O2N
    • O2N kann den Bedarf reduzieren, bei jeder Tupeländerung Indizes auf die neue Version umzubiegen
    • Dafür muss man unter Umständen lange Versionsketten traversieren, um die neueste Version zu finden
  • Das Feld t_tcid im PostgreSQL-Zeilenheader enthält die Tupel-ID der nächsten Version oder, falls es die neueste Version ist, die eigene Tupel-ID
    • Wenn ein Index auf eine alte Version zeigt, muss PostgreSQL der Kette folgen, um die neue Version zu finden

Wie Indizes das Traversieren von Ketten reduzieren sollen

  • PostgreSQL-Entwickler kannten von Anfang an zwei Kostenpunkte
    • Die Kosten, bei jedem Update eine neue vollständige Tupelkopie zu erzeugen, sind hoch
    • Für die meisten Queries ist es Verschwendung, die gesamte Versionskette zu durchlaufen, um die gewünschte neueste Version zu finden
  • Um langes Traversieren zu vermeiden, fügt PostgreSQL Indexeinträge für jede physische Version einer Zeile hinzu
    • Wenn eine logische Zeile fünf physische Versionen hat, kann es für dieses Tupel bis zu fünf Indexeinträge geben
    • Wenn der Index idx_name auf mehrere Versionen von "Shaolin and Wu Tang" zeigt, kann PostgreSQL direkt auf die neueste Version zugreifen
  • Der Zugriff auf die neueste Version kann dadurch schneller werden, aber die Indizes werden größer und ihre Wartung teurer

HOT-Update-Optimierung

  • PostgreSQL verwendet HOT (heap-only tuple) updates, um Situationen zu verringern, in denen verwandte Versionen über mehrere Pages verteilt sind und mehrere Indexeinträge entstehen
  • Zwei Bedingungen müssen für HOT-Updates erfüllt sein
    • Das Update ändert keine Spalten, auf die Tabellenindizes verweisen
    • Auf derselben Datenseite ist genug Platz, um die neue Version zusammen mit der vorherigen Version zu speichern
  • Wenn HOT greift, zeigen die Indizes weiterhin auf die alte Version, und Queries folgen der Versionskette zur neuesten Version
  • PostgreSQL führt im laufenden Betrieb auch Optimierungen aus, die alte Versionen entfernen und so Versionsketten zurückschneiden

Entfernen von dead tuples und vacuum

  • Weil PostgreSQL bei jedem Update eine Zeilenkopie erzeugt, müssen alte Versionen — dead tuples — entfernt werden
  • Im frühen PostgreSQL der 1980er wurden dead tuples nicht entfernt
    • Die Idee war, durch das Beibehalten alter Versionen Time-Travel-Queries zu ermöglichen, die den Datenbankzustand zu einem bestimmten Zeitpunkt anzeigen
    • Wenn dead tuples aber nicht entfernt werden, schrumpfen Tabellen trotz Löschungen nicht, und Versionsketten häufig aktualisierter Tupel werden immer länger
  • PostgreSQL bereinigt dead tuples mit dem vacuum-Verfahren
    • vacuum scannt die seit dem letzten Lauf geänderten Tabellen-Pages sequentiell und sucht nach abgelaufenen Versionen
    • Eine Version gilt als expired, wenn sie für aktive Transaktionen nicht sichtbar ist
    • Da aktuelle Transaktionen nicht mehr auf diese Version zugreifen und zukünftige Transaktionen die neueste live-Version verwenden, kann der Speicherplatz gefahrlos wiederverwendet werden
  • PostgreSQL kann autovacuum je nach Konfiguration regelmäßig automatisch ausführen
    • Mit globalen Einstellungen lässt sich die vacuum-Häufigkeit für alle Tabellen steuern
    • Zusätzlich gibt es autovacuum-Einstellungen auf Tabellenebene
    • Nutzer können vacuum auch manuell mit dem SQL-Befehl VACUUM auslösen

Problem 1: vollständiges Tupelkopieren

  • Bei append-only-MVCC wird selbst dann, wenn sich nur eine Spalte eines Tupels ändert, die komplette Zeile in die neue Version kopiert
  • Das erhöht Datenredundanz und Speicherbedarf erheblich
    • PostgreSQL kann mehr Arbeitsspeicher und Festplattenspeicher benötigen als andere DBMS, um dieselbe Datenbank zu speichern
    • In der Folge können Queries langsamer werden und Cloud-Kosten steigen
  • MySQL und Oracle speichern komprimierte Deltas zwischen neuer und aktueller Version
    • Wenn sich in einer Tabelle mit 1000 Spalten nur eine Spalte ändert, wird nur ein Delta-Record für diese eine geänderte Spalte gespeichert
    • PostgreSQL erzeugt dagegen eine neue Version mit der einen geänderten und den 999 unveränderten Spalten
  • PostgreSQLs TOAST-Attribute werden anders behandelt und sind deshalb aus diesem Vergleich ausgenommen
  • EnterpriseDB startete 2013 das zheap-Projekt, um die append-only-Storage-Engine auf ein delta-basiertes Versionsmodell umzustellen
    • Das letzte offizielle Update war ein Statusbeitrag aus dem Jahr 2021
    • Seitdem ist kein klarer Fortschritt erkennbar

Problem 2: Tabellen-Bloat

  • Abgelaufene Versionen in PostgreSQL, also dead tuples, belegen mehr Platz als Delta-Versionen
  • In write-heavy-Workloads können sich dead tuples schneller ansammeln, als autovacuum sie bereinigen kann
    • Tabellen können dadurch immer weiter wachsen
    • Weil dead tuples und live tuples auf den Pages gemischt liegen, müssen während der Query-Ausführung auch dead tuples in den Speicher geladen werden
    • Unkontrollierter Bloat erhöht IOPS- und Speicherverbrauch bei Table Scans und verschlechtert so die Query-Performance
    • Wenn dead tuples Optimizer-Statistiken verfälschen, kann das zu schlechten Query-Plänen führen
  • Beispiel: Wenn eine movies-Tabelle 10 Millionen live tuples und 40 Millionen dead tuples enthält, sind 80 % davon veraltete Daten
    • Bei einer durchschnittlichen Tupelgröße von 1 KB belegen die live tuples 10 GB, die dead tuples etwa 40 GB
    • Die gesamte Tabelle ist dann 50 GB groß
    • Bei einem Full Table Scan muss PostgreSQL die 50 GB von Disk lesen und in den Speicher holen, obwohl der Großteil veraltet ist
  • PostgreSQL hat zwar Schutzmechanismen dagegen, dass sequentielle Scans den Buffer-Pool-Cache verschmutzen, aber die I/O-Kosten selbst verschwinden dadurch nicht

Unterschied zwischen VACUUM und VACUUM FULL

  • Selbst wenn autovacuum regelmäßig läuft und mit dem Workload Schritt hält, gibt normales autovacuum den freigewordenen Speicher nicht an das Betriebssystem zurück
  • Normales VACUUM entfernt dead tuples und ordnet live tuples innerhalb einzelner Pages neu an, gewinnt aber keine leeren Pages auf der Disk zurück
  • Wenn PostgreSQL die letzte Page leeren kann, kann diese abgeschnitten werden, aber andere Pages bleiben auf der Disk erhalten
    • Selbst wenn in einer 50-GB-Tabelle 40 GB an dead tuples entfernt werden, kann PostgreSQL aus Sicht des Betriebssystems oder von RDS weiterhin 50 GB zugewiesenen Speicher belegen
  • Um ungenutzten Speicher tatsächlich zurückzugeben, muss die gesamte Tabelle mit VACUUM FULL oder pg_repack in neuen Speicher umgeschrieben werden
    • Beide Vorgänge sind ressourcenintensiv und dauern lange
    • In produktiven Datenbanken können sie die Query-Performance stark beeinträchtigen
  • VACUUM FULL entfernt dead tuples auf jeder Page, packt die verbleibenden live tuples kompakt auf neue Pages und löscht danach überflüssige Pages

Problem 3: Wartung sekundärer Indizes

  • PostgreSQL muss beim Aktualisieren eines Tupels alle Indizes der betreffenden Tabelle aktualisieren
  • Das liegt daran, dass sowohl Primär- als auch Sekundärindizes die exakte physische Position einer Version speichern
    • Außer bei HOT-Updates, bei denen die neue Version auf derselben Page liegt wie die alte, ist dieser Schritt bei jedem Update nötig
  • Im Beispiel-Update erzeugt PostgreSQL die neue Version auf Table Page #2 und fügt danach Einträge in movies_pkey, idx_director und idx_name ein, die auf diese neue Version zeigen
  • Diese Architektur, bei der alle Indizes geändert werden müssen, verursacht mehrere Performance-Kosten
    • Update-Queries werden langsamer
    • Es entsteht zusätzliche I/O, um jeden Index zu durchsuchen und neue Einträge einzufügen
    • In internen Strukturen wie Indizes und Buffer-Pool-Page-Tabellen entsteht Lock-/Latch-Contention
    • Auch Indizes, die von echten Queries gar nicht genutzt werden, verursachen Wartungsaufwand
    • Bei DBMS wie Amazon Aurora, die nach IOPS abrechnen, sind die zusätzlichen Reads und Writes besonders problematisch
  • In der Analyse von PostgreSQL-Datenbanken bei OtterTune nutzten im Durchschnitt nur etwa 46 % der Updates die HOT-Optimierung
    • Die übrigen gut 50 % der Updates tragen die Kosten der Index-Wartung
  • Ein typisches Beispiel für dieses Problem ist Ubers Beitrag von 2016 zur Migration von Postgres zu MySQL
    • Dort führten write-heavy-Workloads auf Tabellen mit vielen sekundären Indizes zu großen Performance-Problemen
  • Oracle und MySQL haben dieses Problem nicht, weil sekundäre Indizes dort nicht die physische Adresse der neuen Version speichern
    • Sekundärindizes speichern stattdessen einen logischen Identifikator wie Tupel-ID oder Primärschlüssel
    • Das DBMS nutzt diesen logischen Identifikator, um die physische Adresse der aktuellen Version zu finden
    • Das Lesen über Sekundärindizes kann dadurch langsamer sein, aber andere Vorteile ihrer MVCC-Implementierung reduzieren den Overhead

Problem 4: betriebliche Schwierigkeit von autovacuum

  • Die Performance von PostgreSQL hängt stark von der Wirksamkeit von autovacuum ab, das veraltete Daten entfernt und die Wiederverwendung von Speicherplatz ermöglicht
  • RDS, Aurora und Aurora Serverless sind alles PostgreSQL-Varianten und haben daher dieselben autovacuum-Probleme
  • autovacuum ist komplex und schwer optimal zu betreiben
    • Die Standardkonfiguration passt nicht zu allen Tabellen
    • Besonders bei großen Tabellen wird das Problem gravierender
  • Der Standardwert von autovacuum_vacuum_scale_factor liegt bei 20 %
    • Bei einer Tabelle mit 100 Millionen Tupeln müssen mindestens 20 Millionen Tupel aktualisiert werden, bevor autovacuum ausgelöst wird
    • Dadurch können viele dead tuples lange in der Tabelle verbleiben und I/O- sowie Speicherkosten verursachen
  • Long-running transactions können autovacuum blockieren
    • Wenn sich das Aufräumen expired Versions verzögert, sammeln sich dead tuples und veraltete Statistiken an
    • Performance-Probleme können wiederum mehr long-running transactions erzeugen, die ihrerseits autovacuum blockieren — ein Teufelskreis
    • In solchen Fällen kann es nötig sein, long-running transactions manuell zu beenden

Fallbeispiele von OtterTune-Kunden

  • In einer PostgreSQL-Datenbank auf Amazon RDS veränderte sich die Zahl der dead tuples über zwei Wochen in einem Sägezahnmuster
    • autovacuum führte ungefähr einmal pro Tag eine größere Bereinigung durch
    • Am 14. Februar räumte das DBMS 3,2 Millionen dead tuples auf
    • Über den gesamten Graphen hinweg zeigte die Zahl der dead tuples jedoch einen Aufwärtstrend — ein anomaler Zustand, in dem autovacuum nicht hinterherkam
  • Auf einer PostgreSQL-RDS-Instanz eines OtterTune-Kunden trat nach einer Bulk-Insertion wegen veralteter Statistiken eine long-running query auf
    • Diese Query verhinderte, dass autovacuum die Statistiken aktualisierte
    • Dadurch traten weitere long-running queries auf
    • OtterTunes automatischer Health Check identifizierte das Problem, aber der Administrator musste die Query manuell beenden und nach der Bulk-Insertion ANALYZE ausführen
    • Die Laufzeit der betreffenden langen Query sank von 52 Minuten auf 34 Sekunden

Praktisches Fazit

  • Beim DBMS-Design gibt es immer schwierige Entscheidungen, und je nach Wahl fällt die Performance für unterschiedliche Workloads anders aus
  • Bei einem bestimmten write-intensiven Workload von Uber war die Schreibverstärkung in Indizes durch PostgreSQLs MVCC ein Grund für den Wechsel zu MySQL
  • Die MVCC-Implementierung von PostgreSQL gilt als ein Ansatz, dem man beim Bau eines neuen MVCC-DBMS nicht folgen sollte
    • Die Kombination aus append-only-Speicherung und autovacuum ist das Kernproblem
    • Dieses Design ist ein Erbe der 1980er und stammt aus einer Zeit vor der weiten Verbreitung log-structured Systemmuster seit den 1990ern
  • PostgreSQL bleibt dennoch ein bevorzugtes DBMS, muss aber mit Bewusstsein für die Schwächen seines MVCC betrieben werden
  • Der praktische Ausweg besteht darin, viel Zeit und Aufwand in Tuning zu investieren

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2024-10-21
Hacker-News-Kommentare

  • Obwohl ich dachte, dass ich die Interna von Postgres ziemlich gut kenne, war dieser Artikel hervorragend und ich habe viel gelernt.
    Eine der grundlegenden Schwächen scheint zu sein, dass sich Postgres für O2N statt N2O zur Verfolgung von Zeilenversionen entschieden hat. Ein Wechsel zu N2O würde nicht alle Probleme lösen — etwa bliebe das Problem bestehen, vollständige Zeilenkopien zu speichern —, aber aus einer 80/20-Perspektive könnte das die meisten Nachteile der aktuellen Implementierung wohl verringern.
    Die meisten Transaktionen werden die neueste Zeilenversion wollen; mit N2O-Reihenfolge müsste man der verketteten Liste nur folgen, wenn ältere Versionen gebraucht werden, und wahrscheinlich müsste man nicht jede Zeilenversion im Index speichern.

    • Andys YouTube-Vorlesung History of Databases (CMU Databases / Spring 2020) ist sehenswert.
      Die komplette erste Vorlesung hält er, nachdem er nicht ins Hotel kommt, auf den Straßen von Amsterdam; auch als Persönlichkeit ist er interessant, und seine Fähigkeit, interne Abläufe zu erklären, ist enorm.

  • Ein großer Vorteil ist, dass kein zusätzlicher Speicherplatz nötig ist, wenn die Workload hauptsächlich aus INSERTs und anschließendem Löschen der Tabelle besteht.
    Im Allgemeinen muss man Einfüge-Transaktionen auch nicht aufteilen. Es gibt faktisch keine Grenze für die Größe der erzeugten Daten oder die Gesamtzahl der geänderten Zeilen. Es gibt zwar eine Begrenzung der Anzahl von Statements in einer Transaktion, aber solange man Tabellen nicht zu häufig ändert, kann man das mit COPY FROM umgehen.
    Aus DBA-Sicht muss Rollback-/Undo-Speicher nicht getrennt vom Tabellenspeicher verwaltet werden. Das hängt von der Anwendung ab, aber das PostgreSQL-Design ist keineswegs in jeder Hinsicht der Verlierer. Es ist nicht so etwas wie Bubble Sort.

    • Bei der Spieleentwicklung Anfang der 2000er habe ich gelernt, dass auch Bubble Sort nicht in jeder Hinsicht der Verlierer ist.
      Bei fast sortierten Listen ist die Performance gut, und genau das hat man beim Sortieren von Objekten in 3D-Rendering nach Entfernung zur Kamera. Wenn sich die Kamera nur leicht bewegt oder dreht, funktioniert Bubble Sort beim erneuten Sortieren auf Basis der Reihenfolge des vorherigen Frames sehr gut.
      Um den Worst Case zu vermeiden, kann man die Anzahl fehlgeschlagener Vergleiche im letzten Durchlauf und die bisherige Zahl der Durchläufe mitzählen und ab einem Schwellwert auf einen anderen Sortieralgorithmus wechseln.
    • Bubble Sort ist auf Hardware oder bei fast sortierten Mengen hervorragend.
    • Im Artikel wird wörtlich gesagt, dass das MVCC-Design von pg aus den 90ern stammt und heute niemand mehr so etwas macht.
      Es ist über 30 Jahre alte Technik; vielleicht also nicht in jeder Hinsicht der Verlierer, aber in den wichtigsten Aspekten meiner Meinung nach schon.

  • Ich halte insbesondere den folgenden Teil des Artikels für falsch:
    „In den 2000ern entschied sich die konventionelle Weisheit für MySQL, weil aufstrebende Tech-Stars wie Google und Facebook es nutzten. In den 2010ern war es MongoDB, das dank nicht-dauerhafter Schreibvorgänge zu ‘Webscale’ wurde. In den vergangenen fünf Jahren ist PostgreSQL zum vom Internet geliebten DBMS geworden. Dafür gibt es gute Gründe! Es ist zuverlässig, funktionsreich, erweiterbar und passt gut zu den meisten operationalen Workloads.“
    Clevere Engineers haben Postgres nicht wegen eines Popularitätsarguments gewählt, sondern wegen Datensicherheit, ACID, der Ähnlichkeit zu Oracle, MVCC, SQL-Standardkonformität, des Postgres-Teams, der großartigen und hilfsbereiten Community, der Datentypen, der hohen Performance und der Flexibilität der BSD-Lizenz.
    Das waren Anfang der 2000er auch die Gründe, aus denen ATT sich für Postgres entschied, und Oracle-DBAs konnten den Wechsel sehr leicht akzeptieren. Während MySQL einen rauen Übergang durchlief, ist PG immer stärker geworden und hat sich weiter verbessert. Ich denke, Bruce Momjian hatte großen Anteil an diesem Erfolg, und die Community ist wirklich hervorragend.

    • Ähnlich verschob sich 2005 meine Präferenz von MySQL zu PostgreSQL.
      Der Grund war, dass ich Datenbank-Views nutzen wollte, um eine „Live“-Kompatibilitätsschicht zwischen einem alten AS400-Datenbankschema und einer modernen Rails-App zu bauen.
      Danach wuchs die Präferenz weiter, wegen Datensicherheit, DDL innerhalb von Transaktionen und ähnlicher Dinge.

  • „MySQL und Oracle speichern ein komprimiertes Delta zwischen der neuen und der aktuellen Version (man kann sich das wie git diff vorstellen).“
    Git speichert bekanntlich keine Diffs; speichert es nicht vielmehr, ähnlich wie Postgres hier, das neue Objekt und das vorherige Objekt jeweils vollständig?

    • Der zitierte Satz sagt nicht, dass Git Diffs speichert, sondern dass das, was MySQL und Oracle speichern, ähnlich wie ein git diff ist.
    • Genau. Jede Version einer Datei ist ein eigener Blob.
      Zur Beschleunigung von Klonvorgängen gibt es Packen zur Komprimierung, aber die Rohform, mit der Git arbeitet, sind solche Blobs.
    • Der Vergleich bezog sich nicht auf die Speicherweise von Git, sondern war eine Analogie dazu, dass die Speicherung in MySQL und Oracle dem git-diff-Format ähnelt.
      Git-Repositories komprimieren ebenfalls, und diese Komprimierung ist in gewisser Weise diff-basiert, aber nicht auf der Commit-Historie basierend, wie man naiv erwarten würde.
    • Andere haben zwar „git diffs“ gesagt, aber Git verwendet tatsächlich Deltas als Low-Level-Optimierung in Packfiles, ähnlich wie im MySQL-Vergleich.
      Bei SQL-Abfragen werden Diffs aber trotzdem nicht direkt zurückgegeben.
    • Ein Git-Diff wird on the fly erzeugt, aber ein Diff bleibt trotzdem ein Diff.

  • „Die Notwendigkeit, dass PostgreSQL bei jedem Update alle Indizes einer Tabelle ändern muss, hat mehrere Auswirkungen auf die Performance. Natürlich werden Update-Abfragen langsamer, weil das System mehr Arbeit leisten muss.“
    Ich habe mich wegen dieser Schreibverstärkung gefragt: Bei MySQL stimmt es zwar, dass Indizes nicht auf diese Weise aktualisiert werden müssen, aber die MySQL-Replikation hängt von binlog ab, und jede Änderung muss zusätzlich zu dem, was die Datenbank selbst schreibt (etwa InnoDB-Redo-Log), noch einmal extra protokolliert werden.
    Daher scheint MySQL im Cluster eine andere Art von Schreibverstärkung zu haben. PostgreSQL verwendet für die Replikation WAL wieder, daher gibt es diese Verstärkung dort nicht.
    Außerdem schreibt MySQL auf der Empfängerseite eingehende Binlogs zunächst in ein Relay Log, und der Applier-Thread konsumiert dieses Relay Log und erzeugt dabei zusätzliche InnoDB-Schreibvorgänge sowie standardmäßig weitere Binlogs.

  • Dieses Thema lässt sich nicht diskutieren, ohne über Festplatten zu sprechen
    SSDs schreiben jeweils 4-KB-Seiten. Das heißt: Selbst wenn nur 1 Bit aktualisiert wird, liest die Festplatte 4 KB, ändert das Bit und schreibt die 4-KB-Seite in einen neuen Slot zurück. Daher hängt die Strafe für das Kopieren vom Typ des Datenträgers ab

    • Interessant. Ich frage mich, wie sich das in der AWS-Abrechnung niederschlägt
      AWS berechnet IO pauschal nach MBps, aber ich weiß nicht, ob dabei auf die nächsten 4 KB aufgerundet wird oder ob statt der Anfragemenge das tatsächliche Schreibvolumen des Laufwerks verfolgt wird und für die tatsächlich in der Storage-Implementierung angefallene IO-Menge abgerechnet wird
    • Postgres-Seiten sind 8 KB groß, daher ist dieser Punkt nicht besonders relevant

  • „Oracle und MySQL haben dieses Problem in ihrer MVCC-Implementierung nicht. Der Grund ist, dass sekundäre Indizes nicht die physische Adresse der neuen Version speichern. Stattdessen speichern sie einen logischen Bezeichner (z. B. Tupel-ID oder Primärschlüssel), den das DBMS verwendet, um die physische Adresse der aktuellen Version zu finden. Dadurch können Lesezugriffe über sekundäre Indizes langsamer werden, aber diese DBMS haben andere Vorteile in ihrer MVCC-Implementierung, die den Overhead verringern.“
    Ich habe in MySQL ein interessantes Verhalten beobachtet. Die Datenbank war ungefähr 500 GB groß, und das Schema war eher dokumentenorientiert als relational. Dabei war SELECT id WHERE something; UPDATE what WHERE id=id um Größenordnungen schneller als UPDATE what WHERE something
    Ich vermute, der Grund für dieses Verhalten liegt vielleicht genau hier. Allerdings macht man das in normalen Workloads nicht; langsam ist es nur bei temporären DML-Anweisungen zum Beheben von Inkonsistenzen

    • SELECT ist ein schreibgeschützter Vorgang und kann parallel ausgeführt werden
      UPDATE schreibt dagegen tatsächlich und kann die Tabelle sperren. UPDATE id=id ermöglicht Locks auf Zeilenebene. Allerdings besteht auch das Risiko, dass zwischen SELECT und UPDATE neu eingefügte Datensätze übersehen werden
    • Ich betreibe einige überwiegend leseorientierte Postgres-Instanzen mit mehr als 2 TB, die ebenfalls eher dokumentenorientiert sind
      Ich stimme zu, dass umfangreiche Updates viel zu langsam sein können. Am Ende verarbeitet man Updates oft schrittweise in Batches oder verwendet sogar COPY

  • „In den 2010er Jahren war es MongoDB. Wegen nicht-dauerhafter Schreibvorgänge wurde es ‚webscale‘.“
    Off-Topic, aber das war von Anfang bis Ende Marketing: https://news.ycombinator.com/item?id=15124306

    • MongoDB wurde von ehemaligen DoubleClick-Ingenieuren als selbstgebautes DIY-DB entworfen, weil es keine Datenbank gab, die die Anforderungen anderer Dienste erfüllte
      Version 4.2.8 (2020) ist ziemlich robust geworden, und es ist bekannt, dass es keine Dirty Writes gibt: https://en.wikipedia.org/wiki/MongoDB#Technical_criticisms

  • Am meisten stört mich, dass man selbst 2024 vorneweg noch einen Connection Pooler (z. B. pgbouncer) braucht, damit es wirklich brauchbar ist

  • OrioleDB wollte dieses Problem mit einer neuen Storage Engine lösen: https://github.com/orioledb/orioledb

    • Ist jetzt beim Supabase-Team
      Oriole ist inzwischen bei Supabase, und Alexander und sein Team arbeiten Vollzeit daran. Das Patchset gibt es hier: https://www.orioledb.com/docs#patch-set
      Gegen Ende dieses Jahres soll es auch auf der Supabase-Plattform nutzbar sein