Write-behind: fewer database writes, same progress

In brief: Do not turn every video-player heartbeat into a database transaction.

The problem

A player might report progress every 15 seconds. Multiply that by thousands of learners and these tiny writes pile up. They add lock, index and replication pressure without adding matching business value.

Why the simple answer is not enough

Progress events behave like telemetry: they are frequent, retryable and valuable mainly as an ordered summary. Persisting every heartbeat directly causes write amplification across indexes, the WAL and replicas. Yet the business only needs a durable checkpoint.

The design must still preserve the right semantics. A learner may seek backward, change playback speed or watch on two devices. Because of that, “the latest timestamp received” and “the greatest progress ever seen” are not always the same business rule.

System flow

Architecture decisions

Define the progress model first

Store monotonic completion separately from the current resume position. Completion can use MAX. Resume position should use a sequence number, or a client event time with a bounded clock-skew policy.

Merge atomically in Redis

A single Lua script or transaction compares the incoming sequence, updates the compact state and marks the learner dirty in one operation. This keeps two devices from clobbering the newest accepted event.

Flush with idempotent upserts

Workers claim a bounded batch, write checkpoints behind a version guard, and remove only the entries whose Redis version still matches. A newer event that arrives during the flush therefore stays pending.

Going deeper

Model ordering before optimising writes

A client sequence number is only meaningful inside a single session. Reinstalling the app, opening another device or replaying an offline queue can produce overlapping counters. So give each playback session a server-issued identifier, and keep both the session sequence and the server acceptance version. The merge function should be deterministic: completion is monotonic, watched ranges may be unioned, and resume position follows the newest accepted event rather than the numerical maximum. Keeping these concepts separate stops the player from jumping forward when a learner revisits an earlier chapter.

Design the flush protocol as a state machine

A worker claims a bounded set of dirty keys with a lease, reads their current versions, then performs conditional database upserts. After commit, it removes a key only when Redis still holds the flushed version; otherwise a newer event stays dirty. Batch size must respect database parameter limits, lock duration and replica lag. Add jitter to the schedule so many workers do not flush on the same boundary. For a graceful shutdown, stop claiming batches, finish the active transaction, and release the lease (or let it expire) so another worker can continue.

Implementation path

1. Acknowledge each progress ping only after it is safely merged into Redis.
2. Use an idempotent merge rule so retries never move progress backwards.
3. Flush batches on a schedule, and retry failed rows without repeating the ones that succeeded.

Technical example: Versioned merge and durable checkpoint

incoming = { userId, lessonId, position, completed, seq }
state = redis.HGET(progressKey)
if incoming.seq <= state.seq then return state end

next.position  = incoming.position
next.completed = state.completed OR incoming.completed
next.seq       = incoming.seq
redis.HSET(progressKey, next)
redis.ZADD(dirtyKey, now(), progressKey)

DB UPSERT ... WHERE stored_seq < next.seq

Let the dirty-set score represent the first-unflushed time. That makes both batching and age-based alerts straightforward. The worker must still compare versions before clearing the pending marker.

Failure modes to design for

• Redis accepts an event, then restarts before replication or AOF persistence completes. Document the recovery point objective, and force an immediate flush for high-value milestones such as course completion.
• A poison record repeatedly fails schema or foreign-key validation. Move it to a dead-letter stream after a bounded number of retries, so one learner cannot block the whole batch.
• Two devices emit counters that cannot be compared. Use a server-issued session ID plus a sequence, then define an explicit merge rule across sessions.

What to observe

How to validate the design

• Generate out-of-order and duplicate heartbeats from two sessions, then compare the final state against the documented merge model.
• Kill a worker after the database commit but before the dirty marker is removed; the repeated flush must produce the same checkpoint.
• Kill Redis before an acknowledged event is durable, and verify the measured loss stays within the declared RPO.
• Inject one invalid record into a batch and confirm that valid learners keep flowing while the bad item reaches dead-letter handling.
• Load-test the peak heartbeat rate plus recovery traffic after an outage, not just steady-state throughput.

Safe rollout plan

Start with a dual-write phase: keep the current durable path, compute the write-behind state alongside it, and compare checkpoints asynchronously. Break down mismatches by lesson, client version and device count. Once the merge rule is proven, move a small course or tenant cohort to Redis acknowledgement, keep an immediate-flush path for completion events, and retain a switch to restore direct writes. Then raise the flush interval gradually, watching oldest-dirty age and any support reports about lost or backward progress.

Production checklist

• Redis durability determines how much recent progress you could lose.
• The flush interval trades write reduction against recovery accuracy.
• Monitor buffer age, pending learners and failed flushes.

Takeaway

Write-behind works when the merge rule is deterministic and the delay is visible to operations.