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.
Best Answer
SQLite's pragmas are specific to SQLite; you won't find them (or indeed the
PRAGMAstatement) in other engines.As for database "journalling", that's just SQLite's name for its transaction log (see the entry for
journal_modein 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,OFFDELETE,TRUNCATE,PERSISTspecify 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, althoughOFF"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.