Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

PostgreSQLAdvent Calendar 2024

Day 16
@elhalti(Kei Morishita)in株式会社エムネス

google cloud SQL for postgreSQL v17対応かな？

Last updated at Posted at 2024-12-15

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

この記事はPostgreSQL Advent Calendar 2024 の16日目です。

はじめに

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

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

本記事のテーマ

今回は 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

AlloyDBで遊んでみたいですが、課金が怖くて無理です:innocent:

エディション選択は、お触り程度なので、Enterprise を選択します
qiita2004-002.png

プリセットは開発環境を選択します。
データベースバージョンは、PostgreSQL 17
インスタンスIDを入力
管理ユーザpostgres のパスワードを入力
qiita2004-003.png

ゾーンの可用性は、お触り程度なので、シングルゾーン を選択します
qiita2004-004.png

ネットワークは、プライベートと念の為、パブリックも設定しておきます
qiita2004-005.png

バックアップ関連は、初期設定のままにしておきます
バックアップの時間帯だけ、使わない時間帯にしました
qiita2004-006.png

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

summarize_walとかwal_summary_keep_timeとかあるかと思いましたが選べないようです

qiita2004-007.png

インスタンス作成をぽち！りました。
オンプレ環境構築よりはすこぶる早いですが、それでも何分かは待つことになります
logging で確認して終わってるぽい
qiita2004-008.png
cloud SQLの管理画面のオペレーションとログで確認すると7分くらいでした
qiita2004-009.png

cloud SQLの管理メニューにCloud SQL Studioがあります
お仕事ではcloud SQLを使っていないので、いつ追加されたのか？気づきませんでした。
新規インスタンス作成のガイダンスメニューでは表示されていなかったので、環境構築後に確認した時に気づきました。
qiita2004-010.png

なにやらデータベース管理画面のようです。接続してみます。
qiita2004-011.png

概ね:laughing:pgAdmin4ですね:grin:

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

qiita2004-012.png

3.pgVectorの導入

gceにssh接続して、psqlでcloud SQLに接続します
さくっと接続できました
gceでupdateしなかったからか、psqlはv16で、serverはv17.2です
qiita2004-013.png

TYPOは色々ありますが、
導入済みのextensionを確認します

SELECT * FROM pg_extension;

plpgsqlしか入ってないです
pgVectorを導入します

CREATE EXTENSION IF NOT EXISTS vector;

おお！pgVectorはv0.8.0ですね！

pgVectorの新機能は確認しませんけど

qiita2004-014.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 広島で発表したときのデータを流用してます

importするテーブルを作ります

禁断のuuid(v4)のP-KEYなのは御愛嬌:sweat_smile:

CREATE TABLE vmodel01
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  docs varchar,
  embedding vector(200)
);

qiita2004-015.png

gceにpsqlファイルをアップロードしてインポート（Insert実行）開始！

Insertでエラー吐きまくって、手元に残ってたpsqlファイルは、データクレンジング前のものでした。アポストロフィと顔文字みたいな単語と『』と閉じ括弧をPostgreSQL Language Serverの表示変化を確認しつつクレンジングして、再度実行しました。qiita2004-016.png

数分でエラーなくInsertが完了しました
qiita2004-017.png

5.SQL実行

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

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

Insertしたデータにある検索対象のベクトル値を元に最近傍探索の上位10件を表示します

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

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

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

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

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を利用してちょこっとだけ活動できました。

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

3
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?