1. 背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

1.1 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

1.2 《amd64 image》

ARM机器使用以下docker image:

1.3 《arm64 image》

2. 业务场景1 介绍相似图像搜索

传统数据库不支持图像类型, 图像相似计算函数, 图像相似计算操作服, 相似排序操作符. 所以遇到类似的需求, 需要自行编写应用来解决.

PG|PolarDB 通过imgsmlr插件, 可以将图像转换为向量特征值, 使用相似距离计算函数得到相似值, 使用索引加速相似度排序, 快速获得相似图片, 实现以图搜图.

也可以通过pgvector插件来存储图片向量特征值, 结合大模型服务(抠图、图像向量转换), 可以实现从图像转换、基于图像的相似向量检索全流程能力.

2.1 实现和对照

2.1.1 传统方法设计和实验

无

2.1.2 PolarDB|PG新方法1 设计和实验

1、下周一些测试图片, 例如使用搜索引擎, 搜索特朗普、马斯克、自行车、摩托车、房子, 分别下载5张. 拷贝到容器中.

docker cp ~/Downloads/img pg:/tmp/   
cd /tmp

或者使用我已经下载好的

curl -Z --connect-timeout 120 -m 36000 --retry 12000 --retry-delay 5 --retry-max-time 1200 -L https://github.com/digoal/blog/raw/master/202310/20231013_01_img_001.zip -o 20231013_01_img_001.zip  
unzip 20231013_01_img_001.zip  
cd /tmp/img

图片如下:

root@537879d580a3:/tmp/img# ll  
total 11M  
-rw-r--r-- 1  502 dialout  63K Oct 13 01:17 1.jpeg  
-rw-r--r-- 1  502 dialout  20K Oct 13 01:17 2.jpeg  
-rw-r--r-- 1  502 dialout  53K Oct 13 01:17 3.jpeg  
-rw-r--r-- 1  502 dialout  23K Oct 13 01:18 4.jpeg  
-rw-r--r-- 1  502 dialout  55K Oct 13 01:18 5.jpeg  
-rw-r--r-- 1  502 dialout 140K Oct 13 01:18 6.jpeg  
-rw-r--r-- 1  502 dialout 163K Oct 13 01:19 7.jpeg  
-rw-r--r-- 1  502 dialout 344K Oct 13 01:19 8.png  
-rw-r--r-- 1  502 dialout  51K Oct 13 01:19 9.jpeg  
-rw-r--r-- 1  502 dialout  63K Oct 13 01:19 10.jpeg  
-rw-r--r-- 1  502 dialout 626K Oct 13 01:19 11.jpeg  
-rw-r--r-- 1  502 dialout 864K Oct 13 01:20 12.jpeg  
-rw-r--r-- 1  502 dialout 414K Oct 13 01:20 13.jpeg  
-rw-r--r-- 1  502 dialout 350K Oct 13 01:20 14.jpeg  
-rw-r--r-- 1  502 dialout 1.6M Oct 13 01:20 15.jpeg  
-rw-r--r-- 1  502 dialout  24K Oct 13 01:20 16.jpeg  
-rw-r--r-- 1  502 dialout 708K Oct 13 01:20 17.jpeg  
-rw-r--r-- 1  502 dialout 386K Oct 13 01:20 18.jpeg  
-rw-r--r-- 1  502 dialout 759K Oct 13 01:20 19.jpeg  
-rw-r--r-- 1  502 dialout 906K Oct 13 01:21 20.jpeg  
-rw-r--r-- 1  502 dialout 191K Oct 13 01:21 21.jpeg  
-rw-r--r-- 1  502 dialout 206K Oct 13 01:21 22.jpeg  
-rw-r--r-- 1  502 dialout  78K Oct 13 01:21 23.jpeg  
-rw-r--r-- 1  502 dialout 2.0M Oct 13 01:21 24.jpeg  
-rw-r--r-- 1  502 dialout 421K Oct 13 01:22 25.jpeg  
drwxr-xr-x 2  502 dialout 4.0K Oct 13 01:24 .  
drwxrwxrwt 1 root root    4.0K Oct 13 01:25 ..

2、创建imgsmlr插件. https://github.com/postgrespro/imgsmlr

create extension imgsmlr;

3、创建测试表, 分别用于存储图片二进制数据, 签名.

create table img_raw (  
  id int primary key,  -- 图片ID  
  info text,  -- 图片描述  
  ts timestamp,  
  raw bytea  -- 图片二进制  
);  
create table img_vec (  
  id int primary key references img_raw(id),  -- 图片ID  
  patt pattern, -- 图片详细样式  
  sig signature  -- 图片签名  
);

