2

SQLite has a pragma for journal_mode, which I assume is available across other sql database programs (MySQL, PostgreSQL, MS-SQL, etc.) so this question would also apply to all those database programs which offer journaling mode like SQLite does.

Most modern file-systems (BtrFS, Ext4, NTFS, etc) use journaling. As databases are typically stored upon a journaling file-system, is it not redundant for the database program to also be journaling?

When SQLite journal_mode is enabled, large numbers of updates and/or inserts take an extraordinarily long time to complete compared to journal_mode being disabled.

4

SQLite's pragmas are specific to SQLite; you won't find them (or indeed the PRAGMA statement) in other engines.

As for database "journalling", that's just SQLite's name for its transaction log (see the entry for journal_mode in SQLite's reference). The transaction log is a very different thing to the file-system journal and generally not something you want to disable. Without a transaction log you won't be able to rollback transactions and (as the linked documentation notes): "If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt."

The file-system journal helps ensure that the file-system is consistent (shortly) after a crash; but that says nothing about whether the internal structure of files in that file-system is consistent from the point of view of an application (e.g. SQLite).

In other words: no, it's not redundant and most other databases don't provide any option to disable the transaction log. I'd suggest if your inserts and updates are going slowly, there's probably other reasons (how many changes are made per transaction, etc.)

Journaling is critical to enabling databases to provide the ACID (Atomic, Consistent, Isolated, Durable) guarantees they promise. SQL-ite supports modes: DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF

  • DELETE, TRUNCATE, PERSIST specify different ways of handling the journal file after each transaction commit.

  • WAL - Write-Ahead Logging allows the database file to be updated in-place.

  • MEMORY, OFF - Allow inconsistent state on crash, although OFF "allows the database file to be corrupted using ordinary SQL"

For performance, Write-Ahead Logging (WAL) can sometimes be helpful, or using a database designed for multiple concurrent writers.

2
  • FS journals often don't protect the user's data but only their own metadata. – CL. Feb 22 '17 at 13:44
  • Indeed - I think ext4's default on several distros is meta-data only (but I figured I'd keep things relatively simple for the answer above :) – Dave Jones Feb 22 '17 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.