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. 思考
试一试云服务, 结合抠图和向量, 实现人脸识别?
