If you only read this
- Per-tenant monotonically increasing sequence numbers make pulls simple and gaps detectable.
- Move heavy reads to CDN snapshots and fan out via CDC, not by querying Postgres from every phone.
- Clients are the biggest load generator. Use jitter, Retry-After, coalesced push hints and per-device rate limits.
01Do the arithmetic first
| Quantity | Estimate | How |
|---|---|---|
| Devices | 5 million | given |
| Daily active | ≈ 2.5 million | 50% |
| Syncs per active device per day | ≈ 24 | hourly-ish plus app opens |
| Average sync requests | ≈ 700 per second | 2.5M × 24 ÷ 86,400 |
| Peak (morning, after outage) | 7,000 to 35,000 per second | 10× to 50× average |
| Pull payload | mostly empty | most syncs find nothing new |
Two things stand out. The average stays modest, so spikes dominate the system. And most requests find nothing, so the cheapest possible answer to 'any changes?' is the most valuable thing you can build.
02Architecture
The sync API is stateless, so any instance can serve any request and you scale by adding instances. State lives in Postgres (source of truth), Redis (hot counters and rate limits) and object storage (snapshots).
03Change log and the sequence number
Every accepted write appends a row to a change log with a sequence number that is monotonically increasing per tenant (or per shard). A client's cursor is just 'the last sequence I have'. Pulling is a range scan: give me everything after N. Gaps are detectable: if you receive 101 then 103, you missed 102.
A global auto-increment is a bottleneck and a trap: transactions commit out of order, so a reader can see 105 before 104 commits and skip it forever. Allocate sequences per tenant inside the same transaction as the write, from a counter row, so ordering within the tenant is strict. Then readers use the highest committed sequence as a safe watermark.
Write path in one transaction
1BEGIN;2 -- idempotency: a repeat of op_id returns the stored result and does nothing else3 INSERT INTO applied_op (tenant_id, op_id, result_seq) VALUES ($1, $2, NULL)4 ON CONFLICT DO NOTHING; -- 0 rows means duplicate: return stored seq5 6 UPDATE tenant_counter SET seq = seq + 1 WHERE tenant_id = $1 RETURNING seq; -- allocate (row lock)7 8 UPDATE job SET title = $3, version = version + 1 WHERE id = $4 AND version = $5; -- 0 rows: 4099 10 INSERT INTO change_log (tenant_id, seq, entity, entity_id, op, payload)11 VALUES ($1, $seq, 'job', $4, 'update', $6);12 UPDATE applied_op SET result_seq = $seq WHERE tenant_id = $1 AND op_id = $2;13COMMIT;14 15-- pull16SELECT seq, entity, entity_id, op, payload FROM change_log17WHERE tenant_id = $1 AND seq > $cursor ORDER BY seq LIMIT 500;04Partitioning, retention and the 410
The change log grows forever unless you decide otherwise. Partition by time or tenant range so old partitions detach cheaply. Keep, say, 30 days of changes. A client that has been away longer than the retention window asks for changes older than the log has, and the server answers 410 Gone with a pointer to a snapshot.
A snapshot is a compact, ready-made copy of a tenant's data at a sequence number, built in the background and stored as files on a CDN. The client downloads it, loads it into SQLite and continues from the snapshot's sequence. This is also how a brand-new install syncs: from the CDN, not from Postgres.
05Fan-out without hammering Postgres
Phones should not poll the database. When a change commits, Debezium captures it from the write-ahead log into Kafka. Consumers do three jobs: update the snapshot builder, update the cached head sequence in Redis so 'any changes?' is answered from memory, and send a push hint to devices of the affected users.
Push is a hint, not a payload: 'tenant 7 changed, sync when you can'. Coalesce hints (at most one per device per minute), because a burst of 500 changes should wake the phone once. Never rely on push for delivery. Cursor sync is the guarantee.
The cheap 'anything new?' call
1GET /v1/sync/head?tenant=t7 -> 200 {"head":88412} (served from Redis, ~1 ms)2Client compares with its cursor (88412). Equal: stop. Behind: GET /v1/sync/pull?cursor=8829006Protecting the API from its own clients
- Jitter everywhere: scheduled syncs, retry delays and reconnects use randomised exponential backoff, so a million phones do not act in the same second.
- Honour Retry-After: the server tells clients when to come back, and the SDK obeys it.
- Rate-limit per device and per tenant, and answer 429 early at the gateway before expensive work.
- Shed load by priority: record pushes are more important than telemetry or background refresh. Drop the cheap, deferrable things first.
- Backward compatibility: old app versions live for years. Version the protocol, keep unknown fields ignorable and enforce a minimum client version only when you must.
- Observe the cursor: alert on devices whose cursor lags the head by more than a day, 410 rate, and push-to-sync latency.
07Tested code, explained
Applying one op on the server
Each op must apply exactly once, however many times it arrives. In production this is one Postgres transaction per op.
1/** The server side of sync. Each op is one transaction: check, merge, number, log. */2class SyncServer(startSeq: Long, private val logFloor: Long = 0) : SyncApi {3 private class Row(val fields: MutableMap<String, Stored> = HashMap(), var version: Int = 0)4 5 private val rows = HashMap<String, Row>()6 private val applied = HashMap<String, OpResult>() // op_id -> the answer we gave7 private val log = ArrayList<Change>()8 private var seq = startSeq9 10 /** Rows written, so a test can prove that a retry writes nothing. */11 var writes = 012 private set13 var offline = false14 15 fun seedJob(jobId: String, version: Int) { rows[jobId] = Row(version = version) }16 17 override suspend fun push(ops: List<Op>): List<OpResult> {18 if (offline) throw TryLater()19 return synchronized(this) { ops.map(::applyOne) }20 }21 22 private fun applyOne(op: Op): OpResult {23 applied[op.opId]?.let { return it } // a retry: same answer, no write24 val row = rows.getOrPut(op.jobId) { Row() }25 val result = when (val m = merge(op.field, op.value, op.baseVersion, row.fields[op.field])) {26 is Merge.Refuse -> OpResult.Conflict(op.opId, Theirs(op.field, m.current.value, row.version, m.current.by))27 is Merge.Apply -> {28 row.version += 129 row.fields[op.field] = Stored(m.value, row.version, op.by)30 seq += 131 log += Change(seq, op.jobId, op.field, m.value, row.version)32 writes += 133 OpResult.Applied(op.opId, seq)34 }35 }36 applied[op.opId] = result37 return result38 }39 // pull follows40}applied[op.opId]?.let { return it }- The first line. A retry returns the saved answer and writes nothing. A conflict counts too, so a retry can't turn it into a success.
merge(op.field, ...)- The same function as the merge lesson.
seq += 1- The next line number in the log. In production a per-customer row lock keeps these in commit order.
log += Change(...)- Every change goes here. Other devices read only this.
Compiled and tested by npm run test:mobile.
Serving a pull
A device says where it got to, and gets what comes after.
1override suspend fun pull(cursor: Long, limit: Int): PullPage {2 if (offline) throw TryLater()3 if (cursor < logFloor) throw CursorTooOld("/snapshots/latest")4 return synchronized(this) {5 val after = log.filter { it.seq > cursor }6 val page = after.take(limit)7 PullPage(page, page.lastOrNull()?.seq ?: cursor, hasMore = after.size > limit)8 }9}cursor < logFloor- This device is older than the log keeps, so answer 410 and send it to a snapshot.
it.seq > cursor- Only what this device hasn't read. Asking twice is harmless.
hasMore = after.size > limit- Tells the phone to keep pulling. Large catch-ups arrive in safe pieces.
Compiled and tested by npm run test:mobile.
Telling other devices to look
A push doesn't carry the change. It just says something changed, so Dev's tablet pulls by cursor like any other device.
1/** Push says "come and look". It carries no data, and one push covers many changes. */2class ChangeNotifier(private val sendPush: (device: String) -> Unit) {3 fun onCommitted(changes: List<Change>, devicesFor: (jobId: String) -> Set<String>, author: String) {4 changes.flatMap { devicesFor(it.jobId) }5 .toSet()6 .minus(author)7 .forEach(sendPush)8 }9}flatMap { devicesFor(it.jobId) }- Finds who cares about each change.
.toSet()- Five changes for one tablet still mean one push, which saves battery and provider quota.
.minus(author)- The phone that made the change doesn't need telling.
Compiled and tested by npm run test:mobile.
When to try again
After an outage, every phone wants to retry. This spreads them out.
1/** Full jitter: anywhere from zero up to the exponential cap, so phones spread out after an outage. */2fun retryDelayMs(attempt: Int, random: Random, baseMs: Long = 1_000, capMs: Long = 300_000): Long {3 val exponential = baseMs shl attempt.coerceIn(0, 20)4 return random.nextLong(minOf(exponential, capMs) + 1)5}baseMs shl attempt- Doubles the wait each attempt: 1, 2, 4, 8 seconds and so on.
minOf(exponential, capMs)- Stops growing at five minutes, so a phone never waits for hours.
random.nextLong(...)- Full jitter picks anywhere from zero up to that limit. Without it, phones that failed together retry together.
Compiled and tested by npm run test:mobile.
08Interview questions
Why not use the database's global auto-increment as the cursor?
After a 3-hour outage every phone retries at once. How do you survive?
How does a phone that was off for 60 days catch up?
Why CDC rather than the API publishing events?
How do you shard when one tenant is enormous?
Practise it: