Eine steile These aus dem Datenbankalltag sorgt für Diskussion: Das einzige wirklich skalierbare DELETE in Postgres sei DROP TABLE. Dahinter steckt eine genaue Beobachtung, wie Postgres mit gelöschten Zeilen umgeht.
drweb.de als bevorzugte Quelle auf Google hinzufügenQualitätsgeprüfte Inhalte direkt in Google News & DiscoverJetzt hinzufügen
Ein großes DELETE in Postgres gibt keinen Speicher frei, sondern legt zusätzliche Arbeit auf die Datenbank. Der PlanetScale-Ingenieur Tom Pang erklärt, warum das so ist und welche Wege besser skalieren.
Das Wichtigste in Kürze
- Große DELETEs erzeugen in Postgres tote Zeilenversionen und zusätzliche Vacuum- und Replikationslast.
- DROP TABLE und TRUNCATE skalieren dagegen weitgehend unabhängig von der Datenmenge.
- Partitionierung verwandelt laufendes Löschen in ein gelegentliches DROP der ältesten Partition.
Warum kostet Löschen mehr, als es bringt?
Postgres hält dank seines Mehrversionen-Verfahrens (MVCC) alte und gelöschte Zeilen zunächst neben den aktuellen. Ein DELETE markiert sie nur als tot und fügt damit Arbeit hinzu, statt sie zu erledigen. Erst ein späterer Vacuum-Lauf gibt die Bytes zur Wiederverwendung frei.
Hinzu kommt die Replikationslast. Jedes Löschen ist technisch ein Schreibvorgang, der zu den Replikaten wandert und andere Schreiber ausbremsen kann. Den freigewordenen Platz bekommt das Betriebssystem dabei in der Regel gar nicht zurück.
Was skaliert stattdessen?
DROP TABLE und TRUNCATE entfernen die Dateien direkt und arbeiten unabhängig von der Datenmenge. Sie hinterlassen keine toten Zeilen und keine Vacuum-Schuld, verlangen aber für kurze Zeit eine harte Sperre auf der Tabelle.
Für laufendes Aufräumen hilft Partitionierung. Beim Ablegen historischer Daten nach Datum in Kindtabellen lassen sich alte Zeiträume später per DROP der ganzen Partition entfernen. Aus vielen teuren DELETEs wird so ein billiges, planbares Entfernen.
Datenbanken bestraft niemand für sauberes Aufräumen, wohl aber für das falsche Werkzeug. Ein Schema, das von Anfang an auf DROP statt DELETE setzt, spart später viel Vacuum-Ärger.
— Markus Seyfferth, Chefredakteur Dr. Web
Wie räumt man einmalig sauber auf?
Für einen einmaligen Großputz empfiehlt Pang einen Umweg über eine Temp-Tabelle. Die zu behaltenden Zeilen wandern in eine temporäre Tabelle, danach folgt ein TRUNCATE der Originaltabelle, anschließend kommen die guten Zeilen zurück. In seinem Beispiel dauerte das nur wenige Minuten.
Steht eine lange Sperre nicht zur Wahl, bleibt das Löschen in kleinen Stapeln, etwa zehntausend Zeilen pro Durchgang. Das hält die Transaktionen kurz und lässt den Autovacuum mithalten. Die Wahl der Datenbank-Umgebung hängt auch am Hosting, Optionen vergleicht unser WordPress Hosting Vergleich. Die ausführliche Begründung samt SQL liefert der PlanetScale-Blog.
