Skip to main content

PG-Vector (pgvector)

PG-Vector is a PostgreSQL extension that allows for vector similarity search. It stores vectors in the database and allows for similarity search to them.

How to run​

Running on CGC is very simple. You just need to create a new database and load the PG-Vector extension.

Create volume for the data:

cgc volume create -s 5 -sc <storage_class> pgvector-volume

Run database with volume attached:

cgc db create -n pgvector-001-prod -v pgvector-volume pg-vector

After creating the database, you will receive an app token for your database.

warning

Always create the database with a mounted volume. Without -v the database writes to the pod's ephemeral disk, so every restart (cgc resource restart, node maintenance, a crash) initialises PostgreSQL from scratch and all data is lost after a single restart. With a volume (mounted at /var/lib/postgresql/data, which holds PGDATA) the same rows survived cgc resource restart, and they also survived deleting the database and creating it again with the same name and the same volume.

Default configuration​

The default configuration for PG-Vector is set to use the PostgreSQL database engine. The database will be created with the following parameters:

  • POSTGRES_PASSWORD: The password for the PostgreSQL user. This is set to CGC specific app_token that you receive after creating the database.
  • POSTGRES_USER=admin: The user for the PostgreSQL database.
  • POSTGRES_DB=db: The name of the PostgreSQL database. It is always db, it is not set to the app name you provided.
  • POSTGRES_HOST_AUTH_METHOD=trust: Configures PostgreSQL to skip password verification for connections, trusting that authentication has already occurred at the system level. This is convenient for development but creates a significant security risk in production environments.
  • PGDATA=/var/lib/postgresql/data/pgdata: The data directory, a subdirectory of the mounted volume.
info

If you wish to overwrite POSTGRES_PASSWORD and keep it displayed correctly during cgc db list -d, we advice using CGC flag -ce app_token=YOUR_PASSWORD

How to connect to the database​

For more information about CGC SDK and how to connect to the database, please refer to our docs

Installation​

Enable the PG-Vector extension (do this once in each database where you want to use it)

CREATE EXTENSION vector;

Example usage​

Create a vector column with 3 dimensions.

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

Insert vectors

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

Get the nearest neighbors by L2 distance

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

PGVector also supports:

  • inner product (<#>)
  • cosine distance (<=>)
  • L1 distance (<+>)

Note: <#> returns the negative inner product since Postgres only supports ASC order index scans on operators

For more information or options for PGVector, please refer to the Github repo