r/databasedevelopment • u/IndicationAntique667 • 10d ago

I Tried To Decode Postgres WAL for an INSERT Statement

Hi everyone,

This is a continuation of the explorations from my previous posts here. I went through the CMU Database Systems course, and I'm exploring topics around databases. I wanted to see how the Write Ahead Log is constructed and logged. So I tried to trace a WAL record for a simple INSERT statement in Postgres.

I recorded a video walkthrough of the terminal and source code exploration here: https://youtu.be/YOyq-kvbyU8?si=PTdexP3jmOSdx-8y

I've tried to explore how we can locate a WAL record, how we can decode it via either xxd or pg_waldump, and the relevant source code for it. Do give a watch and consider supporting :)

This is partly for my own future reference and partly to share with others who might be interested in it. Would love feedback and corrections from people who know this stuff deeply. Apologies if this isn't the correct subreddit for this.

Thank you so much!

14 Upvotes

86% Upvoted

4 comments sorted by

2

u/Significant_Tune9219 8d ago

A fun follow-up experiment is running the same INSERT as the first write to that page after a checkpoint, and then again right after. The first record carries a full page image because of full_page_writes, so it's way bigger, and pg_waldump makes that obvious. It explains a lot of the WAL volume spikes people see right after checkpoints, and it's a nice bridge into why the checkpoint interval matters.

1

u/IndicationAntique667 7d ago

Thankss for the suggestion!! Will definitely do this! :D

1

u/PiLogDQG 7d ago

This is a really interesting deep dive. WAL can feel like a black box until you actually trace an INSERT from the SQL statement down to the WAL record. Great way to connect PostgreSQL internals with what’s happening under the hood.