PostgreSQL-Migrationen

Was macht PostgreSQL-Migrationen?

Das Wichtigste. Jede Migration besteht aus einem Up/Down-Paar, und das Down-Skript macht das Up-Skript vollständig rückgängig. Jede Migration wird in einer eigenen Transaktion ausgeführt. Es gibt eine Ausnahme: Ein Index auf einer Tabelle, die bereits Daten enthält und in die geschrieben wird, wird mittels CREATE INDEX CONCURRENTLY in einer separaten Migration ohne Transaktion erstellt, ein Befehl pro Datei.

Die Überprüfung der Behauptungen erfolgt in CHECKS.md.

Dateiformat

migrations/
├── 20250524120000_create_users.up.sql
├── 20250524120000_create_users.down.sql
├── 20250525090000_add_users_phone.up.sql
└── 20250525090000_add_users_phone.down.sql

• Das Präfix ist die UTC-Zeit im Format YYYYMMDDHHmmss, der Name ist eine kurze Beschreibung in snake_case.
• *.up.sql wendet die Änderungen an, *.down.sql macht sie rückgängig. Beide Dateien sind erforderlich.
• Eine bereits angewendete Migration darf nicht bearbeitet werden – schreibe stattdessen eine neue.

Typen und Constraints

Stattdessen	Schreibe	Warum
bigserial	bigint GENERATED ALWAYS AS IDENTITY	SQL-Standard; verwende BY DEFAULT, wenn eigene IDs (Import) eingefügt werden müssen
varchar(n)	text NOT NULL CHECK (length(x) <= n)	Die Längenbeschränkung ist eine Datenregel, die sich leichter ändern lässt
timestamp	timestamptz	Speichert einen Zeitpunkt, nicht „Uhrzeit an der Wand“
integer-Flag	boolean
json	jsonb	Binäres Format, Indizes und Operatoren
uuid aus Erweiterung	uuid DEFAULT gen_random_uuid()	Seit PostgreSQL 13 integriert

Die Länge einer Textspalte sollte immer per CHECK eingeschränkt werden, außer bei bekanntermaßen langen Texten (Beschreibung, Kommentar). Ist die maximale Länge unklar, frage den Benutzer.

Daten	Einschränkung
E-Mail	CHECK (length(email) <= 320)
Name	CHECK (length(name) <= 255)
Telefon	CHECK (length(phone) <= 50)
Slug, Code	CHECK (length(code) <= 100)
URL	CHECK (length(url) <= 2048)
Beschreibung	Keine Einschränkung oder CHECK (length(bio) <= 10000)

Tabellenvorlage:

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text        NOT NULL CHECK (length(name) <= 255),
    email      text        NOT NULL CHECK (length(email) <= 320),
    is_active  boolean     NOT NULL DEFAULT true,
    metadata   jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_users_email ON users (email);

Rückgängigmachen

up	down
CREATE TABLE t	DROP TABLE IF EXISTS t
ALTER TABLE t ADD COLUMN c	ALTER TABLE t DROP COLUMN IF EXISTS c
CREATE INDEX ix	DROP INDEX IF EXISTS ix
CREATE INDEX CONCURRENTLY ix	DROP INDEX CONCURRENTLY IF EXISTS ix – ebenfalls ohne Transaktion
CREATE TYPE t AS ENUM (...)	DROP TYPE IF EXISTS t
ALTER TYPE t ADD VALUE 'v'	Es gibt kein DROP VALUE in PostgreSQL – entweder Typ neu erstellen oder leeres Down-Skript
ALTER TABLE t ALTER COLUMN c SET NOT NULL	ALTER TABLE t ALTER COLUMN c DROP NOT NULL
ALTER TABLE t ADD CONSTRAINT c	ALTER TABLE t DROP CONSTRAINT IF EXISTS c

IF EXISTS im Down-Skript stellt sicher, dass das Rückgängigmachen idempotent ist.

Typische Änderungen

Spalte mit Standardwert. Bei konstantem DEFAULT wird die Tabelle nicht neu geschrieben (PostgreSQL 11+), der Befehl ist sofort ausgeführt. Bei volatilem Standardwert (gen_random_uuid(), clock_timestamp()) wird die Tabelle unter einer exklusiven Sperre vollständig neu geschrieben – füge bei einer großen Live-Tabelle die Spalte ohne Standardwert hinzu und befüllen sie in Batches.

ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'user' CHECK (length(role) <= 50);

Fremdschlüssel – erstelle sofort einen Index auf der referenzierenden Spalte: PostgreSQL erstellt ihn nicht automatisch, und ohne ihn werden sowohl das Löschen des Elterneintrags als auch Joins sequenziell durchlaufen.

CREATE TABLE orders (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users (id),
    total   numeric NOT NULL
);
CREATE INDEX ix_orders_user_id ON orders (user_id);

Enum-Wert hinzufügen. ALTER TYPE ... ADD VALUE funktioniert innerhalb einer Transaktion, der neue Wert kann jedoch erst nach dem Commit verwendet werden (55P04: unsafe use of new value). Wenn der Migrator die Migration in einer Transaktion ausführt, füge den Wert hinzu und verwende ihn (UPDATE, DEFAULT, CHECK) in verschiedenen Migrationen.

