This is the multi-page printable view of this section. .

Return to the regular view of this page.

Administration

Configuration, upgrading, logical replication, FDW, Kubernetes, and migration for pgvecto.rs.

1: Configuration
2: Upgrading
3: Logical replication
4: Foreign data wrapper (FDW)
5: Kubernetes
6: Migration

1 - Configuration

By default, you cannot capture all pgvecto.rs logs. pgvecto.rs starts a background worker process for indexing, and it prints logs to standard error. To capture

Logging

By default, you cannot capture all pgvecto.rs logs. pgvecto.rs starts a background worker process for indexing, and it prints logs to standard error. To capture them, you need to set logging_collector to on. You can get more information from PostgreSQL document about logging collector.

You can set logging_collector to on with the following command:

psql -U postgres -c 'ALTER SYSTEM SET logging_collector = on;'
# You need restart the PostgreSQL cluster to take effects.
sudo systemctl restart postgresql.service   # for pgvecto.rs running with systemd
docker restart pgvecto-rs-demo  # for pgvecto.rs running in docker

Index configuration

since v0.3.0

For each index, there are a number of configuration items that are variable. Unlike options, they support real-time modification after the index has been created. These items can be configured by function alter_vector_index.

-- alter_vector_index(dim: OID, key: TEXT, value: TEXT)
SELECT alter_vector_index('index_name'::regclass::oid, 'configuration_name', 'value');

Here are all the available configurations.

Threads for backend optimizing

By default, each vector index is optimized by only one thread at the backend. However, if you have multiple idle CPUs and want to take advantage of them, you can increase the number of threads.

Set the key argument to optimizing.threads and the value argument to the desired number of threads.

-- alter_vector_index(dim: OID, key = 'optimizing.threads', value: TEXT as INT[1-65535])
SELECT alter_vector_index('table_col_idx'::regclass::oid, 'optimizing.threads', '4');

2 - Upgrading

If you update the extension to a new version, let’s say it’s a.b.c. This command helps you to update the schema of extension:

General Steps

If you update the extension to a new version, let’s say it’s a.b.c. This command helps you to update the schema of extension:

ALTER EXTENSION vectors UPDATE;
-- or
ALTER EXTENSION vectors UPDATE TO 'a.b.c';

If you’re upgrading from 0.1.x, please read Upgrading from 0.1.x.

If you’re upgrading to 0.4.0, please read Upgrading to 0.4.0.

Execute the following SQL. It helps you do some maintenance work.

SELECT pgvectors_upgrade();

You need to restart PostgreSQL to take effects.

You could check the status of all vector indexes in this command:

SELECT
        I.relname AS indexname
    FROM pg_index X JOIN
         pg_class I ON I.oid = X.indexrelid JOIN
         pg_am A ON A.oid = I.relam
    WHERE A.amname = 'vectors';

Let’s assume the output is:

 indexname
-------------
 t_val_idx_1
 t_val_idx_2

Then you could reindex all vector indexes.

REINDEX INDEX t_val_idx_1;
REINDEX INDEX t_val_idx_2;

Upgrade to 0.4.0

You may get this error if some indexes were created before upgrading pgvecto.rs to 0.4.0:

ERROR:  index public.this_is_index depends on operator class vector_cos_ops for access method vectors
index public.this_is_index depends on operator class vector_cos_ops for access method vectors cannot drop operator class vector_cos_ops for access method vectors because other objects depend on it 

ERROR:  cannot drop operator class vector_cos_ops for access method vectors because other objects depend on it
SQL state: 2BP01
Detail: index public.that_is_also_index depends on operator class vector_cos_ops for access method vectors
index public.that_is_also_index depends on operator class vector_cos_ops for access method vectors
Hint: Use DROP ... CASCADE to drop the dependent objects too.

You can simply drop these indexes, and then upgrade the extension:

DROP INDEX this_is_index;
DROP INDEX that_is_also_index;

ALTER EXTENSION vectors UPDATE;

-- CREATE INDEX this_is_index ...
-- CREATE INDEX that_is_also_index ...

Upgrade from 0.1.x

You need to follow these steps to make ALTER EXTENSION vectors UPDATE work.

Let’s assume your pgvecto.rs version is 0.1.x (replace x with a number).

