Installing pgvector in Preparation for Retrieval Augmented Generation

Installing pgvector in Preparation for Retrieval Augmented Generation

In our last post, we finished installing PostgreSQL preparatory to using it as a vector database for our Artificial Intelligence projects to be used for Retrieval Augmented Generation (RAG) with Large Language models.

In this post, we’re going to follow this guide inside the pgvector git repository on how to install the pgvector Extension for PostgreSQL. Recall that pgvector is an extension for PostgreSQL that enhances its capabilities with vector similarity search. It allows you to create database tables that contain vector data and to query your vector data quickly. In other words, pgvector adds vector search capabilities to PostgreSQL which is what we need to be able to use PostgreSQL as a document store to store the embeddings we created out of our documents. In a later post we’ll then use the pgvector extension via Haystack to actually store embedded text to perform a fast cosine similarity search.

The pgvector installation guide contained in the git repository assumes a lot of implicit knowledge and needs a bit of clarification to work properly in Windows. But no worries, I’ll take you through step by step in a lot more detail than the official installation guide contains.

Installing Visual Studios

We’re going to need Microsoft Visual Studio loaded to use the C++ make utilities. If you already have Visual Studio loaded, you’ll need to know the location for ‘vcvars64.bat’ for your particular install. Jump ahead to the next section for a discussion on how to find that. If you don’t have Visual Studio installed already, follow the instructions in this section.

Even if you don’t have a license for Microsoft Visual Studio you can just download the free Community Edition of Microsoft Visual Studios here. (Learn more about Microsoft’s Visual Studio here: I am not going to go into the details of installing Visual Studio since it is a) quite easy, and b) there are plenty of guides elsewhere.

Assuming you now have Visual Studio installed (for me I have the 2022 version) we now need to install C++ support (see also here) so that we can build C++ code. We’ll need this for the installation process for pgvector.

Run the “Visual Studio Installer”:

Windows search bar searching for visual studio installer. Best match is Visual Studio Installer.

Inside the Visual Studio Installer, you will need to “Modify” your version of Visual Studio:

The installer's wizard screen. Three options are shown, on the right of each option are three buttons. The top button is Modify. The modify button on the bottom option, Visual Studio Enterprise 2022, is circled in red.

Then you will need to select “Desktop development with C++” and you’ll see a list of everything about to be installed. Then click “Modify.”:

A pop-up menu from the installer. There is a section titled Desktop & Mobile (5), and there are five options within it. The second from the top on the left option, Desktop development with C++, is activated via a checkmark box on the right of its box. The checkbox is circled in red. On the bottom right of the pop-up menu there is a modify button, it is circled in red.

You’ll then see the installation take place:

The Visual Studio Installer wizard shows the previously modified option at the top of the list being installed, visualized by several loading bars- indicating this will take some time.

You should now have C++ Support installed.

Finding Your ‘vcvars64.bat’ File

Now that C++ support is load, you must find where your vcvars.bat file resides. This will depend on which version of Visual Studio you have loaded.

The base path you’ll be working with, regardless of which version you have installed, is: C:\Program Files\Microsoft Visual Studio\

The official pgvector installation guide lists this as an example:

C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars64.bat

Because I had the Enterprise version of 2022, mine file was located at:

C:\Program Files\Microsoft Visual Studio\2022\Enterprise\VC\Auxiliary\Build\vcvars64.bat

Windows Explorer tab is opened under the above-mentioned location. In the list of files, vcvars64.bat is circled in red.

I suggest navigating to the base directory and then find the specific file you’ll need to run and copy and past the folder path to be used later.

Installing the pgvector Extension

Now we’re ready to run the necessary commands to install the pgvector extension. For this portion you can use Windows Terminal or the Command Window (cmd):

Windows search, cmd is being searched for and Command Prompt is the best match.

Be sure to run the command prompt at Administrator:

Command Prompt has been right clicked to reveal a windows menu, the option 'Run as administrator' is circled in red.

First run:

"C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars64.bat"

Command Prompt console screen, showing the above-mentioned file location or command written.

You should see this result:

Command Prompt console screen, showing a Visual Studio 2022 Developer Command Prompt environment has been initialized as x64. A new line is ready showing "C:\Users\BNielson>", waiting for input.

Next, you’ll run:

set "PGROOT=C:\Program Files\PostgreSQL\16"

