What is an efficient way to store vector data in PostgreSQL?

Question

I have some vectors, from embedding faces, and I would like to store them in a database. I need to be able to find similar vectors from the database given a referenced embedded face.

I have tried using an array type in PostgreSQL, but there isn't any support for subtraction.

1. The short-term question is: can we perform array subtraction efficiently at database level in PostgreSQL?
2. The long term question is: is there a better database system for this type of data and computation?

The specific problem is, suppose I have some vector data in a table

{1, 2, 3},
{4, 5, 6},
{7, 8, 9}

I want to figure out which one of these three vectors is closest (in Euclidean distance) to vector {5, 5, 5}.

The operations required are first to subtract two vectors, and then to find the length of the difference ||{5, 5, 5} - {4, 5, 6}||_2

In my scenario, a vector will have 128 dimensions.

Answer 1

It seems that you want to use PostGIS which is an easy extension of PostgreSQL which allows a whole bunch of geometric data type extensions. (point, vector, arc, etc.)

Answer 2

Since you want to search for vectors for embeddings and are asking for Euclidean distance, the proper PostgreSQL add-on for your use case is pgvector.

It supports the distance functions:

• Inner product
• L² distance (Euclidean distance)
• Cosine distance

L² distance is usually used for face recognition.

Cosine distance is suggested by OpenAI for their embeddings L². However, it would yield the same result.

You can find installation instructions and references to libraries for most programming languages in the link above.

If you are interested in OpenAI embeddings (and Bing brought you here):

• openai-cookbook especially the image explains a lot; instead of Qdrant, PostgreSQL could be used
• try it instantly. Here's a single-page JavaScript application that can create embeddings for you. The funny thing is they don't use a vector database, but just do it on their own in the code, and you can download all of the code (they provide a link). If you are interested in how to do it on your own, check out the JavaScript function find_closest_paragraphs in the code.