[HN Gopher] Storing OpenAI embeddings in Postgres with pgvector
       ___________________________________________________________________
        
       Storing OpenAI embeddings in Postgres with pgvector
        
       Author : kiwicopple
       Score  : 38 points
       Date   : 2023-02-06 21:24 UTC (1 hours ago)
        
 (HTM) web link (supabase.com)
 (TXT) w3m dump (supabase.com)
        
       | arcturus17 wrote:
       | I'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.
        
         | gk1 wrote:
         | Looking forward to it. If we (Pinecone) can help with anything
         | shoot me an email! greg@pinecone.io
        
         | itake wrote:
         | I 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.
        
         | kiwicopple wrote:
         | we merged the pgvector PR about 2 weeks ago
         | (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
        
       | visarga wrote:
       | Played 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.
        
       | simonw wrote:
       | I'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
       | 
       | I then query them using an in-memory FAISS vector search index
       | using my datasette-faiss plugin:
       | https://datasette.io/plugins/datasette-faiss
        
       | kiwicopple wrote:
       | Hey 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
       | 
       | [1] pgvector PR: https://github.com/supabase/postgres/pull/472
       | 
       | [2] pgvector: https://github.com/pgvector/pgvector
        
         | kiwicopple wrote:
         | To 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
        
           | dilippkumar wrote:
           | > 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?
        
       | fzliu wrote:
       | First 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/) and
       | purpose-built vector databases (i.e. Milvus https://milvus.io)?
       | 
       | Curious about both performance/QPS and scale/# of vectors.
        
       ___________________________________________________________________
       (page generated 2023-02-06 23:00 UTC)