Deep Dive into Postgres Write-Ahead Logs

35 points by j-cheong 9 months ago | 5 comments
  • ukuina 9 months ago
    > It stores these commands BEFORE any changes are made. Once the change is committed, the record is marked, indicating to the system that the change was already made. If the system fails, it can read the commands sequentially within the logs, starting from the one it left off on.

    What happens if power is lost AFTER the change is made, but BEFORE the record is marked?

    • j-cheong 9 months ago
      Good question. Actually it's not possible for a power loss to occur after a change is made to the database but before the record is marked in the WAL. This is because Postgres ensures that all changes are written to the WAL and flushed to disk before they are applied to the database. This write-ahead mechanism guarantees that even if a power outage occurs immediately after a change is applied, the transaction's record is already safely stored in the WAL.
    • qianli_cs 9 months ago
      Interesting post but the format is broken on mobile devices. It cuts off sentences on the right.
      • tang8330 9 months ago
        Thanks for the flag. We'll get that fixed!
        • macartain 9 months ago
          You and er, Madison, right? This article is riddled with generalisations, oversimplifications and many comments that are vague to the point of being almost misleading - or certainly of limited value given the lack of context. This is the level of technical article I increasingly encounter out there these days... In a way I suppose it's good in that we all have to fall back on reputable sources again rather than be tempted to hit a search engine where we will this sort of traffic-generating, borderline chatgpt, cut'n'paste salad, complete with occasional chirpy "WAL is a gamechanger!" sorts of comments. And a 'deep-dive'? Sheesh. Anyone looking for a reasonably deep dive - and the first encouraging sign you might notice is that it's >1500 words - could do a lot worse than go here...

          https://www.postgresql.org/docs/16/wal.htmlhttps://www.postgresql.org/docs/16/runtime-config-wal.html#G...

      • 9 months ago