SQLite ships in every iOS and Android device, yet most apps treat it like a slow, synchronous bottleneck. Default configuration yields ~50 writes/second—acceptable for user taps, catastrophic for real-time sensor streams, offline-first sync queues, or bulk imports. With three architectural changes, you can push 2,000+ writes/second on mid-range hardware while keeping the UI at 60fps.
This matters for apps that buffer data locally before cloud sync: speech therapy sessions recording 100 audio features/second, PPG heart rate monitors sampling at 50Hz, or LLM chat apps persisting token streams for offline replay. When building KidzCare—a speech therapy app processing real-time phoneme analysis—naive SQLite writes caused 200ms UI freezes every few seconds. The fix required rethinking how mobile apps should interact with embedded databases.
The Baseline Problem
Out-of-the-box SQLite on mobile defaults to DELETE journal mode and autocommit. Every INSERT triggers a full fsync() to guarantee durability. On iOS, fsync() takes 15-25ms; on Android with F2FS, 10-18ms. At 20ms per write, you're capped at 50 ops/sec—and each one blocks the calling thread.
Developers typically wrap database calls in background queues (DispatchQueue on iOS, Isolate in Flutter). This prevents UI jank but doesn't improve throughput. A 10-second sensor recording generating 500 samples still takes 10 seconds to write, creating a growing backlog. If the user backgrounds the app mid-write, iOS may suspend execution and truncate the queue.
Write-Ahead Logging
Switching to WAL mode is the first optimization. Instead of locking the entire database per transaction, WAL appends changes to a separate log file and checkpoints periodically. Readers see a consistent snapshot; writers don't block readers.
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;Set synchronous to NORMAL instead of FULL. In WAL mode, this guarantees durability at checkpoint boundaries (every ~1000 pages or 4MB) rather than per transaction. For mobile apps, losing the last 4MB of writes in a catastrophic crash is acceptable—users expect app state to lag slightly behind reality. Cloud sync provides the real durability guarantee.
WAL mode alone yields 3-5× throughput improvement, but you're still issuing one transaction per write. The real win comes from batching.
Explicit Transaction Batching
Wrap multiple inserts in a single BEGIN IMMEDIATE / COMMIT block. SQLite then amortizes the fsync cost across all writes in the batch.
BEGIN IMMEDIATE;
INSERT INTO samples (timestamp, value) VALUES (?, ?);
-- repeat 100 times
COMMIT;Batch size is a latency-throughput tradeoff. At 100 inserts/batch, throughput jumps to 2,000-5,000 writes/sec on iPhone 12 / Pixel 6 class hardware. Batch latency is ~20-50ms—acceptable for background queues. Push to 500/batch and you hit 8,000+ writes/sec, but latency climbs to 100-150ms. For real-time sensor streams, 100-200 is the sweet spot.
Implementation requires a buffering layer. In Flutter, use a StreamController that accumulates writes for 50ms or until 100 samples arrive, whichever comes first:
final buffer = [];
Timer.periodic(Duration(milliseconds: 50), (_) {
if (buffer.isEmpty) return;
final batch = List.from(buffer);
buffer.clear();
_writeBatch(batch);
});On iOS with Swift, use a serial DispatchQueue with a DispatchSemaphore to coalesce writes. The key is never blocking the main thread—sensor callbacks enqueue data and return immediately.
Prepared Statements
Parsing SQL strings is expensive. Prepare once, bind parameters per execution:
let stmt = try db.prepare("INSERT INTO samples (ts, val) VALUES (?, ?)")
for sample in batch {
try stmt.run(sample.timestamp, sample.value)
}In Flutter's sqflite package, use rawInsert with the same SQL string repeatedly—the plugin caches prepared statements internally. Avoid string interpolation ("INSERT ... VALUES ($timestamp, $value)"); every unique string forces a reparse.
Prepared statements add another 20-30% throughput on top of batching. Combined with WAL, you're now at 40× baseline performance.
Memory Pressure and Checkpointing
WAL files grow unbounded until checkpointed. On mobile, this can trigger memory warnings. Set an explicit checkpoint interval:
PRAGMA wal_autocheckpoint = 1000;This checkpoints every 1000 pages (~4MB). For high-throughput apps, consider manual checkpoints during idle periods:
PRAGMA wal_checkpoint(TRUNCATE);Run this when the app backgrounds or after completing a large import. TRUNCATE mode shrinks the WAL file to zero, reclaiming disk space. On iOS, hook into UIApplication.didEnterBackgroundNotification; on Android, override onPause().
Read Performance
WAL mode allows concurrent reads and writes, but readers see the database state at transaction start. For apps displaying live sensor data, query inside the write transaction to guarantee consistency:
BEGIN IMMEDIATE;
INSERT INTO samples ...;
SELECT AVG(value) FROM samples WHERE timestamp > ?;
COMMIT;Alternatively, use PRAGMA read_uncommitted = 1 to see in-progress writes—acceptable for dashboards where sub-second staleness is fine.
Schema Design for Write Throughput
Indexes slow inserts. For append-heavy tables (sensor logs, chat messages), defer index creation until query time or batch-create indexes during sync:
CREATE TABLE samples (
id INTEGER PRIMARY KEY,
timestamp INTEGER NOT NULL,
value REAL NOT NULL
) WITHOUT ROWID;WITHOUT ROWID saves 20% space for tables with a single-column primary key. Add indexes on timestamp only after bulk import completes.
For multi-column writes, use a single wide table rather than normalized relations. Joins are expensive on mobile CPUs; denormalization trades disk space for write speed.
Platform-Specific Tuning
On iOS, SQLite uses F_FULLFSYNC by default, which flushes the device's internal write cache—slower than fsync(). In WAL + NORMAL mode, this is overkill:
PRAGMA fullfsync = OFF;On Android, F2FS filesystems already optimize small writes. No additional tuning needed, but verify your target devices use F2FS (most flagships since 2018) rather than ext4.
Testing Throughput
Measure with realistic workloads. Insert 10,000 rows in batches of 100, timing each commit:
let start = Date()
for batch in samples.chunked(by: 100) {
try db.transaction {
for sample in batch {
try stmt.run(sample.ts, sample.val)
}
}
}
let elapsed = Date().timeIntervalSince(start)
print("\(samples.count / elapsed) writes/sec")Run on real devices, not simulators. iPhone 11 and newer hit 3,000-5,000 writes/sec with this configuration; mid-range Android (Snapdragon 7-series) achieves 2,000-3,000.
When Not to Batch
User-initiated writes (saving a form, updating a profile) should commit immediately for perceived responsiveness. Batching is for machine-generated data: sensor streams, sync operations, bulk imports. Mixing the two requires separate write paths—one synchronous for UI, one batched for background.
For apps syncing to cloud databases (Firebase, Appwrite), SQLite becomes a staging area. Batch locally, then upload in bulk. This amortizes network round-trips and reduces mobile radio active time—critical for battery life.
Production Considerations
Monitor WAL file size in production. If users report storage warnings, your checkpoint interval is too large. Add telemetry:
let walSize = try FileManager.default
.attributesOfItem(atPath: dbPath + "-wal")[.size] as! UInt64
if walSize > 50_000_000 { // 50MB
try db.execute("PRAGMA wal_checkpoint(TRUNCATE)")
}In apps processing sensitive data (health records, financial transactions), re-enable synchronous = FULL for audit logs while keeping NORMAL for non-critical tables. Use separate database files if regulatory compliance requires guaranteed durability for specific data types.
SQLite's embedded architecture makes it uniquely suited for mobile offline-first patterns. With WAL mode, transaction batching, and prepared statements, it handles real-time data ingestion at scale—no server required. The techniques here underpin apps processing millions of sensor samples daily, all while keeping the UI responsive and battery drain minimal.