Enhancing Query Performance With AI and Vector Search in Azure Cosmos DB for PostgreSQL

In today's data-driven world, conventional search approaches frequently struggle with intricate queries and delivering pertinent results. The advent of AI-powered vector search presents a game-changing query efficiency and precision advancement. Azure Cosmos DB for PostgreSQL emerges as a robust platform that embraces AI-driven vector search, empowering users to elevate their query capabilities significantly.

In this blog, you will learn: 

1. How AI and vector search functionalities can be used within Azure Cosmos DB for PostgreSQL to elevate query performance. 
2. Essential steps, code snippets, and actionable tips for practical implementation. 

Let’s get started!

What Is Vector Search? 

Vector search is an advanced data retrieval method beyond traditional keyword-based search techniques. Every data type, including text, image, or audio, is converted into a vector. This captures the data's inherent qualities and context.

Using AI models like BERT for text or convolutional neural networks for images, raw data is transformed into vectors that capture intrinsic properties and context. This method excels at handling unstructured data and delivers more accurate results by mapping similar data points close to each other in the vector space. 

Azure Cosmos DB for PostgreSQL is an ideal platform for implementing vector search due to its scalability, flexibility, and robust support for AI and machine learning integrations. It enables efficient and scalable data retrieval across various applications.

Setting up Azure Cosmos DB for Postgresql

Create an Azure Cosmos DB account. This process is straightforward and begins with logging in to the Azure Portal.  

Step 1: Setup Azure Cosmos DB Account

Once logged in, follow these steps:

1. Click on "Create a resource" in the Azure Portal dashboard.
2. Search for and select "Azure Cosmos DB."
3. When prompted, choose the "Azure Cosmos DB for PostgreSQL" API to ensure compatibility with PostgreSQL databases.
4. Fill in the required details such as subscription, resource group, account name, and region.
5. After verifying all the details, click "Review + Create" to finalize the setup.

Step 2: Configuration of PostgreSQL Database

Finish the configuration of the PostgreSQL database to set up your database and tables. This configuration will provide the foundation for storing vectors and performing efficient searches. Follow the steps below to prepare your PostgreSQL database for vector search implementation.

1. Navigate to your newly created Cosmos DB account.
2. Click on "Data Explorer" and create a new database.
3. Create a new table within the database to store your data.

Integrating AI Models for Vector Search

In this example, we'll use a pre-trained BERT model for text data to convert it into vectors. 

Step 1: Create Python Environment

Ensure you have installed Python and the necessary libraries. Create a virtual environment and install the required packages:

python 
-m venv myenv
source myenv/bin/activate
pip install transformers torch psycopg2 numpy

Step 2: Load Pre-Trained BERT Model

To leverage the power of BERT for transforming text data into vectors, follow these steps:

1. Install Transformers Library: Ensure you have the Transformers library installed.
2. Import Required Modules: Import the necessary modules from the transformers library
3. Load BERT Tokenizer and Model: Load the pre-trained BERT tokenizer and model
4. Encode Text Data:  Define a function to encode text into vectors.

from transformers import BertTokenizer, BertModel
import torch
import numpy as np

tokenizer = BertTokenizer.from_pretrained('bert-base-uncased')
model = BertModel.from_pretrained('bert-base-uncased')

def encode_text(text):
    inputs = tokenizer(text, return_tensors='pt')
    outputs = model(**inputs)
    return outputs.last_hidden_state.mean(dim=1).detach().numpy()

The above code snippet can convert text data into dense vector representations using the BERT model. These representations can then be stored and utilized for vector search in your PostgreSQL database.

Store Vectors in PostgreSQL

The next step is to store vectors in our PostgreSQL database. Storing vectors allows us to efficiently perform similarity searches and leverage the power of vector search for enhanced query performance. This process involves connecting to the PostgreSQL database, inserting the vectors along with the original text data, and ensuring the database is set up to handle and query these vector representations effectively.

To store the encoded vectors in your PostgreSQL database, follow these steps

Step 1: Install psycopg2

Ensure you have the psycopg2 library installed.

pip install psycopg2

Step 2: Connect to PostgreSQL

Establish a connection to your PostgreSQL database.

import psycopg2

conn = psycopg2.connect(
    dbname='yourdbname',
    user='yourusername',
    password='yourpassword',
    host='yourhost',
    port='yourport'
)
cur = conn.cursor()

Step 3: Create a Table

Create a table to store texts and their corresponding vectors.

