そーだいなるらくがき帳

そーだいが自由気侭に更新します。

PostgreSQLで時間枠を適切に扱う設計

はじめに

　Googleカレンダーのような時間枠を扱うシステムを設計する際、開始・終了時刻を管理するロジックは容易ではない。 しかし、PostgreSQLには 範囲型 があり、この機能を活用することで、開始時刻（begin_at）と終了時刻（end_at）を1つのカラムで扱えるようになる。 そこで本稿では、範囲型を用いた設計と、その利点を紹介する。

時間枠を扱う難しさ

　まず前提として時間枠の扱いがなぜ難しいかを紹介する。

　ソフトウェアデザインでやっている連載、実戦データベースリファクタリングの 【12】厄介な時間枠に向き合う でも紹介したが、時間の範囲を比較するときが難しい。 範囲の重なりには以下の種類がある。

  • 包含：範囲Aが範囲Bを完全に含む
  • 重複：範囲Aと範囲Bに共通点がある
  • 隣接：範囲Aと範囲Bが隣り合う

　時間枠の扱いはSQLに限らず、プログラミングの題材として難易度が高い。 特に重複と含有が複数のパターンの場合、ロジックが複雑になり、バグの温床になりやすい。 これは『リーダブルコード』でも、「8.5 例：複雑なロジックと格闘する」でこの問題が取り上げられている。

PostgreSQLの範囲型を用いた設計

　この問題を解決するためにPostgreSQLの範囲型の活用方法を紹介する。 範囲型を利用することで、時間枠の重なりや包含、隣接を直感的な演算子で表現でき、コードの複雑性を大幅に削減できる。

高速で柔軟な検索

　データの保存だけではなく、データの検索でも強力だ。 PostgreSQLの公式ドキュメントにあるとおり、以下のようなことができる。

www.postgresql.jp

  • 範囲の包含
  • 範囲の重複
  • 範囲の隣接
  • 対象範囲の開始前か終了後か

  • 対象範囲と重なりの前後

    例えば予約検索で特定の時間帯（2024/12/10 12:00 - 13:00）に利用可能な車両を検索する場合は次のようかSQLになる*1。

-- carsテーブル: 車両の基本情報を管理
CREATE TABLE cars (
    car_id SERIAL PRIMARY KEY,
    car_name TEXT NOT NULL
);

-- reservationsテーブル: 車両IDと予約された時間帯を範囲型で管理
CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    car_id INT NOT NULL REFERENCES cars(car_id),
    reservation_time tstzrange NOT NULL
);

-- 車両データのサンプル投入
INSERT INTO cars (car_name) VALUES ('Car A'), ('Car B'), ('Car C');

-- 予約データのサンプル投入
-- Car Aは2024/12/10 11:00～12:30、Car Bは2024/12/10 12:30～13:30で予約があるとする
INSERT INTO reservations (car_id, reservation_time) VALUES
(1, tstzrange('2024-12-10 11:00:00+09','2024-12-10 12:30:00+09','[)')),  -- Car A
(2, tstzrange('2024-12-10 12:30:00+09','2024-12-10 13:30:00+09','[)')); -- Car B
car_id car_name
1 Car A
2 Car B
3 Car C
reservation_id car_id reservation_time
1 1 ["2024-12-10 11:00:00+09","2024-12-10 12:30:00+09")
2 2 ["2024-12-10 12:30:00+09","2024-12-10 13:30:00+09")

　このデータであれば、2024/12/10 12:00 - 2024/12/10 13:00 で検索すると次のようになる。

  • Car A は11:00～12:30まで予約済みのため、12:00～13:00の範囲と重なる
  • Car B は12:30～13:30まで予約済みなので、12:00～13:00とは12:30～13:00が重なる
  • Car C は予約なしのため重ならず利用可能

　これを実際にテーブルに対して検索すると以下のようになる。

SELECT c.*
FROM cars c
WHERE NOT EXISTS (
    SELECT 1
    FROM reservations r
    WHERE r.car_id = c.car_id
      AND r.reservation_time && tstzrange('2024-12-10 12:00:00+09', '2024-12-10 13:00:00+09', '[)'));
