> Am I correct that out of the box it's implementing a full integrity around "commit" -- ie. The data has to fully hit the disk before the call returns?
Yes, as is the default of any ACID-compliant RDBMS. This is what the "D" in ACID stands for (durability):
> Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash).
There is no way to guarantee durability without fsync, as without fsync there is no guarantee that the data has been written to disk and is not simply cached by the OS. Any database that guarantees durability performs an fsync after every transaction.
> Possibly we may just have a case of a bad default here. Particularly in the context of other posts eg. "SQLite as a file format"
I would argue it is a very sane default. The database system does not know how you value the data that you ask it to store. By default it takes the position that your data is very valuable, and that you would not want to lose it even in the case of a crash.
In some cases that is overkill, but in some cases it is not. The safer default is to be cautious. You can always disable fsync if you find that you need the performance boost. Valuable data that is lost is lost forever.
I do agree that a mode that you describe ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion.
> ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion.
PostgreSQL has such a capability; "synchronous_commit=off". If you do want performance at all costs at the risk of integrity there's a separate "fsync=off". The former is very useful, exactly what we needed on a database application I was developing. The latter I can't see a benefit to using. I'm interested now as to how it's implemented -- which APIs and what assumptions of the filesystem it makes. And therefore whether SQLite would be capable of something similar.
If it exists in SQLite (I can't find it) then it needs promoting and using more.
In general my interactions with SQLite have been good, but its ubiquity seems to be spreading a little plague of disk chatter and bottleneck effects on I/O. It's anecdotal, but a lot of the pauses on my Android phone seem like they might be rooted in this, too.
Yes, as is the default of any ACID-compliant RDBMS. This is what the "D" in ACID stands for (durability):
> Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash).
There is no way to guarantee durability without fsync, as without fsync there is no guarantee that the data has been written to disk and is not simply cached by the OS. Any database that guarantees durability performs an fsync after every transaction.
> Possibly we may just have a case of a bad default here. Particularly in the context of other posts eg. "SQLite as a file format"
I would argue it is a very sane default. The database system does not know how you value the data that you ask it to store. By default it takes the position that your data is very valuable, and that you would not want to lose it even in the case of a crash.
In some cases that is overkill, but in some cases it is not. The safer default is to be cautious. You can always disable fsync if you find that you need the performance boost. Valuable data that is lost is lost forever.
I do agree that a mode that you describe ("I don't care if I lose the latest writes - just don't corrupt my database") would be very useful in many situations, and should perhaps be added/promoted in a more clear fashion.