PostgreSQL as a Vector Database: Part 1, Getting Started With pgvector

H my fellow developers we all know pogress as a relational database for transactional use cases but at the same time pogress can be used as a vector database it has all the essential capabilities needed to build general purpose AI applications and with this video we are starting a series where I will show you how to use progress as a vector database in particular today you will learn the basics you will learn how to use the PG Vector extension to store vectorized data in your database and then how to work with this data to build some meaningful AI apps all right my friend and we are going to learn pogus vectorized and AI capabilities by building a sample application we have a template of the login recommendation service that uses open ey and post spe VOR if you don't know open ey is the company behind cat GPT that millions of us use daily so imagine that you are traveling to San Francisco and you want to stay in the best location in in the nicest neighborhood of the city so you would open this application you would type in your user prompt and then the service will return you a list of places to stay so this is effectively what we are going to build the initial version of the service already uses openi chat completions API with those API we are going to take the user prompt and send it as is to the gp4 model back in the cloud and then the model will return you a list of places where you might want to stay However with that implementation you will notice that it's quite slow and it's not scalable with that we will bring in pogis into the picture posg is going to use the PG Vector extension that enables you to store vectors and supports essential operations for the similarity search and other AI use cases let's dive in I've already cloned this application uh to my local laptop so here is we have the back end we have the front end we have some SQL scripts that will be necessary to complete the implementation of the app but let's start with the application proc properties file you'll see that you need to have the openi API key so go ahead and register it and provide in this application settings that's Mikey and then this port number you don't need to change it that's the port number your UI is listening on and then you have the database connectivity settings I'm running pogress on my local machine so let's just start this application and see how it works we are going to start with the back end let's start the back end yeah it says that we can successfully connect it to pogress how about front end okay it's here that's the open ey chat mode that is already implemented and pus embeding mod that's what we need to support I want to stay near their Golden Gate Bridge with a nice view of the bay that's your preference and then we're starting the search and take a look at this Pacman who keeps eating those bubbles it means that we have already send the request to the open Ai and they were waiting for the response and usually it takes 10 or 50 seconds to get a list of recommendations just because it that's a massive model and it takes time to process and generate responses for you but we've got what we asked but still we want this request to complete within a few seconds or under a second and this is where we are going to use po but before let's take a look at this implementation open I chat mode go to the application logic and under the back end you will find this openi chat service implementation what it does it connects to the openi API and it will be using your openi key and then you creating this request to the chat completions API you are saying that hey chat jpt please be a helpful assistant who helps to find login in San Francisco and suggest at least three places and then you're getting this response you going to use gp4 model or you can use any other model that is available or affordable to you and then you're getting those recommendations and you're returning them as a Json array back to their front end however I want to use POS here because we want to boost the latency performance of our application so my pogress uh let me show it to you connecting to the pogress instance and the database is empty we don't have any relations so where will we get the data for our recommendation service go to the direct of this project you will find the following SQL script and the SQL script creates a sample Airbnb table let's just copy and paste it if you take a look it comes with more than 100 columns it's very big table created but certainly the table is empty also if you Google really well you will find some Airbnb sample data set that can be placed into this table it's already here also under this project all we need to do is to preload it let's use the copy command we are preloading it from the location where your project is it's under the following location on my laptop that's the command okay we've got more than 7,500 Airbnb places available for rentals in San Francisco that's a wonderful data set for our experiment with AI let's take a look at the a few descriptions of several Airbnb properties let's select our name and description from Airbnb listing three of them yeah it's not readable we need to enable the following mode so here is one of them it's the name of this property and here is the description there is detailed information such as the locations to some homeworks on San Francisco if you scroll up here is another uh description for another property and this description column is a wonderful choice for our vectorized data so what we want to do next we want to read descriptions of all of those Airbnb properties and we are going to use openi embedding CPI to generate vectorized representation for these descriptions and we are going to put those vectors back into our database that's our next step let's enable the PG Vector extension so PG Vector extension enables the similarity search in pogress it allows you to store vectors and it provides basic operations such as the calculations of Clan or cosign dist that is necessary for the similarity search I'm running pogress in a Docker container with this extension installed and all I need to do is to create extension Vector it's done and then I want to change the structure of my Airbnb table I need to add a column that will be holding the vectors for my description alter table Airbnb listing add column let's name it description embed and the data type is Vector and then you need to provide the dimension the dimension needs to be 1,536 if you're using openi embeding model if you're using a different model then the size of the dector will vary so we've got everything ready so now we need to implement our data generator for that you can jump back to the back end folder and find the following file embedding generator file so what happens here we have some skeleton we will open connection with the open ey we will connect to the database and next we need to read all those descriptions from the database and generate them bance how do we do that it's simple uh let's read all the rows a wait client we only need ID of an Airbnb property and is description once you got this data uh let's read all the rows one by one and then for every row we need to send the request to the openi embeding model embeding response we are making a call to the openi embeding create API end point and here is we need to provide the model name the name of the model is text embedding a002 and you need to pass your textual representation of the description that you read from the database once you get it back you need to store this information in back into pogress for that let's do this query we want to update not to insert Airbnb places we know that the name of the column is description embedding we have just created it and we need to pass this embedding response it's here and also we need to wrap into this square brackets and we do this for the row with the following ID that we also read before and we are going to do this for every Airbnb property and then let's generate the total count excellent let's check if everything is accurate No the name of the table is wrong Airbnb listing and Airbnb listing needs to be here so now with that hopefully fingers crossed we need to start this data generator let's start the back end okay we successfully connected to pogress and right now what the application does is going to generate embedding for every description of every property and it's going to store this into the description embeddings it will take some time while it's happening you actually can see what's the progress let's see how many embeddings do we already have in our table from Airbnb listing where description embedding is not n and you can see that this this number is changing so let's uh take a little break and in a few minutes you will get the data set ready for the next step finally my friend we generated edits for all of the properties excellent you can see this number and this number will be a little bit lower than the total number of properties because not every property has description so we have 24 properties that don't have any description which mean that they will be excluded from the search now let's take a quick look at those description embeddings let's query name description and description embedding column from Air pnv listing limit three and this is a large Vector right if you remember the size of every Vector is 1,536 that's the size of the vectors returned by open eye and here is you will find this description and the name of this Airbnb property and then if you scroll up you will find another Vector then you will find another description and another property name so we've got all the data in here so now what we want to do we want to implement this logic we have this prompt that presently uses the open ey chat completion CPI but we want to implement this PO embedding mode if you do the search right now you will not find anything because we need to implement this again going back to our project and here is we will find this pogress embeddings code so what happens here we also going to connect to pogress we are going to use openi now search places this method is being called from following file when you provide the user prompt and if you want to use pogress then we are going to make a call to this search places method and here is we providing three parameters the first one is user prom this is what we are typing in the UI uh that's the similarity between their user prompt and descriptions of your Airbnb properties it needs to be within your range between zero and and one the closer to to one the better which mean that it will be more similar to what user is asking for and three is the number of listings and a number of recommendations you want to have let's implement this first we can actually go ahead to the embedding and copy paste this we have to use the same model so let's copy and paste it here but in this case yeah this openi is created differently we are creating this one and we have the user prompt all right wonderful that's user prompt and that's the property next we want to find all those Airbnb places that correspond to my user prompt how do we do that again we need to run some SQL let's get row wait this client so what does it suggest to us we are selecting we no longer need ID but we need name of the property we need to have description we need to want the price we need to know the price next let's do this we are quick in this from Airbnb listing where the distance between the description embedding and our first argument which is going to be the vector of user prompt is as follows we want to do actually the similarity we need to subtract the distance between our description embedding and the embedding of user prompt and they will get that number between zero within the range of zero and one and we need to use the cosine distance because this operator is the idian distance which which is good for the use cases when you know exactly two points and you want to calculate the distance between two points but when you're building AI application that use large language models LMS then it's better to use cosine distance and the cosine distance is just the angle between your vectors to calculate the cosine distance all you need to do is to change your query to the following operator and then the similarity needs to be bigger or equal to what your user provided okay and next we want to order by description embedding yeah why don't we return return this similarity here you can copy and paste is going to be a similarity we want to print the similarity in the UI from arbnb listing we are ordering by similarity this sending and also we want to get only three properties so here is your embedding data representation marching threshold and that's the threshold uh that will be compared to the similarity between the description embedding and we are going to get only three of them that's done finally we need to convert those rows to the places right how do we do that yeah copilot helped us here we are going to query every row and then we are going to add all the informations to the places and also we have similarity if you remember similarity in the row similarity wonderful you made it so let's check it out let's restart our back end let's go back here and well just try to count it just takes less than a second take a look at this we managed to connect to the open eye we used embeding model to generate vectorized representation for this user prompt and then we compared that user prompt embedding to their description embeddings of our Airbnb properties that's how easy it is to use posg for that why don't you run another request let's say that I want to stay near the sale Force Tower and some coffee shops okay and you'll getting different locations that correspond to this user prompt all right my friends and these were the basics that's how easy it is to use po as a vector database to build some meaningful AI applications but the story doesn't end here in the next videos you will learn how to optimize these applications further because let's face it presently the application Stores Only 7,500 Airbnb places but what if your application stores millions and millions of embeding then you would need to optimize it further by using specialized indexes or by using a distributed postgress cluster and that's what I'm going to show you in the next video so if you are interested stay tuned like this video and subs subscribe to get notified about content on pogress and other databases bye-bye now

Channel: Denis Magda

Views: 7,364

Keywords: PostgreSQL, AI, VectorDatabase

Id: RFnZB76KVWk

Length: 16min 35sec (995 seconds)

Published: Tue Nov 28 2023