Azure AI And Pgvector Run Generative AI Directly On Postgres
Written by Nikos Vaggalis   
Tuesday, 26 March 2024

It's a match made in heaven. The Azure AI extension enables the database to call into various Azure AI services like Azure OpenAI. Combined with pgvector you can go far beyond full text search. Let's get the details.

Azure Database for PostgreSQL comes in two deployment modes: Flexible Server and Single Server. The Flexible Server is a fully managed database service designed to provide more granular control and flexibility over database management functions and configuration settings. It's this version that has been extended with the new functionality offered by the AzureAI extension for Postgres which directly integrates the database with Azure's OpenAI services.

In short what this means is that you can generate text embeddings by using SQL queries to call into both Azure OpenAI services without needing a separate application layer. As a refresher, embeddings take a piece of text and create a numerical representation of that text. Text with similar content will have similar vectors in this numeric space. This allows us to compare those vectors and find pieces of text that are similar.

But it's one thing to generate embeddings and another to store them. The process goes that you first convert the text into embeddings and then you've got to load them into a vector store. Here's where pg_vector steps in, in allowing to replace any specialized vector engine with just Postgres so that you can have your embeddings stored alongside your JSON or relation data under the same roof.

So while using azure_ai you do generate embeddings, you’ll still want to use pgvector to store the embeddings (and do distance computations) inside the Azure Database for PostgreSQL. This capability then enables you to do operations like semantic search, recommendations or anomaly detection, faster and more easily than ever before - and without extensive application changes.

As such semantic search goes beyond the full text search capabilities supported natively by Postgres, and is succinctly explained in a great keynote presented at the PASS Data Summit 2023 by Claire Giordano. The keynote showcases how you can improve the relevance of the search results of a recipe website by using Azure OpenAI.

It begins by showcasing how to retrieve results by using the usual LIKE operator in a simple keyword search, looking for recipes with the ingredient of Avocado and how you can get to much better results via Postgres full text search. However even fts can not answer questions like "Give me high protein recipes", which is information in no way incorporated into the database. With azure_ai however you are able to call into the Azure OpenAI services and make that query to get a list with the high protein recipes stored inside your database!

This goes beyond searching tough. Other options now available thanks to the new extension is also the ability to invoke the Azure Language services in order to for instance do sentiment analysis right from within the database. The keynote shows how to do that over recipe reviews to understand which were positive and which negative. Other capabilities now possible is finding out which language a text is written or doing PII redacting when extracting data from the database.

The gist of the matter is that due to Postgres' capability to be extended without affecting the core, Postgres is truly open to innovation;and azure_ai hand in hand with pgvector extend Postgres with never seen before capabilities. Imagination is the limit. 

More Information

Azure Database for PostgreSQL - Flexible Server Azure AI Extension 


Related Articles

Turn PostgreSQL Into A Vector Store



To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.


The University of Tübingen's Self-Driving Cars Course

The recorded lectures and the written material of a course on Self-Driving Cars at the University of Tübingen have been made available for free. It's a first class opportunity to learn the in an [ ... ]

Angular and Wiz To Merge

Two web development frameworks used at Google are merging. One, Angular is open source and widely known, while the other, Wiz, is an internal web framework developed and used by Google for some o [ ... ]

More News

raspberry pi books



or email your comment to:

Last Updated ( Tuesday, 26 March 2024 )