manutej/postgresql-database-engineering

Was macht postgresql-database-engineering?

Umfassende Fähigkeit für PostgreSQL-Datenbank-Engineering, die Indexierungsstrategien, Query-Optimierung, Performance-Tuning, Partitionierung, Replikation, Backup und Recovery, Hochverfügbarkeit und Produktionsdatenbank-Management abdeckt. Beherrsche fortgeschrittene PostgreSQL-Funktionen wie MVCC, VACUUM-Operationen, Connection Pooling, Monitoring und Skalierungsmuster.

Inhaltsverzeichnis

• Einführung
• Indexierungsstrategien
• Query-Optimierung
• Performance-Tuning
• Partitionierung
• Replikation
• Backup und Recovery
• Hochverfügbarkeit
• Produktionsdatenbank-Management
• Fortgeschrittene Funktionen
• Lizenz

Einführung

Diese Fähigkeit richtet sich an Datenbankadministratoren, DevOps-Ingenieure und Softwareentwickler, die PostgreSQL in Produktionsumgebungen verwalten und optimieren möchten. Sie bietet bewährte Verfahren (Best Practices) und praktische Anleitungen.

Indexierungsstrategien

B-Tree-Index

CREATE INDEX idx_users_email ON users(email);

Hash-Index

CREATE INDEX idx_users_id_hash ON users USING hash(id);

GIN-Index (für Array-Typen oder Volltextsuche)

CREATE INDEX idx_articles_tags ON articles USING gin(tags);

GiST-Index (für geometrische Daten)

CREATE INDEX idx_locations_coords ON locations USING gist(coordinates);

Query-Optimierung

EXPLAIN ANALYZE verwenden

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Häufige Optimierungen

• Vermeide SELECT *
• Nutze LIMIT und OFFSET mit Bedacht
• Setze zusammengesetzte Indizes für Filter in WHERE-Klauseln ein

Performance-Tuning

Konfigurationsparameter in postgresql.conf

shared_buffers = 25% des Arbeitsspeichers
effective_cache_size = 50% des Arbeitsspeichers
work_mem = 64MB
maintenance_work_mem = 256MB

Arbeitslastüberwachung

SELECT * FROM pg_stat_activity WHERE state = 'active';

Partitionierung

Bereichspartitionierung (Range Partitioning)

CREATE TABLE messdaten (
    id SERIAL,
    erstellt_am TIMESTAMP,
    wert NUMERIC
) PARTITION BY RANGE (erstellt_am);

CREATE TABLE messdaten_2024 PARTITION OF messdaten
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Replikation

Streaming-Replikation einrichten

1. Konfiguriere primary_conninfo in der Standby-Instanz.
2. Erstelle einen Replikations-Slot: SELECT pg_create_physical_replication_slot('standby_slot');
3. Starte die Standby-Instanz.

Backup und Recovery

Logisches Backup mit pg_dump

pg_dump -U benutzername -d datenbankname > backup.sql

Physisches Backup mit pg_basebackup

pg_basebackup -U replikationsbenutzer -D /pfad/zum/backup -Fp -Xs -P

Point-in-Time Recovery (PITR)

• Aktiviere WAL-Archivierung: archive_mode = on und archive_command.
• Stelle mit recovery.conf auf einen bestimmten Zeitpunkt wieder her.

Hochverfügbarkeit

Patroni für automatisches Failover

# patroni.yml
scope: postgres
namespace: /service/
name: pg1

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.1:5432
  data_dir: /var/lib/postgresql/data

PgBouncer für Connection Pooling

[databases]
meinedb = host=127.0.0.1 port=5432 dbname=meinedb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
pool_mode = session

Produktionsdatenbank-Management

Überwachung mit pg_stat_statements

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

Log-Analyse mit pgBadger

pgbadger /var/log/postgresql/postgresql.log -o report.html

Wartungsarbeiten: VACUUM und ANALYZE

VACUUM ANALYZE tabellenname;

Fortgeschrittene Funktionen

MVCC (Multi-Version Concurrency Control)

• PostgreSQL verwendet MVCC, um gleichzeitige Lese-/Schreibzugriffe zu ermöglichen, ohne dass Leser blockiert werden.
• Alte Tupelversionen werden nach VACUUM bereinigt.

VACUUM-Operationen

• VACUUM gibt Speicherplatz zurück und aktualisiert Abfragestatistiken.
• VACUUM FULL kompaktiert Tabellen, benötigt aber eine Tabelle-Sperre.
• autovacuum wird automatisch basierend auf Schwellenwerten ausgeführt.

Connection Pooling

• Verwende PgBouncer oder Pgpool-II, um die Anzahl der gleichzeitigen Verbindungen zu verwalten.
• Vermeide Erschöpfung von Verbindungen durch Begrenzung der maximalen Verbindungen (max_connections).

Skalierungsmuster

• Horizontale Skalierung: Lies von Replikaten, schreibe auf den Primary.
• Vertikale Skalierung: Erhöhe Arbeitsspeicher, CPU und Speicherplatz.
• Caching: Nutze Redis oder Memcached, um die Datenbanklast zu reduzieren.

Lizenz

Dieses Projekt ist unter der MIT-Lizenz lizenziert – siehe die LICENSE-Datei für Details.