4、创建图片签名索引, 用于加速相似搜索

CREATE INDEX on img_vec using gist (sig);

5、将图片二进制加载到数据库中

insert into img_raw values (1, 'jpeg,特朗普1', now(), pg_read_binary_file('/tmp/img/1.jpeg'));  
insert into img_raw values (2, 'jpeg,特朗普2', now(), pg_read_binary_file('/tmp/img/2.jpeg'));  
insert into img_raw values (3, 'jpeg,特朗普3', now(), pg_read_binary_file('/tmp/img/3.jpeg'));  
insert into img_raw values (4, 'jpeg,特朗普4', now(), pg_read_binary_file('/tmp/img/4.jpeg'));  
insert into img_raw values (5, 'jpeg,特朗普5', now(), pg_read_binary_file('/tmp/img/5.jpeg'));  
insert into img_raw values (6, 'jpeg,马斯克1', now(), pg_read_binary_file('/tmp/img/6.jpeg'));  
insert into img_raw values (7, 'jpeg,马斯克2', now(), pg_read_binary_file('/tmp/img/7.jpeg'));  
insert into img_raw values (8, 'png,马斯克3', now(), pg_read_binary_file('/tmp/img/8.png'));  
insert into img_raw values (9, 'jpeg,马斯克4', now(), pg_read_binary_file('/tmp/img/9.jpeg'));  
insert into img_raw values (10, 'jpeg,马斯克5', now(), pg_read_binary_file('/tmp/img/10.jpeg'));  
insert into img_raw values (11, 'jpeg,自行车1', now(), pg_read_binary_file('/tmp/img/11.jpeg'));  
insert into img_raw values (12, 'jpeg,自行车2', now(), pg_read_binary_file('/tmp/img/12.jpeg'));  
insert into img_raw values (13, 'jpeg,自行车3', now(), pg_read_binary_file('/tmp/img/13.jpeg'));  
insert into img_raw values (14, 'jpeg,自行车4', now(), pg_read_binary_file('/tmp/img/14.jpeg'));  
insert into img_raw values (15, 'jpeg,自行车5', now(), pg_read_binary_file('/tmp/img/15.jpeg'));  
insert into img_raw values (16, 'jpeg,摩托车1', now(), pg_read_binary_file('/tmp/img/16.jpeg'));  
insert into img_raw values (17, 'jpeg,摩托车2', now(), pg_read_binary_file('/tmp/img/17.jpeg'));  
insert into img_raw values (18, 'jpeg,摩托车3', now(), pg_read_binary_file('/tmp/img/18.jpeg'));  
insert into img_raw values (19, 'jpeg,摩托车4', now(), pg_read_binary_file('/tmp/img/19.jpeg'));  
insert into img_raw values (20, 'jpeg,摩托车5', now(), pg_read_binary_file('/tmp/img/20.jpeg'));  
insert into img_raw values (21, 'jpeg,房子1', now(), pg_read_binary_file('/tmp/img/21.jpeg'));  
insert into img_raw values (22, 'jpeg,房子2', now(), pg_read_binary_file('/tmp/img/22.jpeg'));  
insert into img_raw values (23, 'jpeg,房子3', now(), pg_read_binary_file('/tmp/img/23.jpeg'));  
insert into img_raw values (24, 'jpeg,房子4', now(), pg_read_binary_file('/tmp/img/24.jpeg'));  
insert into img_raw values (25, 'jpeg,房子5', now(), pg_read_binary_file('/tmp/img/25.jpeg'));

6、将图片转换为patt和sig写入img_vec表.

insert into img_vec select id, jpeg2pattern(raw), pattern2signature(jpeg2pattern(raw)) from img_raw where id<>8;  
insert into img_vec select id, png2pattern(raw), pattern2signature(png2pattern(raw)) from img_raw where id=8;

7、根据图片搜索相似图片, 例如根据图片5(特朗普5), 搜索与他相似的图片.

用到的操作符:

Operator	Left type	Right type	Return type	Description
`<->`	pattern	pattern	float8	Eucledian distance between two patterns
`<->`	signature	signature	float8	Eucledian distance between two signatures

select * from (  
  select   
    t1.id,  
    t2.info,  
    patt <-> (select patt from img_vec where id=5) as "图片相似距离"  
  from img_vec t1 join img_raw t2 using (id)  
    where t1.id <> 5  
    order by sig <-> (select sig from img_vec where id=5)  
    limit 20  
) t  
order by "图片相似距离" limit 10  
;  
                                                     QUERY PLAN                                                        