car_id car_name
3 Car C

　他にも指定された時間に予約されている車両を探す場合は以下のようなSQLになる。

SELECT c.*
FROM cars c
JOIN reservations r ON c.car_id = r.car_id
WHERE r.reservation_time @> '2024-12-10 12:50:00+09'::timestamptz;
car_id car_name
2 Car B

　このように時間枠に対して、柔軟な検索ができることが範囲型で時間を扱うメリットである。

INDEXの活用

　@> や && などの演算子検索はGiST INDEXを設定することで高速に検索できる。 設定方法は以下の通り。

-- 検索用のINDEX
CREATE INDEX idx_reservations_reservation_time_gist
ON reservations USING gist (reservation_time);

今回はレコードが2行しかないので実行計画を見ると idx_reservations_reservation_time_gist のINDEXは使わないがちゃんと1000件以上のデータなどであればINDEXを使ってくれる。

www.postgresql.jp

排他制約によるダブルブッキング防止

　時間の扱いの難しいところに検索と同じくらい、ダブルブッキングの対応がある。 今回の場合、同じ車両に対する予約の重複を防ぐ方法として排他制約がある。 これについては過去紹介したのでそちらを見てほしい。

soudai.hatenablog.com

　今回の例で排他制約を有効化する場合は以下のとおり。

-- 排他制約の有効化
-- btree_gist拡張の有効化(まだでなければ)
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- 排他制約の追加
ALTER TABLE reservations
ADD CONSTRAINT reservations_no_overlap
EXCLUDE USING gist (
  car_id WITH =,
  reservation_time WITH &&
);

　このように複数の車両があったとしても、同じ車両への予約のダブルブッキング、みたいなことをRDBMSの制約で防ぐことができる。 これは例えばホテルの部屋管理や車の時間割の担当のアサインなど様々なスケジュール管理のシーンでとても有効な選択肢である。

PostgreSQL 14以降の「複数範囲型」対応

　ここまでが範囲型の話なのだが、そこにさらにPostgreSQL 14から複数範囲型がサポートされたことでより進化し、時間枠の扱いとして決定版といえる設計が可能になったので紹介する。

範囲型を用いた計算

　範囲型を使うと、範囲同士の演算（引き算など）も可能になる。 ただし、1つの範囲が複数に分割されるケースにおいては、従来の範囲型ではERRORになり、扱いが困難であった。 しかし、複数範囲型を使えば、分割後の範囲もデータ型として保持できる。

　これを活用することで、たとえば店舗の営業時間（範囲）から既存の予約枠（範囲）を引き算し、現在の空き時間（複数範囲）を計算するといったことが可能になった。

WITH store_hours AS (
    -- 営業時間をmultirangeへキャスト
    SELECT tstzrange('2024-12-10 09:00:00+09', '2024-12-10 18:00:00+09', '[)')::tstzmultirange AS open_time),
     reserved AS (
         -- 営業時間帯に重なる予約を1つのmultirangeに集約
         SELECT range_agg(reservation_time)::tstzmultirange AS reserved_time
         FROM reservations
         WHERE reservation_time && tstzrange('2024-12-10 09:00:00+09', '2024-12-10 18:00:00+09', '[)'))
SELECT unnest(open_time - COALESCE(reserved_time, '{}'::tstzmultirange)) AS free_slots
FROM store_hours,
     reserved;
free_slots
["2024-12-10 09:00:00+09","2024-12-10 11:00:00+09")
["2024-12-10 13:30:00+09","2024-12-10 18:00:00+09")

　複数範囲範囲型を使うことで複数個の予約枠が予約可能か、などの検索もできる。 これによって、複数日程の旅行の予約枠検索なども可能になる。

検索や排他制約への応用

　計算だけでなく検索も排他制約も同様に設定できる。 例えば社員の今月のシフトを作る際に出勤日単位でレコードが増えていたような場合は月単位でレコードをまとめることでレコード数を減らせる。

