Eine Append-only-Zeitreihentabelle in PostgreSQL entwerfen

Ziel

Eine Tabelle, die einen stetigen Strom zeitgestempelter Zeilen aufnimmt, «Serie X zwischen t1 und t2» schnell beantwortet und alte Daten günstig verwirft, ohne Bloat zu erzeugen.

Voraussetzungen

Eine bekannte Aufbewahrungsdauer, die vorherrschenden Abfrageformen (eine Serie über ein Fenster; Aggregate je Fenster), eine Schätzung der Zeilen pro Tag sowie der Uhrentyp: timestamptz, die dokumentierte Abkürzung für timestamp with time zone, speichert einen absoluten Zeitpunkt und vermeidet Mehrdeutigkeiten durch die Sommerzeit.

Schritte

1. Die Zeile definieren: series_id (Fremdschlüssel auf eine Metadatentabelle), ts timestamptz NOT NULL, Messspalten mit NOT NULL, wo ein fehlender Wert unmöglich ist, und keine Surrogat-ID, ausser Zeilen werden einzeln referenziert. Ein Primärschlüssel auf (series_id, ts) bedient zugleich die Hauptabfrage; die Dokumentation verlangt, dass ein Primärschlüssel oder Unique-Constraint auf einer partitionierten Tabelle alle Spalten des Partitionsschlüssels einschliesst.
2. Die Tabelle mit PARTITION BY RANGE (ts) anlegen und eine Partition je Tag, Woche oder Monat wählen, so dass ein typisches Abfragefenster wenige Partitionen umfasst und die Aufbewahrungsdauer einer ganzen Zahl davon entspricht. Die Dokumentation warnt, dass zu viele Partitionen die Planung verlängern und den Speicherverbrauch erhöhen.
3. Künftige Partitionen im Voraus über einen geplanten Job anlegen (oder ein Werkzeug wie pg_partman). Ein Insert in einen fehlenden Bereich schlägt fehl; eine DEFAULT-Partition fängt solche Zeilen still auf, und die Dokumentation merkt an, dass das spätere Anhängen einer Partition diese dann unter einem ACCESS-EXCLUSIVE-Lock scannt, sofern nicht ein CHECK-Constraint den neuen Bereich ausschliesst.
4. Je nach Abfrageform indizieren: der Primärschlüssel (B-Tree) für Bereichsabfragen je Serie; ein BRIN-Index auf ts für zeitliche Scans über die gesamte Tabelle. Die Dokumentation zu den Indextypen beschreibt BRIN als Speicherung von Zusammenfassungen je Bereich physischer Blöcke, wirksam dort, wo Werte mit der physischen Position korrelieren – was bei Append-only-Daten der Fall ist.
5. Aufbewahrung als ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY umsetzen, optional archivieren, dann DROP TABLE. Die Dokumentation zur Partitionierung hält fest, dass dies weit schneller ist als ein Massen-DELETE und den dadurch verursachten VACUUM-Aufwand vermeidet.
6. Eine Rollup-Tabelle (stündliche oder tägliche Aggregate) ergänzen, die von einem Job befüllt wird, wenn Dashboards lange Zeiträume abfragen.
7. In Batches laden (COPY oder Multi-Row-INSERT), nach Zeit geordnet, damit BRIN-Bereiche eng bleiben.

Erwartetes Ergebnis

Abfragen mit einem Zeitprädikat berühren nur die Partitionen im Bereich (Partition Pruning), Inserts landen in der neuesten Partition, alte Daten verschwinden als reine Metadatenoperation, und das Löschen erzeugt keinen Bloat.

Grenzen und Prüfbasis

Pruning braucht ein Prädikat auf dem Partitionsschlüssel; eine Abfrage nach Serie ohne zeitliche Eingrenzung liest den Index jeder Partition. Updates und ausserhalb der Reihenfolge eintreffende Zeilen schwächen die Korrelation von BRIN. Folgt der zitierten Dokumentation; es werden keine Durchsatz- oder Grössenangaben behauptet.