[PostgreSQL] 예약 이중예약 원천봉쇄: range + EXCLUDE + btree_gist + DEFERRABLE + IDENTITY

설치·접속: PostgreSQL 설치와 접속

부제: 회의실 예약 API에서 동시 요청이 겹쳐 이중예약이 가끔 뚫릴 때, 애플리케이션 락 없이 DB 제약만으로 막는다

회의실 예약에서 두 요청이 거의 동시에 들어오면 이런 코드가 뚫린다.

1) SELECT ... WHERE 시간대 겹침  → 0건
2) INSERT 예약

두 요청이 1번을 동시에 통과한 뒤 각자 2번을 INSERT하면 겹치는 예약 두 건이 들어간다. 애플리케이션 락으로 막을 수도 있지만, 여기서는 DB 제약 하나로 옮긴다. tsrange + EXCLUDE USING GIST부터 시작한다.

1단계 — 단일 리소스 겹침 차단 (range + EXCLUDE)

시간 구간을 tsrange로 저장하고, "겹치는(&&) 두 행은 공존할 수 없다"를 EXCLUDE USING GIST (during WITH &&)로 선언한다. PK는 GENERATED ALWAYS AS IDENTITY로 둔다(뒤에서 설명).

CREATE TABLE reservation (
    id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

INSERT INTO reservation (during) VALUES ('[2010-01-01 11:30, 2010-01-01 15:00)');  -- OK
INSERT INTO reservation (during) VALUES ('[2010-01-01 14:45, 2010-01-01 15:45)');  -- 겹침

INSERT 0 1
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts with
         existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

두 번째 INSERT가 14:45~15:00 구간에서 첫 예약과 겹쳐 DB가 거부했다. EXCLUDE는 UNIQUE의 "겹침 버전"이다. UNIQUE는 등호(=) 충돌만 막지만 EXCLUDE는 &&(겹침) 같은 임의 연산자를 쓴다. 그리고 이 검사는 인덱스 레벨에서 원자적으로 일어나므로 위의 SELECT-후-INSERT 경쟁 조건이 사라진다.

2단계 — 방별로만 겹침 차단 (btree_gist)

회의실이 여러 개면 A방과 B방은 같은 시간이어도 된다. "방이 같고(=) 시간이 겹치는(&&)" 경우만 막고 싶다. 그런데 등호 비교를 GiST 인덱스에 태우는 건 기본 지원이 아니라 btree_gist 확장이 필요하다.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservation (
    id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room   text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

INSERT INTO room_reservation (room, during) VALUES ('A', '[2010-01-01 14:00, 2010-01-01 15:00)');  -- OK
INSERT INTO room_reservation (room, during) VALUES ('B', '[2010-01-01 14:00, 2010-01-01 15:00)');  -- OK (다른 방)
INSERT INTO room_reservation (room, during) VALUES ('A', '[2010-01-01 14:30, 2010-01-01 15:30)');  -- A방 겹침

INSERT 0 1
INSERT 0 1
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts with
         existing key (room, during)=(A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

B방은 A방과 같은 14:00~15:00인데도 통과했다. 스칼라(room)와 range(during)를 한 제약에 섞을 수 있고, 스칼라의 등호 비교를 GiST에 얹어주는 게 btree_gist의 역할이다.

3단계 — 인접 예약은 겹치지 않는다 (반열림 구간 [))

09:0010:00 예약과 10:0011:00 예약은 붙어 있지만 겹치지 않아야 한다. range를 반열림 [)(시작 포함, 끝 제외)로 쓰면 이게 자동으로 성립한다.

-- 앞 예약의 끝(15:00)과 다음 예약의 시작(15:00)이 맞닿은 경우
SELECT tsrange('2010-01-01 11:30','2010-01-01 15:00')
    && tsrange('2010-01-01 15:00','2010-01-01 16:00') AS adjacent_overlap;

 adjacent_overlap
------------------
 f

[11:30,15:00)은 15:00을 포함하지 않고 [15:00,16:00)은 15:00부터라, 경계점이 어느 한쪽에만 속해 겹치지 않는다. 그래서 15:00에 끝나는 예약 바로 뒤에 15:00 시작 예약을 넣어도 EXCLUDE가 통과시킨다.

INSERT INTO reservation (during) VALUES ('[2010-01-01 15:00, 2010-01-01 16:00)');  -- OK, 인접
SELECT * FROM reservation ORDER BY id;

 id |                    during
----+-----------------------------------------------
  1 | ["2010-01-01 11:30:00","2010-01-01 15:00:00")
  3 | ["2010-01-01 15:00:00","2010-01-01 16:00:00")

여기서 id가 1 다음에 3인 걸 눈여겨보자. 1단계에서 실패한 INSERT가 IDENTITY 시퀀스 값 2를 이미 소비했다(시퀀스는 롤백되지 않는다). PK를 앱이 아니라 DB가 뽑아주니 이런 구멍이 나도 중복 걱정이 없다.

4단계 — PK는 GENERATED ALWAYS AS IDENTITY

동시성 안전을 DB 제약으로 옮겼으니, PK도 앱이 실수로 끼어들 여지를 없앤다. GENERATED ALWAYS AS IDENTITY는 사용자가 id를 직접 넣는 것 자체를 거부한다.

INSERT INTO reservation (id, during) VALUES (100, '[2010-01-02 09:00, 2010-01-02 10:00)');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

serial이었다면 앱이 실수로 넣은 명시 값이 시퀀스와 어긋나 나중에 중복 키 사고로 번졌을 텐데, ALWAYS는 그 실수를 입구에서 막는다.

결론 — 언제 이 조합을 쓰나

"같은 리소스에 시간(또는 숫자) 구간이 겹치면 안 된다"가 비즈니스 규칙이면 이 조합이 정석이다. tsrange + EXCLUDE로 겹침을 선언적으로 금지하고, 다중 리소스면 btree_gist로 (키 WITH =, 구간 WITH &&), 인접 허용은 반열림 [), PK는 IDENTITY. 애플리케이션 락도, 조언 락(advisory lock)도 없이 동시 INSERT가 DB 레벨에서 직렬화된다.

이렇게도 쓴다

tstzrange로 타임존까지 저장해 글로벌 예약에 쓴다.

EXCLUDE USING GIST (room WITH =, tstzrange(starts_at, ends_at) WITH &&)

 

부분 EXCLUDE로 "취소되지 않은 예약"끼리만 겹침을 막는다. (조합: 부분 인덱스)

EXCLUDE USING GIST (room WITH =, during WITH &&) WHERE (status <> 'canceled')

 

순번 재배치처럼 커밋 시점에만 검증하고 싶으면 EXCLUDE를 지연시킨다. (조합: DEFERRABLE)

EXCLUDE USING GIST (during WITH &&) DEFERRABLE INITIALLY DEFERRED

 

겹치는지 앱에서 미리 물어볼 때도 같은 연산자를 쓴다.

SELECT EXISTS (SELECT 1 FROM room_reservation
  WHERE room='A' AND during && '[2010-01-01 14:30,2010-01-01 15:30)');

 

경계를 명시적으로 열고 닫아 요구사항에 맞춘다. [a,b]는 양끝 포함, (a,b)는 양끝 제외.

SELECT numrange(4,4) AS empty, numrange(4,4,'[]') AS one_point;  -- empty | [4,4]

파티션 테이블에는 EXCLUDE를 못 건다. 그럴 땐 파티션별 제약이나 트리거로 우회한다(다른 도구로 갈아탈 경계).