Category: Performance Tuning

Vacuum is almost always presented as a pain point, a culprit, and an over exaggerated source of performance problems in PostgreSQL. The MVCC implementation in PostgreSQL is different from Oracle, SQL Server, MySQL or MariaDB, and that implementation introduces two requirements of its own, freezing transaction IDs, which is largely seamless, and clearing dead tuples through the various forms of vacuum. At the same time the PostgreSQL community is far ahead in minimizing the impact of vacuum. Each release has introduced enhancements substantial enough that most users never realize vacuum is something they could tune at all, and the list of vacuum specific improvements is long enough to deserve an article of its own. Among all of those optimizations, one of the most often missed is how to avoid the need for vacuuming in the first place. That is achievable most of the time, and it is not new.

Pavan Deolasee worked on the idea through 2006 and 2007 and authored the concept of the Heap Only Tuple, or HOT. Simon Riggs, Heikki Linnakangas, Tom Lane and many other PostgreSQL core team members and contributors have written a great deal of enhancement around it since. In this article we look at what PostgreSQL HOT updates actually are, how fillfactor decides whether they succeed, and how we identify which tables benefit. We then put it to the test with a HammerDB benchmark using the HammerDB TPROC-C workload against PostgreSQL 18.4, six 60-minute runs across three dataset sizes, and the improvement from correctly applied PostgreSQL HOT updates is substantial.

Read on for an overview of HOT tables and the results of this experiment.

Comments closed

Building Oracle’s Automatic Workload Repository in Postgres

Published 2026-08-05 by Kevin Feasel

Kellyn Gorman simulates some functionality:

Moving from Oracle to PostgreSQL means losing one of Oracle’s most-loved diagnostic tools: the Automatic Workload Repository (AWR). The good news? Most of AWR’s core capabilities – snapshot history, wait event sampling, Top SQL analysis, and buffer cache inspection – have direct, open-source equivalents in PostgreSQL.

This guide translates Oracle AWR concepts into practical PostgreSQL diagnostics using extensions like pg_profile, pg_wait_sampling, and pg_stat_statements – complete with runnable SQL you can apply to your own environment today.

Read on to see how.

Comments closed

Optimized Locking in SQL Server 2025

Published 2026-07-30 by Kevin Feasel

Louis Davidson tries out a new feature:

A feature I have been trying to find time to write about since it arrived is Optimized Locking, which first shipped in SQL Server 2025. This is a feature that changes the way SQL Server locks manages concurrency in a very interesting number of ways. Most of which, as I start writing this blog, I don’t understand. And I probably won’t when I finish.

What I can promise you is an example rich blog where I examine the impact of this feature.

Read on to see what Louis was able to learn.

Comments closed

The Performance Impact of Visual Calculations

Published 2026-07-29 by Kevin Feasel

Marco Russo and Alberto Ferrari dig into the nuance:

The goal of visual calculations is to simplify some reports and calculations, rather than to optimize performance. However, it is common sense that – in some scenarios – visual calculations can bring some benefit from the performance point of view.

The main idea is that a report may precompute some values and then, to further elaborate on them, it may use the content of the virtual table rather than recompute the values multiple times.

Read on to learn how you can adjudge what that visual calculation is doing for you in terms of performance relative to alternatives. But hey, if you’re stuck with Power BI Report Server, you don’t have to worry about visual calculations potentially slowing anything down. Mostly because you don’t get visual calculations.

Comments closed

Incremental Liquid Clustering in Apache Spark

Published 2026-07-28 by Kevin Feasel

Miles Cole lays out a recommendation:

Liquid Clustering was already a better abstraction than static partitioning due to its flexible nature. Fabric Spark Runtime 2.0 fixes the part that had me actively cautioning customers to reconsider blindly adopting it: the cost of maintaining the layout.

