4 ms·

So it's basically double buffering, but for databases? That makes sense.

by runiq 3y ago

jbverschoor 3y ago
Tripple
- runiq 3y ago
  Hmm, I don't think I follow—what is the third buffer?
  - masklinn 3y ago
    Database actual? The buffering analogy doesn’t really work tho, because all three sources (db file, wal being flushed, and wal being written to) are read sources.
  - stavros 3y ago
    The main database file, the WAL, and the second WAL. Though I do agree with you that I also think about this as double-buffering, as I think of the database file as the screen.
gregw2 3y ago
In a high-level sense, yes! But it kind of depends on which part of the design pattern you mean by "double buffering for databases". With double-buffering (2d/3d graphics) you are literally writing the final pixel-level data to the back buffer.[1] In a database WAL scenario, to further analogize, it's more like you are writing the 2d/3d graphics commands to the buffer and executing them later. Because that is part of the point of the WAL -- it results in reduced disk writes because only the log file needs to be flushed to disk to guarantee a transaction is committed, rather than every data file/byte(/pixel) changed by the transaction.[2][3] (The WAL content is loosely a bit more like 3D (or 2D) vertex buffer objects/display lists [4] if you are familiar with those.) Swapping the two WAL files though and alternating writing to each is yes like double buffering. A third similar design pattern (to WALs) is used in operating systems' journaling filesystems[5] and actually was a contribution from OSes adopting database WAL techniques back in the 1990s. Apologies if you know all this. [1] https://en.wikipedia.org/wiki/Multiple_buffering#Double_buffering_in_computer_graphics https://en.wikipedia.org/wiki/Multiple_buffering#Double_buff... [2] https://www.postgresql.org/docs/15/wal-intro.html https://www.postgresql.org/docs/15/wal-intro.html [3] https://en.wikipedia.org/wiki/Write-ahead_logging https://en.wikipedia.org/wiki/Write-ahead_logging [4] https://en.wikipedia.org/wiki/Display_list https://en.wikipedia.org/wiki/Display_list [5] https://en.wikipedia.org/wiki/Journaling_file_system https://en.wikipedia.org/wiki/Journaling_file_system
- runiq 3y ago
  Thanks a lot for this extensively sourced post, I learned a lot today! :)

So it's basically double buffering, but for databases? That makes sense.

by runiq 3y ago

So it's basically double buffering, but for databases? That makes sense.

jbverschoor 3y ago

Tripple

runiq 3y ago
Hmm, I don't think I follow—what is the third buffer?
- masklinn 3y ago
  Database actual? The buffering analogy doesn’t really work tho, because all three sources (db file, wal being flushed, and wal being written to) are read sources.
- stavros 3y ago
  The main database file, the WAL, and the second WAL. Though I do agree with you that I also think about this as double-buffering, as I think of the database file as the screen.

gregw2 3y ago

In a high-level sense, yes! But it kind of depends on which part of the design pattern you mean by "double buffering for databases". With double-buffering (2d/3d graphics) you are literally writing the final pixel-level data to the back buffer.[1] In a database WAL scenario, to further analogize, it's more like you are writing the 2d/3d graphics commands to the buffer and executing them later. Because that is part of the point of the WAL -- it results in reduced disk writes because only the log file needs to be flushed to disk to guarantee a transaction is committed, rather than every data file/byte(/pixel) changed by the transaction.[2][3] (The WAL content is loosely a bit more like 3D (or 2D) vertex buffer objects/display lists [4] if you are familiar with those.) Swapping the two WAL files though and alternating writing to each is yes like double buffering. A third similar design pattern (to WALs) is used in operating systems' journaling filesystems[5] and actually was a contribution from OSes adopting database WAL techniques back in the 1990s. Apologies if you know all this. [1] https://en.wikipedia.org/wiki/Multiple_buffering#Double_buffering_in_computer_graphics https://en.wikipedia.org/wiki/Multiple_buffering#Double_buff... [2] https://www.postgresql.org/docs/15/wal-intro.html https://www.postgresql.org/docs/15/wal-intro.html [3] https://en.wikipedia.org/wiki/Write-ahead_logging https://en.wikipedia.org/wiki/Write-ahead_logging [4] https://en.wikipedia.org/wiki/Display_list https://en.wikipedia.org/wiki/Display_list [5] https://en.wikipedia.org/wiki/Journaling_file_system https://en.wikipedia.org/wiki/Journaling_file_system

runiq 3y ago
Thanks a lot for this extensively sourced post, I learned a lot today! :)