Distinguishing WAL checkpoint wait from connection pool exhaustion in SQLite

Short answer

SQLite does not expose a single status flag that says “this connection is blocked on a WAL lock” versus “this caller is waiting for a pool slot.” You must separate the two phases in your own instrumentation. If a request times out while borrowing a connection from the pool, the pool is exhausted. If checkout succeeds but sqlite3_prepare, sqlite3_step, or COMMIT is slow or returns SQLITE_BUSY, the wait is inside SQLite or the VFS layer. The likely explanation for apparent exhaustion is a small pool combined with long write transactions or delayed autocheckpoints; SQLite itself rarely runs out of connections.

Correcting the WAL premise

In WAL mode, a reader does not normally wait for a checkpoint to finish. Readers see the most recent committed snapshot in the write-ahead log; they do not hold database-file read locks until checkpoint completion. A long-running read transaction can pin old WAL frames, prevent checkpoint reset/truncation, and grow the -wal file, but it does not make other readers wait for checkpoint completion. Readers can be delayed by WAL shared-memory (wal-index) contention, by WAL recovery after a crash, or by explicit FULL/RESTART/TRUNCATE checkpoints that intentionally wait for readers. PASSIVE checkpoints copy what they can without blocking readers.

PRAGMAs that change checkpoint timing

PRAGMA busy_timeout only controls how long SQLite retries on SQLITE_BUSY; it does not change checkpoint frequency or the I/O work a checkpoint performs. The settings that affect checkpoint timing independently are:

• PRAGMA wal_autocheckpoint=N — page threshold that triggers an automatic checkpoint after a commit; 0 disables automatic checkpoints.
• PRAGMA wal_checkpoint(PASSIVE|FULL|RESTART|TRUNCATE) — explicit checkpoint mode; only PASSIVE is non-blocking for readers.
• PRAGMA journal_size_limit — caps WAL file size after checkpointing.
• PRAGMA synchronous — changes fsync behavior during commits and checkpoints.
• PRAGMA page_size and PRAGMA cache_size — affect checkpoint copy volume and memory pressure.

Practical verification

Log pool checkout duration separately from SQL execution duration. A useful baseline on a typical SQLite 3.x build with the default VFS:

SELECT sqlite_version();
PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA wal_autocheckpoint;
PRAGMA journal_size_limit;
PRAGMA mmap_size;
PRAGMA compile_options;

Then run a passive checkpoint and record its return columns while watching the -wal file size:

PRAGMA wal_checkpoint(PASSIVE);

If the pool is the bottleneck, checkout time dominates and SQLite return codes are SQLITE_OK or SQLITE_DONE. If SQLite is the bottleneck, checkout is fast but step/commit latency grows, and you may see SQLITE_BUSY when busy_timeout is short or disabled.

One diagnostic that changes the advice

Does the timeout occur while acquiring the pooled connection, or while executing/committing SQL? If you do not already log that split, add it before changing pool size or checkpoint settings. Without it, increasing the pool can hide a checkpoint or long-transaction problem, and tuning checkpoints can mask a genuinely undersized pool.