Re:Post-incident review on the Atlassian April 2022 outage
by wolf550e on Saturday April 30, 2022 @06:10PM
(#31219884)
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.
[ Parent
| on HN ]
Re:Post-incident review on the Atlassian April 2022 outage
by cryptonector on Sunday May 01, 2022 @12:57AM
(#31222312)
How do you set the time back for a query? How do you specify what txid to use for a point in time query?
Re:Post-incident review on the Atlassian April 2022 outage
by Too on Sunday May 01, 2022 @02:36AM
(#31222743)
This 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.
Re:Post-incident review on the Atlassian April 2022 outage
by doctor_eval on Sunday May 01, 2022 @02:48AM
(#31222790)
Maybe 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.
Re:Post-incident review on the Atlassian April 2022 outage
by aidos on Sunday May 01, 2022 @03:57AM
(#31223034)
Lots 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.
Re:Post-incident review on the Atlassian April 2022 outage
by doctor_eval on Saturday April 30, 2022 @07:43PM
(#31220589)
I don’t understand why you’re being downvoted and I hope someone will explain.