> ## Content Index
> Fetch the complete content index at: https://mlog.me/llms.txt
> Use this file to discover other available public pages before exploring further.

# PostgreSQL 예약 시간 중복 방지: EXCLUDE와 취소·재확정 검사
- URL: https://mlog.me/postgresql-exclude-overlapping-reservations-cancel-reconfirm/
- Published: 2026-11-10T05:55:41.000Z
- Updated: 2026-11-10T05:55:41.000Z
- Description: PostgreSQL의 EXCLUDE와 tstzrange로 예약 시간 겹침을 막고 취소·재확정에도 같은 규칙을 적용합니다. 순차 SQL 18개 검증 결과와 동시성 추가 확인 절차를 정리했습니다.
- Author: mLog
- Tags: 데이터베이스, PostgreSQL, 데이터 모델링, #예약-동시성

**겹치는 예약이 없다는 SELECT 결과는 그 시간을 확보했다는 뜻이 아니다. 같은 자원의 예약 시간이 겹치면 저장을 거절하도록 DB 제약을 두고, 취소·재확정 같은 UPDATE에도 같은 규칙을 적용해야 한다.**

예를 들어 한 공간에 09:00\~10:00 예약이 있을 때 09:30\~10:30 예약을 막아야 한다. 시작 시각이 다르므로 `(자원, 시작 시각)`의 UNIQUE만으로는 이 규칙을 표현할 수 없다. PostgreSQL의 범위 타입과 EXCLUDE를 사용하면 ‘자원이 같고 시간이 겹친다’는 조합을 검사할 수 있다.

이 글은 한 자원을 같은 시간에 한 예약만 독점하는 모델을 다룬다. 좌석 수만큼 여러 예약을 받는 정원형 시설, 수량을 나누는 장비 재고, 여러 공간을 한 번에 묶는 예약에는 별도 설계가 필요하다.

검증일은 **2026-11-10**이다. PostgreSQL 17.11과 `btree_gist 1.7`의 격리된 single-user 환경에서 순차 SQL 18개를 확인했다. 동시 접속과 실제 애플리케이션 연동은 검증하지 않았다. 실제 서비스에서 발생한 장애나 성능 개선 사례가 아니라 설명용 데이터로 수행한 스키마 실험이다.

## 1\. 빈 시간 조회와 예약 확정은 역할이 다르다

저장된 예약이 `[시작, 종료)` 방식이라면 새 구간과의 겹침은 다음 조건으로 조회할 수 있다.

```sql
-- $1: 자원 ID, $2: 요청 시작, $3: 요청 종료
-- 입력값의 필수 여부와 시작 < 종료 검사는 별도로 선행한다.
SELECT reservation_id
FROM mlog_booking_lab.reservation
WHERE resource_id = $1
  AND status = 'confirmed'
  AND starts_at < $3::timestamptz
  AND ends_at   > $2::timestamptz;

```

