5.1 Einführung

Das Internet ist voll von dogmatischen Vorschriften darüber, wie Schlüssel in relationalen Datenbanken ausgewählt und verwendet werden sollten. Manchmal werden Streitigkeiten sogar zu Holivars: Sollen natürliche oder künstliche Schlüssel verwendet werden? Ganzzahlen oder UUIDs automatisch inkrementieren?

Nachdem ich vierundsechzig Artikel gelesen, Abschnitte aus fünf Büchern durchgeblättert und jede Menge Fragen zu IRC und StackOverflow gestellt habe, scheine ich (Joe „begriffs“ Nelson, der Autor des Originalartikels) die Puzzleteile zusammengesetzt zu haben und kann nun Gegner versöhnen. Viele wichtige Streitigkeiten entstehen tatsächlich aus einem Missverständnis des Standpunkts einer anderen Person.

Nehmen wir das Problem auseinander und setzen wir es am Ende wieder zusammen. Stellen wir zunächst die Frage: Was ist ein „Schlüssel“?

Vergessen wir für einen Moment die Primärschlüssel, uns interessiert eine allgemeinere Idee. Ein Schlüssel ist eine Spalte (Spalte) oder Spalten, die keine doppelten Werte in Zeilen haben . Außerdem müssen die Spalten irreduzibel eindeutig sein, d. h. keine Teilmenge der Spalten weist diese Eindeutigkeit auf.

Aber zuerst eine Theorie:

Primärschlüssel

PrimärschlüsselWird direkt zur Identifizierung von Zeilen in einer Tabelle verwendet. Es muss die folgenden Einschränkungen einhalten:

  • Der Primärschlüssel muss jederzeit eindeutig sein.
  • Es muss immer in der Tabelle vorhanden sein und einen Wert haben.
  • Der Wert sollte sich nicht häufig ändern. Im Idealfall sollte sich der Wert überhaupt nicht ändern .

Normalerweise stellt ein Primärschlüssel eine einzelne Spalte einer Tabelle dar, es kann sich jedoch auch um einen zusammengesetzten Schlüssel handeln, der aus mehreren Spalten besteht.

Zusammengesetzter Schlüssel

Benutzerdefinierter Schlüssel– eine Kombination von Attributen (Spalten), die jede Tabellenzeile eindeutig identifizieren. Es können alle Spalten, mehrere und eine sein. In diesem Fall sollten die Zeilen, die die Werte dieser Attribute enthalten, nicht wiederholt werden.

Potenzieller Schlüssel

Kandidatenschlüssel- stellt den minimalen zusammengesetzten Schlüssel der Beziehung (Tabelle) dar, d. h. einen Satz von Attributen, der eine Reihe von Bedingungen erfüllt:

  • Irreduzibilität : Es kann nicht reduziert werden, es enthält den kleinstmöglichen Satz an Attributen.
  • Einzigartigkeit : Es muss unabhängig von der Zeilenänderung eindeutige Werte haben.
  • Vorhandensein eines Werts : Er darf keinen Nullwert haben, d. h. er muss einen Wert haben.

5.2 Der seltsame Fall von Primärschlüsseln

Was wir im vorherigen Abschnitt gerade als „Schlüssel“ bezeichnet haben, wird allgemein als „Kandidatenschlüssel“ bezeichnet. Der Begriff „Kandidat“ impliziert, dass alle diese Schlüssel um die Ehrenrolle des „Primärschlüssels“ (Primärschlüssel) konkurrieren und dem Rest „Alternativschlüssel“ (Alternativschlüssel) zugewiesen werden.

Es dauerte eine Weile, bis SQL-Implementierungen die Diskrepanz zwischen Schlüsseln und dem relationalen Modell überwunden hatten, und die frühesten Datenbanken waren auf das Low-Level-Konzept eines Primärschlüssels ausgerichtet. Primärschlüssel in solchen Datenbanken waren erforderlich, um den physischen Speicherort einer Zeile auf sequentiellen Speichermedien zu identifizieren. So erklärt es Joe Celko:

Der Begriff „Schlüssel“ bezeichnete einen Dateisortierschlüssel, der zum Ausführen jeglicher Verarbeitungsvorgänge in einem sequentiellen Dateisystem erforderlich war. Ein Satz Lochkarten wurde in nur einer Reihenfolge gelesen; es war unmöglich, zurückzukehren. Frühere Bandlaufwerke ahmten das gleiche Verhalten nach und ermöglichten keinen bidirektionalen Zugriff. Das heißt, der ursprüngliche Sybase SQL Server musste die Tabelle an den Anfang „zurückspulen“, um die vorherige Zeile zu lesen.

In modernem SQL müssen Sie sich nicht auf die physische Darstellung von Informationen konzentrieren, Tabellen modellieren Beziehungen und die interne Reihenfolge von Zeilen ist überhaupt nicht wichtig. Allerdings erstellt der SQL-Server auch heute noch standardmäßig einen Clustered-Index für Primärschlüssel und ordnet nach alter Tradition die Reihenfolge der Zeilen physisch an.

In den meisten Datenbanken gehören Primärschlüssel der Vergangenheit an und bieten kaum mehr als eine Widerspiegelung oder einen physischen Standort. Beispielsweise erzwingt die Deklaration eines Primärschlüssels in einer PostgreSQL-Tabelle automatisch eine Einschränkung NOT NULLund definiert einen Standardfremdschlüssel. Darüber hinaus sind Primärschlüssel die bevorzugten Spalten für den Operator JOIN.

Der Primärschlüssel setzt die Möglichkeit der Deklaration anderer Schlüssel nicht außer Kraft. Wenn gleichzeitig kein Schlüssel als primärer Schlüssel zugewiesen ist, funktioniert die Tabelle weiterhin einwandfrei. Der Blitz wird Sie jedenfalls nicht treffen.

Java webinar

5.3 Finden natürlicher Schlüssel

Die oben besprochenen Schlüssel werden als „natürlich“ bezeichnet, weil es sich um Eigenschaften des modellierten Objekts handelt, die an sich interessant sind, auch wenn niemand daraus einen Schlüssel machen möchte.

Wenn Sie eine Tabelle auf mögliche natürliche Schlüssel untersuchen, sollten Sie als Erstes daran denken, nicht zu schlau vorzugehen. Der Benutzer sqlvogel auf StackExchange gibt den folgenden Rat:

Manche Menschen haben Schwierigkeiten, einen „natürlichen“ Schlüssel zu wählen, weil sie sich hypothetische Situationen ausdenken, in denen ein bestimmter Schlüssel möglicherweise nicht eindeutig ist. Sie verstehen den eigentlichen Sinn der Aufgabe nicht. Die Bedeutung des Schlüssels besteht darin, die Regel zu definieren, dass Attribute in einer bestimmten Tabelle zu jedem Zeitpunkt eindeutig sein sollen und immer sein werden. Die Tabelle enthält Daten in einem bestimmten und gut verstandenen Kontext (im „Themenbereich“ oder „Diskursbereich“), und die einzige Bedeutung ist die Anwendung der Einschränkung in diesem bestimmten Bereich.

Die Praxis zeigt, dass es notwendig ist, eine Schlüsseleinschränkung einzuführen, wenn die Spalte mit den verfügbaren Werten eindeutig ist und dies in wahrscheinlichen Szenarien auch bleiben wird. Und bei Bedarf kann die Einschränkung aufgehoben werden (wenn Sie das stört, dann sprechen wir weiter unten über die Schlüsselstabilität.)

Beispielsweise kann eine Datenbank mit Hobbyclub-Mitgliedern die Eindeutigkeit in zwei Spalten aufweisen – first_name, last_name. Bei einer kleinen Datenmenge sind Duplikate unwahrscheinlich und bevor es zu einem echten Konflikt kommt, ist es durchaus sinnvoll, einen solchen Schlüssel zu verwenden.

