/* MINDFIRE BLOG */

In previous posts, we’ve learned about cosine similarity as a way to compare embeddings of text, actually putting that idea to work doing an actual semantic search on the Federalist papers, and later looked at using Haystack as a tool to build an Artificial Intelligence (AI) pipeline for use with a Large Language Model (LLM).

One big problem with a Cosine Similarity search is how computationally intensive it is. You can embed the text all in advance, but you still need to embed the query and then compare the results (using the Cosine Similarity algorithm) to each and every text entry. This quickly gets too slow to be useful. Moreover, the current implementation I built involved storing everything in memory, which quickly became unworkable.

What we really need is a database that will allow us to store the embeddings and run the cosine similarities for us.

Luckily, plain old PostgreSQL Database has an extension, called Pgvector, that does nearly everything we need! 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 plain English, 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.

Better yet, Pgvector has built-in support in Haystack! So, we can create a document store in Haystack and use it in a Haystack pipeline while all the data is stored in PostgreSQL and Pgvector does the Cosine Similarity search. Better yet, the Cosine Similarity search will be much faster than the one I wrote because Pgvector doesn’t do its cosine similarity search against everything in the database but instead speeds the search up using a nearest neighbor search (using HSNW) first. This drastically improves the retrieval speed. I’ll do future blog posts explaining nearest neighbor search and HSNW. For now, all you need to know is that it is much faster than if you compare every single vector like I did in my previous posts.

In this post we’re going to install the PostgreSQL database for Windows. I’ll also give links for how to do this for other operating systems or using Docker. In a future post we’ll then install Pgvector and utilize it with Haystack to do Semantic Searches using the built-in Cosine Similarity capabilities.

Installing PostgreSQL for Windows

PostgreSQL is an open-source object-relational database. You can find the page to download and install PostgreSQL for any operating system on this link. For Windows, we’ll use this link here. Click “Download the Installer.” I’ll be using this link to install version 16.3, which is (for me at the time of this writing) the most up-to-date version: (Found here)

After the download, run the installer and go through the install process:

I took the default install folder of “C:\Program Files\PostgreSQL\16”

You definitely want to install pgAdmin4, but I went ahead and installed everything available:

I took the default data directory as well: “C:\Program Files\PostgreSQL\16\data”

You will then need to select a password:

I also took the default port of: 5432. And I selected English, United States as the Locale.

I then went on to let the install Stack Builder:

I went ahead and installed some additional utilities and drivers, but this should be optional:

I won’t go over the details of the rest since it was optional anyhow. Your installation of PostgreSQL should now be done.

Installing PostgreSQL for Other Operating Systems

If you need to install PostgreSQL for another operating system you may find this link helpful.

Alternatively, you could just install PostgreSQL using Docker. The Haystack Pgvector document store install guide suggests the following:

docker run -d -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres ankane/pgvector
Testing Out PostgreSQL Using pgAdmin

You can test that PostgreSQL was properly installed by running pgAdmin which should have been installed with the PostGreSQL installation. 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:

Multiple Ways to Check Your Version

Let’s go over several ways to check your version. It isn’t that we care that much about the version (though this is a great way to be sure things are installed correctly) so much as I want to introduce to you several useful tools.

First, let’s check version using the PSQL Tool built into pgAdmin. You can check this by going to Tools -> PSQL Tool and the PSQL tool will come up in a tab. Enter:

select version();

It should show the version you just installed.

Another way to accomplish this is to run PSQL Shell:

Once you’ve run the shell you’ll need to login. For most of the options you can take the default by hitting Enter. For password, enter your password:

Now type select version();

It should show the correct version that you just installed.

Or you can accomplish the same thing by just using the pgAdmin Query Tool (under Tools -> Query Tool):

Enter “select version();”, then click the little arrow for the run button to run the query. You should get back the version as the result.

All three of these are useful ways to interact with the PostgreSQL server you’ve created.

Note: If you don’t have a default ‘postgres’ database, this tutorial will explain how to create it.

Creating A Table and Inserting Data

Now let’s follow this little tutorial on how to create a table in PostgreSQL. You should already have the Query tool up, but recall you can get there by Tools -> Query Tool. In the query tool type:

CREATE TABLE tutorials (id int, tutorial_name text);

Then, click the Run button. Now you’ll need to refresh your view to see the new table by right mouse clicking on your Server:

You should now be able to see the new table under ‘Schema’ -> ‘Tables’:

There is now a ‘tutorials’ table that you have created. Let’s try to insert some data into it and then query it back following this tutorial. First run this command in the query tool:

INSERT INTO tutorials(id, tutorial_name) VALUES (1, 'postgre');

Then select it back out of the table with this command:

SELECT * FROM tutorials

You should see this result:

Congratulations! You have a working PostgreSQL database running in Windows (or other operating systems).

In future posts we’ll go over tutorials of how to install Pgvector, how to query using vector tables, how to integrate with Haystack, and how to build a semantic search that is much faster than the one we previously built.

Links

• PostgreSQL website:
  • Main site: https://www.postgresql.org/
  • Downloads: https://www.postgresql.org/download/
  • Download and Install for Windows: https://www.postgresql.org/download/windows/
• Several good tutorials for installing PostgreSQL for Windows:
  • https://phoenixnap.com/kb/install-postgresql-windows
  • https://www.guru99.com/download-install-postgresql.html
  • https://www.timescale.com/blog/how-to-install-psql-on-mac-ubuntu-debian-windows/
• Installing Docker for Windows: https://docs.docker.com/desktop/install/windows-install/
• Pgvector Repo (with extensive instructions on how to use it): https://github.com/pgvector/pgvector