Write-Ahead Logging

Write-Ahead Logging (WAL) is a technique that ensures data durability and enables crash recovery. All changes are first written to a log file before being applied to the main database, guaranteeing that committed transactions survive system failures.

1. Overview

What is Write-Ahead Logging?

WAL follows a simple principle: write to the log first, then to the database. If a crash occurs, the database can recover by replaying the log.

Why WAL?

Journal Modes

WitDatabase supports two journaling modes:

How WAL Differs from Rollback Journal

2. WAL Operation

WAL File Structure

The WAL file contains a sequence of log entries:

Entry Types

Write Path

When you write data with WAL enabled:

Read Path

Reads don't involve the WAL — they read from the cache or data file:

Concurrent Access

WAL enables concurrent readers and writers:

WAL File Location

The WAL file is created next to the data file:

Database: /path/to/app.witdb
WAL file: /path/to/app.witdb-wal

With encryption enabled:
Database: /path/to/secure.witdb (encrypted)
WAL file: /path/to/secure.witdb-wal (also encrypted)

3. Checkpoints

What is a Checkpoint?

A checkpoint transfers changes from the WAL to the main data file. After a checkpoint, the WAL can be truncated because those changes are now in the data file.

Automatic Checkpoints

WitDatabase performs automatic checkpoints based on WAL size:

// Automatic checkpoint is enabled by default
// Triggers when WAL grows beyond threshold

Manual Checkpoints

You can trigger a checkpoint explicitly:

// Core API
db.Checkpoint();

// SQL Engine
engine.Execute("PRAGMA wal_checkpoint");

// Forces all WAL changes to data file

Checkpoint Process

When to Checkpoint

Automatic (recommended):

• Let WitDatabase manage checkpoints automatically
• Occurs when WAL grows beyond threshold
• Balances performance and disk space

Manual (specific scenarios):

// Before backup
db.Checkpoint();  // Ensure all data is in main file
File.Copy("app.witdb", "backup.witdb");

// Before shutdown
db.Checkpoint();  // Minimize recovery time on next open
db.Dispose();

// Periodically in long-running applications
var timer = new PeriodicTimer(TimeSpan.FromMinutes(30));
while (await timer.WaitForNextTickAsync())
{
    db.Checkpoint();
}

Checkpoint Impact on Performance

Best practice: Let automatic checkpoints handle most cases. Use manual checkpoints only for specific needs like pre-backup.

4. Crash Recovery

How Recovery Works

When WitDatabase opens after a crash, it automatically recovers by replaying the WAL:

Recovery Scenarios

Scenario 1: Crash after COMMIT written to WAL

Scenario 2: Crash before COMMIT written

Scenario 3: Crash during checkpoint

Integrity Verification

WAL entries include CRC32 checksums:

During replay:

• Each entry's CRC is verified
• Corrupted entries are skipped
• Recovery stops at first corruption (partial entry from crash)

Recovery with Encryption

If the database is encrypted, the WAL is also encrypted:

// Encrypted database
var db = WitDatabase.Open("secure.witdb", "password");

// WAL is automatically encrypted with same key
// Recovery works the same way — decrypt then replay

Recovery requires the correct password — without it, WAL entries cannot be decrypted.

Monitoring Recovery

// Recovery happens automatically on open
var db = WitDatabase.Open("app.witdb");

// Check if recovery occurred (if logging is enabled)
// Recovery count is logged to diagnostics

Recovery Time

Recovery time depends on WAL size:

To minimize recovery time:

• Use automatic checkpoints (keeps WAL small)
• Checkpoint before planned shutdowns
• Set appropriate checkpoint threshold

5. Rollback Journal

How Rollback Journal Works

The rollback journal is an alternative to WAL. Instead of writing new values to a log, it saves original values before modifying them.

Configuration

// Connection string
"Data Source=app.witdb;Journal=rollback"

// Builder pattern
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithRollbackJournal()
    .Build();

Journal File Location

Each active transaction has its own journal file:

Database: /path/to/app.witdb
Journal:  /path/to/app.witdb_12345.rollback  (12345 = transaction ID)

Multiple concurrent transactions:
  app.witdb_12345.rollback  (Tx 12345)
  app.witdb_12346.rollback  (Tx 12346)

Recovery with Rollback Journal

On startup, WitDatabase checks for orphaned journal files:

WAL vs Rollback Journal

When to Use Rollback Journal

Use rollback journal when:

• Single-user applications
• Simple deployment requirements
• Disk space is limited
• WAL complexity is not needed

Use WAL (default) when:

• Multiple readers expected
• Write performance is important
• Concurrent access patterns
• Standard database workloads

// Rollback journal for simple desktop app
"Data Source=app.witdb;Journal=rollback"

// WAL for web server (default)
"Data Source=app.witdb;Journal=wal"
// or simply: "Data Source=app.witdb"

6. Configuration

Connection String Properties

Sync Mode

The Sync Writes option controls whether commits wait for disk sync:

// Full durability (default) — commit waits for fsync
"Data Source=app.witdb;Sync Writes=true"

// Faster commits — risk of data loss on OS crash
"Data Source=app.witdb;Sync Writes=false"

Warning: Sync Writes=false can lose committed transactions if the OS crashes or power fails. The transaction is durable once the OS writes to disk, but that timing is unpredictable.

Builder Pattern Configuration

// WAL with defaults
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithWal()
    .Build();

// Rollback journal
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithRollbackJournal()
    .Build();

// WAL with encryption
var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithBTree()
    .WithEncryption("password")
    .WithWal()  // WAL is also encrypted
    .Build();

WAL File Management

// Get WAL size
var walPath = "app.witdb-wal";
if (File.Exists(walPath))
{
    var size = new FileInfo(walPath).Length;
    Console.WriteLine($"WAL size: {size / 1024} KB");
}

// Manual checkpoint to control WAL size
db.Checkpoint();

// WAL is deleted on clean shutdown
db.Dispose();  // WAL truncated, may be deleted

7. Quick Reference

Journal Modes

Connection String Examples

// Default (WAL, sync enabled)
"Data Source=app.witdb"

// Explicit WAL
"Data Source=app.witdb;Journal=wal"

// Rollback journal
"Data Source=app.witdb;Journal=rollback"

// Fast commits (reduced durability)
"Data Source=app.witdb;Sync Writes=false"

// Full configuration
"Data Source=app.witdb;Journal=wal;Sync Writes=true;Transactions=true"

Key Operations

File Locations

Recovery Behavior

Best Practices

General:

• Use WAL (default) for most applications
• Let automatic checkpoints manage WAL size
• Checkpoint before backups

High availability:

• Keep Sync Writes=true (default)
• Monitor WAL file size
• Plan for recovery time based on WAL size

Development/testing:

• Sync Writes=false acceptable for faster tests
• Use :memory: for unit tests (no journaling needed)

Backups:

// Safe backup procedure
db.Checkpoint();  // Ensure WAL is flushed
db.Flush();       // Ensure cache is flushed
// Now copy app.witdb (WAL is empty)
File.Copy("app.witdb", "backup.witdb");