IDENTITY vs SEQUENCE — 자동 증가 키 생성 전략

지난 글에서 사용자 정의 타입과 도메인을 살펴봤다. 이번에는 테이블 설계에서 항상 마주치는 자동 증가 키 생성 방법을 다룬다. PostgreSQL에는 serial, SEQUENCE, IDENTITY 세 가지 메커니즘이 있다.

serial — 레거시 방식

serial은 PostgreSQL 고유 편의 타입으로, 내부적으로 시퀀스를 생성하고 컬럼 기본값으로 연결한다.

-- serial 선언
CREATE TABLE old_style (
    id serial PRIMARY KEY,
    name text
);

-- 위는 아래와 동일
CREATE SEQUENCE old_style_id_seq;
CREATE TABLE old_style (
    id integer DEFAULT nextval('old_style_id_seq') NOT NULL,
    name text
);
ALTER SEQUENCE old_style_id_seq OWNED BY old_style.id;

serial의 문제점:

1. SQL 표준이 아님 → 이식성 없음
2. 시퀀스와 컬럼의 연결이 느슨해 DROP 시 시퀀스가 남을 수 있음
3. 직접 값을 INSERT해도 오류가 나지 않아 시퀀스와 실제 값이 엇갈릴 수 있음

PostgreSQL 10 이후 공식 문서에서 IDENTITY를 권장하며 serial은 비권장 상태다.

GENERATED AS IDENTITY — 현대적 방식

SQL:2003 표준에 추가된 구문이다. serial과 달리 시퀀스가 컬럼에 완전히 귀속된다.

-- GENERATED ALWAYS: 직접 INSERT 불허
CREATE TABLE users (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

-- GENERATED BY DEFAULT: 직접 INSERT 허용 (마이그레이션 편의)
CREATE TABLE users_v2 (
    id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

GENERATED ALWAYS vs BY DEFAULT

-- ALWAYS: 직접 INSERT 시 오류
INSERT INTO users (id, name) VALUES (100, 'Alice');
-- ERROR: cannot insert a non-DEFAULT value into column "id"

-- ALWAYS 강제 override (데이터 복원 시 사용)
INSERT INTO users (id, name) OVERRIDING SYSTEM VALUE VALUES (100, 'Alice');

-- BY DEFAULT: 직접 INSERT 허용
INSERT INTO users_v2 (id, name) VALUES (100, 'Alice');  -- 정상
INSERT INTO users_v2 (name) VALUES ('Bob');              -- 시퀀스 채번

실무에서는 보통 GENERATED ALWAYS를 기본으로 사용하고, 대량 데이터 마이그레이션이 필요한 경우에만 BY DEFAULT를 선택한다.

시퀀스 옵션 지정

CREATE TABLE ticket (
    id bigint GENERATED ALWAYS AS IDENTITY (
        START  WITH 10000      -- 시작값
        INCREMENT BY 1         -- 증가폭
        MINVALUE 10000         -- 최솟값
        MAXVALUE 9999999999    -- 최댓값
        CACHE 50               -- 캐시 크기
        NO CYCLE               -- 최댓값 도달 시 오류
    ) PRIMARY KEY
);

IDENTITY 컬럼 수정

-- 시퀀스 재시작
ALTER TABLE users ALTER COLUMN id RESTART WITH 1000;

-- 시퀀스 옵션 변경
ALTER TABLE users ALTER COLUMN id SET GENERATED BY DEFAULT;
ALTER TABLE users ALTER COLUMN id SET (CACHE 100);

CREATE SEQUENCE — 독립 시퀀스

컬럼에 종속되지 않는 독립 시퀀스를 만들 수 있다. 여러 테이블이 같은 시퀀스를 공유하거나, 채번 후 별도 로직을 실행하는 경우에 사용한다.

-- 독립 시퀀스 생성
CREATE SEQUENCE event_id_seq
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807  -- bigint 최대
    CACHE 100
    NO CYCLE;

-- 채번
SELECT nextval('event_id_seq');        -- 1 (호출마다 증가)
SELECT currval('event_id_seq');        -- 같은 세션의 마지막 채번 값
SELECT lastval();                      -- 이 세션에서 마지막으로 채번한 값

-- 재설정 (마이그레이션 후)
SELECT setval('event_id_seq', 10000, true);   -- 다음 nextval = 10001
SELECT setval('event_id_seq', 10000, false);  -- 다음 nextval = 10000

CACHE — 성능 vs 갭

CACHE N은 시퀀스 값을 N개씩 미리 메모리에 올린다. nextval 호출 시 잠금 없이 빠르게 값을 반환하지만, 서버 재시작 시 캐시된 값이 사라져 **갭(GAP)**이 발생한다.

CACHE 50: 1~50 캐시 중 재시작 → 51~100 시작
갭: 사용하지 못한 값들 (51~... 아니면 1~50 중 일부)

시퀀스 값에 연속성을 보장하면 안 된다. 결제 번호나 계약 번호처럼 공백 없는 채번이 필요하면 별도 로직(비관적 잠금 + 별도 테이블)이 필요하다.

시퀀스 조회와 모니터링

-- 현재 DB의 시퀀스 목록
SELECT sequencename, last_value, increment_by, cache_size
FROM pg_sequences
WHERE schemaname = 'public';

-- 특정 테이블의 IDENTITY 시퀀스 확인
SELECT pg_get_serial_sequence('users', 'id');
-- public.users_id_seq

-- 시퀀스 최대값과 현재 값 비교 (고갈 경고)
SELECT seqrelid::regclass AS seq,
       last_value,
       max_value,
       round(last_value::numeric / max_value * 100, 2) AS pct_used
FROM pg_sequences
WHERE schemaname = 'public'
ORDER BY pct_used DESC;

bigint 선택 — 오버플로우 방지

-- integer 시퀀스: 2,147,483,647 최대
-- 초당 1000건 INSERT → 약 24.8일이면 고갈

-- bigint 시퀀스: 9,223,372,036,854,775,807 최대
-- 초당 1,000,000건 INSERT → 약 292,471년

신규 테이블은 항상 bigint GENERATED ALWAYS AS IDENTITY로 시작하는 것이 안전하다. integer로 시작했다가 나중에 바꾸려면 전체 테이블 재작성이 필요하다.

마이그레이션 패턴

다른 DB에서 데이터를 가져올 때는 GENERATED BY DEFAULT와 setval을 조합한다.

-- 1. BY DEFAULT로 직접 ID 삽입 허용
CREATE TABLE imported_users (
    id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ...
);

-- 2. 기존 데이터 삽입
INSERT INTO imported_users (id, name, ...) SELECT id, name, ... FROM legacy_table;

-- 3. 시퀀스를 최대 ID+1로 재설정
SELECT setval(
    pg_get_serial_sequence('imported_users', 'id'),
    (SELECT MAX(id) FROM imported_users)
);

-- 4. 이후 INSERT는 시퀀스가 이어받음

정리

serial은 레거시다. 신규 테이블은 bigint GENERATED ALWAYS AS IDENTITY를 기본으로 삼는다. 데이터 마이그레이션이 필요하면 GENERATED BY DEFAULT로 시작해 삽입 후 setval로 동기화한다. 여러 테이블이 공유하는 채번이 필요하면 독립 SEQUENCE를 활용한다. 시퀀스 갭은 정상 동작이며, 연속 채번 보장이 필요하면 별도 설계가 필요하다.

---

지난 글: 사용자 정의 타입과 도메인 — CREATE TYPE, CREATE DOMAIN

다음 글: 테이블 상속 — INHERITS와 파티셔닝의 뿌리

읽어주셔서 감사합니다. 😊