SQLite WAL Mode vs DELETE Mode in Vaultwarden: Configuration and Performance Differences

Vaultwarden configures SQLite with Write-Ahead Logging (WAL) mode to enable concurrent reads during writes, while the default DELETE mode blocks readers and uses rollback journals for every transaction.

Vaultwarden uses SQLite as its default embedded database, configuring it with specific PRAGMA statements that optimize for performance and reliability. Understanding the differences between SQLite WAL mode and the traditional DELETE journal mode helps administrators tune their self-hosted password manager for their specific workload requirements.

How Vaultwarden Configures SQLite WAL Mode

In src/db/mod.rs, Vaultwarden explicitly switches the journal mode from SQLite’s default DELETE behavior to WAL during database initialization. The application first establishes connection parameters for concurrency control and durability:

// src/db/mod.rs
Self::Sqlite => "PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL;".to_string()

Immediately after establishing the connection, Vaultwarden executes a raw SQL query to enable WAL:

// src/db/mod.rs
diesel::sql_query("PRAGMA journal_mode=wal")
    .execute(&mut connection)
    .expect("Failed to turn on WAL");

These settings replace the default DELETE mode, fundamentally changing how Vaultwarden handles concurrent access and disk synchronization.

Key Differences Between WAL and DELETE Modes

Concurrency and Locking Behavior

WAL mode allows multiple readers to access the database while a writer is actively committing changes. Readers operate on the last consistent snapshot without blocking on the writer’s lock. In contrast, DELETE mode (the SQLite default) requires exclusive locks during writes, blocking all readers until the transaction completes and the rollback journal is deleted.

Write Performance and Disk I/O

With WAL mode, writes append to a separate -wal file rather than modifying the main database file directly. This append-only approach avoids the costly file-system syncs required when rewriting database pages in-place. DELETE mode modifies the main database file directly for every transaction, creating and deleting rollback journals that require frequent disk synchronization.

Durability Settings

Vaultwarden sets PRAGMA synchronous = NORMAL, which balances speed and safety by flushing data to the WAL file but not forcing immediate syncs to the main database. DELETE mode typically runs with synchronous = FULL by default, which forces a complete disk sync on every commit for maximum safety at the cost of performance.

Recovery and Crash Safety

In WAL mode, SQLite replays the contents of the -wal file to restore the database to a consistent state after a crash. In DELETE mode, incomplete transactions are automatically rolled back using the rollback journal, but the recovery process does not involve replaying separate log files.

Backup Requirements

WAL mode requires copying both the main database file and the associated -wal file to ensure consistency, or performing a checkpoint to merge the WAL into the main file first. DELETE mode backups only require the main database file since no separate journal files exist during idle states. Vaultwarden’s backup_sqlite function handles this by using read-only connections that automatically manage WAL file inclusion.

Practical Code Examples for Vaultwarden

Enabling WAL Mode in Rust

The following pattern matches Vaultwarden’s initialization logic in src/db/mod.rs, applying the busy timeout and WAL configuration:

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;

let db_url = "sqlite://path/to/db.sqlite3";
let mut conn = SqliteConnection::establish(&db_url)?;

// Apply Vaultwarden's standard PRAGMAs
diesel::sql_query("PRAGMA busy_timeout = 5000")
    .execute(&mut conn)?;
diesel::sql_query("PRAGMA synchronous = NORMAL")
    .execute(&mut conn)?;
diesel::sql_query("PRAGMA journal_mode = wal")
    .execute(&mut conn)?;

Reverting to DELETE Mode

To switch back to the default journal behavior for compatibility with simpler backup tools:

diesel::sql_query("PRAGMA journal_mode = delete")
    .execute(&mut conn)?;

This restores the exclusive locking behavior and eliminates the separate WAL file overhead.

Adjusting Durability Levels

Change the synchronization strategy to match your risk tolerance:

// Maximum durability - every commit syncs to disk
diesel::sql_query("PRAGMA synchronous = FULL")
    .execute(&mut conn)?;

// Balanced approach - Vaultwarden's default
diesel::sql_query("PRAGMA synchronous = NORMAL")
    .execute(&mut conn)?;

Creating Consistent Backups with WAL

Vaultwarden’s backup implementation uses read-only connections to ensure the WAL file is properly included:

// Establish read-only connection for safe backup
let ro_url = "sqlite://path/to/db.sqlite3?mode=ro";
let mut backup_conn = SqliteConnection::establish(&ro_url)?;

// Copy the database file while the app runs
let timestamp = chrono::Utc::now().format("%Y%m%d_%H%M%S");
let backup_path = format!("vaultwarden_backup_{}.sqlite3", timestamp);
std::fs::copy("path/to/db.sqlite3", &backup_path)?;

Summary

• SQLite WAL mode in Vaultwarden is enabled explicitly in src/db/mod.rs via PRAGMA journal_mode=wal, replacing the default DELETE mode.
• Concurrent reads are supported under WAL mode, allowing backup operations and read queries to proceed during active writes.
• Performance improves with WAL’s append-only write pattern compared to DELETE’s in-place file modifications and frequent disk syncs.
• Durability is configurable through PRAGMA synchronous, with Vaultwarden defaulting to NORMAL for a balance of speed and safety.
• Backups require care with WAL mode—either copy both the database and -wal files, or use a read-only connection that handles this automatically.

Frequently Asked Questions

What is the default SQLite journal mode without Vaultwarden's configuration?

SQLite defaults to DELETE mode, where each transaction creates a rollback journal file and modifies the main database directly. Vaultwarden overrides this in src/db/mod.rs to enable WAL mode for better concurrency and performance.

Why does Vaultwarden set a 5-second busy timeout?

The PRAGMA busy_timeout = 5000 setting instructs SQLite to wait up to 5 seconds when encountering a locked database rather than failing immediately. This complements WAL mode’s concurrent read capability by gracefully handling brief lock conflicts from multiple connections.

Can I safely backup a Vaultwarden SQLite database while the application is running?

Yes, but you must use a read-only connection (mode=ro) or ensure the WAL file is copied alongside the main database. Vaultwarden’s backup_sqlite function demonstrates this approach, creating consistent snapshots without stopping the service.

How do I switch from WAL mode back to DELETE mode if needed?

Execute PRAGMA journal_mode = delete on your database connection. This reverts to the traditional journal behavior, though you lose the concurrent read capabilities and may experience increased lock contention during write operations.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}