pgvector - samwellwang

PostgreSQL 是一種開源的關係型資料庫管理系統，它被廣泛用於企業級應用程式和 Web 應用程式。與 MySQL 相比，PostgreSQL 提供了更高級別的功能和更豐富的資料類型。在本文中，我們將介紹 PostgreSQL 的一些重要概念和技巧。

PostgreSQL 與 MySQL 的比較

一個重要的區別是 PostgreSQL 支持插件，而且是開源的。mysql 到目前為止還沒有插件。

Pgvector 插件

Pgvector 插件是一個 PostgreSQL 擴展，它添加了向量類型和向量操作。它可以用於處理文本、圖像和音頻等資料類型。Pgvector 插件可以用於實現相似性搜索和聚類分析等任務。

使用 Pgvector 插件進行向量查詢

要使用 Pgvector 插件進行向量查詢，需要先安裝插件並創建一個向量列。以下是一個創建向量列的示例:

CREATE TABLE product_vectors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    vector (3) NOT NULL
);

在這個示例中，我們創建了一個名為 product_vectors 的表，其中包含一個名為 vector 的向量列。

要進行向量查詢。以下是一個計算歐氏距離的示例:

SELECT embedding,class_name,file_name,embedding <-> [1.0, 2.0, 3.0] as t FROM image_vector ORDER BY embedding <-> [1.0, 2.0, 3.0] LIMIT 20

在這個示例中，我們使用歐氏距離函數 <-> 計算每個向量與給定向量 '[1.0, 2.0, 3.0]' 之間的歐氏距離。結果按相似度排序。

使用 Pgvector 插件進行聚類分析

要使用 Pgvector 插件進行聚類分析，需要使用 kmeans 函數。以下是一個計算聚類中心的示例:

SELECT kmeans(vector, 3) AS centers
FROM product_vectors;

在這個示例中，我們使用 kmeans 函數將向量分為三個聚類，並計算每個聚類的中心。

單表分區

單表分區是一種將大型表拆分為多個小型表的技術。這可以提高查詢性能，並減少資料備份和恢復時間。在 PostgreSQL 中，可以使用表分區來實現單表分區。感覺上和 mysql 的視圖有點像。

以下是一個創建表分區的示例:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (date);

CREATE TABLE sales_2020_01 PARTITION OF sales
    FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');

CREATE TABLE sales_2020_02 PARTITION OF sales
    FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

在這個示例中，我們創建了一個名為 sales 的表，並將其分為兩個分區：sales_2020_01 和 sales_2020_02。每個分區包含一個特定日期範圍內的資料。

索引的使用

索引是一種加速查詢性能的技術。在 PostgreSQL 中，可以使用 B 樹索引、哈希索引和 GiST 索引等不同類型的索引。

以下是一個創建 B 樹索引的示例:

CREATE INDEX idx_sales_date ON sales (date);

在這個示例中，我們創建了一個名為 idx_sales_date 的 B 樹索引，以加速對 sales 表中日期列的查詢。

其他技巧

除了上述技巧之外，還有許多其他技巧可以提高 PostgreSQL 的性能和功能。以下是一些值得注意的技巧：

使用連接池來管理資料庫連接。
使用批量插入來提高插入性能。
使用 EXPLAIN 命令或者 ANALYZE EXPLAIN 來優化查詢計劃。查詢的結果可以在這個網站得到分析結果這裡
避免使用 SELECT * 語句。此處存疑，mysqk 都已經把這個優化了。
使用合適的資料類型來減少存儲空間。
定期清理無用資料。
使用備份和恢復策略來保護資料安全。

結論

在本文中，我們介紹了 PostgreSQL 的一些重要概念和技巧。我們還介紹了 Pgvector 插件、單表分區、索引使用和其他技巧。這些技巧可以幫助您提高 PostgreSQL 的性能和功能，並使其更適合於企業級應用程式和 Web 應用程式。

2021-10-20[]: # Path: MD\blog_ready\pgvector.md#

保存的 SQL draft#

    ---正式用---
CREATE TABLE image_vector_temp (
  id bigserial, 
  file_name varchar, 
  class_name varchar,
  embedding vector,
  PRIMARY KEY (id, class_name)
)PARTITION BY LIST (class_name);

CREATE TABLE image_vector_test_class2 PARTITION OF image_vector_test
  FOR VALUES IN ('class2');
  
select count(*) from image_vector_ma_dama
select *  from image_vector limit 10

select * from image_vector  where file_name = '1fbf05db0869976f9f6681f640e0e93f.jpg'
drop table image_vector_temp

show enable_partition_pruning

---測試用---------

CREATE TABLE image_vector_test (
  id bigserial,
  file_name varchar,
  class_name varchar,
  PRIMARY KEY (id, class_name)
) PARTITION BY LIST (class_name);


CREATE TABLE image_vector_test_class2 PARTITION OF image_vector_test
  FOR VALUES IN ('class2');
  
INSERT INTO image_vector_test (file_name,class_name) VALUES ('abcd','class2')  
  
explain select * from image_vector_test where class_name = 'class2'



EXPLAIN (ANALYZE, VERBOSE, BUFFERS)  SELECT id,file_name,class_name, embedding <-> '此處為face_recogntion生成的123維度的人臉向量'  
show ivfflat.probes
SET ivfflat.probes = 91
SELECT id,file_name,class_name, embedding <-> '此處為face_recogntion生成的123維度的人臉向量'  LIMIT 10 

SHOW max_worker_processes;


SELECT current_setting('max_worker_processes') AS max_worker_processes,
       current_setting('shared_buffers') AS shared_buffers,
       current_setting('effective_cache_size') AS effective_cache_size,
       current_setting('work_mem') AS work_mem,
       current_setting('maintenance_work_mem') AS maintenance_work_mem,
	   current_setting('max_parallel_workers_per_gather') AS max_parallel_workers_per_gather;
	   
SELECT phase, tuples_done, tuples_total FROM pg_stat_progress_create_index;


-- 加index  
CREATE INDEX ON image_vector USING ivfflat (embedding vector_l2_ops) WITH (lists = 8442);


ALTER TABLE image_vector ALTER COLUMN embedding SET DATA TYPE vector(128);
insert into image_vector_temp (file_name,class_name, embedding) values('acv','class1','此處為face_recogntion生成的123維度的人臉向量') 
CREATE TABLE image_vector_temp_class1 PARTITION OF image_vector_temp FOR VALUES IN ('class1')


SELECT id,file_name,class_name, embedding <-> '此處為face_recogntion生成的123維度的人臉向量' limit 20