This is the implementation of the PGVector for MindsDB.

​
PGVector

Open-source vector similarity search for Postgres Store your vectors with the rest of your data. Supports: exact and approximate nearest neighbor search L2 distance, inner product, and cosine distance any language with a Postgres client Plus ACID compliance, point-in-time recovery, JOINs, and all of the other great features of Postgres

​
Implementation

This handler uses pgvector python library to make use of the vector data type in postgres created from the pgvector extension The required arguments to establish a connection are the same as a regular postgres connection:
  • 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

​
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;