On Sun, Sep 19, 2021 at 3:12 PM Hannu Krosing <hannuk@google.com> wrote:
A side table has the nice additional benefit that we can very easily version the *table structure* so when we ALTER TABLE and the table structure changes we just make a new side table with now-currents structure.
It's true that would allow for perfect capture of changes to the table structure, but how would you query the thing?
If a system versioned table was created with a column foo that is type float, and then we dropped that column, how would we ever query what the value of foo was in the past?
Would the columns returned from SELECT * change based on the timeframe requested?
If we then later added another column that happened to also be named foo but now was type JSONB, would we change the datatype returned based on the time period being queried?
Is the change in structure a system versioning which itself must be captured?
Also we may want different set of indexes on historic table(s) for whatever reason
+1
And we may even want to partition history tables for speed, storage cost or just to drop very ancient history