CREATE SCHEMA IF NOT EXISTS vectors;
UPDATE pg_catalog.pg_extension SET extversion = '0.1.x' where extname = 'vectors';
UPDATE pg_catalog.pg_extension SET extrelocatable = true where extname = 'vectors';
ALTER EXTENSION vectors SET SCHEMA vectors;
UPDATE pg_catalog.pg_extension SET extrelocatable = false where extname = 'vectors';
ALTER EXTENSION vectors UPDATE;

Tip

pgvecto.rs is installed in schema vectors from 0.2.0, you may need to set search_path for the database.

3 - Logical replication

Logical replication is a feature in PostgreSQL that enables the replication of individual database objects or a subset of data from one PostgreSQL database to a

Logical replication is a feature in PostgreSQL that enables the replication of individual database objects or a subset of data from one PostgreSQL database to another.

With logical replication, you can selectively replicate specific tables, databases, or even specific rows based on predefined replication rules. This provides more flexibility compared to physical replication, where the entire database cluster is replicated. It allows you to design custom replication topologies and replicate only the data that is necessary for your use case.

We will show you how to use logical replication to replicate data from one PostgreSQL database to another.

Deploy source and target PostgreSQL clusters

$ echo 'version: "3.7"
services:
  source_db:
    image: tensorchord/pgvecto-rs:pg15-v0.2.0
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: password
    command: >
      -c wal_level=logical
      -c shared_preload_libraries=vectors.so
    networks:
      localnet:

  target_db:
    image: tensorchord/pgvecto-rs:pg15-v0.2.0
    ports:
      - "5433:5432"
    environment:
      POSTGRES_PASSWORD: password
    command: >
      -c wal_level=logical
      -c shared_preload_libraries=vectors.so
    networks:
      localnet:

networks:
  localnet:
' > docker-compose.yml

$ docker compose up -d

Create extension vectors in source database and target database.

# create extension vectors
$ psql -h <source_db_ip> -U postgres -p 5432 
DROP EXTENSION IF EXISTS "vectors";
CREATE EXTENSION "vectors";

$ psql -h <target_db_ip> -U postgres -p 5432
DROP EXTENSION IF EXISTS "vectors";
CREATE EXTENSION "vectors";

Configure logical replication

Now, we can set logical replication between source database and target database.

Prepare test data

We need to create a table named test with a column named embedding of type vector(10) in source database and target database. Then we create an index on the embedding column of the test table in source database and target database. Finally, we insert data into the source database.

Create the table

DROP TABLE IF EXISTS test;
CREATE TABLE test (id integer PRIMARY KEY, embedding vector(10) NOT NULL);

Create the index

We create an index on the embedding column of the test table in source database and target database. The index type is flat, it is a brute force algorithm. We choose vector_l2_ops squared Euclidean distance to measure the distance between vectors. Another index type and distance function can be found in here.

CREATE INDEX ON test USING vectors (embedding vector_l2_ops) WITH (options = "[indexing.flat]");

Insert data

We only need to insert data into the source database. The data will be synchronized to the target database.

INSERT INTO test
SELECT i, ARRAY[random(), random(),random(),random(),random(),random(),random(),random(),random()]::real[] 
FROM generate_series(1, 100) i;

In source database, now you can search for the nearest neighbor of a vector in the embedding column of the test table.

postgres=# SELECT id FROM test ORDER BY embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
 id 
----
 82
 69
 80
 22
 31
 20
 85
 97
 72
(10 rows)

If you query the target database, you will get the noting:

SELECT id FROM test ORDER BY embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
 id 
----
(0 rows)

Set up logical replication

To simplify the process of setting up logical replication between two PostgreSQL databases, we will use pg-easy-replicate.

We use pg-easy-replicate to set up logical replication between source database and target database.

# get the network name
$ docker network ls | grep  pg
8631dd77129b   pg_regress_localnet   bridge    local

# get source and target container ip
$ docker inspect <container_id> | grep IP

$ docker run -e SOURCE_DB_URL="postgres://postgres:password@<source_db_ip>:5432/postgres"    -e TARGET_DB_URL="postgres://postgres:password@<target_db_ip>:5432/postgres"   -it --rm --network=<network> shayonj/pg_easy_replicate:latest   pg_easy_replicate config_check
/usr/bin/pg_dump
✅ Config is looking good.

Bootstrap

Every sync will need to be bootstrapped before you can set up the sync between two databases. Bootstrap creates a new super user to perform the orchestration required during the rest of the process. It also creates some internal metadata tables for record keeping.

