What are you (or apparently Couchbase) using to execute SQLite commands ? Are you calling the SQLite C API, or using a shim for another language, or using a full third-party SQLite library ?

We use SQLiteCpp which is basically RIIA thin class wrappers around SQLite calls. I totally was not clear that I am posting on behalf of Couchbase and we received the report from a developer of an app using our library, which received a report from one of their users.

I will note that when I enabled PRAGMA fullfsync = ON that the problem either goes away or becomes much less likely to be hit.

Yes. You're exactly seeing the symptoms of what happens when the OS lies about what happens in a sync; the changes to the database aren't durable, and that can result in corruption, depending on what pending changes still queued get dropped by the OS, firmware, and hardware. It's something that user-level code has very little control over.

If you want your data to survive on Apple hardware, you need that pragma and you need to take the performance hit (only down to about the same level of performance that you normally get when running on other operating systems). If you do not do that, the integrity of your data is a house built on sand. (Not all code has those durability requirements; I have an application where I turn off all syncing because I truly don't care about the data if the system resets, but I know for sure that that's a very application-specific decision.)

To be clear, the "lie" part happens on all OSes except macOS. macOS provides the F_FULLFSYNC to cover up for this (also because it is the only OS that can have full control of all hardware used in macs; hardware that does not lie).

Does that mean that all operating systems are prone to this situation? We had another report on Android a year or two ago that we never solved. We came to the conclusion that it was probably faulty SD cards that they were using but does this mean that this same situation can happen on Android and also there is nothing that can be done about it?

EDIT Assuming obedient drives, is this something that the linux syncfs call could help with for Android?

You are experiencing problems which happen a lot on mobile devices, no matter which operating system they use. Mobile devices keep their permanent storage in Flash memory. SD cards are examples of Flash memory. Flash memory has a big problem: writing to it is very slow. Not just a bit slow, but so slow that users would complain your phone isn't working.

So the operating system has two options: either write everything to storage when asked to (so slow it annoys the user) or cache writes in memory until idle (but if the phone loses power or crashes, they're gone).

A good operating system detects when the phone is idle, and writes its cache to storage when the user isn't caring about the phone screen. This is more difficult than it sounds, and takes more memory and battery power than you'd think. iOS (which is a GUI over Darwin, a version of Unix) does this one way. Android does it another way. They both have problems.

So yes, choosing to set synch options mean that saving data takes an annoyingly long time, making your app seem laggy. But it's the only way to do it if your data has to be saved even though the phone may run out of power or crash.

You are already doing the hard part: working out which 'sync' commands actually do sync and which just pretend to.

Yes. This is why people making serious barebones projects often use Apple hardware or any hardware that puts quality before money$$$

borrrden: If you're using the system-provided SQLite, it would be very helpful if you attached reproducing code to a feedback report and shared the ID here. If you're not using the system-provided SQLite, I'm sure Richard would like to know more about your configuration.

We are building our own and statically linking it, thanks for the suggestions!

EDIT Is upstream support for F_BARRIERFSYNC planned?

Yes, reducing divergence between our trees is currently a priority but there's a non-trivial amount of process required. That said, I misspoke in my original reply—barrier vs full syncing is not your problem here.