Comment by dspillett | Hacker News Reader

Comment by dspillett | Hacker News Reader

Parent Full thread

For a full backup of a huge DB a three-hour window is pretty small, but I doubt that is what is happening here.

A far more common setup is regular full backups, often daily though sometimes more/less frequent, with much smaller transaction log backups at high frequency, perhaps every 15 minutes, between. That way you lose at most what has happened since the last log backup, and you can restore to any point in time between the full backup and the last log backup. It takes more effort to restore as you need to first restore the full then restore (replay) the log backups in sequence until the point you care about.

Sometimes there is a third layer between: differential backups. These are usually much smaller than a full backup, while also smaller (and less faf/time to restore) than the log backups for the period they cover, but don't offer point-in-time recovery.

Out of curiosity, how do transaction logs handle things like created_at fields, or randomly generated UUID, which rely on contextual data? Is the server time/rng seed faked for each replayed transaction?

In most cases, transaction logs/write-ahead logs will contain the return value of non-determistic functions like created_at or random uuid, instead of the function call.

The transaction log is everything that gets done, not how it is done, so it can be replayed reliably. How values that are random, time sensitive, or otherwise arbitrary, are derived during a transaction is not important. What is logged is the fact that the values x/y/z were recorded in row 123,456 which is in page 987,654,321¹ which means that when the log is replayed you end up with exactly the same state the original database was in at the point the log is run to.

[1] in fact it could just be logged at the page level, the granularity of the log structure will vary between systems, if logged at the row level it may be the case that the physical datafile after restore is not exactly the same but the data will still be “random” values & all.

Reply on news.ycombinator.com