Introducing the azure_ai extension to Azure Database for PostgreSQL
Published Nov 15 2023 08:00 AM 5,631 Views

We are excited to announce the preview of the new azure_ai extension which enables you to integrate Azure AI services with your operational data.


Building AI applications that can leverage natural language processing, text analytics, and large language models is a challenging task. However, with the new azure_ai extension for Azure Database for PostgreSQL, you can access Azure OpenAI and Azure AI Language services from your SQL queries with just a simple function call. You can also store, index, and query high-dimensional vectors efficiently using PostgreSQL. This makes it very easy for developers and opens up new opportunities to build scalable AI-powered applications on Azure Database for PostgreSQL.


azure_ai extension – Preview


  • Generate embeddings from within the database with a single line of SQL code invoking a UDF.
  • Call various service from SQL with a simple function call enabling scenarios such as:
    • Sentiment Analysis
    • Language detection
    • Summarization
    • PII information detection
    • Key phase extractions
  • Harness the power of large language models (LLMS) alongside your operational data in PostgreSQL.

pgvector 0.5.1 support – General availability


We are also excited to announce the general availability of support for the latest version of pgvector 0.5.1. This update includes improvements from 0.5.0 and 0.5.1 which bring HSNW indexing (Hierarchical Navigable Small World) that enables efficient searches.


HNSW is a multi-layered graph that has very efficient traversal at larger scale to find nearest neighbors and allows queries to have much better speed-recall.


How does the azure_ai extension work?


Just to provide a quick taste of how easy it is to use azure_ai with Azure Database for PostgreSQL, generating an embedding is a simple call to a UDF inline from SQL passing the Azure OpenAI deployment model name, and the data input to generate the embedding.


SELECT    azure_openai.create_embeddings('text-embedding-ada-002', ' Learn about building intelligent applications with azure_ai extension and pgvector');


Alternately calling one of the language services such as sentiment analysis is also a UDF call from SQL.


select a.*
from azure_cognitive.analyze_sentiment('The GenAI session was awesome','en') a;


Here is a quick example that demonstrates:

  • Adding a vector column to a table with a default that generates an embedding and stores it when data is inserted.
  • Creating an HNSW index
  • Doing a Semantic search by generating an embedding for a search string, and comparing with stored vectors with a similarity search


Note: You will have to first create the azure_ai extension and configure Azure OpenAI settings. 


-- Create a Session table
CREATE TABLE conference_sessions(
  title text,
  session_abstract text,
  duration_minutes integer
-- Create an embeddings column, set default to store embedding generated UDF call to Azure OpenAI as data is inserted.
ALTER TABLE conference_sessions 
	ADD COLUMN session_embedding vector(1536) --Creates vector column with 1536 dimensions
	GENERATED ALWAYS AS  					 -- Generated on inserts
	(azure_openai.create_embeddings('text-embedding-ada-002' -- Calls Azure OpenAI deployment
	 ,title || session_abstract )::vector) STORED;  -- stores the vector.

-- Create a HNSW index
CREATE INDEX ON conference_sessions USING hnsw (session_embedding vector_ip_ops);

-- As rows are inserted, embeddings will be generated and stored
INSERT INTO conference_sessions (title,session_abstract,duration_minutes) 
    ('Gen AI with Azure Database for PostgreSQL'
    ,'Learn about building intelligent applications with azure_ai extension and pgvector' 
    , 60)
    ,('Deep Dive: PostgreSQL database storage engine internals'
    ,' We will dig deep into storage internals'
    , 30);

-- Semantic search using vector similarity match
 session_id, title, session_abstract
 FROM   conference_sessions c
    c.session_embedding <#> azure_openai.create_embeddings('text-embedding-ada-002', 'Session to learn about building chatbots')::vector


Getting Started

With the power of pgvector, the azure_ai extension, and the plethora of platform investments, Azure Database for PostgreSQL is ready to power the next generation of intelligent applications. Try out the preview of the azure_ai extension and see for yourself how easy (and fun) it is to build GenAI applications with Azure Database for PostgreSQL.


To learn more about the azure_ai extension and how it can simplify building applications on Azure Database for PostgreSQL, visit our documentation below:


If you have any questions or need guidance, don't hesitate to reach out to us at Ask Azure DB for PostgreSQL.

Version history
Last update:
‎Feb 26 2024 11:05 PM
Updated by: