4 ms·

Yes. However, keep in mind it's not just SQLite, but pretty much every program that has any kind of state. For instance, a program may save its state into a fi

Yes. However, keep in mind it's not just SQLite, but pretty much every program that has any kind of state. For instance, a program may save its state into a file every time it changes by truncating the file and then writing new contents. If an user were to copy the file after truncation, but before write, the user would end up with an useless backup. That said, this is rather unlikely to happen in practice. Programs usually do stuff (including saving files) when they are interacted with, so a backup copied with File Explorer has a very high probability of working, even when the application is running. Programs can deal with the issue by performing atomic full writes by performing renames, but most programs don't bother to. SQLite cannot do that, because it cannot afford doing full writes as databases tend to store a lot of data.

adrianmonk 7y ago
Writing a copy and renaming works, but is it the only way, or is it just an approach that a lot of software uses because it's simple and it works? In other words, are there ways that might be more work to implement but also more efficient that would be suitable for a database? I agree the risk is pretty small in practice. Though copying with File Explorer isn't the use case I had in mind. It was automatic scheduled backups. Both Windows and macOS make it pretty easy to set these up with an external hard drive. And those could start running while you're using the computer and/or you could start using the computer while backups are running.
- lolc 7y ago
  An append-only format should work fine with most backup software that copies files. A vacuum phase would still have to do an atomic rename.
- GlitchMr 7y ago
  Back-up software can use NTFS/ReFS Shadow Copy in Windows. This shouldn't cause issues with SQLite. Copying files is only an issue for manual back-ups done without special software.
- zlynx 7y ago
  Some backup software gets the modification time of the file, does the copy, then checks the modification time again. If it changed then it recopies the file. If it exceeds some number of attempts it gives up and reports an error for the file.
Someone 7y ago
The better way of ‘overwriting’ is to write the new file under a new name, and then atomically move it to where the old file was. If you have to overwrite (e.g. because of lack of disk space), the program that rewrites that file should open it in exclusive mode. A back-up tool should open files to backup in a mode that disallows others from writing to it, and retry that a few times, each time after waiting a few seconds. If that keeps failing, there’s a problem. Do you skip backing up the file or retry With a “it’s fine if others are reading” mode, with the risk of getting a hybrid of multiple versions? There is no fully correct answer there. And it gets worse. Not all programs write files in exclusive mode. Some might even open a file multiple times while rewriting it. Also, consistency may work cross-files, for example with Mac OS packages. And then, of course, there’s anti-virus tools. They often work below the file system, reading and writing files without the OS being aware of it. The reason that backup tools work relatively reliable and speedy at all mostly is that most programs that write to files will either regularly close them, or just append to them. Databases are the exception. They tend to keep files permanently open and regularly write to them. If you use SQLite in a ’normal’ application for user data, you should flush data after (about) every write (if you use it for caching, you can be a bit more loose, as its relatively fine to discard the database when it gets corrupted.
- justinclift 7y ago
  > A back-up tool should open files to backup in a mode that disallows others from writing to it, and retry that a few times, each time after waiting a few seconds. Generally the preferred approach for backup programs is to trigger a file system snapshot - after quiescing the disks - and back that up. Sometimes (eg databases), the backup program is able to run application specific logic that works in with this (eg flush all database buffers before the snapshot). For the filesystems that don't offer snapshots, some still have the ability to freeze the file system to allow a consistent backup.
- GlitchMr 7y ago
  As I said in a comment you responded to. > Programs can deal with the issue by performing atomic full writes by performing renames, but most programs don't bother to. SQLite cannot do that, because it cannot afford doing full writes as databases tend to store a lot of data. I'm aware you can do that, but most programs aren't. If you pick any random program, chances are it won't do that.

adrianmonk 7y ago

Writing a copy and renaming works, but is it the only way, or is it just an approach that a lot of software uses because it's simple and it works? In other words, are there ways that might be more work to implement but also more efficient that would be suitable for a database? I agree the risk is pretty small in practice. Though copying with File Explorer isn't the use case I had in mind. It was automatic scheduled backups. Both Windows and macOS make it pretty easy to set these up with an external hard drive. And those could start running while you're using the computer and/or you could start using the computer while backups are running.

lolc 7y ago
An append-only format should work fine with most backup software that copies files. A vacuum phase would still have to do an atomic rename.
GlitchMr 7y ago
Back-up software can use NTFS/ReFS Shadow Copy in Windows. This shouldn't cause issues with SQLite. Copying files is only an issue for manual back-ups done without special software.
zlynx 7y ago
Some backup software gets the modification time of the file, does the copy, then checks the modification time again. If it changed then it recopies the file. If it exceeds some number of attempts it gives up and reports an error for the file.

Someone 7y ago

The better way of ‘overwriting’ is to write the new file under a new name, and then atomically move it to where the old file was. If you have to overwrite (e.g. because of lack of disk space), the program that rewrites that file should open it in exclusive mode. A back-up tool should open files to backup in a mode that disallows others from writing to it, and retry that a few times, each time after waiting a few seconds. If that keeps failing, there’s a problem. Do you skip backing up the file or retry With a “it’s fine if others are reading” mode, with the risk of getting a hybrid of multiple versions? There is no fully correct answer there. And it gets worse. Not all programs write files in exclusive mode. Some might even open a file multiple times while rewriting it. Also, consistency may work cross-files, for example with Mac OS packages. And then, of course, there’s anti-virus tools. They often work below the file system, reading and writing files without the OS being aware of it. The reason that backup tools work relatively reliable and speedy at all mostly is that most programs that write to files will either regularly close them, or just append to them. Databases are the exception. They tend to keep files permanently open and regularly write to them. If you use SQLite in a ’normal’ application for user data, you should flush data after (about) every write (if you use it for caching, you can be a bit more loose, as its relatively fine to discard the database when it gets corrupted.

justinclift 7y ago
> A back-up tool should open files to backup in a mode that disallows others from writing to it, and retry that a few times, each time after waiting a few seconds. Generally the preferred approach for backup programs is to trigger a file system snapshot - after quiescing the disks - and back that up. Sometimes (eg databases), the backup program is able to run application specific logic that works in with this (eg flush all database buffers before the snapshot). For the filesystems that don't offer snapshots, some still have the ability to freeze the file system to allow a consistent backup.
GlitchMr 7y ago
As I said in a comment you responded to. > Programs can deal with the issue by performing atomic full writes by performing renames, but most programs don't bother to. SQLite cannot do that, because it cannot afford doing full writes as databases tend to store a lot of data. I'm aware you can do that, but most programs aren't. If you pick any random program, chances are it won't do that.