Command Prompt console screen, showing that the above-mentioned line has been entered. As a result, two new lines now show as "C\Users\BNielson>cd %TEMP%" and then "C:\BNielson\AppData\Local\Temp>git clone --branch v0.7.0".

This sets PGROOT which will be used by the make utility.

Now run:

cd %TEMP%

This will set you to a temp directory where you’ll clone the pgvector git repo. Then run this:

git clone --branch v0.7.0

Here is the result you should see:

Command Prompt console screen, showing that pgvector has been cloned into a temp location, in this case it was "C:\Users\BNielson\AppData\Local\Temp".

After the git repo is cloned, you’ll run:

cd pgvector

This will move you to the directory of the repo you just cloned.

Now run:

nmake /F

Command Prompt console screen, showing the above-mentioned line has been inputed.

After the make utility runs you can then run the final command:

nmake /F install

If everything went as planned, you now have the pgvector extension added to your PostgreSQL installation from the last blog post. If something went wrong, check the Windows installation notes here for ideas on common problems.

pgvector Extension in pgAdmin4

In the previous post we loaded pgAdmin4 as a way to interact with and query our PostgreSQL database. Let’s open it again following the same instructions from the last post:

For Windows search for “pgAdmin4.”

Windows search tab, with pgAdmin 4 being searched for and the app pgAdmin 4 is circled in red.

Note: I seem to have two versions of each app due to having previously installed an older version of PostgreSQL in the past. You’ll probably just have one.

Then click on “pgAdmin4” to run and you’ll see something like this:

The pgAdmin 4 dashboard screen with various options and links. The top section is Welcome, then Quick Links, then Getting Started.

Click the arrow next to “Servers” and then select the database you just installed:

The list of tabs on the left is cropped to show Servers (2) expanded and the two server options under, PostgreSQL 13 and PostgreSQL 16. PostgreSQL 16 has a red arrow pointing towards it.

Enter the password you created:

The Connect to Sever pop-up is opened and has an option for inputting a password. There is also an option to save password.

You should then be able to navigate to the tables (under ‘Schema’). At this point you will not have any tables yet:

The listed tab of Tables is open and displayed on the right, showing various sessions, transactions, etc., and database activity.

Activating pgvector Extension

Now that you’re inside of pgAdmin4, bring up the query tool:

On the top left of the pgAdmin app, the drop-down menu of Tools (third from left) is circled in red and opened, showing a list of tools. Query Tool is circled in red.

Be sure you’ve selected the correct server, then run this command:


Then click the “run” button.

You should see a result that looks like this:

The listed Tables tab is opened again, the tab on the right of postgre/postgres@PostgreSQL 16* is opened showing a screen with a Query section and a Messages section. Near the top there is a section where postgres/postgres@PostgrSQL 16 has been inputted. In the Query section a command of "CREATE EXTENSION vector;" has been inputted and is circled in red. Under the Messages section "Query returned successfully in 203 msec." is reported.

Testing Out Cosine Similarity Search

Now let’s test out the new cosine similarity search capabilities of pgvector. Use the query tool to run these commands in order:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));

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

Now that we’ve inserted two vectors into a table, we can query them back out using the new cosine similarity operator of ‘<->’:

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

You should see this result:

Under the Query section, "SELECT * FROM items ORDER BY embedding <-> ''[3,1,2]' LIMIT 5;" is written and circled in red. Under Data Ouput, a tab to the left of Messages, a table shows the top result is [1,2,3] because this is closer to [3,1,2] than [4,5,6] is.

We are querying for the vector most similar to [3,1,2]. There are only two options: [1,2,3] and [4,5,6]. This should be intuitively obvious which it is most similar to, but if you need a reminder how cosine similarity works you can check my blog post. In any case, it came back with the top result as [1,2,3] just as we expected.

But now say we query for the most similar to [6,5,4] instead:

Undery the Query section, "SELECT * FROM items ORDER BY embedding <-> ''[6,5,4]' LIMIT 5;" is written. Under the Data Output section the top option shows [4,5,6] with id 2. The bottom option shows [1,2,3] with id 1.

Sure enough it now came back with [4,5,6] as the most similar result just like we’d expect!

Congratulations, you have successfully installed pgvector on a Windows installation of PostgreSQL!

Useful Links:


comments powered by Disqus

Follow Us

Latest Posts

subscribe to our newsletter