Build a Vector Database with FAISS & PostgreSQL

What We’re Building

In this tutorial, we will construct a robust vector database system capable of supporting AI applications that require fast and efficient vector search capabilities. By integrating FAISS for vector similarity search and PostgreSQL with pgvector for relational data management, the system will efficiently handle high-dimensional data and perform semantic searches. This setup is particularly useful for applications like recommendation engines, semantic search engines, and other AI-driven solutions requiring quick retrieval of similar items from large datasets.

The end result will be a system that can index and search through millions of vectors efficiently, leveraging GPU acceleration for performance improvements. This will enable AI models to perform operations such as similarity matching and semantic retrieval with reduced latency and increased accuracy.

Incorporating regional initiatives like Saudi Vision 2030 and the UAE National Strategy for AI, this setup can significantly enhance AI infrastructure in the GCC, supporting local businesses and government projects in achieving their digital transformation goals.

Setup and Installation

We need to install the necessary libraries and set up our environment to support vector operations both on the CPU and GPU. This includes setting up FAISS with GPU support, PostgreSQL with the pgvector extension, and the necessary Python libraries for data processing and API interaction.

pip install faiss-gpu==1.14.0
pip install psycopg2-binary
pip install numpy
pip install pandas

Additionally, ensure that PostgreSQL is installed and pgvector extension is enabled. You may need administrative access to install extensions on your PostgreSQL database.

CREATE EXTENSION IF NOT EXISTS vector;

Environment variables can be managed using a `.env` file to keep track of database credentials and API keys securely.

DB_HOST=localhost
DB_PORT=5432
DB_USER=yourusername
DB_PASSWORD=yourpassword
DB_NAME=yourdbname

Step 1: Setting Up the PostgreSQL Database

First, we will configure our PostgreSQL database to store vector data. This involves creating a table with a column specifically designed to hold vector data, using the pgvector extension.

import psycopg2

connection = psycopg2.connect(
    host="localhost",
    database="yourdbname",
    user="yourusername",
    password="yourpassword"
)

cursor = connection.cursor()

create_table_query = '''
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    embedding VECTOR(300) -- Assuming 300 dimensions for embeddings
);
'''

cursor.execute(create_table_query)
connection.commit()

cursor.close()
connection.close()

This code connects to your PostgreSQL database and creates a table named `products` with a `VECTOR` column to store embeddings. The `VECTOR(300)` indicates that each vector will have 300 dimensions, which is typical for certain pre-trained models like BERT.

Step 2: Preparing Data and Generating Embeddings

Next, we will prepare our data and generate embeddings using a pre-trained model. These embeddings will be stored in our PostgreSQL database for later retrieval.

import numpy as np
import pandas as pd
from transformers import AutoTokenizer, AutoModel

# Load pre-trained model tokenizer and model
tokenizer = AutoTokenizer.from_pretrained('bert-base-uncased')
model = AutoModel.from_pretrained('bert-base-uncased')

def generate_embedding(text):
    inputs = tokenizer(text, return_tensors='pt')
    outputs = model(**inputs)
    # Use the mean pooling of the last hidden state as the embedding
    return outputs.last_hidden_state.mean(dim=1).squeeze().detach().numpy()

# Sample data
data = pd.DataFrame({
    'name': ['Product 1', 'Product 2'],
    'description': ['This is a great product.', 'Another excellent choice.']
})

# Generate embeddings
data['embedding'] = data['description'].apply(generate_embedding)

This script uses the BERT model to generate 300-dimensional embeddings for product descriptions. We apply the mean pooling technique over the last hidden state to obtain a fixed-size vector representation for each description.

Step 3: Inserting Embeddings into the Database

With our embeddings ready, the next step is to insert them into the PostgreSQL database. This involves converting the numpy array to a list format compatible with SQL insertion.

def insert_embeddings_to_db(data):
    connection = psycopg2.connect(
        host="localhost",
        database="yourdbname",
        user="yourusername",
        password="yourpassword"
    )
    cursor = connection.cursor()

    insert_query = '''
    INSERT INTO products (name, description, embedding)
    VALUES (%s, %s, %s)
    '''
    for _, row in data.iterrows():
        cursor.execute(insert_query, (row['name'], row['description'], row['embedding'].tolist()))
    
    connection.commit()
    cursor.close()
    connection.close()

# Insert data into the database
insert_embeddings_to_db(data)

This function iterates over the DataFrame, inserting each row into the database. The embeddings are converted to lists to match the expected input format for the `VECTOR` type in PostgreSQL.

Testing Your Implementation

To verify that our setup works, we will perform a similarity search using the inserted embeddings. This involves querying the database to find the most similar items based on vector similarity.

def search_similar_products(query_embedding, top_k=5):
    connection = psycopg2.connect(
        host="localhost",
        database="yourdbname",
        user="yourusername",
        password="yourpassword"
    )
    cursor = connection.cursor()

    search_query = '''
    SELECT id, name, description, embedding  %s AS distance
    FROM products
    ORDER BY distance ASC
    LIMIT %s;
    '''
    cursor.execute(search_query, (query_embedding.tolist(), top_k))
    results = cursor.fetchall()

    cursor.close()
    connection.close()
    return results

# Example query embedding
query_embedding = generate_embedding("Looking for a great product.")
similar_products = search_similar_products(query_embedding)
print(similar_products)

This function searches for the top `k` similar products by computing the cosine distance between the query embedding and stored embeddings. The results are ordered by similarity, with the most similar products appearing first.

What to Build Next

After completing this tutorial, consider extending your project with the following features:

• Integrate a web interface using a framework like React or Next.js to allow users to interact with the search functionality directly.
• Enhance the recommendation system by incorporating user behavior data and feedback loops to improve accuracy over time.
• Optimize the performance by experimenting with different index types in FAISS, such as IVF or HNSW, to handle larger datasets more efficiently.