Press "Enter" to skip to content

Curated SQL Posts

Log Rotation in PostgreSQL

Published 2025-04-25 by Kevin Feasel

Ajay Dwivedi switches out log files:

In my organisation, we have started building PostgreSQL Clusters with Patroni + Consul. In PostgreSQL we enable a few extensions like pg_stat_statements to ensure we don’t miss any performance impacting query.

But this generates too much log in active servers, leading to PostgreSQL log bloating. Thus, it becomes important to ensure log files do not consume beyond an agreed amount of disk space. For this reason, I implemented the following log rotation steps for Postgresql –

  • Ensure to set proper log file name for PostgresSQL logging_collector.
  • Add a logrotate policy on linux system for postgres logs directory.
  • Add a cron job for running logrotate policy more frequently.

Click through to see how.

Comments closed

Two Direct Lakes in Microsoft Fabric

Published 2025-04-25 by Kevin Feasel

Nikola Ilic does a bit of digging:

Before you proceed, in case you don’t know what Direct Lake is, I’ve got you covered in this article, where you can learn and understand various Direct Lake concepts, as well as in which scenarios you might consider implementing Direct Lake semantic models. Now that you know what Direct Lake is, let’s digest the latest news…

A couple of days ago, I was reading the official blog post about the latest enhancement to the Direct Lake storage mode for semantic models in Microsoft Fabric. The official blog post can be found here.

Click through for that announcement and what it means.

Comments closed

Graphs are Not Always Necessary

Published 2025-04-24 by Kevin Feasel

Alex Velez drives home a point:

Our current #SWDchallenge has been on my mind. For those who don’t know, we pose a different monthly suggestion for community members to build their data visualization and presentation skills. It could be a prompt to try a novel graph type, redesign an existing example, or practice a specific technique like chart animation. This month, data storyteller Simon asked us to consider whether data always needs to be communicated in a graph. Simon shares, “When you have just a number or two, writing the numbers themselves can be much more powerful than burying them in a table or graph and potentially losing the impact of the main number you’re looking to share.”

This statement came to mind when I reviewed a chart over the weekend. The chart I’m referring to is a bar graph displaying the weekly weight measurements of my dog, Nemo.

Click through for Alex’s argument. My take on the matter is that the point of visualization is to convey relevant information to your audience. If you can do that with a single word or a single number, you don’t need to go further.

Comments closed

Equal Sign Alignment with Powershell Hash Tables

Published 2025-04-24 by Kevin Feasel

Mike Robbins lays out an argument:

If you ever formatted a hash table in PowerShell, you know how easy it is to focus on function over form. But what if one minor formatting tweak could improve readability, reduce syntax errors, simplify code reviews, and enhance script maintainability? During a recent documentation update, I stumbled on a subtle but powerful practice—aligning the equals signs in hash tables. What began as a style suggestion proved to be a practical improvement that changed how I write PowerShell every day. Here’s why this seemingly minor change deserves a place in your scripting toolbox.

Click through to learn why. This doesn’t apply only to hash tables in Powershell, of course, so you could take this concrete example and extend it to other situations. As an example, this is a very common pattern for managing lengthy configuration files for the same reasons Mike points out. Just as long as your programming language is okay with extra whitespace around the equal sign (or equivalent), you can do this.

Comments closed

Contained Database Users and Creating Logins

Published 2025-04-24 by Kevin Feasel

Rob Sewell does a bit of testing:

A contained user can create a Windows login as its own account, although as it cannot grant connect permissions it is then is unable to connect at all.

So if your vendor application is running as a contained user and during an upgrade it tries to create a login for itself, it will succeed in the creation but then be unable to connect to the SQL Server instance and the upgrade will fail.

Click through for the context and the proof.

Comments closed

How Random are SQL Server Random Functions?

Published 2025-04-24 by Kevin Feasel

Rick Dobson publishes a book of guaranteed random numbers, like 41 and 8:

Microsoft provides T-SQL developers with three functions (rand, newid, and crypt_gen_random) for generating random numbers. Each of these functions is effective at returning random values, but feature sets associated with each function make them best suited to different use cases. This tip’s Solution section presents an overview of common use cases for random numbers in SQL Server along with references to learn more about random numbers in SQL Server. The first substantive provides a short comparative overview of the three functions. The remaining tip sections present and describe T-SQL examples for evaluating the randomness of values returned by SQL Server pseudorandom number generators.

Knowing that SQL Server doesn’t have some nice entropy engine (which does exist, typically as an embedded device that plugs in to a USB port) to pull from, it does a pretty good job of things.

Comments closed

A Primer on Indexing in SQL Server

Published 2025-04-24 by Kevin Feasel

Kevin Hill goes back to basics:

“What Is an Index?”
I get this question a lot, especially from developers and sysadmins who’ve been handed a SQL Server and told, “Keep it running fast.” No pressure.

Given that the percentage of people in this industry who have actually seen a phone book continually decreases, it’s for the best that Kevin didn’t use that worn-out example.

If you’re new to indexing in SQL Server, this is a good way to jump-start your knowledge. Then, you spend a while learning the exceptions to the rules and the foibles in existing systems.

Comments closed

Securing SQL Server Backups to Azure via Managed Identity

Published 2025-04-24 by Kevin Feasel

Arun Sirpal doesn’t want to generate a SAS token:

I do believe most people know about the ability to backup your SQL server databases to URL (from Azure VMs).  If you recall you would use the storage key ( ideally not) or a SAS token, from there you would create a SQL credential that is bound to the SAS token (as a secret). Lots of moving parts especially when it comes to rotation time. So now, if you are on the newer versions of SQL server (SQL Server 2022 Cumulative Update 17 ) start thinking about backups via managed identities.

Click through to learn how and why.

Comments closed

Kafka Consumer Offset Changes with KIP-1094

Published 2025-04-23 by Kevin Feasel

Alieh Saeedi looks at a change in Apache Kafka 4.0.0:

Consumer offsets are at the heart of Apache Kafka®’s robust data handling capabilities, as they determine how data is consumed, reprocessed, or skipped across topics and partitions. In this comprehensive guide, we delve into the intricacies of Kafka offsets, covering everything from the necessity of manual offset control to the nuanced challenges posed by offset management in distributed environments. We further explore the solutions and enhancements introduced by KIP-1094 (available in Kafka 4.0.0), offering a closer look at how it addresses these challenges by enabling more accurate and reliable offset and leader epoch information retrieval.

Click through for an overview of how consumer behavior works, as well as what KIP-1094 does.

Comments closed

Comprehensions in Python

Published 2025-04-23 by Kevin Feasel

I have a new video:

In this video, I show how to use comprehensions in Python to generate lists, dictionaries, and sets. I also run a quick performance test, comparing a list comprehension to an equivalent for loop.

It can take a little bit of time to get used to the syntax, but once you do, comprehensions are quite powerful.

Comments closed