Internals – Curated SQL

Performance Optimizing PostgreSQL for RTABench Q0

Published 2025-08-05 by Kevin Feasel

I wanted to explore whether Postgres could be improved by thoroughly utilising all available tools, and for this, I chose the RTABench benchmark. RTABench is a relatively recent benchmark that is described as being close to real-world scenarios and highly selective. One of its advantages is that the queries include expressions involving the JSONB type, which can be challenging to process. Additionally, the Postgres results on RTABench have not been awe-inspiring.

Ultimately, I decided to review all of the benchmark queries, and fortunately, there aren’t many, to identify possible optimisations. However, already on the zero query, there were enough nuances that it was worth taking it out into a separate discussion.

Click through for a dive into this particular query and what Andrei did and some of the lessons you can draw from it.

Leave a Comment

Stolen Memory in SQL Server

Published 2025-07-21 by Kevin Feasel

Chad Callihan is overdrawn at the memory bank:

When you specify the max amount of memory that SQL Server can use, you may think that’s all the memory to be used for the buffer pool. That’s not the case. SQL Server will use memory not only for the buffer pool but also in tasks such as sorts, memory grants, and other internal tasks. These tasks can swoop in and “steal” memory from the buffer pool, causing more work for SQL Server.

Click through for some notes on the topic, as well as your reminder that “128 GB for the buffer pool” in Standard Edition doesn’t mean “128 GB total memory usage for SQL Server.”

Leave a Comment

PostgreSQL for the Oracle DBA: Tuples and MVCC

Published 2025-07-18 by Kevin Feasel

Kellyn Gorman continues a series on PostgreSQL for Oracle DBAs:

After our first two weeks of ensuring Grant and I didn’t burn down SQLServerCentral figuring out how Steve Jones has kept the pace he has for so long, (quite an impressive feat, I think we’d both agree!) I’m back to working with my comparisons and building more knowledge in PostgreSQL. What caught my attention this week was the simple concept of a row (or tuple) which might seem universal in relational systems, after all, data is data no matter the platform, right? But under the hood, the way databases store, manage, and control visibility of that data can differ drastically. This is especially true when comparing PostgreSQL with how Oracle manages rows.

Read on to learn more about how these systems work.

Leave a Comment

Fast-Path Search in OrioleDB

Published 2025-07-18 by Kevin Feasel

Alexander Korotkov describes a new feature coming to OrioleDB:

When you optimize the CPU time of a transactional database management system, it comes down to one question: how fast can you read a page without breaking consistency? In this post, we explore how OrioleDB avoids locks, trims memory copies, and — starting with beta12 — even bypasses both copying and tuple deforming altogether for fixed-length types during intra-page search. This means that not only are memory copies skipped, but the overhead of reconstructing tuples is also eliminated. The result: an even faster read path, with no manual tuning required.

Read on to see what’s new and how it works.

Leave a Comment

Looking at PostgreSQL Internals with pg_visibility

Published 2025-07-14 by Kevin Feasel

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.

Comments closed

What’s New in SQL Server 2025 CTP 2.1

Published 2025-07-03 by Kevin Feasel

Brent Ozar looks under the covers:

When CTP 2.0 came out, I blogged the thousands of new feature flags, messages, and system objects in SQL Server 2025 CTP 2.0, and I haven’t had the time to dig more deeply into those yet. In that post, I discussed the new sys.dm_feature_switches table, and I mentioned that it might disappear before release. Indeed it has – last week’s new CTP 2.1 removed access to that table, meaning that was probably our one-time shot to get a glimpse into something cool and undocumented. I would have loved to have been a fly on the wall when folks at Microsoft read my blog post and realized they’d left that table unguarded and publicly visible, hahaha. I love my job.

So now CTP 2.1 is out, and it comes with a few things I think you’re going to be interested in.

Read on to see what Brent has found.

Comments closed

Comparing Oracle and PostgreSQL Physical Architectures

Published 2025-06-16 by Kevin Feasel

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.

Comments closed

Optimizing SQL Server via Indirect Checkpoints

Published 2025-06-09 by Kevin Feasel

Jon Russell covers a quiet feature:

A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to disk, and finally writes those pages to disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with SQL instances that have OLTP databases, use more memory and/or involve sequential scanning of all pages, the performance of the system could be impacted.

With SQL Server 2012, indirect checkpoints were introduced. In this case, the dirty page manager manages the dirty page list and keeps track of all the dirty page modifiers of the database. By default, it runs every 60 seconds and tracks the dirty pages that need to be flushed.

Read on to learn more about why indirect checkpointing exists, the kinds of capabilities it offers, and the extent to which you might want to tweak its settings.

Comments closed

New Objects in SQL Server 2025

Published 2025-05-21 by Kevin Feasel

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.

Comments closed

Data Recovery in SQL Server without a Backup

Published 2025-05-13 by Kevin Feasel

Rodrigo Riberio Gomes digs in:

In more than 10 years of experience, I have dealt with cases where someone has performed incorrect operations on a table, such as updating or deleting wrong rows, in a SQL Server database that does not have full backups available. There are multiple reasons for no full backup: corrupted backups, taking too much time to restore, etc.

In this post, I want to show an alternative for these cases, an ace up one’s sleeve, that you can use to recover data. This method also provides a deep understanding of the internal workings of how your SQL Server stores data. So, in addition to learning how to recover data, you will gain more insights into the internals of SQL.

Read on to see how. Rodrigo also points out some limitations or things that would need to change if you have index compression. I consider this a very neat thing you might need to know but never want to use.

Comments closed

Category: Internals