PostgreSQL with Vector Support

Suggest Edits

pgvector is an open-source vector similarity search for Postgres, allowing users to store vectors alongside their data. It supports various types of vectors, including single-precision, half-precision, binary, and sparse vectors. It offers different distance metrics such as L2 distance, inner product, cosine distance, L1 distance, Hamming distance, and Jaccard distance.

The platform is compatible with any language that has a Postgres client and offers ACID compliance, point-in-time recovery, JOINs, and other Postgres features.

Image Overview

The pgvector extension is built on top of PostgreSQL 16, extending the native Docker image. It's as simple as pulling the image rax-public/pgvector-pg16 and configuring the desired environment variables.

Available Environment variables

Variable	Description
`POSTGRES_PASSWORD`	Sets the superuser password for PostgreSQL. This variable is required unless `POSTGRES_HOST_AUTH_METHOD` is set to 'trust'.
`POSTGRES_USER`	Sets the superuser name. If not specified, the default is "postgres".
`POSTGRES_DB`	Defines the name of the default database to be created. If not specified, it defaults to the value of `POSTGRES_USER`.
`POSTGRES_INITDB_ARGS`	Sends arguments to postgres initdb. For example, `--data-checksums`.
`POSTGRES_HOST_AUTH_METHOD`	Controls the auth-method for host connections for all databases, all users, and all addresses. If unspecified, defaults to "md5" password authentication.
`POSTGRES_PASSWORD_FILE`	Path to a file containing the superuser password. An alternative to using `POSTGRES_PASSWORD`.
`POSTGRES_USER_FILE`	Path to a file containing the superuser name. An alternative to using `POSTGRES_USER`.
`POSTGRES_DB_FILE`	Path to a file containing the name of the default database. An alternative to using `POSTGRES_DB`.
`POSTGRES_INITDB_ARGS_FILE`	Path to a file containing arguments for postgres initdb. An alternative to using `POSTGRES_INITDB_ARGS`.
`POSTGRES_INITDB_WALDIR`	Defines a custom location for the transaction log directory.
`PGDATA`	Defines the PostgreSQL data directory. If not specified, defaults to `/var/lib/postgresql/data`.
`PGPORT`	Specifies the port on which PostgreSQL should listen. If not set, the default port 5432 is used.
`PGUSER`	Specifies the PostgreSQL user to be used. If not set, defaults to the value of `POSTGRES_USER`.

Launching Postgres on Run:AI

runai submit -i pgvector/pgvector:pg16 --cpu 8 --gpu 0 --memory 8G --interactive --large-shm --service-type nodeport,port=30432:5432 --environment PORT=30432 --environment POSTGRES_PASSWORD=NotATest2k93020 --environment POSTGRES_HOST_AUTH_METHOD=trust --environment POSTGRES_DB=vector_store

Exposing Ports

Due to the nature of development, you might want to expose ports during your coding sessions. To do that, simply execute:

runai port-forward <JOB_NAME> --port 30432:5432

Command Breakdown

Image Specification

-i pgvector/pgvector:pg16: Specifies the Docker image to use (pgvector/pgvector with PostgreSQL 16).

Resource Allocation

--cpu 8: Allocates 8 CPU cores to the job.
--gpu 0: Allocates 0 GPUs to the job (CPU-only).
--memory 8G: Allocates 8 gigabytes of memory to the job.

Job Configuration

--interactive: Runs the job in interactive mode.
--large-shm: Enables a large shared memory space.

Networking

--service-type nodeport,port=30432:5432: Creates a NodePort service, mapping port 30432 on the host to port 5432 in the container.

Environment Variables

--environment PORT=30432: Sets the PORT environment variable to 30432.
--environment POSTGRES_PASSWORD=NotATest2020: Sets the PostgreSQL password.
--environment POSTGRES_HOST_AUTH_METHOD=trust: Configures PostgreSQL to trust connections (no password required).
--environment POSTGRES_DB=vector_store: Sets the name of the database to be created.

Notes

This command sets up a PostgreSQL 16 database with the pgvector extension.
The database will be accessible on port 30432 of the host machine.
The default database name is set to vector_store.
The PostgreSQL password is set to NotATest2020, but the authentication method is set to trust, which means no password is required for connections.
We didn't add any PVC while launching the image, it is recommended to set one.

Obtaining the connection Address

(base) alberto@barrahome:~$ runai describe job  job-62a3eab71dab|grep 'Service URLs:'
Service URLs: 10.4.102.12:30432
(base) alberto@barrahome:~$

Installing pgAdmin

pgAdmin 4, an open-source management tool for Postgres. Designed for both novice and experienced users, pgAdmin 4 offers a powerful graphical interface to simplify the creation and management of databases.

Launching pgAdmin is quite a straightforward task

Deploy a "Workload" with a type of "Inferencing".

Create an environment using the official image "dpage/pgadmin4".
Define the following environment variables:
- PGADMIN_DEFAULT_EMAIL
- PGADMIN_DEFAULT_PASSWORD

Set the desired amount of CPU and RAM, then launch the container.

Obtain the address for our newly created container from the Run:AI interface (Workloads, enable Columns → Connection(s)):

Log into pgAdmin

Open the corresponding Address from the step below, your page should look like this one:

Register a Server

To register a server, after logging in, right-click on "Servers" located next to Object Explorer. This option can be found on the left side of your screen, as illustrated below:

Upon clicking on "Server...", a new window will display with the title "Register - Server". Since we are using "trust" on Postgres in this example, a password will not be required. Instead, copy the IP and Port obtained from the "Obtaining the Connection Address" step, change the Username to "postgres", and you are ready to proceed!

Upon clicking "Save," our instance will be displayed on the left side, listed under "Servers" with the name we have chosen.

Now, we are all set to go through the pgvector "Getting Started."

Updated 2 months ago