Press "Enter" to skip to content

Category: Internals

Join Operator Properties and Query Performance

Published 2025-08-26 by Kevin Feasel

Andy Brownsword takes a closer look at the big three join operators in SQL Server:

In the previous post I explained Join Operators in SQL Server. Whilst compiling that I dug a little deeper and came across a few interesting points I thought were worth sharing.

Let’s look at behaviour of the operators which may occur under specific conditions. Hopefully you find them as interesting as I did:

Click through for an interesting tidbit about nested loops joins, merge joins, and hash joins.

Leave a Comment

A Primer on Join Operators

Published 2025-08-20 by Kevin Feasel

Andy Brownsword takes a peek at the three most common types of join operators, plus a bonus:

When reviewing our execution plans we’ll see joins executed using different operators. The type of operator is chosen based on the data that’s available to join and how the optimiser wants to execute it.

In this post we’ll take a look at what the operators are, when they are used, and how they work. These are the operators we’ll cover:

  • Nested Loop Joins
  • Merge Joins
  • Hash Match Joins
  • (Bonus) Adaptive Joins

Read on for a quick overview of which works best when.

Leave a Comment

Ordered Insert Optimization in OrioleDB

Published 2025-08-20 by Kevin Feasel

Alexander Korotkov deals with hot page issues:

When many sessions try to insert into the same B-tree leaf page, classic exclusive page locking serializes progress and wastes time on sleep/wake cycles. We’re introducing a batch page insertion path that lets the session holding the page lock insert for itself and its neighbors. The result: dramatically reduced lock waits, and big gains at high client counts (2X throughput boost starting from 64 clients in our benchmark).

Click through to see how it differs from normal PostgreSQL behavior on what is one of the more common performance problems on busy OLTP systems.

Leave a Comment

Diving into Hash Tables

Published 2025-08-15 by Kevin Feasel

Hugo Kornelis dives into the arcane:

But what you probably don’t know is how that hash table is structured. How is the data stored? Where are new rows added, how is the table accessed?

To be fair, none of this is useful knowledge, unless you work for the engine team at Microsoft. And if you do, then you have access to source code and documentation, so you won’t need me to explain this structure to you. So why do I even take the trouble to investigate and describe this structure? Because I am a geek, and geeks love to dig into technical stuff and uncover things they were never meant to uncover.

“Because I can” is a perfectly valid reason to dig into a topic.

Leave a Comment

Kafka: From ZooKeeper to KRaft

Published 2025-08-14 by Kevin Feasel

Phil Yang lays out how to make a migration:

Apache Kafka has made a landmark shift in KIP-500 with the introduction of Kafka Raft (KRaft) mode, eliminating the dependency on Apache ZooKeeper for metadata management. With KRaft, the Kafka nodes themselves can be configured as KRaft controllers – which allow for metadata management and leader elections to work all within just Kafka, resulting in significant performance improvements. This cemented KRaft’s status as the metadata management protocol for Kafka moving forward.

This blog will guide you through the importance of this transition, what migrating from ZooKeeper to KRaft entails, and how we, at NetApp Instaclustr, make this seamless with our automated, streamlined process that is built into our platform.

Click through to see how you can update your own clusters, whether you’re using the Instaclustr service or not.

Leave a Comment

Performance Optimizing PostgreSQL for RTABench Q0

Published 2025-08-05 by Kevin Feasel

Andrei Lepikhov gets under the hood:

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.

Comments closed

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.”

Comments closed

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.

Comments closed

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.

Comments closed

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