¶ 
SQLite Configuration
SQLite is an embedded database library, not a standalone daemon. Configuration is mostly done through database file location, file permissions, and runtime PRAGMA settings. These configurations are optimized for Linux DevOps environments with SQLite 3.51.2+.
¶ 
File Locations and Layout
Use a dedicated application data path and avoid writing databases to temporary directories.
¶ Recommended Paths
/var/lib/<app>/data/app.db # Main database file
/var/lib/<app>/data/app.db-wal # Write-Ahead Log file
/var/lib/<app>/data/app.db-shm # Shared Memory file
¶ Permission Baseline
# Create data directory with proper ownership
sudo install -d -o <appuser> -g <appgroup> -m 750 /var/lib/<app>/data
# Set proper ownership and permissions for database files
sudo chown <appuser>:<appgroup> /var/lib/<app>/data/app.db*
sudo chmod 640 /var/lib/<app>/data/app.db*
¶ 
Production PRAGMA Baseline
Apply these once after opening the database, then persist where possible. These settings are optimized for SQLite 3.51.2+.
-- WAL mode for better concurrency and crash recovery
PRAGMA journal_mode = WAL;
-- Balance durability and performance
PRAGMA synchronous = NORMAL;
-- Enforce relational integrity
PRAGMA foreign_keys = ON;
-- Handle lock contention gracefully
PRAGMA busy_timeout = 30000; -- 30 seconds
-- Optimize temporary storage
PRAGMA temp_store = MEMORY;
-- Configure cache size (negative value = KB)
PRAGMA cache_size = -65536; -- 64MB cache
-- Enable memory mapping for better performance (if supported)
PRAGMA mmap_size = 1073741824; -- 1GB mmap if supported
-- Optimize page size for modern filesystems
PRAGMA page_size = 4096; -- 4KB pages (default, but explicit)
-- Optimize auto-vacuum behavior
PRAGMA auto_vacuum = INCREMENTAL; -- More efficient than FULL
-- Enable incremental vacuum
PRAGMA incremental_vacuum(100); -- Vacuum 100 pages at a time
¶ 
Important Settings Explained
journal_mode=WAL: Improves concurrency for read-heavy apps and safer crash recovery. Essential for multi-process access.synchronous=NORMAL: Good durability/performance balance for most applications.foreign_keys=ON: Enforces relational integrity (off by default in some clients).busy_timeout=30000: Waits up to 30 seconds for locks to clear before returning an error.cache_size=-65536: Sets cache to 64MB, reducing disk I/O for repeated queries.mmap_size=1073741824: Enables memory mapping up to 1GB for improved read performance on supported filesystems.auto_vacuum=INCREMENTAL: More efficient than FULL, allows incremental cleanup.page_size=4096: Standard page size optimized for most filesystems.
¶ 
Performance Tuning for Heavy Loads
For applications with higher write loads, consider these additional settings:
-- Reduce synchronous requirements (less durable but faster)
PRAGMA synchronous = NORMAL; -- Default, good balance
-- Optimize WAL checkpoints
PRAGMA wal_autocheckpoint = 1000; -- Checkpoint every 1000 pages
PRAGMA wal_checkpoint(TRUNCATE); -- Truncate WAL after checkpoint
-- Increase cache for write-heavy workloads
PRAGMA cache_size = -131072; -- 128MB cache for write-heavy apps
¶ 
Connection and Application Notes
- Keep write transactions short to minimize lock contention.
- Use one shared DB file per logical workload boundary.
- Avoid SQLite for heavy concurrent write workloads (>100 writes/sec).
- Set PRAGMAs explicitly at application startup to avoid driver defaults.
- Consider using connection pooling for applications with frequent database access.
- Monitor WAL file sizes to ensure proper checkpointing.
¶ 
Backup and Integrity
¶ Online-safe backup (preferred)
# Basic backup
sqlite3 /var/lib/<app>/data/app.db ".backup '/var/backups/<app>/app-$(date +%F).db'"
# Backup with compression
sqlite3 /var/lib/<app>/data/app.db ".backup '| gzip > /var/backups/<app>/app-$(date +%F).db.gz'"
# Automated backup script
#!/bin/bash
DB_PATH="/var/lib/<app>/data/app.db"
BACKUP_DIR="/var/backups/<app>"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
sqlite3 "$DB_PATH" ".backup '$BACKUP_DIR/app_$DATE.db'"
find "$BACKUP_DIR" -name "app_*.db" -mtime +30 -delete # Keep 30 days
¶ Integrity check
sqlite3 /var/lib/<app>/data/app.db "PRAGMA integrity_check;"
¶ Quick metadata checks
sqlite3 /var/lib/<app>/data/app.db "PRAGMA journal_mode;"
sqlite3 /var/lib/<app>/data/app.db "PRAGMA foreign_keys;"
sqlite3 /var/lib/<app>/data/app.db "PRAGMA synchronous;"
sqlite3 /var/lib/<app>/data/app.db "PRAGMA mmap_size;"
¶ 
Monitoring and Maintenance
¶ Size monitoring script
#!/bin/bash
DB_PATH="/var/lib/<app>/data/app.db"
WAL_PATH="/var/lib/<app>/data/app.db-wal"
SHM_PATH="/var/lib/<app>/data/app.db-shm"
echo "Database size: $(du -h "$DB_PATH" | cut -f1)"
[[ -f "$WAL_PATH" ]] && echo "WAL size: $(du -h "$WAL_PATH" | cut -f1)"
[[ -f "$SHM_PATH" ]] && echo "SHM size: $(du -h "$SHM_PATH" | cut -f1)"
# Check for uncommitted transactions
sqlite3 "$DB_PATH" "SELECT count(*) FROM pragma_lock_status();"
¶ Automated maintenance
# Add to crontab for weekly maintenance
0 2 * * 0 /usr/bin/sqlite3 /var/lib/<app>/data/app.db "PRAGMA wal_checkpoint(TRUNCATE); VACUUM;"
¶ 
Security and Operations Notes
- Restrict DB file access to the service account only.
- Do not place DB files on shared/public paths.
- Keep periodic backups and test restore procedures regularly.
- Monitor database size growth and WAL checkpoint behavior.
- If write contention increases significantly, plan migration to PostgreSQL or MariaDB.
- Consider using LUKS encryption for database files on sensitive systems.
- Regularly update SQLite to address security vulnerabilities.