SQLite WAL Mode Benchmark in Python: DELETE vs WAL Numbers

You've read that WAL is, in the SQLite WAL docs' words, "significantly faster in most scenarios", and you want to know how much faster on your own machine, and whether it actually stops database is locked. This post has one standard-library script that measures both, the output I got when I ran it, and the rules I'd draw from those numbers. Everything below comes from one machine (a Dell Inspiron 7773 with Windows 11 and a Lexar NM620 NVMe SSD). I did not isolate whether the OS, the filesystem or the drive drives the ratios, and other platforms will differ, so run the script yourself before you trust any of them.

SQLite WAL vs DELETE journal mode: what the script measures

• Test 1, inserts: 10,000 single-row INSERTs of a 100-byte string, each in its own transaction, timed for DELETE and WAL journal modes with synchronous=FULL and NORMAL. Five runs per combination, median reported.
• Test 2, contention: a table pre-seeded with 1,000 rows of 100 bytes. One writer thread inserts rows in a loop while one reader thread runs a 51-row range SELECT in a loop on its own connection, for 5 seconds, with synchronous=FULL. The script counts completed operations and database is locked errors for each thread. Five runs per mode, median reported.
• Isolation: every run gets a fresh database in a temp directory that is deleted afterwards.

The script

It uses only sqlite3, threading, statistics and friends. It needs Python 3.12+ because it passes autocommit=True to sqlite3.connect(), so every execute() is its own transaction. The Python sqlite3 docs say the autocommit parameter was added in 3.12.

import os, platform, random, shutil, sqlite3, statistics, sys, tempfile, threading, time

RUNS = 5
N_INSERTS = 10_000
CONTENTION_SECONDS = 5
BUSY_TIMEOUT = 0.0  # seconds; test2 takes an optional second argument to change it


def connect(path, mode, sync, timeout=5.0, set_mode=True):
    # autocommit=True: every execute() is its own transaction (Python 3.12+)
    # connect with the default wait so the setup pragmas can't fail with BUSY
    con = sqlite3.connect(path, timeout=5.0, autocommit=True)
    if set_mode:  # journal_mode is persistent for WAL; setting it can itself hit BUSY
        con.execute(f"PRAGMA journal_mode={mode}")
    con.execute(f"PRAGMA synchronous={sync}")
    con.execute(f"PRAGMA busy_timeout={int(timeout * 1000)}")  # applied after setup pragmas
    return con


def insert_bench(mode, sync):
    d = tempfile.mkdtemp(prefix="walbench_")
    try:
        con = connect(os.path.join(d, "t.db"), mode, sync)
        con.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)")
        t0 = time.perf_counter()
        for i in range(N_INSERTS):
            con.execute("INSERT INTO t (v) VALUES (?)", ("x" * 100,))
        dt = time.perf_counter() - t0
        con.close()
        return dt
    finally:
        shutil.rmtree(d, ignore_errors=True)


def contention_bench(mode, sync):
    d = tempfile.mkdtemp(prefix="walbench_")
    path = os.path.join(d, "t.db")
    try:
        setup = connect(path, mode, sync)
        setup.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)")
        setup.execute("BEGIN")
        for _ in range(1000):
            setup.execute("INSERT INTO t (v) VALUES (?)", ("x" * 100,))
        setup.execute("COMMIT")
        setup.close()

        stop = time.monotonic() + CONTENTION_SECONDS
        res = {"writes": 0, "write_busy": 0, "reads": 0, "read_busy": 0}

        def writer():
            con = connect(path, mode, sync, timeout=BUSY_TIMEOUT, set_mode=False)  # 0: surface BUSY at once
            while time.monotonic() < stop:
                try:
                    con.execute("INSERT INTO t (v) VALUES (?)", ("y" * 100,))
                    res["writes"] += 1
                except sqlite3.OperationalError as e:
                    if "locked" in str(e): res["write_busy"] += 1
                    else: raise
            con.close()

        def reader():
            con = connect(path, mode, sync, timeout=BUSY_TIMEOUT, set_mode=False)
            rnd = random.Random(1)
            while time.monotonic() < stop:
                try:
                    con.execute("SELECT * FROM t WHERE id BETWEEN ? AND ?",
                                (a := rnd.randint(1, 1000), a + 50)).fetchall()
                    res["reads"] += 1
                except sqlite3.OperationalError as e:
                    if "locked" in str(e): res["read_busy"] += 1
                    else: raise
            con.close()

        ts = [threading.Thread(target=writer), threading.Thread(target=reader)]
        [t.start() for t in ts]; [t.join() for t in ts]
        return res
    finally:
        shutil.rmtree(d, ignore_errors=True)


