The local database

At the end of chapter 6, Ben came out of the subway and his phone caught up on what he had missed, by its inbox cursor. What that chapter did not say: where do the fetched messages go?

Earlier chapters left two debts. Chapter 3 said the outbox must be on disk, so unsent messages survive the app being killed; chapter 6’s inbox cursor must also survive a restart, or every launch pulls from the beginning. This chapter puts them, and the messages themselves, into a local databaselocal database本地数据库The phone’s own copy of messages, conversations and the sync cursor, usually SQLite. The screen reads only it, and everything from the network is written into it first; a cursor is committed in the same transaction as the messages it stands for.See the glossary on the phone.

Three questions: why the phone keeps its own copy; what it stores; and how to write it so that no message goes missing whenever the app is killed. The third is this chapter’s real decision.

1. Why the phone keeps a copy

Keeping nothing is possible. The phone holds only the screen being looked at, in memory, and asks the server for the last 50 messages each time a chat is opened. Web clients often do just that (chapter 26). On a phone, it costs this:

• By the book’s numbers Ben opens the app 20 times a day; say he looks at 3 chats each time: 20 × 3 = 60 chats opened a day.
• Each open fetches the last 50: 50 × 200 bytes = 10 KB; a day, 60 × 10 KB = 600 KB. What Ben actually receives in a day is 267 × 200 bytes ≈ 53 KB (chapter 6). The same messages are downloaded again and again, 11 times what arrives.
• Each open waits at least one round trip: 0.2 s on a mobile network, 60 × 0.2 = 12 s a day, and that is on a good network. In the subway, nothing shows.

Two things must be on disk anyway: the outbox (chapter 3) and the inbox cursor (chapter 6). Once there is a place that survives a restart, the messages go there too.

So the work on the phone is split like this:

• The screen reads only the local database. Opening a chat is a local query for the last 50, with no network.
• Everything from the network is written to the database first: each page a sync pulls, each pushed message, each ACK from the server. Then the screen reads it from the database.
• Sent messages are written to the database first too, as “sending”, and shown at once; the ACK turns them into “sent”.

The local database most phones use is SQLite. It is not a server to connect to but a library inside the app: the whole database is one file in the app’s folder, which the app reads and writes directly; iOS and Android both ship it. Telegram’s client library TDLib and Signal’s apps are built on it too, as we will see.

2. What to store

Four tables are enough for this chapter:

CREATE TABLE messages (
  id              INTEGER PRIMARY KEY,         -- the local row number
  conversation_id INTEGER NOT NULL,
  seq             INTEGER,                     -- the server's seq in the conversation (chapter 5); none yet while sending
  sender_id       INTEGER NOT NULL,
  msg_id          BLOB NOT NULL,               -- the message ID the phone made (chapter 4)
  text            TEXT,
  state           TEXT NOT NULL,               -- 'sending' / 'sent' / 'failed' / 'received'
  UNIQUE (conversation_id, seq),
  UNIQUE (sender_id, msg_id)
);
CREATE TABLE conversations (id INTEGER PRIMARY KEY, last_seq INTEGER, preview TEXT);  -- highest seq held; last message's preview
CREATE TABLE gaps (conversation_id INTEGER, from_seq INTEGER, to_seq INTEGER);        -- a stretch not downloaded yet
CREATE TABLE sync_state (key TEXT PRIMARY KEY, value INTEGER);                        -- the inbox cursor

A few notes:

• The outbox is not another table. It is the rows of messages with state = 'sending'. A message has one row: showing it, resending it and ACKing it all change that row. When the app restarts, it finds the rows still “sending” and resends them with their original message IDs (chapter 4).
• (conversation_id, seq) is unique, and it is the index the chat screen uses. Opening a chat asks for “the 50 messages with the highest seq in this conversation”: with the index, the database goes straight to the end of that conversation and reads 50 rows, as fast with a million messages on the phone; without it, it reads the whole table. The two unique keys have another use, in section 4: the same message written twice is recognised.
• gaps records holes. Chapter 6 said that after a long time offline the phone takes only each conversation’s newest message and fetches the rest when the chat is opened. So a local conversation has its newest message, then a stretch not yet downloaded, then older messages. This table remembers where, for example a row (hiking, 120, 140): seqs 120 to 140 of the hiking group are still only on the server. When Ben scrolls up to there, the phone fetches them.
• conversations.last_seq is the highest seq the phone has in that conversation, which chapter 5 uses to see a jump; preview is only the last message shown in the list; the full conversation list is chapter 17’s.
• The cursor lives in the same database, not in the app’s settings. Why is the next section.

