5 ms·

The default is FULL https://sqlite.org/compile.html#default_synchronous https://sqlite.org/compile.html#default_synchronous >SQLITE_DEFAULT_SYNCHRONOUS=<0-3>

The default is FULL https://sqlite.org/compile.html#default_synchronous https://sqlite.org/compile.html#default_synchronous >SQLITE_DEFAULT_SYNCHRONOUS=<0-3> This macro determines the default value of the PRAGMA synchronous setting. If not overridden at compile-time, the default setting is 2 (FULL). >SQLITE_DEFAULT_WAL_SYNCHRONOUS=<0-3> This macro determines the default value of the PRAGMA synchronous setting for database files that open in WAL mode. If not overridden at compile-time, this value is the same as SQLITE_DEFAULT_SYNCHRONOUS. Many wrappers for sqlite take this advice and change the default, but the default is FULL.

avinassh 1y ago
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!
- eatonphil 1y ago
  Is this sqlite built from source or a distro sqlite? It's possible the defaults differ with build settings.
  - supriyo-biswas 1y ago
    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.
    - ncruces 1y ago
      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).
    - zimpenfish 1y ago
      > 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
- larschdk 1y ago
  Just checked debian/ubuntu/alpine/fedora/arch docker images. All are FULL by default.
- mediumsmart 1y ago
  Same with macports here - 2 (opt/local/bin/sqlite3) and /usr/bin/sqlite3 is 1
nh2 1y ago
From this (linked https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous), does anybody understand EXTRA? > EXTRA provides additional durability if the commit is followed closely by a power loss. means? How can one have "additional" durability, if FULL already "ensures that an operating system crash or power failure will not corrupt the database"? Is it that FULL only protects against "corruption" as stated, but will still lose committed transactions? It seems so from the points on https://stackoverflow.com/questions/58113560/during-power-loss-sqlite-rollbacks-my-db-to-a-point-before-begin https://stackoverflow.com/questions/58113560/during-power-lo... Which is also quite nasty. I want my databases to be fully durable by default, and not lose anything once they have acknowledged a transaction. The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash, after already having acknowledged it to a third-party over the network.
- charleslmunger 1y ago
  >EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss. It depends on your filesystem whether this is necessary. In any case I'm pretty sure it's not relevant for WAL mode.
- agwa 1y ago
  This is what the documentation (https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous) says about EXTRA: > EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode So it only has an effect in DELETE mode; WAL mode doesn't use a rollback journal. That said, the documentation about this is pretty confusing.
  - nh2 1y ago
    Yes, I'm talking about the fact that sqlite in its default (journal_mode = DELETE) is not durable. Which in my opinion is worse than whatever may apply to WAL mode, because WAL is something a user needs to explicitly enable. If it is true as stated, then I also don't find it very confusing, but would definitely appreciate if it were more explicit, replacing "will not corrupt the database" by "will not corrupt the database (but may still lose committed transactions on power loss)", and I certainly find that a very bad default.

avinassh 1y ago

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!

eatonphil 1y ago
Is this sqlite built from source or a distro sqlite? It's possible the defaults differ with build settings.
- supriyo-biswas 1y ago
  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.
  - ncruces 1y ago
    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).
  - zimpenfish 1y ago
    > 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
larschdk 1y ago
Just checked debian/ubuntu/alpine/fedora/arch docker images. All are FULL by default.
mediumsmart 1y ago
Same with macports here - 2 (opt/local/bin/sqlite3) and /usr/bin/sqlite3 is 1

nh2 1y ago

From this (linked https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous), does anybody understand EXTRA? > EXTRA provides additional durability if the commit is followed closely by a power loss. means? How can one have "additional" durability, if FULL already "ensures that an operating system crash or power failure will not corrupt the database"? Is it that FULL only protects against "corruption" as stated, but will still lose committed transactions? It seems so from the points on https://stackoverflow.com/questions/58113560/during-power-loss-sqlite-rollbacks-my-db-to-a-point-before-begin https://stackoverflow.com/questions/58113560/during-power-lo... Which is also quite nasty. I want my databases to be fully durable by default, and not lose anything once they have acknowledged a transaction. The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash, after already having acknowledged it to a third-party over the network.

charleslmunger 1y ago
>EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss. It depends on your filesystem whether this is necessary. In any case I'm pretty sure it's not relevant for WAL mode.
agwa 1y ago
This is what the documentation (https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous) says about EXTRA: > EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode So it only has an effect in DELETE mode; WAL mode doesn't use a rollback journal. That said, the documentation about this is pretty confusing.
- nh2 1y ago
  Yes, I'm talking about the fact that sqlite in its default (journal_mode = DELETE) is not durable. Which in my opinion is worse than whatever may apply to WAL mode, because WAL is something a user needs to explicitly enable. If it is true as stated, then I also don't find it very confusing, but would definitely appreciate if it were more explicit, replacing "will not corrupt the database" by "will not corrupt the database (but may still lose committed transactions on power loss)", and I certainly find that a very bad default.