wshobson/postgresql-table-design

Was macht postgresql-table-design?

Verwenden Sie diese Fähigkeit beim Entwerfen oder Überprüfen eines PostgreSQL-spezifischen Schemas. Sie behandelt Best Practices, Datentypen, Indizierung, Constraints, Leistungsmuster und erweiterte Funktionen.

Einleitung

Diese Fähigkeit bietet eine umfassende Anleitung für das PostgreSQL-Schema-Design. Sie deckt alle Aspekte ab, von grundlegenden Datentypen bis hin zu fortgeschrittenen Leistungsoptimierungen, und ist für Entwickler und KI-Agenten gleichermaßen geeignet.

Datentypen

• Numerische Typen: INTEGER, BIGINT, NUMERIC(p,s) für Präzisionsberechnungen, REAL, DOUBLE PRECISION
• Zeichenketten: TEXT, VARCHAR(n) (bevorzugen Sie TEXT mit Constraints, wenn nötig)
• Binäre Typen: BYTEA
• Datum/Zeit: TIMESTAMPTZ, DATE, TIME, INTERVAL
• Boolesch: BOOLEAN
• JSON/JSONB: JSON für Speicherung, JSONB für Indizierung und Abfragen
• Array: INTEGER[], TEXT[] usw.
• Benutzerdefiniert: ENUM, DOMAIN, zusammengesetzte Typen

Indizierung

Index-Typen

• B-Tree: Standard, gut für Gleichheit und Bereichsabfragen (CREATE INDEX idx_name ON table (col);)
• GiST: Für geometrische Daten, Volltextsuche (CREATE INDEX idx_geom ON table USING GIST (geom);)
• GIN: Für JSONB, Arrays, Volltext (CREATE INDEX idx_json ON table USING GIN (jsonb_col);)
• BRIN: Für große, natürlich sortierte Tabellen (CREATE INDEX idx_brin ON table USING BRIN (date_col);)

Best Practices

• Indizieren Sie Spalten, die in WHERE, JOIN, ORDER BY oder GROUP BY verwendet werden.
• Vermeiden Sie Über-Indizierung; jeder Index verlangsamt Schreibvorgänge.
• Verwenden Sie zusammengesetzte Indizes für häufige Abfragemuster (z. B. CREATE INDEX idx_a_b ON table (a, b);).
• Nutzen Sie partielle Indizes für gefilterte Abfragen (WHERE status = 'active').
• Führen Sie regelmäßig REINDEX in Wartungsfenstern durch.

Constraints

• Primärschlüssel: PRIMARY KEY (id) – eindeutig und nicht NULL
• Fremdschlüssel: FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
• CHECK: CHECK (age >= 0)
• UNIQUE: UNIQUE (email)
• NOT NULL: col_name TYPE NOT NULL
• Ausschluss: EXCLUDE USING GIST (period WITH &&) zur Vermeidung von Überlappungen

Leistungsmuster

Partitionierung

• Bereichspartitionierung: PARTITION BY RANGE (date)
• Listenpartitionierung: PARTITION BY LIST (region)
• Hash-Partitionierung: PARTITION BY HASH (id)

Materialized Views

CREATE MATERIALIZED VIEW mv_summary AS
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

Aktualisieren Sie mit REFRESH MATERIALIZED VIEW mv_summary;.

EXPLAIN ANALYZE

Verwenden Sie EXPLAIN ANALYZE SELECT ... um Abfragepläne zu überprüfen und Engpässe zu identifizieren.

Erweiterte Funktionen

• Volltextsuche: tsvector, tsquery, @@ Operator, GIN-Index
• Window Functions: ROW_NUMBER(), RANK(), SUM() OVER (PARTITION BY ...)
• Common Table Expressions (CTEs): WITH ... AS (...)
• Recursive CTEs: Für hierarchische Daten
• Trigger: CREATE TRIGGER ... AFTER INSERT ON ...
• Erweiterungen: pg_stat_statements, postgis, pg_trgm

Beispiel-Schema

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL CONSTRAINT valid_username CHECK (length(username) > 2),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_users_meta ON users USING GIN (metadata);

Häufige Fallstricke

• NULL vs. NOT NULL: Vermeiden Sie NULL in indizierten Spalten, wenn möglich.
• Over-Indexing: Zu viele Indizes verlangsamen Einfügungen.
• Sequential Scans: Große Tabellen ohne Index führen zu langsamen Abfragen.
• Locking: Änderungen an Schemas in Produktion können zu Blockaden führen.

Fazit

Diese Fähigkeit befähigt Sie, PostgreSQL-Schemas zu entwerfen, die sowohl korrekt als auch leistungsoptimiert sind. Nutzen Sie sie als Referenz beim Erstellen von Tabellen, Indizes und Constraints sowie bei der Optimierung bestehender Datenbanken.