Skip to content

Search the site


It’s time for enterprise SQL systems to take their turn in the AI sandbox...

"The addition of pgvector for the SQL database specialist is also good news for users in sectors like financial services" says technology veteran David Walker.

CIOs are all too familiar with the excitement around Generative AI (Artificial Intelligence) and the time and cost savings it promises. 

They’re also aware of the practical challenges that need to be overcome when developing business apps based on AI. A notable concern is the tendency of ChatGPT-style Large Language Models to confidently tell us things that just aren’t true! There’s also the issue that public versions of GenAI tend to be extremely sloooow. 

However, if we could get AI to assist with actual, business-critical, database applications, the enterprise IT team would be more than interested. 

The reality is that if you want to use your own data, or if you want low latency, you need a way to search your own database based on a user prompt. So far, that hasn’t been that easy to do, even using scalable, open source cloud and on-prem databases like PostgreSQL

Don’t get me wrong--Postgres users were building amazing AI-powered applications well before the ChatGPT hype started. 

For example, my company works with a major AI-powered procurement analytics platform that helps buyers select the very best set supply chain partners and prices. The cloud-native transactional database, YugabyteDB, plays a major role at the back end, based on an understanding and analysis of the marketplace and past transactions. The same database is an integral part of the solution from an anti-money laundering (AML) leader. This solution uses machine learning algorithms over a combination of public data, and pooled, anonymised, private customer data, to make real-time judgements about potentially risky transactions. 

So, it is possible to use a database to power a large, industrial-use, non-ChatGPT level AI system. However, a big drawback on database-oriented AI progress has been the lack of support for complex search. We’re talking about a data structure that’s a bit far from the traditional SQL schema. 

Here, I have to add that AI people want to work with mathematical representations of information that can associate lots of features or attributes. These are called ‘vectors’. 

A vector has many dimensions, so can group together a lot of detail about an item. That means that instead of having to store all that connected data as separate rows and tables, the developer can use a vector to store hundreds or even thousands of numbers as one record. Vectors also make searching for similarities a lot easier, as a vector is a way of storing information about some ‘thing’ in the world that you can describe with characteristics that can be quantified. That could be a person, a set of transactions, an image, or even a customer journey. Artificial Intelligence allows us to run queries and perform searches and analytics over these ‘things’. 

Vector processing--storing and searching vectors--is therefore a critical requirement for database-leveraging AI apps. That’s because if you want to find something new (which is often what we want AI for) we need to start with all the data items that are close to the answer. Basically, something close to this, or even the same (“find me images, documents, or people like this”). 

A vector database allows you to quickly find the most similar or relevant data based on its semantic or contextual meaning. And there are specialist vector databases available. So, use them if they make sense for your project! But if you want all the advantages of a scalable database that’s as easy to use on-prem as it is in the cloud (something PostgreSQL is all about) it’s better to be able to use vectors in your current database engine, and connect to new or existing AI-friendly apps and services. 

‘A great option is to keep your AI development connected to a scalable, familiar, SQL-based object-relational database like Postgres.’ 

Up until recently, this just wasn’t possible. But that changed earlier this year when the open source community announced that PostgreSQL now supports vectors, via the introduction of a new vector similarity extension, pgvector. Even better, it’s as simple as a developer enabling the extension by creating a Table with Vectors as Data Type

Now it’s much easier to have AI applications ramping at high speed through big data sets to find patterns with a ‘search for similar things’ capability. 

In techie terms, pgvector can now give you core vector norms such as exact and approximate nearest neighbour search, L2 distance, inner product, and cosine distance. Even better, it works for any app using a programming language that can target a Postgres client, from C to Ruby, Java, and Python. 

This is great news for the enterprise database community. They now have a new option to keep their AI development connected to a scalable, familiar, SQL-based object-relational database like Postgres (or YugabyteDB, which is Postgres-compatible). 

The addition of pgvector for the SQL database specialist is also good news for users in sectors like financial services. Those industries are very interested in AI, but require ultra-low latency, real-time, fault-tolerant data handling. It’s also great for other mainstream areas, including data warehouses, which have had to hold back from the AI push until now, but are keen to get into vectors and the sophisticated search options it unlocks. 

It’s definitely time to dip into the AI stream, and start riding the wave of innovation!

Join peers following The Stack on Linkedin