Press "Enter" to skip to content

Curated SQL Posts

Notes on Temporal Tables

Published 2024-11-11 by Kevin Feasel

Teo Lachev describes some benefits and properties of temporal tables:

At the same time, temporal tables are somewhat more difficult to work with. For example, you must disable system versioning before you alter the table. Here is the recommended approach for altering the schema by the documentation:

I think the main drawback to using temporal tables in this way is that we can only use system time as the separator, unless you manually load data into the history table. It’d be great to have a user time capability to open up temporal tables to these sort of warehousing scenarios, such as using them for type-2 slowly-changing dimensions.

Leave a Comment

Scenarios Leading to Autovacuum in Postgres

Published 2024-11-11 by Kevin Feasel

Semab Tariq covers autovacuum:

However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space. 

Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven’t been cleaned up, resulting in inefficient storage and reduced performance

To address the issues of dead tuples and table bloat, autovacuum comes into play. It’s an automatic process designed to clean up these dead tuples and maintain optimal database performance.

Read on for a list of scenarios that can trigger autovacuum.

Leave a Comment

Apache Kafka 3.9 Now Available

Published 2024-11-08 by Kevin Feasel

Colin McCabe announces Apache Kafka 3.9:

We are proud to announce the release of Apache Kafka 3.9.0. This is a major release, the final one in the 3.x line. This will also be the final major release to feature the deprecated Apache ZooKeeper® mode. Starting in 4.0 and later, Kafka will always run without ZooKeeper.

That’s a pretty big change, but there are also quite a few other significant changes here to check out.

Leave a Comment

Configuring Azure Database Watcher

Published 2024-11-08 by Kevin Feasel

Rod Edwards configures Azure Database Watcher to watch databases in Azure:

First off, at the time of writing, this is still in Preview, and is only for Azure SQL PaaS offerings, namely Azure SQL DB and SQL Managed Instance, so if you’re out of luck if you’re using SQL on VM. Expect this to be added at some point in future, its number 2 on the published roadmap.

Preview or GA…the long and short of it is that it allows collection of performance AND config data into a central datastore for ALL of your SQL MI and Azure DB estate. With all of the data in one place, then dashboards are connected to here for easier estate-wide visualisations.

Read on for a step-by-step guide on configuring it. But also pay attention to Rod’s note near the end that troubleshooting setup is a pain—there aren’t many useful logs that show exactly why it isn’t working.

Leave a Comment

Reviewing Kusto Query History in Microsoft Fabric

Published 2024-11-08 by Kevin Feasel

Dennes Torres looks over prior commands:

We can consume a Kusto database in Fabric from many different places: Notebooks, semantic models, real time dashboards and more. Kusto register all queries sent by the consumers in the query history.

Sometimes, either for logging purpose or to analyze and fix some bug, we need to identify the queries the database is receiving and executing.

Read on to see what you can do with query history in Kusto.

Leave a Comment

Analyzing Azure Network Security Group Flow Logs

Published 2024-11-08 by Kevin Feasel

Reitse Eskens says the bits must flow:

I had an interesting question lately where I was requested to show all the network traffic within an Azure landing zone. Specifically source and target IP, protocol and port. From the aspect of Zero Trust, it’s important to show both successful and failed connections in your network. To be able to answer this question I had prepared myself by enabling the so-called flow logs on the Network Security Groups (NSG). NSG’s are used to control traffic on the IP and port level between resources. There’s no packet inspection, just a check if IP 1 is allowed to connect to IP 2 on port 3. In this specific case, it also had to do with a migration to Azure Firewall where all the NSG rules had to be validated.

But getting the data is one thing, finding out what is in it is something else. In this blogpost I’ll drag you along the steps I took to get the raw JSON data into a SQL table and analyse the data.

Read on for the process and quite a bit of T-SQL code.

Leave a Comment

Verification of PostgreSQL Checksums with WAL-G

Published 2024-11-08 by Kevin Feasel

Marat Bogatyrev talks checksums with us:

A key aspect of maintaining backup integrity is understanding data checksums. Without proper checksum validation, detecting data corruption becomes virtually impossible. Therefore, we will start with The Importance of Data Checksums.

Read on to learn more about how PostgreSQL, in particular, deals with checksums. SQL Server has similar functionality for pages and backup files and it’s worth knowing about and enabling checksum capabilities in both database platforms.

Leave a Comment

Charting Microsoft Fabric Workspace Activity

Published 2024-11-07 by Kevin Feasel

Sandeep Pawar creates a chart:

Semantic Link Labs v0.8.3 has list_activities method to get the list of all activities in your Fabric tenant. It uses the same Power BI Admin - Get Activity Events API but this API now also includes Fabric activities. Note that this is an Admin API so you need to be a Fabric administrator. Check the API details.

To answer the above question, I will use the admin.list_activity_events , loop over the last 30 days and plot the results by Fabric item type in my personal tenant:

Click through for that code. Though if you’re going to do something similar in your environment, I recommend not using a line chart for this visual, as line charts indicate a flow over time and this is definitely point-in-time categorical data. A bar chart or dot plot would be better in that case.

Leave a Comment