SQLite Persistence: WAL, Write Buffer, S3 Backups
architecture · persistence
WAL mode and write buffers.
bunqueue uses SQLite with WAL mode for persistence, optimized for high-throughput job processing: batched writes, crash recovery, and S3 backups.
This page is intentionally specific to the memory/SQLite engine. Standalone
servers configured with PostgreSQL bypass SqliteStorage and WriteBuffer;
they use Bun’s native SQL pool and database-authoritative transactions. See
Storage backends for that architecture and its backup,
durability, and failover boundaries.
SQLite Configuration
Section titled “SQLite Configuration”SQLite pragmasset at startup
journal_mode = WAL Write-Ahead Loggingsynchronous = NORMAL balanced safety/performancecache_size = -64000 64MB in-memory cachetemp_store = MEMORY in-memory temp tablesmmap_size = 268435456 256MB memory-mapped I/Opage_size = 4096 4KB pagesbusy_timeout = 5000 wait up to 5s on lock contentionWrite Buffer Architecture
Section titled “Write Buffer Architecture”Write bufferjob arrives
Add to buffer max 100 jobs
↓
Buffer full 100 jobs
Timer fires 10ms
↓
Batch insert INSERT INTO jobs VALUES (...), (...), (...)
Prepared statement cache (1-100 rows), single transaction, 50-100x faster than individual inserts.
Buffered vs Durable Mode
Section titled “Buffered vs Durable Mode”Write modesper-job choice
Buffered, default up to 10 ms loss; 186,384 jobs/s median for the published public on-disk Embedded addBulk workload
Durable, opt-in per job immediate write; 60,835 ops/s median for the published sequential Embedded add workload
Usage: queue.add('job', data, { durable: true })
The two medians above describe different workloads. They are capacity evidence, not a direct buffered-versus-durable ratio; see Engineering Benchmarks for distributions and TCP results.
Database Schema
Section titled “Database Schema”TablesSQLite schema
jobs, 30 columns
id TEXT PRIMARY KEY, UUIDv7
queue TEXT
data BLOB, MessagePack
priority INTEGER
state TEXT
run_at INTEGER
attempts INTEGER
... 23 more fields
indexes
(queue, state) PULL queries
(queue, created_at, id) stable unfiltered pagination
(queue, state, created_at, id) stable state pagination
(run_at) delayed job scheduler
(queue, unique_key) deduplication
(custom_id) idempotency
(parent_id) parent job lookup
(state, started_at) stall detection
(group_id) group operations
(queue, state, priority, run_at) priority pull
(completed_at DESC) completed-job recovery ordering
job_results job_id TEXT PRIMARY KEY, result BLOB MessagePack, completed_at INTEGER
dlq id INTEGER PRIMARY KEY AUTOINCREMENT, job_id TEXT, queue TEXT, entry BLOB full DlqEntry MessagePack, entered_at INTEGER
cron_jobs name TEXT PRIMARY KEY, queue TEXT, data BLOB, schedule TEXT, repeat_every INTEGER, priority INTEGER, next_run INTEGER, executions INTEGER, max_limit INTEGER, timezone TEXT, unique_key TEXT, dedup BLOB, skip_missed_on_restart INTEGER, skip_if_no_worker INTEGER, prevent_overlap INTEGER, job_options BLOB
queue_state name TEXT PRIMARY KEY, paused/rate/concurrency fields plus stall_enabled, stall_interval, max_stalls, stall_grace_period; persists queue controls and custom stall policy across restarts
Crash Recovery Flow
Section titled “Crash Recovery Flow”Startup recoverycrash recovery on boot, batches of 10,000 rows
1. Recover active jobs restore custom stall policy first, then read repeated 10k batches from offset zero; each interrupted job persists stallCount++ and attempts++. Below both maxStalls and maxAttempts it is requeued with backoff; reaching either bound persists exactly one DLQ entry. Cron-spawned preventOverlap jobs are dropped, the scheduler recreates them
↓
2. Load pending jobs state waiting/delayed: jobs with unmet dependencies go to waitingDeps, the rest to their shard queue; jobIndex, customId and uniqueKey mappings restored
↓
3. Load DLQ entries restore to in-memory DLQ shards, populate jobIndex
↓
4. Restore queue state paused flag, rate limit and concurrency limit per queue; the stall policy snapshot was already applied before active recovery
↓
5. Load completed jobs up to the 50k in-memory cap, for clean() and stats
↓
6. Load cron jobs populate cron scheduler heap; past next_run is recalculated forward when skipMissedOnRestart is set
S3 Backup Flow
Section titled “S3 Backup Flow”S3 backupscheduled: every 6 hours, configurable
backup
1. Flush pending WriteBuffer abort if any write remains
2. SQLite VACUUM INTO WAL-safe standalone snapshot
3. PRAGMA integrity_check
4. Gzip + SHA256 uncompressed bytes
5. Upload metadata sidecar {unique-key}.meta.json
6. Publish gzip payload commit point
7. Cleanup old backups keep N most recent
restore
1. Download backup file from S3
2. Load metadata sidecar
3. Decompress, verify size + SHA256
4. Validate temp SQLite candidate
5. Quarantine WAL/SHM, atomically rename
Stop the server before restore. Current compressed backups require metadata; legacy no-metadata restores are uncompressed only.
Flush on Failure
Section titled “Flush on Failure”Error recoveryflush() fails
Re-buffer failed jobs double-buffered swap: the failed flush buffer is merged back, nothing is dropped
↓
Retry with exponential backoff 100ms initial, 30s max, up to 10 retries; regular flushing pauses during backoff
↓
After max retries critical-error callback fires with the affected jobs, so they can be surfaced instead of silently lost
on shutdown
Final flush of remaining buffer, wait for completion before exit
Serialization
Section titled “Serialization”MessagePackwhy MessagePack instead of JSON?
2-3x faster encoding/decoding
Smaller payload size
Binary data support
used for
Job data BLOB
DLQ entry BLOB
TCP protocol payloads
Job results storage