Tag: databases

There was a Slack thread at Redgate recently where a developer was showing some code where they decided to use the “extra” column from the information_schema.columns view. They were making decisions on how to detect certain metadata about a column based on the data in this column. Apparently, the data in here is overloaded for different options that might be set on a table.

This caught my eye because I had no idea there was a column named “extra” in this view. I flipped over to SSMS and decided to check what was being stored in here. To my surprise, there was no “extra” column. As I dug in a little deeper in the thread, I realized the developer was talking about Information_schema.columns in a MySQL database.

That was a surprise to me. While I know different platforms will add features and functionality to their databases, I thought the information_schema views were consistent across platforms. They should give you a set of information you can count on. Apparently, that’s not true. You can count on some things, but not all, which means that these aren’t consistent structures.

Perhaps it doesn’t matter. It seems every product out there will extend the SQL “standard” where they see fit, adding features or functions that suit particular use cases. Commercial vendors do this for profit, and OSS projects likely do this because an individual wants a change. That has resulted in a wide variety of database platforms that meet different needs and solve different problems.

It would be nice if we could write SQL code and be sure it would run on SQL Server, Oracle, Snowflake, PostgreSQL, or any platform. And in many cases, we can. Lots of basic queries are the same. However, what would be the point? I certainly don’t want more people in management wanting to switch from one platform to the other, just because they feel like it. I’d imagine that we’d thrash between platforms every time a senior developer or VP decided a system should run on their favorite platform.

A base standard is good, like a base class in programming. However, they aren’t always as useful as they seem, and extending them to meet needs is better for us all. I don’t need a standard implementation of the SQL language or the information_schema views, it was just a surprise to realize that this actually how the platforms are coded.

Note: If you find this interesting, I had a discussion recently with a few others in a webinar.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

I had never heard of a vector database. I assumed this was a specialist type of database used for a particular problem domain, like a streaming database or graph database. There is a need for specialized platforms in certain situations, but I wasn’t sure what a vector was. The description I saw for a vector database was that they “… are specifically designed to work with the unique characteristics of vector embeddings. They index data in a way that makes it easy to search and retrieve objects according to their numerical values.”

That sounds like any database. However, I saw a few more articles on the hype and then some details about the ways in which this type of database is helpful. Essentially, this is a database designed to store the outputs from various Artificial Intelligence (AI) and Machine Learning (ML) models that examine unstructured data. Things like images, video, audio, and even text are turned into numerical values, or vectors. The vector database is designed to help index and then search these vectors.

What is interesting about the possibilities here is that the entire image, video, or whatever isn’t turned into a single numerical hash of some sort. Instead, the AI/ML process might identify that Steve Jones is in this video. That he is wearing a hat, or that he’s wearing a kilt. If I wanted to search for other videos of Steve Jones, or if this is the type of hat he’s wearing, a vector database can help. It’s much more powerful than simple tags that might be placed on a video because the details of the content are rendered into vectors which can be compared to other vectors. Not for exact matches, but likely ones.

One interesting example in the second link above is that content could be “vectorized” to determine if an apple in the content refers to a fruit or the company that Steve Jobs and Steve Wozniak made famous. Not easy to do with a tag, but more possible with a vector database.

And lots of data. Lots of vectors specifically, whose inventory is growing all the time. As more software is built to analyze unstructured data, and as organizations collect more unstructured data, the need to apply database techniques to this data becomes important.

For those of us working with databases, I’d expect a lot of the mechanics of dealing with a database would still apply. Things like security, backups, and indexing will be needed with vector databases. We’ll get calls about slow performance, missing data, or strange results, and we’ll troubleshoot the system. How we do that specifically might vary, but those are just details we’ll work out.

I like the idea of new databases, which provide more tools, challenges, and opportunities for us as data professionals. I haven’t met anyone using a vector database yet, but I’m looking forward to the day when that happens.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

I saw a customer asking about Exasol recently, which is an in-memory, columnar database. I know nothing about it, and it might work great, but we don’t support it. I didn’t think much of it, as I’m sure the customer has a reason for choosing this platform.

Later, however, I wondered if this was a good idea. Another customer had inquired about Knack, which I had never heard of either. Not only that, it’s an online database that appears to assemble its “tables” from data stored in other systems. Strange, but I’m sure it works well for some companies, especially those without many software developers.

I often find there are developers, or even analysts, that find a new platform that appears to work better for their particular problem. Sometimes they’re excited, sometimes they have some experience in the past, but there seems to be a regular push to add new types of technology to many organizations. Often technology that isn’t substantially different in function from something that already is in place.

Whenever you add a new technology to your organization, you are adding more than the capabilities. You are also adding the support of this system, which means not only training end users, but also training the staff that has to support the software. If you do this too often, you risk having staff that don’t know how to keep things running efficiently. This is why we rarely see companies changing their core database platform. The change is a big disruption.

What’s more, our staff is not consistent across time. People come and go, and finding new people can be hard. Especially those that know all of our technology stacks. I appreciate and would like to see more customers working with new platforms, but I also think this has to be something an org considers carefully. After all, many software companies limit the number of products and versions they support for this very reason.

There’s also the problem of technology becoming end-of-life’d. While we might think database platforms are around forever, some of them have disappeared over time, and even if they exist, support goes away. We might find ourselves with the need to upgrade multiple platforms, each of which requires different knowledge. Our staff might spend a lot of time learning and practicing upgrades for disparate platforms, knowledge that doesn’t transfer to the next upgrade.

I am not advocating for everyone to run SQL Server (or PostgreSQL or MySQL or DB2 or Snowflake, etc.) I do think there are reasons why we might choose to use Synapse or Teradata instead of an Oracle database. However, I think the list of platforms ought to be limited in some way. Just like our list of programming languages should be limited. Having a handle on our domain of skills makes it easier to find, train, grow, and build knowledgeable staff. Adding to the list ought to be done slow and carefully, after some debate, discussion, and voting.

I am glad that there are so many RDBMS platforms, and NOSQL platforms. It’s great to see people building new and improved databases. This work is how we get amazing datastores like Neo4J, Redis, and ElasticSearch. At the same time, I do think caution about adding new platforms is warranted inside of organizations. Reusing the knowledge we have should be the first thought, with the decision to grow based on a true need, not just someone’s desire to play with something new.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.