$ docker run -e SOURCE_DB_URL="postgres://postgres:password@<source_db_ip>:5432/postgres"    -e TARGET_DB_URL="postgres://postgres:password@<target_db_ip>:5432/postgres"   -it --rm --network=pg_regress_localnet shayonj/pg_easy_replicate:latest pg_easy_replicate bootstrap --group-name database-cluster-1 --copy-schema

Start sync

Once the bootstrap is complete, you can start the sync. Starting the sync sets up the publication, subscription and performs other minor housekeeping things.

$ docker run -e SOURCE_DB_URL="postgres://postgres:[email protected]:5432/postgres"    -e TARGET_DB_URL="postgres://postgres:[email protected]:5432/postgres"   -it --rm --network=pg_regress_localnet shayonj/pg_easy_replicate:latest pg_easy_replicate start_sync --group-name database-cluster-1

Test logical replication

Query

Now we can query the target database to get the nearest neighbor of a vector in the embedding column of the test table. The result is the same as the source database.

postgres=# SELECT id FROM test ORDER BY embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
 id 
----
 82
 69
 80
 22
 31
 20
 85
 97
 72
(10 rows)

Update index

If you insert data into the source database, the data will be synchronized to the target database. Insert data into the source database:

INSERT INTO test
SELECT i, ARRAY[random(), random(),random(),random(),random(),random(),random(),random(),random()]::real[] 
FROM generate_series(101, 200) i;

Query in the source database:

postgres=# SELECT id FROM test ORDER BY embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
id  
-----
  82
 130
 145
  69
  80
 200
  22
  31
 182
 (10 rows)

Query in the target database, it will return the same result:

postgres=# SELECT id FROM test ORDER BY embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
id  
-----
  82
 130
 145
  69
  80
 200
  22
  31
 182
 (10 rows)

4 - Foreign data wrapper (FDW)

Foreign data wrapper(FDW) is an extension in PostgreSQL that allows you to access and query data stored in remote databases as if they were local tables. FDW pr

Foreign data wrapper(FDW) is an extension in PostgreSQL that allows you to access and query data stored in remote databases as if they were local tables. FDW provides a way to integrate and interact with different data sources.

With the FDW extension, you can define foreign tables in your local PostgreSQL database that mirror the structure of tables in remote databases. These foreign tables act as proxies or wrappers for the remote data, enabling you to perform SELECT, INSERT, UPDATE, and DELETE operations on them, just like regular tables in PostgreSQL.

In this tutorial, we will use the postgres_fdw module, which includes the foreign-data wrapper. This wrapper can be used to access data stored in external PostgreSQL servers. This article will explain how to use postgres_fdw to access index data in a foreign PostgreSQL cluster that already has the pgvecto.rs extension installed.

Deploy local and foreign PostgreSQL clusters

In this tutorial, we will use docker compose to deploy two PostgreSQL clusters.

$ echo 'version: "3.7"
services:
  local_db:
    image: tensorchord/pgvecto-rs:pg15-v0.2.0
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: password
    command: >
      -c shared_preload_libraries=vectors.so
    networks:
      localnet:

  foreign_db:
    image: tensorchord/pgvecto-rs:pg15-v0.2.0
    ports:
      - "5433:5432"
    environment:
      POSTGRES_PASSWORD: password
    command: >
      -c shared_preload_libraries=vectors.so
    networks:
      localnet:

networks:
  localnet:
' > docker-compose.yml

$ docker compose up -d

# create extension postgres_fdw in local db
$ psql -h <local_db_ip> -U postgres -p 5432 
DROP EXTENSION IF EXISTS postgres_fdw;
CREATE EXTENSION postgres_fdw;

# create extension vectors in foreign db
$ psql -h <foreign_db_ip> -U postgres -p 5432
DROP EXTENSION IF EXISTS "vectors";
CREATE EXTENSION "vectors";

Foreign database operations

First, we need create a table test and build an index on it in the foreign db. The test table has two columns: id and embedding. The embedding column is a vector column, and its type is vector(10). The id column is the primary key of the test table.

Create a table

DROP TABLE IF EXISTS test;
CREATE TABLE test (id integer PRIMARY KEY, embedding vector(10) NOT NULL);
INSERT INTO test SELECT i, ARRAY[random(),random(),random(),random(),random(),random(),random(),random(),random(),random()]::real[] FROM generate_series(1, 100) i;

