Powering next gen AI apps with Postgres 🚀 Learn More
AI & embeddings

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.


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.


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.


pgvector source code: https://github.com/pgvector/pgvector

Need help?

Send a request to support@neon.tech, or join the Neon community forum.

Edit this page
Was this page helpful?