---------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=10.31..10.33 rows=10 width=40)  
   ->  Sort  (cost=10.31..10.36 rows=20 width=40)  
         Sort Key: t."图片相似距离"  
         ->  Subquery Scan on t  (cost=5.03..9.88 rows=20 width=40)  
               ->  Limit  (cost=5.03..9.68 rows=20 width=44)  
                     InitPlan 1 (returns $0)  
                       ->  Index Scan using img_vec_pkey on img_vec  (cost=0.15..2.37 rows=1 width=32)  
                             Index Cond: (id = 5)  
                     InitPlan 2 (returns $1)  
                       ->  Index Scan using img_vec_pkey on img_vec img_vec_1  (cost=0.15..2.37 rows=1 width=64)  
                             Index Cond: (id = 5)  
                     ->  Nested Loop  (cost=0.29..146.51 rows=629 width=44)  
                           ->  Index Scan using img_vec_sig_idx on img_vec t1  (cost=0.14..24.84 rows=629 width=100)  
                                 Order By: (sig <-> $1)  
                                 Filter: (id <> 5)  
                           ->  Index Scan using img_raw_pkey on img_raw t2  (cost=0.15..0.19 rows=1 width=36)  
                                 Index Cond: (id = t1.id)  
(17 rows)

结果如下:

select * from (  
  select   
    t1.id,  
    t2.info,  
    patt <-> (select patt from img_vec where id=5) as "图片相似距离"  
  from img_vec t1 join img_raw t2 using (id)  
    -- where t1.id <> 5  
    order by sig <-> (select sig from img_vec where id=5)  
    limit 20  
) t  
order by "图片相似距离" limit 10  
;  
 id |     info     | 图片相似距离   
----+--------------+--------------  
  5 | jpeg,特朗普5 |            0  
  1 | jpeg,特朗普1 |     4.338817  
  9 | jpeg,马斯克4 |     4.594334  
  3 | jpeg,特朗普3 |     4.752559  
  7 | jpeg,马斯克2 |     4.884595  
  6 | jpeg,马斯克1 |    4.9381833  
  8 | png,马斯克3  |    4.9781213  
  2 | jpeg,特朗普2 |    5.0608463  
 24 | jpeg,房子4   |     5.091457  
  4 | jpeg,特朗普4 |     5.091898  
(10 rows)

8、使用pgvector进行相似图片搜索. https://github.com/pgvector/pgvector

实际上imgsmlr插件将图片二进制转换为签名后, 签名就是向量.

