Speed up PostgreSQL® pgvector queries with indexes

Learn the theory and the details of how to speed up PostgreSQL® pgvector queries using indexes IVFFlat, HNSW and traditional indexes

Subscribe to RSS

Using AI, it’s possible to find similar text information, photos or products in a database. As the number of searches increases, performance can be a problem, though.In this article, I’ll show you how indexes can help.

We’re going to use PostgreSQL’s pgvector extension. It enables you to store AI embeddings, which are representations of information, and enables you to perform similarity searches on them. Pgvector has a couple of features that make it particularly popular. It supports hybrid searches, mixing standard and vector queries. For example, someone shopping online might want to search for shoes that look like a photo of their existing pair (a vector search), and that cost less than $100 (a standard database query). Pgvector also enables retrieval-augmented generation (RAG), where information from the database gives additional context to generative AI, improving the quality of the output and reducing hallucinations.

This tutorial explains:

The basics of vector similarity
How pgvector can be used for vector similarity searches
How indexes of various types can help to speed up searches
The possible tradeoffs in output quality when using indexes

See our previous blog for a refresher on what embeddings are and how pgvector can be used for face recognition.

To get insights about your database performance and improvement suggestions, check out EverSQL by Aiven

Understanding vector queries

In the AI world, embeddings are a representation of a piece of information (such as text, image, sound, or video). They are expressed as an array of numbers.

AI models such as OpenAI’s text-embedding-ada-002 can generate embeddings from text (see the image below). These embeddings are always the same length, no matter how long the source text is. In the case of ada-002, the embeddings are 1536 elements long. The embeddings can be compared to find text with a similar meaning.

But how does this embedding comparison work? We can picture the embeddings as points in an N-dimensional space. In the case of text-embedding-ada-002, it would be a 1536-dimension space.

To find information similar to a particular search query, the AI model would:

Create an embedding for the search query, using the same method used for the stored data. Information with a similar meaning should be represented by embeddings with similar values. These embeddings would be closer to each other in the N-dimensional space.
Search the N-dimensional space to find the embeddings that are closest to the search query.

It's hard to imagine a 1536 dimension space, so to keep things simple, we’ll visualize the data in a two-dimensional space. In the image below, you can see the search query embedding in yellow in the center, and the six closest results we’d want to find in red.

In that image, we’re plotting the data as coordinates. However, the embeddings can be considered as vectors. These are mathematical objects that have a length and direction. They’re represented by an arrow that starts at the graph’s origin at [0,0] in the bottom left and extends to the vector position. You’ll see these shortly.

There are several ways that the similarity of vectors can be measured. Let’s create a PostgreSQL environment where we can explore the different approaches using pgvector.

Setting up a PostgreSQL playground

We’re going to spin up an Aiven for PostgreSQL database. Follow these steps:

Access the Aiven Console.
Select Create service.
Select Aiven for PostgreSQL.
Select the cloud provider and region.
Select the plan (the hobbyist or even the free plan would be enough).
Provide the service a name, such as pg-pgvector-index-test.
Click on Create service.
Connect to PostgreSQL. To do this, wait until the service is in the RUNNING state, grab the Service URI and, with psql already installed locally, connect to PostgreSQL with:
```
Loading code...
```
Create the pgvector extension using CREATE EXTENSION vector;
Add some data using the following code:
```
Loading code...
```

We now want to calculate the similarity between the two vectors (VectorA and VectorB) and a third vector (VectorC) with coordinates [4,4], as shown in the image below.

Understanding how similarity metrics work

Similarity metrics define the distance between two embeddings (or vectors). There are a lot of different similarity metrics we could use, but we’ll define the three basic ones: distance, inner product and cosine.

L2 Distance

The distance similarity metric calculates the Euclidean distance between two vectors using the formula $\sqrt{Δx^2+Δy^2}$ , as shown in the image below. You can visualize this by drawing a right-angled triangle, with the long side connecting the vectors. To calculate the length of the line connecting the vectors length, use Pythagoras’ theorem.

Embeddings with a lower distance metric are closer together in the space, and so represent content with a more similar meaning.

