Does vacuum ever result in data loss

(1) By daveryderoxford1 on 2022-06-05 22:43:16 [source]

I have a slowly changing embedded database that I backup regularly. I ensure backups pass the integrity check.

In case of the database being corrupted and failing the integrity check, I want to first attempt to repair the database without losing any data, and if this fails then revert to using my backup.

In the repair process I am considering:

Vacuuming the database
Re-building indices.

Testing I have performed so far indicates that rebuilding indices will never loose any data.

Is this the case with vacuum? Will the vacuum fail rather than loose data?

(2) By Larry Brasfield (larrybr) on 2022-06-05 22:56:32 in reply to 1 [link] [source]

When a database is known to be corrupted then, without knowing more vacuum cannot be counted upon to not "lose" [sic] data. What vacuum does is to rebuild the b-trees which hold the data so that they occupy contiguous pages insofar as that is possible. If, by some misfortune, there are data that have become separated from their proper b-tree, then they will not be preserved by this "compact copy" process. Whether this counts as "losing" is for you say.

Whether a vacuum would fail when some data has become separated the b-tree that constitutes a table is impossible to predict in the general case. It would depend on how the corruption has occurred.

(3) By Richard Damon (RichardDamon) on 2022-06-05 23:09:51 in reply to 1 [link] [source]

My first response to a possible corruption issue is to make a backup of the possibly corrupted database (and related files), and make these copies read-only. If I need to do something that needs write-access to those backups, I make a second copy that is writable. That maximizes the ability to go back to as close to the original version as I can. Once you have corruption, anything you do that changes the database has a chance to lose data.

IF falling back to the backup is acceptable, then maybe you don't need to be as paranoid, but it might still make sense to use a vacuum into operation to see what it recovers. That will have minimal chance of disturbing the original data.

(4) By daveryderoxford1 on 2022-06-06 09:28:11 in reply to 1 [link] [source]

Many thanks, in my specific case, falling back to the backup looks like a safer option than relying on the possibly unpredictable vacuum behavior.

(5) By Ryan Smith (cuz) on 2022-06-06 09:53:06 in reply to 4 [link] [source]

Just to be clear (and slightly pedantic) - VACUUM's behaviour is very predictable, it works every time, all the time, precisely the same.

What is not predictable, is ANY data-operation carried out on a corrupt database file. (VACUUM is no more or less special in this regard than any other such operation).

SQLite has very strong tooling to avoid corruption, and unless you forego the measures (such as FullFSYNC, safe journal mode, etc.) it is extremely hard to impossible to physically corrupt a DB file by SQLite-access alone. If you do not use all the precautionary measures, for reasons of increased speed, network use, or the like, it is possible to end up with a corrupt database, and once you have a corrupt database, ALL bets are off, none of the DB data-operations are guaranteed to work correctly.