/* MINDFIRE BLOG */

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: https://visualstudio.microsoft.com/) 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”:

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

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.”:

You’ll then see the installation take place:

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

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):

Be sure to run the command prompt at Administrator:

First run:

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

You should see this result:

Next, you’ll run:

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

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 https://github.com/pgvector/pgvector.git

Here is the result you should see:

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 Makefile.win

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

nmake /F Makefile.win 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.”

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:

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

Enter the password you created:

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

Activating pgvector Extension

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

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

CREATE EXTENSION vector;

Then click the “run” button.

You should see a result that looks like this:

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:

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:

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:

• pgvector Github Repo: https://github.com/pgvector/pgvector
• Haystack documentation for pgvector: https://haystack.deepset.ai/integrations/pgvector-documentstore
• Haystack pgvector Document Store: https://docs.haystack.deepset.ai/v2.0/docs/pgvectordocumentstore
• Using pgvector on Heroku: https://devcenter.heroku.com/articles/pgvector-heroku-postgres
• Setup pgvector using Docker: https://docs.spring.io/spring-ai/reference/api/vectordbs/pgvector.html
• How to install C++ Support for Visual Studio
  • https://learn.microsoft.com/en-us/cpp/build/building-on-the-command-line?view=msvc-170#download-and-install-the-tools
  • https://learn.microsoft.com/en-us/cpp/build/vscpp-step-0-installation?view=msvc-170
• A Python module to use pgvector: https://pypi.org/project/pgvector/