이 조회는 선택 가능한 시간을 안내하는 데 유용하다. 하지만 기본 Read Committed에서 다른 요청의 커밋되지 않은 변경을 보지 못할 수 있고, 조회가 끝난 뒤 저장하기 전에도 다른 요청이 들어올 수 있다. [PostgreSQL 트랜잭션 격리](https://www.postgresql.org/docs/18/transaction-iso.html?ref=mlog.me)

기존 예약을 찾는 SELECT에 `FOR UPDATE`를 붙여도 결과가 없으면 잠글 예약 행이 없다. 모든 경로에서 별도 자원 행을 잠그는 설계는 가능하지만, 단순히 조회 쿼리 끝에 붙이는 것과는 다른 정책이다. [PostgreSQL 행 잠금](https://www.postgresql.org/docs/17/explicit-locking.html?ref=mlog.me)

조회 조건 자체의 포함·겹침 차이는 [PostgreSQL 기간 겹침 조회](https://mlog.me/postgresql-date-range-overlap-start-end-boundaries/)에서 설명했다. 여기서는 조회 후 쓰기 규칙에 집중한다.

## 2\. 같은 자원과 겹치는 시간을 함께 검사한다

아래는 **새 실습 DB**에서 실행할 스키마다. 기존 운영 테이블에 그대로 덮어쓰는 마이그레이션이 아니다. `btree_gist` 확장 파일이 서버에 준비돼 있어야 하며, 확장·스키마 생성 권한도 필요하다.

```sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE SCHEMA mlog_booking_lab;

CREATE TABLE mlog_booking_lab.reservation (
    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    resource_id bigint NOT NULL,
    starts_at timestamptz NOT NULL,
    ends_at timestamptz NOT NULL,
    status text NOT NULL DEFAULT 'confirmed',

    CONSTRAINT reservation_status_ck
        CHECK (status IN ('confirmed', 'cancelled')),
    CONSTRAINT reservation_period_ck
        CHECK (isfinite(starts_at) AND isfinite(ends_at)
               AND starts_at < ends_at),

    CONSTRAINT reservation_no_overlap
        EXCLUDE USING gist (
            resource_id WITH =,
            tstzrange(starts_at, ends_at, '[)') WITH &&
        ) WHERE (status = 'confirmed')
);

```

`resource_id WITH =`와 `시간 범위 WITH &&`가 모두 참인 두 확정 예약을 허용하지 않는다. 다른 자원은 같은 시각을 사용할 수 있다. `WHERE`는 검사 대상을 확정 상태로 좁힌다. [CREATE TABLE의 EXCLUDE](https://www.postgresql.org/docs/18/sql-createtable.html?ref=mlog.me)

`btree_gist`는 bigint 같은 일반 타입의 비교를 GiST 구성에 함께 쓰도록 해준다. 이것을 모든 B-tree 인덱스의 교체 수단이나 자동 성능 개선 기능으로 해석하지 않는다. [btree\_gist 공식 문서](https://www.postgresql.org/docs/18/btree-gist.html?ref=mlog.me)

[요금 조회 우선순위와 이력 설계](https://mlog.me/postgresql-fare-priority-effective-date-query/)에서도 기간 배제 제약을 짧게 다뤘다. 이번 예제는 취소 이력을 남기면서 확정 상태에만 같은 규칙을 적용하는 경우다. 자원 ID가 조직 안에서만 유일하다면 조직 ID까지 충돌 키에 포함해야 하며, 본 예제는 자원 ID가 전체 범위에서 유일하다고 가정한다.

## 3\. 맞닿은 시간과 잘못된 입력을 나눈다

`[)`는 시작을 포함하고 종료를 제외한다. 따라서 09:00\~10:00 바로 다음에 10:00\~11:00을 배정할 수 있다. 이 정책이 청소·준비 시간까지 허용한다는 의미는 아니다. 준비 시간이 필요하면 점유 구간에 포함할지 업무 규칙부터 정해야 한다. [PostgreSQL 범위와 경계](https://www.postgresql.org/docs/18/rangetypes.html?ref=mlog.me)

```sql
-- 모두 설명용 자원 번호와 날짜다.
INSERT INTO mlog_booking_lab.reservation
    (resource_id, starts_at, ends_at)
VALUES
    (101, '2026-11-20 09:00+09', '2026-11-20 10:00+09'),
    (101, '2026-11-20 10:00+09', '2026-11-20 11:00+09'),
    (102, '2026-11-20 09:30+09', '2026-11-20 10:30+09');

```

아래는 같은 자원의 기존 구간과 겹치므로 거절 대상이다.

```sql
INSERT INTO mlog_booking_lab.reservation
    (resource_id, starts_at, ends_at)
VALUES
    (101, '2026-11-20 09:30+09', '2026-11-20 10:30+09');

```

실험에서는 `23P01`과 제약 이름 `reservation_no_overlap`을 확인했다. 위 두 코드 블록과 같은 경계·자원 조합을 검증 스크립트에서도 검사했다.

겹침 검사만으로 필수값과 기간 유효성 검사를 대신하지 않는다. 본 스키마는 NULL을 `NOT NULL`로, 0 길이·역전 기간을 CHECK로 막는다. `infinity`는 NULL과 다르므로 유한한 시각만 받겠다는 정책을 `isfinite`로 명시했다. [제약 조건](https://www.postgresql.org/docs/18/ddl-constraints.html?ref=mlog.me), [날짜·시간 함수](https://www.postgresql.org/docs/17/functions-datetime.html?ref=mlog.me)

입력에는 `+09`처럼 오프셋을 명시했다. 테스트에서는 `09:30+09`와 `00:30+00`이 같은 시점으로 충돌함도 확인했다. 실제 API에서는 시간대가 없는 문자열을 어느 지역의 시각으로 해석하는지 계약을 정해야 한다. 반복 일정의 ‘매주 오전 9시’ 규칙까지 이 테이블 하나로 표현한 것은 아니다.

## 4\. 취소 기록을 남겨도, 재확정은 다시 검사한다

이번 모델에서 취소는 행 삭제가 아니라 상태 변경이다. 취소된 행은 남지만 EXCLUDE의 확정 예약 비교에서는 제외된다. 반대로 다시 확정하거나 자원·시각을 바꾸면 새 값으로 제약을 만족해야 한다.

각 상태 변경은 별도 요청이다. 아래 두 문장을 하나의 취소 작업으로 연속 실행하는 예제가 아니다.

```sql
-- 취소 요청: 실제 변경할 예약 ID를 바인딩한다.
UPDATE mlog_booking_lab.reservation
SET status = 'cancelled'
WHERE reservation_id = $1
  AND status = 'confirmed';

```

```sql
-- 재확정: 그 사이 같은 시간이 다른 예약에 배정됐다면 충돌한다.
UPDATE mlog_booking_lab.reservation
SET status = 'confirmed'
WHERE reservation_id = $1
  AND status = 'cancelled';

```

실험에서는 겹치는 취소 행을 저장할 수 있었지만, 그 행의 재확정은 `23P01`로 거절됐다. 다른 자원의 예약을 충돌 자원으로 옮기는 UPDATE도 거절됐다. 취소 후 대체 예약 저장은 순차 실행에서 허용됐다. **다른 세션에 보이는 취소의 커밋 시점은 이번 순차 실험으로 측정하지 않았다.**

애플리케이션은 UPDATE의 영향 행 수도 확인해야 한다. 0행이면 해당 상태의 예약을 변경하지 못했다는 뜻이지, 원하는 전환이 성공했다는 뜻은 아니다. 사용자 권한·예약 소유권·허용 상태 전환은 별도 조건이다.

`status = 'confirmed'`는 이 글의 단순화된 규칙이다. 승인 대기나 결제 대기도 시간을 점유해야 한다면 해당 상태를 비교 대상에 포함하는 방향으로 다시 설계해야 한다. 임의 상태가 우회 통로가 되지 않도록 허용 상태 목록도 함께 관리한다.

## 5\. 시간이 지났다고 부분 인덱스의 행이 자동으로 빠지지는 않는다

임시 점유에 만료 시각을 추가하고 다음처럼 처리하고 싶을 수 있다.

```sql
-- 이 방식으로 시간 경과에 따른 자동 제외를 구현하면 안 된다.
CREATE INDEX invalid_time_predicate
ON mlog_booking_lab.reservation(resource_id)
WHERE ends_at > now();

```

실험에서 이 문장은 `42P17`로 거절됐다. 인덱스 식과 조건에 사용하는 함수는 현재 시각 같은 외부 상태에 의존하면 안 된다. [CREATE INDEX](https://www.postgresql.org/docs/17/sql-createindex.html?ref=mlog.me)

임시 점유를 지원한다면 만료 처리 작업이 상태를 실제로 변경하고 커밋하는 절차, 예약 확정과 만료 처리의 경쟁, 작업 재시도 규칙을 별도로 설계해야 한다. 이 글의 두 상태 스키마는 TTL 자동 만료 기능을 제공하지 않는다. [Redis 락 TTL과 DB 중복 방지](https://mlog.me/redis-lock-ttl-expiry-safe-release-seat-reservation/)도 함께 읽으면 락의 만료와 예약의 최종 상태를 구분하는 데 도움이 된다.

## 6\. 동시 요청은 별도 두 세션에서 검증한다

다음은 **두 세션에서 직접 확인할 수 있는 추가 검증 절차**다. 이번 실행 환경은 소켓 생성이 차단되어 이 절차를 완료하지 못했다. 아래 결과는 직접 관측한 로그가 아니라 제약 의미에 따른 예상 결과다. 대기 시간이나 처리량 수치를 제시하지 않는다.

새 실습 DB에 2절 스키마를 만든 뒤 동일 DB에 psql 두 개를 연결한다. 아직 사용하지 않은 자원 `301`을 사용한다.

세션 A에서 실행하고 COMMIT은 잠시 보류한다.

```sql
BEGIN;
INSERT INTO mlog_booking_lab.reservation
    (resource_id, starts_at, ends_at)
VALUES (301, '2026-11-20 09:00+09', '2026-11-20 10:00+09');

```

세션 B에서 겹치는 구간을 요청한다.

```sql
\set VERBOSITY verbose
BEGIN;
SET LOCAL statement_timeout = '60s';
INSERT INTO mlog_booking_lab.reservation
    (resource_id, starts_at, ends_at)
VALUES (301, '2026-11-20 09:30+09', '2026-11-20 10:30+09');

```

곧바로 A에서 `COMMIT;`을 실행한다. 단순한 이 두 요청에서 기대하는 결과는 B가 충돌을 확인하고 `23P01`을 받는 것이다. B에서는 오류 뒤 `ROLLBACK;`으로 트랜잭션을 끝낸다. 60초 제한에 먼저 걸리면 그것은 제약 충돌 성공 결과가 아니라 타임아웃이므로 구분한다.

두 번째 시험은 새 자원 `302`로 같은 순서를 반복하되 A에서 `ROLLBACK;`한다. 다른 충돌이 없다면 B가 저장할 수 있어야 하며 B도 `COMMIT;`한다. 각 시험 뒤 확정 행 수와 서로 겹치는 확정 쌍이 없는지 확인한다. 여러 자원을 한 번에 잡는 요청, 취소와 재예약의 교차 실행, 교착·타임아웃은 이 두 사례만으로 검증되지 않는다.

이 스키마는 기본 `NOT DEFERRABLE`을 사용한다. 제약을 지연 검사하도록 바꾼다면 오류가 드러나는 시점도 다시 검증해야 한다. [제약 검사 시점](https://www.postgresql.org/docs/18/sql-createtable.html?ref=mlog.me)

## 7\. 모든 DB 오류를 ‘이미 예약됨’으로 처리하지 않는다

실험에서 확인한 거절 종류는 서로 다르다.

| 조건              | 확인한 SQLSTATE | 애플리케이션에서 구분할 일         |
| --------------- | ------------ | ---------------------- |
| 같은 자원·겹치는 확정 시간 | 23P01        | 제약 이름까지 확인 후 예약 충돌로 안내 |
| 필수 자원·시작 시각 누락  | 23502        | 입력 계약과 바인딩 오류 확인       |
| 유효하지 않은 기간·상태   | 23514        | 어떤 CHECK가 실패했는지 확인     |

오류 문장의 언어·문구를 문자열로 비교하기보다 SQLSTATE와 구조화된 제약 이름을 확인하는 편이 명확하다. `23P01`도 다른 배제 제약에서 나올 수 있으므로 전부 같은 예약 오류로 묶지 않는다. [PostgreSQL 오류 코드](https://www.postgresql.org/docs/18/errcodes-appendix.html?ref=mlog.me)

명시적 트랜잭션에서 제약 오류가 나면 전체 롤백이나 적절한 SAVEPOINT 복구가 필요하다. 외부에 성공 응답이나 확정 알림을 먼저 내보내지 말고, 해당 쓰기가 실제 커밋됐는지 기준을 맞춘다. Spring에서 `catch`만으로 트랜잭션을 되살릴 수 있는지 헷갈린다면 [UnexpectedRollbackException과 rollback-only](https://mlog.me/spring-unexpected-rollback-exception-catch-rollback-only/)로 이어서 확인한다. 이 글은 Spring 예외 변환이나 HTTP 응답 구현까지 실험한 결과가 아니다.

## 8\. 기존 테이블에는 데이터와 잠금 계획을 먼저 확인한다

운영 테이블에 제약을 추가하기 전에 확정 상태, 필수값, 시간 경계가 같은 의미인지 확인한다. 아래는 본문 스키마에서 이미 저장된 확정 예약의 겹침 쌍을 찾는 읽기 전용 예시다.

```sql
SELECT a.reservation_id AS first_id,
       b.reservation_id AS second_id,
       a.resource_id
FROM mlog_booking_lab.reservation a
JOIN mlog_booking_lab.reservation b
  ON a.reservation_id < b.reservation_id
 AND a.resource_id = b.resource_id
 AND a.starts_at < b.ends_at
 AND b.starts_at < a.ends_at
WHERE a.status = 'confirmed'
  AND b.status = 'confirmed';

```

대량 데이터의 self join은 부하를 줄 수 있다. 실습 결과를 보고 운영 전체에 무제한으로 실행하지 말고 복제본·스테이징에서 실행 계획과 범위를 먼저 검토한다. NULL, 역전 기간, 무한 시각은 별도 품질 검사 대상이다.

EXCLUDE는 `NOT VALID`로 추가해 나중에 검증하는 방식에 해당하지 않는다. 이번 PostgreSQL 17.11에서도 해당 시도는 `0A000`으로 거절됐다. 기존 인덱스를 `USING INDEX`로 제약에 연결하는 UNIQUE/PRIMARY KEY 절차를 그대로 가져오는 것도 맞지 않는다. [ALTER TABLE](https://www.postgresql.org/docs/18/sql-altertable.html?ref=mlog.me)

운영 적용은 기존 충돌 처리, 백업·복구 확인, 쓰기 경로 정리, 제약 생성 중 잠금과 소요 시간 검증을 포함한 별도 변경 작업이어야 한다. 오류를 없애기 위해 예약 행을 임의 삭제하거나 제약을 제거하는 방식은 복구 절차로 제시하지 않는다.

## 9\. 이번 실험에서 남긴 결론과 한계

순차 SQL 검사에서는 겹치는 확정 예약을 거절하면서 맞닿은 구간과 다른 자원을 허용했다. 취소 행을 남긴 상태에서도 재확정과 자원 변경이 다시 검사되는 것을 확인했다. NULL·기간·상태 검사는 겹침 제약과 분리해서 갖췄다.

하지만 18개 통과를 동시 요청·운영 성능 검증으로 확대하지 않는다. 배포 후보로 확정하려면 두 세션의 커밋·롤백, 취소와 재확정의 경합, 실제 애플리케이션의 오류·트랜잭션 처리를 추가로 확인해야 한다. 이 글의 핵심은 **조회 결과, 저장 제약, 상태 전환을 같은 예약 규칙으로 맞추는 것**이다.