PostgreSQL Partitioning: Milliarden Zeilen performant abfragen

Wann Partitioning nötig wird

Ab einer bestimmten Tabellengröße werden selbst B-Tree-Indexes ineffizient. VACUUM dauert Stunden, Backups werden zum Problem, und Queries treffen unnötig große Datenmengen. Partitioning teilt eine große Tabelle in kleinere, verwaltbare Teile.

Partitioning-Strategien

• Range Partitioning: Ideal für Zeitreihen-Daten. Partitionieren Sie nach Monat oder Quartal, Queries, die einen Zeitraum abfragen, scannen nur relevante Partitionen.
• List Partitioning: Für kategorische Daten wie Region, Mandant oder Status. Jede Partition enthält Zeilen mit bestimmten Werten.
• Hash Partitioning: Gleichmäßige Verteilung über eine feste Anzahl Partitionen, gut für Write-heavy Workloads ohne natürlichen Partitionsschlüssel.

Best Practices

• Partition Key im Query: Queries müssen den Partition Key in der WHERE-Klausel enthalten, damit Partition Pruning greift.
• Nicht zu viele Partitionen: Hunderte Partitionen verlangsamen den Query Planner. 12-36 aktive Partitionen sind ein guter Richtwert.
• Automatische Partition-Erstellung: Nutzen Sie pg_partman für automatische Erstellung und Verwaltung von Zeitreihen-Partitionen.
• Indexes pro Partition: Jede Partition hat eigene Indexes, das hält Index-Maintenance schnell.

Ergebnis

In einem E-Commerce-Projekt haben wir eine 2.4-Milliarden-Zeilen Order-Tabelle mit Range Partitioning nach Monat aufgeteilt. Die durchschnittliche Query-Zeit sank von 12 Sekunden auf 80 Millisekunden, ein Faktor 150x.