Why SQLite in WAL Mode is All You Need for Most Production Backends

When developers build backend applications, they usually choose PostgreSQL or MySQL right away. People often call SQLite a small database, a tool for mobile apps, or something you only use for quick local testing.

That idea ignores a simple fact: for 90% of web apps, network delay to a remote database server takes up much more time than running the query itself.

When your application and database run on the same server, query times drop from 2 to 15 milliseconds down to microsecond speeds. And when you turn on SQLite’s WAL (Write-Ahead Logging) mode, the old limitation of SQLite where writes block reads completely goes away.

In this guide, we will look at how WAL mode works in plain English, how it lets you read and write at the same time, and how to set it up for fast production apps.

1. Traditional Mode vs. Write-Ahead Logging (WAL)

To understand why WAL mode is so helpful, let’s look at how SQLite handles data by default in Rollback Journal mode.

Default Mode: Rollback Journal

In traditional rollback mode, whenever you change data in database.db, SQLite makes a copy of the old data pages into a separate .journal file before updating the main database file.

[ROLLBACK JOURNAL MODE]
  
   Writer Process                    Main Database File
  +------------------+             +--------------------+
  | 1. Lock DB       | ----------> |    database.db     | (FULL LOCK)
  | 2. Copy original |             +--------------------+
  |    pages to      |                       ^
  |    database.journal                      |
  | 3. Overwrite     | ----------------------+
  |    database.db   |
  +------------------+
  
  Readers trying to read database.db must wait until the write finishes.

The problem here is simple: while a process is writing to the database, nobody else can read from it. Readers block writers, and writers block readers.

The Fix: Write-Ahead Logging (WAL) Mode

In WAL mode, SQLite flips this workflow. Instead of changing the main database file directly during a transaction, SQLite appends new changes to a separate file called .wal.

[WRITE-AHEAD LOGGING (WAL) MODE]

   Writer Process                   WAL File                     Main DB File
  +---------------+               +------------------+         +-------------+
  | Appends new   | ------------> |  database.db-wal |         | database.db |
  | changes here  |               +------------------+         +-------------+
  +---------------+                         ^                         ^
                                            |                         |
   Reader Processes                         |                         |
  +---------------+                         |                         |
  | Reads newest  | ------------------------+                         |
  | changes from  |                                                   |
  | the WAL file  |                                                   |
  | (if present)  |                                                   |
  | Fallback to   | --------------------------------------------------+
  | database.db   |
  +---------------+

  READERS NEVER WAIT FOR WRITERS. WRITERS NEVER WAIT FOR READERS.

Because the original pages in database.db are untouched during writes, readers can keep reading without any waiting while writers write to the .wal file.

2. How it Works: The Role of the -shm File

When you open an SQLite database in WAL mode, SQLite creates two extra helper files next to your main database file:

• app.db-wal: The Write-Ahead Log file where new writes are saved first.
• app.db-shm: The Shared Memory index file that helps SQLite quickly find where the latest data lives.

/var/data/
├── app.db       # Main database storage file
├── app.db-wal   # New write transactions file
└── app.db-shm   # Quick shared index file

How Reading Works

1. A reader starts a read query.
2. It checks app.db-shm in memory to ask: “Is the newest version of this data in the WAL file?”
3. If yes, SQLite reads the data from app.db-wal.
4. If no, SQLite reads the data directly from app.db.

Checking app.db-shm in memory takes nanoseconds, keeping reads extremely fast.

3. Checkpointing: Syncing the Log

Over time, changes in the .wal file need to be copied back into the main database.db file. This cleanup step is called checkpointing.

SQLite automatically runs checkpoints in the background (usually whenever the WAL file grows to 1,000 pages or about 4MB). You can also control when checkpoints run:

4. Production Recipe: Recommended Settings

When connecting to SQLite in your backend app, execute these simple commands on every new database connection:

-- 1. Enable Write-Ahead Logging mode
PRAGMA journal_mode = WAL;

-- 2. Safe and fast disk sync settings
PRAGMA synchronous = NORMAL;

-- 3. Wait up to 5000ms if the database is busy instead of failing immediately
PRAGMA busy_timeout = 5000;

-- 4. Increase memory cache size to 64MB
PRAGMA cache_size = -64000;

-- 5. Store temporary tables in memory instead of disk
PRAGMA temp_store = MEMORY;

-- 6. Turn on foreign key checks
PRAGMA foreign_keys = ON;

-- 7. Memory-map database pages into memory for faster access
PRAGMA mmap_size = 2147483648;

Simple Go Connection Example

If you build backends in Go, here is a clean way to open your database:

package main

import (
	"database/sql"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func initDB(dbPath string) (*sql.DB, error) {
	// Enable WAL mode and timeout in the connection string
	dsn := dbPath + "?_journal_mode=WAL&_busy_timeout=5000&_synchronous=NORMAL&_foreign_keys=ON"
	
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, err
	}

	// WAL mode handles many readers at once, but only one write at a time.
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(25)

	log.Println("SQLite connected with WAL mode enabled")
	return db, nil
}

5. Speed Comparison: SQLite WAL vs. Cloud Postgres

Why is local SQLite often faster than a cloud PostgreSQL database? Simple physics.

Even if PostgreSQL finishes a query in microseconds on its server, sending data back and forth over a network connection takes several milliseconds. SQLite avoids network delay completely.

6. When Should You Switch to PostgreSQL?

While SQLite in WAL mode is great for single-server apps and side projects, you should use PostgreSQL or MySQL when:

1. You run multiple web servers that write to the same database: SQLite lives as a file on a single server. If you run 10 separate servers behind a load balancer, they cannot easily share one SQLite file without tools like Litestream or Turso.
2. Heavy Write Traffic: While readers scale easily in WAL mode, only one write transaction happens at a time. If your app handles thousands of write requests every second, SQLite will queue up writes.

Summary

SQLite in WAL mode is one of the simplest and fastest database options available for modern web apps. Paired with a fast server, a single SQLite database running in WAL mode can easily handle millions of requests every day with microsecond response times.