WAL vs Normal Modes in Sqlite/Room

WAL vs Normal (DELETE/TRUNCATE) journal modes in SQLite/Room, specifically for read/write behavior.

---

🔹 1. Normal mode (DELETE / TRUNCATE journal)

When SQLite is in DELETE or TRUNCATE mode:

• Write path:

  1. Copy the database into a temporary journal file.

  2. Apply changes to the main database.

  3. Delete/truncate the journal.

• Read path:

  • Reads always come from the main database file only.

  • No merging is required.

✅ Advantages:

• Very safe (data goes directly to DB).

• Simple, fewer moving parts.

• No separate -wal file.

❌ Disadvantages:

• Slower for many small writes, because the whole DB (or pages) must be journaled.

• Writers block readers → no concurrent reads while writing.

---

🔹 2. WAL mode (Write-Ahead Logging)

When SQLite is in WAL mode (Room default):

• Write path:

  1. Changes go into the WAL file (database-wal) → very fast append.

  2. The main DB (database) is not touched until a checkpoint merges WAL back into it.

• Read path:

  • Reads come from both main DB + WAL (SQLite merges them internally).

  • So, you see the latest values even if they are only in the WAL file.

✅ Advantages:

• Much faster writes (append-only).

• Readers and writers don’t block each other → great concurrency.

❌ Disadvantages:

• Until checkpoint runs, data only lives in the WAL file.

• On sudden power loss, the last WAL writes may not reach flash (depending on PRAGMA synchronous).

• You can have “old data” if the WAL wasn’t replayed correctly on reboot.

---

🔹 WAL vs Normal Read/Write

---

• WAL → Faster, but you risk stale/old values after reboot if the WAL wasn’t checkpointed.

• Normal (TRUNCATE) → Safer (writes go to DB directly), slightly slower, but in AAOS safety matters more than speed.

✅ Switch to TRUNCATE journal mode (if your use-case tolerates it)
This avoids WAL and writes directly to the main DB:

val db = Room.databaseBuilder(context, AppDatabase::class.java, "app_db")
    .setJournalMode(RoomDatabase.JournalMode.TRUNCATE)
    .build()

✅ Force DB close on shutdown
If you know when your service is being destroyed (like onDestroy), call:

db.close()

This forces pending transactions to sync.