The distance similarity is measured using PostgreSQL <-> operator.

The similarity between VectorA and VectorC is calculated as

$\sqrt{(4-2)^2+(4-3)^2}$ which equals 2.23606797749979. The similarity between VectorB and VectorC will be $\sqrt{(8-4)^2+(4-1)^2}$ , which equals 5.

Let’s run the following query:

Loading code...

The result is what we expected:

Loading code...

The Euclidean distance is ideal for finding similar vectors if all the components of a vector have equal importance, and if they are normalized so the numbers are on the same scale (for example, between 0 and 1).

L1 distance

Comparing to L2 distance, L1 distance, also known as Manhattan distance or taxicab distance, calculates the distance between two points by summing the absolute differences of their coordinates.

L1 distance can be used to measure the dissimilarity between vectors in a high-dimensional space. Unlike L2 distance, which computes the Euclidean distance and is sensitive to large differences due to its squared terms, L1 distance treats all dimensions equally and is more robust to outliers. This makes L1 distance particularly useful in scenarios where the data may contain significant noise or when the scale of differences across dimensions varies greatly.

However, be aware that L1 distance might not capture the true geometric distance in a multidimensional space as effectively as L2 distance. Therefore, the choice between L1 and L2 distance in pgvector depends on the specific characteristics of the data and the requirements of your application.

L1 distance similarity is measured using <+> or a vector function l1_distance:

Loading code...

Cosine

We said that the distance metric is ideal for normalized data, but what if there could be large differences in the scale of the data?

The cosine similarity calculates the difference in the angle between the two vectors, measuring how closely the two vectors are pointing in the same direction. The cosine similarity metric does not otherwise consider the vectors’ positions in the space. The vector positions could be a long way apart.

For example, imagine a certain element contains the “number of times Pizza is mentioned in the document”, with the length of the line representing the frequency and the position of the vector representing the idea of pizza. You could use the cosine distance to find other documents containing the idea of Pizza, rather than focusing on documents that mention Pizza a certain number of times. By contrast, a distance search on this element might be more likely to find items with a similar frequency, than items with a similar idea.

In the case of VectorA and VectorC the cosine difference is calculated as $((Xa*Xc)+(Ya*Yc))/(\sqrt{Xa^2+Ya^2)}*\sqrt{Xc^2+Yc^2)})$ , with the result being 0.9805806756909202.

The distance similarity is measured using <=>.

Let’s try it:

Loading code...

The result is in line with the above calculation. The angle between VectorA and VectorC is way smaller than the one between VectorB and VectorC, therefore the cosine similarity of the VectorA is greater. In the output, a higher number represents greater similarity.

Loading code...

Inner product

The inner product allows you to measure both the space between vectors and how well aligned they are. Although the inner product works well where the elements of a vector are normalized and have equal importance, it also works where not all the elements have an equal weight.

To see how it’s useful, imagine you have an AI model that creates elements expressing how often a certain word appears in a document. The word Margherita would appear often in articles about pizza and about queens of Italy. These are two totally different contexts, of course. Using the inner product, you can get a sense of whether a query and a result have a similar direction, which will reflect how close their meanings are. You can also get a sense of scale: how many mentions there are of the word.

The inner product calculates the area of the parallelogram created when you join the vectors as shown in the image below.

The calculation is made by multiplying each of the coordinates and then summing the result with the formula $(Xa*Xc)+(Ya*Yc)$ . This is known as the dot product. The result, in the case of VectorA and VectorC, is (2*4)+(3*4)=20.

The inner product is measured using <#>.

Try executing the query against the PostgreSQL database with this code:

Loading code...

We multiply the result by -1 because PostgreSQL returns the negative inner product and we want to make the result positive. The result is in line with the formula:

Loading code...

Hamming distance

Hamming distance measures the difference between two binary vectors by counting the number of positions at which the corresponding elements differ. It is primarily used in error detection and correction, where it is crucial to determine how many bits need to be changed to transform one binary string into another:

Hamming distance is measured using <~> or hamming_distance function.

Loading code...

Jaccard distance

