SQLite WAL Mode for Better Concurrent Web Performance

# php# database# sqlite# performance
SQLite WAL Mode for Better Concurrent Web Performanceahmet gedik

How SQLite WAL mode enables concurrent reads and writes for PHP web applications, with real benchmar

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.