Create a user

Create a user named fdw_user in foreign db, and grant SELECT, INSERT, UPDATE, DELETE privileges on table test to fdw_user.

CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret';
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE test TO fdw_user;

Create the index

We create an index on the embedding column of the test table. The index type is flat, it is a brute force algorithm. We choose vector_l2_ops squared Euclidean distance to measure the distance between vectors. Another index type and distance function can be found in here.

CREATE INDEX ON test USING vectors (embedding vector_l2_ops) WITH (options = "[indexing.flat]");

Local database operations

In local database, we need to create a table local and a foreign server foreign_server. The local table has two columns: id and name. The id column is the primary key of the local table. The foreign_server is a foreign server, which is used to access the foreign db.

Create local table

DROP TABLE IF EXISTS local;
CREATE TABLE local (id integer PRIMARY KEY, name VARCHAR(50) NOT NULL);
INSERT INTO local (id, name) VALUES (1, 'terry'), (2, 'jason'), (3, 'curry');

create local user

Using superuser, execute the following statement in the local PostgreSQL database to create a regular user named local_user.

CREATE USER local_user WITH ENCRYPTED PASSWORD 'secret';

Create the foreign server

To create an external server using the CREATE SERVER statement, you need to specify the host, port, and database name of the remote database.

CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '<foreign_db_ip>', port '5432', dbname 'postgres');

Create the user mapping

Use the CREATE USER MAPPING statement to create a mapping between remote users and local users, requiring the username and password of the remote user.

CREATE USER MAPPING FOR local_user
    SERVER foreign_server
    OPTIONS (user 'fdw_user', password 'secret');

Creat the foreign table

Use the CREATE FOREIGN TABLE statement to create a remote table. It is important to note that the types of each column should match those of the actual remote table, and it’s best to keep the column names consistent. Otherwise, you will need to use the column_name parameter to specify the column name in the remote table for each individual column.

CREATE FOREIGN TABLE foreign_test (id integer, embedding vector(10) NOT NULL)
    SERVER foreign_server
    OPTIONS (schema_name 'public', table_name 'test');

Authorization

Authorize the local_user:

Grant local_user the permission to use postgres_fdw.
Grant local_user the permission to use foreign server foreign_server.
Grant local_user the permission to access all tables in schema public.

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO local_user;
GRANT USAGE ON FOREIGN SERVER foreign_server TO local_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO local_user;

Query

Now we can use join query to access the foreign table in local db.

$ psql -h <local_db_ip> -U local_user -p 5432 -d postgres

SELECT l.id, l.name FROM local l LEFT JOIN foreign_test f on l.id = f.id ORDER BY f.embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
 id | name  
----+-------
  2 | jason
  3 | curry
  1 | terry


EXPLAIN SELECT l.id, l.name FROM local l LEFT JOIN foreign_test f on l.id = f.id ORDER BY f.embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]' LIMIT 10;
 Limit  (cost=209.63..209.65 rows=10 width=126)
   ->  Sort  (cost=209.63..213.04 rows=1365 width=126)
         Sort Key: ((f.embedding <-> '[0.40671515, 0.24202824, 0.37059402, 0.50316447, 0.10779921, 0.80774295, 0.8879849, 0.31292745, 0.05584943, 0.8738258]'::vector))
         ->  Hash Right Join  (cost=122.15..180.13 rows=1365 width=126)
               Hash Cond: (f.id = l.id)
               ->  Foreign Scan on foreign_test f  (cost=100.00..150.95 rows=1365 width=36)
               ->  Hash  (cost=15.40..15.40 rows=540 width=122)
                     ->  Seq Scan on local l  (cost=0.00..15.40 rows=540 width=122)

5 - Kubernetes

If you want to use pgvecto.rs in PostgreSQL which is running in Kubernetes, we provide a tutorial to help you. In this tutorial, we will use CloudNative-PG to d

If you want to use pgvecto.rs in PostgreSQL which is running in Kubernetes, we provide a tutorial to help you. In this tutorial, we will use CloudNative-PG to deploy PostgreSQL cluster in Kubernetes.

Requirements

Name	Version
helm	>= 2.4.1
kubectl	>= 1.14
Kubernetes	>= 1.24.0

Install CloudNative-PG

