Pg_vectorize: Vector search and RAG on Postgres
github.com
github.com
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.
2. Chunking of your data needs to be smart. Just chunking every N characters wasn'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.
3. Use the context window effectively. There is a weighted knapsack problem here, there are chunks of various sizes (chars/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?
4. Quality of input data counts. I spent 30 minutes copy-pasting the entire book into markdown format.
This was only a small project. I'd be interested to hear any other thoughts/tips.
If you could put the entire book into the context window of Gemini (1M tokens), how do you think it would compare with your approach? It would cost like $15 to do this, so not cheap but also cost effective considering the time you’ve spent chunking it.
> Were you happy with the results?
It was somewhat ok. I was testing the system from a (copyrighted) psychiatry textbook book and getting feedback on the output from a psychotherapist. The idea was to provide a tool to help therapists prep for a session, rather than help patients directly.
As usual it was somewhat helpful but a little too vague sometimes, or missed important specific information for situation.
It is possible that it could be improved with a larger context window, having more data to select, or different prompting. But the frequent response was along the lines of, "this is good advice, but it just doesn't drill down enough".
Ultimately we found that GPT3.5/4 could produce responses that matched or exceeded our RAG-based solution. This was surprising as it is quite domain specific, but also it seemed pretty clear that GPT must have be trained on very data very similar the (copyrighted) content we were using.
Further steps would be:
1. Use other LLM models. Is it just GPT3.5/4 that is reluctant to drill down?
2. Used specifically-trained LLMs(/LORA) based on the expected response style
I'd be careful of entering this kind of arms race. It seems to be a fight against mediocre results, and at any moment OpenAI at al may release a new model that eats your lunch.
RAG and chatbot memory systems are here to stay, and they will always provide a benefit.
https://twitter.com/parthsarthi03/status/1753199233241674040
processes documents, organizing content and improving readability by handling sections, paragraphs, links, tables, lists, page continuations, and removing redundancies, watermarks, and applying OCR, with additional support for HTML and other formats through Apache Tika:
You need a human eye to figure that out and this is the task nlm-ingestor tackles.
As for the content, semantic contiguity is not always guaranteed. A typical example of this are conversations, where people engage in narrative/argumentative competitions. Topics get nested as the conversation advances, along the lines of "Hey, this remind me of ...". Building up a stack that can be popped once subtopics have been exhausted: "To get back to the topic of ...".
This is explored at length by Kebrat-Orecchioni in:
https://www.cambridge.org/core/journals/language-in-society/...
And an explanation is offered by Dessalles in:
Is there any science associated with creative effective embedding sets? For a book, you could do every sentence, every paragraph, every page or every chapter (or all of these options). Eventually people will want to just point their RAG system at data and everything works.
There is an optimal chunk size, which IIRC is ~512 tokens depending on some factors. You could hierarchically model your data with embeddings by chunking the data, then generating summaries of those chunks and chunking the summaries, and repeating that process ad nauseum until you only have a small number of top level chunks.
Another pro tip, you can again use a small model to summarize/extract RAG content to get more actual data in the context.
""" Task: Divide the provided text into semantically coherent chunks, each containing between 250-350 words. Aim to preserve logical and thematic continuity within each chunk, ensuring that sentences or ideas that belong together are not split across different chunks.
Guidelines: 1. Identify natural text breaks such as paragraph ends or section divides to initiate new chunks. 2. Estimate the word count as you include content in a chunk. Begin a new chunk when you reach approximately 250 words, preferring to end on a natural break close to this count, without exceeding 350 words. 3. In cases where text does not neatly fit within these constraints, prioritize maintaining the integrity of ideas and sentences over strict adherence to word limits. 4. Adjust the boundaries iteratively, refining your initial segmentation based on semantic coherence and word count guidelines.
Your primary goal is to minimize disruption to the logical flow of content across chunks, even if slight deviations from the word count range are necessary to achieve this. """
That being said, it seems that everyone working at the state of the art is thinking about using LLMs to summarize chunks, and summarize groups of chunks in a hierarchical manner. RAPTOR (https://arxiv.org/html/2401.18059v1) was just published and is close to SoTA, and from a quick read I can already think of several directions to improve it, and that's not to brag but more to say how fertile the field is.
[0]: https://huggingface.co/microsoft/phi-2
[1]: https://old.reddit.com/r/LocalLLaMA/comments/197kweu/experie...
It's really hard to coordinate between LLMs, a vector store, chunking the embeddings, turning user's chats into query embeddings - and other queries - etc. It's a complicated search relevance problem that'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).
I say this to get across Postgres is actually uniquely situated to bring a multifaceted approach that's not just about vector storage / retrieval.
When we looked at the pricing for vector DBs and less mature DB extensions, the real playing field shrunk down pretty quick. (And esp if you want managed & unified system wherever possible vs a NIH Frankenstein.)
You're right for many applications. Yet, for many other applications, simply converting each document into an embedding, converting the search string into an embedding, and doing a search (e.g. cosine similarity), is all that is needed.
My first attempt at RAG was just that, and I was blown away at how effective it was. It worked primarily because each document was short enough (a few lines of text) that you didn't lose much by making an embedding for the whole document.
My next attempt failed, for the reasons you mention.
Point being: Don't be afraid to give the simple/trivial approach a try.
And for the one that worked, BTW, would work almost unchanged in an application. I may put it online one day as several people have expressed interest - simply because there are no existing sites that do the job as well as my POC. The challenges will mostly be the usual web app challenges (rate limits, etc), but the actual RAG component doesn't need improvement to be the "best" one out there.
I run a directory site for UX articles and tools, which I've recently rebuilt in Django. It has links to over 5000 articles, making it hard to browse, so I thought it'd be fun to use RAG with citations to create a knowledge search tool.
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'm using LlamaIndex, OpenAI and Supabase.
It's taken a little while to figure out as I really didn't know what I was doing and there's loads of improvements to make, but you can try it out here: https://www.uxlift.org/search/
I'd love to hear what you think.
I relied on a lot of articles and code examples when putting it together so I'm happy to do the same.
Show the question I asked. I asked something like "What are the latest trends in UX for mobile?" and got a good answer that started with "The latest trends in UX design for mobile include..." but that's not the same as just listing the exact thing I asked.
Either show a chat log or stuff it into the browser history or...something. There's no way to see the answer to the question I asked before this one.
I just went back and did another search, and the results had 6 links, while the response (only) had cites labeled [7] [6] and [8] in that order.
Again, great stuff!
Good idea about showing the question, I've just added that to the search result.
Yes, I agree a chat history is really needed - it's on the list, along with a chat-style interface.
Also need to figure out what's going on with the citations in the text - the numbers seem pretty random and don't link up to the cited articles.
Thanks again!
Content is stored in Django as posts, so I wrote a custom document reader that created a new LlamaIndex document for each post, attaching the post id, title, link and published date as metadata. This gave better results than just loading in all the content as a text or CSV file, which I tried first.
I did try with a bunch of different techniques to split the chunks, including by sentence count and a larger and smaller number of tokens. In the end I decided to leave it to the LlamaIndex default just to get it working.
Given a list of separators (regexes), it goes through them in order and keeps splitting the text by them until the chunk fits within the desired size. By putting the higher level separators first (e.g., for HTML split by <h1> before <h2>), it's a pretty good proxy for maintaining context.
Which chunk size you decide on largely depends on your data, so I typically eyeball a sample of the results to determine if the splitting is satisfactory.
You can see the separators here: https://github.com/drittich/SemanticSlicer/blob/main/Semanti...
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 "good".
Maybe it was because of the type of data or the capabilities of the model at the time (GPT 3.5 and GPT 4)?
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.
Where does it shine?
It helps a lot with discovery. We have some large PDFs and also a large amount of smaller PDFs. Simply asking a question, getting an answer with the exact location in the pdf is really helpful.
Instead of performing rag on the (vectorised) raw source texts, we create representations of elements/"context clusters" contained within the source, which are then vectorised and ranked. That's all I can disclose, hope that helps.
"RAPTOR: Recursive Abstractive Processing for Tree-Organized Retrieval"
* Doing vector search with just 2 commands https://tembo.io/blog/introducing-pg_vectorize
* Connecting Postgres to any huggingface sentence transformer https://tembo.io/blog/sentence-transformers
* Building a question answer chatbot natively on Postgres https://tembo.io/blog/tembo-rag-stack
It's cool that postgresql can do this, and I've even bookmarked the project. But in my projects I expect all network code and API access like this to be on the same application layer.
For example, one might need to analyze/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!
In cases like these, can anyone recommend a more promising approach than RAG?
Large context windows can cause the LLMs to get confused or use part of the window. I think googles new 1M context window is promising for its recall but needs more testing to be certain.
Other llms have shown they reduce performance on larger contexts.
Additionally, the LLM might discard instructions and hallucinate.
Also, if you are concatenating user input, you are opening yourself up to prompt injection.
The larger the context, the larger the prompt injection can be.
There is also the cost. Why load up 1M tokens and pay for a massive request when you can load a fraction that is actually relevant?
So regardless if google’s 1M context is perfect and others can match it, I would steer away from just throwing it into the massive context.
To me, it’s easier to mentally think of this as SQL query performance. Is a full table scan better or worse than using an index? Even if you can do a full table scan in a reasonable amount of time, why bother? Just do things the right way…
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.
Personally I haven't used that many Postgres extensions, so perhaps these risks are mitigated somehow that I don't know?
On Tembo cloud, we deploy this as part of the VectorDB and RAG Stacks. So you get a dedicated Postgres instance, and a container next to Postgres that hosts the text-to-embeddings transformers. The API calls/data never leave your namespace.
https://github.com/tembo-io/pg_vectorize/blob/main/src/chat....
This would lead me to believe there is some way to actually use SQL for not just embeddings, but also prompting/querying the LLM... which would be crazy powerful. Are there any examples on how to do this?
You can provide your own prompts by adding them to the `vectorize.prompts` table. There's an API for this in the works. It is poorly documented at the moment.
1: https://medium.com/@mutahar789/optimizing-rag-a-guide-to-cho...
> General-purpose language models can be fine-tuned to achieve several common tasks such as sentiment analysis and named entity recognition. These tasks generally don't require additional background knowledge.
> For more complex and knowledge-intensive tasks, it's possible to build a language model-based system that accesses external knowledge sources to complete tasks. This enables more factual consistency, improves reliability of the generated responses, and helps to mitigate the problem of "hallucination".
> Meta AI researchers introduced a method called Retrieval Augmented Generation (RAG) to address such knowledge-intensive tasks. RAG combines an information retrieval component with a text generator model. RAG can be fine-tuned and its internal knowledge can be modified in an efficient manner and without needing retraining of the entire model.
I have a small command line python app that uses sqlite for a db. Postgres would be a huge overkill for the app
PS: is sqlite-vas good? https://github.com/asg017/sqlite-vss
Stuffing everything in PG makes sense, because PG is Good Enough in most situations that one throws at it, but hardly "very good". It's just "the best" because it is already there¹. PG truly is "very good", maybe even "the best" at relational data. But all other cases: there are better alternatives out there.
Meilisearch is small. It's not embeddable (last time I looked) so It does run as separate service; but does so with minimal effort and overhead. And aside from being blazingly fast in searching (often faster than elasticsearch; definitely much easier and lighter), it has stellar vector storage.
¹ I wrote e.g. github.com/berkes/postgres_key_value a Ruby (so, slow) hashtable-alike interface that uses existing Postgres tech for a situation where Redis and Memcached would've been a much better candidate, but where we already had Postgres, and with this library, could postpone the introduction, complexity and management of a redis server.
Edit: forgot this footnote.
Another comment mentioned meilisearch. You might be interested in the fact that a recent rearchitecture of their codebase split it into a library that can be embedded into your app without needing a separate process.
Has anyone tried using it at scale? How does it do vs pine cone / Cloudflare before search?
But it’s Postgres + pg_vector only, no Supabase
Definitely more and more vector production workloads coming to Postgres
Paper: https://www.cs.purdue.edu/homes/csjgwang/pubs/ICDE24_VecDB.p...
I'm in the early stages of evaluating pgvector myself. but having used pinecone I currently am liking pgvector better because of it being open source. The indexing algorithm is clear, one can understand and modify the parameters. Furthermore the database is postgresql, not a proprietary document store. When the other data in the problem is stored relationally, it is very convenient to have the vectors stored like this as well. And postgresql has good observability and metrics. I think when it comes to flexibility for specialized applications, pgvector seems like the clear winner. But I can definitely see pinecone's appeal if vector search is not a core component of the problem/business, as it is very easy to use and scales very easily
* Reliance on vacuum means that you really have to watch/tune autovacuum or else recall will take a big hit, and the indices are huge and hard to vacuum.
* No real support for doing ANN on a subset of embeddings (ie filtering) aside from building partial indices or hoping that oversampling plus the right runtime parameters get the job done (very hard to reason about in practice), which doesn’t fit many workloads.
* Very long index build times makes it hard to experiment with HNSW hyperparameters. This might get better with parallel index builds, but how much better is still TBD.
* Some minor annoyances around database api support in Python, e.g. you’ll usually have to use a DBAPI-level cursor if you’re working with numpy arrays on the application side.
That being said, overall the pgvector team are doing an awesome job considering the limited resources they have.
I think the same problem exists with classical/supervised machine learning. Most model's features went through some sort of transformation, and when its time to call the model for inference those same transformations will need to happen again.
PostgresML has a bunch of features for supervised machine learning and ML Ops...and pg_vectorize does not do any of that. e.g. you cannot train an XGboost model on pg_vectorize, but PostgresML does a great job with that. PGML is a great extension, there is some overlap but architecturally very different projects.
You chop up your documents into chunks. You create fancy numbers for those chunks. You take the user's question and find the chunks that kind of match it. You pass the user's question with the document to an LLM and tell it to produce a nice looking answer.
So it's a fancy way of getting an LLM to produce a natural looking answer over your chunky choppy search.
It does check the context length of the request against the limits of the chat model before sending the request, and optionally allows you to auto-trim the least relevant documents out of the request so that it fits the model's context window. IMO its worth spending time getting chunks prepared, sized, tuned for your use case though. There are some good conversations above discussing methods around this, such as using a summarization model to create the chunks.
Self-hosting can be a big advantage for cost control, but it can be complicated too. Tembo.io's managed service provides privately hosted embedding models, but does not have hosted chat completion models yet.
I only ask because making embedding model consumption based charges seem like the norm is off to me — there are a ton of open source, self managed embeddings that you can use.
You can spin up a tool like Marqo and get a platform that handles making the embedding calls and chunking strategy as well.
I see that pg_vectorize uses pgvector under the hood so it does.. more things?
So part of pg_vectorize is specific to LLMs?
vectorize.rag() requires selection of a chat completion model. Thats more specific to LLM than vector search IMO.
For example, it creates the index for you, create cron job to keep embeddings updated (or triggers if thats what you prefer), handles inserts/upserts as new data hits the table or existing data is updated. When you search for "products for mobile electronic devices", that needs to be transformed to embeddings, then the vector similarity search needs to happen -- this is what the project abstracts.