Pg_vectorize: Vector search and RAG on Postgres

Pro tip, you can use small models like phi-2 to do semantic chunking rather than just chunk on based on size, and chunking works even better if you book-end chunks with summaries of text coming before/after to enrich the context.

Another pro tip, you can again use a small model to summarize/extract RAG content to get more actual data in the context.

> Just chunking every N characters wasn't especially fruitful

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.

It seems like you put a lot of thought and effort into this. Were you happy with the results?

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.

tree-based approach to organize and summarize text data, capturing both high-level and low-level details.

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:

https://github.com/nlmatics/nlm-ingestor

We're getting very solid results.

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.

I had similar experiences. Can't understand all the hype around RAG when the results are so bad.

We built a RAG system for one of our clients in the aviation industry. >20m technical support messages and associated answers / documentation, and we're seeing between 60-80% recall for top 3 documents when testing. Definitely pays off to use as much of the structure you'll find in the data, plus combining multiple strategies (knowledge graph for structured data, text embeddings across data types, filtering and boosting based on experts experience, etc). The baseline pure RAG-only approach was under 25% recall.

have you tried Vectara?

Any chance you'd Open Source it?

Could you share more about your strategy or the approach you took for chunking the articles? I'm curious about the criteria or methods you used to decide the boundaries of each chunk and how you ensured the chunks remained meaningful for the search functionality. Thanks!

This is awesome! I just tried it out, and I have a few bits of feedback:

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!

Yes ... and no.

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.

Not sure if postgres is uniquely situated, elasticsearch can do everything you mentioned and much more.

Totally agree - the "R" in RAG is about retrieval which is a complex problem and much more than just similarity between embedding vectors.

No. Anything with a vector representation is compatible.

Personally, I'd use meilisearch in your case (though, obv, I know next to nothing about all the requirements and details of your case).

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.

Duckdb seems pretty reasonable for embeddings, as there's a native vector type and you can insert it from a dataframe. For similarity, there's array_cosine and array_dot_product.

We do, from years ago, with embedding stored as blobs (well before sqlite_vss). No SQLite extension but some user-defined functions for some distance operations and for others we are able to load all weights into memory and perform the ops there (data is small enough).

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.

fwiw, we have plenty of companies using pgvector at scale, in production - some of them migrating their workload directly from pinecone. I’ll see if I can dig up some of the high-level numbers later today

I’ve used pgvector at scale (hundreds of millions of rows) and while it does work quite well, you’ll hit some rough edges:

* 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.

There's an issue in the pgvector repo about someone having several ~10-20million row tables and getting acceptable performance with the right hardware and some performance tuning: https://github.com/pgvector/pgvector/issues/455

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

Pg_vector is great! I’m using it at scale here:

https://trendingpapers.com/

But it’s Postgres + pg_vector only, no Supabase

given the context of vector db, it would be Retrieval-Augmented Generation (RAG) [1]

1: https://medium.com/@mutahar789/optimizing-rag-a-guide-to-cho...

I found this explanation: https://www.promptingguide.ai/techniques/rag

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.

Indeed! Everyone who pushes Postgres is a win in my book.

I'll also attempt an answer.

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.

The other way round. You stick a search engine in front of your LLM to selectively feed it facts relevant to your query, which the LLM synthesizes in its own words.

To oversimplify, its more like sending a block of text to an LLM and asking it to answer a query based on that block of text.

rag is also so much faster and cheaper and energy efficient than running a giant transformer over every single document, it's like not remotely close

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…

I would agree with you. Similar to a Langchain in my mind, some interesting ideas but a lot more powerful to implement on your own. I would much rather use pgvectors directly.

We are working on a 'self-hosted' alternative to OpenAI. The project already has that for the embeddings. i.e. you specify an open-source model from hugging face/sentence-transformers, then API calls get routed to that service that you're self hosting in a container next to Postgres. This is how the docker-compose example in the project readme is set up. We'll be doing the same pattern but for chat completion models.

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.

RAG can cost a lot of money if not done thoughtfully. Most embedding and chat completion model providers charge by the token (think number of words in the request). You'll pay to have the data in the database transformed into embeddings, that is mostly a one-time fixed cost. Then every time there is a search query in RAG, that question needs to be transformed. The chat completion model (like ChatGPT 4), will charge for the number of tokens in the request + number of tokens in the response.

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.

There is no chunking built into the postgres extension yet, but we are working on it.

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.

There is a RAG example here https://github.com/tembo-io/pg_vectorize?tab=readme-ov-file#...

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.

I feel the same way. I think it's useful to think of a database as simply a fast storage and lookup for data. When it starts to run network code or run doom it breaks our expectations. This will confuse people on your team, and confusion creates security problems and tech debt.

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.

One difference between the two projects is that pg_vectorize does not run the embedding or chat models on the same host as postgres, rather they are always separate. The extension makes http requests to those models, and provides background workers that help with that orchestration. Last I checked, PostgresML extension interoperates with a python runtime on same host as postgres.

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.

Its certainly up for debate and there is a lot of nuance. I think it can simplify the system's architecture quite a bit of all the consumers of data do not need to keep track of which transformer model to use. After all, once the embeddings are first derived from the source data, any subsequent search query will need to use the same transformer model that created the embeddings in the first place.

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.