We use CloudNative-PG (cnpg) to deploy PostgreSQL cluster in Kubernetes. You can install it by helm.

$ sudo helm repo add cnpg https://cloudnative-pg.github.io/charts
$ sudo helm upgrade --install cnpg   --namespace cnpg-system   --create-namespace   cnpg/cloudnative-pg
$ helm list --all-namespaces
NAME                    NAMESPACE       REVISION        UPDATED                                 STATUS          CHART                         APP VERSION
cnpg                    cnpg-system     1               2024-01-02 08:17:11.661297545 +0000 UTC deployed        cloudnative-pg-0.20.0         1.22.0     
$ kubectl get pod -n cnpg-system   
NAME                                   READY   STATUS    RESTARTS   AGE
cnpg-cloudnative-pg-67ff4f5f66-94m8d   1/1     Running   0          1m

Build PostgreSQL image with `pgvecto.rs`

We use the official CloudNative-PG docker image as the base image, and install pgvecto.rs in it. And you can also find pgvecto.rs version from releases.

# syntax=docker/dockerfile-upstream:master
ARG CNPG_TAG

FROM ghcr.io/cloudnative-pg/postgresql:$CNPG_TAG

ARG CNPG_TAG
ARG PGVECTORS_TAG
ARG TARGETARCH

# drop to root to install packages
USER root

# install pgvecto.rs
ADD https://github.com/tensorchord/pgvecto.rs/releases/download/$PGVECTORS_TAG/vectors-pg${CNPG_TAG%.*}_${PGVECTORS_TAG#"v"}_$TARGETARCH.deb ./pgvectors.deb
RUN apt install ./pgvectors.deb

USER postgres

$ docker build -t <your-postgresql-image-repo>:15 --build-arg CNPG_TAG=15 --build-arg PGVECTORS_TAG=v0.1.13   --build-arg TARGETARCH=amd64 .

Notice: PostgreSQL docker image tag must start with CNPG_TAG for cloudnative-pg to recognize the postgres version.

Create PostgreSQL cluster

We provide a sample yaml file to create a PostgreSQL cluster in Kubernetes. You can modify it according to your needs. You need pay attention to the following points:

Set shared_preload_libraries to load pgvecto.rs shared library. You can also set postInitApplicationSQL ALTER SYSTEM SET shared_preload_libraries = "vectors.so"; to load pgvecto.rs shared library.
Execute CREATE EXTENSION IF NOT EXISTS "vectors"; to create vectors extension.

apiVersion: v1
kind: Secret
metadata:
  name: tensorchord
type: kubernetes.io/basic-auth
data:
  password: dGVuc29yY2hvcmQ= # tensorchord 
  username: dGVuc29yY2hvcmQ= # tensorchord
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pgvectors 
spec:
  instances: 1 
  bootstrap:
    initdb:
      database: tensorchord
      postInitApplicationSQL:
        - CREATE EXTENSION IF NOT EXISTS "vectors";
      owner: tensorchord
      secret:
        name: tensorchord
      dataChecksums: true
      encoding: 'UTF8'
  storage:
    size: 1Gi
  imageName: <your-postgresql-image-repo>:<pg-tag>
  postgresql:
    shared_preload_libraries: # load pgvecto.rs shared library
    - "vectors.so"

You can install cnpg kubectl plugin to manage your PostgreSQL cluster. Now we can check the status of the cluster.

$ sudo kubectl get pod
pgvectors-1                                                1/1     Running   0             3m54s

$ kubectl cnpg status pgvectors
Cluster Summary
Name:                pgvectors
Namespace:           default
System ID:           7323107530735296538
PostgreSQL Image:    docker.io/xieydd/pgvectors:15
Primary instance:    pgvectors-1
Primary start time:  2024-01-12 07:17:34 +0000 UTC (uptime 17m33s)
Status:              Cluster in healthy state 
Instances:           1
Ready instances:     1
Current Write LSN:   0/4000000 (Timeline: 1 - WAL File: 000000010000000000000003)

Certificates Status
Certificate Name       Expiration Date                Days Left Until Expiration
----------------       ---------------                --------------------------
pgvectors-ca           2024-04-11 07:12:00 +0000 UTC  89.98
pgvectors-replication  2024-04-11 07:12:00 +0000 UTC  89.98
pgvectors-server       2024-04-11 07:12:00 +0000 UTC  89.98

