Meine Notizen zum GitLab-Postgres-Schema-Design

 (shekhargulati.com)
1 Punkte von GN⁺ 2024-02-18 | 1 Kommentare | Auf WhatsApp teilen

Meine Notizen zum GitLab-Postgres-Schema-Design

  • Indem ich mir das Postgres-Schema von GitLab anschaue, kann ich es mit meinem eigenen Schema vergleichen und Best Practices aus GitLabs Schemadefinition ableiten.
  • GitLab ist eine Open-Source-DevOps-Plattform, eine Alternative zu GitHub, die selbst gehostet werden kann.

Richtige Verwendung von Primärschlüsseltypen

  • Wenn eine Datenbank klein ist, fällt das kaum auf, mit dem Wachstum wirken sich Primärschlüssel jedoch auf Speicherplatz, Schreibgeschwindigkeit und Lesegeschwindigkeit aus.
  • Von 573 Tabellen verwendet GitLab bigserial als Primärschlüsseltyp in 380 Tabellen, serial4 in 170, und die übrigen 23 nutzen zusammengesetzte Primärschlüssel.

Verwendung interner und externer IDs

  • Es ist eine gute Praxis, Primärschlüssel nicht nach außen hin offenzulegen.
  • GitLab verwendet in Tabellen wie issues, ci_pipelines, deployments und epics sowohl interne IDs (id) als auch externe IDs (iid).

Verwendung von text-Datentypen und CHECK-Einschränkungen

  • Das GitLab-Schema verwendet sowohl character varying(n) als auch text, wobei es text häufiger nutzt.
  • Der Datentyp text hat keine Längenbeschränkung; GitLab definiert die Längenbeschränkung stattdessen mit CHECK.

Namenskonventionen

  • Alle Tabellen sind im Plural benannt und nutzen Modulnamenpräfixe, um Namespaces bereitzustellen.
  • Tabellen- und Spaltennamen folgen der snake_case-Konvention.

Zeitzonennutzung bei Timestamps

  • GitLab verwendet sowohl timestamp with timezone als auch timestamp without timezone.
  • Für Systemvorgänge wird timestamp without timezone verwendet, für Benutzeraktivitäten timestamp with timezone.

Fremdschlüssel-Constraints

  • GitLab nutzt Fremdschlüssel-Constraints in den meisten Tabellen, in einigen wie audit_events, abuse_reports, web_hooks_logs und spam_logs jedoch nicht.

Partitionierung großer Tabellen

  • GitLab partitioniert Tabellen, die im Laufe der Zeit groß werden können, um die Abfrageleistung zu verbessern.

Unterstützung von LIKE-Suchfällen mit Trigrammen und gin_trgm_ops

  • GitLab nutzt GIN(Generalized Inverted Index)-Indizes, um eine effiziente Suche bereitzustellen.

Einsatz von jsonb

  • Das GitLab-Schema verwendet den Datentyp jsonb in mehreren Tabellen.

Weitere Tipps

  • Änderbare Tabellen haben Audit-Felder wie updated_at, unveränderliche Log-Tabellen dagegen nicht.
  • Enums werden als smallint statt character varying gespeichert, um Platz zu sparen.

