Storing OpenAI embeddings in Postgres with pgvector

Summary

Post describes how embeddings represent semantic relatedness as vectors and how OpenAI’s text-embedding-ada-002 model produces 1536-dimensional embeddings for text. It addresses storing and searching large or dynamically changing collections that may not fit comfortably in memory, using PostgreSQL and the pgvector extension instead of a small CSV-and-NumPy workflow. The implementation enables the vector extension, creates a documents table with a vector(1536) column, and defines match_documents() with cosine distance, thresholds, counts, and ordering. It also shows JavaScript code for generating embeddings, a search flow that retrieves matching documents for prompt context, and token limiting before a completion request, while noting that documents can be chunked. For larger tables, the post recommends an ivfflat index with vector_cosine_ops and gives 4 * sqrt(table_rows) as a starting point for lists.

Takeaways

1. 1

   pgvector adds a vector data type and three distance operators: Euclidean distance, negative inner product, and cosine distance. The example uses cosine distance because OpenAI recommends cosine similarity for its embeddings.

2. 2

   The search flow generates one embedding for the user’s query, calls match_documents() with a threshold of 0.78 and a maximum of 10 matches, then limits combined context to 1500 tokens before sending it to a completion model.

3. 3

   For cosine-distance ordering, the example uses an ivfflat index with vector_cosine_ops; 4 * sqrt(table_rows) is given as a starting value for the number of lists.