Wenn die Datenbank wächst und das Informationsvolumen zunimmt, kann die Auswahl eines natürlichen Schlüssels schwieriger werden. Die von uns gespeicherten Daten stellen eine Vereinfachung der äußeren Realität dar und enthalten einige Aspekte nicht, die Objekte in der Welt unterscheiden, wie etwa ihre Koordinaten, die sich im Laufe der Zeit ändern. Wenn einem Objekt jeglicher Code fehlt, wie kann man dann zwei Getränkedosen oder zwei Schachteln Haferflocken anhand ihrer räumlichen Anordnung oder geringfügiger Gewichts- oder Verpackungsunterschiede unterscheiden?

Aus diesem Grund erstellen und versehen Normungsgremien Unterscheidungsmerkmale für Produkte. Fahrzeuge sind mit einer Fahrzeugidentifikationsnummer (VIN) versehen , Bücher werden mit ISBNs gedruckt und Lebensmittelverpackungen haben UPCs . Sie mögen einwenden, dass diese Zahlen nicht natürlich erscheinen. Warum nenne ich sie also natürliche Schlüssel?

Die Natürlichkeit oder Künstlichkeit einzigartiger Eigenschaften in einer Datenbank hängt von der Außenwelt ab. Ein Schlüssel, der künstlich war, als er in einem Normungsgremium oder einer Regierungsbehörde erstellt wurde, wird für uns natürlich, weil er auf der ganzen Welt zum Standard wird und/oder auf Gegenstände gedruckt wird.

Es gibt viele Branchen-, öffentliche und internationale Standards für eine Vielzahl von Themen, darunter Währungen, Sprachen, Finanzinstrumente, Chemikalien und medizinische Diagnosen. Hier sind einige der Werte, die häufig als natürliche Schlüssel verwendet werden:

  • ISO 3166-Ländercodes
  • ISO 639-Sprachcodes
  • Währungscodes gemäß ISO 4217
  • Börsensymbole ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • Anmeldenamen
  • E-mailadressen
  • Zimmernummern
  • Netzwerk-Mac-Adresse
  • Breiten- und Längengrad für Punkte auf der Erdoberfläche

Ich empfehle, wann immer möglich und sinnvoll, Schlüssel zu deklarieren, vielleicht sogar mehrere Schlüssel pro Tabelle. Bedenken Sie jedoch, dass es für alle oben genannten Punkte Ausnahmen geben kann.

  • Nicht jeder hat eine E-Mail-Adresse, obwohl dies unter bestimmten Datenbankbedingungen akzeptabel sein kann. Außerdem ändern Menschen von Zeit zu Zeit ihre E-Mail-Adressen. (Mehr zur Schlüsselstabilität später.)
  • ISIN-Aktiensymbole ändern sich von Zeit zu Zeit. Beispielsweise beschreiben die Symbole GOOG und GOOGL die Umstrukturierung des Unternehmens von Google zu Alphabet nicht genau. Manchmal kann es zu Verwirrung kommen, wie zum Beispiel bei TWTR und TWTRQ, da einige Anleger letzteres versehentlich während des Twitter-Börsengangs gekauft haben.
  • Sozialversicherungsnummern werden nur von US-Bürgern verwendet, unterliegen Datenschutzbeschränkungen und werden nach dem Tod wiederverwendet. Darüber hinaus können Personen nach einem Dokumentendiebstahl neue Nummern erhalten. Schließlich kann dieselbe Nummer sowohl eine Person als auch eine Einkommensteuer-Identifikationsnummer identifizieren.
  • Postleitzahlen sind für Städte eine schlechte Wahl. Manche Städte haben einen gemeinsamen Index, oder umgekehrt gibt es in einer Stadt mehrere Indexe.

5.4 Künstliche Schlüssel

Da es sich bei dem Schlüssel um eine Spalte mit eindeutigen Werten in jeder Zeile handelt, besteht eine Möglichkeit, ihn zu erstellen, darin, zu schummeln – Sie können fiktive eindeutige Werte in jede Zeile schreiben. Dabei handelt es sich um künstliche Schlüssel: erfundener Code, mit dem auf Daten oder Objekte verwiesen wird.

