백엔드 개발을 하다 보면 한 번쯤 이런 에러를 보게 된다.
ERROR: duplicate key value violates unique constraint
Key (id)=(4) already existsauto increment인데 왜 중복이 발생할까?
처음에는 DB가 자동으로 값을 잘 관리해줄 거라고 생각하기 쉽다.
하지만 실제로는 그렇지 않다.
결론부터 말하면
시퀀스는 테이블 상태를 전혀 모른다.
auto increment의 정체
PostgreSQL에서 auto increment는 내부적으로 다음과 같이 동작한다.
nextval('sequence_name')이 함수는 단순히 숫자를 하나씩 증가시키며 반환한다.
중요한 점은 다음이다.
- 테이블을 조회하지 않는다
- 현재 PK 상태를 확인하지 않는다
- 단순히 숫자만 증가시킨다
즉, auto increment는 “똑똑한 증가 기능”이 아니라
독립적인 숫자 생성기에 가깝다.
duplicate key가 발생하는 구조
다음과 같은 상황을 가정해보자.
현재 상태
테이블 데이터:
id = 1, 2, 3, 4, 5
시퀀스 상태:
nextval = 4
이 상태에서 insert를 수행하면
INSERT INTO table (...) VALUES (...);실제로는 내부적으로 다음과 같이 처리된다.
id = nextval(...)
→ 4이미 테이블에 존재하는 값이기 때문에
duplicate key 에러가 발생한다.
시퀀스가 꼬이는 이유
이런 상황은 의외로 자주 발생한다.
1. 수동으로 PK를 넣은 경우
INSERT INTO table(id, ...) VALUES (100, ...);테이블에는 100이 들어가지만
시퀀스는 이 사실을 모른다.
이후 시퀀스는 기존 값 기준으로 계속 증가하게 되고
결국 충돌이 발생한다.
2. dump / restore
데이터를 백업했다가 복구할 때
- 데이터는 정상적으로 들어오지만
- 시퀀스 값은 초기 상태로 남아있는 경우가 많다
실무에서 가장 흔한 원인이다.
3. 서버 간 데이터 이관
운영 환경의 데이터를 개발 환경으로 복사할 때
- 테이블 데이터만 복사
- 시퀀스 값은 그대로
이 경우에도 쉽게 문제가 발생한다.
4. truncate에 대한 오해
TRUNCATE TABLE table;이 명령은 데이터를 삭제할 뿐
시퀀스 값은 초기화하지 않는다.
그래서 이후 insert 시 예상과 다른 값이 들어갈 수 있다.
5. 트랜잭션 롤백
SELECT nextval('seq'); -- 10
ROLLBACK;이 경우 많은 사람들이
값이 롤백될 것이라고 생각한다.
하지만 시퀀스는 트랜잭션과 무관하다.
- 10은 이미 사용된 값
- 다시 되돌아가지 않는다
그래서 중간 숫자가 비는 것은 정상적인 동작이다.
왜 이렇게 설계되어 있을까
시퀀스가 테이블과 독립적으로 동작하는 이유는 성능 때문이다.
만약 시퀀스가 테이블 상태를 매번 확인한다면
- 락이 발생하고
- 동시성이 크게 떨어진다
그래서 DB는 단순한 구조를 선택했다.
- 숫자를 빠르게 발급
- 충돌 여부는 제약조건으로 처리
해결 방법
문제가 발생했다면 시퀀스를 테이블에 맞춰주면 된다.
SELECT setval(
'sequence_name',
(SELECT MAX(id) FROM table)
);이렇게 하면 시퀀스가 현재 데이터 기준으로 재정렬된다.
실무에서의 대응 방법
1. 데이터 이관 후 시퀀스 정렬
데이터를 복사하거나 복구했다면
반드시 시퀀스를 맞춰야 한다.
2. PK 수동 입력 지양
특별한 이유가 없다면
auto increment에 맡기는 것이 안전하다.
3. duplicate key 발생 시 우선 확인
코드 문제로 보기 전에
시퀀스 상태를 먼저 확인하는 것이 빠르다.
정리
auto increment는 테이블을 기준으로 동작하는 기능이 아니다.
- 테이블 상태를 모른다
- 트랜잭션과 무관하다
- 단순히 숫자만 증가시킨다
이 구조를 이해하지 못하면
예상하지 못한 duplicate key 문제를 만나게 된다.