In Runtime 1.3 (Delta 3.2) a small append followed by OPTIMIZE would rewrite every file in a partial Z-Cube. In Runtime 2.0, the incremental strategy only touches files that are unclustered, small, or carrying a high density of deletion vectors. That changes Liquid Clustering from an occasional, potentially expensive maintenance operation into something that works beautifully with workloads of any shape and with adjacent layout optimizations. Batch or streaming writes. Auto Compaction and/or Fast Optimize. With the new incremental strategy, Liquid Clustering is highly compatible and highly efficient, and should now take its rightful place as the de facto new data layout strategy.

Read on to learn more about what Liquid Clustering is, how it works, and why you should think about it if you’re using Fabric Spark Runtime 2.0.

Comments closed

Improving Power BI Performance

Published 2026-07-27 by Kevin Feasel

James Serra has updated a guide on Power BI performance optimizations:

This post is a complete replacement for and update to my April 2022 post, Power BI Performance Features. That post focused on several performance features that were new or in preview at the time. Four years later, many of those features have matured, Microsoft Fabric has changed the architecture choices available to us, and Direct Lake has become an important part of the conversation. So rather than keep adding notes to an old feature list, I decided to step back and answer the more useful question: How should you design, diagnose, and improve Power BI performance today?

Here’s the bottom line: Power BI performance is not one feature, one setting, or one capacity size. It is the result of a chain that includes the data source, data preparation, storage mode, semantic model, DAX, report design, security, refresh process, and Fabric capacity. A weakness anywhere in that chain can become the bottleneck, which is why randomly changing DAX or buying more capacity often does not solve the real problem. The best approach is to make the right architectural choices first, measure what is slow, and then fix the layer that is actually causing the delay. For more info, see Optimization guide for Power BI.

Click through for James’s guidance.

Comments closed

Performance Tuning with Microsoft Fabric Warehouse Query Insights

Published 2026-07-21 by Kevin Feasel

Koen Verbeeck troubleshoots query performance:

We are in the process of building a data warehouse in Microsoft Fabric. Since we have good knowledge of T-SQL, we are using Fabric Warehouse as the database engine. We are hitting some performance issues in our ETL and we would like to investigate further, but traditional SQL Server DMVs don’t give the results we are hoping for. How can we troubleshoot performance in the Fabric Warehouse?

Click through to see how.

Comments closed

Dealing with linger.ms in Apache Kafka

Published 2026-07-14 by Kevin Feasel

Jack Vanlightly covers a common performance optimization:

Recently I was curious to see if there was any general performance improvement since Kafka 3.X. So I ran a suite of benchmarks with Dimster against 3.7.2 and 4.3.0. I saw two common patterns:

Those two patterns involved higher latency for the newer version of Kafka, but better scale. Click through to understand what changed between these two versions that had such a big impact.

Comments closed

Share Groups and Sub-Optimal Performance

Published 2026-07-01 by Kevin Feasel

Jack Vanlightly creates a problem:

In this post we’re going to see how share.acquire.mode=record_limit combined with:

fewer consumers than partitions

and various cases of “partition skew”

…can result in subpar performance with share groups.

I stumbled on these issues when running large sets of dimensional tests with Dimster’s explore-limits mode, which finds the highest sustainable throughput while staying within a target end-to-end latency target. There was a specific subset of the tests that explore-limits mode would consistently fail to complete, and they all happened to be with record_limit and a consumer count lower than the partition count. In this test, we’ll understand why Dimster had such a hard time with this combination.

Click through for the details, as well as how to mitigate this sort of scenario.

Comments closed

The Basics of Query Folding with Power BI

Published 2026-07-01 by Kevin Feasel

Andy Brownsword explains one performance improvement technique for Power BI data transformations:

As a database developer, when I started using Power BI, I was concerned about it retrieving reams of data only to perform transformations downstream. The Power Query editor misleads us into thinking the retrieval and transformations are applied sequentially.

Thanks to Query Folding, that’s not usually the case. And that gives us more power to extract performance from the database.

This only works in situations where there’s something downstream to perform that processing, like a relational database. And one of the areas where you can affect performance, either positively or negatively, is in organizing operations such that you have a stretch of foldable operations. That way, all of it can run as one operation in the database.

Comments closed