docker pull pgvector/pgvector:pg16

运行

docker run --name pgvector --restart=always -e POSTGRES_USER=pgvector -e POSTGRES_PASSWORD=pgvector -v /srv/tlw/pgvectordata:/var/lib/postgresql/data -p 54333:5432 -d pgvector/pgvector:pg16

CREATE EXTENSION vector;
--创建表
CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
--添加字段
ALTER TABLE items ADD COLUMN embedding1 vector(30);
--插入数据
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
--查询
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
SELECT * FROM items ORDER BY embedding <#> '[3,1,2]' LIMIT 5;
SELECT * FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;
--插入数据
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
--修改记录
UPDATE items SET embedding = '[1,2,3]' WHERE id = 1;
--删除记录
DELETE FROM items WHERE id = 1;
--Get the nearest neighbors to a row
SELECT * FROM items WHERE id != 1 ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;
--Get rows within a certain distance
SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;
--embedding 创建索引hnsw
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);
--embedding 创建索引ivfflat
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

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

--Use EXPLAIN ANALYZE to debug performance.性能分析
EXPLAIN ANALYZE SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
   

参考：GitHub - pgvector/pgvector: Open-source vector similarity search for Postgres