google cloud SQL for postgreSQL v17対応かな？

google cloud SQL for postgreSQL をお触りしてみた（v17対応してる？）

この記事は[PostgreSQL Advent Calendar 2024](https://qiita.com/advent-calendar/2024/postgresql) の16日目です。

# はじめに
PostgreSQLに携わりそれなりの年月が立ちました。そろそろ現役引退を考えてしまう年齢にもなりどうしたものか？と日々過ごしています。
現職でPostgreSQLは利用していないのですが、現職で活躍させていただいているのはPostgreSQLが繋がりを作っていただいたこともあり、微力ながら恩返し？ができればと思っています。

今年は[PostgreSQLアンカンファレンス](https://pgunconf.connpass.com/)もあまり参加できずでしたが、体調管理も万全に[PostgreSQL Conference Japan 2024](https://www.postgresql.jp/jpug-pgcon2024)に参加させていただきました。
色んな人のお話を聞いたり、色んな人との繋がりを持てたりすることをカジュアルに行えるのが、オープンソースコミュニティの一番良いところだと再確認しました。

## 本記事のテーマ
今回は google cloud SQL for PostgreSQL の最新バージョンをサクッと立ち上げて、昨年はよくわかっていなかったpgVectorを少しだけ本格的に触ってみようと思います。

# 本題
## やったこと
1.compute Engineのインスタンス作成
2.cloud SQL for postgreSQL のインスタンス作成
3.pgVectorの導入
4.データロード
5.SQL実行

## 調理しま～す
### 1.compute Engineのインスタンス作成
:information_source:`詳細はpostgreSQLじゃないので割愛します`
データロード目的でpsqlを使いたいので、`e2-standard-2` でインスタンスを作成します。
その後、`sudo apt install -y postgresql-client`で、さくっとpsqlをインストールします。

### 2.cloud SQL for postgreSQLのインスタンス作成

postgreSQL は v17対応されてましたので、そちらを選択！
> 毎年12月の記事を書くときにまだ最新版が未対応だったりしてましたが、今年は対応してました！

![qiita2004-001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/0fcbe72f-5a4b-8404-b903-47e7a4d9e92c.png)
> AlloyDBで遊んでみたいですが、課金が怖くて無理です:innocent:

エディション選択は、お触り程度なので、`Enterprise` を選択します
![qiita2004-002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/178e4189-7b10-fb9b-6350-ee01dd414327.png)

プリセットは`開発環境`を選択します。
データベースバージョンは、`PostgreSQL 17`
インスタンスIDを入力
管理ユーザ`postgres` のパスワードを入力
![qiita2004-003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/89c3e844-a8bc-3f31-7d49-19b174717148.png)

ゾーンの可用性は、お触り程度なので、`シングルゾーン` を選択します
![qiita2004-004.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/bf474780-5ca4-0192-b328-0da10e382a6a.png)

ネットワークは、プライベートと念の為、パブリックも設定しておきます
![qiita2004-005.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/9db4a330-80f3-8dd6-5a88-e1c00bd55f6c.png)

バックアップ関連は、初期設定のままにしておきます
バックアップの時間帯だけ、使わない時間帯にしました
![qiita2004-006.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/a17356bd-1530-2d46-cbc5-8b6d0faecffe.png)

データベースフラグ（データベースパラメータ）は特に変更はしません。
v17で追加されたパラメータがあるかな？と確認しましたが、あるもの・ないものがありますね。
増分バックアップ関係のお触りをしてみようかと目論みましたが、バックアップは自動化されてたりするので、触れないようにしているのかもしれません。
> `summarize_wal`とか`wal_summary_keep_time`とかあるかと思いましたが選べないようです

![qiita2004-007.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/28ec0bdd-a93f-4230-3a8d-896d4bb75e4f.png)

インスタンス作成をぽち！りました。
オンプレ環境構築よりはすこぶる早いですが、それでも何分かは待つことになります
logging で確認して終わってるぽい
![qiita2004-008.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/a49a5295-c355-2e19-bef8-19b16ed150f9.png)
cloud SQLの管理画面の`オペレーションとログ`で確認すると`7分くらい`でした
![qiita2004-009.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/8bf26f61-9ad2-4f96-a536-4a7baf835799.png)

cloud SQLの管理メニューに`Cloud SQL Studio`があります
お仕事ではcloud SQLを使っていないので、いつ追加されたのか？気づきませんでした。
新規インスタンス作成のガイダンスメニューでは表示されていなかったので、環境構築後に確認した時に気づきました。
![qiita2004-010.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/c518123b-82c7-16d4-9b1a-12e9b7cbd52d.png)

なにやらデータベース管理画面のようです。接続してみます。
![qiita2004-011.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/dec1f0a5-f869-501d-b7f8-e475bdafb8d8.png)

概ね:laughing:`pgAdmin4`ですね:grin: 
> 記事の流れで、gceにインストールするかも？と思ってちょっと大きめのインスタンスにしましたが、不要でした。。。。

![qiita2004-012.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/83e76664-56ee-d6b7-fe63-04bbdf0efbea.png)

### 3.pgVectorの導入
gceにssh接続して、psqlでcloud SQLに接続します
さくっと接続できました
gceでupdateしなかったからか、psqlは`v16`で、serverは`v17.2`です
![qiita2004-013.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/bed14fb5-b26d-0bde-e6e5-a77540c44b02.png)

TYPOは色々ありますが、
導入済みのextensionを確認します
```SQL
SELECT * FROM pg_extension;
```
`plpgsql`しか入ってないです
pgVectorを導入します
```SQL
CREATE EXTENSION IF NOT EXISTS vector;
```
おお！pgVectorは[v0.8.0](https://github.com/pgvector/pgvector/blob/master/CHANGELOG.md#080-2024-10-30)ですね！
> pgVectorの新機能は確認しませんけど

![qiita2004-014.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/22be466b-f357-20a5-7dfb-2793fac63ad7.png)

### 4.データロード
検証（と言ってもお触り程度です）用のデータをロードします。

検証用データは、
- 2024/03の wikipedia jp の日本語データセットを利用
- MeCab-ipadic-neologdで形態素解析
- word2vecでベクトル化
    - ベクトル化は次のパラメータです```vector_size=200,window=5,min_count=5,sample=1e-3,negative=5,hs=0,workers=6```
- powershellを使うと言う`力技`:smiling_imp:でINSERT文を生成
- psql \\i でINSERT実行

で作成しました。
> こちらは[第34回 中国地方DB勉強会 in 広島](https://dbstudychugoku.connpass.com/event/316403/)で発表したときのデータを流用してます

importするテーブルを作ります
> 禁断のuuid(v4)のP-KEYなのは御愛嬌:sweat_smile:
```SQL
CREATE TABLE vmodel01
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  docs varchar,
  embedding vector(200)
);
```
![qiita2004-015.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/0f896b91-0f75-873f-e519-78d9b4f143d7.png)

gceにpsqlファイルをアップロードしてインポート（Insert実行）開始！
> Insertでエラー吐きまくって、手元に残ってたpsqlファイルは、データクレンジング前のものでした。アポストロフィと顔文字みたいな単語と『』と閉じ括弧を`PostgreSQL Language Server`の表示変化を確認しつつクレンジングして、再度実行しました。![qiita2004-016.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/45cf6662-9453-2694-53d4-75d467a90e80.png)

数分でエラーなくInsertが完了しました
![qiita2004-017.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/ff069cfa-3eaf-39a4-31f7-c894fc3a7032.png)

### 5.SQL実行
検索は、最近傍探索で行ってみます。
word2vec(python)のmost_similarの結果と比較してみます。

:::note info
同じベクトル化したデータで比較したかったのですが、SQL文作成処理の実行速度があまりにも遅く（レコード件数があまりに多すぎて）、SQL文の再作成が出来ていないです。
wikipediaのデータ（元データ）は同一でword2vecをベクトル化係数をあわせて再実行した結果で比較しています。
:::

Insertしたデータにある検索対象のベクトル値を元に最近傍探索の上位10件を表示します
```SQL
SELECT
  docs
  , 1 - (embedding <=> (SELECT embedding FROM vmodel01 WHERE docs='クラウド')) As cosdis
FROM vmodel01
WHERE
   (1 - (embedding <=> (SELECT embedding FROM vmodel01 WHERE docs='クラウド'))) > 0.5
ORDER BY cosdis DESC
LIMIT 10;
```

#### クラウド
![qiita2004-018.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/96234c5a-ad08-fcef-4eb2-7914994db66f.png)

word2vecの結果
```
vmodel01.wv.most_similar('クラウド')
[
 ('SaaS', 0.7533861994743347),
 ('クラウドコンピューティング', 0.7158094048500061),
 ('オンプレミス', 0.7138813138008118),
 ('グループウェア', 0.7120069861412048),
 ('オンラインストレージ', 0.6940287351608276),
 ('IoT', 0.681062638759613),
 ('ソリューション', 0.6732637882232666),
 ('クラウドベース', 0.6686390042304993),
 ('ブロックチェーン', 0.6576725840568542),
 ('Webサービス', 0.6571625471115112)
]
```

#### 広島
![qiita2004-019.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/c90e90f5-158f-c1e5-93ba-6b3c254719c8.png)

word2vecの結果
```
vmodel01.wv.most_similar('広島')
[
 ('福岡', 0.8193759322166443),
 ('熊本', 0.7683453559875488),
 ('岡山', 0.7587913870811462),
 ('愛媛', 0.7524473667144775),
 ('徳島', 0.7431946992874146),
 ('高知', 0.7337004542350769),
 ('神戸', 0.7320082783699036),
 ('新潟', 0.7303196787834167),
 ('北九州', 0.7289063334465027),
 ('富山', 0.7283827662467957)
]
```

#### 形態素解析
![qiita2004-020.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/3d50b93d-ff8a-6407-8b3a-06ef8b37d7ad.png)

word2vecの結果
```
vmodel01.wv.most_similar('形態素解析')
[
 ('機械翻訳', 0.8098910450935364),
 ('自然言語処理', 0.7758089900016785),
 ('MeCab', 0.7326663732528687),
 ('自然言語', 0.7311792969703674),
 ('コンパイラ最適化', 0.726638913154602),
 ('機械学習', 0.7245981693267822),
 ('コンピュータプログラム', 0.7233229875564575),
 ('かな漢字変換', 0.7197654247283936),
 ('パターンマッチング', 0.715961217880249),
 ('インプットメソッド', 0.7034615278244019)
]
```

#### アドベント
![qiita2004-021.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/321061/d8b935de-ad6d-58b1-9ce9-7f9b2f8d5e36.png)

word2vecの結果
```
vmodel01.wv.most_similar('アドベント')
[
 ('待降節', 0.7181289792060852),
 ('復活祭', 0.7133809924125671),
 ('降誕祭', 0.6975960731506348),
 ('四旬節', 0.6800898313522339),
 ('公現祭', 0.6762986779212952),
 ('ペンテコステ', 0.6743162870407104),
 ('イースター', 0.6716506481170654),
 ('聖金曜日', 0.649904191493988),
 ('復活大祭', 0.6457144021987915),
 ('灰の水曜日', 0.6446409821510315)
]
```

# まとめ
触ってみてわかったことですが、
- vector化する元データのリファレンスを解析結果に持たせることで、元データに新たなタグを生成できるかなぁ。
- 文字データを扱うときは、データクレンジングがとても重要で、膨大なデータを初めて取り扱うとデータの分布や特性はわからないので、解析前にクレンジングすることは出来ないなぁ。
- 一般的な利用シーンだとベンダーが提供するAPIを利用するのだろうと想像しますが、自分たちのビジネスに合わせた`ファインチューン`を行ったり、大量なリクエストをさばくために、PostgreSQLのようなRDBMSを活用する必要があるのだろうなぁ。
と、感じました。


この記事では、300万レコードくらいのvectorデータで検証するつもりでしたが、クレンジング済みのpsqlファイルを紛失（おそらく削除した）してしまい、手元に残っていたファイルで行いました。
期待値は、word2vecとSQLの検索結果がおなじになることでした。


word2vecのベクトル化係数を幾つか変更しながら、psqlファイルの結果と近い状態になるように探索したのですが、psqlファイルと同一の値を見つけることが出来ませんでした。
おそらくpsqlファイルのレコード数から、`min_count`がかなり大きな値なのだろうと思われます。
psqlファイルを作成する処理はgoとかで書くなどして性能検証してみたいです（冬休みの宿題かな:sweat_smile:）

検証でかかったクラウド利用料を載せようと思いましたが、締切に間に合わずです。コメントにでも登録したいと思います。



定年が見えてきたお年頃になり、新しい技術に追従するのにかなり時間がかかってしまうようになりました。
今年は`pgVector`を利用してちょこっとだけ活動できました。

最後までお読みいただき、ありがとうございます。少しでもお役に立てれば幸いです。