pgvector on Heroku Postgres

Last updated April 27, 2026

The pgvector extension for Heroku Postgres adds support for the vector data type and vector operators such as nearest neighbor search and cosine distance. Use vectors with large language models and other machine learning applications that generate embeddings in vector format.

Use Cases

With pgvector, you can:

  • Perform retrieval augmented generation (RAG): Populate the database with vectorized embeddings that represent the semantic nature of the documents indexed, such as the latest product documentation for a specific domain. Given a query, RAG retrieves the most relevant embeddings and the corresponding documents, and then uses them to augment the context of the prompt for generative AI. The AI then generates responses that are both accurate and contextually relevant.
  • Recommend products: With a vector database containing various attributes, searching for alternatives based on the search criteria is simple. For example, you can make recommendations based on similar products like dresses or shirts, or match the style and color to offer pants or shoes. You can further extend this capability with collaborative filtering where the similar preferences of other shoppers enhance the recommendations.
  • Search Salesforce data: Use Heroku Connect to synchronize Salesforce data into your Heroku Postgres database. Create a table with the embeddings since Heroku Connect can’t synchronize vector data types. For example, you can search for similar support cases with embeddings from Service Cloud cases.
  • Search multimedia: Search across multimedia content, like images, audio, and video. You can embed the content directly or work with transcriptions and other attributes to perform your search. For example, generating a music playlist by finding similar tracks based on embedded features like tempo, mood, genre, and lyrics.
  • Categorize and segment data: From industries such as healthcare to manufacturing, data segmentation and categorization are key to successful data analysis. For example, by converting patient records, diagnostic data, or genomic sequences into vectors, you can identify similar cases, aiding in rare disease diagnosis and personalized treatment recommendations.
  • Detect anomalies: Detect anomalies in your data by comparing vectors that don’t fit the regular pattern. This comparison is useful in analyzing and detecting problematic or suspicious patterns in areas such as network traffic data, industrial sensor data, transactions data, or online behavior.
  • Perform similarity searches: Perform simple vector similarity searches (VSS) based on the input query vector. Generally, you use query embeddings to search against the embeddings loaded into the database, but any vector data can work.

Provisioning

Run CREATE EXTENSION vector; to install pgvector on your database.

$ heroku pg:psql DATABASE_URL -a example-app
--> Connecting to ⛁ postgresql-octagonal-12345
psql (17.2 (Postgres.app), server 16.9 (Ubuntu 16.9-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)
Type "help" for help.


example-app::DATABASE=> CREATE EXTENSION vector;
CREATE EXTENSION

Run \dx in a psql session to check the extensions installed on your database and their current version:

example-app::DATABASE=> \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
…
 vector             | 0.8.0   | public     | vector data type and ivfflat and hnsw access methods

Alternatively, run the query to check the version of pgvector you installed:

=> SELECT extversion FROM pg_extension WHERE extname = 'vector';
 extversion
------------
 0.8.0
(1 row)

Work with pgvector

pgvector adds different vector data types that you can use like any other PostgreSQL columns.

For example, to create an animals table with a standard vector column called embedding:

CREATE TABLE animals(id serial PRIMARY KEY, name VARCHAR(100), embedding VECTOR(100));

Alternatively, you can expand an existing table in your database to add an existing vector column:

ALTER TABLE fruit ADD COLUMN embedding VECTOR(100);

Insert vector data as:

INSERT INTO animals(name, embedding) VALUES ('llama', '[-0.15647223591804504,
…
-0.7506130933761597, 0.1427040845155716]');

Perform Vector Queries

The pgvector extension provides different operators for vector data. Some common query vector operators are:

<->: Euclidean Distance

The Euclidean distance, or L2 distance, operator measures the straight-line distance between two points in a vector. This operator is best for searching for an alternative item or most similar item to the query.

This example uses the Euclidean distance operator to search for animals similar to a shark in the animals table:

=> SELECT name FROM animals WHERE name != 'shark' ORDER BY embedding <-> (SELECT embedding FROM animals WHERE name = 'shark') LIMIT 5;
 name
-----------
 crocodile
 dolphin
 whale
 turtle
 alligator
(5 rows)

<#>: Negative Inner Product

The negative inner product operator measures the orthogonal projection, which is whether the vectors point in the same or opposite direction with magnitude. The greater the inner product means a greater similarity between two vectors. This operator is best for searching for items of similar topics and similar in magnitude. For example, image identification where an image of a shark yields the highest inner product against the embedding of a shark.

<=>: Cosine Distance

The cosine distance operator measures the cosine of the angle between two vectors. The value ranges from –1 to 1, with values closer to 1 representing greater similarity between the vectors. This operator is best for similarity searches where it’s better to omit the magnitude. For example, anomaly detection where the frequency doesn’t matter, or text-based search for whole paragraph and document semantics.

Performance

Query performance can degrade as you add more vector data to your database. You can index vector data like other columns in Postgres. The pgvector extension supports multiple index types. Keep in mind:

  • Adding an index to a vector column causes pgvector to switch to using approximate nearest neighbor search instead of exact nearest neighbor search. This scenario can change query results.
  • Indexing functions are based on distance calculations. Create functions based on the calculation you plan to rely on the most in your app.

You can also adjust Postgres’ parallelization settings to optimize your database’s performance.

For example, max_parallel_workers_per_gather sets the maximum number of workers that a single Gather or Gather Merge node can start. Increasing this setting can help speed up queries without creating an index.

SET max_parallel_workers_per_gather = 4;

Use the SHOW command with the setting name to check the current setting value:

=> SHOW max_parallel_workers_per_gather;
 max_parallel_workers_per_gather
---------------------------------
 2
(1 row)

See the pgvector documentation for guidance on creating indexes and optimizing performance on vector data types.

Indexes

pgvector supports two index types: HNSW (Hierarchical Navigable Small Worlds) and IVFFlat (Inverted File with Flat Compression index). Depending on the type of index you select, there are common trade-offs including speed, recall, or the quality of the query result, build times, and resource consumption.

The default approach to work with vector columns is having no index on them, as pgvector is configured for exact nearest neighbor search. The default allows for perfect recall at the expense of speed. By adding an HNSW or IVFFlat index, you can perform approximate nearest neighbor (ANN) searches.

HNSW

HNSW is the preferred index type for ANN searches as it performs better than IVFFlat in most use cases. However, HNSW indexes take longer to build and take more memory. The algorithm builds multi-layer graphs that allow for fast search results. The index is built as you insert data into the table, so you can create these indexes without any data.

IVFFlat

You can use IVFFlat indexes for improving performance on ANN searches, including searches against high-dimensional embeddings. Since the IVFFlat relies on an existing vector, it’s best to build the index after the table has data in it for better recall. If the data distribution changes significantly, rebuild the index. IVFFlat indexes have faster build times and take less memory than HNSW, but it has worse query performance.

Caveats

Examples