Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた

第 39 回 PostgreSQL アンカンファレンス@オンライン 2023/2/20

Transcript

1. Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた 第 39 回 PostgreSQL アンカンファレンス@オンライン　 2023/2/20 まつひさ（hmatsu47）
2. 自己紹介 松久裕保（@hmatsu47） • https://qiita.com/hmatsu47 • 現在のステータス： ◦ 名古屋で Web インフラのお守り係をしています ◦ Aurora MySQL v1 → v3 移行完了（開発・お試し用含む） ▪ https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book ▪ https://zenn.dev/hmatsu47/books/aurora-mysql-do-book 2
3. 今回の発表ネタ • Supabase で PGroonga がサポートされた https://www.clear-code.com/blog/2023/1/17/supabase-support-pgroonga.html ◦ Supabase でマネージドな PGroonga を使える！ • 第 29 回「Flutter で Supabase の PostgreSQL with PostGIS を試してみた」の Flutter アプリに PGroonga 全文検索を実装した ◦ スポット検索機能 3
4. 最近の発表ネタが Supabase に偏っている件 4 回 タイトル 開催日 15 Alibaba Cloud の PolarDB が東京にやってきたのであらためて試してみた（7 ヶ月ぶり 2 回目） 2020/07/30 17 IPA データベーススペシャリスト試験が秋に実施されると聞いたので PostgreSQL と絡めて紹介してみる 2020/09/24 18 MySQL と PostgreSQL と INDEX（良いタイトルが思い浮かばなかったので考えるのを諦めた） 2020/11/02 19 MySQL と PostgreSQL と主キー（良いタイトルが思い浮かばなかったので考えるのを諦めた・その②） 2020/12/07 21 RDS / Auroraパフォーマンスインサイトを使ってみる（PostgreSQL 編） 2021/03/02 29 Flutter で Supabase の PostgreSQL with PostGIS を試してみた 2021/12/21 33 SolidJS で Supabase の Row Level Security を試してみた 2022/05/31 37 Supabase で TCE（透過的列暗号化）を試してみた 2022/12/20 39 Supabase で PGroonga がサポートされたので Flutter アプリから使ってみた 2023/02/20
5. 関連記事 • https://zenn.dev/hmatsu47/articles/supabase_pgroonga_flutter ◦ Flutter で Supabase の PGroonga 全文検索を試してみた • https://zenn.dev/hmatsu47/articles/supabase_pgroonga_synonyms ◦ Supabase の PGroonga 全文検索で同義語検索してみる • https://zenn.dev/hmatsu47/articles/supabase_pgroonga_stopword_wa ◦ Supabase の PGroonga 全文検索でストップワード対応のワーク アラウンドを試してみる 5
6. Supabase とは？（おさらい） • BaaS（Backend as a Service）の一つ ◦ Firebase Alternative • サービスは 4 つ（それぞれの機能は以前よりも増えている） ◦ Database ← PostgreSQL が使われている ◦ Authentication ◦ Storage ◦ Edge Functions 6
7. PGroonga とは？（説明不要かもしれませんが、一応） • ざっくり ◦ PostgreSQL 向け拡張機能（Extension） ◦ 全文検索エンジン「Groonga」を使用 ◦ 高速な日本語全文検索が可能 ◦ トークナイザーには N-gram・MeCab などが使用可能 7
8. Flutter とは？ • Google 製の UI フレームワーク ◦ 使う言語は Dart ◦ 当初はクロスプラットフォームモバイルアプリ開発用 ◦ その後、Web や Windows / macOS / Linux も対象に ◦ 先月バージョン 3.7 が出た ▪ もうすぐ 4.0 が出る模様 8
9. 実装対象の Flutter アプリ（maptool） • https://github.com/hmatsu47/maptool • 主な機能 ◦ 訪問（予定）地へのピン立て（登録）と写真撮影 ◦ 登録ピンと関連づけて写真撮影 ◦ 登録ピンの検索 ◦ 文化財などの近隣スポット検索←ここで PostGIS を利用 ◦ スポット名と説明文の全文検索←New!! 9
10. 実装対象の Flutter アプリ（maptool） • 訪問（予定）地へのピン立て（登録） 10 注：画面は少し古めのバージョン です（以降同じ）
11. 実装対象の Flutter アプリ（maptool） • 登録ピンと関連づけて写真撮影 11
12. 実装対象の Flutter アプリ（maptool） 12 • 文化財などの近隣スポット検索（Supabase / PostGIS） ※第 29 回の発表後、カテゴリ別の 　ピン表示にも対応 ▪
13. 実装対象の Flutter アプリ（maptool） 13 • スポットの全文検索（Supabase / PostGIS / PGroonga） ▪ PGroonga でキーワードを全文検索して ▪ PostGIS で距離が近い順に表示 • 地図の中心が起点 注：サンプルデータとして、以下を改変して利用 • 愛知県文化財マップ（ナビ愛知）、愛知県、クリエイ ティブ・コモンズ・ライセンス 表示２.１日本 • https://www.pref.aichi.jp/soshiki/joho/0000069385.html
14. Supabase で全文検索を使うには • Supabase（Database）側で設定 ◦ PGroonga を有効化 ◦ 検索対象テーブルに全文検索用インデックスを追加 ◦ 全文検索を使うストアドファンクションを実装 • クライアント側のコードを実装 ◦ RPC でストアドファンクションを呼び出す ▪ 関数や特殊な表現はクエリビルダーでは使用不可（PostGIS と同様） 14
15. 注意 • 通常は RLS（行レベルセキュリティ）と認証を実装する ◦ 今回は自分一人で利用するアプリであり、アプリストアなどでの 公開もしないので RLS とユーザー認証を実装していない ◦ 通常のアプリでは、情報漏洩や API の悪用防止のため RLS と ユーザー認証を実装する 15
16. PGroonga を有効化 • Database - Extensions で「PGROONGA」を有効化 16
17. 「PGROONGA」が見つからないときは • Setting - General - Infrastructure の Pause project ◦ 一旦プロジェクトを停止後、再び起動する 17 Restart serverではなく 使うのはこちら→
18. 検索対象テーブルに全文検索用インデックスを追加 • 元のテーブル・インデックス定義はこちら CREATE TABLE spot_opendata ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, category_id int REFERENCES category (id) NOT NULL, title text NOT NULL, describe text NOT NULL, location geometry(point, 4326) NOT NULL, prefecture text NOT NULL, municipality text NOT NULL, pref_muni text GENERATED ALWAYS AS (prefecture || municipality) STORED, created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL, updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL ); CREATE INDEX spot_location_idx ON spot_opendata USING GIST (location); CREATE INDEX spot_pref_idx ON spot_opendata (prefecture); CREATE INDEX spot_muni_idx ON spot_opendata (municipality); CREATE INDEX spot_pref_muni_idx ON spot_opendata (pref_muni); 18
19. 検索対象テーブルに全文検索用インデックスを追加 • 全文検索用の生成列とインデックスを追加 ◦ https://pgroonga.github.io/ja/tutorial/ • スポットのタイトル・説明文・都道府県＋市区町村が検索対象 • トークナイザーとして MeCab（形態素解析器）を指定 ALTER TABLE spot_opendata ADD COLUMN ft_text text GENERATED ALWAYS AS (title || ',' || describe || ',' || prefecture || municipality) STORED; CREATE INDEX pgroonga_content_index ON spot_opendata USING pgroonga (ft_text) WITH (tokenizer='TokenMecab'); 19
20. 全文検索を使うストアドファンクションを実装 • 近隣スポット検索のストアドファンクションを変更 • 検索キーワード「keywords」を引数に追加 CREATE OR REPLACE FUNCTION get_spots(point_latitude double precision, point_longitude double precision, dist_limit int, category_id_number int, keywords text) RETURNS TABLE ( distance double precision, category_name text, title text, describe text, latitude double precision, longitude double precision, prefecture text, municipality text ) AS $$ 20
21. 全文検索を使うストアドファンクションを実装 • このあたりは変更なし BEGIN RETURN QUERY SELECT ((ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) / 1000) AS distance, category.category_name, spot_opendata.title, spot_opendata.describe, ST_Y(spot_opendata.location), ST_X(spot_opendata.location), spot_opendata.prefecture, spot_opendata.municipality FROM spot_opendata INNER JOIN category ON spot_opendata.category_id = category.id 21
22. 全文検索を使うストアドファンクションを実装 ◦ 「ft_text &@~ keywords」が全文検索を行っている箇所 ▪ 「LIKE」は性能面で不利なので「&@」または「&@~」を使うほうが良い WHERE (CASE WHEN dist_limit = -1 AND keywords = '' THEN false ELSE true END) AND (CASE WHEN dist_limit = -1 THEN true ELSE (ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) <= dist_limit END) AND (CASE WHEN category_id_number = -1 THEN true ELSE category.id = category_id_number END) AND (CASE WHEN keywords = '' THEN true ELSE ft_text &@~ keywords END) ORDER BY distance; END; $$ LANGUAGE plpgsql; 22
23. ストアドファンクションを直接呼び出してみる • Database - SQL Editor で「get_spots」を呼び出す 23
24. クライアント側のコードを実装 • SupabaseClient を使って接続 ◦ 今回、Flutter 用のライブラリ「supabase_flutter」に変更 ▪ ここでの使い方は以前の「supabase」（Dart 用ライブラリ）と同じ import 'package:mapbox_gl/mapbox_gl.dart'; import 'package:supabase_flutter/supabase_flutter.dart'; import 'class_definition.dart'; // Supabase Client SupabaseClient getSupabaseClient(String supabaseUrl, String supabaseKey) { return SupabaseClient(supabaseUrl, supabaseKey); } 24
25. RPC でストアドファンクションを呼び出す（変更） Future<List<SpotData>> searchNearSpot(SupabaseClient client, LatLng latLng, int? distLimit, int? categoryId, String? keywords) async { final List<dynamic> items = await client.rpc('get_spots', params: { 'point_latitude': latLng.latitude, 'point_longitude': latLng.longitude, 'dist_limit': (distLimit ?? -1), 'category_id_number': (categoryId ?? -1), 'keywords': (keywords ?? '') }); final List<SpotData> resultList = []; for (dynamic item in items) { final SpotData spotData = SpotData( item['distance'] as num, item['category_name'] as String, item['title'] as String, item['describe'] as String, LatLng((item['latitude'] as num).toDouble(), (item['longitude'] as num).toDouble()), PrefMuni(item['prefecture'] as String, item['municipality'] as String)); resultList.add(spotData); } return resultList; } 25 ←以前のコードに引数「keywords」を追加しただけ 　（ライブラリのバージョンアップで最後の 　「.execute()」が不要に）
26. 同義語検索を追加する • Supabase（Database）側で設定 ◦ https://pgroonga.github.io/ja/how-to/synonym-expansion.html ◦ 同義語テーブルを作成 ◦ 同義語テーブルにインデックスを追加 ◦ ストアドファンクションに同義語展開の実装を追加 • クライアント側のコードには変更なし 26
27. 同義語テーブルを作成し、インデックスを追加 • 同義語テーブルに同義語データを追加 ◦ 「美術館」→「ミュージアム」 ◦ 「博物館」→「ミュージアム」 ◦ 「ミュージアム」→「美術館」「博物館」 CREATE TABLE synonyms ( term text PRIMARY KEY, synonyms text[] ); CREATE INDEX synonyms_search ON synonyms USING pgroonga (term pgroonga_text_term_search_ops_v2); INSERT INTO synonyms (term, synonyms) VALUES ('美術館', ARRAY['美術館', 'ミュージアム']); INSERT INTO synonyms (term, synonyms) VALUES ('博物館', ARRAY['博物館', 'ミュージアム']); INSERT INTO synonyms (term, synonyms) VALUES ('ミュージアム', ARRAY['ミュージアム', '美術館', '博物館']); 27
28. ストアドファンクションの全文検索部分を変更 • 「pgroonga_query_expand」で同義語を展開 AND (CASE WHEN keywords = '' THEN true ELSE ft_text &@~ pgroonga_query_expand('synonyms', 'term', 'synonyms', keywords) END) 28
29. 再びストアドファンクションを直接呼び出してみる 29
30. ストップワード（検索除外キーワード・文字列）対応 • PGroonga では Groonga のストップワードに非対応 ◦ TokenFilterStopWord の指定はできるものの辞書が指定できない • ワークアラウンドで対応する ◦ 検索キーワードおよび検索対象から特定の文字列を除外する ▪ ストップワードが多いケースには向かない ◦ 今回はインデックス適用対象の生成列変更で対応 ▪ 式インデックスを使う方法もある 30
31. 全文検索用インデックスを変更する • 全文検索用の生成列を変更してインデックスを再作成 • 一旦インデックスを DROP して生成列を変更してから再作成 • 生成列で検索対象から「の」「・」を正規表現で除外 DROP INDEX pgroonga_content_index; ALTER TABLE spot_opendata DROP COLUMN ft_text, ADD COLUMN ft_text text GENERATED ALWAYS AS (REGEXP_REPLACE((title || ',' || describe || ',' || prefecture || municipality), '[の・]', '', 'g')) STORED; CREATE INDEX pgroonga_content_index ON spot_opendata USING pgroonga (ft_text) WITH (tokenizer='TokenMecab'); 31
32. ストアドファンクションの全文検索部分を再変更 • 検索キーワードの「の」「・」も除外 • 式インデックスを使う方法はこちらの ML アーカイブを参照 https://ja.osdn.net/projects/groonga/lists/archive/dev/2018-November/004708.html AND (CASE WHEN keywords = '' THEN true ELSE ft_text &@~ pgroonga_query_expand('synonyms', 'term', 'synonyms', REGEXP_REPLACE(keywords, '[の・]', '', 'g')) END) 32
33. ストアドファンクションを直接呼び出してみる 33
34. まとめ • Supabase では PGroonga の標準的な機能が使える • 検索クエリの実装はストアドファンクションで対応 • クライアント側で RPC を使ってストアドファンクション を呼び出して使う ◦ クライアント側では自由なクエリは実装できない ▪ 自由に実装できると SQL インジェクションなどの脆弱性に繋がる 34
35. Supabase を発表ネタとして取り上げている理由 • たまたま • 「Supabase が◦◦をサポート」というアナウンスを見て PostgreSQL の拡張機能と使い方を知ることが多い ◦ 結果として PostgreSQL の便利な使い方が覚えられる ▪ 今月は pgvector を OpenAI と組み合わせて使う例が Blog に示されている https://supabase.com/blog/openai-embeddings-postgres-vector 35