Category: Internals

Radim Marek takes us through the structure of a page:

If you read previous post about buffers, you already know PostgreSQL might not necessarily care about your rows. You might be inserting a user profile, or retrieving payment details, but all that Postgres works with are blocks of data. 8KB blocks, to be precise. You want to retrieve one tiny row? PostgreSQL hauls an entire 8,192-byte page off the disk just to give it to you. You update a single boolean flag? Same thing. The 8KB page is THE atomic unit of I/O.

But knowing those pages exist isn’t enough. To understand why the database behaves the way it does, you need to understand how it works. Every time you execute INSERT, PostgreSQL needs to figure out how to fit it into one of those 8,192-byte pages.

It is a little wild how three of the largest relational database systems use 8KB pages. I know that, on the SQL Server side, they’ve experimented with different page sizes internally and have repeatedly said that, even recently (the last time I heard this was maybe about 3 years ago at a SQL Saturday), there just isn’t a benefit from moving away from 8KB. But what’s in those 8KB differ, and Radim goes into details on what’s in PostgreSQL.

Paul Randal re-continues a series:

In the first part of this series I introduced basic terminology around logging so I recommend you read that before continuing with this post. Everything else I’ll cover in the series requires knowing some of the architecture of the transaction log, so that’s what I’m going to discuss this time. Even if you’re not going to follow the series, some of the concepts I’m going to explain below are worth knowing for everyday tasks that DBAs handle in production.

Note: as I progress through this series and talk about aspects of the log, there are often little edge-cases or weird behaviors in niche circumstances that have been added or changed over the years. I’ll ignore those unless I specifically want to call them out, otherwise the posts would be riddled with rat-holes and mazes of twisty-turny little passages (yes, I loved ’80s text-based adventure games 🙂 that would distract from the main things to learn about in each post.

Click through for a primer on virtual log files, log blocks, and log sequence numbers.

Kellyn Gorman brings in the usual suspects:

“How does the database identify this query and its execution plan?”

Both Oracle and PostgreSQL answer this question, but I find they do it in very different ways, reflecting fundamentally different design philosophies around optimization, observability, and stability.  As I dive into this rabbit hole once again, I’m going to reflect on how Oracle’s SQL_ID differs from the query_id in PostgreSQL and how two terms that sound so similar (PLAN_HASH_VALUE and query_hash) could be generated so differently, as well as misinterpreted.  I’m guilty of it myself, so it’s a good place to spend some time.

Read on for the answer.

Radim Marek goes deep into buffers:

The work around RegreSQL led me to focus a lot on buffers. If you are a casual PostgreSQL user, you have probably heard about adjusting shared_buffers and followed the good old advice to set it to 1/4 of available RAM. But after we went a little bit too enthusiastic about them on a recent Postgres FM episode I’ve been asked what that’s all about.

Buffers are one of those topics that easily gets forgotten. And while they are a foundation block of PostgreSQL’s performance architecture, most of us treat them as a black box. This article is going to attempt to change that.

Read on to learn more about how PostgreSQL users buffers.

Hugo Kornelis digs in when you’re overdrawn at the memory bank:

Finding data in tempdb is hard. Not when the data is in objects we created ourselves, such as temporary tables or variables. They are stored in the internal system tables and reflected in various system dynamic management views. But that changes for internal objects. They are only used by the internal logic of, in this case, the Hash Match operator. There is no advantage to storing them in the system tables. When I explored the internals of tables used by Table Spool, Index Spool, and Window Spool, I found out that Microsoft has indeed not bothered to put anything in the system tables for such internal objects. The Hash Match operator is not different in this regard.

But I still found a way to locate this information.

Hugo explains how, though it does include some contrivances to make life a lot easier. I always love this sort of spelunking deep into the bowels of how things work, and Hugo is definitely on the top shelf when it comes to this kind of work.