Press "Enter" to skip to content

Category: Internals

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.

Comments closed

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.

Comments closed

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.

Comments closed

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

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