Optimizing RAG Workflows with PostgreSQL as a VectorDB in Amazon Bedrock

Overview

Retrieval-Augmented Generation (RAG) workflows empower AI systems to provide highly accurate and contextual responses by retrieving relevant data before generating an answer. In this guide, we explore the setup and integration of PostgreSQL as a Vector Database (VectorDB) with Amazon Bedrock Knowledge Base to enable scalable and efficient RAG workflows.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Introduction

PostgreSQL as VectorDB Amazon Aurora PostgreSQL, with its scalability and feature set, serves as an excellent VectorDB solution. Leveraging the pgvector extension supports vector storage, indexing, and searches, seamlessly integrating with Amazon Bedrock’s Knowledge Base.

Use Case: This integration enhances foundational models’ capabilities, enabling them to generate more accurate and context-rich responses by retrieving relevant data stored in PostgreSQL.

Prerequisites

Aurora PostgreSQL Versions: Ensure you use PostgreSQL version 12.16 or higher.
pgvector Extension: Version 0.5.0+ is required for vector searches.
AWS Secrets Manager: Use AWS Secrets Manager to store database credentials securely.
Amazon Bedrock Access: Enable Amazon Bedrock to connect with the Knowledge Base.

PostgreSQL Setup

Create Amazon Aurora PostgreSQL Cluster
- Use the AWS Management Console to create a PostgreSQL cluster.
- Enable the Amazon RDS Data API and make a note of the DB Cluster ARN.
Install and Verify pgvector Run the following SQL commands to install and verify the pgvector extension:

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname='vector';

1 2	CREATE EXTENSION IF NOT EXISTS vector; SELECT extversion FROM pg_extension WHERE extname='vector';

Configure Schema and Roles Set up a dedicated schema and assign roles:

CREATE SCHEMA bedrock_integration;
CREATE ROLE bedrock_user WITH PASSWORD 'your_password' LOGIN;
GRANT ALL ON SCHEMA bedrock_integration TO bedrock_user;

CREATE SCHEMA bedrock_integration;

CREATE ROLE bedrock_user WITH PASSWORD 'your_password' LOGIN;

GRANT ALL ON SCHEMA bedrock_integration TO bedrock_user;

Vector Table Setup

Table Definition: Create a table to store vector embeddings, metadata, and text data:

CREATE TABLE bedrock_integration.bedrock_kb (
    id UUID PRIMARY KEY,
    embedding vector(1024),
    chunks text,
    metadata json
);

CREATE TABLE bedrock_integration.bedrock_kb (

id UUID PRIMARY KEY,

embedding vector(1024),

chunks text,

metadata json

);

Embedding Dimension: Ensure the dimension matches the model, e.g., 1024 for Amazon Titan v2.
Metadata: Store additional contextual information in JSON format.

Vector Search Index Optimize vector search using the HNSW index:

CREATE INDEX ON bedrock_integration.bedrock_kb USING hnsw (embedding vector_cosine_ops);

1	CREATE INDEX ON bedrock_integration.bedrock_kb USING hnsw (embedding vector_cosine_ops);

For pgvector version 0.6.0+, enable parallel indexing:

CREATE INDEX ON bedrock_integration.bedrock_kb USING hnsw (embedding vector_cosine_ops) WITH (ef_construction=256);

1	CREATE INDEX ON bedrock_integration.bedrock_kb USING hnsw (embedding vector_cosine_ops) WITH (ef_construction=256);

Data and Metadata Preparation for Amazon Bedrock

Data Ingestion Steps

Chunk your text data into smaller files and associate each chunk with metadata.
Example Metadata:

{
  "metadataAttributes": {
    "Name": "Sample Recipe",
    "TotalTimeInMinutes": "25",
    "CholesterolContent": "0",
    "SugarContent": "5"
  }
}

