Ship faster with database branching workflows: Add prod-like data to your preview and local dev environments.

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.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Sign Up

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.

  embedding VECTOR(3)

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:

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

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:

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

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:

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

This query adds an HNSW index to the embedding column for the L2 distance function:

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

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.

ERROR: memory required is 202 MB, maintenance_work_mem is 67 MB

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.

SET maintenance_work_mem TO '205MB';


ALTER DATABASE <dbname> SET maintenance_work_mem TO '205MB';

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:

Need help?

Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.

Last updated on

Edit this page
Was this page helpful?