SQLite (with WAL) doesn't do `fsync` on each commit under default settings

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by avinassh on Sunday August 24, 2025 @12:43PM (#45005665)

hey, I just tested and `NORMAL` is default:

    $ sqlite3 test.db
    
    SQLite version 3.43.2 2023-10-10 13:08:14
    Enter ".help" for usage hints.
    sqlite> PRAGMA journal_mode=wal;
    wal
    sqlite> PRAGMA synchronous;
    1
    sqlite>

edit: fresh installation from homebrew shows default as FULL:

    /opt/homebrew/opt/sqlite/bin/sqlite3 test.db
    SQLite version 3.50.4 2025-07-30 19:33:53
    Enter ".help" for usage hints.
    sqlite> PRAGMA journal_mode=wal;
    wal
    sqlite> PRAGMA synchronous;
    2
    sqlite>

I will update the post, thanks!

[ Parent | on HN ]

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by larschdk on Sunday August 24, 2025 @12:50PM (#45005717)

Just checked debian/ubuntu/alpine/fedora/arch docker images. All are FULL by default.

[ Parent | on HN ]

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by mediumsmart on Sunday August 24, 2025 @02:56PM (#45006696)

Same with macports here - 2 (opt/local/bin/sqlite3)

and /usr/bin/sqlite3 is 1

[ Parent | on HN ]

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by eatonphil on Sunday August 24, 2025 @12:44PM (#45005677)

Is this sqlite built from source or a distro sqlite? It's possible the defaults differ with build settings.

[ Parent | on HN ]

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by supriyo-biswas on Sunday August 24, 2025 @01:23PM (#45005963)

The one which avinassh shows is MacOS's SQLite under /usr/bin/sqlite3. In general it also has some other weird settings, like not having concat() method, last I checked.

[ Parent | on HN ]

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by ncruces on Sunday August 24, 2025 @05:21PM (#45007899)

The Apple built macOS SQLite is something.

Another oddity: misteriously reserving 12 bytes per page for whatever reason, making databases created with it forever incompatible with the checksum VFS.

Other: having 3 different layers of fsync to avoid actually doing any F_FULLFSYNC ever, even when you ask it for a fullfsync (read up on F_BARRIERFSYNC).

[ Parent | on HN ]

Re:SQLite (with WAL) doesn't do `fsync` on each commit under... by zimpenfish on Monday August 25, 2025 @05:28AM (#45011941)

> it also has some other weird settings

You also can't load extensions with `.load` (presumably security but a pain in the arse.)

    user ~ $ echo | /opt/homebrew/opt/sqlite3/bin/sqlite3 '.load'
    [2025-08-25T09:27:54Z INFO  sqlite_zstd::create_extension] [sqlite-zstd] initialized
    user ~ $ echo | /usr/bin/sqlite3 '.load'
    Error: unknown command or invalid arguments:  "load". Enter ".help" for help

[ Parent | on HN ]