GN⁺-Meinung:

  • Das GitLab-Schema-Design liefert Einblicke in die Datenbankgestaltung und beinhaltet wichtige Lehren, insbesondere zur Schema-Optimierung für Großsysteme.
  • Da GitLab Open Source ist, liefern solche Schema-Entscheidungen praktische Beispiele, die andere Entwickler in ihren eigenen Projekten übernehmen können.
  • Was man aus dem GitLab-Schema lernen kann, ist, dass Datentypauswahl, Indexierungsstrategie, Partitionierung und der Einsatz von Fremdschlüssel-Constraints zu Aspekten beitragen, die die Datenbankleistung und Wartbarkeit maßgeblich beeinflussen.

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2024-02-18
Hacker-News-Kommentare

  • Ich frage mich, warum die Praxis nötig ist, Primärschlüssel nicht nach außen offenzulegen. Wenn Requests ohnehin authentifiziert werden müssen, sehe ich auch nicht, welchen Wert es hat, das Erraten von IDs zu verhindern.
    Wenn man allein mit einer erratenen ID ohne Authentifizierung und Autorisierung etwas Nützliches tun kann, ist an anderer Stelle bereits etwas ernsthaft kaputt; darauf sollte man sich konzentrieren, statt dem Schema unnötige Komplexität hinzuzufügen. Competitive Intelligence in dem Sinne, dass Wettbewerber die Kundenzahl nicht abschätzen können, kann einen gewissen Wert haben, aber ich glaube nicht, dass GitLab sich darum besonders stark gekümmert hat. Bei GitLabs Entscheidung für id + iid dürften eher Anforderungen an die Query-Performance als der Schutz vor dem Erraten interner IDs ausschlaggebend gewesen sein

    • Stimmt, aber erratbare IDs können eine Sicherheitslücke furchtbar machen oder sie deutlich verschlimmern.
      Wenn man den Nutzern UUIDs offengelegt hat, müsste ein Angreifer trotz derselben Schwachstelle erst die UUID treffen, was viel schwieriger ist und möglicherweise eine zweite Quelle erfordert. Selbst wenn Daten abfließen, gewinnt man Zeit für eine Reaktion, und die Menge des Abflusses lässt sich einschätzen. Bei sequenziellen IDs hingegen wächst das Problem sofort zu einem vollständigen Leak an und kann zu einem großen Vorfall werden, der den Datenschutzaufsichtsbehörden gemeldet werden muss. Das ist eine Defense in Depth, die eigentlich nicht nötig sein sollte, aber es gibt tatsächlich schreckliche Software, die genau auf diese Weise kompromittiert wurde
    • Wie auch im Artikel erwähnt, geht es weniger um Sicherheit als um Competitive Intelligence. Einfache Auto-Increment-IDs verraten die Gesamtzahl der Datensätze in einer Tabelle oder deren Wachstumsrate.
      Wenn man den Primärschlüssel id der Issue-Tabelle offenlegt, beginnt er beim Erstellen von Issues in einem Projekt nicht bei 1; dadurch lässt sich leicht abschätzen, wie viele Issues es insgesamt in GitLab gibt
    • Der Ausdruck Sicherheitstheater wird viel zu oft verwendet. Sicherheit kann und sollte aus mehreren Schichten bestehen, und nur weil eine Schicht wie die Authentifizierung bricht, sollte der Rest nicht ebenfalls leicht zugänglich werden.
      Natürlich ist es ein großes Problem, wenn allein mit einer erratenen ID ohne Authentifizierung und Autorisierung etwas möglich ist. Aber wenn es an diesem Punkt keine weiteren Schichten gibt, ist das Spiel schon vorbei. Bugs kündigen sich nicht vorher an, insbesondere subtile Bugs nicht. Wenn so ein Bug auftritt und wenigstens die IDs nicht erratbar sind, wird man dankbar sein, dass nicht alle Nutzerkonten des Systems leicht zugänglich geworden sind
    • Auch in der Zugriffskontrolle gibt es Bugs. Nicht erratbare IDs machen es deutlich schwieriger, einige solcher Bugs auszunutzen.
      Natürlich sollte man sich zuerst darauf konzentrieren, korrekte Zugriffskontrolle sicherzustellen, aber nicht erratbare IDs können den Unterschied zwischen einer schrecklichen Katastrophe und einem Beinaheunfall ausmachen. Wenn UUIDs nicht passen, kann man auch Auto-Increment-Datenbank-IDs verwenden und sie verschlüsseln; mit einer geeigneten Softwareschicht funktionieren verschlüsselte IDs nahezu automatisch
    • Nur ein kleiner terminologischer Unterschied: Das sollte man wohl eher Competitive Intelligence nennen als „Business Intelligence“, was üblicherweise die interne Datennutzung in einem Unternehmen meint. https://en.wikipedia.org/wiki/Competitive_intelligence

  • Von 128 Millionen öffentlichen Repositories ist die Mehrheit Forks anderer Repositories und existiert nur, um Pull Requests an das Haupt-Repository zu stellen; sofern niemand einen Fehler macht, dürften sie keine Issues haben.
    Kleine Spielzeugprojekte oder schnell aufgegebene Projekte haben wahrscheinlich ebenfalls keine oder nur sehr wenige Issues. Es gibt sicher Projekte mit Hunderten oder Tausenden Issues, aber der Durchschnitt über alle 128 Millionen Repositories dürfte ziemlich niedrig sein und wahrscheinlich unter der 2-Milliarden-Grenze bleiben. Trotzdem stimme ich zu, dass die Verwendung eines 4-Byte-Typs – genauer gesagt von 31 Bit – für diese Tabelle für einige Organisationen einschließlich github.com eine Zeitbombe ist

    • Auch aktuell liegt man mit 362.107.148 Repositories und 818.516.506 eindeutigen Issues und Pull Requests noch unter der Grenze.
      https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
    • Ich denke, GitHubs Entscheidung, sich von Rails zu entfernen, wurde teilweise auch durch einen großen Mangel von ActiveRecord beeinflusst: die fehlende Unterstützung für zusammengesetzte Primärschlüssel.
      Eine grundlegende Anforderung wie PRIMARY KEY(repo_id, issue_id) wird in ActiveRecord unnötig kompliziert, und um zu ActiveRecord zu passen, das eine einzelne Primärschlüsselspalte verlangt, braucht man den Umweg über einen Unique Key plus einen separaten Primärschlüssel. UUID-Primärschlüssel wirken ebenfalls wie ein Workaround, aber die Unique-Constraint auf das Paar (repo_id, issue_id) ist weiterhin nötig, wodurch Datenbankgröße und Overhead steigen. Allgemeiner betrachtet erzeugt auch die auf einer einzelnen Model-, Controller- und View-Schicht basierende monolithische MVC-Struktur von Ruby on Rails mit wachsender Anwendung Probleme bei Skalierbarkeit und Wartbarkeit; MVC passt meiner Ansicht nach besser zu modularen oder komponentenbasierten Strukturen
    • Ich frage mich, ob man sicher weiß, ob GitLab Cloud eine Multi-Tenant-Datenbank verwendet oder Datenbanken pro Nutzer, Kunde oder Organisation.
      Produkte, die sowohl Self-Hosting als auch Cloud anbieten, haben häufig Datenbanken pro Kunde bevorzugt. Denn dadurch wird der gemeinsam genutzte Teil der Codebasis deutlich einfacher, weil unabhängig vom Hosting-Typ dieselben Queries verwendet werden können. Bei Datenbanken pro Kunde käme man kaum in die Nähe solcher Nutzungslimits; und falls doch, wäre Self-Hosting wohl geeigneter
    • Diese Zeitbombe ist eine Bombe, die sich mit einer 11-sekündigen Migration entschärfen lässt
    • Eine Migration von Primärschlüsseln von int zu bigint ist möglich. Sie erfordert etwas Vorbereitung und Custom Code, lässt sich aber ohne Downtime durchführen.
      Im Großen und Ganzen verwalte ich große Migrationen nach diesem Verfahren und nutze es mit kleinen Anpassungen: http://zemanta.github.io/2021/08/25/column-migration-from-in...
      Foreign Keys, Indizes und Constraints insgesamt erschweren den Prozess, machen ihn aber nicht unmöglich. In meinem Fall dauerte die Datenmigration einige Stunden, musste aber nicht schnell sein. Soweit ich weiß, hat GitLab Werkzeuge, die Post-Upgrade-Jobs ausführen, sodass sie an jedem Punkt während eines Versions-Upgrades funktionieren

  • Die Diskussion über die Speichergröße von UUID-Spalten ist wenig überzeugend. Wenn eine Tabelle fünf weitere Spalten hat, ist der Unterschied zwischen 128 Bit und 64 Bit nicht groß
    Die wichtigere Sorge ist die Performance. UUIDv4 wird breit unterstützt, ist aber vollständig zufällig und daher für Index-Performance nicht ideal. UUIDv7[0] kommt Snowflake[1] näher und hat zeitliche Lokalität, aber Implementierungen sind noch weniger verbreitet. Ein anderer Ansatz ist, bigserial zu verwenden und den Schlüssel zu verschlüsseln: https://github.com/abevoelker/gfc64
    Dieses Verfahren hat jedoch die Nachteile, dass 1) der geheime Wert nicht rotiert werden kann und 2) nach einer einmaligen Offenlegung jeder die Tabellengröße per Fermi-Abschätzung schätzen kann. Öffentliche IDs und interne IDs zu trennen ist umständlich, und wenn die öffentliche ID UUIDv4 ist, opfert man auch Performance. UUIDv7 ist aus meiner Sicht die Lösung, die die meisten Anforderungen erfüllt
    [0]: https://uuid7.com/
    [1]: https://en.wikipedia.org/wiki/Snowflake_ID

    • Es geht nicht nur um die Größe dieser einen Spalte, sondern auch um jede Stelle, an der diese id als Fremdschlüssel verwendet wird, sowie um die Größe der für diese Fremdschlüsselspalten nötigen Indizes
      Man denke an Werte wie Benutzer-IDs, auf die datenbankweit Dutzende oder Hunderte von Fremdschlüsseln verweisen können
    • Das Problem ist, dass die anderen fünf Spalten nicht indexiert sind
      Bei Datenbank-Performance gibt es drei Stufen: 1) Indexe und Daten passen beide in den Speicher. 2) Indexe passen in den Speicher, Daten aber nicht. 3) Weder Indexe noch Daten passen in den Speicher. Stufe 1 ist gut, aber wenn man sie nicht erreicht, muss man Stufe 2 um jeden Preis verteidigen. Eine Verdopplung der Indexgröße macht das schwieriger
    • Man kann sich den Primärschlüssel einer Datenbank wie einen Basis-Pointer à la typedef void* vorstellen. Seine Größe beeinflusst die Gesamtperformance: Speicher- und Plattenverbrauch, Durchsatz-Engpässe und sogar die CPU-Zeit für Schlüsselvergleiche in den innersten Schleifen von Joins und Lookups
      Als x86-64-CPUs neu aufkamen, war der Performance-Einfluss des Wechsels auf 64-Bit-Pointer so groß, dass x32/ilp32 entstand; aus demselben Grund setzt .NET bis heute standardmäßig auf „prefer 32-bit“. 128-Bit-UUIDs als Datenbank-Primärschlüssel zu verwenden, ist ein schrecklicher Fehler
    • Auch UUIDv7 ist kein Allheilmittel. In vielen Fällen möchte man die Erstellungszeit einer Ressource nicht preisgeben
      Zum Beispiel könnte man ein Video einen Monat vor der Veröffentlichung hochladen wollen, ohne dass das Publikum davon erfährt
    • Es gibt auch andere Varianten dieses Ansatzes: https://pgxn.org/dist/permuteseq/
      Es ist auch möglich, Werte bei der Anzeige in URLs, E-Mails usw. zu verschlüsseln: https://wiki.postgresql.org/wiki/Pseudo_encrypt
      So lassen sich viele Vorteile sequenzieller Indizes erhalten, während Schlüsseländerungen möglich bleiben. Ändert man jedoch den Schlüssel, gehen Bookmarks kaputt, in früheren E-Mails versendete Links werden ungültig, und faktisch bleibt derselbe Effekt wie beim Umbenennen von allem

  • Eine kleine Spitzfindigkeit, aber den Abschnitt text versus varchar möchte ich ansprechen
    Der Autor verwendet viele Worte darauf, einen nicht existierenden Performance-Unterschied zu belegen, und kommt dann zu dem Schluss, dass es „keinen großen Performance-Unterschied zwischen den beiden Typen“ gebe. Das Thema ist seit Langem geklärt, und es heißt nicht „nicht groß“, sondern „nicht vorhanden“. Das PostgreSQL-Wiki[1] sagt ausdrücklich, man solle text verwenden, sofern es keinen sehr guten Grund dagegen gibt, und die Dokumentation[2] sagt ebenfalls: „For many purposes, character varying acts as though it were a domain over text“; in der grünen Tip-Box steht, dass es zwischen diesen drei Typen keinen Performance-Unterschied gibt. Dass GitLab überwiegend text verwendet, wirkt daher so, als hätte man die Dokumentation gelesen und das Schema für PostgreSQL entworfen, statt ein halbgares „portables“ Schema zu bauen
    [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...
    [2] https://www.postgresql.org/docs/current/datatype-character.h...

    • Tatsächlich entsteht ein erheblicher Performance-Unterschied, wenn man das Schema passend zu geänderten Längen gespeicherter Strings migrieren muss
      Um varchar(300) in varchar(200) zu ändern, müssen alle Zeilen neu geschrieben werden; die Constraint einer text-Spalte zu aktualisieren ist dagegen im Grunde fast kostenlos und erfordert nur einen vollständigen Tabellenscan, um zu prüfen, ob die bestehenden Werte die neue Constraint erfüllen. Auch der Text sagt, dass die Verwendung des Typs text mit einer CHECK-Constraint die Schema-Evolution bei Längenprüfungen einfacher macht als character varying oder varchar(n)

  • Die Aussage, dass Fremdschlüssel teuer sind, wird oft wiederholt, ist aber eine Behauptung, zu der Benchmarks selten sind
    Es gibt viele Arten, sie falsch zu implementieren, aber irgendwo im Stack wird die Integrität ohnehin erzwungen. Wer dafür die Datenbank nutzen will, statt es neu zu implementieren, braucht Wissen und Experimente – und vermeidet damit in der Regel größere Unfälle

  • Mich würde interessieren, ob jemand die Performance-Unterschiede zwischen GitLab und GitHub zusammengefasst oder genauer beobachtet hat.
    Beide sind Rails-basierte Anwendungen, aber insgesamt empfinde ich die Ladezeiten von GitLab-Seiten im Vergleich zu GitHub als furchtbar.

    • Als ich GitLab vor ein paar Jahren genutzt habe, waren die clientseitigen Performance-Probleme bei großen Pull Requests erheblich. GitHub ist auch nicht ideal, kommt damit aber noch einigermaßen zurecht.
    • GitHub mit GitLab zu vergleichen ist ähnlich wie Chrome mit anderen Browsern zu vergleichen, sogar mit Chromium-basierten Browsern.
      Chrome und GitHub werden alle möglichen Tricks anwenden, selbst wenn sie damit Nutzern schaden. Zum Beispiel habe ich in einem Firmen-GitHub einmal einen Merge-Diff geöffnet, mit Ctrl F gesucht, keine Treffer bekommen, mich dann manuell durch die Git-Historie zum nächsten Diff gehangelt und erst beim 100. Diff gemerkt, dass die wichtigste Datei tief darin versteckt war. Vermutlich, weil es für jemanden bequemer war, Seitenlade-Metriken zu erfüllen und befördert zu werden.
    • GitHub ist, abgesehen von zwei Ausfällen im letzten Jahr, insgesamt stabil und meist ziemlich schnell. Sonst würde ich keine Tastenkürzel verwenden.
      Hier ist ein Beitrag eines ehemaligen Entwicklers, der helfen kann, GitLabs Kultur und die geringe Wertschätzung von Performance zu verstehen: https://news.ycombinator.com/item?id=39303323
      Ich nutze GitLab nicht genug, um die Performance-Probleme selbst deutlich zu spüren, aber ich denke, dieser Beitrag kann hilfreich sein.

  • Ich habe mich immer gefragt, wofür das zusätzliche I in den CI-Variablen CI_PIPELINE_IID und CI_MERGE_REQUEST_IID steht.
    Ich hatte vermutet, dass es eine datenbankbezogene Entscheidung ist, und dieser Artikel bestätigt das.

  • Wenn man liest, dass „1 Quintillion 1.000.000.000 Milliarden entspricht“, wirkt es ziemlich seltsam, dass wir uns meist nur zwischen int32 und int64 entscheiden. Es sollte wohl einen 5-Byte-Integer-Typ geben, der eine Kardinalität von ungefähr 1 Billion unterstützt.

    • Wenn man Werte nicht dicht gepackt speichert, ergibt es keinen Sinn, eine Größe zu wählen, die keine Zweierpotenz ist.

  • Keine automatisch inkrementierenden IDs zu verwenden kann sinnvoll sein, aber den Vorteil von zwei IDs für interne und externe Zwecke sehe ich nicht wirklich.
    Die Zahl der Spalten und Indizes steigt, man muss immer zuerst nachschlagen, und mir fällt auch kein Sicherheits­szenario ein, in dem man den internen Schlüssel ändert, den externen aber nicht. Übersehe ich etwas?

    • Wenn man etwas auf Projektebene tut, hat man die nötigen Informationen ohnehin schon. Außerdem ist es für Nutzer freundlicher, wenn die Issues jedes Projekts bei 1 beginnen, statt bei einer Zahl wie 2 Billionen 700 Milliarden 300 Millionen 5 Millionen 717 Tausend 325.

  • Es heißt, dass die Verwendung des nativen PostgreSQL-Typs UUID v4 statt bigserial die Tabellengröße um 25 % erhöht und die Insert-Rate auf 25 % von bigserial senkt. Mich würde interessieren, warum UUIDv4 so schlecht ist.
    Ist eine UUID nicht einfach eine 128-Bit-Zahl? Ist die Erzeugung extrem teuer, oder was passiert da?

    • UUIDv4 ist vollständig zufällig, und B-Tree-Indizes erwarten „rechtslastige“ Werte mit einer sinnvollen Ordnung.
      Deshalb wird das Indexieren von UUIDv4-Spalten langsamer, und das war ein Motiv für die Entwicklung von UUIDv6 und UUIDv7.
    • Die 25 % größere Größe stimmt, aber das ist nur ein kleiner, vorhersehbarer linearer Zuwachs von 8 Byte pro Zeile. Im Vergleich zu den übrigen Daten einer Zeile ist das kein besonders großer Grund zur Sorge.
      Das größere Problem ist die Insert-Rate. Bei UUIDs wird die Insert-Rate durch die verfügbare RAM-Menge begrenzt. Bei automatisch inkrementierenden Integern ist das nicht der Fall. Integer korrelieren mit der Zeit, UUID4 ist dagegen zufällig; dadurch ändern sich die Performance-Eigenschaften bei größerem Maßstab grundlegend. Bei kleinen Tabellen ist die Insert-Strafe fast vernachlässigbar, aber sobald die Größe des B-Tree-Index an die Speichergrenze stößt, kann PostgreSQL den UUID-B-Tree nicht mehr vollständig im Speicher halten und ist auf den Austausch von Disk-Pages angewiesen. Automatisch inkrementierende Integer verwenden für zeitlich nahe beieinanderliegende Zeilen dieselben Index-Pages, sodass sie bei derselben Last nicht auf die Disk zugreifen müssen. Wenn man diese Größenordnung erreicht, ist der Unterschied kein konstanter Rückgang um 25 %, sondern eine 25-fache Performance-Klippe; abgesehen von einer Schema-Migration bleibt dann nur, mehr RAM zu kaufen.
    • Ich denke, es liegt am B-Tree. B-Trees und Pages funktionieren besser, wenn vor allem die letzte Page stark genutzt wird.
      UUIDs erzeugen viele ungeordnete Schreibvorgänge und führen zu Page Bloat.
    • Wenn die Sortierreihenfolge zufällig verteilt ist, verschlechtert sich die Cache-Lokalität des B-Trees. Inserts landen nicht auf der letzten Page, sondern werden überall verstreut.
      Auch die Lokalität von Batch-Inserts ist später bei Abfragen schlechter, sodass zusammengehörige Datensätze zufällig gesucht werden müssen. Am Ende zahlt man die Kosten sowohl beim Insert als auch später beim Select.