Jaccard distance, on the other hand, can be used to compute the similarity between two asymmetric binary variables.

This metric is widely used in various data science applications, such as text mining to measure document similarity based on shared terms, in e-commerce to find customers with similar purchase histories, and in recommendation systems to suggest products based on shared preferences. It is particularly useful for asymmetric binary variables, where the presence of an attribute (e.g., an item purchased) is more significant than its absence.

Hamming distance is measured using <%> or jaccard_distance function.

Loading code...

Testing the vector similarity theory at scale

As we have just seen, each coordinate needs to be evaluated to calculate the vector similarity.. If our data set has N rows containing vectors with M dimensions, we’ll need to perform at least N*M calculations to measure the similarity.

If we are working with long vectors, such as the ones coming from AI embeddings (usually in the range of 1000 elements), we would need to perform at least 1000 calculations for every row in our data set to calculate full vector similarity. This would not scale well, but there are several optimizations that can help.

To experiment, let’s download the OpenAI pre-computed Wikipedia article embeddings, which are available in the OpenAI Cookbook. The file contains embeddings for 25,000 Wikipedia articles.

Follow these steps:

Open the terminal.
Download the zip file:
```
Loading code...
```
Unzip it with:
```
Loading code...
```
Connect to PostgreSQL:
```
Loading code...
```
Create a table named articles:
```
Loading code...
```
Load the table with 25,000 articles and related embeddings:
```
Loading code...
```

Now, if we want to retrieve the top 10 articles which have a similar meaning to Pineapple Pizza we can issue the following query using the distance similarity:

Loading code...

The vector defined in the data section is the embedding of Pineapple Pizza generated using the OpenAI text-embedding-ada-002 model. This is the same model that was used to calculate the embeddings of the Wikipedia data set.

The query results in the following 10 articles. The top articles are closely related to pineapple and/or pizza. Hawaiian Punch is semantically similar, as a food or drink item with a pineapple flavor. Afelia and pie are also food items. Lanai is known as the Pineapple Island because pineapples were grown there previously. We’re not sure why the New Zealand island of Te Puke appears in the results.

Loading code...

The above query returns in 447.857 ms, since the data set is quite small. Still, in order to provide the results, PostgreSQL had to:

Scan all the rows, where each row represents an article.
Perform, for each row, a calculation over the 1536 elements.
Order the results based on the similarity score.
Retrieve the top 10 results.

Such an operation cannot scale to huge data volumes because it’ll need to perform at least N*1536 calculations for each query. It’s worth remembering that there are more than six million pages in the English Wikipedia, and we’re only using 25,000 of them.

So, how can we speed it up?

Introducing indexes in pgvector

A common solution to speed up queries in a database is to use indexes. They usually enable you to look up some column values quickly without losing accuracy.

This is not true for pgvector indexes, which can index one or more columns containing embeddings. To speed up the performance, we need to make accuracy tradeoffs.

If we want to optimize the performance of our query above, we need to take some shortcuts in the four execution steps. The ordering and retrieval are essential for our query. The only opportunities for optimization are avoiding scanning all the rows or performing the similarity search based on a subset of the 1536 elements.

Indexes to avoid a full table scan

Let’s look at indexes that allow us to avoid scanning and comparing the input vector with all the rows in the data set. These indexes work by segmenting the vector space and allowing us to perform a top-down approximate search.

The two main indexes of this type in pgvector are IVFFlat and HNSW.

Using the IVFFlat index

The Inverted File with Flat Compression (IVFFlat) index groups vectors in clusters. The centroid is the middle of all the points in a cluster. When searching for a vector similar to an input, the first step is to find the nearest centroids and then look into the subset of vectors within their clusters.

In the image below, the search algorithm will look into the nearest clusters (Cluster A and Cluster C) but might skip all the points in Cluster B.

The accuracy and speed of the results depends on the effectiveness of the clustering algorithm. IVFFlat provides two parameters you can tune:

lists: the maximum number of vectors in a cluster. All clusters have the same maximum number of vectors, but the actual number of vectors in a cluster can vary.
probes: the number of clusters to examine during the search.

Tuning the IVFFlat lists parameter

