Pgai Tutorial - Vector Embeddings in PostgreSQL Made Easy

Key Takeaways

one of the problems with Factor databases is that you always need another database to handle your nonn Factor data so this could be your text Data text blops application data and you might also need lexical search which now means that you're juggling three systems and keeping all of this in sync can become a real challenge especially when it comes to your embeddings and that's why time scale argues that factor databases are built on the wrong abstraction in this article they introduce a new factorize as part of PGA aai that lets you create and sync embeddings stra from postgress and I went through the whole setup testing everything out and in this video I will walk you through that process to not only show you how you can create automated factor embeddings in postgress SQL but also help you understand the kind of like the Paradigm Shift here why it's different for you to then eventually assess whether this could be part of your AI workflow because I think they're on to something here and it's it is really an interesting approach so in this video I will walk you through the entire quick start I'll show you the code examples I will also make the repository available for you so you have everything that you need so let's dive in so the first step is to come to the repository Link in the description and I want to let you know that this video is part of a larger series that I'm doing on building rack systems using postgress so I'm a big fan of using a postgress SQL database for vector embeddings vector search and on the branches here you can see the initial video that I did the setup the hybrid search and now we're diving into PG aai you don't need to watch the other videos but it can give you some more more context just so you know now in order to follow along you need Docker desktop a post SQL gooey client an open AI key and you need python so clone the repository I have it open over here let me close this up and let's uh quickly fix the EnV so you should be familiar with how this works there are two EnV files in here I think that's the only thing that you have to keep in mind we have one in the app folder and we have one in the docker folder so the docker folder example. EMV switch that to the uh regular em plug in your openai API key and then here in the app folder same story the time scale surface URL is already in here you can leave a default plug in your openai API key more on why we're doing that in a bit but that is the first step all right what we can now do is we can come over here to the docker folder and we can see a Docker compos file in here so this contains two images we have the time scale DB postle dat datase so this is going to be your regular postle database but with some um time scale extensions and functionality already embeded in there so that is the first one and we'll provide it with the openai API key because this is going to be used by the extensions that we're going to use and we're going to spin up a vectorizer worker so I want to give you some quick context on how this works let me scroll down more in the article but essentially how this whole system works is you create a table in your poer SQL database with your regular data so let's uh say you're capturing blog post so you have one column blocks it's a text column that's in there now what we're going to do is we're going to create an embedding table that you can see over here so this is the embedding table and through the worker every time there something changes within the source data table we're going to trigger the worker to put it into a queue and process the embeddings so essentially we're creating this sync between the source data table and the embedding data table but we're doing it straight from the database so the logic is embedded in there so we don't need an additional backend or logic to keep track of that so that's how this works and I think that is the the new paradigm that they're introducing here we keep everything in sync from within the database so that's the vectorizer worker so more on how it specifically works when we when we get to it but just so you know this is the other image and it also requires the open AI a I key so with that out of the way we can come over here and open up our terminal and let me see I am already in a Docker folder so make sure that when you open up this folder over here you CD into the docker folder you can check that everything is in here and what you can run is the following command so we can do the docker compose up to start the docker container we do it in detached mode and the first time that you're running this it can take a little longer because it's going to download the images first first um this is going to start up and then if everything is configured correctly which it should be because you download it clone it straight from the repository you can see within your Docker desktop that you now have PGA running over here with two surfaces underneath your database and the worker all right so now let's look at what this database setup is doing because if you come over here to the docker folder you can see the uh plain Docker compos but we also have a init DB folder in here with some SQL queries and how this works with Docker it's going to look into this folder upon startup and it's going to uh run these files in the order that they are within within the folder so let's come back over here to see what we're doing and if you look at the quick start so again quick contact switch this is the Quick Start provided by time scale you can see their approach I essentially started with this Docker compose file and follow the instructions but they instruct you to do everything in plain squl uh meaning that meaning that you connect to your database and then run the SQL commands manually I don't like doing that manually over and over again especially if you're configuring things and constantly have to for example delete the docker image because you messed something up so what I did is I took the setup parts and I put them into SQL scripts that are going to be uh ran uh the first time that you boot this up so the first uh script over here is to in uh install two extensions so here you can see create extension and the first one is AI so this is installing the PG AI where is it this is installing the PGA AI extension within postgress and the other one is the vector scale for this tutorial we don't even need it you could leave this out but I put it in here just uh in case you can view more about that in my other videos so then the second one is simply create a new table for this tutorial we're going to use a data set of news articles so we have an ID an article highlights and we're also going to add a summary so very straightforward and then the third one this is an interesting one because this is the how we can create the vectorizer and it follows a specific syntax because as you can see we're using the AI extension here now I took this example from the quick start so coming over here so here you can see explaining how you can create these these vectorizes and there is much more information if you look into the API reference of theorizer so here you can see how you can create the configurations for everything but for now I just want you to know that we're essentially saying we're creating an embedding table we call it news embedding we call it open AI small because we're using the small model and here we're uh essentially giving some instructions on how to chunk the news articles and then also how to format it so that's all you need to know for for now so that is what is happening behind the scenes when we boot up this Docker container and create the volume for the first time all right so now that everything is up and running we can already insert the data and then check the results but let me already pull this up over here and reconnect to the database so here you can see I'm using table plus and can I zoom no I can't zoom this in right now you can see everything that we just created so we have a news table with article and highlight and we also have a embedding store similar to how the name that we specified in the SQL script and we also have a few so you'll see in a bit what that is doing but essentially going to uh take the raw data and the embeddings and put it together in a single VI so that is what you should have right now but it's still empty so now what we can do is if we create if you create a environment and install the requirements.txt that are in here you can come to the app folder and look at the Absurd file over here and what we can do over here if we load this we can load this data set so we use the CNN daily mail and I'm just going to select 10 of the articles for now so we have the subset and we can put it into a data frame and now over here you can see what it looks like an article and the Highlight so just 10 simple articles not too much and now next what we can do over here is we can add some uh context in the form of a summary so I'm just going to run this you can look into the summarize what what it's doing over here but this is inspired by contextual retrieval from entropic but in a slightly different way because we're not creating the chunks first but we're just adding some context because these articles are relatively long we're adding a summary column so that when we are later chunking we can use that summary to attach to every chunk so let me show what it looks like so now if we look into the data frame we can see of the article we have the highlight and we have a what is it one or two sentence summary and now next here is a bunch of codes that allows us to store this into the database so you can see insert into news article so here's your SQL query so again remember if you start to tweak with things make sure you update these column names and then also um correctly map the values and this is essentially it's making a connection via the database surface URL which is just loading the time scale surface URL from the environment variable I'm using a settings.py file over here it's not needed for this it's just how I like to structure my settings and I did it in the previous tutorial so now with everything in here this should be instant and we can come back to our database over here and Let me refresh this and now we can see ID article highlights summary 10 articles beautiful but now let's look over here now what we can see here is still an empty embedding stable but let me show you what is happening now behind the scenes because if I now open up a new SQL query over here and I run this so this is the ai. factorer status we can see that there are 10 pending items Now quickly coming back to the context over here this worker that is running behind the scenes so let me show you this worker that is running it is essentially the default settings are to run and process this every 5 minutes so every 5 minutes it will look into your Source data table and then if there are any changes it will process that and sync it with the embedding table and it can do all of that because we plugged in our open AI API key and we also specified remember over here in the init which model to use so embedding open AI text uh three small and it has the API key so it has all the context that it needs in order to run this so now If This Were A system that was in production what we could either do is wait so then 5 minutes will go by and then this will start to process but one thing that I found that also works is stopping just the vectorizer worker and then what we can do is we can just run that command again so come over here make sure we in the docker and just run the docker compos up but then only specify the factorize a worker and that is because we we've named that image like this and if I run this this it will boot that up again this should be here and now if we come back to the overview and we reload this this should get to zero so now all of the items that were in the queue have been processed and now here's the cool thing now we can open the embedding store and we can see all of our embeddings in here so let me visually show you what that looks like here we have an embedding ID 6 we have a chunk sequence we have the chunk itself so here you can see it first has the summary and then the chunk that it identified and then also the embedding and the ID is now linking back to the ID within your original data table and the vi that they created brings all of this together so here we can see the embedding U ID we can see the Chunk we can see the the actual chunk and also then if we scroll down a little we can see the highlights the summary and we have the whole context of everything that we need to now start before performing similarity search on this so by now I think you should get the picture and should start to see how this is this is pretty much a paradigm shift in how to manage and create and keep your factors in sync and like I've said I think they're on to something here I really like this approach and it could tackle a lot of the problems and issues that we've ran into in the past and what I also like that everything is here available within the AI schema so you can look at your vectorizes over here you can see the error logs and all of your settings are stored here in the schema as well so now that this is in place performing similarity searches is pretty straightforward so we can come to the let me close this up to the search.by so here we have the search let me show you you can see this yeah let's boot this up we have the semantic search so what we can now do here is we have another function to interact with our database and to execute a SQL command and we're going to select the uid and the Chunk from our news embedding open AI small so remember we're we're using the few over here so that's the table we're querying on and we're going to order by the embedding and here's the cool thing look at this we can now say select AI open AI embed and specify the name of the model here so not only do we abstract the embedding logic upon creation to the back end in our database we also abstract that same logic to the search and for those of you that are new to performing similarity search with both this is the cosine similarity that we're using over here as a metric and we can come in here and what we can now do is we have a question is there any news about London and we can run this search and it will take some time and there we go and this semantic search that we're doing over here you can see we have the limit specified to 15 so this is going to get 15 results from the database so that's how you can perform rack with this system you you specify your SQL query and the embeddings are taken care of by the database and the extension running on it and we also have a synthesizer in here which now essentially allows us to generate a response so you can look into the code based what what this is doing but this is for not really the scope of this video so just sending it to open AI uh with the information and the query and you can see the news related to Etc it's going to give a summary so simple prompt behind that nothing too crazy now and one thing that I still have to look into is as you can see right now here we're using plain SQL to do the similarity search and for people that have watched my previous tutorials on uh rack with postgress you might remember that we were using the time scale Vector Library so it's a pip install time scale vector and with this we could do similarity search Hybrid search metadata filtering timebase search all of that from a python client now I scrolled all the way down to the read me over here and you can see that there are time scale is already making some documentation here so there are some ways to already work with this but I haven't figured it out completely yet so just so you know if you followed the other tutorials and you're now seeing like hey but now the whole the whole vector class that we created doesn't work with this I still have to see how how those two fit together but there're I I believe they're working on it so there's information over here so so that would be a cool addition for a future video maybe so here what we did the AI extension create factorizes it sets up the automatic embedding for based on your table then when you insert update the worker detects these changes and it's going to put it in queue worker automatically generates the embeddings in this case using open AI edings are stored in a separate table kept in sync and we can now perform similarity search on this with this operator again cosign distance and this entirely eliminates the need to manage the embeddings within your application code and now while I think the major major benefit here is keeping everything in sync another really interesting thing that you can now do is compare different embedding models but very easily so let me show you what it now looks like if we were to compare open AI embeddings small with large I have some example code here in the read me as well and let me now come back to my database and run this command so we have the same news table everything is the same but we are now using the text embeding three large and also we specify the dimension for that uh model the API key but it's essenti it's exactly the same just a different model we run that we execute that now let me see let's see what happens if you if I refresh this table we have two new we have one new table and one new few so this is now also still empty but let me come to the status over here you can now see that because we created this on the news table we also have 10 10 embeddings waiting within the line so let me do this trick again to force this one more more time let me see start this up do the factorer run this one more time all right let's see all right there we go and now we also have the large embeddings in here and now look at what we can do so now if I come back to my application code over here and let's see I can now literally just change this into large and then also change this into large and this should now work oh if I run it cor correctly and there we go and now we have a result using text anding 3 large so see how there was literally like 20 seconds to set this up so now I changed this so what you ideally should do is of course run experiments so what you would do is you would set up a different one get clear on some metrics like recall groundedness correctness and then do a test to really see okay what's the difference here between for example migrating from Ada to Tex anding 3 small or difference between small and large or maybe check an embedding model from entropic or cooh here and run these tests side by side and of course it's nothing new it's also something you can do in your application code but every having everything here within your database coming back over here and just having these Separate Tables and also keeping everything in sync so now if I come over here and I add something so I could for example come in here and let's see let's just do 11 and do test whatever test maybe add this in here as well so now if I come back to the vectorizer so here you can now see that both of them have one new item in line so by setting up that factorize once we keep everything in sync so that's really cool thing here as well not only keeping your main embedding models in sync but also being able to do and run experiments in parallel because ultimately that is long term for your AI applications is going to be one of the the key success factors is where you can create continue and monitor enough experiments with everything that is going on so that's how you automate factor embeddings in postgress using PGA aai so I'm really curious to hear your take and your opinion on this is this something that would fit well for the type of projects that you are working on because I think for us it definitely makes sense I'm not sure if we can immediately migrate all of this but I'm definitely going to run some projects in parallel to see if we can really migrate to all of this because we are already within post SQL database doing everything there but now just managing our embeddings within our python application and this could really further streamline our development workflow for this and that is something that we at data Lumina are always looking for because being an AI development agency we're working with lots of projects lots of clients so we're always trying to find and optimize things and make things more manageable at scale so that's it for this video thank you for watching and now by the way if you want to learn more about how we help developers like you beyond these YouTube videos you can check out the links in the descriptions one is to our recently launched generative AI Launchpad this is for you if you want to build and deploy gen AI apps faster but we also help data professionals to get started with freelancing so if you're a developer and you've been thinking about the idea of starting your own thing becoming an independent contractor but you don't really know where to start or how to land that first client again you might want to check out the first link in the description there's a video of me going over how dat Lumina my company can help you with this now of course as with both of these these pitches over here these are funnels designed to get leads for a company so just so you know that all transparency if you're just here for YouTube and the videos that's okay but if you want to see how we work with other developers go check those out and then I want to thank you as always for watching um I'll see you in the next one and make sure to check out these videos here next this is the series that I was talking about about how to get the most out of postgress SQL uh databases when you're building rack systems