Press "Enter" to skip to content

Category: Internals

Kafka Internals: Handling a Producer Request

Published 2024-09-26 by Kevin Feasel

Danica Fine continues a series on Kafka internals:

Welcome to the second installment of our blog series to understand the inner workings of the beautiful black box that is Apache Kafka®. 

We’re diving headfirst into Kafka to see how we actually interact with the cluster through producers and consumers. Along the way, we explore the configurations that affect each step of this epic journey and the metrics that we can use to more effectively monitor the process. 

In the last blog, we explored what the Kafka producer client does behind the scenes each time we call producer.send() (or similar, depending on your language of choice). In this post, we follow our brave hero, a well-formed produce request, that’s on its way to the broker to be processed and have its data stored on the cluster.

Click through to learn more about how it all works.

Comments closed

Self-Joins and Halloween Protection

Published 2024-09-19 by Kevin Feasel

Paul White has an explanation:

I was asked recently why Halloween Protection was needed for data modification statements that include a self-join of the target table. This gives me a chance to explain, while also covering some interesting product bug history from the SQL Server 7 and 2000 days.

Read on for that explanation.

Paul’s explanation of the bug reminded me of the “quirky update” approach to building a running total, except that, instead of fixing a bug that eliminated it, the process always remained on a knife’s edge of “unsupported but works…at least until we change something and it doesn’t work anymore.”

Comments closed

The State of the ANY Aggregate Transformation

Published 2024-09-16 by Kevin Feasel

Paul White covers an aggregate operator:

SQL Server provides a way to select any one row from a group of rows, provided you write the statement using a specific syntax. This method returns any one row from each group, not the minimum, maximum or anything else. In principle, the one row chosen from each group is unpredictable.

The general idea of the required syntax is to logically number rows starting with 1 in each group in no particular order, then return only the rows numbered 1. The outer statement must not select the numbering column for this query optimizer transformation (SelSeqPrjToAnyAgg) to work.

Read on for information about this internal operator, a bug that existed in it for a long time, and the current state of fixes.

Comments closed

Vacuum, MERGE, and ON CONFLICT Directives

Published 2024-09-06 by Kevin Feasel

Shane Borden looks back at a directive:

I previously blogged about ensuring that the “ON CONFLICT” directive is used in order to avoid vacuum from having to do additional work. You can read the original blog here: Reduce Vacuum by Using “ON CONFLICT” Directive

Now that Postgres has incorporated the “MERGE” functionality into Postgres 15 and above, I wanted to ensure that there was no “strange” behavior as it relates to vacuum when using merge.

Read on to see how things look now.

Comments closed

Search in a Vector Database

Published 2024-09-05 by Kevin Feasel

Brendan Tierney continues a series on vector databases:

Searching semantic similarity in a data set is now equivalent to searching for nearest neighbors in a vector space instead of using traditional keyword searches using query predicates. The distance between “dog” and “wolf” in this vector space is shorter than the distance between “dog” and “kitten”. A “dog” is more similar to a “wolf” than it is to a “kitten”.

Click through to learn more about some of the common techniques for performing similarity searches against vectorized data.

Comments closed

Performance Profile of Fast-Forward Cursors

Published 2024-09-03 by Kevin Feasel

Hugo Kornelis continues a deep dive into cursors:

One of the things that has always bothered me about the fast forward cursor type is the shocking lack of documentation of what it does exactly. Okay, the name suggests that it is fast. But is it really? When I first looked at cursor performance (granted, a long time ago), I found that a static cursor was actually faster than a fast forward cursor for the same query. So… fastish forward?

The name also suggests that this cursor is forward only. That is indeed the case. At least they got that right in the naming.

Read on to learn more about this cursor property, as well as how it compares to static and dynamic cursors. Hugo ends on a spicy take you won’t want to miss.

Comments closed

Write-Ahead Logging in PostgreSQL

Published 2024-08-21 by Kevin Feasel

Semab Tariq continues a series on internals by covering write-ahead logging:

Write-Ahead Logging (WAL) is a critical feature in PostgreSQL that ensures data integrity and durability. It records changes to the database before they are applied, allowing PostgreSQL to recover data and restore the database to its most recent state in case of a crash or failure.

Before the introduction of Write-Ahead Logging (WAL) in PostgreSQL, PostgreSQL relied on a simpler mechanism for ensuring data integrity, which was less robust and did not support advanced features like point-in-time recovery and replication.

Read on for a bit of history, how write-ahead logging has changed over the years, and the basics of how it works. Write-ahead logging is a common feature in major relational databases and serves a critical purpose for database consistency. There are also some sneaky performance gains you can get out of it.

Comments closed

Query Re-Optimization in Postgres

Published 2024-08-20 by Kevin Feasel

Andrei Lepikhov walks through an interesting scenario:

What was the impetus to begin this work? It was caused by many real cases that may be demonstrated clearly by the Join Order Benchmark. How much performance do you think Postgres loses if you change its preference of employing parallel workers from one to zero? Two times regression? What about 10 or 100 times slower?

The black line in the graph below shows the change in execution time of each query between two cases: with parallel workers disabled and with a single parallel worker per gather allowed. For details, see the test script and EXPLAINs, with and without parallel workers.

Click through for an overview of what Andrei wrote, including architectural notes. But stick around until the end to see just how difficult the challenge is to re-optimize without making performance worse in the end.

Comments closed

Dynamic Cursors in SQL Server

Published 2024-06-04 by Kevin Feasel

Hugo Kornelis continues a series on cursors:

We’re already at part 31 of the plansplaining series. And this is also the third part in my discussion of execution plans for cursors. After explaining the basics, and after diving into static cursors, it is now time to investigate dynamic cursors. As a quick reminder, recall that a static cursor presents data as it was when the cursor was opened (and does so by simply saving a snapshot of that data in tempdb), whereas a dynamic cursor is supposed to see all changes that are committed while the cursor is open. Let’s see how this change in semantics affects the execution plan.

Read on as Hugo gives it the college try and also admits he might be missing something in the explanation.

Comments closed

Impossible Execution Plan Timings

Published 2024-06-03 by Kevin Feasel

Paul White puts up an article:

I showed a hidden option to make all operators report only their individual times in

More Consistent Execution Plan Timings in SQL Server 2022

. That feature isn’t complete yet, so the results aren’t perfect, and it’s not documented or supported.

I mention all that in case you are interested in the background. None of the foregoing explains what we see in this mixed mode plan. The row mode Gather Streams elapsed time ought to include its children. The batch mode Sort should just be reporting its own elapsed time.

With that understanding in mind, there’s no way the Sort could run for longer than the Gather Streams. What’s going on here?

Read on for a Paul White-level discussion of the topic, including a demo from Erik Darling.

Comments closed