3 ms·
In MVCC systems like PostgreSQL, if you don't vacuum (garbage collect the old tuples), your database is append-only and you can query as if your transaction was
by wolf550e 4y ago
In MVCC systems like PostgreSQL, if you don't vacuum (garbage collect the old tuples), your database is append-only and you can query as if your transaction was started at some time in the past. I don't know how to set auto-vacuum to have a fixed delay, e.g. keeping 24h of changes, but I bet it can be added if it's not built-in.
- doctor_eval 4y agoI don’t understand why you’re being downvoted and I hope someone will explain.
- cryptonector 4y agoHow do you set the time back for a query? How do you specify what txid to use for a point in time query?
- Too 4y agoThis would just be equivalent to rollback to a checkpoint in time of the whole table. I think the question was more on a row-level. If you are just interested in global time traveling, there are many solutions, such as replaying the oplog from snapshots in time or delayed replication.
- doctor_eval 4y agoMaybe I'm misunderstanding but if you know the txid at the time you're looking for then you can find the value of any specific row at any point in time using xmin and xmax at the row-level (if you're not running vacuum, as the parent suggested). Am I mistaken? The only problem is that you need to keep a map of timestamps to txids so you can find the txid that was valid at a particular moment in time. This doesn't sound to me like a significantly difficult problem, but maybe I'm mistaken. That said, it's not like you need super high time resolution for the use case in question.
- aidos 4y agoLots of people have updated_at time stamps on all tables, so you could probably inspect those to find your way back. I’ve never tried to query the history implicitly hidden in Postgres tables so I’m not sure how possible (or sensible) any of this is.