The pgvector extension
Use the pgvector for vector similarity search in Postgres
The pgvector extension enables you to store vector embeddings and perform vector similarity search in Postgres. It is particularly useful for applications involving natural language processing, such as those built on top of OpenAI's GPT models. This topic describes how to enable the pgvector extension in Neon and how to create, store, and query vectors.
Enable the pgvector extension
You can enable the pgvector extension by running the following CREATE EXTENSION statement in the Neon SQL Editor or from a client such as psql that is connected to Neon.
For information about using the Neon SQL Editor, see Query with Neon's SQL Editor. For information about using the psql client with Neon, see Connect with psql.
Create a table to store vectors
To create a table for storing vectors, use the following SQL command, adjusting the dimensions as needed.
The command generates a table named items with an embedding column capable of storing vectors with 3 dimensions. OpenAI's text-embedding-ada-002 model supports 1536 dimensions for each piece of text, which creates more accurate embeddings for natural language processing tasks. For more information about embeddings, see Embeddings, in the OpenAI documentation.
Storing vectors and embeddings
After you have generated an embedding using a service like the OpenAI API, you can store the resulting vector in your database. Using a Postgres client library in your preferred programming language, you can execute an INSERT statement similar to the following to store embeddings:
This command inserts two new rows into the items table with the provided embeddings.
Querying vectors
To retrieve vectors and calculate similarity, use SELECT statements and the built-in vector operators. For instance, you can find the top 5 most similar items to a given embedding using the following query:
This query computes the Euclidean distance (L2 distance) between the given vector and the vectors stored in the items table, sorts the results by the calculated distance, and returns the top 5 most similar items.
pgvector also supports inner product (<#>) and cosine distance (<=>).
For more information about querying vectors, refer to the pgvector README.
Indexing vectors
Using an index on the vector column can improve query performance with a minor cost in recall.
You can add an index for each distance function you want to use. For example, the following query adds an ivfflat index to the embedding column for the L2 distance function:
This query adds an HNSW index to the embedding column for the L2 distance function:
For additional indexing guidance and examples, see Indexing, in the pgvector README.
note
If you encounter an error similar to the following while attempting to create an index, you can increase the maintenance_work_mem setting to the required amount of memory using a SET or ALTER DATABASE statement.
The default maintenance_work_mem setting depends on your compute size. The SET statement changes the value for the current session. ALTER DATABASE updates the session default.
or
Always consider your compute instance's memory resources when adjusting this parameter, as setting it too high could lead to out-of-memory situations or unexpected behavior.
Resources
pgvector source code: https://github.com/pgvector/pgvector
Need help?
Join the Neon community forum to ask questions or see what others are doing with Neon. Neon Pro Plan users can open a support ticket from the console. For more detail, see Getting Support.
Last updated on