For our time-series data use case data can arrive from telemetry, but then may undergo several iterations of curation and modification. We need to see all of these changes, the data as it arrived, the data after each change and the data is now. SQL Server has support for system-versioned temporal tables and the ability to query data as it was at a point in time.
Does TimescaleDB (or PostgreSQL) have support for this? I’ve found some extensions, but they don’t seem to have widespread support (< 1000 stars, < 20 contributors).
Hi Dave, that’s a great question. I don’t think we have anything internal like SQLServer. I’m not going to share expertise here from my own projects, but for what I see Timescale community doing.
We see some folks also using pgaudit but then you just have access to it via log and not tables, but this doesn’t look your case.
I think you can access all time series related events using composite index and them you can also consider or remove events depending on the version you want to see. You can add_job with an action to serve as a curation pipeline which you could also stream the before/after every batch processed.
You can customize the retention policies as all data can be stored in hypertables.