If you only read this
- Turn on WAL, keep transactions short and put the outbox in the same transaction as the data.
- Page with keyset (seek) queries on an indexed key. OFFSET gets slower with every page.
- Migrations must work from any old version, never touch the outbox destructively, and be tested on real old files.
01Start with the access pattern, not the entity
Most slow mobile databases are not slow because SQLite is slow. They are slow because the schema mirrors the server's tables instead of the screens. Write down the five queries your screens run most, then design tables and indexes for them.
A field-service list screen asks one question: jobs assigned to me, not completed, sorted by due date, newest edits first, 30 at a time. That query needs a composite index on (assignee_id, status, due_at, id). If the index does not match the query, you scan a million rows to show thirty.
A schema built for the list screen
1CREATE TABLE job (2 id TEXT PRIMARY KEY, -- UUIDv7, generated on the device3 assignee_id TEXT NOT NULL,4 status INTEGER NOT NULL, -- small ints index better than strings5 title TEXT NOT NULL,6 due_at INTEGER NOT NULL, -- unix ms, UTC7 updated_at INTEGER NOT NULL,8 server_seq INTEGER, -- NULL until the server has acknowledged it9 deleted INTEGER NOT NULL DEFAULT 0 -- tombstone, so sync can delete10) WITHOUT ROWID;11 12CREATE INDEX job_list ON job (assignee_id, status, due_at, id) WHERE deleted = 0;13 14CREATE TABLE outbox (15 seq INTEGER PRIMARY KEY AUTOINCREMENT,16 op_id TEXT NOT NULL UNIQUE, -- idempotency key17 entity TEXT NOT NULL,18 entity_id TEXT NOT NULL,19 payload BLOB NOT NULL,20 attempts INTEGER NOT NULL DEFAULT 0,21 created_at INTEGER NOT NULL22);23 24CREATE TABLE sync_state (key TEXT PRIMARY KEY, value TEXT NOT NULL); -- cursor lives here02WAL, pragmas and short transactions
Write-ahead logging lets readers keep reading while one writer appends to the log. That is exactly what a sync engine writing in the background and a UI reading on the main thread need. Android's Room and most iOS wrappers such as GRDB enable it for you, but you should know what it does.
The rules that matter more than any setting: one writer at a time, transactions measured in milliseconds, and never hold a transaction open across a network call. Commit a sync batch of 500 rows in chunks, not in one two-second transaction that blocks the user's tap.
| Setting | Typical value | Why |
|---|---|---|
| journal_mode | WAL | Readers do not block the writer and vice versa. |
| synchronous | NORMAL with WAL | Durable enough against app crashes; a power cut can lose the last commits but never corrupt the file. |
| busy_timeout | 3000–5000 ms | Wait for the writer instead of failing with SQLITE_BUSY. |
| foreign_keys | ON | Off by default per connection. Turn it on every time you open. |
| wal_autocheckpoint | 1000 pages (default) | Large bulk imports may checkpoint manually so the WAL file does not grow without bound. |
| auto_vacuum | INCREMENTAL | Lets you reclaim space in small steps after bulk deletes instead of one giant VACUUM. |
03Paging a million rows without OFFSET
OFFSET 30000 LIMIT 30 makes SQLite walk and throw away 30,000 rows before returning thirty. It also shifts when rows are inserted, so users see duplicates and gaps. Keyset paging remembers the last row seen and asks for rows after it. The cost is the same on page 1 and page 10,000.
Keyset paging
1-- first page2SELECT id, title, due_at FROM job3WHERE assignee_id = ? AND status = 1 AND deleted = 04ORDER BY due_at, id5LIMIT 30;6 7-- next page: pass the last row's (due_at, id)8SELECT id, title, due_at FROM job9WHERE assignee_id = ? AND status = 1 AND deleted = 010 AND (due_at, id) > (?, ?) -- row-value comparison, SQLite 3.15+11ORDER BY due_at, id12LIMIT 30;On Android, Paging 3 with Room does keyset-like loading for you through a PagingSource and invalidates when the table changes. On iOS, GRDB's ValueObservation or a cursor you keep in a view model does the same job. In React Native, a native module or op-sqlite query feeds a FlashList. The principle is the same everywhere: never let the UI hold the whole table.
04Full-text search with FTS5
Searching with LIKE '%term%' cannot use an index and scans everything. FTS5 builds an inverted index. For synced data, use an external-content table so you do not store the text twice, and keep it fresh with triggers.
Two practical details. First, tokenise for your users: the unicode61 tokenizer with remove_diacritics handles most European text, and the trigram tokenizer helps substring search and languages without spaces, at a size cost. Second, update the index in the same transaction as the row, or searches will return rows that no longer exist.
FTS5 over the job table
1CREATE VIRTUAL TABLE job_fts USING fts5(2 title, notes,3 content='job', content_rowid='rowid',4 tokenize='unicode61 remove_diacritics 2'5);6 7CREATE TRIGGER job_ai AFTER INSERT ON job BEGIN8 INSERT INTO job_fts(rowid, title, notes) VALUES (new.rowid, new.title, new.notes);9END;10CREATE TRIGGER job_ad AFTER DELETE ON job BEGIN11 INSERT INTO job_fts(job_fts, rowid, title, notes) VALUES ('delete', old.rowid, old.title, old.notes);12END;13-- WITHOUT ROWID tables need a rowid; keep a hidden integer key if you want external content.14 15SELECT j.id, snippet(job_fts, 0, '[', ']', '…', 8)16FROM job_fts JOIN job j ON j.rowid = job_fts.rowid17WHERE job_fts MATCH 'leak* NEAR/3 valve' ORDER BY rank LIMIT 20;05Migrations for users who skip eleven versions
Server migrations run once on your schedule. Mobile migrations run whenever the user finally opens the app, possibly from a version two years old, on a phone with 200 MB free. That changes the rules.
Rule one: every migration runs from every older version, in order, inside a transaction. Rule two: never drop or rewrite the outbox in a migration, because unsynced work is the one thing you cannot regenerate. Rule three: heavy rewrites (adding a column to a table with millions of rows is cheap, rebuilding a table is not) run in the background in batches, with the app working on the old shape until the new one is ready.
Test with real files. Keep a folder of database snapshots from every released version and run the full migration chain against each of them in CI. Add a crash-loop guard: if a migration fails twice, export the outbox to a file, start with a clean database, then replay the exported operations. A user losing their cache is annoying; a user losing unsynced inspections is a headline.
Expand, migrate, contract
1-- Release N: expand. Add the new column, nullable. Old code ignores it.2ALTER TABLE job ADD COLUMN priority INTEGER;3 4-- Release N (background, 1000 rows at a time): backfill.5UPDATE job SET priority = 0 WHERE rowid IN (6 SELECT rowid FROM job WHERE priority IS NULL LIMIT 1000);7 8-- Release N+2: contract. Only after telemetry shows no old versions use the old shape.9-- (SQLite can DROP COLUMN since 3.35, but rebuilding is still the safe path on older OS versions.)06Disk, battery and the long tail
- Retention: keep a rolling window of completed work (for example 90 days) and let older rows be fetched on demand. A phone is a cache with an opinion, not an archive.
- Blobs stay out of the database. Store files on disk and keep the path, size and hash in a row. Large BLOBs make the WAL and backups heavy.
- Encryption: SQLCipher or the platform's file protection when the data is sensitive. Keep the key in the Android Keystore or iOS Keychain and decide what happens on a lost passcode.
- Reclaiming space: with auto_vacuum incremental, run PRAGMA incremental_vacuum(500) after large deletes, during charging and idle time.
- Observability: log slow queries over 100 ms with their plan (EXPLAIN QUERY PLAN) in debug builds and sampled in production.
07Tested code, explained
The rows Priya's edit creates
Two tables hold everything the phone must remember: the job as the user sees it, and the changes the server hasn't confirmed.
1/** What the supervisor's copy said when the server refused an edit. */2data class Theirs(val field: String, val value: String, val version: Int, val by: String)3 4/** One row of the job table. */5data class Job(6 val id: String,7 val answers: Map<String, String> = emptyMap(),8 val version: Int = 0,9 val syncState: SyncState = SyncState.SYNCED,10 val theirs: Theirs? = null,11 val error: String? = null,12)13 14/** One row of the outbox: a change the server has not confirmed yet. */15data class Op(16 val opId: String,17 val jobId: String,18 val field: String,19 val value: String,20 val baseVersion: Int,21 val by: String = "device",22)val version: Int- The server version this phone last saw. It becomes the base version of the next edit.
val theirs: Theirs?- Empty until a conflict. Then it holds the other person's value, so the screen can show both.
val opId: String- Created on the device before the first send, so a retry can be recognised.
val baseVersion: Int- What the user was looking at. The server uses it to spot a change made behind their back.
Compiled and tested by npm run test:mobile.
A transaction, all or nothing
SQLite gives you this guarantee. The class below behaves the same way, so the tests can crash mid-write and check that nothing changed.
1/**2 * Behaves like SQLite for this lesson: a transaction commits whole or not at all,3 * and observers hear about it once, after it commits.4 */5class LocalDb(cursor: Long = 0) {6 private val lock = Any()7 private var committedOutbox: List<Op> = emptyList()8 private var committedCursor = cursor9 private val jobTable = MutableStateFlow<Map<String, Job>>(emptyMap())10 11 /** The query a screen subscribes to. */12 val jobs: StateFlow<Map<String, Job>> = jobTable.asStateFlow()13 val outbox: List<Op> get() = synchronized(lock) { committedOutbox }14 val cursor: Long get() = synchronized(lock) { committedCursor }15 16 fun <T> transaction(block: Tx.() -> T): T = synchronized(lock) {17 val tx = Tx(jobTable.value, committedOutbox, committedCursor)18 val result = tx.block() // a throw here leaves every table untouched19 committedOutbox = tx.outbox20 committedCursor = tx.cursor21 jobTable.value = tx.jobs // one emission, however many rows changed22 result23 }24}val tx = Tx(...)- Works on a scratch copy of the tables, not the real ones.
tx.block()- Runs your writes. If it throws, the function ends here and the real tables never change.
jobTable.value = tx.jobs- Publishes once at the end, so a transaction touching 140 rows redraws the screen once.
val jobs: StateFlow- This is what a screen subscribes to. On Android, a Room or SQLDelight query plays this role.
Compiled and tested by npm run test:mobile.
08Interview questions
Why WAL and what does it not fix?
A list screen takes 900 ms to open on a 600k-row table. Where do you look?
How do you migrate a table with 5 million rows without freezing the app?
How do you keep the outbox safe across migrations?
Room, GRDB, SQLDelight, WatermelonDB or raw SQLite?
Practise it: