SQLite Persistence: WAL, Write Buffering, 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.
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 ~100k jobs/sec, up to 10ms loss, batched writes. Use for: emails, notifications, analytics
Durable, opt-in per job ~10k jobs/sec, no data loss, immediate write. Use for: payments, critical events, financial transactions
Usage: queue.add('job', data, { durable: true })
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
(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 INTEGER, rate_limit INTEGER, concurrency_limit INTEGER; persists pause/limits across restarts
Crash Recovery Flow
Section titled “Crash Recovery Flow”Startup recoverycrash recovery on boot, batches of 10,000 rows
1. Recover active jobs each counts as one stall: stallCount++ and attempts++; below maxStalls it is requeued with backoff, at maxStalls it moves to the DLQ. 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, concurrency limit per queue
↓
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. Check database file exists
2. Generate key backups/bunqueue-{timestamp}.db
3. Read file as ArrayBuffer
4. Calculate SHA256 checksum
5. Upload to S3 application/x-sqlite3
6. Upload metadata sidecar {key}.meta.json
7. Cleanup old backups keep N most recent
restore
1. Download backup file from S3
2. Load metadata sidecar
3. Verify SHA256 checksum
4. Write to database path
5. Restart server to load
Supports AWS S3, Cloudflare R2, MinIO, DigitalOcean.
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