postgres=# select sig from img_vec ;  
                                                                               sig                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 (1.270838, 0.500069, 0.427376, 1.366013, 0.652232, 0.414306, 1.269745, 0.585161, 0.310808, 1.543595, 0.634172, 0.477508, 1.154742, 0.022050, 0.245384, 0.299234)  
 (1.335650, 0.652390, 0.393671, 1.451109, 0.679567, 0.446861, 1.292568, 0.587897, 0.663593, 1.938486, 0.624385, 0.202768, 0.990529, 0.886401, 0.378102, 0.183046)  
 (1.259477, 0.463581, 0.302786, 1.391778, 0.522553, 0.387998, 1.161666, 0.570548, 0.167146, 2.493381, 0.330893, 1.010456, 0.672455, 0.279118, 0.387682, 0.309127)  
 (1.228738, 0.635732, 0.345098, 1.665270, 0.688938, 0.428735, 1.332798, 0.923155, 0.430726, 1.887990, 0.552760, 0.415664, 1.402567, 0.974207, 0.509415, 0.349885)  
 (1.038409, 0.546281, 0.308734, 0.979218, 0.603014, 0.447820, 1.423429, 0.566507, 0.385624, 1.655614, 0.204032, 0.143830, 0.633320, 0.156373, 0.284772, 0.270491)  
 (1.123810, 0.806228, 0.341440, 1.489432, 0.756371, 0.488137, 1.296715, 0.798313, 0.455280, 1.723156, 1.060410, 1.315746, 0.241827, 0.406081, 0.773475, 0.168470)  
 (1.051140, 0.929567, 0.571599, 1.103242, 1.172313, 0.564209, 0.851655, 1.037920, 0.407908, 1.940456, 0.267620, 0.240161, 1.027433, 0.625909, 0.209637, 0.286994)  
 (1.070145, 0.922921, 0.408203, 0.910248, 1.203817, 0.414439, 1.210977, 0.843991, 0.503972, 1.478885, 0.823880, 0.493970, 0.052616, 0.009954, 0.099550, 0.315186)  
 (1.014297, 0.790946, 0.369958, 1.375566, 1.175911, 0.615906, 1.725093, 1.100603, 0.888356, 2.429368, 0.790357, 0.533646, 0.841290, 0.094227, 0.512363, 0.762683)  
 (1.389513, 1.153228, 0.744124, 1.242119, 1.083352, 0.877110, 0.929370, 0.882974, 0.749842, 0.796497, 1.040739, 0.455902, 0.829046, 0.549320, 0.200105, 0.780134)  
 (1.840959, 1.533322, 0.982332, 1.549675, 1.161729, 1.026618, 0.961527, 1.288943, 1.012587, 0.722171, 0.803587, 0.251371, 0.195488, 1.123139, 0.510777, 0.816122)  
 (1.140996, 1.103443, 0.783740, 0.934260, 0.942023, 0.706223, 0.717351, 1.088809, 0.600009, 0.387582, 0.653488, 0.204923, 0.120064, 0.769957, 0.375608, 0.822386)  
 (1.378323, 1.336249, 0.771542, 1.593466, 1.709017, 1.007581, 1.874725, 1.797209, 0.741901, 0.739310, 0.825211, 0.507408, 0.200242, 2.302737, 0.302406, 0.803787)  
 (1.734405, 1.418693, 0.904873, 1.704088, 1.041535, 0.894270, 0.986760, 0.922298, 1.012795, 0.398549, 0.774437, 0.346058, 0.043864, 0.961082, 0.279609, 0.856370)  
 (1.017063, 0.891952, 0.518283, 1.296003, 0.889525, 0.631072, 1.028389, 1.016346, 0.826589, 0.387130, 1.553492, 0.452997, 0.136074, 0.344077, 0.492554, 0.406418)  
 (1.044747, 0.865196, 0.699529, 1.055459, 1.066624, 0.686950, 0.732742, 0.955629, 0.468359, 0.288743, 1.175085, 0.525265, 0.103813, 0.826011, 0.108001, 0.327941)  
 (1.192153, 1.132730, 0.581893, 1.432754, 1.358221, 0.844080, 1.964747, 1.332726, 0.668437, 2.398839, 2.569553, 1.546886, 0.579011, 1.008996, 0.233965, 0.703409)  
 (0.785036, 1.120896, 0.521437, 0.848337, 1.129619, 0.592841, 0.512232, 1.519339, 0.437692, 0.867090, 1.713499, 0.472001, 0.096089, 2.776011, 0.307675, 0.378741)  
 (1.259128, 1.213047, 0.697513, 0.934826, 1.419087, 0.731266, 0.649640, 2.099825, 0.328735, 0.527142, 1.159824, 0.554019, 0.167307, 0.191503, 0.732738, 0.403090)  
 (0.819240, 0.978856, 0.400882, 0.735962, 1.016948, 0.481790, 0.798128, 0.895937, 0.451740, 0.598053, 0.463426, 0.758902, 0.501993, 2.592159, 0.405716, 0.572428)  
 (0.940529, 1.066946, 0.390509, 0.910571, 1.120290, 0.573702, 0.610590, 1.507975, 0.660956, 2.136865, 1.391262, 0.710579, 0.953027, 0.843065, 0.473637, 0.436862)  
 (0.744365, 0.735474, 0.356165, 0.689846, 0.860880, 0.376143, 0.870404, 1.415974, 0.616256, 0.637707, 1.422517, 0.660677, 0.603397, 2.646075, 0.597267, 0.585969)  
 (0.961462, 0.949659, 0.479205, 1.132539, 1.038588, 0.534572, 0.960774, 1.406613, 0.639095, 1.050783, 0.710784, 0.296672, 1.935601, 1.559467, 0.900891, 0.385614)  
 (1.136182, 0.844896, 0.521564, 0.961399, 0.843313, 0.631543, 1.412504, 0.887028, 0.367572, 0.771699, 0.862837, 0.673957, 0.467803, 0.892946, 0.185740, 0.481828)  
 (0.802251, 0.639182, 0.310990, 1.108486, 0.686296, 0.386382, 1.180181, 0.798023, 0.610358, 2.261603, 1.103760, 0.213359, 0.305237, 1.335694, 0.323504, 0.468408)  
(25 rows)