A smaller lists parameter creates smaller clusters, with less distance between the vectors and the centroid. However, it increases the number of clusters, making the search less efficient. The image below shows how a change in the lists parameter from 5 to 8 might affect the cluster composition.

The number of clusters and the distance of the centroid affect the accuracy of the results. In the following visualization we can see how the 4 similar vectors from the input might change in the case when only the closest cluster is inspected. Note that in both cases, one of the closest results is not discovered when the index is used.

How to define the IVFFlat list parameter

The lists parameter is defined during the index creation stage. The pgvector documentation suggests using the following formula for lists as a starting point:

rows / 1000 for up to 1 million rows
sqrt(rows) for over 1 million rows

For our testing purposes, let’s create an index with a maximum of 200 vectors in a cluster:

Loading code...

Creating the index took approximately 15 seconds in my test environment.

Note: Increasing the lists parameter will require more working memory. If your working memory is not enough you could get an error like this:

Loading code...

In such cases, check your server has enough memory and then increase the working memory with a statement like this:

Loading code...

You can then try entering the index creation statement again.

When you issue a new search query, it will use the index automatically. In some cases, you might need to run:

Loading code...

to force the index to be recognized.

If we issue the same Pineapple Pizza query we used earlier, the results are retrieved much faster (130 ms) but are quite different from the original query.

Loading code...

Most of the results from our original results (including Pizza Pizza, Pineapple, and Hawaiian Punch) have disappeared. The new results have less similarity to Pineapple Pizza.

Tuning the IVFFlat probes parameter

Why so much change? It’s because we clustered the vectors in groups of 200 and we analyzed only the cluster with the closest centroid to our input vector, because the probes parameter is 1 by default.

The probes parameter, which can be changed at query time, identifies the number of clusters that PostgreSQL will inspect. In the case of probes=1, only the cluster with the closest centroid to the input vector will be inspected. With probes=N the top N closest clusters will be inspected (see the image below).

A lower probes parameter speeds up queries at the cost of accuracy. A higher probes number will probably slow down the query, but it will return higher quality results.

How to define the IVFFlat probes parameter

You can change the probes parameter before you run a query. In our example, we can change the probes parameter to 2 with:

Loading code...

If we now issue the same query as before, it displays a result set more aligned with the one retrieved from the non-indexed table.

Loading code...

This is a good time to talk about performance measurement. When I ran this query, the time taken dropped from 130 ms (probes=1) to 79 ms (probes=2). That means I achieved both higher quality and faster performance, which was unexpected. However, the time scales we’re dealing with are tiny and highly susceptible to effects such as network delay because I was using a cloud service. I recommend you conduct your own speed tests. Results may be more repeatable and more likely to be as expected when working with larger data sets.

Some entries from the original non-indexed results, such as Hawaiian Punch, are not shown. Let’s try increasing the probes parameter to 10:

Loading code...

The query now runs in 208 ms (much faster than the non-indexed 447 ms) and the result set is more aligned with the initial content list.

Loading code...

The most important takeaway is that increasing the probes parameter increases the accuracy, but also the query time.

Using the Hierarchical Navigable Small Worlds (HNSW) index

Another approach is the Hierarchical Navigable Small Worlds (HNSW) index. Instead of creating clusters of nodes around centroids, the HNSW index creates layers of increasingly dense linked vectors, as shown in the image below.

At query time, the search algorithm will start at the top layer and find the nearest vector to the input vector. It then descends through it, and assesses the vectors that are directly linked on the next layer. It keeps descending through the layers and evaluating the distance from the input vector until it generates the closest vector as the result at the bottom layer.

Internally the HNSW indexes are built as linked lists, with an increasing number of nodes added at each layer.

The accuracy and speed of the results depends on how effectively the layers are built. HNSW provides two index creation parameters that we can tune:

m: the number of connections to nearest neighbors. For every vector on a layer, m defines the number of links to other vectors.
ef_construction: the number of nearest neighbors to keep in the list while traversing the layer for index creation.

