Write-behind: absorbing write-heavy load without overloading the database

One kind of load tires a database out very easily: a great many small writes, each carrying only a scrap of information. Not orders, not money transfers, just "this learner is at second 412." Thirty seconds later the number is 442. If a few seconds of progress are lost, the experience may barely suffer. But if every one of those numbers goes straight into Postgres, this secondary write path can slow the system down ahead of far more important business operations.

In Z-EDU we solve this with write-behind (deferred writes): the user's request only writes to Redis and returns immediately; a worker aggregates the data and writes it down to Postgres on a cycle. The measured result: 20 pings → 2 DB transactions, where the old path cost roughly 80 queries for those exact 20 pings.

This article does not sell write-behind as a recipe to use everywhere. It is a technique that trades immediate durability for throughput. Apply it to the wrong kind of data and you are not optimizing the system; you are building a mechanism that can lose data silently.

1. The problem: dense writes, very low value per write

The video player sends a progress ping every 15 seconds for every learner watching. That number is a considered one: any sparser and a learner who leaves the page could lose too much of their position; any denser and it burns resources for no reason. But look at it from the angle of write load.

A learner who watches a 25-minute lecture to the end sends about 100 pings. The old path, written in the usual "clean" way, did all of the following for every ping: read the current progress, read the lesson metadata to learn the duration and the completion threshold, update the progress, then update the course progress rollup. Four queries. Multiply by 20 pings and that is about 80 queries. Multiply again by a few hundred learners watching at peak hour, and the number is no longer small.

The crux is that this load does not grow with business value. It grows mainly with the number of learners and the length of the video. You are paying Postgres connection pool, WAL, and vacuum costs for an integer column that users typically only read back when they hit "continue learning."

Before considering any solution, one thing has to happen: classify the data by how much loss it tolerates. This is a precondition, not an optional step.

Write-behind should only be used for the first row of the table above. If you cannot draw that boundary very clearly in the design and in the code, do not use this technique.

2. Architecture: pings hit Redis, the worker batches down to Postgres

The full flow, in words:

Video player (ping every 15s)
      |
      v
POST /api/v1/progress/ping   -- NestJS, verify JWT, check learner permission
      |
      v
Redis:  HSET progress:pending <courseId>:<lessonId>:<userId> {watchedSec, completed, ts}
        SADD progress:dirty   <same key>
      |
      +--> return 204 to the client immediately  (no Postgres involved)
      |
      v
Worker (BullMQ, 5-second cycle)
      |  1. take and remove the "dirty" key set (atomic)
      |  2. read the latest value of each key
      |  3. MERGE into ONE INSERT ... ON CONFLICT DO UPDATE
      v
PostgreSQL  -- 1 transaction for the whole batch

The point to stress: the user's request never touches Postgres. It writes into a hash in Redis and marks that key as "dirty." If the same learner pings 5 times within one 5-second cycle, Redis keeps only the last value; the four earlier pings are absorbed entirely and create no load on the DB. That is where the 20 pings → 2 transactions figure comes from: data is merged by key (many pings for the same lesson) and merged by batch (many learners within the same cycle).

The ping-receiving side of the API, abridged:

// progress.service.ts
const PENDING = 'progress:pending';
const DIRTY = 'progress:dirty';

async ping(userId: string, lessonId: string, dto: PingDto): Promise<void> {
  const key = `${dto.courseId}:${lessonId}:${userId}`;

  const payload = JSON.stringify({
    watchedSec: dto.watchedSec,
    completed: dto.watchedSec >= dto.durationSec * COMPLETE_RATIO,
    ts: Date.now(),
  });

  try {
    await this.redis
      .multi()
      .hset(PENDING, key, payload)   // overwrite: only the latest value survives
      .sadd(DIRTY, key)              // mark as needing a flush to the DB
      .exec();
  } catch (err) {
    // Redis is down -> do not swallow the ping silently, see section 4
    this.logger.warn(`redis down, fallback to direct write: ${err.message}`);
    await this.flushOne(key, payload);
  }
}

And here is the worker. It runs every 5 seconds, takes the dirty key set, and writes it as one batch. The 5-second cycle is a deliberate choice: short enough that the risk window is only a few seconds, long enough that many pings from the same learner fall into the same batch.

// progress.worker.ts — runs every 5 seconds
async flush(): Promise<number> {
  // take & remove the dirty set in one step, so another worker cannot pick up duplicates
  const keys: string[] = await this.redis.spop(DIRTY, BATCH_MAX);
  if (keys.length === 0) return 0;

  const raw = await this.redis.hmget(PENDING, ...keys);
  const rows = keys
    .map((k, i) => raw[i] && this.toRow(k, JSON.parse(raw[i])))
    .filter(Boolean);
  if (rows.length === 0) return 0;

  // ONE INSERT for the whole batch, ONE transaction
  await this.prisma.$executeRaw(this.buildUpsert(rows));

  await this.redis.hdel(PENDING, ...keys);
  return rows.length;
}

