PostgreSQL AI Extensions

Postgre database has AI extensions allowing you to integrate AI services directly with the PostgreSQL database. It supports functionalities like generating vector embeddings, sentiment analysis, language detection, and more by calling OpenAI.

Let’s talk about a few extensions.

pgvector

pgvector is an open-sourcing extension to enable vector similarity search. This extension allows you to store and query vectors directly within your PostgreSQL database, making it easier to integrate AI and machine learning functionalities.

import numpy as np
import psycopg

# Generate random data
rows = 1000000
dimensions = 128
embeddings = np.random.rand(rows, dimensions)

# Connect to PostgreSQL
conn = psycopg.connect(dbname='pgvector_example', autocommit=True)
conn.execute('CREATE EXTENSION IF NOT EXISTS vector')

# Create table
conn.execute(f'CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector({dimensions}))')

# Load data
cur = conn.cursor()
with cur.copy('COPY items (embedding) FROM STDIN WITH (FORMAT BINARY)') as copy:
    copy.set_types(['vector'])
    for embedding in embeddings:
        copy.write_row([embedding])

# Create index
conn.execute("CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)")

Within the PostgreSQL database, you can create the vector table and load the data, then you will be able to see the similarity between the database.

pg_vectorize