4 ms·
> I just open-sourced a streaming replication tool for SQLite called Litestream that does physical replication (raw pages) instead of logical replication (SQL c
by webmaven 6y ago
> I just open-sourced a streaming replication tool for SQLite called Litestream that does physical replication (raw pages) instead of logical replication (SQL commands). Each approach has its pros and cons. Physical replication logs tend to be larger than logical logs but I agree that you avoid a lot of issues if you do physical replication.
Hmm. Not having dug into your solution much, is it safe to say that the physical replication logs have something like logical checkpoints? If so, would it make sense to only keep physical logs on a relatively short rolling window, and logical logs (ie. only the interleaved logical checkpoints) longer?
- benbjohnson 6y agoI suppose you could save both the physical and logical logs if you really needed log term retention. SQLite databases (and b-trees in general) tend to compress well so the physical logging isn't as bad as it sounds. You could also store a binary diff of the physical page which would shrink it even smaller. One benefit to using physical logs is that you end up with a byte-for-byte copy of the original data so it makes it easy to validate that your recovery is correct. You'd need to iterate all the records in your database to validate a logical log. However, all that being said, Litestream runs as a separate daemon process so it actually doesn't have access to the SQL commands from the application.
- webmaven 6y agoSo, no logical checkpoints, logical logging would have to be a separate process. The main reason I would want both is to be certain that I was restoring the DB to a consistent state, even if the application is doing things stupidly (eg. not using transactions correctly. Or at all). I suppose timestamps would be enough to view the logs interleaved or side-by-side in some fashion.