Es ist sehr wichtig, dass der Code aus der Datenbank selbst generiert wird und niemandem außer den Benutzern der Datenbank bekannt ist. Dies unterscheidet künstliche Schlüssel von standardisierten natürlichen Schlüsseln.

Während natürliche Schlüssel den Vorteil haben, vor doppelten oder inkonsistenten Zeilen in einer Tabelle zu schützen, sind künstliche Schlüssel nützlich, weil sie es für Menschen oder andere Systeme einfacher machen, auf die Zeile zu verweisen, und sie beschleunigen Suchvorgänge und Verknüpfungen, weil sie nicht verwendet werden String-Vergleiche (oder mehrspaltige Vergleiche). Schlüssel.

Leihmütter

Als Anker werden künstliche Schlüssel verwendet – egal wie sich die Regeln und Spalten ändern, eine Zeile kann immer auf die gleiche Weise identifiziert werden. Der hierfür verwendete künstliche Schlüssel wird „Ersatzschlüssel“ genannt und erfordert besondere Aufmerksamkeit. Im Folgenden werden wir Surrogate betrachten.

Künstliche Schlüssel ohne Ersatz sind nützlich, um auf eine Zeile von außerhalb der Datenbank zu verweisen. Ein künstlicher Schlüssel identifiziert kurzzeitig Daten oder Objekte: Er kann als URL angegeben, einer Rechnung beigefügt, am Telefon diktiert, von einer Bank bezogen oder auf ein Nummernschild gedruckt werden. (Das Nummernschild eines Autos ist für uns ein natürlicher Schlüssel, aber von der Regierung als künstlicher Schlüssel konzipiert.)

Synthetische Schlüssel sollten unter Berücksichtigung der möglichen Übertragungswege ausgewählt werden, um Tippfehler und Fehler zu minimieren. Es ist zu beachten, dass die Taste gesprochen, ausgedruckt gelesen, per SMS versendet, handschriftlich gelesen, über die Tastatur getippt und in eine URL eingebettet werden kann. Darüber hinaus enthalten einige künstliche Schlüssel, beispielsweise Kreditkartennummern, eine Prüfsumme, um bei Auftreten bestimmter Fehler diese zumindest erkennen zu können.

Beispiele:

  • Für US-Nummernschilder gibt es Regeln zur Verwendung mehrdeutiger Zeichen wie O und 0.
  • Krankenhäuser und Apotheken müssen angesichts der Handschrift von Ärzten besonders vorsichtig sein.
  • Senden Sie einen Bestätigungscode per SMS? Gehen Sie nicht über den Zeichensatz GSM 03.38 hinaus.
  • Im Gegensatz zu Base64, das beliebige Bytedaten kodiert, verwendet Base32 einen begrenzten Zeichensatz, der für Menschen auf älteren Computersystemen bequem zu verwenden und zu handhaben ist.
  • Proquints sind lesbare, schreibbare und aussprechbare Bezeichner. Dabei handelt es sich um Pro-Nonkabel-Quint-Uplets eindeutig verstandener Konsonanten und Vokale.

Denken Sie daran, dass, sobald Sie Ihren künstlichen Schlüssel der Welt vorstellen, die Menschen ihm seltsamerweise besondere Aufmerksamkeit schenken werden. Schauen Sie sich nur die Nummernschilder der „Diebe“ oder das System zur Erstellung aussprechbarer Identifikatoren an, das zum berüchtigten automatischen Fluchgenerator geworden ist.

Auch wenn wir uns auf Zifferntasten beschränken, gibt es Tabus wie den dreizehnten Stock. Während Proquinte eine höhere Informationsdichte pro gesprochener Silbe aufweisen, sind Zahlen in vielerlei Hinsicht auch in Ordnung: in URLs, Pin-Tastaturen und handschriftlichen Notizen, sofern der Empfänger weiß, dass der Schlüssel nur aus Zahlen besteht.

