Pg_vectorize: Vector search and RAG on Postgres

I did a hobby RAG project a little while back, and I&#x27;ll just share my experience here.<p>1. First ask the LLM to answer your questions without RAG. It is easy to do and you may be surprised (I was, but my data was semi-public). This also gives you a baseline to beat.<p>2. Chunking of your data needs to be smart. Just chunking every N characters wasn&#x27;t especially fruitful. My data was a book, so it was hierarchal (by heading level). I would chunk by book section and hand it to the LLM.<p>3. Use the context window effectively. There is a weighted knapsack problem here, there are chunks of various sizes (chars&#x2F;tokens) with various weightings (quality of match). If your data supports it, then the problem is also hierarchal. For example, I have 4 excellent matches in this chapter, so should I include each match, or should I include the whole chapter?<p>4. Quality of input data counts. I spent 30 minutes copy-pasting the entire book into markdown format.<p>This was only a small project. I&#x27;d be interested to hear any other thoughts&#x2F;tips.

I think people assume RAG will just be a vector search problem. You take the user&#x27;s input and somehow get relevant context.<p>It&#x27;s really hard to coordinate between LLMs, a vector store, chunking the embeddings, turning user&#x27;s chats into query embeddings - and other queries - etc. It&#x27;s a complicated search relevance problem that&#x27;s extremely multifaceted, use case, and domain specific. Just doing search from a search bar well without all this complexity is hard enough. And vector search is just one data store you might use alongside others (alongside keyword search, SQL, whatever else).<p>I say this to get across Postgres is actually uniquely situated to bring a multifaceted approach that&#x27;s not just about vector storage &#x2F; retrieval.

Funny this has come up – I&#x27;ve literally just finished building a RAG search with Postgres this morning.<p>I run a directory site for UX articles and tools, which I&#x27;ve recently rebuilt in Django. It has links to over 5000 articles, making it hard to browse, so I thought it&#x27;d be fun to use RAG with citations to create a knowledge search tool.<p>The site fetches new articles via RSS, which are chunked, embedded and added to the vector store. On conducting a search, the site returns a summary as well as links to the original articles. I&#x27;m using LlamaIndex, OpenAI and Supabase.<p>It&#x27;s taken a little while to figure out as I really didn&#x27;t know what I was doing and there&#x27;s loads of improvements to make, but you can try it out here: <a href="https:&#x2F;&#x2F;www.uxlift.org&#x2F;search&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.uxlift.org&#x2F;search&#x2F;</a><p>I&#x27;d love to hear what you think.

Are there any examples for when RAG powered by vectorsearch works really well?<p>I tried best practices like having the llm formulate an answer and using the answer for the search (instead of the question) and trying different chunk sizes and so on but never got it to work in a way that I would consider the result as &quot;good&quot;.<p>Maybe it was because of the type of data or the capabilities of the model at the time (GPT 3.5 and GPT 4)?<p>By now context windows with some models are large enough to fit lots of context directly into the prompt which is easier to do and yields better results. It is way more costly but cost is going down fast so I wonder what this means for RAG + vectorsearch going forward.<p>Where does it shine?

Few relevant blogs on using pg_vectorize:<p>* Doing vector search with just 2 commands <a href="https:&#x2F;&#x2F;tembo.io&#x2F;blog&#x2F;introducing-pg_vectorize" rel="nofollow">https:&#x2F;&#x2F;tembo.io&#x2F;blog&#x2F;introducing-pg_vectorize</a><p>* Connecting Postgres to any huggingface sentence transformer <a href="https:&#x2F;&#x2F;tembo.io&#x2F;blog&#x2F;sentence-transformers" rel="nofollow">https:&#x2F;&#x2F;tembo.io&#x2F;blog&#x2F;sentence-transformers</a><p>* Building a question answer chatbot natively on Postgres <a href="https:&#x2F;&#x2F;tembo.io&#x2F;blog&#x2F;tembo-rag-stack" rel="nofollow">https:&#x2F;&#x2F;tembo.io&#x2F;blog&#x2F;tembo-rag-stack</a>

I don&#x27;t know how to articulate the uncomfortable feeling I&#x27;d be having, about something &#x27;inside&#x27; the database doing the download and making requests to other systems outside a boundary. It might be a security threat or just my inexperience, how common is it for postgres extensions to do this?

There&#x27;s a fair amount of skepticism towards the efficacy of RAG in these comments—often in contrast to simply using a model with a huge context window to analyze the corpus in one giant chunk. But that will not be a viable alternative in all use cases.<p>For example, one might need to analyze&#x2F;search a very large corpus composed of many documents which, as a whole, is very unlikely to fit within any realistic context window. Or one might be constrained to only use local models and may not have access to models with these huge windows. Or both!<p>In cases like these, can anyone recommend a more promising approach than RAG?

The high level API seems very smooth to quickly iterate on testing RAGs. It seems great for prototyping, however I have doubts whether it&#x27;s a good idea to hide the LLM calling logic in a DB extension.<p>Error handling when you get rate limited, the token has expired or the token length is too long would be problematic, and from a security point of view it requires your DB to directly call OpenAI which can also be risky.<p>Personally I haven&#x27;t used that many Postgres extensions, so perhaps these risks are mitigated somehow that I don&#x27;t know?

Sorry if i&#x27;m completely missing it, I noticed in the code, there is something around chat:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;tembo-io&#x2F;pg_vectorize&#x2F;blob&#x2F;main&#x2F;src&#x2F;chat.rs">https:&#x2F;&#x2F;github.com&#x2F;tembo-io&#x2F;pg_vectorize&#x2F;blob&#x2F;main&#x2F;src&#x2F;chat....</a><p>This would lead me to believe there is some way to actually use SQL for not just embeddings, but also prompting&#x2F;querying the LLM... which would be crazy powerful. Are there any examples on how to do this?

What is RAG in this context? I only know it as red, amber, green...

Has anyone used sqlite for storing embeddings? Are there any extensions or tips for making it easier?<p>I have a small command line python app that uses sqlite for a db. Postgres would be a huge overkill for the app<p>PS: is sqlite-vas good? <a href="https:&#x2F;&#x2F;github.com&#x2F;asg017&#x2F;sqlite-vss">https:&#x2F;&#x2F;github.com&#x2F;asg017&#x2F;sqlite-vss</a>

Tembo has been doing very interesting work.

I’m using pg_vector in supabase and it seems great in a prototype form.<p>Has anyone tried using it at scale? How does it do vs pine cone &#x2F; Cloudflare before search?

been a lot of these RAG abstractions posted recently. As someone working on this problem, it&#x27;s unclear to me whether the calculation and ingestion of embeddings from source data should be abstracted into the same software package as their search and retrieval. I guess it probably depends on the complexity of the problem. This does seem interesting in that it does make intuitive sense to have a built-in db extension if the source data itself is coming from the same place as the embeddings are going. But so far I have preferred a separation of concerns in this respect, as it seems that in some cases the models will be used to compute embeddings outside the db context (for example, the user search query needs to get vectorized. why not have the frontend and the backend query the same embedding service?) Anyone else have thoughts on this?

How should developers think about using this extension versus PostgresML&#x27;s pgml extension?

Is RAG just a fancy term for sticking an LLM in front of a search engine?

I assume you need to split the data to suitable sized database rows matching your model max length? Or does it do some chunking magic automatically?

How does this compare to other vector search solutions (LanceDB, Chroma, etc.)? Curious to know which one I should choose.

Neat! Any notable gotchas we should know about?

Is this only for LLMs?