multirange 型（多重範囲型）の活用

Transcript

1. multirange 型（多重範囲型）の活用 今井 陽一 / 2025-11-21
2. 自己紹介 今井 陽一 Web アプリケーションエンジニア SI でサーバ・インフラを担当後、スタートアップで事業立ち上げ時 から成長期まで開発者として関わる PostgreSQL との関わりは 7.4 〜 2
3. アジェンダ 1. range 型 / multirange 型とは 2. 何が便利なのか？ 3. 予約管理での使用例 空き枠の管理 カレンダーの生成 予約の重複防止 4. Tips 3
4. 範囲型、ご存知ですか？ 知っている人 〜range 型 〜multirange 型 使っている人 4
5. range 型とは 連続した区間を1 つの値で表す型 例 10 〜20 (intrange) 11/21 10:00 〜 11/22 20:00 (tsrange) 開区間/ 閉区間を保持（例： [a,b) ） 5
6. multirange 型とは range の集合 例： [09:00,12:00) と [13:00,18:00) PostgreSQL v14 で追加 6
7. range 型の何が便利なのか？ 開始・終了がセットである →範囲に対する演算をすることができる 主な演算の例 && 10:00 〜11:00 に10:30 〜11:15 は重なっていますか？ <@ 10:00 〜11:00 に10:30 〜10:45 は含まれていますか？ 7
8. multirange の何が便利なのか？ 複数のrange をまとめた値で表現することでさらなる演算が可能 多重範囲(multirange) に集約すると歯抜けとなった範囲もひとつ の値として扱える 例：09:00 〜10:00, 12:00 〜13:00 差・積で空き検索や絞り込みがシンプルなSQL に 差 - ：重なる範囲を取り除く 09:00 〜17:00 から 10:00-11:00 を引いたら？ 積 * ：共通部分で絞り込む 09:00 〜17:00 と 13:00 〜20:00 、 15:00 〜21:00 の共通部分 は？ 8
9. 例の紹介：予約アプリ 設備やスタッフの予約 タイムゾーンありで扱うため tstzrange / tstzmultirange を使用 9
10. スキーマ例（FK 等省略） CREATE EXTENSION IF NOT EXISTS btree_gist; -- 枠 CREATE TABLE reservation_slot ( id bigserial PRIMARY KEY, available tstzrange NOT NULL ); -- 予約 CREATE TABLE reservation ( id bigserial PRIMARY KEY, reservation_slot_id bigint NOT NULL, period tstzrange NOT NULL, EXCLUDE USING gist ( reservation_slot_id WITH =, period WITH &&) DEFERRABLE INITIALLY DEFERRED ); 10
11. 空き枠と予約の挿入 つながった区間が重複しないよう終端は「) 」で開いておく INSERT INTO reservation_slot (available) VALUES ('[2025-10-20 09:00:00+09,2025-10-20 12:00:00+09)'::tstzrange); INSERT INTO reservation (reservation_slot_id, period) VALUES (1, '[2025-10-20 09:00:00+09,2025-10-20 10:00:00+09)'::tstzrange); 11
12. 特定の期間の予約の一覧を知りたい && を使うと範囲に重なりがあるレコードを抽出できる 例：10/20 の予約の一覧を得たい -- 指定時間帯に重なる予約を検索 SELECT * FROM reservation r WHERE r.period && tstzrange(:start, :end, '[)'); tstzrange() は範囲をパラメータで渡すときに便利 時刻の組に開閉の指定 [) を与えられる 12
13. 予約をかぶらせたくない 排他制約を使うことで時間帯の重複を防ぐことができる 例: EXCLUDE USING gist (slot_id WITH =, period WITH &&) INSERT INTO reservation(reservation_slot_id, period) VALUES (1, tstzrange('2025-09-22 10:00+09', '2025-09-22 10:30+09', '[)')); -- EXCLUDE USING gist 〜により挿入が防がれる INSERT INTO reservation(reservation_slot_id, period) VALUES (1, tstzrange('2025-09-22 10:15+09', '2025-09-22 10:45+09', '[)')); 13
14. 予約の空き時間を計算したい 範囲に重なる「枠」と「予約」を絞り込む（ && を使用） 枠ごとに予約済み範囲の差分を取っておく（ - を使用） 例：09:00 〜17:00 - ｛10:00 〜11:00 、15:00 〜16:00 ｝- ① カレンダーのセルの部分を生成する（ generate_series を使用） 例：09:00 〜10:00, 10:00 〜11:00, 11:00 〜12:00, ... カレンダーのセル部分がそれぞれ、①の空き時間帯に含まれている かどうか確認する（ <@ を使用） 例：09:00 〜10:00 OK, 10:00 〜11:00 NG 、11:00 〜12:00 OK, ... 14
15. 予約の空き時間を計算したい（図） 15
16. カレンダーの生成SQL WITH vacancy AS ( SELECT rs.id, rs.available, (tstzmultirange(rs.available) - coalesce(range_agg(r.period), tstzmultirange())) AS vacant_range FROM reservation_slot rs LEFT OUTER JOIN reservation r ON rs.id = r.reservation_slot_id AND r.period && tstzrange(:start_time, :end_time, '[)') WHERE rs.available && tstzrange(:start_time, :end_time, '[)') GROUP BY rs.id, rs.available ), calendar_item AS ( SELECT tstzrange(t, t + '30 minutes'::interval) AS period FROM generate_series(:start_time, :end_time, '30 minutes') t ) SELECT lower(c.period) AS start, EXISTS( SELECT 1 FROM vacancy v WHERE c.period <@ v.vacant_range ) AS vacant FROM calendar_item c 16
17. 契約条件など別の条件で絞り込みたい ユーザーのプランによる制限など、別の制約も積（ * ）によって重ね られる。 -- 指定ウィンドウとの共通部分だけを提示（固定値の例） WITH ( 指定略) SELECT lower(c.period) AS start, EXISTS( SELECT 1 FROM vacancy v WHERE c.period <@ (v.vacant_range * tstzmultirange( '[2025-10-10 00:00:00+0900, 2025-10-11 00:00:00+0900)'::tstzrange)) ) AS vacant FROM calendar_item c 17
18. Tips 18
19. 予約の重複防止のやりかたいくつか 排他制約 トリガー 19
20. 予約の重複防止（パターンA ） ：排他制約 シンプル 複数行の一括予約でも、同テーブル内の重複はDB が検出 注意：他テーブル横断の整合や業務ルールが絡む場合は難しい 中間テーブルに値を外部キーで引っ張ることで対処できる場合も EXCLUDE USING gist ( reservation_slot_id WITH =, period WITH && ) DEFERRABLE INITIALLY DEFERRED 20
21. 予約の重複防止（パターンB ） ：CONSTRAINT TRIGGER COMMIT 時 に 業務ルールをまとめて検証 CREATE CONSTRAINT TRIGGER reservation_check AFTER INSERT OR UPDATE ON reservation DEFERRABLE INITIALLY DEFERRED FOR EACH STATEMENT EXECUTE FUNCTION verify_reservations(); CREATE OR REPLACE FUNCTION verify_reservations() -- 実体略 21
22. 行を減らすための && 厳密な計算の前に、 && で先にざっくり削れたりする range_agg 対象の削減 例：10/1 〜10/31 を知りたいのに11/1 の計算は必要ない -- 日付や対象範囲で先に重なる候補を絞る SELECT reservation_slot_id, COALESCE(range_agg(period), tstzmultirange()) AS used_ranges FROM reservation WHERE period && tstzrange(:day_start, :day_end, '[)') GROUP BY reservation_slot_id 22
23. coalesce を活用する 空になる可能性がある → coalesce で空のmultirange を作るのが便利 -- サブクエリがNULL を返す可能性がある場合 SELECT coalesce( 〜, tstzmultirange()) FROM t; 後続の - や * が書きやすくなる 23
24. まとめ 時間帯などの範囲はrange 型で記録できる range 型は差や積などの範囲に対する演算が行える range_agg() 等でmultirange 型にすると歯抜けの範囲も扱える 予約管理などの範囲を扱うアプリケーションで便利 24