PGVector

This is the implementation of the PGVector for MindsDB.

PGVector is an open-source vector similarity search for Postgres. It supports the following:

• exact and approximate nearest neighbor search,
• L2 distance, inner product, and cosine distance,
• any language with a Postgres client,
• ACID compliance, point-in-time recovery, JOINs, and all of the other great features of Postgres.

​

Connection

This handler uses pgvector Python library.

To connect to a PGVector instance, use the following statement:

CREATE DATABASE pvec
WITH
    ENGINE = 'pgvector',
    PARAMETERS = {
    "host": "127.0.0.1",
    "port": 5432,
    "database": "postgres",
    "user": "user",
    "password": "password",
    "distance": "cosine"
    };

The required arguments to establish a connection are the following:

• host: The host name or IP address of the postgres instance.
• port: The port to use when connecting.
• database: The database to connect to.
• user: The user to connect as.
• password: The password to use when connecting.
• distance: It defines how the distance between vectors is calculated. Available methods include cosine (default), l1, l2, ip, hamming, jaccard. Learn more here.

​

Usage

​

Installing the pgvector extension

where you have postgres installed run the following commands to install the pgvector extension

cd /tmp git clone --branch v0.4.4 https://github.com/pgvector/pgvector.git cd pgvector make make install

​

Installing the pgvector python library

Ensure you install all from requirements.txt in the pgvector_handler folder

​

Creating a database connection in MindsDB

You can create a database connection like you would for a regular postgres database, the only difference is that you need to specify the engine as pgvector

CREATE DATABASE pvec
WITH
    ENGINE = 'pgvector',
    PARAMETERS = {
    "host": "127.0.0.1",
    "port": 5432,
    "database": "postgres",
    "user": "user",
    "password": "password"
    };

You can insert data into a new collection like so

CREATE TABLE pvec.embed
    (SELECT embeddings FROM mysql_demo_db.test_embeddings
);

CREATE ML_ENGINE openai
FROM openai
USING
    api_key = 'your-openai-api-key';

CREATE MODEL openai_emb 
PREDICT embedding 
USING    
  engine = 'openai',
  model_name='text-embedding-ada-002',    
  mode = 'embedding',    
  question_column = 'review'; 

create table pvec.itemstest (
SELECT m.embedding AS embeddings, t.review content FROM  mysql_demo_db.amazon_reviews t
  join openai_emb  m
);

You can query a collection within your PGVector as follows:

SELECT *
FROM pvec.embed
Limit 5;

SELECT *
FROM pvec.itemstest
Limit 5;

You can query on semantic search like so:

SELECT *
FROM pvec3.items_test
WHERE embeddings = (select * from mindsdb.embedding) LIMIT 5;