7

I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm working with are numpy.arrays of floats with length 100 <= L <= 1000.

I looked into the cube module for similarity search, but it is limited to vectors with <= 100 dimensions. The embeddings I am using will result in vectors that are 100-dimensions minimum and often much higher (depending on settings when training word2vec/doc2vec models).

What is the most efficient way to store large dimensional vectors (numpy float arrays) in Postgres, and perform quick lookup based on cosine similarity (or other vector similarity metrics)?

Improve this question
0

2 Answers 2

Reset to default
2

The 2023 answer to this is to use the pgvector extension.

As of this writing, the number of maximum dimensions (defined here) is 16k.

Improve this answer
2
  • 1
    Thanks! Knowing that this library exists now is tempting me to revive this old project :)
    – J. Taylor
    Commented Apr 5, 2023 at 20:32
  • 1
    You should! :-) I've used annoy for similar projects before, but one downside of that library is that the vector-index file cannot be changed after it's created i.e. you can add as many embeddings as you want into it (and later query them by distance), but you can't edit/add/delete them. For some use cases, that's ok. Postgres + pgvector is obviously more flexible. Cheers!
    – Greg Sadetsky
    Commented Apr 6, 2023 at 1:56
2

I'm not aware of any existing modules that will do this for you. It will be hard to index this in a traditional sense of jumping to a specific and small part of the index which covers all possibly-qualifying rows.

Will the vectors be centered? Normalized? Could you get away with representing them as integers with an implicit divisor, rather than true floats?

Your best hope may be to implement an index somewhat like the Bloom extension, in which it always scans the full "index" but does it in a way which is much faster than it would it be to scan the table, both because the index is smaller, and because the data is stored in a way that doesn't have to go through the type abstraction machinery but rather uses the CPUs more directly.

Improve this answer
4
  • Not sure if this one does the job: github.com/jirutka/smlar
    – user1822
    Commented Feb 22, 2019 at 18:50
  • 1
    @a_horse_with_no_name The lack of usable documentation or comments is not encouraging. A quick look at the code suggests that it is treating the floats as labels, not as numbers, and then doing Tanimoto on the label overlaps between the arrays.
    – jjanes
    Commented Feb 22, 2019 at 19:27
  • Yes, it appears that all of the similarity metrics in smlar are set-based, and it's version of "cosine similarity" isn't really correct (although I might be misunderstanding the code because my C skills are minimal, and the code is poorly documented). It looks like they're calculating the # of equal components, then dividing by the sqrt(|A| * |B|). I think this is an attempt to calculate Ochiai coefficient, but it's using the floats themselves instead of bit arrays the Ochiai algorithm is supposed to work with ...
    – J. Taylor
    Commented Feb 22, 2019 at 22:28
  • 1
    @codecraig, Unfortunately, I did not end up finding a satisfactory solution. Some people on other forums suggested using PCA or other forms of dimension reduction techniques to simplify vectors to <100 length (so I could use cube module). But this wouldn't give me the accuracy I needed. If you discover (or already know of) a way to do this, I would be very interested in hearing about it!
    – J. Taylor
    Commented Oct 14, 2019 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.