Continuous Backup status
Not configured

Physical backups
No running physical backups found

Streaming Replication status
Not configured

Unmanaged Replication Slot Status
No unmanaged replication slots found

Managed roles status
No roles managed

Tablespaces status
No managed tablespaces

Instances status
Name         Database Size  Current LSN  Replication role  Status  QoS         Manager Version  Node
----         -------------  -----------  ----------------  ------  ---         ---------------  ----
pgvectors-1  29 MB          0/4000000    Primary           OK      BestEffort  1.22.0           kind-control-plane

Notice: This article just provides a minimal configuration, not include logical replication, streaming logical replication, backup and restore, etc. We will provide those in another article. You can also refer to CloudNative-PG for more Cluster configuration.

Connect to PostgreSQL cluster

You can use kubectl port-forward to connect to PostgreSQL cluster.

$ kubectl port-forward services/pgvectors-rw 5432:5432
$ psql -h 127.0.0.1 -d tensorchord -U tensorchord
tensorchord=> \dx
Name   | Version |   Schema   |                                         Description                                          
---------+---------+------------+----------------------------------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vectors | 0.1.13  | public     | vectors: Vector database plugin for Postgres, written in Rust, specifically designed for LLM

The vectors extension is installed successfully. You can try quick-start without installation.

6 - Migration

When migrating from pgvector to PGVecto.rs, it is possible that you may have a table with a column of vector type. An example of this would be:

outline: [2,3]

PGVecto.rs Migration Overview

From pgvector

Prerequisites

When migrating from pgvector to PGVecto.rs, it is possible that you may have a table with a column of vector type. An example of this would be:

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

In addition, some rows will be inserted into the table, as follows:

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

In most cases, there will also be one or more vector indexes:

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

This is a typical scenario when pgvector is used. If the following requirements are met, the migration can be initiated.

Please note that your pgvector extension is not installed in the vectors schema. This means that you can install pgvector and PGVecto.rs at the same time.

Steps to Migration

1. Install PGVecto.rs

Please follow the instructions provided to install PGVecto.rs and ensure that the extension is loaded by PostgreSQL.

CREATE EXTENSION vectors;

The two extensions, pgvector and PGVecto.rs, are now installed at the PostgreSQL.

To validate the install, please run \dx in psql. You will see that there is an extension called vector for pgvector and another called vectors for PGVecto.rs.

postgres=# \dx
                                                 List of installed extensions
  Name   | Version |   Schema   |                                         Description                                          
---------+---------+------------+----------------------------------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vector  | 0.7.2   | public     | vector data type and ivfflat and hnsw access methods
 vectors | 0.3.0   | vectors    | vectors: Vector database plugin for Postgres, written in Rust, specifically designed for LLM

Please note that there are some symbols with the same name in both extensions. This may cause confusion when doing the migration. To differentiate between the two, please verify that the search_path does not include the PGVecto.rs schema vectors.

SHOW search_path;
-- if it shows: `"$user", public` -> This is okay
-- if it shows: `"$user", public, vectors` -> Remove `vectors` by:
SET search_path = "$user, public";

2. Check existing indexes

For indexes created at vector columns, please retrieve the definition and record it in a convenient location. This information is necessary for the recovery of the indexes at a later stage.

postgres=# SELECT indexdef FROM pg_indexes WHERE tablename = 'items';
                                       indexdef                                        
---------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX items_pkey ON public.items USING btree (id)
 CREATE INDEX items_embedding_idx ON public.items USING hnsw (embedding vector_l2_ops)
(2 rows)

3. Migrate vector columns

To migrate from a pgvector vector column, the first step is to create a PGVecto.rs vector column of the corresponding type. The subsequent step is to replicate the vector data from the original column to the new one.

Here provides the conversion path for each vector type of pgvector:

vector(pgvector) -> real[] -> vector(PGVecto.rs)
halfvec -> vector(pgvector) -> real[] -> vector(PGVecto.rs) -> vecf16
bit -> text[] -> real[] -> vector(PGVecto.rs) -> bvector

This vector type is not supported for migration yet:

sparsevec

Method 1: Conversion after validation

With this method, you can check the correctness of the vector data during the migration process, and you do not need to worry about the indexes that have been created on the vector columns, as they will be automatically deleted. However, it requires additional memory to store the vectors before and after the migration.

Warning

This step will cause some downtime, due to:

