1

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)?

  • The vector itself could be stored as a float[] but implementing the similarity function in an efficient (=indexable) way will be challenging. It's easy to define such an operator, but its usage won't be fast. If the similarity was based only on a single array (vector) this could be done efficiently though. – a_horse_with_no_name Feb 22 at 7:19
1

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.

  • Not sure if this one does the job: github.com/jirutka/smlar – a_horse_with_no_name Feb 22 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 Feb 22 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 Feb 22 at 22:28
  • @J.Taylor did you come up with a solution? – codecraig Oct 8 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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