Note the order: SPOP first, then the DB write, and only then HDEL. If the worker dies midway, the key has already been popped from the dirty set but the value is still in the pending hash. A cleanup job in the zedu-maintenance queue sweeps for these orphaned keys and returns them to the dirty set. This part is very easy to miss at a glance: write-behind is not just "write to Redis and flush"; it is a small state machine, and every point that can fail needs a path that handles it.

3. Why the merge must be idempotent

This is the easiest part to get wrong, and at the same time the part that decides whether write-behind is safe or dangerous.

In a batching system you do not control ordering. A batch can be retried after a network timeout. Two workers can run in parallel once you scale out to several replicas. An old, slow batch can reach the DB after a newer one. If the merge rule is "the value that arrives later wins" (last write wins), the following scenario will happen sooner or later:

t=0s   batch A: watchedSec = 300   (stuck in the network, hasn't reached the DB)
t=5s   batch B: watchedSec = 340   -> written to the DB successfully. DB = 340
t=6s   batch A arrives (retry)     -> last write wins: DB = 300  ❌ GOES BACKWARD

The learner watches up to minute 5, shuts the machine down, reopens it, and the player jumps back to minute 4. Worse, if the lesson was already marked complete, the old batch can erase that state, dropping course progress from 100% back to 90%. This class of bug usually does not blow up right away. It shows up sporadically, and by the time the first complaint arrives the logs may no longer be enough to trace it.

The fix does not live in the application layer (locks, versions, comparisons in TypeScript — all of them have race conditions of their own). The fix is to push the merge rule down into the SQL itself, so that Postgres guarantees monotonic behavior for you, inside a single transaction:

INSERT INTO "LessonProgress"
  ("userId", "lessonId", "courseId", "watchedSec", "completedAt", "updatedAt")
VALUES
  ($1, $2, $3, $4, $5, NOW()),
  ($6, $7, $8, $9, $10, NOW())
  -- ... the rest of the batch, in the SAME statement
ON CONFLICT ("userId", "lessonId") DO UPDATE SET
  -- progress is ONLY ALLOWED to go up, never down
  "watchedSec" = GREATEST(
      "LessonProgress"."watchedSec",
      EXCLUDED."watchedSec"
  ),
  -- keep the FIRST COMPLETION TIMESTAMP; a late batch does not overwrite the old one,
  -- and cannot erase an existing one either (COALESCE takes the old value first)
  "completedAt" = COALESCE(
      "LessonProgress"."completedAt",
      EXCLUDED."completedAt"
  ),
  "updatedAt" = NOW();

Read the two expressions closely. GREATEST turns the update into a monotonic merge — the value only goes up, it is never pulled back down: no matter what order the batches arrive in, no matter how many times they run again, the final result is always the largest value ever seen. COALESCE does the same for the completion timestamp: once a timestamp exists, no batch can overwrite it.

The consequence is powerful: this statement is idempotent. Running it once or five times with the same data gives the same result. That means retries become entirely safe, and you can run the worker across several replicas in parallel without a single line of distributed locking. All the concurrency complexity is compressed into one SQL statement — the one place in the system that can truly guarantee it.

The rule: in any batching system, the merge must be commutative and monotonic. If you have to ask "which batch arrived first?" the design is already wrong.

This also imposes a limit worth stating plainly: write-behind of this kind only suits data that has a natural merge — max, min, accumulation, set union. For data with no such merge (for example "the latest status of an order"), you cannot write an idempotent expression, and that is a clear signal that this technique is not for it.

4. When Redis dies

The question you must answer before taking write-behind to production: what happens when Redis dies? If the answer is "the system stops accepting pings," then the cache has become a single point of failure.

Our principle is fail-safe according to how important the data is — each layer degrades in a different way, and that mode of degradation is chosen based on how much losing data at that layer costs:

Notice the rate limit row: it fails open, while progress switches to the direct write path. Two opposite choices, and both are right — because the cost of being wrong differs. Missing a few rate limit blocks during a ten-minute incident is acceptable; losing every learner's progress during those ten minutes is not. Do not pick a degradation mode out of habit; pick it by asking "how much does losing this cost?"

The worst case when Redis dies: a few seconds of video position for the pings sitting in the hash that had not been flushed yet. No scores lost, no submissions lost — because they never went down this path.

5. BullMQ queues: not just progress

Once you have the infrastructure to move work off the request path, you will find more parts that can be handled asynchronously. Z-EDU runs two BullMQ queues:

• zedu-maintenance — progress cleanup, periodic jobs, including the orphaned-key sweep mentioned in section 2.
• zedu-audit — the admin action log. Previously each admin action wrote 2 inline queries on the request path, meaning that every time an administrator edited a lesson, that person had to wait for the log write as well. Log writes do not need to be waited on. Push them into a queue, the request returns sooner, and if the log write fails it does not break the business operation.

For deployment, we picked the simplest option: the worker runs in the same process as the API. No separate service, no separate image, no separate pipeline.

// app.module.ts
const WORKER_ENABLED = process.env.WORKER_ENABLED !== '0';

@Module({
  imports: [
    // ...
    ...(WORKER_ENABLED ? [WorkerModule] : []),
  ],
})
export class AppModule {}

But we left an escape hatch in place. The WORKER_ENABLED=0 flag turns the worker off in a given process. When worker load grows to the point that it needs to be split out, all we have to do is: stand up one more service in Swarm using that same image, set WORKER_ENABLED=1 on it and WORKER_ENABLED=0 on the API replicas. Without changing a single line of code.

This is the kind of architectural decision we favor: choose the simple option for today, but leave an environment variable ready so that the more complex option later does not require a rewrite.

6. Observability: the metric you must have

Write-behind has one property to watch out for: when it breaks, the system does not always report an error. The worker dies, pings are still accepted, the API still returns 204, users still watch video normally, there is no 500, no exception in the request log. The data simply piles up in Redis and never reaches Postgres. You will find out hours — or days — later, when a learner asks why their progress was not recorded.

The only way to avoid that situation is to make queue size a first-class metric. Z-EDU's /health endpoint returns pendingProgress — the number of progress records waiting to be written:

// health.controller.ts
@Get('/health')
@Public()
async health() {
  const pendingProgress = await this.redis.hlen('progress:pending');

  return {
    status: 'ok',
    pendingProgress,   // normally ≈ 0
  };
}

How to read this metric:

• ≈ 0 — normal. The worker keeps up with batching; the queue is empty right after each 5-second cycle.
• Bumpy at peak hours, then back to 0 — normal. That is exactly batch merging doing its job.
• Rising and staying there — the worker is dead. The alert has to be built on precisely this property: not "pendingProgress > N", but "pendingProgress > N continuously for several minutes". A sharp spike is normal; a flat plateau is an incident.

Without a queue depth metric, do not deploy write-behind. You are not building a faster system — you are building a system that loses data with nobody the wiser.

Measured results

The last two rows are the price, and we do not want to hide them behind the 80 → 2 figure. You trade immediate durability for throughput, and at the same time trade simplicity for performance. For a video position, that is a reasonable trade. For scores, it is a serious mistake.

7. When NOT to use write-behind

The checklist below helps decide whether to use write-behind. Answering "yes" to any single line means you should stop:

• Losing a few seconds of this data causes real damage. Money, scores, contracts, inventory, submissions. Stop right there.
• Users need to read back the value they just wrote from another path that reads straight from the DB. You have just created an inconsistent read-after-write. Either route the read path through Redis, or drop write-behind.
• The merge cannot be written idempotently. If the only rule you can come up with is "the value that arrives later wins", then you will have data loss on retry. No exceptions.
• You cannot expose and alert on queue depth. See section 6 again.
• The write load is light to begin with. If the DB has not complained at all, what you are adding is just a background process that can die in silence. Measure first, optimize after.
• There is a cheaper way. Before building write-behind, ask: can the ping frequency be reduced? Can it be merged on the client? Can redundant queries be removed from the write path? We took this road because we tried, and a 15-second frequency is the lowest threshold that still preserves the experience.

Conclusion

Write-behind is not a simple speed trick. It is a conscious decision: sacrifice the immediate durability of one specific layer of data, in exchange for the database not having to carry meaningless writes. It is only right when three conditions hold together: the data is allowed to lose a few seconds, the merge can be written as an idempotent expression in SQL, and you have a metric that tells you when it breaks.

At Z-EDU, all three conditions fit: a video position can tolerate losing a few seconds, GREATEST handles the idempotent part, and pendingProgress on /health covers the alerting. In exchange, 20 pings come down to 2 DB transactions instead of roughly 80 queries.

If you plan to apply this technique to your own system, start from the data classification table in section 1, not from the code in section 2. The hard part is deciding which data is allowed to be delayed or lost for a few seconds, not the snippet of code that talks to Redis.