Experiment testing pgvector extension for Google Cloud SQL Postgres

gcp-sql-postgres-pgvector.sh

#!/usr/bin/env bash

#####################################################################
# REFERENCES
# - https://cloud.google.com/sql/docs/postgres/create-instance 
# - https://cloud.google.com/sql/docs/postgres/create-manage-databases
# - https://cloud.google.com/sql/docs/postgres/users
# - https://cloud.google.com/blog/products/databases/using-pgvector-llms-and-langchain-with-google-cloud-databases
# - https://cloud.google.com/sql/docs/postgres/extensions#miscellaneous-extensions
# - https://github.com/pgvector/pgvector 
# - https://stackoverflow.com/questions/63296851/how-to-install-external-extensions-in-gcps-postgresql
#####################################################################

export PROJECT_ID=$(gcloud config get-value project)
export PROJECT_USER=$(gcloud config get-value core/account) # set current user
export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format="value(projectNumber)")
export IDNS=${PROJECT_ID}.svc.id.goog # workflow identity domain

export GCP_REGION="us-central1" # CHANGEME (OPT)
export GCP_ZONE="us-central1-a" # CHANGEME (OPT)
export NETWORK_NAME="default"

# enable apis
gcloud services enable compute.googleapis.com \
    storage.googleapis.com \
    sqladmin.googleapis.com

# configure gcloud sdk
gcloud config set compute/region $GCP_REGION
gcloud config set compute/zone $GCP_ZONE

# create cloud sql instance
export DB_INSTANCE_NAME="pgtest"
export DB_INSTANCE_VERSION="POSTGRES_15"

gcloud sql instances create $DB_INSTANCE_NAME \
    --database-version=$DB_INSTANCE_VERSION \
    --cpu=2 \
    --memory=7680MB \
    --region=$GCP_REGION

# create postgres database
export DB_NAME="genai"

gcloud sql databases create $DB_NAME \
    --instance=$DB_INSTANCE_NAME 

# override postgres password
gcloud sql users set-password postgres \
    --instance=$DB_INSTANCE_NAME \
    --prompt-for-password

# verify db
gcloud sql databases list --instance $DB_INSTANCE_NAME

# connect to db
gcloud sql connect $DB_INSTANCE_NAME --user=postgres

# install pgvector extension
# CREATE EXTENSION IF NOT EXISTS vector;
# SELECT extversion FROM pg_extension WHERE extname = 'vector';
# ALTER EXTENSION vector UPDATE;

Upgrading PGVector on Cloud SQL

A customer reported attempting to use pgvector 0.5.0 on their Cloud SQL database instances, both version 13 and 15 after upgrade.

Issue

Unfortunately the only supported and compiled version of that extension was 0.4.2. This experiment attempted to recreate the customer experience to verify the issue. Google's own documentation, however, cited support of 0.5.0 which appears to be in error.

ERROR: access method "hnsw" does not exist

Result

➜  gcp-cloud-sql-pgvector gcloud sql connect $DB_INSTANCE_NAME --user=postgres
Allowlisting your IP for incoming connection for 5 minutes...done.                                                                        
Connecting to database with SQL user [postgres].Password: 
psql (14.8 (Homebrew), server 15.2)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION
postgres=> SELECT extversion FROM pg_extension WHERE extname = 'vector';
 extversion 
------------
 0.4.2
(1 row)

postgres=> ALTER EXTENSION vector UPDATE;
NOTICE:  version "0.4.2" of extension "vector" is already installed
ALTER EXTENSION
postgres=>