Bitte beachten Sie jedoch, dass Sie in öffentlichen Ziffernschlüsseln keine sequentielle Reihenfolge verwenden sollten, da Sie dadurch Ressourcen durchsuchen können (/videos/1.mpeg, /videos/2.mpeg usw.) und auch Informationen über die Nummer preisgeben Daten. Überlagern Sie eine Zahlenfolge mit einem Feistel-Netz und bewahren Sie die Eindeutigkeit, während Sie die Reihenfolge der Zahlen verbergen.

Das einzige Argument gegen die Deklaration zusätzlicher Schlüssel besteht darin, dass jeder neue einen weiteren eindeutigen Index mit sich bringt und die Kosten für das Schreiben in die Tabelle erhöht. Es hängt natürlich davon ab, wie wichtig Ihnen die Richtigkeit der Daten ist, aber höchstwahrscheinlich sollten die Schlüssel trotzdem deklariert werden.

Es lohnt sich auch, ggf. mehrere künstliche Schlüssel zu deklarieren. Beispielsweise gibt es in einer Organisation Stellenbewerber (Bewerber) und Mitarbeiter (Mitarbeiter). Jeder Mitarbeiter war einmal ein Kandidat und verweist auf Kandidaten mit seiner eigenen Kennung, die auch der Schlüssel des Mitarbeiters sein sollte. Ein weiteres Beispiel: Sie können die Mitarbeiter-ID und den Anmeldenamen als zwei Schlüssel in „Mitarbeiter“ festlegen.

5.5 Ersatzschlüssel

Wie bereits erwähnt, wird eine wichtige Art von künstlichen Schlüsseln als „Ersatzschlüssel“ bezeichnet. Es muss nicht wie andere künstliche Schlüssel prägnant und passabel sein, sondern wird als internes Etikett verwendet, das die Zeichenfolge immer identifiziert. Es wird in SQL verwendet, aber die Anwendung greift nicht explizit darauf zu.

Wenn Sie mit den Systemspalten von PostgreSQL vertraut sind, können Sie sich Surrogate quasi als Datenbankimplementierungsparameter (wie ctid) vorstellen, der sich jedoch nie ändert. Der Ersatzwert wird einmal pro Zeile ausgewählt und danach nie mehr geändert.

Ersatzschlüssel eignen sich hervorragend als Fremdschlüssel, und kaskadierende Einschränkungen müssen angegeben werden, ON UPDATE RESTRICTum der Unveränderlichkeit des Ersatzschlüssels zu entsprechen.

Andererseits sollten Fremdschlüssel zu öffentlich freigegebenen Schlüsseln mit gekennzeichnet werden ON UPDATE CASCADE, um maximale Flexibilität zu gewährleisten. Ein kaskadierendes Update wird auf derselben Isolationsstufe wie die umgebende Transaktion ausgeführt. Machen Sie sich also keine Sorgen über Parallelitätsprobleme – die Datenbank ist in Ordnung, wenn Sie eine strikte Isolationsstufe wählen.

Machen Sie Ersatzschlüssel nicht „natürlich“. Sobald Sie Endbenutzern den Wert des Ersatzschlüssels zeigen oder, schlimmer noch, sie mit diesem Wert arbeiten lassen (insbesondere durch eine Suche), geben Sie dem Schlüssel effektiv einen Wert. Dann kann der angezeigte Schlüssel aus Ihrer Datenbank zu einem natürlichen Schlüssel in der Datenbank einer anderen Person werden.

Indem wir externe Systeme zwingen, andere künstliche Schlüssel zu verwenden, die speziell für die Übertragung entwickelt wurden, können wir diese Schlüssel nach Bedarf ändern, um sich ändernden Anforderungen gerecht zu werden, während gleichzeitig die interne referenzielle Integrität mit Surrogaten gewahrt bleibt.

Automatisches Inkrementieren von INT/BIGINT

Die häufigste Verwendung für Ersatzschlüssel ist die automatisch inkrementierende „bigserial“ -Spalte , auch bekannt als IDENTITY . (Tatsächlich unterstützt PostgreSQL 10 jetzt das IDENTITY-Konstrukt, ebenso wie Oracle, siehe CREATE TABLE.)

