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. Feb 22 '19 at 7:19

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 Feb 22 '19 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 '19 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 '19 at 22:28
  • 1
    @J.Taylor did you come up with a solution?
    – codecraig
    Oct 8 '19 at 17:32
  • 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
    Oct 14 '19 at 20:55

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.