{"id":49163,"date":"2025-09-05T13:03:18","date_gmt":"2025-09-05T17:03:18","guid":{"rendered":"https:\/\/mjtsai.com\/blog\/?p=49163"},"modified":"2025-09-08T14:28:43","modified_gmt":"2025-09-08T18:28:43","slug":"sqlite-on-macos-not-acid","status":"publish","type":"post","link":"https:\/\/mjtsai.com\/blog\/2025\/09\/05\/sqlite-on-macos-not-acid\/","title":{"rendered":"SQLite on macOS Not ACID"},"content":{"rendered":"

Jonathan Johnson<\/a> (2022):<\/p>\n

From my investigation, Apple’s version of SQLite instead replaces PRAGMA fullfsync = on<\/code>’s implementation to use F_BARRIERFSYNC<\/code>.<\/p>

SQLite users who are expecting PRAGMA fullfsync<\/code> to provide durability\nguarantees in the event of power failures or kernel panics can override xSync\nvia a custom VFS<\/a> or build SQLite from source.<\/p>

[…]<\/p>

Apple’s documentation clearly states that for any guarantees about data loss due\nto power loss or kernel panic, you must use the fcntl()<\/code> API with the\nF_FULLFSYNC<\/code> command.<\/p>

[…]<\/p>

For most consumer applications, F_BARRIERFSYNC<\/code> will be enough to provide\nreasonable durability with the benefit of performing much more quickly. However,\nthere are some situations where true ACID compliance is desired.<\/p>

[…]<\/p>

It’s very confusing when a feature that’s documented to be specific to\nmacOS<\/a> doesn’t behave as documented on macOS.<\/p><\/blockquote>\n

Although, SQLite itself is open-source, as are many parts of macOS and iOS<\/a>, he says that the source for Apple’s SQLite is not available.<\/p>\n\n

Andrew Ayer<\/a> (Hacker<\/a> News<\/a>):<\/p>\n

Unfortunately, SQLite’s documentation about its durability properties is far from clear. I cannot tell whether SQLite is durable by default, and if not, what are the minimal settings you need to use to ensure durability.<\/p>

[…]<\/p>

A Hacker News commenter who agrees with my reading of the documentation asked Hipp<\/a> how his comment is consistent with the documentation, but received no reply.<\/p>

\nHipp also says that WAL mode used to be durable by default, but it was changed after people complained about poor performance.\nThis surprised me, since I had the impression that SQLite cared deeply about backwards compatibility, and weakening the\ndefault durability setting is a nasty breaking change for any application which needs durability.<\/p>

[…]<\/p>

My takeaway is that if you need durability, you’d better set the synchronous<\/code> option explicitly because who knows what the default is, or what it will be in the future. With WAL mode, FULL seems to suffice. As for DELETE mode, who knows if FULL is enough, so you’d better go with EXTRA to be safe. And if your application might be used on macOS, enable fullfsync<\/code>.\n<\/p><\/blockquote>\n

Previously:<\/p>\n