def test1():
    print(f"== Test 1: {N_INSERTS} single-row inserts, one transaction each ==")
    for mode in ("DELETE", "WAL"):
        for sync in ("FULL", "NORMAL"):
            runs = [insert_bench(mode, sync) for _ in range(RUNS)]
            m = statistics.median(runs)
            print(f"{mode:6} sync={sync:6} median {m:7.2f}s  "
                  f"({N_INSERTS/m:8.0f} inserts/s)  all: {[round(r,2) for r in runs]}")


def test2():
    print(f"\n== Test 2: 1 writer + 1 reader thread, {CONTENTION_SECONDS}s, busy_timeout={BUSY_TIMEOUT}s, synchronous=FULL ==")
    for mode in ("DELETE", "WAL"):
        runs = [contention_bench(mode, "FULL") for _ in range(RUNS)]
        med = {k: statistics.median(r[k] for r in runs) for k in runs[0]}
        print(f"{mode:6} median: writes={med['writes']:.0f} write_busy={med['write_busy']:.0f} "
              f"reads={med['reads']:.0f} read_busy={med['read_busy']:.0f}")
        for r in runs: print("        run:", r)


def main():
    global BUSY_TIMEOUT
    if len(sys.argv) > 2:
        BUSY_TIMEOUT = float(sys.argv[2])
    print("Platform:", platform.platform())
    print("Python :", sys.version.split()[0])
    print("SQLite :", sqlite3.sqlite_version)
    print("Temp dir:", tempfile.gettempdir())
    print(f"Median of {RUNS} runs. Laptop numbers on a local SSD.\n")
    which = sys.argv[1] if len(sys.argv) > 1 else "all"
    if which in ("all", "test1"):
        test1()
    if which in ("all", "test2"):
        test2()


if __name__ == "__main__":
    main()

Run python wal_bench.py for both tests, python wal_bench.py test1 / test2 for one, or python wal_bench.py test2 5 to give Test 2 a 5-second busy timeout instead of zero.

Two choices worth knowing about:

• Test 2 defaults to timeout=0. The Python docs give the timeout parameter a default of five seconds: how long the connection waits before raising an OperationalError when a table is locked. With a wait, a lock would show up as slowness instead of errors. With zero, each "database is locked" surfaces immediately and gets counted. Real apps usually set a non-zero timeout, so a variant run below shows what that looks like.
• Each run gets a fresh database, so WAL file growth from one run doesn't leak into the next.

SQLite WAL mode benchmark in Python: results

Below is the output of one complete python wal_bench.py run of the script exactly as printed above, both tests in a single pass. The Platform: line comes from platform.platform(). The hardware named in the intro comes from these two commands on the same machine:

PS> Get-Partition -DriveLetter C | Get-Disk | Select FriendlyName,BusType
FriendlyName         BusType
Lexar SSD NM620 1TB  NVMe

PS> Get-CimInstance Win32_ComputerSystem | Select Manufacturer,Model
Manufacturer  Model
Dell Inc.     Inspiron 7773

The temp directory is on C:, which is that NVMe drive.

Platform: Windows-11-10.0.26200-SP0
Python : 3.12.10
SQLite : 3.49.1
Temp dir: C:\Users\Bekzod\AppData\Local\Temp
Median of 5 runs. Laptop numbers on a local SSD.

== Test 1: 10000 single-row inserts, one transaction each ==
DELETE sync=FULL   median   60.13s  (     166 inserts/s)  all: [66.41, 60.13, 60.75, 58.47, 59.96]
DELETE sync=NORMAL median   45.84s  (     218 inserts/s)  all: [46.83, 45.32, 44.5, 46.09, 45.84]
WAL    sync=FULL   median   14.56s  (     687 inserts/s)  all: [13.77, 15.39, 14.56, 15.79, 14.19]
WAL    sync=NORMAL median    0.55s  (   18210 inserts/s)  all: [0.53, 0.55, 0.54, 0.56, 0.55]

== Test 2: 1 writer + 1 reader thread, 5s, busy_timeout=0.0s, synchronous=FULL ==
DELETE median: writes=829 write_busy=62 reads=307 read_busy=117
        run: {'writes': 829, 'write_busy': 59, 'reads': 110, 'read_busy': 118}
        run: {'writes': 799, 'write_busy': 83, 'reads': 307, 'read_busy': 103}
        run: {'writes': 784, 'write_busy': 74, 'reads': 410, 'read_busy': 110}
        run: {'writes': 833, 'write_busy': 62, 'reads': 372, 'read_busy': 117}
        run: {'writes': 830, 'write_busy': 54, 'reads': 299, 'read_busy': 120}
WAL    median: writes=3387 write_busy=0 reads=41736 read_busy=0
        run: {'writes': 3442, 'write_busy': 0, 'reads': 44553, 'read_busy': 0}
        run: {'writes': 3421, 'write_busy': 0, 'reads': 42383, 'read_busy': 0}
        run: {'writes': 3235, 'write_busy': 0, 'reads': 34276, 'read_busy': 0}
        run: {'writes': 3252, 'write_busy': 0, 'reads': 35987, 'read_busy': 0}
        run: {'writes': 3387, 'write_busy': 0, 'reads': 41736, 'read_busy': 0}

Test 1: 10,000 single-row inserts, one transaction each

Medians of five runs:

Within each row the fastest and slowest runs differ by about 15% at most (WAL/FULL, 13.77 s to 15.79 s, is the widest spread), much less than the gaps between rows. Splitting the ratios so one variable changes at a time, from those medians:

• Journal mode only, FULL both sides: DELETE 60.13 s vs WAL 14.56 s, about 4.1x.
• Journal mode only, NORMAL both sides: DELETE 45.84 s vs WAL 0.55 s, about 83x.
• synchronous only, WAL: FULL 14.56 s vs NORMAL 0.55 s, about 26x. In DELETE mode the same switch gave only about 1.3x (60.13 s vs 45.84 s).
• Both changed: DELETE/FULL to WAL/NORMAL is about 109x. That number changes two settings at once.

I ran only this one machine and OS, so I can't separate the filesystem, the drive and Windows from SQLite itself; a different setup will give different ratios.

The pattern is consistent with what the WAL page describes. With synchronous=FULL, writers sync the WAL on every transaction commit; with NORMAL they don't, and "the checkpoint is the only operation to issue an I/O barrier or sync operation". The PRAGMA docs are explicit about the price, in the synchronous section: "WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash."

So the 4.1x is the journal-mode effect with the same synchronous setting on both sides. The much larger numbers need synchronous=NORMAL, which is a durability decision, not a free speedup.

Test 2: one writer, one reader, five seconds

Both threads use timeout=0 and synchronous=FULL. Medians of five runs, with the min–max across runs in brackets:

In DELETE mode both sides got database is locked: 62 writer errors and 117 reader errors in the median, against none in WAL mode in all five runs. Treat the successful-read counts in DELETE mode as anecdotal: they ranged from 110 to 410 across runs. Also, the two threads share Python's GIL, and the script never forces the writer to stall the reader, so these counts reflect this particular interleaving on this machine, not a worst case.

Why SQLITE_BUSY here. I didn't trace individual errors to a cause. The SQLite file locking docs describe the rollback-journal mechanism: "No new SHARED locks are permitted against the database if a PENDING lock is active" and "Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock." That is the documented behaviour consistent with both sides seeing BUSY in DELETE mode, not proof of the cause in these runs.

In WAL mode the reader completed about 42,000 reads in the same five seconds. That matches what the WAL page promises ("Reading and writing can proceed concurrently"). The same page says what WAL doesn't change: "since there is only one WAL file, there can only be one writer at a time." I tested one writer. With two writer threads WAL doesn't make them parallel, and I didn't measure that case.

What a non-zero busy timeout does

