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