SQLite WAL vs DELETE Journal: Write Throughput Lab

Intro — what this post promises

SQLite’s default journal_mode=DELETE still works. WAL is the mode most production embeds eventually enable — and the folklore is “WAL is faster.” How much, under what commit pattern, and what does a checkpoint actually cost?

This is a hands-on lab with measured numbers:

1. Insert + update throughput for DELETE vs WAL vs OFF at synchronous=FULL.
2. Batched commits (every 50) vs commit-every-row.
3. WAL + NORMAL and OFF + OFF as labeled lower-durability arms.
4. WAL checkpoint cost with wal_autocheckpoint=0 and a real ~4 MiB WAL.

Related links:

• fsync vs fdatasync localhost lab
• mmap vs read / O_DIRECT localhost lab
• flock contention exclusive vs shared lab
• pipe vs tmpfile IPC localhost lab
• Why your average latency graph is lying (p50 / p95 / p99)

Lab honesty (1 Oct 2026 IST): Shared Linux lab box (8 cores, kernel 6.12). Python 3.13.5 / SQLite 3.46.1 on /tmp overlay. No Docker. Affiliates: 0. Single-writer microbench — not multi-reader WAL scaling, not a crash-recovery drill.

Verdict up front: under FULL + commit-every-1, WAL inserted 3.6×~~ faster than DELETE (~~8492 vs 2341 RPS) and updated ~8× faster. Batching commits helped both; OFF was fastest and not durable.

What journal_mode changes

synchronous=FULL (pragma value 2) is the cautious default pairing we lead with. NORMAL (1) and OFF (0) are measured and labeled.

Related links:

• SQLite WAL documentation
• fsync vs fdatasync localhost lab

Lab topology

Python sqlite3 · fresh DB per trial under /tmp
Table: t(id INTEGER PRIMARY KEY, k TEXT, v TEXT) · v = 64-byte payload
Arms: journal_mode × synchronous × commit_every
Shapes: 2000 inserts (+1000 updates) commit/1; 5000 inserts (+2000 updates) commit/50
Also: wal_autocheckpoint=0 → ~20k inserts → PASSIVE/TRUNCATE checkpoint
Metric: batch wall p50 → RPS = n_ops / p50_s

Script: lab-evidence/34-sqlite-wal-vs-delete/results/run_lab.py.

Arm A — commit every insert (synchronous=FULL)

2000 inserts, then 1000 updates (ids 1..1000). p50 batch wall → RPS:

WAL / DELETE insert ≈ 3.63×. Update ≈ 8.0×. OFF wins speed and loses the durability story — keep it in the “know what you disabled” column.

Stability×3 insert p50 (ms): DELETE 808 / 837 / 822; WAL 248 / 184 / 241.

5 000 inserts commit/1 (spot check): DELETE ~2460 RPS vs WAL 13341~~ RPS (~~5.4×).

Arm B — commit every 50

5000 inserts, 2000 updates:

Batching lifts everyone. WAL still ~2.1× DELETE under FULL. NORMAL and OFF jump again — those are fewer/weaker durability barriers, not free lunch.

Related links:

• Why your average latency graph is lying (p50 / p95 / p99)
• nice / ionice CPU and disk priority lab

Arm C — checkpoint cost (honest)

Default runs often showed empty WAL after the batch (autocheckpoint already ran) and sub‑ms “TRUNCATE” no-ops. That would be a misleading headline.

Dedicated arm: PRAGMA wal_autocheckpoint=0, ~20 000 inserts (commit every 100), then checkpoint:

• WAL size before PASSIVE ≈ 4.09 MiB
• wal_checkpoint(PASSIVE) → (0, 992, 992) — 992 frames
• PASSIVE p50 3.82 ms (p95 4.79 ms)
• Follow-up TRUNCATE p50 2.75 ms on this sequence

Checkpoint is cheap here relative to a full DELETE-journal rewrite story — still measure on your volume, especially with huge WALs and readers holding back truncate.

How to read these numbers

• WAL helps most when commits are frequent under FULL — every-row commit is the painful DELETE shape.
• Batch commits shrink the mode gap but WAL still led under FULL.
• NORMAL / OFF are different products; do not paste those RPS into a FULL durability plan.
• Autocheckpoint can hide checkpoint cost; disable it when you intend to time checkpoint.

Related links:

• stdout buffering line vs full localhost lab
• sendfile vs userspace copy localhost lab

Pitfalls we hit (or avoided)

1. Timing checkpoint after autocheckpoint — WAL already empty; fixed with wal_autocheckpoint=0.
2. Calling OFF “just as good” — fastest arm, worst crash story.
3. Ignoring update path — WAL’s update win (~8× at c/1) mattered as much as inserts.
4. Multi-connection reader claims — not measured here.
5. Cloud disk flush myths — this is overlay/virtio localhost, same class as our fsync lab.

Group commit is the other half of the story. Moving from commit-every-1 to commit-every-50 multiplied DELETE insert RPS from ~2.3k to ~74k and WAL from ~8.5k to ~159k. If your ORM auto-commits every row, fixing that often beats debating journal modes on a whiteboard.

Practical checklist

• Default new embeds to WAL unless you have a DELETE-specific reason.
• Keep synchronous=FULL until you have an explicit RPO story for NORMAL.
• Batch commits (or use transactions that match business boundaries).
• Monitor -wal size; schedule/understand checkpoint under load.
• Never ship journal_mode=OFF for data you cannot rebuild.

Related links:

• ulimit soft vs hard EMFILE lab
• context-switch pipe ping-pong localhost lab
• zstd vs gzip vs lz4 compression localhost lab

Synchronous pragma values (quick decoder)

SQLite reports PRAGMA synchronous as integers. On this run:

• 2 = FULL (lead arms)
• 1 = NORMAL (wal_normal_c50)
• 0 = OFF (off_off_c50)

FULL is the apples-to-apples durability setting when comparing DELETE vs WAL. Mixing FULL DELETE against NORMAL WAL inflates the WAL win with a durability cheat — we kept a dedicated NORMAL arm so you can see the dial, not so you paste it into a FULL capacity plan.

File sidecars after WAL arms showed a 32 KiB -shm and, once checkpointed, 0-byte -wal on the default autocheckpoint path. That is why the dedicated autocheckpoint=0 arm matters for checkpoint timing.

Related links:

• epoll vs select FD_SETSIZE localhost lab
• http keepalive vs connection close lab

Versions pinned for this run

• Python 3.13.5 / stdlib sqlite3
• SQLite 3.46.1
• DB files under /tmp on overlay/virtio (same class of storage as the fsync lab)

Verdict

On this box with SQLite 3.46.1, WAL + FULL + commit-every-1 delivered ~8492 insert RPS vs DELETE 2341~~ (~~3.6×), and ~8× on updates. Commit-every-50 under FULL: WAL ~159k vs DELETE ~74k RPS. A ~4 MiB WAL PASSIVE checkpoint landed near 3.8 ms when autocheckpoint was disabled. WAL is not magic — but under frequent commits it earned the folklore.

Evidence path on the lab box: lab-evidence/34-sqlite-wal-vs-delete/results/. Affiliates: 0.