PostgreSQL: SERIAL oder IDENTITY? Bei Auto-Increment liegen die meisten Schemata daneben

Sep 12, 2023 · Daniele Teti · 6 min read

🌐

Dieser Artikel ist auch in anderen Sprachen verfügbar:
🇪🇸 Español • 🇧🇷 Português • 🇬🇧 English

Wenn es darum geht, in PostgreSQL Primärschlüssel mit Auto-Increment zu erzeugen, hat die Wahl zwischen “Identities” und “Serial”-Typen spürbare Folgen für Datenbankdesign und Performance. In diesem Artikel sehen wir uns die wichtigsten Unterschiede zwischen beiden Varianten an und stellen Best Practices vor, mit denen du fundiert entscheiden kannst.

Identities und Serial-Typen im Überblick

Sowohl “Identities” als auch “Serial”-Typen dienen dazu, automatisch eindeutige Werte für Primärschlüssel zu erzeugen. Umsetzung und Funktionsumfang unterscheiden sich allerdings.

🔔 Eine weitere Möglichkeit, automatisch hochzählende Werte zu erzeugen, sind reine Sequences. Darum geht es in diesem Artikel nicht, denn in vielen Fällen will man die Sequences “hinter” Serials oder Identities nutzen, ohne sich um ihre direkte Verwaltung zu kümmern. In manchen Fällen erlauben Sequences aber eine “ungewöhnliche” Nummerierung (z. B. eine eindeutige Kennung über alle Datensätze der Datenbank oder über eine Gruppe von Tabellen).

Serial-Typen

“Serial” ist ein PostgreSQL-spezifisches Feature, das eine Integer-Spalte anlegt und mit einer Sequence verknüpft. Es ist eine Kurzschreibweise, um eine Sequence und eine Integer-Spalte anzulegen. Serial-Datentypen sind keine echten Typen, sondern nur eine bequeme Notation, um Spalten mit eindeutigen Kennungen anzulegen (ähnlich der Eigenschaft AUTO_INCREMENT, die MySQL, MariaDB, MSSQLServer und andere Datenbanken unterstützen).

Die erzeugten Werte sind auf die folgenden Integer-Datentypen beschränkt.

Die Serial-Typen sind: smallserial, serial und bigserial.

Name	Speicherbedarf	Beschreibung	Wertebereich
smallserial	2 Byte	kleiner automatisch hochzählender Integer	1 bis 32767
serial	4 Byte	automatisch hochzählender Integer	1 bis 2147483647
bigserial	8 Byte	großer automatisch hochzählender Integer	1 bis 9223372036854775807

Eine Tabelle mit einem Serial-Primärschlüssel deklariert man etwa so:

CREATE TABLE people (
    people_id bigserial primary key,
    first_name varchar,
    last_name varchar
)

was der Ausführung folgender Anweisungen entspricht:

CREATE SEQUENCE people_people_id_seq AS bigint;
CREATE TABLE people (
    people_id bigint NOT NULL DEFAULT nextval('people_people_id_seq') primary key ,
    first_name varchar,
    last_name varchar    
);
ALTER SEQUENCE people_people_id_seq OWNED BY people.people_id;

Wie du siehst, ist ein Feld vom Typ bigserial eine Abkürzung für:

eine Spalte vom Typ bigint, deren Default-Werte aus einem Sequence-Generator kommen,
einen NOT NULL-Constraint, damit kein Nullwert eingefügt werden kann,
die Markierung der Sequence als owned by der Spalte, damit sie gelöscht wird, wenn die Spalte oder die Tabelle gelöscht wird.

Mehr zu den Serial-Typen steht in der PostgreSQL-Dokumentation.

Identities

Eingeführt mit PostgreSQL 10, entsprechen “Identities” dem SQL-Standard.
Sie trennen das Konzept der Identity-Erzeugung vom Datentyp und lassen mehr Freiheit bei der Wahl des Datentyps für den Primärschlüssel.
Sie erlauben eine feine Steuerung der Eigenschaften, etwa ob Updates erlaubt sind, und vertragen sich mit verschiedenen Constraints.
Identities sind einfacher zu handhaben.

Eine Tabelle, die eine IDENTITY als automatisch erzeugten Primärschlüssel verwendet, wird so deklariert:

CREATE TABLE people (
   people_id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   first_name varchar,
   last_name varchar
)

oder so:

CREATE TABLE people (
   people_id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   first_name varchar,
   last_name varchar
)

Zur Klausel IDENTITY sagt die PostgreSQL-Dokumentation:

Die Klausel IDENTITY legt die Spalte als Identity-Spalte an. Ihr wird implizit eine Sequence zugeordnet, und die Spalte erhält in neuen Zeilen automatisch Werte aus dieser Sequence. Eine solche Spalte ist implizit NOT NULL.

