wal_checkpoint Counted 15,000 Frames, Moved Zero

On the server behind the site you are reading right now, the database that holds runtime data has a write-ahead log sitting next to it — subscriptions, reactions and rate-limit counters all live there:

-rw-r--r-- 1 node node 40628224 Oct 10 05:16 blog.db
-rw-r--r-- 1 node node    32768 Oct 10 05:34 blog.db-shm
-rw-r--r-- 1 node node   692192 Oct 10 05:33 blog.db-wal

692,192 bytes. The page size is 4096, so that is exactly 168 frames: (692192 - 32) / (4096 + 24) = 168. The container had restarted at 05:15 that morning, so this is the trace left by eighteen minutes of traffic. 676 KiB next to a 38.7 MiB database. Healthy.

The trouble is that for a long time I couldn't honestly call that number healthy, because I didn't know what healthy looked like. Should I worry when the WAL file grows? Has something jammed if it never shrinks? What is wal_checkpoint actually telling me when I call it? I sat down and measured, and found something more uncomfortable than I expected: a checkpoint doesn't report an error when it does no work at all.

Measurement environment. Every experiment here ran on this Mac against SQLite 3.53.0, the version bundled by better-sqlite3 12.9.0; the production container uses the same version, so the lab and the server share one engine. The PRAGMA readings on the server were taken through its own sqlite3 3.45.1 shell, opened read-only. On the day this was written, the newest SQLite release is 3.54.0 (9 October 2026). I state the version because every figure below is behaviour-dependent: releases 3.51.3 and 3.53.0 fixed a data-corruption bug known as the "WAL-reset bug", so this machinery has been touched recently.

First, the vocabulary: SQLite's WAL is not PostgreSQL's WAL

The same three letters describe two different things, and that confusion sends people looking in the wrong place. In PostgreSQL, the WAL is a durable stream of changes; it underpins replication and archiving, and it has its own separate list of reasons for eating your disk: retained replication slots, a failing archive command, wal_keep_size.

In SQLite, the WAL is none of that. It is a single local file whose job is to temporarily hold pages that have not yet been written into the main database. The official documentation's own definition is plain: moving WAL content back into the main file is called a checkpoint. No archive, no replication. Just a staging area and the mechanism that drains it.

Which raises the question: when the staging area is drained, does the file shrink?

No. And I saw it most clearly in my own deploy

This blog's publishing pipeline runs scripts/migrate-content.mjs on every deploy, rendering every MDX file into content.db. I ran it locally in a worktree based on origin/main and sampled both the database and the WAL size every half second. The run took 27.7 seconds and wrote 2,652 articles (TR + EN). The sampler wrote a line every half second; the table below shows every fourth one:

second	content.db	content.db-wal
6.0	4,096	37,112
8.1	4,096	3,831,632
10.1	5,808,128	5,986,392
14.1	14,725,120	5,986,392
20.2	27,529,216	5,986,392
26.2	40,591,360	5,986,392
32.2	55,951,360	5,986,392

Look at the right-hand column. In the full trace it reaches 5,986,392 bytes at second 8.6, and for the remaining 24 seconds it does not move by one byte. The left-hand column, over the same stretch, grows from 5.8 MB to 56 MB — close to tenfold.

5,986,392 bytes is 1,453 frames. Yet the automatic checkpoint threshold is 1,000 pages. So the file crossed the threshold, the checkpoint ran and the file stayed that size. The "Avoiding Excessively Large WAL Files" section of the documentation puts it this way: a checkpoint does not normally truncate the WAL file (unless the journal_size_limit pragma is set); it merely causes SQLite to start overwriting the WAL file from the beginning.

I have left the parenthetical in italics, because I will come back to it: it is not quite right, and I will measure why.

The distinction to draw here is the one I kept confusing for a long time:

• File size is a high-water mark. Once it has reached 1,453 frames, it stays there.
• The number of live frames inside the file is independent of that. It can drop to zero after a checkpoint while the file remains 5.9 MB.

ls -la shows you only the first one. And the first one tells you nothing about the second.

Automatic checkpoints are always PASSIVE, and PASSIVE never waits for a reader

The PRAGMA wal_autocheckpoint documentation has two sentences, and the second is far more important than the first. The first says the default is 1,000 pages. The second: all automatic checkpoints are PASSIVE.

PASSIVE is defined as checkpointing as many frames as possible without waiting for any reader or writer. If it is blocked it doesn't wait; it quietly does less work — or none at all.

To see this I copied content.db, put the copy in WAL mode, and ran two processes. The writer opens a small transaction every 400 ms updating 600 rows; every fifth round it also calls PRAGMA wal_checkpoint(PASSIVE) by hand and prints the three columns it returns. With no reader around:

round  wal_bytes  wal_frames  busy  ckpt_wal  ckpt_moved
1      2472032    600
2      4944032    1200
5      4944032    1200         0     600       600
10     4944032    1200         0     600       600
15     4944032    1200         0     600       600

The file parks at 1,200 frames, and each checkpoint appears to move back all 600 frames in the WAL. The two numbers are equal. The system is breathing.

Then I changed one single thing. I opened a second process that did this, and nothing else:

BEGIN DEFERRED;
SELECT count(*) FROM posts;
-- then wait 20 seconds

One open read transaction. It modifies no rows, it finished its SELECT long ago, and from the outside it looks like it is holding nothing. I ran the same writer again:

round  wal_bytes  wal_frames  busy  ckpt_wal  ckpt_moved
1      2472032    600
5      12360032   3000         0     3000      0
10     24720032   6000         0     6000      0
15     37080032   9000         0     9000      0
20     49440032   12000        0     12000     0
25     61800032   15000        0     15000     0
30     74160032   18000        0     18000     0

Exactly 600 frames per round, thirty rounds, 18,000 frames. 74,160,032 bytes — the WAL file is 74 MB while the database itself is 59.8 MB. In half a minute, because of one open SELECT.

In this experiment I opened the reader before any of the writes, and that ordering is what determines the result. The reader's snapshot was fixed before the first write, so there is not a single frame that can be backfilled, and the third column stays at zero for all thirty rounds. Open your reader in the middle of the writes instead and you will see a number above zero: the frames accumulated until that moment can still move, the ones after it get stuck.

(I didn't chase down why each row maps to exactly one page; what matters here is that the per-round figure is constant, so you can recompute the table yourself: 600 × the round number, every time.)

What the three columns actually say

The most irritating part of that table is the busy column. Zero for thirty rounds straight — "I was not blocked." On the same lines, the third column is also zero for thirty rounds: not a single page was moved.

This is where I jumped to a wrong conclusion on the first pass — I wrote down "PASSIVE never sets this flag." The source code turned out to be more interesting. In src/wal.c, sqlite3WalCheckpoint tries to take an exclusive checkpoint lock for every mode except NOOP, PASSIVE included:

if( eMode!=SQLITE_CHECKPOINT_NOOP ){
  rc = walLockExclusive(pWal, WAL_CKPT_LOCK, 1);

The comment just above it confirms the consequence: if another process is running a checkpoint at the same time, the lock cannot be obtained and SQLITE_BUSY is returned. And vdbe.c derives the first column straight from that return code (aRes[0] = 1). So PASSIVE can indeed set this flag — but only when another checkpointer is in the way. A blocking reader never reaches this column, because PASSIVE was not waiting for the reader in the first place.

The second and third columns also turned out to mean something different from what I assumed. The wal_checkpoint_v2 documentation and the source agree:

if( pnLog )  *pnLog  = (int)pWal->hdr.mxFrame;
if( pnCkpt ) *pnCkpt = (int)(walCkptInfo(pWal)->nBackfill);

The second column is not "frames this call wrote" but the total number of frames in the WAL. The third is not "frames this call moved" but the cumulative count of frames backfilled — the documentation spells it out in parentheses: including any that were already checkpointed before the call. That is why a second PASSIVE call shows you the same two numbers; the counter carries a running total, so the second reading reports the same total.

So the correct reading is this: the gap between the two numbers is how many frames have not yet made it home. A single instantaneous measurement proves nothing — in normal operation the gap is above zero after every commit, and the next checkpoint closes it. If you are building an alarm, watch for the gap persisting: if it refuses to close for minutes on end and keeps growing, somebody forgot to say COMMIT.

There is a measurable way to find the culprit

SQLite has no pg_stat_activity; there is no view you can query for which connection is holding a read transaction. But there is an indirect detector, and it works. Unlike PASSIVE, TRUNCATE mode waits for readers; with busy_timeout at zero it returns busy=1 immediately instead of waiting. Here are three moments in a row on the same database:

moment	PASSIVE	TRUNCATE
no reader	busy=0 wal=2936 moved=2936	busy=0 wal=0 moved=0
reader present	busy=0 wal=600 moved=0	busy=1 wal=600 moved=0
reader gone	busy=0 wal=600 moved=600	busy=0 wal=0 moved=0

The middle row is exactly what I wanted: PASSIVE says nothing while TRUNCATE says "a reader is holding me." It isn't free, though — TRUNCATE also takes the writer lock, so it steps into the middle of your write traffic, and on success it zeroes the file. Don't call it every minute as a health check; save it for answering "the WAL is growing, is a reader the reason?"

NOOP, which is completely harmless, only returns the three columns and touches no frames. Hang your monitoring on that one, and reach for TRUNCATE when you need a diagnosis.

The file does not shrink by itself once the reader leaves

I assumed that relieving the starvation would fix the file. A third experiment settles it: inside a single process, never closing the connection, wait for the reader to go away and then call the checkpoint.

start          db=57.0 MB  wal=0 bytes
reader open    db=57.0 MB  wal=58.9 MB  (15000 frames)
  PASSIVE: busy=0 wal=15000 moved=0
[reader] transaction closed
reader gone, CONNECTION STILL OPEN:
  PASSIVE: busy=0 wal=15000 moved=15000   -> wal file 58.9 MB
  PASSIVE: busy=0 wal=15000 moved=15000   -> wal file 58.9 MB
  after a fresh commit, wal file 58.9 MB (15000 frames)
  TRUNCATE: busy=0 wal=0 moved=0  -> wal file 0 bytes

A note on units: the script printing this output divides bytes by 1,048,576, so it is really writing MiB. The 59.8 MB in the prose and the 57.0 MB here are the same file — 59,805,696 bytes. Frame counts and byte figures are raw everywhere; only the lines labelled "MB" are binary.

The moment the reader left, PASSIVE moved all 15,000 frames back and the third column caught up with the second. The data is safe, the main file is current. But the file is still 58.9 MB. A second PASSIVE changed nothing — the cumulative counter simply repeats the same total. I committed again: still 58.9 MB, because SQLite is overwriting it from the beginning.

The only thing that shrank the file was TRUNCATE: zero bytes.

There is one more trap here, and I fell into it on my first attempt. When I closed the writer process, the 74 MB WAL file vanished from disk; I was surprised and noted down "so it does clean up after itself." The documentation makes the reason obvious: when the last connection closes it performs one final checkpoint and then deletes the WAL and its shared-memory file. The documentation says "usually", because the file stays behind if the process dies without shutting the connection down cleanly, or if persistent-WAL mode is on. The practical consequence: if you test with a short-lived CLI process, you will never see the problem. A production server process, meanwhile, stays up for months. If you don't build the experiment around a connection that lives as long as the process, what you're measuring is not your scenario.

Exactly where journal_size_limit helps

The only fence the documentation mentions in this context is PRAGMA journal_size_limit. The default is -1, meaning no limit. Setting it to zero says "always trim the WAL file to its minimum size." When it trims is the interesting part.

I repeated the same starvation scenario with journal_size_limit = 0:

journal_size_limit = 0
with reader open, wal = 51.9 MB                        <- the limit does NOT prevent starvation
reader gone, PASSIVE: wal=13200 moved=13200 -> 51.9 MB <- the checkpoint does not trim either
after the next commit, wal = 2472032 bytes             <- the limit trims HERE

Three different moments, three different outcomes. The limit does nothing while starvation lasts: the file climbs to 51.9 MB. It doesn't trim at checkpoint time either — and that is precisely where the parenthetical I italicised earlier turns out to be loose. The trim happens at the next WAL reset after that, which is to say on the next commit. The source says so plainly; the condition in wal.c sits on the commit path:

if( isCommit && pWal->truncateOnCommit && pWal->mxWalSize>=0 ){
  i64 sz = pWal->mxWalSize;
  if( walFrameOffset(iFrame+nExtra+1, szPage)>pWal->mxWalSize ){
    sz = walFrameOffset(iFrame+nExtra+1, szPage);
  }

That second if also explains the oddity in my measurement: the limit was zero, yet the file dropped to 2,472,032 bytes rather than to nothing. SQLite cannot go below what the current commit's own frames occupy, and 32 + 600 × 4120 is exactly 2,472,032. The comment in the code adds that the trim is only attempted when the first transaction in the WAL completes.

Two more practical notes. This pragma is a per-connection setting (sqlite3WalLimit writes straight into that connection's Wal struct), so it has to be set again on every new connection, and it must come after journal_mode = WAL. And journal_size_limit does not install a ceiling; it works as a cleaner that gives the disk back once the pressure passes. It will not protect you while starvation lasts.

The backup that goes quietly stale

Everything so far has been about disk space. The place you actually pay for this is backups, and measuring this part made me sit up straight.

If you copy only the .db file while the WAL still holds frames that haven't been moved back, none of those not-yet-written changes end up in the copy. I set the experiment up like this: with a reader open, I updated 600 rows with a recognisable marker, read the WAL state, then took two separate backups — one a plain file copy, one via SQLite's own backup API.

WAL state: wal=2937 moved=0 | db=59805696 wal=12100472
marked rows in the source        : 600
in the plain .db copy            : 0
in the backup API copy           : 600
integrity_check (plain .db copy) : ok

That last line is the worst news in this experiment. The plain copy lost 600 rows, and PRAGMA integrity_check calls it ok. It is an internally consistent database; it simply never saw the last 2,937 frames of changes. You "verified" your backup, you got a green light, and what you hold is a database that has been quietly rewound.

I owe a correction to one of my own earlier posts here. In my comparison of SQLite and PostgreSQL in production I wrote that copying a live database with cp risks producing a corrupt backup. The measurement says something worse: the backup is stale, and a corrupt one would at least have been caught by integrity_check.

A copy taken with VACUUM INTO under the same conditions carried all 600 rows too. So there are two working routes:

VACUUM INTO '/path/backup.db';

sqlite3 /path/source.db ".backup /path/backup.db"

These are not the same mechanism: the first rebuilds the database into the destination, the second is a shell dot-command that calls SQLite's backup API. Each has its own failure mode, and each reports failure openly. VACUUM INTO can fail if an unfinalized statement is holding a read transaction open. The backup API restarts its work whenever the source is written to, and under heavy enough write traffic it may never run to completion. So "let the database do it" isn't enough on its own; you have to check the return value too.

If you insist on copying files, .db is not enough; you need the -wal and -shm files as well, taken at a consistent instant. My advice is to not attempt that route at all: you cannot grab three files atomically with cp, and the failure is silent.

One warning. Seeing no -wal file next to a database does not mean it isn't in WAL mode. WAL mode is persistent, and with no connection open the -wal file has already been deleted — so a closed WAL database looks like an innocent single file sitting in a directory. Don't infer the journal mode from a file listing; ask PRAGMA journal_mode.

What this blog's own setup does

Looking at my own infrastructure through this lens, I found two different decisions, both deliberate.

On the content.db side the problem has been eliminated at the source. When scripts/migrate-content.mjs finishes writing, it runs two consecutive lines:

db.pragma('wal_checkpoint(TRUNCATE)');
db.pragma('journal_mode = DELETE');

So the file that ships is a single artifact: no -wal, no -shm beside it. At runtime, src/lib/content-db.ts opens it with readonly: true, and the comment next to it sums up the situation: because it is read-only and was switched to a DELETE journal during migrate, no extra pragma is needed. On top of that, the container mounts /app read-only — even if you wanted to create a WAL file, you have no write permission on the directory. Three layers, all pointing the same way. (That is also the 59,981,824-byte file: content.db sitting in the deploy directory on the server, with no log file beside it.)

The blog.db side is different. There, src/lib/sqlite-adapter.ts sets the classic trio:

sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');
sqlite.pragma('busy_timeout = 5000');

No journal_size_limit — it appears nowhere in the repository, and live it reads -1. A long-lived server process, a constantly-written rate-limit table, an unbounded WAL. The 168 frames at the top of this article are therefore reassuring but not sufficient evidence on their own: an eighteen-minute window says very little about a monthly high-water mark.

What actually reassures me is the shape of the code. The access layer runs every statement on its own; there is not a single .iterate() call in the application code. With no long-lived cursor, there is no reader to trigger starvation. So the protection here comes from the pattern in the code rather than from a pragma.

My to-do list reads like this: set journal_size_limit for blog.db to an explicit value, immediately after the journal_mode line (not zero — trimming at every WAL reset means pointless I/O), and start monitoring the gap between the two counters periodically with wal_checkpoint(NOOP). There is also one knob I considered and dropped: lowering wal_autocheckpoint below 1,000 would achieve nothing in this scenario, because during starvation every checkpoint moves zero frames, and moving zero more often helps no one.

A decision framework for your own setup

Ask these four questions in order. If you can answer "no" to all of them, you can forget the WAL entirely — the documentation itself says that in the vast majority of cases applications need not worry about this file at all.

1. Is the process long-lived? Short-lived CLI processes usually delete the WAL as they close. The problem accumulates on servers that stay up for months.
2. Is there a read transaction that stays open for a long time? A transaction opened with BEGIN and closed late, a pagination cursor, a loop streaming rows one at a time, a reporting query. One is enough.
3. Did you disable the automatic checkpoint? If you wrote wal_autocheckpoint = 0, nothing is left to keep the WAL from growing. This is one of the three causes the documentation lists explicitly.
4. Do you do very large writes in a single transaction? A checkpoint cannot happen before a large transaction completes; the WAL has to carry the whole thing.

Hang your monitoring not on file size but on the gap between the second and third columns of wal_checkpoint(NOOP); don't look at a single reading, look at whether that gap persists. One final trap: if the database is not in WAL mode, or the call errors, both columns come back as -1 and the gap computes to zero. A naive monitor will therefore show green for a database that has no WAL at all. Check the columns for -1 separately.

What's left

The lesson I took from this small investigation is bigger than checkpoints. Several independent design decisions — automatic checkpoints being PASSIVE, PASSIVE not surfacing reader blocking in its flag, the counters being cumulative, the file not shrinking on its own — are each perfectly reasonable in isolation and each documented. But put together, they let a completely jammed mechanism look completely healthy.

This is how black boxes spend their worst nights: nobody is lying, it's just that nobody is asking the right question. For years I was content to run ls -la and look at the WAL file's size — nearly the same thing as not looking at all. The right question isn't how big the file is, but how many frames inside it are still waiting to come home.

Official Sources

• SQLite — Write-Ahead Logging: checkpointing, starvation and oversized WAL files
• SQLite — PRAGMA reference: wal_checkpoint, wal_autocheckpoint, journal_size_limit
• SQLite — sqlite3_wal_checkpoint_v2: mode semantics and return values
• SQLite — VACUUM INTO and its documented failure condition
• SQLite source — WAL and checkpoint implementation (src/wal.c)
• PostgreSQL — what the WAL is for, and how it differs from SQLite's