Ich glaube jedoch, dass eine automatisch inkrementierende Ganzzahl eine schlechte Wahl für Ersatzschlüssel ist. Diese Meinung ist unpopulär, also lassen Sie es mich erklären.

Nachteile von Serienschlüsseln:

  • Wenn alle Sequenzen bei 1 beginnen und schrittweise erhöht werden, haben Zeilen aus verschiedenen Tabellen dieselben Schlüsselwerte. Diese Option ist nicht ideal, es ist jedoch dennoch vorzuziehen, disjunkte Schlüsselsätze in Tabellen zu verwenden, damit beispielsweise Abfragen nicht versehentlich Konstanten verwechseln JOINund unerwartete Ergebnisse zurückgeben können. (Alternativ könnte man, um sicherzustellen, dass es keine Schnittmengen gibt, jede Folge aus Vielfachen verschiedener Primzahlen konstruieren, aber das wäre ziemlich mühsam.)
  • Der Aufruf nextval() , eine Sequenz im heutigen verteilten SQL zu generieren, führt dazu, dass das gesamte System nicht gut skaliert.
  • Der Verbrauch von Daten aus einer Datenbank, die auch sequentielle Schlüssel verwendet, führt zu Konflikten, da sequentielle Werte systemübergreifend nicht eindeutig sind.
  • Aus philosophischer Sicht ist die sequentielle Zunahme der Zahlen mit alten Systemen verbunden, in denen die Reihenfolge der Zeilen impliziert war. Wenn Sie nun die Zeilen ordnen möchten, dann tun Sie dies explizit mit einer Zeitstempelspalte oder etwas, das in Ihren Daten sinnvoll ist. Andernfalls wird die erste Normalform verletzt.
  • Schwacher Grund, aber diese kurzen Kennungen sind verlockend, es jemandem zu sagen.

UUID

Schauen wir uns eine andere Option an: die Verwendung großer Ganzzahlen (128 Bit), die nach einem Zufallsmuster generiert werden. Algorithmen zur Generierung solcher Universally Unique Identifier (UUIDs) haben eine äußerst geringe Wahrscheinlichkeit, denselben Wert zweimal zu wählen, selbst wenn sie gleichzeitig auf zwei verschiedenen Prozessoren laufen.

In diesem Fall scheinen UUIDs eine natürliche Wahl als Ersatzschlüssel zu sein, nicht wahr? Wenn Sie Zeilen auf einzigartige Weise beschriften möchten, gibt es nichts Besseres als eine eindeutige Beschriftung!

Warum nutzt sie nicht jeder in PostgreSQL? Dafür gibt es mehrere erfundene Gründe und einen logischen, der umgangen werden kann, und ich werde Benchmarks vorstellen, um meinen Standpunkt zu veranschaulichen.

Zuerst werde ich über die weit hergeholten Gründe sprechen. Manche Leute denken, dass UUIDs Zeichenfolgen sind, weil sie in der traditionellen Hexadezimalschreibweise mit einem Bindestrich geschrieben werden: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Tatsächlich haben einige Datenbanken keinen kompakten (128-Bit) UUID-Typ, PostgreSQL hingegen schon und hat eine Größe von zwei bigint, d. h. im Vergleich zur Menge anderer Informationen in der Datenbank ist der Overhead vernachlässigbar.

Auch UUIDs wird zu Unrecht vorgeworfen, sie seien umständlich, aber wer spricht sie aus, tippt sie ein oder liest sie? Wir haben gesagt, dass es sinnvoll ist, künstliche Schlüssel anzuzeigen, aber niemand (per Definition) sollte die Ersatz-UUID sehen. Es ist möglich, dass die UUID von einem Entwickler bearbeitet wird, der SQL-Befehle in psql ausführt, um das System zu debuggen, aber das ist auch schon alles. Und der Entwickler kann auch mit bequemeren Schlüsseln auf Zeichenfolgen verweisen, sofern diese angegeben sind.

