The device is a cache, the server is the record
A wiped phone is the requirement that settles the architecture in one line. If the transcript only exists on devices, a wipe destroys it, so the server holds the record and every device is a cache that can be rebuilt. That is a decision about durability, not about sync, and it comes before any discussion of local databases.
Forty thousand messages is small. Say an average message with metadata is 400 bytes. Forty thousand of them is sixteen megabytes for the text, which fits on the phone and is nothing at all on the server. The interesting numbers are not the total.
They are the read patterns. Users open a conversation and want its last twenty messages in under a couple of hundred milliseconds. They occasionally scroll back through years. Once in a while, on a new device, they want everything. Those three shapes want different things from the same store.
Partition by conversation, cluster by sequence descending
The message row is keyed by conversation id and the per-conversation sequence number the server assigned when it accepted the message. Partition on the conversation, order within the partition by sequence descending, and the most common query becomes a read of the first few rows of one partition. The newest twenty in this chat, in one seek. No sort, no scatter, one node.
Partitioning by user instead is the tempting alternative and it is worse in a way that only shows up later. A conversation between two people would then live in two places and every write would go to both, which doubles the write path and creates two versions of the truth to reconcile. In a group of fifty it is fifty copies. Conversation-keyed storage writes once and lets readers arrive.
The cost of conversation partitioning is the inbox screen, which needs the latest message from each of a user's conversations and would otherwise fan out across many partitions. Keep a small per-user conversation list holding the conversation id, its highest sequence and a denormalised preview of the last message, updated on write. That list is what the home screen reads. It is bounded by the number of conversations a person has, not by messages.
Attachments never live in the row. A photo goes to blob storage and the row holds a key, a content type, dimensions and a thumbnail reference, so the transcript can be rendered before any bytes are fetched. Putting a two-megabyte image in the message table makes every scroll pay for it.
Keyset paging, and what offset costs at page eight hundred
Paging is where a design that looks fine at a thousand messages stops being fine at forty thousand. Do the arithmetic in front of the interviewer. Forty thousand messages at fifty to a page is eight hundred pages. If page N is expressed as OFFSET (N-1)*50, then reaching page eight hundred means the database walks and discards 39,950 rows to return fifty. Every deep scroll gets slower in proportion to how far back you go, and the pathological case is the user who scrolls patiently to the beginning of a five-year conversation.
Use a cursor instead. The client says "give me fifty messages in this conversation with sequence below 12,340" and the store seeks directly to that point in the clustered order.
SELECT sequence, sender_id, body, composed_at
FROM messages
WHERE conversation_id = :conversation
AND sequence < :cursor
ORDER BY sequence DESC
LIMIT 50;
The cursor is also stable under concurrent writes. Offset paging shifts when new messages arrive, so a page boundary can repeat a row or skip one; a sequence cursor cannot, because the sequence of the row you last saw does not change.
For the new-device restore, do not page the same way. Eight hundred sequential round trips over a mobile network is minutes of waiting. Fetch a shallow slice of every conversation first, enough to render the inbox and open any chat, then backfill deeper history in the background with a much larger page size, oldest conversations last. The user is looking at a usable app while the rest arrives.
Per-user state, kept out of the message
One message, many recipients, and each recipient has their own opinion about it: read or unread, deleted for me, starred, reported. None of that belongs on the shared row, because writing to a shared row on behalf of one member turns every read receipt into a write on a hot partition.
Read position is a cursor per participant, as a single highest-seen sequence. Deletions for one user are rows in a small exclusion table consulted at read time, or a tombstone list the client applies locally after sync. Both keep the message body written exactly once and make per-user state proportional to what a user has done rather than to what they have received.
Deleting for everyone is different and is the one case that touches the shared row. Replace the body with a tombstone and keep the sequence, because the sequence is load-bearing for ordering and gap detection on every client that has not synced yet.
What a wipe really tests is where the key lives
Here is the part that separates an answer that has shipped a messenger from one that has read about storage layouts. If the messages are end-to-end encrypted and the decryption key exists only on the device, then wiping the phone destroys the key, and the ciphertext sitting safely on your servers is unreadable forever. The storage design was never the problem.
So "survives a wipe" forces an explicit choice about key custody. Either you hold keys yourself, which makes restore trivial and means you can read user messages. Or the user's key material is escrowed under something they know or hold: a passphrase, a recovery code, another enrolled device. Restore then depends on them producing it. Or transcripts do not survive a wipe, which is a legitimate product stance that has to be stated as one rather than discovered by a customer.
Say which of the three you are building. An answer that describes beautiful partitioning and never notices that encryption changes the requirement has answered a different question.
Two traps in the same design
The first is the group write amplification you get by keying on the recipient rather than the conversation, because it is invisible in a two-person chat and it is the whole cost of the system in a large group.
The second is quieter: paging that works because your test account has two hundred messages. Nothing fails, no error appears, and the design is only wrong for the users who have been on the product longest, which is the cohort you can least afford to make wait.
One row per message in a conversation-keyed partition, keyset cursors instead of offsets, per-user opinions in a side table, and an explicit answer about who holds the key — a wipe then costs the user a download rather than their history.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).