範囲型を使って有効な時間の一覧を出す

　最後にPostgreSQLの generate_series() と範囲型を組み合わせると店舗での受付可能な時間枠一覧を生成できる。 そこで今回は60分単位の予約枠を30分刻みで受付可能な開始時刻として一覧化するSQLを紹介する。

WITH slots AS (
  SELECT gs AS start_time,
    -- 営業時間の一覧から予約に必要な時間の枠を生成する   
    tstzrange(gs, gs + INTERVAL '60 minutes', '[)') AS slot_range
  FROM generate_series(
    -- 30分刻みの対象の営業時間の一覧を生成する
    '2024-12-10 09:00:00+09'::timestamptz,
    '2024-12-10 18:00:00+09'::timestamptz,
    '30 minutes'
  ) AS gs
)
SELECT start_time, slot_range
FROM slots
WHERE NOT EXISTS (
  SELECT 1
  FROM reservations r
  -- slots.slot_rangeが予約に含まれていないことを確認
  WHERE r.reservation_time && slot_range
)
ORDER BY start_time;
start_time slot_range
2024-12-10 09:00:00.000000 +09:00 ["2024-12-10 09:00:00+09","2024-12-10 10:00:00+09")
2024-12-10 09:30:00.000000 +09:00 ["2024-12-10 09:30:00+09","2024-12-10 10:30:00+09")
2024-12-10 10:00:00.000000 +09:00 ["2024-12-10 10:00:00+09","2024-12-10 11:00:00+09")
2024-12-10 13:30:00.000000 +09:00 ["2024-12-10 13:30:00+09","2024-12-10 14:30:00+09")
2024-12-10 14:00:00.000000 +09:00 ["2024-12-10 14:00:00+09","2024-12-10 15:00:00+09")
2024-12-10 14:30:00.000000 +09:00 ["2024-12-10 14:30:00+09","2024-12-10 15:30:00+09")
2024-12-10 15:00:00.000000 +09:00 ["2024-12-10 15:00:00+09","2024-12-10 16:00:00+09")
2024-12-10 15:30:00.000000 +09:00 ["2024-12-10 15:30:00+09","2024-12-10 16:30:00+09")
2024-12-10 16:00:00.000000 +09:00 ["2024-12-10 16:00:00+09","2024-12-10 17:00:00+09")
2024-12-10 16:30:00.000000 +09:00 ["2024-12-10 16:30:00+09","2024-12-10 17:30:00+09")
2024-12-10 17:00:00.000000 +09:00 ["2024-12-10 17:00:00+09","2024-12-10 18:00:00+09")
2024-12-10 17:30:00.000000 +09:00 ["2024-12-10 17:30:00+09","2024-12-10 18:30:00+09")
2024-12-10 18:00:00.000000 +09:00 ["2024-12-10 18:00:00+09","2024-12-10 19:00:00+09")

　これによって、予約済みの時間帯を除外した候補枠を一覧表示できる。 たとえば11:00からはCar Aの予約が入っているため、10:30以降は受付ができない。

このパターンも予約サイトなどで頻出するユースケースだが、このクエリは INDEXが利用されているため、高速に検索 できてる。

注意点

　PostgreSQLの独自機能を使う実装なのでMySQLなどに移行することはできなくなる。 またORMが対応していないことも多く、実際には予約枠可能テーブルとしてViewを定義して、それに対してORMから扱うことが多くなる。 Viewの扱いなどはマイグレーションの方法にも影響を与えるので、運用も含めて検討してほしい。

終わりに

　複数範囲型や範囲型を利用した時間枠の扱いを紹介した。 自分の関わっているプロジェクトでは実際に活用していて、実績もあるが他にプロジェクなどでは見たことがないので紹介した。 新規案件の場合などはこの設計のためだけにPostgreSQLを選択するメリットがあるのでぜひ検討してほしい。

*1:ChatGPTでサンプル作った。めちゃ便利。しかもちゃんと動く