Das eigentliche Problem bei UUIDs besteht darin, dass stark randomisierte Werte aufgrund ganzseitiger Schreibvorgänge in das Write-Ahead-Protokoll (WAL) zu einer Schreibverstärkung führen . Der Leistungsabfall hängt jedoch tatsächlich vom UUID-Generierungsalgorithmus ab.

Lassen Sie uns die Schreibverstärkung messen . In Wahrheit liegt das Problem in älteren Dateisystemen. Wenn PostgreSQL auf die Festplatte schreibt, ändert es die „Seite“ auf der Festplatte. Wenn Sie den Computer ausschalten, melden die meisten Dateisysteme immer noch einen erfolgreichen Schreibvorgang, bevor die Daten sicher auf der Festplatte gespeichert sind. Wenn PostgreSQL eine solche Aktion naiv als abgeschlossen ansieht, wird die Datenbank beim nächsten Systemstart beschädigt.

Da PostgreSQL den meisten Betriebssystemen/Dateisystemen/Festplattenkonfigurationen nicht vertrauen kann, um Kontinuität zu gewährleisten, speichert die Datenbank den vollständigen Status der geänderten Festplattenseite in einem Write-Ahead-Protokoll, das zur Wiederherstellung nach einem möglichen Absturz verwendet werden kann. Die Indizierung stark randomisierter Werte wie UUIDs umfasst typischerweise eine Reihe verschiedener Festplattenseiten und führt dazu, dass für jeden neuen Eintrag die gesamte Seitengröße (normalerweise 4 oder 8 KB) in die WAL geschrieben wird. Dabei handelt es sich um das sogenannte Full-Page Write (Full-Page Write, FPW).

Einige Algorithmen zur UUID-Generierung (z. B. „snowflake“ von Twitter oder uuid_generate_v1() in der uuid-ossp-Erweiterung von PostgreSQL) generieren auf jedem Computer monoton steigende Werte. Dieser Ansatz konsolidiert Schreibvorgänge auf weniger Festplattenseiten und reduziert den FPW.

5.6 Schlussfolgerungen und Empfehlungen

Nachdem wir nun die verschiedenen Arten von Schlüsseln und ihre Verwendung kennengelernt haben, möchte ich meine Empfehlungen für deren Verwendung in Ihren Datenbanken auflisten.

Für jede Tabelle:

  • Definieren und deklarieren Sie alle natürlichen Schlüssel.
  • Erstellen Sie einen Ersatzschlüssel <table_name>_idvom Typ UUID mit dem Standardwert uuid_generate_v1(). Sie können ihn sogar als Primärschlüssel markieren. Wenn Sie diesem Bezeichner den Namen der Tabelle hinzufügen, wird dies vereinfacht JOIN, d. h. erhalten JOIN foo USING (bar_id)statt JOIN foo ON (foo.bar_id = bar.id). Geben Sie diesen Schlüssel nicht an Clients weiter und legen Sie ihn überhaupt nicht außerhalb der Datenbank offen.
  • Deklarieren Sie für Zwischentabellen, die durchlaufen werden JOIN, alle Fremdschlüsselspalten als einen einzigen zusammengesetzten Primärschlüssel.
  • Fügen Sie optional einen künstlichen Schlüssel hinzu, der in der URL oder anderen String-Referenzangaben verwendet werden kann. Verwenden Sie ein Feistel-Gitter oder pg_hashids , um automatisch inkrementierende Ganzzahlen auszublenden.
  • Geben Sie eine kaskadierende Einschränkung an, ON UPDATE RESTRICTindem Sie Ersatz-UUIDs als Fremdschlüssel und für künstliche Fremdschlüssel verwenden ON UPDATE CASCADE. Wählen Sie natürliche Schlüssel basierend auf Ihrer eigenen Logik.

Dieser Ansatz stellt die Stabilität interner Schlüssel sicher und ermöglicht und schützt gleichzeitig natürliche Schlüssel. Darüber hinaus bleiben sichtbare künstliche Schlüssel an nichts hängen. Wenn Sie alles richtig verstanden haben, können Sie sich nicht nur auf „Primärschlüssel“ beschränken und alle Möglichkeiten der Verwendung von Schlüsseln nutzen.