Schön, aber was ist der Unterschied zwischen den Klauseln ALWAYS und BY DEFAULT?

Die Klauseln ALWAYS und BY DEFAULT bestimmen, wie explizit vom Benutzer angegebene Werte in INSERT- und UPDATE-Befehlen behandelt werden.
Ist in einem INSERT-Befehl ALWAYS gewählt, wird ein vom Benutzer angegebener Wert nur akzeptiert, wenn die INSERT-Anweisung OVERRIDING SYSTEM VALUE angibt. Ist BY DEFAULT gewählt, hat der vom Benutzer angegebene Wert Vorrang.
Ist in einem UPDATE-Befehl ALWAYS gewählt, wird jede Änderung der Spalte auf einen anderen Wert als DEFAULT abgelehnt. Ist BY DEFAULT gewählt, lässt sich die Spalte normal aktualisieren. (Für den UPDATE-Befehl gibt es keine OVERRIDING-Klausel.)

Mehr zu Identities steht in der PostgreSQL-Dokumentation

Best Practices

Also: Was ist besser, und warum?

✅ Klarheit und Standardkonformität: Verwende “Identities”, um dein Schema klarer zu machen. Sie drücken den Zweck der Spalte explizit aus und folgen dem SQL-Standard, was Lesbarkeit und Wartbarkeit verbessert.

✅ Portabilität und Interoperabilität: “Identities” passen besser zu Standard-SQL und erleichtern die Integration, wenn du Datenbanken migrierst oder mit Entwicklern zusammenarbeitest, die die Standardkonventionen kennen.

✅ Datenintegrität und Wartung: Setze auf “Identities”, wenn es darum geht, die Datenintegrität zu wahren und Constraints durchzusetzen. Die Möglichkeit, Eigenschaften zu steuern, bietet einen umfassenden Ansatz für Updates und Constraints. Außerdem ist serial kein echter Typ und lässt sich deshalb nicht in jeder Anweisung verwenden, in der ein echter Feldtyp stehen kann. Du kannst serial als Spaltentyp angeben, wenn du eine Tabelle anlegst oder eine Spalte hinzufügst. Einer bestehenden Spalte die Serial-Eigenschaft zu nehmen oder sie ihr hinzuzufügen, ist dagegen nicht so einfach.

✅ Langfristige Anpassungsfähigkeit: Wenn du künftige Änderungen an den Datenanforderungen erwartest, entscheide dich wegen ihrer Flexibilität für “Identities”. So ist dein Schema für mögliche Änderungen gerüstet.

🔔 ACHTUNG! JETZT KOMMT EINE STURE FAUSTREGEL! 🔔

Verwende IDENTITY!

Für neue Anwendungen sollten Identity-Spalten verwendet werden; du solltest immer eine Identity-Spalte nehmen, es sei denn, du musst alte PostgreSQL-Versionen unterstützen (zur Erinnerung: Identity-Spalten gibt es seit PostgreSQL v10). Das macht deinen Code handhabbarer, sauberer und portabler.

Fazit

Im PostgreSQL-Datenbankdesign hängt die Wahl zwischen “Identities” und “Serial”-Typen für automatisch erzeugte Primärschlüssel von der Komplexität deines Schemas, den Performance-Anforderungen und der langfristigen Anpassungsfähigkeit ab. “Identities” glänzen in Szenarien, die Flexibilität, Klarheit und Standardtreue verlangen, während “Serial”-Typen für einfache, performanceorientierte Situationen taugen. Wenn du die Entscheidung an den Eigenheiten deines Projekts ausrichtest, bekommst du ein robustes und optimiertes Datenbankschema, das zu deinen Anforderungen passt. Am Ende geht es darum, bei automatisch erzeugten Primärschlüsseln in PostgreSQL die Balance zwischen Effizienz, Klarheit und Zukunftssicherheit zu finden.

Weitere Artikel zu PostgreSQL:

PostgreSQL Composite Types: Das Feature zwischen einfachen Spalten und JSON - Fortgeschrittene Techniken der Datenmodellierung
PostgreSQL als Job Queue: Braucht man wirklich Redis oder RabbitMQ? - Praktische PostgreSQL-Muster
LIKE-Abfragen mit pg_trgm beschleunigen - Tipps zur Performance-Optimierung

Schulungen

Wenn du PostgreSQL wirklich beherrschen willst, bietet meine Firma einige sehr gefragte Schulungen an:

Wenn deine Firma eine maßgeschneiderte Schulung braucht, sag uns Bescheid.

Die Schulungen gibt es remote und vor Ort.

Weitere interessante Links: