Djangoユーザが知っ得なPostgreSQL機能 - 設計の選択肢を増やす / Djang-use-PostgreSQL

下記の記事の解説のPostgreSQLにフォーカスしたslideです。
https://soudai.hatenablog.com/entry/2026/06/05/153530

Transcript

1. Djangoユーザが知っ得なPostgreSQL機能 - 設計の選択肢を増やす — Djangoの記事に出てくるPostgreSQLの機能を初心者向けに解説する
2. INTRO 注意書き この資料はsoudai1025が書いた原稿をChatGPTを使って推敲し、Claud Designｄ作成しています 指摘事項等があれば @soudai1025 まで Djangoで便利なPostgreSQLの活用方法 - そーだいなるらくがき帳 の内容をPostgreSQL初心者のために解説します Django側の実装等については上記のブログを参照してください ゴール：「これはアプリ側ではなく DB側で自然に表現できる問題 かもしれない」と判断できるようになる。
3. INTRO この資料でやること DjangoのAPI名ではなく、PostgreSQL側で何が起きているかを理解する JSONB・配列・制約・index・ロックを、実データと結果 で見る GIN / BRIN / HNSW などの「構造の違い」を図で掴む 使いどころだけでなく、使いすぎると危ない場面も押さえる ゴール：「これはアプリ側ではなく DB側で自然に表現できる問題 かもしれない」と判断できるようになる。
4. INTRO 今回追加した主な要素 追加要望 この資料での反映 JSON / 配列型 実クエリと検索結果を掲載 GIN index 転置indexの構造とB+treeとの違いを図解 部分index / 部分一意制約 対象行・対象外行を表で整理 BRIN index Block Range の要約構造を図解 SKIP LOCKED FOR UPDATE との違いを表とタイムラインで説明 PostGIS / pgvector 半径・矩形検索、近似検索、ユースケースを追加
5. INTRO 全体の流れ 01 実データで見る型 JSONB / 配列型 / 範囲型 02 indexの構造 B+tree / GIN / GiST / BRIN 03 制約と部分 index 部分一意 / NULLS NOT DISTINCT 04 同時実行と運用 FOR UPDATE / SKIP LOCKED 05 検索の応用 全文検索 / PostGIS / pgvector 06 まとめ 使い分けの軸
6. INTRO まず押さえる 3つの考え方 型 保存形式だけでなく 検索方法を決 める jsonb / array / range / vector 制約 アプリのvalidationとは別に 最後の 整合性を守る UNIQUE / CHECK / EXCLUDE index 検索条件に合わせて データ構造を 選ぶ B-tree / GIN / GiST / BRIN / HNSW PostgreSQLは単なるデータ置き場ではない。型・制約・ index・ロックを使うと、同時実行時のバグや不整合をDB側で減らせる。
7. 01 実データで見る型 JSONB / 配列型 / 範囲型を、クエリと結果から理解する Django × PostgreSQL ／ soudai1025
8. 01 実データで見る型 サンプルプロジェクトの前提 Django 5.2 / PostgreSQL 18 + pgvector の最小プ ロジェクト モデル定義・マイグレーション・サンプルデータが入っている この資料では、サンプルコードのデータを SQLの結果 として 見せる 起動 git clone https://github.com/soudai/explain-analyze-training.git cd explain-analyze-training/django docker compose up --build -d 代表的な確認 curl http://localhost:8000/sample/ { "jsonfield":{...}, "arrayfield":{...}, "rangefield":{...}, "pgvector":{...}, "constraints":{...} }
9. 01 実データで見る型 サンプルデータの全体像 テーブル 使う機能 代表的な値 sampleapp_webhookevent JSONB stripe / github の payload sampleapp_article 配列型 tags = {postgresql,django} sampleapp_campaign 範囲型 active_period = tstzrange sampleapp_draftarticle 部分一意制約 user ごとの draft は1件まで sampleapp_customer 部分index deleted_at IS NULL だけ対象 sampleapp_ragchunk pgvector embedding vector(3) このあと、まずは JSONBと配列型 を「実際に検索するとどう返るか」から見ていく。
10. 01 実データで見る型 JSONB：スキーマが少し揺れるデータを扱う Webhook payload / 外部APIレスポンス / 可変属性 / ユー ザー設定に向く 全部をカラム化すると、外部仕様変更のたびにDB変更が必 要になる jsonb なら保存したJSONの中身に対して検索できる payload jsonb { "customer": { "id": "cus_123" }, "livemode": true , "amount": 1200 } ただし、JOIN・集計・外部キー参照・業務上の必須項目は 普通の列や別テーブル に逃がす。
11. 01 実データで見る型 JSONB：実データ id provider event_type payload の要点 1 stripe invoice.paid customer.id = cus_123 / livemode = true / amount = 1200 2 stripe invoice.created customer.id = cus_999 / livemode = false / amount = 800 3 github push repository.full_name = example/project / branch = main この3行を題材に、JSONの中身をどう検索するかを クエリと結果 で見ていく。
12. 01 実データで見る型 JSONB：通常列 + JSON内の値を取り出す SQL SELECT event_type, payload #>> '{customer,id}' AS customer_id, payload ->> 'livemode' AS livemode FROM sampleapp_webhookevent WHERE provider = 'stripe' AND event_type = 'invoice.paid'; 検索結果 event_type | customer_id | livemode -------------+-------------+--------- invoice.paid | cus_123 | true (1 row) #>> はJSONのパスをたどって text として取り出す。Djangoの payload__customer__id の裏側で、JSON内の値をDBが検索できる。
13. 01 実データで見る型 JSONB：包含検索 @> SQL SELECT provider, event_type FROM sampleapp_webhookevent WHERE payload @> '{"livemode": true}'::jsonb; 検索結果 provider | event_type ---------+-------------- stripe | invoice.paid (1 row) payload @> '{"livemode": true}' 左のJSONが右のJSONを 含む 行を探す GIN index + jsonb_path_ops はこのような包含検索に向 く
14. 01 実データで見る型 JSONB：キー存在検索 ? SQL SELECT provider, event_type FROM sampleapp_webhookevent WHERE payload ? 'customer' ORDER BY id; 検索結果 provider | event_type ---------+----------------- stripe | invoice.paid stripe | invoice.created (2 rows) jsonb_path_ops は @> に寄せた operator class ? / ?| / ?& のようなキー存在検索も多いなら、default の jsonb_ops や式indexも検討する
15. 01 実データで見る型 JSONBを使いすぎると危ない場面 JSONBで持ち続けるとつらいもの 理由 外部キーで参照したい値 JSONの中身にはFK制約を張れない JOINや集計の主対象 SQLが複雑になり、index設計も難しくなる 業務上の必須項目 NOT NULL / CHECK / UNIQUE を素直に張りにくい 複数人が同時更新する中心データ JSON全体の更新衝突や差分管理がつらい 判断基準：スキーマが揺れる 「周辺データ」 には強い。業務の中心になるデータは 正規化を先に 考える。
16. 01 実データで見る型 配列型：小さなリストを 1行の属性として持つ Djangoでは ArrayField として扱える タグ・ラベル・簡単なフラグ群のような 小さく閉じた集合 に向 く 配列要素の包含 @> や重なり && をSQLで検索できる DDL CREATE TABLE article ( id bigserial PRIMARY KEY , title text NOT NULL , tags text[] NOT NULL ); 配列の中身には外部キーを張れない。 タグマスタ・権限・並び順・集計 が必要なら中間テーブルにする。
17. 01 実データで見る型 配列型：実データ id title tags 1 PostgreSQL and Django {postgresql, django} 2 Only Django {django} 3 Django full text search {django, search} 配列は「1つのカラムの中に複数要素がある」ため、 B-treeの単純な大小比較より GINが合いやすい。
18. 01 実データで見る型 配列型：postgresql を含む記事 SQL SELECT title, tags FROM sampleapp_article WHERE tags @> ARRAY['postgresql']::varchar(50)[] ORDER BY id; 検索結果 title | tags ----------------------+-------------------- PostgreSQL and Django | {postgresql,django} (1 row) @> は「左の配列が右の配列を含む」。タグ検索のように 特定要素を持つ行 を探すときに直感的。
19. 01 実データで見る型 配列型：django または postgresql と重なる記事 SQL SELECT title, tags FROM sampleapp_article WHERE tags && ARRAY['django','postgresql']::varchar(50)[] ORDER BY id; 検索結果 title | tags ------------------------+-------------------- PostgreSQL and Django | {postgresql,django} Only Django | {django} Django full text search | {django,search} (3 rows) && は「重なりがあるか」。どれか 1つでも含めばよい検索では、 ORを自分で展開しなくてよい。
20. 01 実データで見る型 範囲型：期間を 1つの値として扱う SQL SELECT name, active_period @> now() AS active_now FROM sampleapp_campaign ORDER BY name; 検索結果 name | active_now --------------------+----------- Expired Campaign | f Now Active Campaign | t @> は「範囲が値を含む」 && は「2つの範囲が重なる」 [) は開始を含み、終了を含まない 境界
21. 01 実データで見る型 範囲型：境界 [) の考え方 10:00 11:00 12:00 [10:00, 11:00) [11:00, 12:00) • 連続しているが、境界は重ならない 終了時刻を含まない [) にすると、10:00–11:00 と 11:00–12:00 は 重ならない。予約システムで扱いやすい。
22. 02 indexの構造 検索条件に合ったデータ構造を選ぶ：B+tree / GIN / BRIN Django × PostgreSQL ／ soudai1025
23. 02 indexの構造 indexは「検索条件」とセットで考える 検索条件 向きやすいindex 理由 email = ? B-tree ソート可能な単一値の等価・範囲検索 tags @> ARRAY[...] GIN 1カラム内の複数要素を引きたい payload @> {...} GIN JSONの構成要素から行を引きたい active_period && range GiST 範囲の重なりなどを扱う created_at の巨大時系列 BRIN 物理順と値が相関している embedding ORDER BY distance HNSW / IVFFlat 近傍候補を高速に探す
24. 02 indexの構造 B+tree：ソートされたキーから行を探す root [ m ] internal [ a … l ] internal [ m … z ] alice@… → TID(1) bob@… → TID(5) miki@… → TID(8) sato@… → TID(13) yuki@… → TID(21) 得意：= / < / BETWEEN / ORDER BY。苦手：JSONや配列の「中に含まれる要素」から探すこと。
25. 02 indexの構造 GIN：転置indexで「要素 → 行」を引く テーブル行 id tags 1 {postgresql, django} 2 {django} 3 {django, search} GIN index entries entry key posting list postgresql {TID 1} django {TID 1, TID 2, TID 3} search {TID 3} GINは Generalized Inverted Index 。配列要素・JSONBのキー/値・全文検索のlexemeのように、1つの値の中に複数の構成要素があるデータに向く。
26. 02 indexの構造 GINの内部イメージ： entry tree + posting list entry tree amount:1200 customer.id:cus_123 django livemode:true postgresql → posting list / tree entry TIDs livemode:true 1 django 1, 2, 3 customer.id 1 → heap rows row 1 row 2 row 3 ポイント：B+treeのように「行ごとに1キー」ではない。1行から 複数entry を作り、検索時はposting listの集合演算で候補行を絞る。
27. 02 indexの構造 GINとB+treeの違い 観点 B+tree GIN indexに入る単位 列の値そのもの 値の中から抽出した構成要素 典型クエリ email = ? / created_at BETWEEN tags @> / payload @> / 全文検索 データ構造 ソートされたキー → 行 要素 → 行のposting list 範囲検索 得意 基本的には用途外 更新コスト 比較的素直 1行更新で複数entry更新になりやすい 注意点 JSON/配列の中身検索には合わない operator classと演算子の相性が重要
28. 02 indexの構造 JSONBのGIN：operator classを意識する DDL CREATE INDEX webhook_payload_path_gin ON sampleapp_webhookevent USING gin (payload jsonb_path_ops); 対象クエリ SELECT id FROM sampleapp_webhookevent WHERE payload @> '{"livemode": true}'::jsonb; operator class 向きやすい検索 注意 jsonb_ops キー存在・包含など幅広い indexが大きめになりがち jsonb_path_ops @> 包含検索 ? 系のキー存在検索には向かない 式index payload #>> ... など特定式 式とWHERE句を揃える
29. 02 indexの構造 小さいサンプルでは Seq Scanでも正常 SQL EXPLAIN SELECT id FROM sampleapp_webhookevent WHERE payload @> '{"livemode": true}'::jsonb; 実行計画例 Seq Scan on sampleapp_webhookevent Filter: (payload @> '{...}'::jsonb) サンプルは数件なので、indexを読むより 全表scan の方が 安いことがある 効果を見るなら実データに近い件数で ANALYZE 後に確認 する EXPLAIN (ANALYZE, BUFFERS) で時間とI/Oを見る
30. 02 indexの構造 GiST：範囲や空間など、単純な大小でない検索 GiSTは「1種類の固定index」ではなく、さまざまな 検索戦略 の枠組み 範囲型の @> / && 、PostGISの空間検索、排他制約でよく 出る 厳密なソート順より、重なり・包含・近傍 の判定に向く DDL CREATE INDEX campaign_active_period_gist ON sampleapp_campaign USING gist (active_period); 初心者向けの覚え方： 範囲型の重なり検索や排他制約ではGiSTを検討する。
31. 02 indexの構造 BRIN：巨大な時系列テーブルのための小さい index BRIN = Block Range INdex 行ごとではなく、連続した物理ブロック範囲ごとの 要約 を持 つ created_at がINSERT順に増えるログで効きやすい 値がランダムに散らばる列では効果が出にくい DDL CREATE INDEX event_log_occurred_at_brin ON event_log USING brin (occurred_at); BRINは「全行へのポインタ」ではなく、 このページ範囲には何日から何日までがありそう 、という要約。
32. 02 indexの構造 BRINの構造：block rangeごとの要約 heap pages（物理順） 6/01 6/02 6/03 6/04 6/05 7/01 7/02 7/03 7/04 7/05 block range #1 min=2026-06-01 / max=2026-06-05 block range #2 min=2026-07-01 / max=2026-07-05 WHERE occurred_at >= '2026-06-02' AND < '2026-06-04' → block range #1だけ候補。候補内の行は再チェックする。
33. 02 indexの構造 BRIN・GIN・B+treeの違い index 何を持つか 向いている検索 弱いところ B+tree ソートされたキー → TID 等価・範囲・ORDER BY 配列/JSONの中身検索 GIN 構成要素 → posting list 配列/JSON/全文検索 更新時にentryが増えやすい BRIN block rangeごとの要約 巨大な時系列・物理順相関 ランダム分布・少量データ BRINは 候補ブロックを減らすindex。「何行目か」ではなく「どのページ範囲を読むべきか」を絞る。
34. 03 制約と部分 index アプリの事前チェックだけでなく、DBに最後の整合性を守らせる Django × PostgreSQL ／ soudai1025
35. 03 制約と部分 index 排他制約：予約の重複を DBが拒否する 制約の意味 EXCLUDE USING gist ( room_id WITH =, timespan WITH && ) WHERE (cancelled = false) 同じ部屋 かつ 予約期間が重なる 未キャンセル予約 を禁止する アプリ側で「空いているか」を見てから INSERTしても、同時実行では 2つのリクエストが同じ判断をすることがある。 最後はDB制約で止める。
36. 03 制約と部分 index 排他制約：対象行と対象外行 room timespan cancelled 制約対象? 理由 A-101 [6/05 00:00, 6/06 00:00) false YES 未キャンセル予約 A-101 [6/05 00:00, 6/06 00:00) true NO WHERE cancelled=false から外れる A-101 [6/05 10:00, 6/05 11:00) false REJECT 1行目と room= かつ timespan && キャンセル済みは制約対象から外れるが、 未キャンセルの重複予約はDBが拒否する。NOTICE: reservation_overlap_blocked=t
37. 03 制約と部分 index UNIQUE / CHECK：まず覚えるべき制約 制約 守ること 例 NOT NULL 値が必ずある email は必須 UNIQUE 重複しない tenant_id + external_id CHECK 条件を満たす status IN ('draft','published',...) FOREIGN KEY 参照先が存在する room_id は room を参照 Djangoのvalidationは 「アプリの入口」。DB制約は 「最後の砦」。管理画面・バッチ・ SQL直実行のミスも守る。
38. 03 制約と部分 index 部分一意制約： draftだけ一意にする DDL CREATE UNIQUE INDEX unique_draft_per_user ON sampleapp_draftarticle (user_id) WHERE status = 'draft'; user_id status 一意対象? 1 draft YES 1 published NO 1 draft（2件目） REJECT 「常に一意」ではなく 「この条件のときだけ一意」 という業務ルールを DBに置ける。
39. 03 制約と部分 index 部分一意制約：検索結果と INSERT結果 検索結果 user_id | status | title --------+-----------+------------ 1 | draft | first draft 1 | published | published article 重複 INSERT INSERT INTO sampleapp_draftarticle (user_id, status, title) VALUES (1, 'draft', 'duplicate draft'); DBの反応 ERROR: duplicate key value violates unique constraint "unique_draft_per_user"
40. 03 制約と部分 index UNIQUE NULLS NOT DISTINCT：NULLも同じ値として扱う 通常のUNIQUEでは、NULLは複数入る SQLのNULLは「不明」なので、NULL同士は等しいと扱われ ないため PostgreSQL 15以降は UNIQUE NULLS NOT DISTINCT が使える DDLのイメージ UNIQUE NULLS NOT DISTINCT (tenant_id, external_id) 業務上「未連携（external_id=NULL）もテナントごとに1件まで」としたいときに効く。
41. 03 制約と部分 index UNIQUE NULLS NOT DISTINCT：対象行の表 tenant_id external_id 登録済み? 新規INSERTの結果 1 NULL YES 2件目の (1, NULL) は REJECT 1 acct_123 YES 2件目の (1, acct_123) は REJECT 2 NULL YES 2件目の (2, NULL) は REJECT 3 NULL NO (3, NULL) は ALLOW 1 acct_456 NO (1, acct_456) は ALLOW
42. 03 制約と部分 index UNIQUE NULLS NOT DISTINCT：検索結果と INSERT結果 検索結果 tenant_id | external_id ----------+------------ 1 | 1 | acct_123 2 | 重複 NULL INSERT INSERT INTO sampleapp_externalaccount (tenant_id, external_id) VALUES (1, NULL); DBの反応 NOTICE: duplicate_null_blocked=t
43. 03 制約と部分 index 部分index：条件に合う行だけ indexに入れる DDL CREATE INDEX active_customer_email_idx ON sampleapp_customer (email) WHERE deleted_at IS NULL; 論理削除テーブルで、普段見るのは deleted_at IS NULL だけ 削除済み行をindexに入れないので、 サイズと更新コスト を抑えやすい 検索条件が部分 indexのWHERE条件と合わないと使われにくい 「ほとんどの検索で同じ条件が付く」 なら部分indexを検討する。
44. 03 制約と部分 index 部分index：対象になる行 / ならない行 email deleted_at index対象? alice@example.com NULL YES old@example.com 2026-06-xx NO この条件ならindexと合う SELECT email, name FROM sampleapp_customer WHERE email = 'alice@example.com' AND deleted_at IS NULL; 検索条件が部分indexのWHERE条件と 論理的に一致 するかを確認する。
45. 03 制約と部分 index 部分index：条件がズレると使われにくい 検索条件の違い -- 部分indexの条件と合う WHERE email = 'alice@...' AND deleted_at IS NULL -- 条件が足りない WHERE email = 'alice@...' -- 逆条件 WHERE email = 'old@...' AND deleted_at IS NOT NULL 部分indexは 「小さいindex」 ではなく 「前提条件つきindex」 DjangoのManagerで active only を隠蔽すると漏れにくい indexのWHERE句とSQLのWHERE句が一致するか確認する
46. 03 制約と部分 index 関数index / カバリング indexも同じ発想 関数index CREATE INDEX customer_lower_email_idx ON sampleapp_customer ((lower(email))); SELECT email, name FROM sampleapp_customer WHERE lower(email) = 'alice@...'; カバリングindex CREATE INDEX customer_email_inc_name_idx ON sampleapp_customer (email) INCLUDE (name); SELECT email, name FROM sampleapp_customer WHERE email = 'alice@...'; indexは 「よく実行するSQLの形」 に合わせて作る。式・ WHERE条件・返す列まで含めて考える。
47. 04 同時実行と運用 FOR UPDATE / SKIP LOCKED / パーティショニング / CONCURRENTLY Django × PostgreSQL ／ soudai1025
48. 04 同時実行と運用 FOR UPDATE：選んだ行を更新対象としてロックする SQL BEGIN; SELECT id, status, payload FROM sampleapp_importjob WHERE status = 'pending' ORDER BY id ASC LIMIT 1 FOR UPDATE; -- processingへ更新 COMMIT; id status lock状態 1 pending Worker A がロック 2 pending 空き 3 processing 対象外 同じジョブを2つのworkerが同時に処理することを防げる。 ただし、ロック済み行に当たると待つ。
49. 04 同時実行と運用 FOR UPDATE と SKIP LOCKED の違い 状況 FOR UPDATE FOR UPDATE SKIP LOCKED id=1 が他Txでロック済み id=1の解放を待つ id=1を飛ばして次へ進む id=2 がpendingで空き id=1待ちなので取れない id=2をロックして取得できる FIFO性 先頭待ちなので崩れにくい ロック行を飛ばすので厳密 FIFOではない 向く用途 同じ行の同時更新防止 複数workerのジョブ取り出し 向かない用途 高スループットなキュー 一覧検索・厳密順序キュー
50. 04 同時実行と運用 SKIP LOCKED：待たずに次の行を取りに行く Worker A BEGIN → id=1 を FOR UPDATE → 処理中 Worker B（FOR UPDATEだけ） id=1 に当たる → 待つ Worker B（SKIP LOCKED） id=1 はロック済み → 飛ばす → id=2 を取得 SKIP LOCKEDは「今ロックされている行は見なかったことにする 」に近い。通常の業務検索で使うと、見えるべき行を落とす。
51. 04 同時実行と運用 SKIP LOCKED：実クエリと結果 SQL BEGIN; SELECT id, status, payload FROM sampleapp_importjob WHERE status = 'pending' ORDER BY id ASC LIMIT 1 FOR UPDATE SKIP LOCKED; ROLLBACK; 検索結果 id | status | payload ---+---------+---------------------- 1 | pending | {"name": "import-1"} (1 row) 確認だけなら ROLLBACK。実際のworkerでは同じトランザクション内で processing に更新してCOMMITする。
52. 04 同時実行と運用 SKIP LOCKEDの注意点 注意点 何が起きるか 対策の方向 厳密FIFOではない 先頭がロック中なら後ろが先に処理される 順序保証が必要な業務には使わない starvation 長くロックされた行が後回しになり続ける timeout / retry / dead job 監視 通常検索に不向き ロック済み行が結果から消える 一覧や帳票では使わない Txが長いと悪化 ロック保持時間が長い ジョブ取得と状態更新を短くする
53. 04 同時実行と運用 パーティショニング：巨大テーブルを期間で分ける 親テーブル CREATE TABLE event_log ( id uuid NOT NULL , occurred_at timestamptz NOT NULL , tenant_id bigint NOT NULL , payload jsonb NOT NULL , PRIMARY KEY (id, occurred_at) ) PARTITION BY RANGE (occurred_at); 月次パーティション CREATE TABLE event_log_2026_06 PARTITION OF event_log FOR VALUES FROM ('2026-06-01' ) TO ('2026-07-01'); 時系列ログ・監査ログのように、 増え続け、古いデータを期間単位で消したい テーブルに向く。
54. 04 同時実行と運用 パーティションプルーニング：関係ない月を読まない event_log（親テーブル） 2026_05 2026_06 2026_07 実行計画の見え方 Seq Scan on event_log_2026_06 Filter: (occurred_at >= ... AND occurred_at < ... AND tenant_id = 1) 検索条件に partition key を入れる。パーティションキーで絞れない SQLは、期待通りに効かないことがある。
55. 04 同時実行と運用 CONCURRENTLY：本番の大きなテーブルで indexを足す SQL CREATE INDEX CONCURRENTLY customer_email_idx ON customer (email); 通常の CREATE INDEX は書き込みを長くブロックすること がある CONCURRENTLY は時間はかかるが、 書き込みへの影響を 抑えながら 作る トランザクションブロック内では実行できない（Django migrationは atomic = False ） index追加は 本番運用作業。ロック・実行時間・失敗時の再実行・ロールバック方法を事前に確認する。
56. 05 検索の応用 全文検索・PostGIS・pgvector・近似検索を押さえる Django × PostgreSQL ／ soudai1025
57. 05 検索の応用 全文検索 / pg_trgm / pg_bigm の位置づけ やりたいこと まず見る機能 注意 英語の文章検索 PostgreSQL全文検索 tsvector / tsquery / 辞書 スペルミス・ゆらぎ pg_trgm 短い検索語・日本語には弱い場面あり 日本語LIKE部分一致 pg_bigm PostgreSQL拡張の導入可否を確認 プロダクト中心の高度検索 検索エンジンも検討 同期・再index・運用も増える 管理画面や小中規模の検索なら、外部検索エンジンの前に PostgreSQLでどこまでできるか 確認する価値がある。
58. 05 検索の応用 全文検索： SearchVector / SearchRank をQuerySetで書く Django code vector = SearchVector("title", weight="A" ) \ + SearchVector("body", weight="B" ) query = SearchQuery("postgresql django" ) qs = (Article.objects .annotate(rank= SearchRank (vector, query)) .filter(rank__gte= 0.1 ) .order_by( "-rank")) 生成SQL（整形） SELECT title, ts_rank( setweight(to_tsvector(title), 'A' ) || setweight(to_tsvector(body), 'B' ), plainto_tsquery( 'postgresql django' ) ) AS rank FROM sampleapp_article WHERE ts_rank(...) >= 0.1 ORDER BY rank DESC; 文章を tsvector に、検索語を tsquery に変換し、ts_rank で関連度順に並べる。
59. 05 検索の応用 全文検索：一致判定だけでなく rankで並べる title / body → to_tsvector → plainto_tsquery → ts_rank → ORDER BY rank DESC 順位 title rank 理由 1 PostgreSQL and Django 高 titleに両語がある 2 Django full text search 中 bodyにPostgreSQLがある 3 Only Django 低 Djangoだけが一致
60. 05 検索の応用 pg_trgm："postgress" で PostgreSQL を拾いたい 生成SQL CREATE EXTENSION IF NOT EXISTS pg_trgm; SELECT title, SIMILARITY(title, 'postgress') AS similarity FROM sampleapp_article WHERE SIMILARITY(title, 'postgress') > 0.2 ORDER BY similarity DESC; title 判定 意味 PostgreSQL and Django HIT 表記ゆれに近い Only Django OUT 類似度が低い Django full text search OUT titleが近くない pg_trgm は 3文字単位の類似度。短い語や日本語LIKEでは pg_bigm も検討する。
61. 05 検索の応用 PostGIS：位置情報を DBで扱う 緯度経度をFloatで2列持つだけでは、距離・範囲・ポリゴン 検索がつらい PostGISは地理空間データの 型・関数・index を追加する GeoDjangoでは PointField(srid=4326) などで扱う DDL CREATE EXTENSION IF NOT EXISTS postgis; CREATE TABLE store ( id bigserial PRIMARY KEY , name text NOT NULL , location geometry(Point, 4326) NOT NULL ); 位置情報が ビジネスの中心 なら最初からPostGISを検討。単なる補助情報なら導入コストと相談。
62. 05 検索の応用 PostGIS：半径3km以内の店舗を探す 半径検索 SELECT name FROM store WHERE ST_DWithin ( location::geography, ST_SetSRID(ST_MakePoint( 139.767, 35.681), 4326 )::geography, 3000 ); ST_DWithin は「指定距離以内か」を判定する geographyでは距離単位が メートル になる 空間indexがあると、候補を絞ってから距離判定できる 距離順に並べたい場合は ST_Distance やGeoDjangoの Distance でannotateしてORDER BYする。
63. 05 検索の応用 PostGIS：半径検索の図 • 現在地 A HIT B HIT C OUT D OUT ST_DWithin：円の内側 を探す。空間indexで候補を絞ってから正確な距離を判定する。
64. 05 検索の応用 PostGIS：矩形範囲の店舗を探す 矩形検索 WITH bbox AS ( SELECT ST_MakeEnvelope( 139.70, 35.65, 139.80, 35.72, 4326 ) AS geom ) SELECT store.name FROM store, bbox WHERE store.location && bbox.geom AND ST_Within(store.location, bbox.geom); ST_MakeEnvelope は矩形Polygonを作る && は bounding box の交差判定で indexを使いやすい ST_Within で本当に矩形内かを判定する 地図の表示範囲内検索、管理画面のエリア絞り込み、タイル内のデータ取得で使いやすい。
65. 05 検索の応用 PostGIS：矩形検索の図 A HIT B HIT C OUT D OUT E OUT ST_MakeEnvelope：四角の内側 を探す。bounding box indexで候補を絞り、ST_Within で正確に判定する。
66. 05 検索の応用 pgvector：ベクトルを保存して近い順に探す テキストや画像をembedding modelで 数値ベクトル に変換 する 本番はmodelの次元数に合わせる（例 vector(1536) ） サンプルの vector(3) は説明用。3次元のまま本番実装 しない DDL CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE rag_chunk ( id bigserial PRIMARY KEY , title text NOT NULL , body text NOT NULL , embedding vector( 1536) NOT NULL ); -- sample only: embedding vector(3) 次元数は保存済みembeddingと一致必須。model変更時は 再embedding・再index を計画する。
67. 05 検索の応用 pgvector：実クエリと検索結果 SQL SELECT title, round((embedding <=> '[0.9,0.1,0.2]')::numeric, 6 ) AS cosine_distance FROM sampleapp_ragchunk ORDER BY embedding <=> '[0.9,0.1,0.2]' LIMIT 3; 検索結果 title | cosine_distance -----------------------+---------------- JSONField and jsonb | 0.000000 pgvector for RAG | 0.004004 PostgreSQL range types | 0.618754 <=> は cosine距離。距離が小さいほど近い。実運用は利用モデルの次元に合わせた query embeddingを渡す。
68. 05 検索の応用 ベクトル検索：近い点を探すイメージ Q query embedding JSONB RAG Range Django 円の中の点ほど近い候補。 semantic similarity＝意味の近さを距離で表す。ただし高次元では図のようには見えない。
69. 05 検索の応用 近似検索とは何か 方式 探し方 結果 コスト 厳密検索 全候補を距離計算して本当の Top-Kを返す 取りこぼしなし データが増えると重い 近似検索 近そうな候補だけを高速に探索する 本当のTop-Kを取りこぼす可能性 速い / indexが効く 評価指標の例：recall@k。速さだけでなく、取りこぼしの許容度をプロダクト要件で決める 。
70. 05 検索の応用 HNSW：多層グラフで近傍候補をたどる Layer 2：粗い近道 Layer 1 Layer 0：全体に近い層 上の層で大まかに近い方向へ進み、下の層で細かく候補を探す。 ef_search を上げるとrecallは上がるが遅くなる。
71. 05 検索の応用 IVFFlat：クラスタのリストを一部だけ探す list 1 list 2（probe） list 3 Q データをクラスタに分け、 queryに近いlistだけをprobe する。probe数を増やすとrecallは上がるが遅くなる。
72. 05 検索の応用 ベクトル検索の実際のユースケース ユースケース 何をembeddingするか 返したいもの RAG / 社内文書検索 文書チャンク・FAQ・議事録 質問に近い根拠文書 問い合わせ分類 問い合わせ本文 似た過去チケット・カテゴリ レコメンド 商品説明・閲覧履歴・ユーザー特徴 似た商品・おすすめ候補 重複検出 記事・店舗名・商品名 似ているレコード 画像検索 画像embedding 似ている画像・素材 不正/異常の候補抽出 操作ログ・イベント特徴量 通常パターンから遠いもの
73. 05 検索の応用 ベクトル検索だけで最終結果を決めない RAGでありがちなSQL SELECT title, body FROM rag_chunk WHERE tenant_id = 42 AND published = true AND category = 'manual' ORDER BY embedding <=> :query_embedding LIMIT 10; テナント・権限・公開状態・カテゴリなどの 通常条件で絞る 必要ならTop-K候補を取り出して再ランキングする 近似indexではfilter適用後に件数が足りなくなることがある pgvectorの強みは、リレーショナル条件とベクトル距離を同じ DBで扱える こと。
74. 06 その他の便利機能とまとめ MERGE / 拡張機能 / EXPLAIN / 使い分け Django × PostgreSQL ／ soudai1025
75. 06 まとめ MERGE：条件に応じて INSERT / UPDATE / DELETE SQL MERGE INTO sampleapp_stock AS target USING (VALUES ('A-001', 12 , now()), ( 'B-002', 0 , now()), ( 'C-003', 5 , now()) ) AS source(sku, quantity, seen_at) ON target.sku = source.sku WHEN MATCHED AND source.quantity = 0 THEN DELETE WHEN MATCHED THEN UPDATE SET quantity = source.quantity WHEN NOT MATCHED AND source.quantity > 0 THEN INSERT (sku, quantity, seen_at) VALUES (source.sku, source.quantity, source.seen_at); 結果 MERGE 3 sku | quantity ------+--------- A-001 | 12 C-003 | 5 単純なUPSERTなら ON CONFLICT で十分。条件でDELETEもした い、分岐が複数あるなら MERGE が読みやすい。
76. 06 まとめ 拡張機能： PostgreSQLに型・関数・ indexを足す 拡張 役割 btree_gist GiSTでB-tree相当の比較を扱う。排他制約でよく使う pg_trgm トライグラム類似検索 pg_bigm 2-gramによる日本語LIKE検索高速化 bloom 複数カラム等価検索をコンパクトに扱う postgis 地理空間データ vector ベクトル型と近傍検索 本番では マネージドDBで利用可能な拡張・バージョン・権限 を早めに確認する。
77. 06 まとめ EXPLAIN：indexは「効いているか」を確認する SQL EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM sampleapp_webhookevent WHERE payload @> '{"livemode": true}'::jsonb; 見るもの 意味 Seq Scan テーブルを順番に読む Index / Bitmap Scan indexを使って候補を探す actual time 実際の実行時間 rows 見積もり行数 / 実行時の行数 Buffers 読んだページ量 Seq Scanは常に悪ではない。小さいテーブルでは全表 scanのほうが安いことがある。
78. 06 まとめ 初心者向けの使い分け やりたいこと まず検討する機能 外部API payloadや可変属性を保存 JSONB JSONB / 配列の包含検索を速く GIN index 期間の包含・重なりを扱いたい 範囲型 + GiST 予約重複をDBで防ぎたい 排他制約 条件付きで一意にしたい 部分一意制約 やりたいこと まず検討する機能 有効行だけ検索したい 部分index 巨大な時系列を小さく絞りたい BRIN 複数workerでジョブを取りたい FOR UPDATE SKIP LOCKED 位置情報を扱いたい PostGIS embeddingの近傍検索をしたい pgvector + HNSW/IVFFlat
79. 06 まとめ 今日持ち帰ること 1 データの性質に合った型を使う JSONB / 配列 / 範囲 / geography / vector 2 整合性は DBにも守らせる UNIQUE / CHECK / EXCLUDE / NULLS NOT DISTINCT 3 検索条件に合った indexを選ぶ B+tree / GIN / GiST / BRIN / HNSW ORMとSQLは対立しない。Djangoで表現できるところはDjangoに、DBが得意なことはPostgreSQLに任せる。
80. 06 まとめ 参考 元記事：soudai.hatenablog.com/entry/2026/06/05/153530 サンプルリポジトリ：github.com/soudai/explain-analyze-training/tree/main/django PostgreSQL documentation：Index Types / JSON / Range / Constraints / Locks / Partitioning / MERGE PostGIS documentation：ST_DWithin / ST_MakeEnvelope / ST_Within / && pgvector：github.com/pgvector/pgvector ／ Django：django.contrib.postgres / gis ありがとうございました