This document describes the pgvector extension for storing and querying vector data.
Install the extension. This needs to be done once per database.
=# CREATE EXTENSION vector;Create a table with a 3-dimensional vector column.
=# CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));Insert vector data.
=# INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');Find nearest neighbors using L2 distance.
=# SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;Inner product (<#>) and cosine distance (<=>) are also supported.
Note!
<#>returns negative inner product, as YMatrix only supports ordered index scans for this operator.
This section demonstrates DDL and DML usage.
Create a new table with a vector column.
=# CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));Or add a vector column to an existing table.
=# ALTER TABLE items ADD COLUMN embedding vector(3);Insert vector data.
=# INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');Upsert vector data.
=# INSERT INTO items (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;Update vector data.
=# UPDATE items SET embedding = '[1,2,3]' WHERE id = 1;Delete vector data.
=# DELETE FROM items WHERE id = 1;This section shows how to write query SQL statements.
Find nearest neighbors of a given vector.
=# SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;Find nearest neighbors of a specific row.
=# SELECT * FROM items WHERE id != 1 ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;Find rows within a certain distance.
=# SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;Note!
Queries must include both ORDER BY and LIMIT clauses to utilize indexes.
Compute distances.
=# SELECT embedding <-> '[3,1,2]' AS distance FROM items;For inner product, multiply by -1 (since <#> returns negative inner product).
=# SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;For cosine similarity, subtract cosine distance from 1.
=# SELECT 1 - (embedding <=> '[3,1,2]') AS cosine_similarity FROM items;Compute average vector.
=# SELECT AVG(embedding) FROM items;Compute grouped average vectors.
=# SELECT id, AVG(embedding) FROM items GROUP BY id;By default, pgvector performs exact nearest neighbor search, providing perfect recall.
You can create an index to enable approximate nearest neighbor (ANN) search, trading some recall for speed. Unlike traditional indexes, ANN indexes may return different results on repeated queries.
Supported index types:
IVFFlat is a clustering-based index that partitions high-dimensional space into clusters and searches only the clusters closest to the query vector. It has faster build time and lower memory usage than HNSW, but relatively lower query performance in terms of speed-recall trade-off.
Three keys to good recall:
rows / 1000 for up to 1M rows, or sqrt(rows) beyond 1M rows.sqrt(lists).Create one IVFFlat index per distance function you want to use.
L2 distance:
=# CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);Inner product:
=# CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);Cosine distance:
=# CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);Vectors up to 2000 dimensions can be indexed.
Set the number of probes (default is 1).
=# SET ivfflat.probes = 10;Higher values improve recall at the cost of speed. You can set it equal to the number of lists for exact nearest neighbor search (in which case the optimizer will not use the index).
Use SET LOCAL within a transaction to apply the setting to a single query.
=# BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT ...
COMMIT;HNSW is a graph-based index that builds a multi-layered graph. The bottom layer contains all vectors, and higher layers contain fewer nodes. Compared to IVFFlat, HNSW has slower build time and higher memory usage, but better query performance in terms of speed-recall trade-off. It does not require training, so you can create the index even when the table is empty.
Create one HNSW index per distance function.
L2 distance:
=# CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);Inner product:
=# CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);Cosine distance:
=# CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);Vectors up to 2000 dimensions can be indexed.
Specify HNSW parameters:
m - maximum connections per layer (default: 16)ef_construction - size of dynamic candidate list during graph construction (default: 64)=# CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);Set the size of the dynamic candidate list during search (default: 40).
=# SET hnsw.ef_search = 100;Higher values improve recall at the cost of speed.
Use SET LOCAL within a transaction for per-query settings.
=# BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ...
COMMIT;Monitor index creation progress.
=# SELECT phase, tuples_done, tuples_total FROM pg_stat_progress_create_index;Phases:
initializingloading tuples - IVFFlat onlyclustering - IVFFlat onlyindexingNote!
tuples_done and tuples_total are only populated during the loading tuples phase.
There are several ways to filter nearest neighbor queries using WHERE clauses.
=# SELECT * FROM items WHERE id = 123 ORDER BY embedding <-> '[3,1,2]' LIMIT 5;Create an index on one or more columns used for exact filtering.
=# CREATE INDEX ON items (id);Or create a partial index on the vector column for filtered approximate search.
=# CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100)
WHERE (id = 123);Use partitioning for efficient filtering across many distinct values of a column.
=# CREATE TABLE items (embedding vector(3), id int) PARTITION BY LIST(id);import psycopg2
def connect_to_database():
# Connect to the database
try:
conn = psycopg2.connect(
database="your_database_name",
user="your_database_user",
password="your_database_password",
host="your_database_host",
port="your_database_port"
)
return conn
except psycopg2.Error as e:
print(f"Error connecting to the database: {e}")
return None
def close_database_connection(conn):
# Close the database connection
if conn:
conn.close()
def create_extension(conn, extension_name):
try:
cursor = conn.cursor()
# Execute insert operation
cursor.execute('create extension if not exists %s ;' % extension_name)
conn.commit()
cursor.close()
print("extension %s has been created successfully." % extension_name)
except psycopg2.Error as e:
print(f"Error create extension: {e}")
def create_table(conn, tablename, sql_tabledef):
try:
cursor = conn.cursor()
# Create vector table
cursor.execute(sql_tabledef)
conn.commit()
cursor.close
print('table %s has been created successfully.' % tablename)
except psycopg2.Error as e:
print(f"Error inserting data: {e}")
def insert_data(conn, tablename, data):
try:
cursor = conn.cursor()
# Execute insert operation
cursor.execute("INSERT INTO %s VALUES ('%s',%s, %s,'%s')" % (
tablename, data['value1'], data['value2'], data['value3'], data['value4']))
conn.commit()
cursor.close()
print("Data inserted successfully.")
except psycopg2.Error as e:
print(f"Error inserting data: {e}")
def select_data(conn, tablename):
try:
cursor = conn.cursor()
# Execute query operation
cursor.execute("SELECT * FROM %s" % tablename)
result = cursor.fetchall()
cursor.close()
return result
except psycopg2.Error as e:
print(f"Error selecting data: {e}")
return []
def vector_search(conn, tablename, vector_type, nearest_values):
try:
cursor = conn.cursor()
# Execute query operation
cursor.execute(
"SELECT * FROM %s order by embedding %s '%s' limit 5;" % (tablename, vector_type, nearest_values))
result = cursor.fetchall()
cursor.close()
return result
except psycopg2.Error as e:
print(f"Error selecting data: {e}")
return []
def update_data(conn, tablename, data):
try:
cursor = conn.cursor()
# Execute update operation
cursor.execute(
"UPDATE %s SET embedding = '%s' WHERE id = %s" % (tablename, data['new_value'], data['condition']))
conn.commit()
cursor.close()
print("Data updated successfully.")
except psycopg2.Error as e:
print(f"Error updating data: {e}")
def delete_data(conn, tablename, condition):
try:
cursor = conn.cursor()
# Execute delete operation
cursor.execute("DELETE FROM %s WHERE id = %s" % (tablename, condition))
conn.commit()
cursor.close()
print("Data deleted successfully.")
except psycopg2.Error as e:
print(f"Error deleting data: {e}")
if __name__ == "__main__":
conn = connect_to_database()
if conn:
# Perform database operations here
data_to_insert = {
'value1': '2023-10-12 00:00:00',
'value2': 1,
'value3': 1,
'value4': [1, 2, 3]
}
# Create vector extension
create_extension(conn, 'vector')
# Create vector table
sql_tabledef = 'drop table if exists documents_l2;' \
'CREATE TABLE documents_l2(' \
'created_at timestamptz,' \
'id integer,' \
'document_type int,' \
'embedding vector(3)' \
')' \
'distributed by (id);';
create_table(conn, 'documents_l2', sql_tabledef)
# Insert data into table
insert_data(conn, 'documents_l2', data_to_insert)
# Query data
data_to_select = select_data(conn, 'documents_l2')
print("Selected Data:", data_to_select)
# Update data
data_to_update = {
'new_value': '[4,5,6]',
'condition': 1
}
update_data(conn, 'documents_l2', data_to_update)
# Query data
data_to_select = select_data(conn, 'documents_l2')
print("Selected Data:", data_to_select)
# Vector search
print('Vector search: L2 distance')
data_vector_search = vector_search(conn, 'documents_l2', '<->', '[4,5,6]')
print("vector search (L2):", data_vector_search)
print('Vector search: inner product')
data_vector_search = vector_search(conn, 'documents_l2', '<#>', '[4,5,6]')
print("vector search (IP):", data_vector_search)
print('Vector search: cosine distance')
data_vector_search = vector_search(conn, 'documents_l2', '<=>', '[4,5,6]')
print("vector search (cos):", data_vector_search)
# Delete data
data_to_delete = 1
delete_data(conn, 'documents_l2', data_to_delete)
# Query data
data_to_select = select_data(conn, 'documents_l2')
print("Selected Data:", data_to_select)
close_database_connection(conn)package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
type Data struct {
Id int32
Embedding string
}
func getConn() (*sql.DB, error) {
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", "192.168.100.30", 6432, "mxadmin", "", "mxadmin")
db, err := sql.Open("postgres", psqlInfo)
if err != nil {
return nil, err
}
return db, nil
}
func insertData() error {
db, _ := getConn()
defer db.Close()
_, err := db.Exec(`INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]')`)
return err
}
func selectData() ([]Data, error) {
db, _ := getConn()
defer db.Close()
datas := make([]Data, 0)
rows, err := db.Query(`SELECT id, embedding FROM items`)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
data := Data{}
err = rows.Scan(&data.Id, &data.Embedding)
if err != nil {
return nil, err
}
datas = append(datas, data)
}
return datas, err
}
func main() {
insertData()
datas, _ := selectData()
fmt.Println(datas)
}