DEV Community

ahmet gedik
ahmet gedik

Posted on

SQLite WAL Mode for Better Concurrent Web Performance

#sqlite #database #php #performance

Introduction

SQLite gets dismissed as a "toy database" for web applications, but with the right configuration it can handle real production traffic. The secret weapon is WAL (Write-Ahead Logging) mode. Here's how I use it to power ViralVidVault — a video platform serving thousands of pages while cron jobs write new data concurrently.

The Problem: Readers Block Writers

In SQLite's default journal mode (DELETE), a write operation locks the entire database. If your cron job is inserting 200 videos, every web request that tries to read the database will wait. On a video platform, this is unacceptable.

The Solution: WAL Mode

WAL mode flips the locking model: readers never block writers, and writers never block readers. Multiple readers can access the database simultaneously while a single writer appends to a separate write-ahead log.

<?php

class Database
{
    private \PDO $pdo;

    public function __construct(string $dbPath)
    {
        $this->pdo = new \PDO("sqlite:{$dbPath}");
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        // Enable WAL mode for concurrent access
        $this->pdo->exec('PRAGMA journal_mode=WAL');

        // Additional performance pragmas
        $this->pdo->exec('PRAGMA synchronous=NORMAL');
        $this->pdo->exec('PRAGMA cache_size=-8000');  // 8MB cache
        $this->pdo->exec('PRAGMA temp_store=MEMORY');
        $this->pdo->exec('PRAGMA mmap_size=268435456'); // 256MB mmap
    }

    public function getPdo(): \PDO
    {
        return $this->pdo;
    }
}
Enter fullscreen mode Exit fullscreen mode

Understanding the Pragmas

Pragma Value Effect
journal_mode=WAL WAL Enables Write-Ahead Logging
synchronous=NORMAL NORMAL Fsync at checkpoints only (safe for WAL)
cache_size=-8000 8MB Larger page cache for reads
temp_store=MEMORY MEMORY Temp tables in RAM
mmap_size=268435456 256MB Memory-map reads for speed

Real-World Scenario

On ViralVidVault, here's what happens during a cron run:

Timeline:
|---- Web request (SELECT videos) ----|
    |---- Cron job (INSERT 200 videos) ----|
        |---- Web request (SELECT categories) ----|
            |---- Web request (SELECT trending) ----|

Without WAL: Web requests WAIT for cron INSERT to finish
With WAL: Everything runs concurrently, zero blocking
Enter fullscreen mode Exit fullscreen mode

Measuring the Difference

I benchmarked both modes with Apache Bench while running a simulated cron insert:

# Simulate cron writing 500 rows
php simulate_cron_write.php &

# Simultaneously benchmark reads
ab -n 1000 -c 10 https://viralvidvault.com/
Enter fullscreen mode Exit fullscreen mode

Results

Metric DELETE mode WAL mode Improvement
Avg response 245ms 92ms 62% faster
P99 response 1,200ms 180ms 85% faster
Failed requests 12 0 100%
Throughput 38 req/s 105 req/s 2.7x

The P99 improvement is the most striking. Without WAL, tail latency spikes during writes. With WAL, response times stay consistent.

WAL Checkpointing

WAL files grow until a checkpoint merges them back into the main database. SQLite auto-checkpoints at 1000 pages by default. You can also trigger manual checkpoints:

// After cron completes all writes
$db->exec('PRAGMA wal_checkpoint(TRUNCATE)');
Enter fullscreen mode Exit fullscreen mode

Caveats

  1. WAL mode is persistent — set it once, it sticks across connections
  2. One writer at a time — WAL doesn't allow concurrent writers (use transactions wisely)
  3. Network filesystems — WAL requires shared memory, won't work on NFS
  4. WAL files — You'll see .db-wal and .db-shm files alongside your database

When SQLite + WAL Is Enough

For sites like viralvidvault.com with:

  • Thousands of daily page views (not millions)
  • Single-server deployment
  • Read-heavy workload (95%+ reads)
  • Periodic batch writes (cron every few hours)

SQLite with WAL mode is not just adequate — it's optimal. Zero config, zero maintenance, zero external dependencies. The entire database is a single file you can back up with cp.

Key Takeaways

  1. Always enable WAL mode for web-facing SQLite databases
  2. Set synchronous=NORMAL for WAL (it's safe and faster)
  3. Use cache_size and mmap_size pragmas for read performance
  4. Checkpoint after batch writes to control WAL file growth
  5. SQLite + WAL handles real production traffic — don't dismiss it

This powers ViralVidVault — your vault of viral videos from around the world.

Top comments (0)