cur.execute('''
    CREATE TABLE text_vectors (
        id SERIAL PRIMARY KEY,
        text TEXT,
        vector FLOAT8[]
    )
''')
conn.commit()

Step 4: Insert Vectors Into the Table

Convert text data into vectors and insert them into the PostgreSQL table.

texts = ["example text 1", "example text 2", "example text 3"]

for text in texts:
    vector = encode_text(text).tolist()
    cur.execute('INSERT INTO text_vectors (text, vector) VALUES (%s, %s)', (text, vector))

conn.commit()

Implementing Vector Search

The next step is to efficiently perform vector-based searches. Follow these steps to connect to your PostgreSQL database and store the vectors.

Step 1: Calculate Cosine Similarity 

As mentioned in the code snippet below, define a function to calculate the cosine similarity between vectors. This will help determine the similarity between the query and stored vectors. 

import numpy as np

def cosine_similarity(vec1, vec2):
    return np.dot(vec1, vec2) / (np.linalg.norm(vec1) * np.linalg.norm(vec2))

Step 2: Perform Vector Search

Fetch vectors from the database and compute similarity:

def search_similar_vectors(query_text, top_n=5):
    query_vector = encode_text(query_text).tolist()
        cur.execute('SELECT id, text, vector FROM text_vectors')
    results = cur.fetchall()
        similarities = []
    for result in results:
        vector = np.array(result[2])
        similarity = cosine_similarity(query_vector, vector)
        similarities.append((result[0], result[1], similarity))
        # Sort by similarity and get top N results
    similarities.sort(key=lambda x: x[2], reverse=True)
    return similarities[:top_n]

# Example usage
query_text = "example search text"
top_results = search_similar_vectors(query_text)
for result in top_results:
    print(f"Text: {result[1]}, Similarity: {result[2]}")

This function encodes the query text and retrieves stored vectors from the database. It also calculates cosine similarities and returns the top N most similar vectors.

Optimize Search Performance

Follow the approaches below to enhance vector search performance in the PostgreSQL database.

1. Indexing Vectors

To index vectors, use appropriate indexing methods, such as GiST (Generalized Search Tree) or SP-GiST (Space-partitioned Generalized Search Tree). 

cur.execute('CREATE INDEX ON text_vectors USING GIST (vector)')
conn.commit()

2. Batch Processing

Batch processing reduces the overhead of inserting vectors one by one and improves overall performance. Data is inserted in batches to minimize database round trips.

batch_size = 100
texts = ["example text 1", "example text 2", "example text 3"] * 100  # Example data

for i in range(0, len(texts), batch_size):
    batch_texts = texts[i:i+batch_size]
    batch_vectors = [encode_text(text).tolist() for text in batch_texts]
    cur.executemany('INSERT INTO text_vectors (text, vector) VALUES (%s, %s)', zip(batch_texts, batch_vectors))
    conn.commit()

3. Use GPU Acceleration

GPUs can significantly speed up the computation of vector transformations and similarity calculations.  Use libraries such as PyTorch that support GPU acceleration.

4. Caching

Caching frequently queried data reduces the need for repeated computations and database access. Use caching mechanisms like Redis or built-in PostgreSQL caching.

import redis
import numpy as np

cache = redis.Redis(host='localhost', port=6379, db=0)

def cache_vector(text, vector):
    cache.set(text, np.array(vector).tobytes())

def get_cached_vector(text):
    cached_vector = cache.get(text)
    if cached_vector:
        return np.frombuffer(cached_vector, dtype=np.float32)
    return None

5. Use Faiss

For more efficient similarity search, especially with large datasets, consider using Facebook's Faiss library, which is optimized for vector similarity search. 

import faiss

# Assuming vectors are stored in a NumPy array
vectors = np.array([result[2] for result in results])
index = faiss.IndexFlatL2(vectors.shape[1])
index.add(vectors)

# Perform search
D, I = index.search(np.array(query_vector).reshape(1, -1), top_n)
top_indices = I[0]
top_similarities = D[0]

for idx, similarity in zip(top_indices, top_similarities):
    print(f"Text: {results[idx][1]}, Similarity: {similarity}")

Use these strategies to enhance the performance and efficiency of your vector search operations in Azure Cosmos DB for PostgreSQL.

Conclusion

Integrating AI models can significantly enhance query performance and achieve more accurate search results. This approach is especially beneficial for handling unstructured data and complex queries. With the steps and code snippets this blog provides, enterprises can implement these advanced search capabilities in their projects.