ALTER TYPE payment_method ADD VALUE 'bank_transfer';

Down: Es gibt kein DROP VALUE. Der Wert ist harmlos – lasse das Down-Skript mit einem Kommentar leer; falls nicht, erstelle den Typ neu (neuen Typ, ALTER COLUMN ... TYPE ... USING, lösche den alten).

Indizes auf Live-Tabellen

Die Tabelle enthält bereits Daten und es wird in sie geschrieben – der Index wird über CREATE INDEX CONCURRENTLY (CIC) erstellt. Ein normaler CREATE INDEX blockiert das Schreiben für die gesamte Dauer des Aufbaus. Ein Index zusammen mit einer neuen Tabelle in derselben Migration wird mit einem normalen CREATE INDEX erstellt. Die Datenbankgröße spielt keine Rolle: Eine hängende Transaktion zerstört den neuen Index bei jeder Größe (siehe unten).

Up:

CREATE INDEX CONCURRENTLY ix_orders_customer_id ON orders (customer_id);

Down:

DROP INDEX CONCURRENTLY IF EXISTS ix_orders_customer_id;

• Separate Migration ohne Transaktion, ein Befehl pro Datei. Innerhalb eines Transaktionsblocks ist CIC verboten („cannot run inside a transaction block“). Mehrere Befehle, die in einer einzelnen Anfrage gesendet werden, führt PostgreSQL als implizite Transaktion aus, und CIC schlägt mit demselben Fehler fehl.
• Wenn der Migrator keine Migration ohne Transaktion unterstützt, lege CIC nicht in eine normale Migration. Sage dem Benutzer, dass dieser Modus benötigt wird (Abschnitt „Anforderungen an den Migrator“), oder führe CIC separat aus und trage die Migration manuell ein.
• CIC wartet auf alle Transaktionen mit altem Snapshot, selbst auf solche, die andere Tabellen lesen. Die Migration kann lange dauern.
• Überprüfe nach dem Anwenden den Index. CIC ist nicht transaktional: Bei einem Fehler bleibt ein INVALID-Index zurück, die Migration selbst zeigt dies nicht an.

SELECT c.relname, i.indisvalid, i.indcheckxmin
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'ix_orders_customer_id';

• indisvalid = false – führe DROP INDEX CONCURRENTLY IF EXISTS aus und erstelle den Index neu, oder REINDEX INDEX CONCURRENTLY.
• Wiederholung einer fehlgeschlagenen CIC-Migration. Ohne IF NOT EXISTS schlägt sie mit „relation already exists“ auf dem INVALID-Index fehl. Mit IF NOT EXISTS ist es schlimmer: Der Befehl überspringt stillschweigend den INVALID-Index, und die Migration gilt als „bestanden“. Verwende daher IF NOT EXISTS nicht in CIC: Entferne zuerst den ungültigen Index und wiederhole dann die Migration.

Index vorhanden, aber Seq Scan

Der Index ist gültig, aber der Optimierer verwendet ihn nicht – überprüfe indcheckxmin (siehe Abfrage oben). true: Abfragen verwenden den Index nicht, solange es Transaktionen gibt, die vor seiner Erstellung gestartet wurden. Grund – UPDATE: In der Tabelle sind HOT-Versionen-Ketten übrig, auf die der Index nicht verweist. Nur INSERT verursacht dies nicht. Erstelle den Index nicht neu und suche nicht nach der Ursache in der Statistik. Finde alte Transaktionen:

SELECT pid, xact_start, backend_xmin, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;

Warte auf deren Abschluss oder beende sie (pg_terminate_backend(pid)) – jedoch nur nach Absprache mit dem Benutzer. Vor dem Release reproduzieren: Öffne in einer Sitzung eine repeatable-read-Transaktion mit Lesen der Tabelle, führe in einer anderen UPDATE-Zeilen und CREATE INDEX aus; indcheckxmin wird true.

Anforderungen an den Migrator

• Jede Migration wird in einer eigenen Transaktion ausgeführt, zusammen mit dem Eintrag im Migrationslogbuch: Eine fehlgeschlagene Migration wird vollständig zurückgerollt, bereits angewendete bleiben erhalten.
• Sperre gegen parallele Ausführung (z.B. advisory lock): Mehrere Replicas, die gleichzeitig starten, wenden nicht dieselbe Migration doppelt an.
• Modus ohne Transaktion für einzelne Migrationen – Markierung in der Datei (z.B. erste Zeile -- migrate:no-transaction). Eine solche Datei wird als einzelner Befehl außerhalb der Transaktion gesendet. Nachteil: Das Anwenden und der Eintrag im Logbuch sind nicht mehr atomar. Fällt der Prozess dazwischen, schlägt die Wiederholung mit „already exists“ fehl; überprüfe indisvalid und trage bei gültigem Index die Migration manuell in das Logbuch ein.
• Erstellung von Dateipaaren ohne Verbindung zur Datenbank.