timeout=0 is a measurement choice, not how most apps run: with a busy timeout, errors can become waits. I ran python wal_bench.py test2 5 (5-second busy timeout) to see it:

Platform: Windows-11-10.0.26200-SP0
Python : 3.12.10
SQLite : 3.49.1
Temp dir: C:\Users\Bekzod\AppData\Local\Temp
Median of 5 runs. Laptop numbers on a local SSD.


== Test 2: 1 writer + 1 reader thread, 5s, busy_timeout=5.0s, synchronous=FULL ==
DELETE median: writes=714 write_busy=0 reads=270 read_busy=0
        run: {'writes': 659, 'write_busy': 0, 'reads': 294, 'read_busy': 0}
        run: {'writes': 754, 'write_busy': 0, 'reads': 270, 'read_busy': 0}
        run: {'writes': 714, 'write_busy': 0, 'reads': 223, 'read_busy': 0}
        run: {'writes': 709, 'write_busy': 0, 'reads': 411, 'read_busy': 0}
        run: {'writes': 761, 'write_busy': 0, 'reads': 81, 'read_busy': 0}
WAL    median: writes=3323 write_busy=0 reads=38198 read_busy=0
        run: {'writes': 3421, 'write_busy': 0, 'reads': 44145, 'read_busy': 0}
        run: {'writes': 3008, 'write_busy': 0, 'reads': 33671, 'read_busy': 0}
        run: {'writes': 3228, 'write_busy': 0, 'reads': 33782, 'read_busy': 0}
        run: {'writes': 3323, 'write_busy': 0, 'reads': 38198, 'read_busy': 0}
        run: {'writes': 3470, 'write_busy': 0, 'reads': 45976, 'read_busy': 0}

With the wait, DELETE mode reported zero BUSY errors in all five runs, but the work didn't go up: 714 writes (659–761) and 270 reads (81–411) in five seconds, against 3,323 writes and 38,198 reads in WAL. The reader's median read count in DELETE mode went from 307 to 270 and one run managed only 81 reads, but with that spread I wouldn't call the drop meaningful; what I can say is that the errors turned into waiting, not into throughput. (This variant was a separate run, so its WAL numbers differ a bit from the first.)

Gotchas

• The setup pragmas run with the default five-second wait. connect() opens the connection with timeout=5.0, runs journal_mode and synchronous, and only then sets busy_timeout to the test value, so the zero applies to the measured loops only.
• Set journal_mode=WAL once. The WAL page says the WAL journal mode is persistent and applies to all connections to that file. The script sets synchronous on every connection, and so should you.
• Run time. The medians printed for Test 1 add up to about ten minutes on this machine (60.13 + 45.84 + 14.56 + 0.55 s, times five runs), nearly all of it DELETE mode.

When to use which

These rules are limited to what the WAL page says and what the script showed on this machine:

1. A reader and a writer at the same time: use WAL. 62 writer and 117 reader BUSY errors (medians) in DELETE mode with timeout=0, vs 0 in WAL, on this machine.
2. Many tiny commits you can't batch: WAL with FULL was about 4.1x faster here with the same synchronous setting. Measure on your own disk.
3. synchronous=NORMAL in WAL: only if losing the last few commits after a power cut is acceptable. The PRAGMA docs say committed transactions might roll back. The big ratios are real on this machine, but they buy speed with durability.
4. Don't batch to extremes. I didn't benchmark one big transaction. The WAL page says WAL "works best with smaller transactions" and "does not work well for very large transactions."
5. Not over a network filesystem. "All processes using a database must be on the same host computer."
6. Read-only, single-connection workloads: you gain little. The WAL page says WAL "might be very slightly slower (perhaps 1% or 2% slower)" for read-heavy work. I didn't measure that.

What to do next

Rerun python wal_bench.py test1 with tempfile.mkdtemp pointed at the drive your app's data lives on, and compare your WAL/FULL vs DELETE/FULL ratio to my 4.1x. If yours is close to 1x, the sync cost isn't your bottleneck and switching modes buys you mainly the reader/writer concurrency from Test 2. Then pick FULL or NORMAL by asking what a lost last commit costs you, not by the benchmark ratio, and set a non-zero busy_timeout on each connection.