HSNW also provides a parameter tunable at query time, named ef_search. It dictates how many closest neighbors to keep in the working list during the search. The explanation above is simplified by implying there is just one path through the layers. The search can follow multiple paths through the layers, which is why the working list is needed. With a higher ef_search value, more vectors are inspected, which is slower but is likely to deliver better results.

Tuning the HNSW `m` parameter

A small m parameter will create layers with fewer connections, which are therefore faster to navigate. At the same time, having fewer connections between vectors means that the accuracy could be reduced. There could be a missing link between a node accessed at layerN+1 and the optimal node at layerN as shown in the image below.

On the other hand, increasing m means the vectors on a level are more connected. There are more chances to find the closest vector, and just not an approximation due to a lack of connections. The improved quality comes at the cost of performance: more connections are available and therefore more connections need to be checked.

Tuning the HNSW `ef_construction` parameter

The ef_construction parameter defines the size of the closest candidate list used during the index building process. The list contains the closest candidates found so far while traversing the graph. Once the traversal is finished, the list is sorted and only the first m links are kept. See the image below.

A higher value for ef_construction will likely increase accuracy because more vectors are kept in the working list. But it will take longer to create the index because there are more vectors to evaluate.

How to define the HNSW `m` and `ef_construction` parameters

Both the m and ef_construction parameters are defined during index creation. Drop the IVFFlat index created previously with:

Loading code...

We can then create the HNSW index with m=10 and ef_construction=40 by entering:

Loading code...

The HNSW index is slower to build than the IVFFlat index. You can check progress with:

Loading code...

Which will show a table like this:

Loading code...

This tells us that the index is in the loading tuples stage (the second and final stage) at 97.4%. Our example took 2 min 5 secs to build the HNSW index. That’s quite a lot longer than the 15 seconds of the IVFFlat.

Note: you might hit the following warning:

Loading code...

This can be solved by increasing the maintenance_work_mem as discussed for the IVFFlat index.

Time to query! Let’s try our standard query and check the results! The query is now retrieved in only 28 ms and returns exactly the same results as the non-indexed query which took 447.857 ms.

Loading code...

Tuning the HNSW `ef_search` parameter

As mentioned earlier, the ef_search parameter dictates the number of closest vectors to keep in the working list during the search. The default value is 40. Let’s check how changing this parameter affects the performance and the accuracy by modifying it to keep only the closest vector in the working list:

Loading code...

If we now rerun the query, the result set is as shown below:

Loading code...

There are a couple of things of note:

The result set contains only 1 row instead of 10: this is because ef_search dictates the number of vectors to keep in the working list, effectively setting a maximum number of results that can be output.
The retrieved row is not the one with highest similarity: since we are only keeping the closest vector during our descent through the layers, we found a local optimum and not the overall optimum result. The new result is both new and not closely related to the query.

Let’s now increase ef_search to 5:

Loading code...

Again, the result set seems stuck in a local optimum, far from the expected results:

Loading code...

Raising the ef_search parameter to 20 gives us a result set closer to the expected results:

Loading code...

HNSW vs IVFFlat: Which to use?

The tests we’ve done and their results provide a nice overview of the two indexing options we have. The next question is: which one should I choose?

The reply is, as always, it depends: IVFFlat indexes are usually faster to build and smaller in size, but, on the other hand, are slower to use and less accurate. If your main optimization objective is to speed up the index creation phase or to keep the index size to a minimum, then IVFFlat is your best option. If, though, you want to maximize both accuracy and query speed, then choose HNSW.

If you are carrying out a lot of updates and deletes on the database, HNSW is the better option. The IVFFlat clustering mechanism would need to be rebuilt, but HNSW can remove vectors from the internal linked list easily, taking less time.

Additional techniques to speed up vector indexes

The above techniques assume we want to create an index across the entire data set. However, we can use standard PostgreSQL performance improvements to further speed up certain types of queries.

Partial indexes

For example, if we only care about indexing a particular subset of the data, we can apply a WHERE condition during the index creation process, like this:

Loading code...

This will index only the rows having an id less than 4000, resulting in a smaller and faster index for queries that are looking within this segment of data.

