vector

pig build pkg pgvector         # 构建 RPM 包

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION vector;

CREATE EXTENSION vector;

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

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

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

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

ALTER TABLE items ADD COLUMN embedding vector(3);

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

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

INSERT INTO items (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
    ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;

UPDATE items SET embedding = '[1,2,3]' WHERE id = 1;

DELETE FROM items WHERE id = 1;

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

SELECT * FROM items WHERE id != 1 ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;

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

SELECT embedding <-> '[3,1,2]' AS distance FROM items;

SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;

SELECT 1 - (embedding <=> '[3,1,2]') AS cosine_similarity FROM items;

SELECT AVG(embedding) FROM items;

SELECT category_id, AVG(embedding) FROM items GROUP BY category_id;

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);

CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);

CREATE INDEX ON items USING hnsw (embedding bit_jaccard_ops);

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 100;

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ...
COMMIT;

SET maintenance_work_mem = '8GB';

SET max_parallel_maintenance_workers = 7; -- 加上 leader 进程

SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);

CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

CREATE INDEX ON items USING ivfflat (embedding bit_hamming_ops) WITH (lists = 100);

SET ivfflat.probes = 10;

BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT ...
COMMIT;

SET max_parallel_maintenance_workers = 7; -- 加上 leader 进程

SELECT phase, round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

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

CREATE INDEX ON items (category_id);

CREATE INDEX ON items (location_id, category_id);

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

SET hnsw.ef_search = 200;

SET hnsw.iterative_scan = strict_order;

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);

CREATE TABLE items (embedding vector(3), category_id int) PARTITION BY LIST(category_id);

SET hnsw.iterative_scan = strict_order;

SET hnsw.iterative_scan = relaxed_order;
# or
SET ivfflat.iterative_scan = relaxed_order;

WITH relaxed_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance FROM items WHERE category_id = 123 ORDER BY distance LIMIT 5
) SELECT * FROM relaxed_results ORDER BY distance + 0;

WITH nearest_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance FROM items ORDER BY distance LIMIT 5
) SELECT * FROM nearest_results WHERE distance < 5 ORDER BY distance;

SET hnsw.max_scan_tuples = 20000;

SET hnsw.scan_mem_multiplier = 2;

SET ivfflat.max_probes = 100;

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

CREATE INDEX ON items USING hnsw ((embedding::halfvec(3)) halfvec_l2_ops);

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

CREATE TABLE items (id bigserial PRIMARY KEY, embedding bit(3));
INSERT INTO items (embedding) VALUES ('000'), ('111');

SELECT * FROM items ORDER BY embedding <~> '101' LIMIT 5;

CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(3)) bit_hamming_ops);

SELECT * FROM items ORDER BY binary_quantize(embedding)::bit(3) <~> binary_quantize('[1,-2,3]') LIMIT 5;

SELECT * FROM (
    SELECT * FROM items ORDER BY binary_quantize(embedding)::bit(3) <~> binary_quantize('[1,-2,3]') LIMIT 20
) ORDER BY embedding <=> '[1,-2,3]' LIMIT 5;

CREATE TABLE items (id bigserial PRIMARY KEY, embedding sparsevec(5));

INSERT INTO items (embedding) VALUES ('{1:1,3:2,5:3}/5'), ('{1:4,3:5,5:6}/5');

SELECT * FROM items ORDER BY embedding <-> '{1:3,3:1,5:2}/5' LIMIT 5;

SELECT id, content FROM items, plainto_tsquery('hello search') query
    WHERE textsearch @@ query ORDER BY ts_rank_cd(textsearch, query) DESC LIMIT 5;

CREATE INDEX ON items USING hnsw ((subvector(embedding, 1, 3)::vector(3)) vector_cosine_ops);

SELECT * FROM items ORDER BY subvector(embedding, 1, 3)::vector(3) <=> subvector('[1,2,3,4,5]'::vector, 1, 3) LIMIT 5;

SELECT * FROM (
    SELECT * FROM items ORDER BY subvector(embedding, 1, 3)::vector(3) <=> subvector('[1,2,3,4,5]'::vector, 1, 3) LIMIT 20
) ORDER BY embedding <=> '[1,2,3,4,5]' LIMIT 5;

SHOW config_file;

SHOW shared_buffers;

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

CREATE INDEX CONCURRENTLY ...

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

SET max_parallel_workers_per_gather = 4;

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

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 1000);

REINDEX INDEX CONCURRENTLY index_name;
VACUUM table_name;

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, ROUND((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    ROUND((total_plan_time + total_exec_time) / 60000) AS total_time_min
    FROM pg_stat_statements ORDER BY total_plan_time + total_exec_time DESC LIMIT 20;

BEGIN;
SET LOCAL enable_indexscan = off; -- 使用精确搜索
SELECT ...
COMMIT;

CREATE TABLE embeddings (model_id bigint, item_id bigint, embedding vector, PRIMARY KEY (model_id, item_id));

CREATE INDEX ON embeddings USING hnsw ((embedding::vector(3)) vector_l2_ops) WHERE (model_id = 123);

SELECT * FROM embeddings WHERE model_id = 123 ORDER BY embedding::vector(3) <-> '[3,1,2]' LIMIT 5;

CREATE TABLE items (id bigserial PRIMARY KEY, embedding double precision[]);

-- PostgreSQL 数组使用 {} 而非 []
INSERT INTO items (embedding) VALUES ('{1,2,3}'), ('{4,5,6}');

ALTER TABLE items ADD CHECK (vector_dims(embedding::vector) = 3);

CREATE INDEX ON items USING hnsw ((embedding::vector(3)) vector_l2_ops);

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

SELECT pg_size_pretty(pg_relation_size('index_name'));

-- 使用索引
ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- 不使用索引
ORDER BY 1 - (embedding <=> '[3,1,2]') DESC LIMIT 5;

BEGIN;
SET LOCAL enable_seqscan = off;
SELECT ...
COMMIT;

BEGIN;
SET LOCAL min_parallel_table_scan_size = 1;
SET LOCAL parallel_setup_cost = 1;
SELECT ...
COMMIT;

ALTER TABLE items ALTER COLUMN embedding SET STORAGE PLAIN;

DROP INDEX index_name;

cd /tmp
git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # 可能需要 sudo

set "PGROOT=C:\Program Files\PostgreSQL\18"
cd %TEMP%
git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

export PG_CONFIG=/Library/PostgreSQL/18/bin/pg_config

sudo --preserve-env=PG_CONFIG make install

sudo apt install postgresql-server-dev-18

pg_config --cppflags

make OPTFLAGS=""

docker pull pgvector/pgvector:pg18-trixie

git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
docker build --pull --build-arg PG_MAJOR=18 -t myuser/pgvector .

docker run --shm-size=1g ...

brew install pgvector

pgxn install vector

sudo apt install postgresql-18-pgvector

sudo yum install pgvector_18
# 或
sudo dnf install pgvector_18

pkg install postgresql17-pgvector

cd /usr/ports/databases/pgvector
make install

apk add postgresql-pgvector

conda install -c conda-forge pgvector

ALTER EXTENSION vector UPDATE;

SELECT extversion FROM pg_extension WHERE extname = 'vector';