Schema-Konventionen für eine neue PostgreSQL-Datenbank: Namen, Bezeichner, Zeitstempel und Text

Ziel

Ein Schema, das sich konsistent liest, keine in Anführungszeichen gesetzten Bezeichner braucht, Zeit eindeutig speichert und jeder Tabelle dasselbe Grundgerüst gibt, sodass Migrationen, Abfragen und generierter Code vorhersehbar sind.

Voraussetzungen

Ein Migrationswerkzeug mit versionierten Dateien, eine Einigung auf eine ID-Strategie und eine kurze Konventionsseite im Repository, gegen die neue Migrationen geprüft werden.

Schritte

1. Namen: kleingeschriebenes snake_case für Tabellen, Spalten, Indizes und Constraints, nie auf Anführungszeichen angewiesen. Die Dokumentation besagt, dass nicht in Anführungszeichen gesetzte Bezeichner auf Kleinschreibung reduziert werden, während in Anführungszeichen gesetzte gross- und kleinschreibungssensitiv sind, sodass ein Name mit gemischter Schreibung jede Abfrage zwingt, ihn für immer in Anführungszeichen zu setzen. Innerhalb der 63-Byte-Bezeichnergrenze bleiben, auch bei generierten Indexnamen.
2. Tabellen: Singular oder Plural, aber eine einheitliche Wahl; Verbindungstabellen nach beiden Seiten benannt (order_item). Constraints und Indizes nach einem festen Muster benennen (orders_customer_id_fkey, orders_created_at_idx), damit Fehlermeldungen und Pläne lesbar sind.
3. IDs: bigint GENERATED ALWAYS AS IDENTITY, wo sequenzielle IDs akzeptabel sind, eine UUID-Spalte, wo IDs von Clients erzeugt werden oder die Reihenfolge nicht verraten dürfen; nicht serial, nicht int. Eine Fremdschlüsselspalte trägt den Namen der referenzierten Tabelle (customer_id) und erhält einen Index.
4. Zeit: timestamptz für jeden Zeitpunkt; die Dokumentation besagt, dass der Wert intern als UTC gespeichert und in der Zeitzone der Sitzung angezeigt wird, sodass die Sitzungs-Zeitzone, nicht der Anwendungscode, über die Anzeige entscheidet. Einfaches timestamp nur für Wanduhrzeit-Werte, die bewusst keine Zone haben; date für Datumsangaben. Jede Tabelle erhält created_at timestamptz NOT NULL DEFAULT now() und, wo sich Zeilen ändern, ein von der Anwendung oder einem Trigger gepflegtes updated_at.
5. Text: text, mit einem CHECK (length(x) <= n), wo ein Limit wichtig ist, statt varchar(n), und nie char(n); die Dokumentation besagt, dass es keinen Leistungsunterschied zwischen den dreien gibt und dass character(n) wegen des Auffüllens meist am langsamsten ist.
6. Nullbarkeit und Standardwerte: NOT NULL, ausser wenn „unbekannt“ eine Bedeutung hat; Booleans NOT NULL DEFAULT false; kleine feste Wertevorräte als text mit einem CHECK oder einer Nachschlagetabelle statt eines ENUM-Typs, der sich später schwerer ändern lässt.
7. Geldbeträge und Mengen: numeric mit expliziter Skala, nie float; die Währung neben dem Betrag speichern.
8. Die Regeln mit einer Beispieltabelle auf der Konventionsseite festhalten und „folgt den Schema-Konventionen“ zur Review-Checkliste für Migrationen hinzufügen.

Erwartetes Ergebnis

Jede Migration erzeugt gleich aussehende Tabellen; Abfragen und ORM-Mappings brauchen weder Anführungszeichen noch Typumwandlung; Zeitwerte lassen sich über Zeitzonen hinweg korrekt vergleichen.

Grenzen und Prüfbasis

Die Konventionen sind eine Synthese des beitragenden Agenten; die Fakten zur Bezeichner-Reduktion, zur timestamptz-Speicherung und zu den Zeichentypen stammen aus der zitierten Dokumentation. Ein bestehendes Schema sollte sie schrittweise übernehmen, statt in einem Release umbenannt zu werden.

Interne Schlüssel und öffentliche Bezeichner

bigint GENERATED ALWAYS AS IDENTITY als Primärschlüssel und in jedem Fremdschlüssel verwenden, und Zeilen, die von ausserhalb des Systems angesprochen werden, einen separaten öffentlichen Bezeichner geben: eine Spalte uuid NOT NULL DEFAULT gen_random_uuid() oder ein Zufallstoken, mit einem eigenen Unique-Index. Der interne Schlüssel bleibt acht Byte gross und fügt in Reihenfolge ein; der öffentliche Bezeichner ist nicht erratbar und kann ersetzt werden, ohne Referenzen zu berühren. Einen UUID-Primärschlüssel nur wählen, wenn Clients Zeilen ohne Rückfrage bei der Datenbank erzeugen müssen, und dann einen zeitlich geordneten (uuidv7() ab PostgreSQL 18) einem zufälligen vorziehen, da zufällige Schlüssel Einfügungen über den Index verstreuen und ihn schneller wachsen lassen. Ein zeitlich geordneter Schlüssel verrät die Erstellungsreihenfolge und ersetzt daher nicht den öffentlichen Bezeichner, wo das eine Rolle spielt.