If we add WHERE id between 1 and 1000 to our original query, using EXPLAIN will show the idx_articles_hnsw index is being used:

Loading code...

Table partitioning

If we want to index the whole data set, but we are happy to create different vector indexes based on categorizing the data, then we can use partitioned tables with dedicated indexes for each partition.

In our Wikipedia example, we can create a table that partitions the data based on the hashing of the title column with:

Loading code...

We can insert the data into the articles_partitioned table with:

Loading code...

We can now create an index like this:

Loading code...

The index is created pretty fast, but the results are not as accurate as before:

Loading code...

The accuracy is lower because PostgreSQL creates an index per partition. From the similarity search point of view, it creates layers or clusters based only on data contained in a single partition (see the image below).

This approach is good if we know our search will always hit data in just one partition. For example, if we partition the table based on products, searching within a particular product category will work well. If a search is done across partitions, for example looking for items with similar designs across different product categories, there may be suboptimal results. In our pizza example, we partitioned by hashing the id and the query spanned all the partitions, so the accuracy was lower.

Hybrid search with index combination

You might be wondering whether it’s possible to combine an index with a hybrid search, to speed up combined searches of vectors and standard data columns. Unfortunately, it isn’t yet.

You might think we could index the vector and standard data columns separately and that PostgreSQL could combine the results of the indexes.

For example, let’s consider the following query (the vector data is hidden in the SQL):

Loading code...

Our data set is somewhat limited, so for demonstration purposes we’ll search by ID as well as the article content, even though the ID is just a sequential number. Let’s create an index on the articles column id:

Loading code...

Our hope is that PostgreSQL will use both indexes to retrieve the results without accessing the table. However, PostgreSQL only uses the index on the content_vector column to find the nearest vectors and then filters the result using the id column, as shown by EXPLAIN:

Loading code...

While this returns the right results, it doesn’t give us the speed benefit of using an index on the id column.

Unfortunately, you can’t create multi-column indexes that mix vector and standard data. Let’s try, for example, to add multiple columns to an hnsw (or ivfflat index) like this:

Loading code...

We get the following error:

Loading code...

We could try to do this with the INCLUDE option like this:

Loading code...

But this results in a similar error:

Loading code...

Since hybrid search is becoming more popular, we expect methods like this to be supported in the future. Looking at the pgvector GitHub repository, there’s an active discussion about combining HNSW and filtering in a unique index. Moreover, a branch was recently created as a first attempt to implement the HQANN search. This should allow scalar and vector types to be mixed in the same index using a modified distance function. It’s worth keeping an eye on both of these developments because hybrid searches are a common requirement.

Please note that hybrid queries are still possible in PostgreSQL, they will only partially use one of the index available.

Indexes to avoid full vector element comparison

The indexes we’ve explored allowed us to avoid scanning the full table, but this is only one of the optimization options when dealing with vectors. The other is to reduce the amount of work by shortening the vector size: instead of comparing 1536 elements per array, why don’t we compare only a subset (for example, 256) of them?

In the past, this wouldn’t work because models were trained with a specific embedding length in mind and gave each element equal weight. Skipping some embeddings would mean arbitrarily discarding useful information. But more recent models, such as OpenAI text-embedding-3-large, provide a native way to shorten embeddings, without losing too much accuracy. In technical terms, this is called Matryoshka representation learning.