a short ACCESS EXCLUSIVE lock by ALTER TABLE ADD COLUMN
a much longer ROW EXCLUSIVE lock by UPDATE
a short ACCESS EXCLUSIVE lock by ALTER TABLE DROP COLUMN

-- From vector type
ALTER TABLE items ADD COLUMN migrate_embedding vectors.vector(3);
UPDATE items SET migrate_embedding = embedding::real[]::vectors.vector;

-- From halfvec type
ALTER TABLE items ADD COLUMN migrate_embedding vectors.vecf16(3);
UPDATE items SET migrate_embedding = embedding::vector::real[]::vectors.vector::vectors.vecf16;

-- For bit type
ALTER TABLE items ADD COLUMN migrate_embedding vectors.bvector(3);
UPDATE items SET migrate_embedding = string_to_array(embedding::text, NULL)::real[]::vectors.vector::vectors.bvector;

Once the data has been copied, you can check the data by SELECT and replace the existing column with the new one.

ALTER TABLE items DROP COLUMN embedding;
ALTER TABLE items RENAME COLUMN migrate_embedding TO embedding;

Method 2: Conversion without additional memory

With this method, the migration will be completed in one command and no additional memory is required. However, you need to delete the index on the vector column in advance.

Warning

This step will cause some downtime, due to:

a long ACCESS EXCLUSIVE lock by ALTER TABLE ALTER COLUMN

-- The name of index is from step 2
DROP INDEX IF EXISTS items_embedding_idx;

-- From vector type
ALTER TABLE items ALTER COLUMN embedding TYPE vectors.vector(3) USING embedding::real[]::vectors.vector;

-- From halfvec type
ALTER TABLE items ALTER COLUMN embedding TYPE vectors.vecf16(3) USING embedding::vector::real[]::vectors.vector::vectors.vecf16;

-- For bit type
ALTER TABLE items ALTER COLUMN embedding TYPE vectors.bvector(3) USING string_to_array(embedding::text, NULL)::real[]::vectors.vector::vectors.bvector;

4. Recreate index

To recreate index on vector columns for PGVecto.rs , please enable the compatibility mode and create it like before.

SET vectors.pgvector_compatibility=on;
-- Execute the command you get in step 2
CREATE INDEX items_embedding_idx ON public.items USING hnsw (embedding vector_l2_ops);

For other vector types, such as halfvec, sparsevec, and bit, please refer to the indexing. Currently, pgvector_compatibility mode is not supported for these types.

5. Clean the environment

You may now safely remove the pgvector extension if it is no longer required.

DROP extension vector;

In the event that the pgvector extension is removed, it is advisable to reset the search_path in order to ensure that symbols in PGVecto.rs are accessible globally.

SET search_path = "$user, public, vectors";
-- or globally
ALTER SYSTEM SET search_path = "$user, public, vectors";

Verify the result

Once the migration task is executed, you can verify the new columns by SELECT SQL.

SELECT embedding, pg_typeof("embedding") from items limit 3;

-- embedding |   pg_typeof    
-------------+----------------
-- [1, 2, 3] | vectors.vector
-- [4, 5, 6] | vectors.vector

Administration

1 - Configuration

Logging

Index configuration

Threads for backend optimizing

2 - Upgrading

General Steps

Upgrade to 0.4.0

Upgrade from 0.1.x

3 - Logical replication

Deploy source and target PostgreSQL clusters

Configure logical replication

Prepare test data

Create the table

Create the index

Insert data

Set up logical replication

Bootstrap

Start sync

Test logical replication

Query

Update index

4 - Foreign data wrapper (FDW)

Deploy local and foreign PostgreSQL clusters

Foreign database operations

Create a table

Create a user

Create the index

Local database operations

Create local table

create local user

Create the foreign server

Create the user mapping

Creat the foreign table

Authorization

Query

5 - Kubernetes

Requirements

Install CloudNative-PG

Build PostgreSQL image with pgvecto.rs

Create PostgreSQL cluster

Connect to PostgreSQL cluster

6 - Migration

outline: [2,3]

PGVecto.rs Migration Overview

From pgvector

Prerequisites

Steps to Migration

1. Install PGVecto.rs

2. Check existing indexes

3. Migrate vector columns

4. Recreate index

5. Clean the environment

Verify the result

Build PostgreSQL image with `pgvecto.rs`