Press "Enter" to skip to content

Author: Kevin Feasel

Spatial Data in MySQL vs PostgreSQL

Aisha Bukar contrasts two data platforms:

MySQL and PostgreSQL both store spatial and geometric data, but they take fundamentally different approaches. While MySQL bakes spatial support directly into its engine, PostgreSQL separates two ecosystems: native geometric types (built-in, flat-plane, non-geographic), and PostGIS (a full GIS extension with coordinate systems, projections, and hundreds of spatial functions).

For most real-world location-aware applications — GPS tracking, geofencing, distance queries — PostGIS is the more powerful option, though MySQL 8.0 closed the gap considerably for common use cases. This guide compares their architectures, data types, indexing strategies, spatial functions, and what to watch out for if you’re migrating between them.

Click through for the comparison.

Leave a Comment

Issues with INSERT-EXEC

Erik Darling has two problems:

All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…

why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.

Click through to learn more about both problems, including windows of time when SQL Server gets blackout drunk and can’t account for its time.

Leave a Comment

Implementing the Gauss Kronod Quadrature Formula in SQL Server

Sebastiao Pereira implements a formula:

Gauss-Kronrod quadrature is a numerical integration method that extends the Gaussian quadrature providing high accuracy and a built-in error estimation. It is based on the work of Carl Friedrich Gauss and Alexander Kronrod. The Kronrod method reuses all Gauss nodes adding extra points obtaining one integral estimate for Gauss and another for Kronrod and the differences gives the estimated error.

Click through to see how you can do this in T-SQL.

Leave a Comment

Datatypes and Constraints in Microsoft Fabric Data Warehouses

Louis Davidson starts digging in:

There are three major “engines” in Fabric that I have seen that use T-SQL. There is the SQL Azure Fabric engine, the Lakehouse, and the Data Warehouse. Having this capability to manipulate data is wonderful, but there are some things you need to understand before you start writing code (unless you want to learn them the hard way like I have. I also will not profess to have found all of these differences. Changing my mindset when using these engines was the subject of this editorial

Read on for some inconsistency.

Leave a Comment

Simplifying Code via Computed Columns

Andy Brownsword moves an expression:

Computed columns allow us to bake logic into our schema. I find particular use for these when reviewing or optimising established solutions where you spot the quirky ways the data is being used.

Here I want to demonstrate 3 symptoms and show how different implementations of computed columns solve them.

Click through for the technique. It certainly won’t solve every performance problem relating to non-SARGable functions, but it does work pretty well on specific classes where you have limited control over the code.

Leave a Comment

Working with Blob Leases

Miles Cole explains how leasing works with Blob Storage and Data Lake Storage blobs:

Leased a blob? No, this isn’t some new car model with a great leasing offer. Blobs are files in ADLS / OneLake, and leasing is the process of temporarily holding an exclusive lock on one of those files. Does this sound useful in data engineering? Maybe not at first, but in this post I’ll show why understanding the extended features of object storage APIs like blob leases can be invaluable when building data systems that scale.

I’ve never actually done this, but it’s a smart way of preventing multiple job agents from picking up the same work.

Leave a Comment

A Comparison of R GUIs

Bob Muenchen puts together a comparison:

Graphical user interfaces for the R language are easy to use and getting more powerful all the time. Here is my updated comparison of jamovi, JASP, BlueSky Statistics (free & Pro), Rattle, RKWard, R-Instat, R AnalyticFlow, and R Commander.

With so many detailed reviews of Graphical User Interfaces (GUIs) for R available, which should you choose? It’s not too difficult to rate them based on the number of features they offer, so I’ll start there. Then, I’ll follow with a brief overview of each.

Click through for the criteria and results. Bob also has a link to the dataset for your own comparisons. H/T R-Bloggers.

Leave a Comment

Conceptualizing the Agent2Agent Protocol

Paul Brebner continues a series on Apache Kafka and the Agent2Agent Protocol. Part 3 explains the details of the protocol:

In Part 2, we discovered that A2A’s object model centres on the “nouns” Agent Card, Task, Message, Part, and Artifact. A client sends messages; the remote agent responds with an immediate Message or a stateful Task. Artifacts — the durable outputs — live on the Task, not as a separate top-level response type.

This post covers the “verbs”: how agents find each other, how work flows at runtime, and the confusions that surfaced when I first read the specification (but are hopefully clarified by the end of this blog). These runtime patterns allow agents to discover each other, delegate work, track long-running operations, and exchange results across distributed systems. (Note: Part 4 will add sequence and state diagrams plus concrete request/response traces.)

By the end of this post, you’ll understand the core runtime flow behind the A2A protocol and how it supports scalable agent communication architectures that can be combined with technologies such as Apache Kafka.

Part 4 visualizes the different components:

In Parts 1-3, we treated the topic in prose: why multi-agent interoperability matters (Part 1), the core A2A objects (Part 2), and the operational patterns in (Part 3). Useful, but when I turned to implementation, I kept wanting sketches on the table: where modules sit, how objects connect, what the wire sequence looks like, which task states are legal.

The diagrams that follow are that layer. They provide a visual guide to the Agent2Agent protocol and help translate the specification into something easier to design, implement, test, and reason about.

Leave a Comment

How OR Predicates Affect Indexes

Dualcore DBA adds a clause:

We’ve created an index on both of the columns in the WHERE clause both of which are also in the SELECT list. As a reminder, non-clustered indexes implicitly include the clustered index key in the included columns even if we have not explicitly specified it and so with this in mind, our index fully covers our query. This index should be good for an index seek right? Let’s execute our query again:

This solution isn’t the only way to SQL Server to use a specific pair of indexes—you can also use the UNION operator to replace one OR, for example. And that usually resolves the issue without needing index hints.

Leave a Comment

PostgreSQL HOT Tables and TPC-C Workloads

Avinash Vallarapu tunes a PostgreSQL implementation of the TPC-C workload:

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.

Leave a Comment