Vector Data / PostgreSQL

Posted on 2024/04/07, 7:56 AM By admin22

ベクトルデータの類似性検索ができるpgvectorというPostgresの拡張機能があると知り、試してみました。

参考）　https://www.sraoss.co.jp/tech-blog/pgsql/pgvector-intro/
環境）　PostgreSQL v16 / Mac(arm)

拡張機能はデフォルトで入っていたためcreate extensionコマンドで使えるようになりました。

ベクトルデータの類似性による分類は、下記の例などで使われます。

Support Vector Machine

mydb=# create extension vector;
CREATE EXTENSION
mydb=# \dx
                        List of installed extensions
  Name   | Version |   Schema   |                Description                 
---------+---------+------------+--------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vector  | 0.5.0   | public     | vector data type and ivfflat access method
(2 rows)

mydb=# CREATE TABLE items (id bigserial PRIMARY KEY, vec vector(3));
CREATE TABLE
mydb=# insert into items (vec) select array[random() * 100, random() * 100, random() * 100] from generate_series(1, 1000);
INSERT 0 1000
mydb=# select * from items limit 5;
 id |              vec               
----+--------------------------------
  1 | [43.189995,31.358536,26.84495]
  2 | [37.70672,48.738,26.866821]
  3 | [96.311966,53.182575,9.592497]
  4 | [5.7535515,89.065216,36.55039]
  5 | [70.51249,35.026676,40.871254]
(5 rows)

mydb=# select vec, vec <#> '[1,2,3]' from items order by vec <#> '[1,2,3]' limit 10;
              vec              |      ?column?      
-------------------------------+--------------------
 [81.54763,94.854904,96.15609] | -559.7257080078125
 [82.12906,90.73807,96.854774] | -554.1695556640625
 [75.41077,94.3053,92.4181]    |  -541.275634765625
 [52.821335,96.40333,98.39411] |  -540.810302734375
 [94.34945,78.67333,96.08497]  | -539.9510498046875
 [79.69239,95.93435,88.05331]  | -535.7210083007812
 [91.20255,86.22478,88.92222]  | -530.4187622070312
 [43.513527,96.38977,96.76844] |  -526.598388671875
 [91.98024,67.0477,99.18572]   |       -523.6328125
 [72.57968,78.32932,97.56131]  | -521.9222412109375
(10 rows)

mydb=#

mydb=# create extension vector;

CREATE EXTENSION

mydb=# \dx

List of installed extensions

Name | Version | Schema | Description

---------+---------+------------+--------------------------------------------

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

vector | 0.5.0 | public | vector data type and ivfflat access method

(2 rows)

mydb=# CREATE TABLE items (id bigserial PRIMARY KEY, vec vector(3));

CREATE TABLE

mydb=# insert into items (vec) select array[random() * 100, random() * 100, random() * 100] from generate_series(1, 1000);

INSERT 0 1000

mydb=# select * from items limit 5;

id | vec

----+--------------------------------

1 | [43.189995,31.358536,26.84495]

2 | [37.70672,48.738,26.866821]

3 | [96.311966,53.182575,9.592497]

4 | [5.7535515,89.065216,36.55039]

5 | [70.51249,35.026676,40.871254]

(5 rows)

mydb=# select vec, vec <#> '[1,2,3]' from items order by vec <#> '[1,2,3]' limit 10;

vec | ?column?

-------------------------------+--------------------

[81.54763,94.854904,96.15609] | -559.7257080078125

[82.12906,90.73807,96.854774] | -554.1695556640625

[75.41077,94.3053,92.4181] | -541.275634765625

[52.821335,96.40333,98.39411] | -540.810302734375

[94.34945,78.67333,96.08497] | -539.9510498046875

[79.69239,95.93435,88.05331] | -535.7210083007812

[91.20255,86.22478,88.92222] | -530.4187622070312

[43.513527,96.38977,96.76844] | -526.598388671875

[91.98024,67.0477,99.18572] | -523.6328125

[72.57968,78.32932,97.56131] | -521.9222412109375

(10 rows)

mydb=#

DBというと、どうしても条件の完全一致、部分的に一致したデータの抽出をイメージしがちでしたが、このような似たものをランク付けして抽出できるのは、最近のAIなどで使われそうな機能と思いました。

About admin22

Categories: 未分類タグ: SQL

About admin22

Recent Posts

Tag

Links

Archive