Setting up your first Vector Database (pgvector)

To See All Tech Articles: Index of Lessons in Technology

  
Note: The operating system we are using is :

(base) ashish@ashish:~$ uname -a
Linux ashish 6.5.0-28-generic #29~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Thu Apr  4 14:39:20 UTC 2 x86_64 x86_64 x86_64 GNU/Linux


Ubuntu includes PostgreSQL by default. To install PostgreSQL on Ubuntu, use the apt (or other apt-driving) command:

apt install postgresql

Ref: postgresql.org

Installing and Checking the PostGRE SQL Setup First

$ sudo apt install postgresql

$ sudo -u postgres psql
could not change directory to "/home/ashish": Permission denied
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# SELECT CURRENT_DATE ;
 current_date 
--------------
 2024-05-15
(1 row)

postgres=# select version();
                                                                version                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

postgres=# exit

GETTING ACCESS TO POSTGRE SQL EXTENSIONS VIA APT REPO

Ref (1): postgresql.org

Ref (2): wiki.postgresql.org

PostgreSQL Apt Repository:

$ sudo apt install -y postgresql-common
$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

INSTALLING PGVECTOR USING 'apt'

Ref: github.com

Debian and Ubuntu packages are available from the PostgreSQL APT Repository. Follow the setup instructions and run:

sudo apt install postgresql-16-pgvector

Note: Replace 16 with your Postgres server version

For us, the Postgre SQL version was 14 so command becomes:
$ sudo apt install postgresql-14-pgvector


DO NOT MAKE THIS MISTAKE OF GETTING IN WITHOUT ROOT ACCESS:

Here is the mistake:

Ref (1): stackoverflow

Ref (2): community.retool.com

(base) ashish@ashish:~/Desktop/ws/gh/others/pgvector$ sudo -u postgres psql
could not change directory to "/home/ashish/Desktop/ws/gh/others/pgvector": Permission denied
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.12 (Ubuntu 14.12-1.pgdg22.04+1))
Type "help" for help.

postgres=# CREATE EXTENSION pgvector;
ERROR:  could not open extension control file "/usr/share/postgresql/14/extension/pgvector.control": No such file or directory
postgres=# exit


Here is the fix:


(base) ashish@ashish:~$ sudo -i -u postgres
[sudo] password for ashish: 
postgres@ashish:~$ psql
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.12 (Ubuntu 14.12-1.pgdg22.04+1))
Type "help" for help.

postgres=# create extension vector;
CREATE EXTENSION
postgres=# 

TESTING THE PGVECTOR SETUP

postgres=#  \dx
                             List of installed extensions
  Name   | Version |   Schema   |                     Description                      
---------+---------+------------+------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vector  | 0.7.0   | public     | vector data type and ivfflat and hnsw access methods
(2 rows)

Ref: dev.to

RUNNING A COUPLE OF TESTS

Ref: github.com/pgvector

postgres=# CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
CREATE TABLE
postgres=# INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
INSERT 0 2
postgres=# SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
 id | embedding 
----+-----------
  1 | [1,2,3]
  2 | [4,5,6]
(2 rows)

Tags: Technology,Large Language Models,