4 ms·

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 c

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 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 ago
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 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 ago
    You 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 ago
    locality 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 ago
      That's the reason I come to HN every day. Thanks for the explanation, which probably could not be more compact.
    - justjonathan 4y ago
      That’s so smart! I read stuff like that and I’m just in awe of the cleverness.
  - ec109685 4y ago
    They 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 ago
  Does this replace pinecone?
  - kiwicopple 4y ago
    Specialized 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.

kiwicopple 4y ago

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 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 ago
  You 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 ago
  locality 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 ago
    That's the reason I come to HN every day. Thanks for the explanation, which probably could not be more compact.
  - justjonathan 4y ago
    That’s so smart! I read stuff like that and I’m just in awe of the cleverness.
- ec109685 4y ago
  They 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 ago
Does this replace pinecone?
- kiwicopple 4y ago
  Specialized 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.