Press "Enter" to skip to content

Author: Kevin Feasel

Tracking Record Changes in SQL

Published 2026-05-13 by Kevin Feasel

Andy Brownsword builds a hash key:

The issue: there was no indicator of which records had been modified and as a result the process took way too long, and downstream reporting wasn’t available on time.

After reviewing and stepping through the process it became clear that the vast majority of data didn’t change. This was a daily process handling 12 months of data, yet over 99% had no changes at all. However the process ingested the whole dataset (~250m records) and processed it in SQL.

Click through for an architectural-level discussion. In practice, HASHBYTES() works really well, especially when you use CONCAT() or CONCAT_WS() to put together the columns you care about

Comments closed

Generated Columns in JSONB in PostgreSQL

Published 2026-05-13 by Kevin Feasel

Richard Yen works with JSONB:

Over the past year, I’ve worked in a handful of contexts managing large volumes of data stored as JSONB in PostgreSQL. The scenario is common: users appreciate the flexibility of a document-oriented storage model, avoiding the need to predefine schemas or constantly migrate table structures as their data requirements evolve. JSONB documents can be deeply nested with numerous optional fields, and they scale to hundreds of kilobytes per record without issue. However, when the time comes to query these documents – filtering by user ID, event type, timestamps, or nested action properties – the queries can become slow and/or cumbersome to work with.

The problem I want to address is: “How do we make searching JSONB data more efficient without breaking apart our documents or forcing it into columns in a relational database?” There are several approaches available in Postgres, each with different tradeoffs. I hope to shed some light on those approaches in this article.

Click through for the demonstration, as well as various trade-offs and caveats.

Comments closed

Tips for Reading an Explain Plan

Published 2026-05-13 by Kevin Feasel

Jamal Hansen has a primer on explain plans for Python developers:

We talked early in this series about SQL being a declarative language. You tell the database what you want, and it figures out how to get it. But we’ve also seen that SQL gives you the freedom to do things in many ways, and some of those ways are more efficient than others.

Sometimes, a slow query means you didn’t choose the most efficient approach. Other times, your data has simply outgrown the default way the database finds records, and you need to give it a little help.

And in a judo move, if you already understand how explain plans work, you can figure out how to perform code profiling in Python.

Comments closed

Building Mermaid Diagrams for Jekyll

Published 2026-05-12 by Kevin Feasel

Thomas Williams builds a visual:

Mermaid https://mermaid.ai/ is “Markdown inspired” diagrams as code. With the diagram defined as text, special/proprietary software isn’t needed to create or edit diagrams. The text defining the diagrams can be edited by anyone – so can be kept up-to-date. Diagrams can also be version controlled, like any other code.

This matters because better diagrams improve documentation, and communication.

I’ve used Mermaid a bit. It’s fairly powerful, though can be tricky if you’re used to a more free-flow diagram design.

Comments closed

PlanTrace Support for PostgreSQL

Published 2026-05-12 by Kevin Feasel

Eduardo Pivaral has updated a website:

When I built PlanTrace, it was a Redshift-only tool. Paste your EXPLAIN output, get an interactive graph, cost heatmap, and tuning insights — all client-side, nothing stored. It worked well, but Redshift is only part of the picture for most teams.

PostgreSQL is where most development happens, where staging environments live, and where a lot of production workloads run. So I extended PlanTrace to support it — same interface, same zero-friction flow, automatic engine detection.

Click through to see how it works.

Comments closed

Writing SQL-Only Extensions for Postgres

Published 2026-05-12 by Kevin Feasel

Shaun Thomas writes an extension:

Recently at Postgres Conference 2026 in San Jose, I presented a talk called Let’s Build a Postgres Extension! Since that entire presentation was primarily focused on writing a C extension while exploring the Postgres source code, I only mentioned pure SQL extensions as an aside. But what’s more likely in the Postgres community in general: C devs, or people who know SQL?

It turns out that you can do a lot with functions, triggers, views, tables, and various other Postgres-native capabilities. The extension system doesn’t care whether the contents are compiled C or plain SQL. It just wants a control file, a SQL script, and an optional Makefile to help with installation.

So let’s build a relatively trivial extension article entirely in SQL.

It’s a fairly neat capability, for sure.

Comments closed

5 Reasons Analytics Projects Fail

Published 2026-05-12 by Kevin Feasel

Reza Rad has a new video:

Have you ever wondered why analytics projects fail? There are hundreds of organizations, thousands of BI teams, and countless consulting companies building analytics solutions every year. Yet a large number of those projects never deliver what they promised.

Why does that keep happening?

Read on for a summary of Reza’s decades of experience.

Comments closed

Working with Variables in Powershell

Published 2026-05-12 by Kevin Feasel

Garry Bargsley is a wild card:

Last week, we learned that quotes are not created equal. This week, we’re going deeper into the building blocks that make PowerShell scripts actually useful: variables.

If quotes are your lightsaber, variables are the Force itself. They carry information from one part of your script to another. They make the difference between a script that works on one server and a script that works on all of them. Get comfortable with variables, and you’ll look back at your pre-PowerShell DBA life the way Luke looked back at Tatooine – relieved to be moving on.

I can tell when Garry wrote this one.

Comments closed

The Pain of Views in PostgreSQL

Published 2026-05-12 by Kevin Feasel

Radim Marik digs into views:

VIEWs should be the cleanest abstraction SQL, and therefore Postgres, has on offer. I love the concept. The promise of decoupling logical intent from physical storage is perfect on paper. In practice, few things in the database world trigger such a heated debate or carry as much historical baggage. VIEWs mix big promises with false hopes, and the promises rarely survive contact with production.

The appeal is straightforward. Abstract “active customer” once and reuse it everywhere. Every query, report and dashboard uses the same definition. The “active customer” then becomes the foundation of a “customer orders” view, which in turn powers an operational “customer summary” view.

Some of these pain points are PostgreSQL-specific, but others (especially around the performance of nested views) resonates with SQL Server as well.

Comments closed

Optimized Locking in SQL Server 2025

Published 2026-05-11 by Kevin Feasel

John Deardurff enables a feature:

Modern SQL Server workloads demand high concurrency without sacrificing consistency. Traditionally, we relied on locking to enforce correctness, but that came with blocking and performance challenges. To resolve some of these issues, row versioning was used to reduce some of that contention overhead. But now with optimized locking in SQL Server 2025, we have a more efficient and scalable concurrency model.

Click through for a demo. My point of curiosity is, what’s the impact in a practical but busy environment? I don’t have one of those running SQL Server 2025, so I do wonder when we’ll get the tell-all post from someone in a very busy environment who has it on. (Or, if we already have and I’ve missed it, please do let me know!)

2 Comments