When using models which have the embeddings shortening feature, we can work with a subset of the elements, usually taking the first chunk in predefined sizes (for example 256, 512 or 1024.

Shortening the vector size in PostgreSQL

How do we shorten the vector size? You might think you could use subscripting:

Loading code...

Unfortunately this doesn’t work, giving you the following error:

Loading code...

You could try to cast the vector as an 256-item array of float:

Loading code...

But this also fails:

Loading code...

What we can do is cast the vector as text and then as an array like this:

Loading code...

In the above we:

convert the content_vector to text
substitute the [ and ] with { and }
cast the result to a FLOAT array
take the first 256 entries from the resulting array via subscripting ([0:256])
substitute the { and } with [ and ]
Convert back to text
cast the result back to vector

Indexing a shortened vector in PostgreSQL

How can we speed up the search query? We could create an index on the entire expression for creating the shortened vectors like this (paste the first 256 elements of the query into the second line):

Loading code...

This code runs, without indexes, in 30 sec and is creating the 256 element version of the embeddings.

We could try to create the index with:

Loading code...

But that fails with the following error:

Loading code...

What’s more, indexes over functions are not a great technique because they force users to use the same complex syntax to query them.

An alternative is to create an additional column in the articles table to store the 256 element vectors:

Loading code...

We can populate it like this:

Loading code...

And create the index on the 256 element version with:

Loading code...

Creating this index is way faster (15 sec).

We can now try this query, limiting the input vector to the first 256 elements of the original array:

Loading code...

The result set comes back in 24 ms with the top results in line with the original non-indexed query, as shown below. The similarity figures are much lower than our original search because the similarity calculation uses fewer elements.

Loading code...

Conclusion

Pgvector is a very powerful backend for your AI applications. The ability to store embeddings and calculate vector similarity, together with the huge set of PostgreSQL SQL features, enables users to mix standard queries and AI-driven searches to provide better results.

Indexing can speed up performance when the amount of data or the size of the embeddings are a challenge. The IVFFlat and HNSW indexes allow you to cluster similar embeddings or layer the vector search queries. Shortening the vector size speeds up performance at the cost of accuracy.

In the vector space there’s no free food: performance and accuracy are a tradeoff when using indexes. Analyzing the indexing parameters and how they influence the query behavior can help you to define the sweet spot between the query speed and the accuracy of the results.

Not using Aiven services yet? Sign up now for your free trial!

Speed up PostgreSQL® pgvector queries with indexes

Understanding vector queries

Setting up a PostgreSQL playground

Understanding how similarity metrics work

L2 Distance

L1 distance

Cosine

Inner product

Hamming distance

Jaccard distance

Testing the vector similarity theory at scale

Introducing indexes in pgvector

Indexes to avoid a full table scan

Using the IVFFlat index

Tuning the IVFFlat lists parameter

How to define the IVFFlat list parameter

Tuning the IVFFlat probes parameter

How to define the IVFFlat probes parameter

Using the Hierarchical Navigable Small Worlds (HNSW) index

Tuning the HNSW m parameter

Tuning the HNSW ef_construction parameter

How to define the HNSW m and ef_construction parameters

Tuning the HNSW ef_search parameter

HNSW vs IVFFlat: Which to use?

Additional techniques to speed up vector indexes

Partial indexes

Table partitioning

Hybrid search with index combination

Indexes to avoid full vector element comparison

Shortening the vector size in PostgreSQL

Indexing a shortened vector in PostgreSQL

Conclusion

Understanding vector queries

Setting up a PostgreSQL playground

Understanding how similarity metrics work

L2 Distance

L1 distance

Cosine

Inner product

Hamming distance

Jaccard distance

Testing the vector similarity theory at scale

Introducing indexes in pgvector

Indexes to avoid a full table scan

Using the IVFFlat index

Tuning the IVFFlat lists parameter

How to define the IVFFlat list parameter

Tuning the IVFFlat probes parameter

How to define the IVFFlat probes parameter

Using the Hierarchical Navigable Small Worlds (HNSW) index

Tuning the HNSW m parameter

Tuning the HNSW ef_construction parameter

How to define the HNSW m and ef_construction parameters

Tuning the HNSW ef_search parameter

HNSW vs IVFFlat: Which to use?

Additional techniques to speed up vector indexes

Partial indexes

Table partitioning

Hybrid search with index combination

Indexes to avoid full vector element comparison

Shortening the vector size in PostgreSQL

Indexing a shortened vector in PostgreSQL

Conclusion

Tuning the HNSW `m` parameter

Tuning the HNSW `ef_construction` parameter

How to define the HNSW `m` and `ef_construction` parameters

Tuning the HNSW `ef_search` parameter

Tuning the HNSW `m` parameter

Tuning the HNSW `ef_construction` parameter

How to define the HNSW `m` and `ef_construction` parameters

Tuning the HNSW `ef_search` parameter