Category: Internals

Ian Parker introduces us to an extension:

When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level—information that directly affects vacuum efficiency and index-only scans.

Read on to see how this extension works.

Kellyn Gorman continues a series on learning PostgreSQL for Oracle DBAs:

In the previous post, I covered some high-level areas around installation and architecture, but for this post, we’re going to go a little deeper.  For the seasoned Oracle DBA, this should feel like we’re stepping into a familiar landscape with just a few different rules. While both PostgreSQL and Oracle Database are robust, feature-rich systems, their physical architecture and internal mechanics diverge in key areas, especially around storage structures, memory architecture, and background processing.

In this post, we’ll break down these differences so Oracle DBAs can feel more comfortable with the shift when they transition between the two.

Click through to see how the two differ.

Brent Ozar does a bit of spelunking:

The first public preview of SQL Server 2025 dropped yesterday, and here’s what’s new under the hood as compared to today’s SQL Server 2022 CU19. Forgive me – this is quite a lengthy post, and it’s mostly a data dump. There are new stored procedures, extended stored procs, views, feature switches, new columns in existing tables, messages, and more.

I suspect that the contents of the new undocumented sys.dm_feature_switches table alone will get y’all really curious because there are 5,910 lines in there (included in this post) that suggest all kinds of wild things Microsoft is currently working on, but hasn’t announced publicly yet.

Read on for the very long list.

Grant Fritchey explains a mechanism:

Let me start by giving you the short version of what MVCC is, and then the rest of the article explains more details. Basically, PostgreSQL is focused on ensuring, as much as possible, that reads don’t block writes and writes don’t block reads. This is done by always, only, inserting rows (tuples). No updates to an existing row. No actual deletes or updates. Instead, it uses a logical delete mechanism, which we’ll get into. This means that data in motion doesn’t interfere with data at rest, meaning a write doesn’t interfere with a read, therefore, less contention & blocking. There’s a lot to how all that works, so let’s get into it.

Click through for the dive. The pattern for MVCC is interesting, though quite different from pretty much any other implementation of concurrency management in a relational database system.