可以将sig转换为Vector, 使用vector插件来进行相似检索.

create extension vector;

增加1列存储embedding

postgres=# alter table img_vec add column embedding vector;  
ALTER TABLE

将sig转换为Vector

postgres=# update img_vec set embedding = replace(replace(sig::text, '(', '{'), ')', '}')::float[]::vector;  
UPDATE 25

用到的操作符:

Operator	Description	Added
`+`	element-wise addition
`-`	element-wise subtraction
`*`	element-wise multiplication	0.5.0
`<->`	Euclidean distance
`<#>`	negative inner product
`<=>`	cosine distance

查询语句对比如下:

select   
    t1.id,  
    t2.info,  
    sig <-> (select sig from img_vec where id=5) as "图片相似距离"  
  from img_vec t1 join img_raw t2 using (id)  
    -- where t1.id <> 5  
    order by sig <-> (select sig from img_vec where id=5)  
    limit 20;  
  select   
    t1.id,  
    t2.info,  
    embedding <-> (select embedding from img_vec where id=5) as "图片相似距离"  
  from img_vec t1 join img_raw t2 using (id)  
    -- where t1.id <> 5  
    order by embedding <-> (select embedding from img_vec where id=5)  
    limit 20;  
  select   
    t1.id,  
    t2.info,  
    embedding <#> (select embedding from img_vec where id=5) as "图片相似距离"  
  from img_vec t1 join img_raw t2 using (id)  
    -- where t1.id <> 5  
    order by embedding <#> (select embedding from img_vec where id=5)  
    limit 20;  
  select   
    t1.id,  
    t2.info,  
    embedding <=> (select embedding from img_vec where id=5) as "图片相似距离"  
  from img_vec t1 join img_raw t2 using (id)  
    -- where t1.id <> 5  
    order by embedding <=> (select embedding from img_vec where id=5)  
    limit 20;

使用欧式距离查询, 结果一致:

id |     info     |    图片相似距离      
----+--------------+--------------------  
  5 | jpeg,特朗普5 |                  0  
  1 | jpeg,特朗普1 | 0.9242109025894523  
  2 | jpeg,特朗普2 | 1.1693161557029454  
  9 | jpeg,马斯克4 | 1.2604511140682175  
  7 | jpeg,马斯克2 | 1.2629390519659738  
  3 | jpeg,特朗普3 | 1.3601256742332883  
  4 | jpeg,特朗普4 | 1.4927405485877139  
 25 | jpeg,房子5   | 1.5683724484783568  
 10 | jpeg,马斯克5 | 1.6255270763384484  
  8 | png,马斯克3  | 1.7250440315487197  
  6 | jpeg,马斯克1 | 1.7355783085763679  
 11 | jpeg,自行车1 | 1.9072035608892033  
 13 | jpeg,自行车3 | 2.0770209111431517  
 22 | jpeg,房子2   | 2.1903881080956666  
 16 | jpeg,摩托车1 | 2.1914728327953057  
 17 | jpeg,摩托车2 | 2.2108417395891924  
 24 | jpeg,房子4   |  2.471026373061353  
 15 | jpeg,自行车5 |  2.552121988639544  
 12 | jpeg,自行车2 |  2.624972933675099  
 20 | jpeg,摩托车5 | 2.6716438773506574  
(20 rows)

3. 知识点

3.1 向量类型、计算函数、操作符、排序符

3.2 GiST索引接口, 扩展支持vector相似检索

3.3 将图片转成向量

3.4 vector, imgsmlr插件

3.5 模型集市:

《沉浸式学习PostgreSQL|PolarDB 16: 植入通义千问大模型+文本向量化模型, 让数据库具备AI能力》

4. 思考

试一试云服务, 结合抠图和向量, 实现人脸识别?

沉浸式学习PostgreSQL|PolarDB 21，相似图像搜索

1. 背景

2. 业务场景1 介绍相似图像搜索

2.1 实现和对照

2.1.1 传统方法设计和实验

2.1.2 PolarDB|PG新方法1 设计和实验

3. 知识点

4. 思考

PolarDB PostgreSQL 版

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像

沉浸式学习PostgreSQL|PolarDB 21，相似图像搜索

1. 背景

2. 业务场景1 介绍相似图像搜索

2.1 实现和对照

2.1.1 传统方法 设计和实验

2.1.2 PolarDB|PG新方法1 设计和实验

3. 知识点

4. 思考

PolarDB PostgreSQL 版

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像

2.1.1 传统方法设计和实验