Temporal Tables PostgreSQL Extension

One useful example is continuous forecasting. Every forecast is done with a certain moving window of historical training data, and each time the forecast is run, this training data is different.

In order to evaluate/backtest the performance of the forecasting algorithm over time, you need to be able to retrieve all the previous training datasets from the database, calculate a performance metric, and then aggregate. This lets you do that on a continuous basis in the database itself, without dumping each training set to secondary storage each time.

Also, in many real life applications, historical data isn’t immutable — data corrections can arrive after the fact that will change state, so it’s usually not sufficient to just use a simple WHERE clause to retrieve a particular time range. This is where time travel becomes really useful.

Turns out, there's a bit of weird stuff required in order to collect orderbook data, which involves recording updates, but also the 'full state' (or at least as much as I can get) periodically. The current plan is to add an interface between the DB and the consumers that makes it work a bit like a temporal table (if we're storing the full state at the beginning of every hour, the previous state along with any updates should expire at the end of the hour).

If postgres could also leverage the temporal tables extension we might prevent needing to store a lot of entries that 'invalidate' previous entries, but then I imagine there's internal overhead with the temporal tables extension as it manages expiry time.

I'm wondering, are there any notes on using the temporal tables extension with the timescaledb extension? Wondering if that's something worth investigating.