Skip to content

CONCEPT Cited by 1 source

WAL record granularity

Definition

WAL record granularity is the property that a database's Point-in-Time Recovery target-time cannot be finer than the cadence at which its Write-Ahead Log flushes durable records.

Operationally: if the caller asks for recovery to 22:56:02Z but the nearest durable WAL record is at 22:55:50Z (12 seconds earlier), the recovery snaps backward to 22:55:50Z — because that's the latest timestamp with a known-good durable state. WAL-record granularity is therefore both a floor on precision and a structural guarantee — every PITR target time corresponds to a provably durable database state.

Why snap backward

PITR target times always snap backward, never forward. Reason: any state later than the snap-back timestamp but earlier than the caller's requested time is not yet durable (or, more precisely, not yet a known-durable checkpoint the storage layer can materialise). Snapping forward would produce a state the WAL can't actually reconstruct deterministically.

Structural properties

  • Always backward. Never forward-snap.
  • Bounded by WAL-flush cadence. High-write workloads generate WAL records frequently, shrinking the snap-back window. Low-write workloads may have multi-second or multi-minute gaps between records.
  • Not user-controllable at request time. The caller can't ask for "precisely 22:56:02Z" — only "as close to 22:56:02Z as the WAL cadence allows, snapping back."
  • Configurable at substrate level. Operators can tune WAL commit / flush frequency (at some cost to write throughput) if tight PITR precision is a first-class requirement.

Disclosed datum (Lakebase, 2026-04-30)

Thoughtworks POC explicitly calls this out as "an important caveat for time-sensitive recovery workflows." Verbatim: "Notably, we asked for 22:56:02Z, but Lakebase snapped to 22:55:50Z, 12 seconds earlier, snapping backward to the nearest WAL record."

12 seconds is the demonstrated snap-back on the Backstage POC workload. Higher-write workloads would see smaller windows; idle databases would see larger ones.

Practical implication for recovery workflows

A recovery operator who knows "the bad write landed at time T" must request T − ε for recovery target, where ε is chosen large enough to land on a WAL record strictly before T. On the Lakebase POC workload this ε is on the order of 10-20 seconds. On a high-throughput production workload it would typically be sub-second.

The alternative — requesting T exactly and accepting the snap-back — risks landing after the bad write (if the snap-back lands within the bad write's transaction window). The safe pattern is to request a margin that guarantees the snap-back precedes the bad write.

Contrast with binlog-position granularity

Classical MySQL / binlog-based recovery uses binlog position (file + offset) rather than timestamp. Position- addressing doesn't have the snap-back problem because every position is a valid recovery target. The cost is that position-addressing is much harder for operators — "what binlog position was the bad write at?" requires trawling logs — whereas timestamp-addressing is intuitive at the cost of WAL-cadence granularity.

Modern WAL-timestamp-indexed PITR (Lakebase / Aurora / Neon) trades position precision for time intuitiveness + snap-back.

Seen in

  • sources/2026-04-30-databricks-backstage-with-lakebase — canonical first wiki instance. Thoughtworks POC demonstrates a 12-second snap-back from 22:56:02Z to 22:55:50Z during Lakebase PITR. Flagged as "an important caveat for time-sensitive recovery workflows" — load-bearing for operators who must choose a margin when requesting PITR targets close to known bad-write timestamps.
Last updated · 439 distilled / 1,268 read