/Learn
Mobile systems at scale · Part 2 of 7

SQLite at a million rows

Local databases are the product. Here are the schema, pragmas, paging and migration rules that stay fast at a million rows and safe for users who skip eleven updates.

02
AndroidiOSSQLite

Mobile engineeringAll mobile pages

Priya's edit lands here first. Every other part reads what this database holds, so its speed and safety decide how the whole app feels. Start from the basics in Day 2 →

Study with AI

If you only read this

  1. Turn on WAL, keep transactions short and put the outbox in the same transaction as the data.
  2. Page with keyset (seek) queries on an indexed key. OFFSET gets slower with every page.
  3. Migrations must work from any old version, never touch the outbox destructively, and be tested on real old files.
The one ruleDesign the database for the worst user, not the median one: a million rows, a month offline, and an app that was killed mid-write.

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

schema-built-list.sqlSQL
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 here
24 linesUTF-8SQL
Say it out loudA partial index (WHERE deleted = 0) keeps tombstones out of the index the UI uses, so deleting a million rows does not slow the list down.

02WAL, 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.

SettingTypical valueWhy
journal_modeWALReaders do not block the writer and vice versa.
synchronousNORMAL with WALDurable enough against app crashes; a power cut can lose the last commits but never corrupt the file.
busy_timeout3000–5000 msWait for the writer instead of failing with SQLITE_BUSY.
foreign_keysONOff by default per connection. Turn it on every time you open.
wal_autocheckpoint1000 pages (default)Large bulk imports may checkpoint manually so the WAL file does not grow without bound.
auto_vacuumINCREMENTALLets you reclaim space in small steps after bulk deletes instead of one giant VACUUM.
Common mistakeOpening several connections that all write. SQLite still has one writer, so you get lock contention for no gain. Use one write connection and a small pool of read connections.

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

keyset-paging.sqlSQL
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;
12 linesUTF-8SQL

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

fts5-over-job.sqlSQL
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;
17 linesUTF-8SQL
GotchaExternal-content FTS tables need a real rowid. If you chose WITHOUT ROWID for the main table, either keep a separate integer key or store the text in the FTS table directly.

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

expand-migrate-contract.sqlSQL
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.)
9 linesUTF-8SQL

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.

LocalDb.ktKotlin
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)
22 linesUTF-8Kotlin
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.

LocalDb.ktKotlin
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}
24 linesUTF-8Kotlin
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?
Readers and the single writer stop blocking each other, which suits a UI reading while sync writes. It does not allow two concurrent writers and does not make long transactions acceptable.
A list screen takes 900 ms to open on a 600k-row table. Where do you look?
The query plan first. Usually a missing or wrong composite index, an OFFSET, a sort not covered by the index, or loading too many columns including large text. Fix with a matching index and keyset paging, then measure again.
How do you migrate a table with 5 million rows without freezing the app?
Add columns nullable (cheap), backfill in small batches in the background, keep the app reading both shapes until done, and only then add constraints or drop the old column.
How do you keep the outbox safe across migrations?
Migrations never rewrite or drop it. Version operation payloads so newer code can still read old ones, and export the outbox before a failed migration resets anything.
Room, GRDB, SQLDelight, WatermelonDB or raw SQLite?
Pick by team and platform. Room on Android and GRDB on iOS give compile-time safety and observation. SQLDelight suits shared KMP code. WatermelonDB or op-sqlite suit React Native. The schema and sync rules matter more than the wrapper.

Practise it:

AndroidiOSSQLite
Written by

Rizvan

Builds mobile and AI systems at scale and writes the series he wished existed before his own principal loop: one rule, one analogy, something to break, and the interview kit.

All 7 parts
AI Engineer · written by Rizvan · part 2 of 7