How big it gets. Ben receives 267 messages a day and sends 40: 307 in all. Count 300 bytes per message (the 200-byte message, plus two indexes and slack in the pages; an assumption): 307 × 300 bytes ≈ 92 KB a day, 33.6 MB a year, about 100 MB in three years. Text is not what fills a phone; photos and videos are (chapter 8).

3. Watch it fail: the cursor gets ahead of the messages

Two things on the phone must agree: the messages and the cursor. The cursor means “I have everything up to here”. After a restart the phone asks only for what comes after it, and the server can only trust it.

A natural way to write them is in two places. The cursor is one number, a good fit for the app’s settings: iOS’s UserDefaults and Android’s SharedPreferences are a small settings file, separate from the database, written to disk when they decide. The network code saves the cursor when a reply arrives. The messages are many, so they go to a write queue on a background thread that writes them into the database one by one without blocking the screen. So when the cursor is saved, the queue often has not finished. And both are written to disk in the background (SharedPreferences’ apply(), for one): which one reaches the disk first is not fixed. That is the real problem.

Here is chapter 6’s busy evening: Ben comes back to 230 new messages in three groups (120, 80 and 30), 50 a page, pulled one page after another, a page every 0.2 s on a mobile network. Say writing one message takes 0.1 ms. Each commit waits until the data is really on flash (fsync); say 5 ms here. Drag “the app is killed at” to see what happens after a restart if the app is killed at that moment.

The app is killed at 1.0 s: the last page has just arrived and the cursor is already saved as 230, but some messages are still queued for the disk. After the restart, what happens to them?

They are downloaded again after the restartThey are missing on this phone until that group’s next messageThe server notices and pushes them again

↑ Guess first

messages on disksaved cursorkilled here: messages missing

On disk at the kill

–

After the restart it pulls after the cursor; missing

–

Moments in the sync that lose messages

–

Commits

–

Cursor first, messages one by onecursor in settings; a transaction per messageCursor first, a page at a timecursor in settings; a transaction per pageOne transactiona page of messages and the cursor together

The app is killed at

1,000 ms

The default is “cursor first, messages one by one”. Without BEGIN … COMMIT, SQLite treats each INSERT as its own transaction, and each waits for a commit: a page of 50 takes 50 × 5.1 ≈ 255 ms, but the next page arrives 200 ms later. The queue grows; the network is done at 1.0 s, the disk at 1.37 s. The cursor was saved when each reply arrived, without waiting for the queue: so the cursor is already 230 while the messages on disk lag behind. The shaded area is that difference.

Killed at 1.0 s: 156 messages on disk, the cursor at 230. After the restart the phone pulls after 230, and the 74 in between (hiking 38, classmates 26, work 10) the server thinks it already has. They will not be sent again.

They are not gone for good: chapter 5’s seq is still there. When the hiking group’s next message arrives, the phone sees its seq jump by dozens past the highest it holds, and fetches the gap. These three groups talk all evening, so it is filled within minutes. But if the missing message were Ben’s mother’s “arriving tomorrow morning”, it would wait for her next message: tomorrow, or next week. Until then Ben’s list shows the old last message; he never sees hers, and nothing tells him.

Switch to “cursor first, a page at a time”: 50 messages in one transaction, written in 50 × 0.1 + 5 = 10 ms, before the next page. The window is much smaller but still there: killed in the 10 ms after a reply arrives, with the cursor saved and the page not yet committed, that page is missing. 4.7% of the moments in the sync fall in such a window; killed right at 1.0 s, the last page’s 30 are missing.

Switch to “one transaction”: a page of messages and the cursor commit together. Killed before the commit, neither is written, and after the restart the phone pulls that page again from the old cursor; killed after it, both are there. No moment loses a message.

