6 ms·
Storing OpenAI embeddings in Postgres with pgvector
- kiwicopple 4y agoHey HN, this one has a cool back story with it that shows the power of open source. The author, Greg[0], wanted to use pgvector in a Postgres services, so he created a PR[1] in our Postgres repo. He then reached out and we decided it would be fun to collaborate on a project together, so he helped us build a "ChatGPT" interface for the supabase docs (which we will release tomorrow). This article explains all the steps you'd take to implement the same functionality yourself. I want to give a shout-out to pgvector too, it's a great extension [2] [0] Greg: https://twitter.com/ggrdson https://twitter.com/ggrdson [1] pgvector PR: https://github.com/supabase/postgres/pull/472 https://github.com/supabase/postgres/pull/472 [2] pgvector: https://github.com/pgvector/pgvector https://github.com/pgvector/pgvector
- kiwicopple 4y agoTo summarise the article if you're skipping to the comments, the pgvector allows you to create a "vector" type in your database create table documents ( id bigserial primary key, content text, embedding vector (1536) ); Then you can use OpenAI's Embedding API[0] to convert large text blocks into a 1535-dimension vector, which you will store in the database. From there you can used pgvector's cosine distance operator for searching for related documents You can combine the search results into a prompt, and send that to GPT for a "ChatGPT-like" interface, where it will generate an answer from the documents provided [0] https://platform.openai.com/docs/guides/embeddings https://platform.openai.com/docs/guides/embeddings
- dilippkumar 4y ago> From there you can used pgvector's cosine distance operator for searching for related documents How does this scale with the number of rows in the database? My first thoughts are that this is O(n). Does the pgvector have a smarter implementation that allows performing k-nearest neighbor searches efficiently?
- pedrosorio 4y agoYou can add indexes per distance function to perform fast approximate nearest neighbor search: https://github.com/pgvector/pgvector#indexing https://github.com/pgvector/pgvector#indexing They mention this paper in the references: https://dl.acm.org/doi/pdf/10.1145/3318464.3386131 https://dl.acm.org/doi/pdf/10.1145/3318464.3386131
- anon291 4y agolocality sensitive hashing is the typical method here. You generate a ton of random vectors. These vectors automatically give rise to infinite hyperplanes (the one they are normal to). Each vector/hyperplane is associated with a bit in the final hash. Then, each input vector is hashed by setting the bit if it's on one side of the hyperplane or unsetting it if it's on the other. The hamming distance between two vectors is now correlated with the cosine similarity. Or something like that.
- kekub 4y agoThat's the reason I come to HN every day. Thanks for the explanation, which probably could not be more compact.
- justjonathan 4y agoThat’s so smart! I read stuff like that and I’m just in awe of the cleverness.
- ec109685 4y agoThey describe optimizations here as well: https://simonwillison.net/2023/Jan/13/semantic-search-answers/ https://simonwillison.net/2023/Jan/13/semantic-search-answer...
- moneywoes 4y agoDoes this replace pinecone?
- kiwicopple 4y agoSpecialized databases will always outperform Postgres, which is a "jack of all trades". It really depends on your use-case, but I imagine if you need anything beyond basic vector support then Pinecone is a better option.
- arcturus17 4y agoI'll soon be releasing a CLI app that creates embeddings for entire Youtube channels and actually looked whether Supabase offered a pgvector plugin, but seeing as a couple weeks ago it didn't, I ended up going for Pinecone. I will add a mention to this in the docs.
- kiwicopple 4y agowe merged the pgvector PR about 2 weeks ago (https://github.com/supabase/postgres/pull/472 https://github.com/supabase/postgres/pull/472). If you're missing anything for your CLI don't hesitate to reach out and we'll see if we can integrate it into the product (my email is in my profile) as an aside, Pinecone looks great
- gk1 4y agoLooking forward to it. If we (Pinecone) can help with anything shoot me an email! greg@pinecone.io
- itake 4y agoI tested pgvector against a vanilla fais index and pgvector was significantly slower with 511d vectors. If you have a small dataset (less than 100k?) vectors, its probably fine, but for larger storage, I would look at a distributed vector search provider.
- swyx 4y agodo you transcribe the youtube videos or what do you mean by embeddings for youtube channels?
- arcturus17 4y agoYes, the CLI is a pipeline that fetches audio -> transcribes -> creates embeddings.
- leobg 4y agohttps://github.com/nmslib/hnswlib https://github.com/nmslib/hnswlib Used it to index 40M text snippets in the legal domain. Allows incremental adding. I love how it just works. You know, doesn’t ANNOY me or makes a FAISS. ;-)
- fzliu 4y agoFirst time I've heard of pgvector - for folks with experience, how does it compare to other ANN plugins (i.e. Redis https://redis.io/docs/stack/search/reference/vectors/ https://redis.io/docs/stack/search/reference/vectors/) and purpose-built vector databases (i.e. Milvus https://milvus.io https://milvus.io)? Curious about both performance/QPS and scale/# of vectors.
- txtai 4y agoThis is a really nice article by Dmitry Kan on number of popular vector databases. https://towardsdatascience.com/milvus-pinecone-vespa-weaviate-vald-gsi-what-unites-these-buzz-words-and-what-makes-each-9c65a3bd0696 https://towardsdatascience.com/milvus-pinecone-vespa-weaviat...
- losteric 4y agoThis link does not cover pgvector
- visarga 4y agoPlayed with GPT-3 embeds for the first time this week and they seem to be unusually good. Everything >0.85 cosine similarity is a match, everything under 0.76 is not a match, and in the 0.76 .. 0.85 is the boundary zone where some positives and negatives get mixed up. Training models on top works great, even averaging 2-3 phrases works great for making a more precise query.
- bigpeach 4y ago> Training models on top works great, even averaging 2-3 phrases works great for making a more precise query. What does this mean?
- visarga 4y agoIf you average a query embedding with that of similar terms, you get a more focused ranking. Averaging amplifies the common component of the vectors and diminishes the spurious components.
- chipgap98 4y agoWhat do you mean by "average a query embedding"?
- CardenB 4y agoWhen you input a query, you get a numerical representation output. If you have 3-5 similar queries (each phrased a bit differently), they all have slightly different outputs. If you average them all together, it combines them in a way that improves results.
- teaearlgraycold 4y agoPresumably this operation: def average_embeddings(embeddings): return [ sum([embedding[x] for embedding in embeddings]) / len(embeddings) for x in range(len(embeddings)) ] Just finding the mid-point in N-dimensional space between all points. Although I believe OpenAI says these are directions rather than locations. Averaging makes sure you don't accidentally pick a target vector that happens to be on the fringes of your desired embedding space. So if you're querying a document for predatory birds and you are using the string "Eagles" as your target you will be accidentally digging into embedding space for sports teams (go birds). But if your target is `average_embeddings([eagles_embedding, hawks_embedding, falcons_embedding])` you'll end up with less noise.
- simonw 4y agoI've been experimenting with something similar to this on top of SQLite. My experiments so far have involved storing the embeddings as binary strings representing the floating point arrays in a SQLite blob column: openai-to-sqlite is my tool for populating those: https://datasette.io/tools/openai-to-sqlite https://datasette.io/tools/openai-to-sqlite I then query them using an in-memory FAISS vector search index using my datasette-faiss plugin: https://datasette.io/plugins/datasette-faiss https://datasette.io/plugins/datasette-faiss
- deleted 4y ago[deleted]
- xrd 4y agoAwesome, I came here to ask about SQLite and you already answered it! I love your writing, Simon. Have you blogged about this yet?
- simonw 4y agoYeah I wrote these up in https://simonwillison.net/2023/Jan/13/semantic-search-answers/ https://simonwillison.net/2023/Jan/13/semantic-search-answer... a few weeks ago.
- jabo 4y agoDisclaimer: I work on Typesense [1] (an open source alternative to Algolia + Pinecone) and we recently added Vector Search as a feature to Typesense [2]. Postgres can do a lot of things, but for large enough datasets and/or when you want to add filtering into the mix along with vector search, then it becomes slow. And at that point you want to use a dedicated vector search database. It's similar to how Postgres can also do full text search, but for large datasets and/or you want to add typo tolerance, faceting, grouping, filtering, synonyms, etc - the usual features you'd need when implementing a search feature - then it becomes slow to do this in pg and you'd then use a dedicated search engine. In Typesense, we've now combined Vector Search along with filtering based on attributes in your documents, so you get the best of both worlds [2], and we made it fast with an in-memory index. [1] https://typesense.org/ https://typesense.org/ [2] https://typesense.org/docs/0.24.0/api/vector-search.html https://typesense.org/docs/0.24.0/api/vector-search.html
- qigtyofhp 4y agoThis is an interesting feature. But you don't need to use just OpenAI's embeddings. You can generate your own embeddings with open source SOTA transformer models which would probably work just the same. You could generate a couple hundred thousand embeddings with a rented A100 for less than 2 dollars. And the point of converting text or other objects (like images) into embeddings is to compare a large number of documents to a source document very fast. It's more useful to put the embeddings in something like Redis. This pgvector data type would be good for an offline backup of vectors.
- mattnewton 4y agoDoes redis let you do some kind of similarity based lookup? Is that new?
- freeqaz 4y agoDo you have any examples that come to mind for this? I'd love to understand what other models exist and what Redis extensions exist to compare embeddings.
- qigtyofhp 4y agoThis will load the transformer models from Hugging Face (their models have a similar architecture to OpenAI embedding models): https://www.sbert.net/docs/pretrained_models.html https://www.sbert.net/docs/pretrained_models.html Redis has approximate nearest-neighbors vector similarity search: https://redis-py.readthedocs.io/en/stable/examples/search_vector_similarity_examples.html https://redis-py.readthedocs.io/en/stable/examples/search_ve... Generate the embeddings on a rented GPU, push to Redis then do a similarity search. Store vectors in Redis using ndarray.tobytes()
- e12e 4y ago> And the point of converting text or other objects (like images) into embeddings is to compare a large number of documents to a source document very fast. But a "source document" could also be a natural language query that you need to convert to an embedding - if you want to enable natural language search on your corpus? (maybe along with handling queries in French getting good semantic hits in English etc?)?
- AndrewKemendo 4y agoFantastic article and I really love how the author explains embeddings - very well done. I love also the practicality with basic postgres, very very powerful stuff here.
- schappim 4y agoI agree with other comments, this is an outstanding article and you should read it if you haven't already. Is it correct that if OpenAI alters the embeddings model, such as they did with Ada on 22 December, you need to generate all your embeddings again?
- vore 4y agoYes, if the nodes had their weights retrained. The resulting output vector from the model would subsequently have different values and would not be meaningful if used against a different set of weights.
- woodson 4y agoOne downside of pgvector is that it currently only supports one type of index (ivfflat), while others (FAISS, Milvus, qdrant, etc.) support other types of indices that can be advantageous depending on your workload (properties of vectors, size of dataset). See [1] for some more background. [1] https://github.com/facebookresearch/faiss/wiki/Guidelines-to-choose-an-index https://github.com/facebookresearch/faiss/wiki/Guidelines-to...
- whakim 4y agoDoes anyone know if it's possible to make this work on RDS (e.g. by getting Amazon to install it for you or something?) Being able to store sentence embeddings in Postgres and compute similarity on-the-fly would save a lot of work!
- seanlinehan 4y agoYou can request it: https://aws.amazon.com/rds/postgresql/faqs/ https://aws.amazon.com/rds/postgresql/faqs/
- nicoslepicos 4y agoIf anyone wants to get an A.I. assistant for their docs page, I've been working on Pal (http://www.heypal.chat http://www.heypal.chat) these past few weeks. I'm onboarding folks, and if you have Algolia, Gitbook, Readme or most common docs platform it takes minutes to set up. It also integrates with your Slack and Discord (both in terms of letting people ask questions and get responses from the assistant in those tools, and marking conversations you want the assistant to know about for the future). Would love more folks to try it out if anyone's interested, and think their product would benefit from it!
- gorbypark 4y agoHow do you think this would work with a semi-large Discord channel related to programming? Discord (and IRC, etc) are great for forming vibrant communities, but past knowledge is lost pretty quickly. Forums (eg: web forum or Discord forums/topics) are great for keeping information more accessible, but in my opinion really hinders the growth of a community.
- osigurdson 4y agoIt seems that it would make sense to reduce the dimensionality for similarity comparisons.
- sandGorgon 4y agoquick question - can a tsvector not be repurposed to do this exact same thing. i know tsvector is text search. but the advantage is that "similarity" semantics kick in with tsvector - with things like rankings, relevance, etc. so i think we can do trigram matching (i could be wrong..been a while since i did this) while pgvector seems to be doing cosine distance matching. im not sure which is better, but tsvector will support the popular RUM plugin for cosine similarity. any thoughts here ?
- deleted 4y ago[deleted]
- hp12571236 4y agoI stored sentence transformers in elasticsearch and used its cosineSimilarity function to find similar documents. I'm wondering if maybe if makes sense to use pgvector? or sqlite? instead
- uh_uh 4y agoI was tinkering with pgvector recently but I was getting inconsistent results. Sometimes Postgres just failed to use the index and resorted to a brute force search which is very slow. Sometimes the index worked and IIRC I managed to sort 500K vectors (limit=10 or so) with dimension size 1536 in about 80ms which is okay but not state of the art. I was using an artificial dataset which might have biased the index statistics resulting in poor execution. Mind you that this was all running in WSL and I'm very new to vector search so I might not know what I'm doing.
- runnerup 4y agoDoes OpenAI have an API like this to get the embeddings of images?
- dventimihasura 4y agoQuestion: How is pgvector applied in this way different from and maybe superior to the PostgreSQL cube extension? https://www.postgresql.org/docs/current/cube.html https://www.postgresql.org/docs/current/cube.html The cube datatype also stores arrays of numbers (cubes or vectors, same thing) and has functions/operators for computing distance. Granted, they're slightly different, with no direct substitute for cosine distance, but on the unit sphere the cosine distance, inner product, and Euclidean distance have direct correspondences.
- saurik 4y agoI looked into this, and (apparently) the cube extension has a very small hard-coded dimensionality limit that isn't sufficient for this purpose; you can recompile it to support more dimensions, but the way it stores data requires the entire vector to still fit within a single database page (which these vectors will not).
- dventimihasura 4y agoThanks!