Die Daten verteilen sich auf mehrere Dateien und man kann die älteren Daten auf günstigeren, langsameren Disks speichern und die aktuellen Daten auf schnelleren.
Die Datenabfrage wird auch sauberer sein, da man nur eine Tabelle und keine zwei hat (und bei allen Abfragen bedenken muss!).
Jein. Auch bei nur einer Tabelle verteilen sich (bei PostgreSQL zumindest) die Daten auf bis zu je 2GB großen Dateien. Eine 100GB Tabelle ist also auf 50 Dateien verteilt.
Ja, man kann Partitionen mit weniger oft benötigten Daten auf langsameren Tablespaces auslagern.
Datenabfrage bezieht sich immer auf die Haupttabelle, man hat aber logisch gesehen mehrere einzellne, die Partitionen halt.
Anhand der WHERE-Condition kann aber via EXCLUSION CONSTRAINTS die Abfrage auf die Partitionen begrenzt werden, die Daten enthalten KÖNNTEN.
Beispiel, Partitionierung nach Monat. Dann hast Du in 10 Jahren 120 einzelne Partitionen. Bei einer Abfrage nach Daten aus dem Januar 2021 können von diesen also schon 119 ausgeschlossen werden - Exclusion Constraint.
Es gäbe in der PostgreSQL-Welt übrigens noch eine andere Lösung für Append-Only-Tabellen: ein BRIN-Index. BRIN steht für Block Range INdex und ist im Prinzip eine Partitionierung via Index und ohne wirkliche Partitionierung.
Führt hier aber wohl dann doch zu weit ...