The 85.3% and 4.7% of the first two rest on the assumption that a commit waits 5 ms. With WAL mode and synchronous = NORMAL, below, commits need not wait for flash and even the one-by-one queue keeps up with the network: at 0.5 ms a commit, 85.3% falls to 13.5%, and a page at a time to 2.7%. The window does not go away: as long as the cursor is saved first and the messages after, the window is as long as a page takes to write. The only number that rests on no assumption is the 0 of “one transaction”.

Is being killed common? Yes. Once the app is in the background, iOS gives it only a short time to finish what it is doing, and Apple’s documentation is blunt: if you don’t end your tasks in time, the system terminates your app. Android kills background processes when memory is short. Users swipe apps away, and apps crash. And a sync often happens in exactly the first seconds after the app opens or a push wakes it.

How often the window is hit, no public number says. By chapter 6, v1 runs 2,000,000 syncs a day; if only 1 in 10,000 is cut between the two writes, that is 200 phones a day missing messages, and 20 at 1 in 100,000. Nothing reports an error, users cannot describe it, and tests rarely reproduce it: the user only says “I never got that message”.

4. The fix: a page of messages and the cursor, one transaction

One rule: the cursor may not be written before what it stands for. The simplest way is to put them in one transaction:

BEGIN;
INSERT INTO messages (conversation_id, seq, sender_id, msg_id, text, state)
  VALUES (...), (...), ...      -- this page's 50; Ben's own have state 'sent', others' 'received'
  ON CONFLICT (sender_id, msg_id) DO UPDATE SET seq = excluded.seq, state = excluded.state;
INSERT INTO conversations (id, last_seq, preview) VALUES (:conv, :seq, :text)
  ON CONFLICT (id) DO UPDATE SET                             -- each conversation in the page
    last_seq = max(coalesce(last_seq, 0), excluded.last_seq),
    preview  = CASE WHEN excluded.last_seq >= coalesce(last_seq, 0) THEN excluded.preview ELSE preview END;
INSERT INTO sync_state (key, value) VALUES ('inbox_cursor', :next_cursor)
  ON CONFLICT (key) DO UPDATE SET value = excluded.value;
COMMIT;

SQLite’s documentation guarantees that the changes in one transaction happen completely or not at all, even if writing them is interrupted by a program crash, an operating-system crash or a power failure. So the cursor and its messages always appear on disk together. That is also why the cursor must be in the same database: the settings file and the database are two stores, and no transaction spans both.

ON CONFLICT … DO UPDATE means: if a row with this (sender, message ID) already exists, do not insert another; update that row with the new seq and state. So repeated writes are harmless: killed before the commit, the same page comes again after the restart; a push and a sync may bring the same message; each lands on its original row. It is chapter 4’s server-side dedup, on the phone. Ben’s own message, still “sending”, becomes “sent” with its seq the same way when it syncs back from his inbox. The conversations statement works the same way: a conversation seen for the first time gets a new row; a known one only has last_seq raised, and its preview changes only when the message is newer; coalesce treats an empty last_seq as 0. The sync_state statement also works on the first run, when there is no cursor yet. ON CONFLICT … DO UPDATE needs SQLite 3.24 or later; Android’s built-in SQLite was still 3.22 through Android 10 (Android’s documentation lists each version), so apps that support older phones either bundle their own SQLite or do INSERT OR IGNORE followed by UPDATE.

The same rule applies everywhere the phone writes:

• A pushed message: if its inbox seq is exactly cursor + 1 (chapter 6), the message and the new cursor commit together; if it jumps, store only the message, leave the cursor, and fetch the missing stretch.
• Sending: first one transaction writes the row with state = 'sending', the screen shows “sending”, and then it goes to the network. When the ACK comes, one UPDATE fills in the seq and marks it sent. If the ACK is lost, the message syncs back from the inbox and lands on the same row by the conflict rule above.
• Filling a gap: Ben scrolls up and a stretch of older messages arrives; those messages and the shortened gaps row commit together. The conversations statement runs as usual, but the older seqs are below last_seq, so neither the preview nor last_seq is set back.

One more detail. SQLite has a WAL mode (changes are appended to a log file first), and with synchronous = NORMAL (not waiting for flash at every commit) it writes faster. The documentation says that committed transactions then survive an application crash, but a power loss or system crash may roll back the last few. That is fine: the cursor and its messages are in the same transaction and roll back together, still agreeing. A cursor kept elsewhere has no such guarantee.

5. Estimate: how much the phone writes in a big sync

One transaction a page also fixes something else: writing too slowly.

Chapter 6’s Ben back from a week offline: downloading everything is 1,869 messages in 38 pages, 38 × 0.2 = 7.6 s on a mobile network, about 246 messages a second to write.

• A transaction per message: 1,869 commits at the assumed 0.1 + 5 ms each, 9.5 s in all, slower than the download. The write queue keeps growing, and the list on screen catches up slowly.
• A transaction per page: 38 commits, 1,869 × 0.1 + 38 × 5 ms ≈ 0.38 s.

A month offline (8,010 messages, 161 pages): 40.9 s with a transaction per message, 1.6 s with one per page.

The 5 ms is this chapter’s assumption, and phones and settings differ a lot; what does not depend on it is the number of commits: 1,869 against 38, 49 times fewer. Each commit is a write to flash. SQLite’s FAQ, answering “INSERT is really slow”, says exactly this: on an ordinary computer it does 50,000 or more INSERTs a second but only “a few dozen transactions per second”, because by default each commit waits until the data is really on the disk. That number is for spinning disks, and flash is much faster, but the FAQ’s later note says the gist holds: wrapping many INSERTs in one BEGIN … COMMIT spreads the cost of the commit over all of them.

These writes run on a background thread, and the screen only reads. With reads and writes apart, a big sync does not freeze the screen while Ben scrolls.

6. Reinstalling and new phones

The local database lives in the app’s folder; delete the app or change phones and it is gone. How much comes back depends on how much the server keeps. This book’s server keeps the messages, so a new phone does chapter 6’s “list first”: the newest message of each of 200 conversations, about 42 KB in 4 requests, and older messages as Ben scrolls. How deep a new device’s first sync should go is chapter 14’s question. Some products’ servers delete a message once delivered, such as WhatsApp (privacy policy) and Signal (terms); history is only on the phone, and losing it means a backup or the old phone: chapter 41.

7. The cost

• Two copies of the truth. The server has one, the phone has one, and the phone’s can be wrong: a message the server rejects must be marked failed; a hole from syncing must go into gaps and be filled when reached; the conversation list’s unread counts and last messages drift from the server’s (chapter 17 checks them at login). The client must be able to repair itself.
• Storage on the phone. About 34 MB of text a year; in v2 the 500-member hiking group sends 1,000 messages a day, 110 MB a year for that one group. Users need a way to clean up, and someone must decide how long old messages stay.
• Upgrades need migrations. The tables change between versions; on every upgrade the old database on each phone must be changed in place, without errors and without making the app hang for seconds at launch (chapter 29).
• Discipline in the code. Every place that moves a cursor or a state must do it in one transaction with the data it stands for. The rule is easy; keeping everyone to it as the code grows is not.

Resource	Nothing stored, ask the server each time	Local database, a transaction per page
Phone requests	one per chat opened, 60 a day	none to open a chat; only syncs and gap fills
Phone data	about 600 KB a day for opening chats	only new messages, about 53 KB a day
Phone storage	almost none	about 92 KB a day, 34 MB a year
Phone disk writes	almost none	38 commits after a week offline (1,869 one by one)

8. Other answers

• Store nothing, or only the latest few: no migrations, no second copy of the truth, and a new device is no different. Fine where the network is always good and conversations are few (say, a customer-service window in a browser). Chapter 26 weighs it for the web.
• Messages first, then the cursor, in separate stores: when the cursor is managed by a library you cannot change and cannot join the transaction, this order loses nothing either, as long as the writes are idempotent (a unique key, repeats skipped). The cost is downloading a page again after a kill.
• TDLib: Telegram’s open-source client library. Its README says it takes care of the network, encryption and local data storage, and its source includes SQLite. Among its parameters, use_message_database is described as “keep cache of chats and messages between restarts”, and database_encryption_key encrypts the database. That is this chapter’s design: the local database is part of the client library, and the app does not write its own.
• Signal: the local database is an encrypted SQLite, SQLCipher; it is in the dependencies of the Android and desktop apps. The server keeps no history, so a stolen phone holds all of it, and encryption is well worth it; the cost is some CPU on every read and write, and a safe place for the key.

9. This chapter’s decision

The messages have a home. Next chapter: a 4 MB photo should not squeeze through the same connection as the text messages.