{

"metadataAttributes": {

"Name": "Sample Recipe",

"TotalTimeInMinutes": "25",

"CholesterolContent": "0",

"SugarContent": "5"

}

Upload Data to Amazon S3 Use Python and Boto3 to upload your data:

import os
import boto3

s3_client = boto3.client('s3')

def upload_directory(path, bucket_name):
    for root, dirs, files in os.walk(path):
        for file in files:
            s3_client.upload_file(os.path.join(root, file), bucket_name, file)

import os

import boto3

s3_client = boto3.client('s3')

def upload_directory(path, bucket_name):

for root, dirs, files in os.walk(path):

for file in files:

s3_client.upload_file(os.path.join(root, file), bucket_name, file)

Amazon Bedrock Integration

Knowledge Base Setup Configure Amazon Bedrock to use the PostgreSQL vector table:

Provide the following:
- Aurora DB Cluster ARN
- Secrets Manager ARN
- Database and Table Names
- Index Field Mapping

Field Mapping Details

Vector Field Name: The column for storing embeddings.
Text Field Name: The column for storing raw text chunks.
Metadata Field Name: The column for storing metadata.
Primary Key: Specify the primary key column.

Retrieval-Augmented Generation (RAG)

Metadata Filtering Improve retrieval accuracy by applying metadata constraints:

def retrieve(query, kb_id, number_of_results=5):
    return bedrock_agent_client.retrieve(
        retrievalQuery={'text': query},
        knowledgeBaseId=kb_id,
        retrievalConfiguration={
            'vectorSearchConfiguration': {
                'numberOfResults': number_of_results,
                'filter': {
                    'andAll': [
                        {"lessThan": {"key": "CholesterolContent", "value": 10}},
                        {"lessThan": {"key": "TotalTimeInMinutes", "value": 30}}
                    ]
                }
            }
        }
    )

def retrieve(query, kb_id, number_of_results=5):

return bedrock_agent_client.retrieve(

retrievalQuery={'text': query},

knowledgeBaseId=kb_id,

retrievalConfiguration={

'vectorSearchConfiguration': {

'numberOfResults': number_of_results,

'filter': {

'andAll': [

{"lessThan": {"key": "CholesterolContent", "value": 10}},

{"lessThan": {"key": "TotalTimeInMinutes", "value": 30}}

]

}

)

Retrieve and Generate Responses Combine retrieval with generation for context-rich answers:

prompt = “””

Human: You have great knowledge about food, so provide answers to questions by using facts.

If you don’t know the answer, just say that you don’t know; don’t try to make up an answer.

Assistant:”””

def retrieve_and_generate(query, kb_id, model_id, number_of_results=10):
    return bedrock_agent_client.retrieve_and_generate(
        input={'text': query},
        retrieveAndGenerateConfiguration={
            'knowledgeBaseConfiguration': {
                'generationConfiguration': {
                    'promptTemplate': {
                        'textPromptTemplate': f"{prompt} $search_results$"
                    }
                },
                'knowledgeBaseId': kb_id,
                'retrievalConfiguration': {
                    'vectorSearchConfiguration': {
                        'numberOfResults': number_of_results,
                        'filter': {
                            'andAll': [
                                {"lessThan": {"key": "CholesterolContent", "value": 10}},
                                {"lessThan": {"key": "TotalTimeInMinutes", "value": 30}}
                            ]
                        }
                    }
                }
            },
            'modelArn': model_id
        }
    )

def retrieve_and_generate(query, kb_id, model_id, number_of_results=10):

return bedrock_agent_client.retrieve_and_generate(

input={'text': query},

retrieveAndGenerateConfiguration={

'knowledgeBaseConfiguration': {

'generationConfiguration': {

'promptTemplate': {

'textPromptTemplate': f"{prompt} $search_results$"

}

'knowledgeBaseId': kb_id,

'retrievalConfiguration': {

'vectorSearchConfiguration': {

'numberOfResults': number_of_results,

'filter': {

'andAll': [

{"lessThan": {"key": "CholesterolContent", "value": 10}},

{"lessThan": {"key": "TotalTimeInMinutes", "value": 30}}

]

}

'modelArn': model_id

}

)

Benefits of Metadata Filtering

Accuracy: Ensures retrieved results meet specific constraints.
Efficiency: Reduces token costs by focusing on relevant data.
Applications: Useful for chatbots, search engines, and recommendation systems.

Conclusion

Integrating PostgreSQL as a VectorDB with Amazon Bedrock offers a scalable, cost-effective, and efficient architecture for RAG workflows. You can build intelligent applications that deliver contextually accurate and impactful responses by leveraging vector embeddings, metadata filtering, and foundational models.

This setup is ideal for use cases like AI-driven chatbots, personalized recommendations, and advanced search engines.

Drop a query if you have any questions regarding PostgreSQL and we will get back to you quickly.

Empowering organizations to become ‘data driven’ enterprises with our Cloud experts.

Reduced infrastructure costs
Timely data-driven decisions

Get Started

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, Microsoft Gold Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon ECS Service Delivery Partner, AWS Glue Service Delivery Partner, Amazon Redshift Service Delivery Partner, AWS Control Tower Service Delivery Partner, AWS WAF Service Delivery Partner, Amazon CloudFront, Amazon OpenSearch, AWS DMS and many more.

FAQs

1. Why choose Aurora PostgreSQL with pgvector over other VectorDBs?

ANS: – Aurora PostgreSQL combines familiarity with SQL with vector embedding capabilities via pgvector. It’s cost-effective, highly available, and integrates seamlessly with Amazon Bedrock.

2. How does metadata filtering improve RAG workflows?

ANS: – By applying constraints (e.g., TotalTimeInMinutes < 30), metadata filtering ensures retrieved results are contextually relevant, optimizing foundational model performance and reducing irrelevant token usage.

WRITTEN BY Shantanu Singh

Shantanu Singh works as a Research Associate at CloudThat. His expertise lies in Data Analytics. Shantanu's passion for technology has driven him to pursue data science as his career path. Shantanu enjoys reading about new technologies to develop his interpersonal skills and knowledge. He is very keen to learn new technology. His dedication to work and love for technology make him a valuable asset.