CODEWITHSUNDEEP
postgresql
Luka Horvat
Luka Horvat

Reputation: 4402

Execute queries at some previous point in time

Is it possible to "remember" some point in time, and give it an identifier, so that I can later reference it in queries and get the results from the database as it was then?

If such a mechanism exists, how lightweight is it? Would it be acceptable to generate tens of there "snapshots" a second?

I think I can get something like this with transactions that expose their IDs, but I'm worried this is too heavy weight and I'm not sure if a transaction can be exited, but not destroyed.

Upvotes: 3

Views: 955

Answers (1)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24551

You can't access data you don't keep, but there are approaches to schema design that let you do this kind of thing. If you're really interested, read about bi-temporal tables and SQL:2011. Two great books are Developing Time-Oriented Database Applications in SQL by Richard Snodgrass (also available as a free PDF on his website) and Bitemporal Data by Tom Johnston.

A less academic approach, which is not bi-temporal but lets you access old data, is described in this talk from 2015.

Another approach, also not bi-temporal, is using the temporal_tables extension (with github here).

I think the biggest challenge is handling old data as your schema evolves over time. For instance, you add a new NOT NULL column. What do you do with all the old records? As far as I know, there is very little that addresses this, either in the research or the tools.

Upvotes: 1

Related Questions