설치·접속: PostgreSQL 설치와 접속
부제: "사용자당 활성 쿠폰 1개", "대소문자 무시 이메일 중복 금지" 같은 비즈니스 규칙을 앱 검증이 아니라 DB 인덱스로 강제할 때
"사용자당 미사용 쿠폰은 하나만", "Foo@x.com과 foo@x.com은 같은 계정"—이런 규칙을 애플리케이션 코드로 검사하면 동시 요청에 뚫린다. 부분 유니크 인덱스와 표현식 유니크 인덱스를 겹쳐 쓰면 이 규칙들을 DB가 원자적으로 막는다.
1단계 — 부분 유니크로 "활성 행 하나만" (WHERE status='active')
쿠폰 테이블에서 발급 이력(사용완료)은 여러 개 쌓이되, status='active'인 미사용 쿠폰은 사용자당 하나여야 한다. 일반 UNIQUE는 전체 행에 걸려 이력까지 막아버리니, 조건을 만족하는 부분집합에만 유일성을 거는 부분 유니크를 쓴다.
CREATE TABLE coupon (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer,
code text,
status text
);
-- active 행에 대해서만 user_id 유일
CREATE UNIQUE INDEX coupon_one_active ON coupon (user_id) WHERE status='active';
INSERT INTO coupon (user_id, code, status) VALUES (1, 'WELCOME10', 'active'); -- OK
INSERT INTO coupon (user_id, code, status) VALUES (1, 'SUMMER5', 'used'); -- OK (이력)
INSERT INTO coupon (user_id, code, status) VALUES (1, 'FALL5', 'used'); -- OK (이력)
INSERT INTO coupon (user_id, code, status) VALUES (1, 'SPRING20', 'active'); -- 두 번째 activeINSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR: duplicate key value violates unique constraint "coupon_one_active"
DETAIL: Key (user_id)=(1) already exists.used 행 두 개는 아무 제약 없이 들어갔지만, 두 번째 active 쿠폰은 거부됐다. WHERE status='active'가 인덱스에 들어가는 행을 활성 쿠폰으로 한정하기 때문에, 이력은 무제한 · 활성은 1개라는 규칙이 정확히 표현된다.
2단계 — 표현식 유니크로 대소문자 무시 중복 금지 (lower(email))
WHERE lower(email)=...는 email에 건 일반 인덱스를 못 탄다(함수가 씌워진 컬럼이라). 함수 결과 자체에 인덱스를 걸면 검색도 빨라지고, UNIQUE로 선언하면 대소문자만 다른 값의 삽입을 막는다.
CREATE TABLE account (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text
);
CREATE UNIQUE INDEX account_lower_email ON account (lower(email));
INSERT INTO account (email) VALUES ('Foo@x.com'); -- OK
INSERT INTO account (email) VALUES ('foo@x.com'); -- 대소문자만 다름INSERT 0 1
ERROR: duplicate key value violates unique constraint "account_lower_email"
DETAIL: Key (lower(email))=(foo@x.com) already exists.Foo@x.com과 foo@x.com은 lower()를 거치면 같은 값이라 두 번째가 막혔다. 대소문자 무시 유일성 검증을 앱이 아니라 DB로 옮긴 셈이다.
3단계 — 둘을 결합: soft delete + 대소문자 무시
이제 둘을 합친다. soft delete 환경에서 "살아있는(deleted_at IS NULL) 회원끼리만 이메일이 대소문자 무시로 유일"하게 한다. 탈퇴한 회원의 이메일은 재사용을 허용해야 하므로 표현식 유니크에 부분 조건을 얹는다.
CREATE TABLE member (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text,
deleted_at timestamptz
);
CREATE UNIQUE INDEX member_active_email
ON member (lower(email)) WHERE deleted_at IS NULL;
INSERT INTO member (email) VALUES ('bar@x.com'); -- OK
UPDATE member SET deleted_at = now() WHERE email='bar@x.com'; -- 탈퇴
INSERT INTO member (email) VALUES ('BAR@x.com'); -- OK (기존 행은 삭제됨)
INSERT INTO member (email) VALUES ('bar@x.com'); -- 살아있는 BAR와 충돌INSERT 0 1
UPDATE 1
INSERT 0 1
ERROR: duplicate key value violates unique constraint "member_active_email"
DETAIL: Key (lower(email))=(bar@x.com) already exists.SELECT id, email, (deleted_at IS NOT NULL) AS deleted FROM member ORDER BY id; id | email | deleted
----+-----------+---------
1 | bar@x.com | t
2 | BAR@x.com | f탈퇴한 1번(deleted_at 있음)은 인덱스에서 빠져 있어 같은 이메일 재가입이 통과했고, 살아있는 2번이 있는 상태에서 세 번째 시도는 막혔다. 부분(활성만) + 표현식(대소문자 무시) 두 성질을 하나의 인덱스가 동시에 강제한다.
결론 — 언제 이 조합을 쓰나
"어떤 조건을 만족하는 행끼리만 유일해야 한다"거나 "값을 정규화한 뒤 유일해야 한다"가 규칙이면, 앱 검증 대신 인덱스로 못 박는다. 활성 플래그·soft delete·대소문자/공백 정규화가 대표 사례다. 표현식 인덱스는 INSERT/비-HOT UPDATE마다 식을 재계산하니 쓰기보다 읽기·무결성이 중요한 컬럼에 맞다.
이렇게도 쓴다
NULL 하나만 허용해 "대표(primary) 주소 1개"를 강제한다.
CREATE UNIQUE INDEX ON addresses (user_id) WHERE is_primary;
두 컬럼을 이어붙인 표현식에 유니크를 건다. 괄호를 이중으로 감싸는 게 함정.
CREATE UNIQUE INDEX people_full ON people ((first_name || ' ' || last_name));
정규화 함수가 인덱스를 타려면 IMMUTABLE이어야 한다. (조합: 함수 volatility)
CREATE FUNCTION norm(text) RETURNS text IMMUTABLE LANGUAGE sql AS $$ SELECT lower(trim($1)) $$;
CREATE UNIQUE INDEX ON member (norm(email)) WHERE deleted_at IS NULL;
대소문자 무시가 검색 전반에 필요하면 표현식 인덱스 대신 citext 타입으로 갈아탄다(다른 도구로 갈아탈 경계).
CREATE TABLE u (email citext UNIQUE); -- 컬럼 자체가 대소문자 무시 비교
부분 인덱스는 쿼리 WHERE가 인덱스 predicate와 (거의) 일치해야 planner가 인식한다. (조합: EXPLAIN)
EXPLAIN SELECT * FROM coupon WHERE user_id=1 AND status='active';