PostgreSQL 테이블 생성 심화 - CTAS, 파티셔닝, 제약조건 완벽 가이드

CREATE TABLE은 가장 기본적인 DDL이지만, 제대로 활용하면 성능과 유지보수에 큰 차이가 난다. 기본 문법은 이미 알고 있다고 가정하고, 실무에서 자주 쓰는 고급 기능들을 정리한다.

목차

1. CREATE TABLE IF NOT EXISTS
2. CREATE TABLE AS SELECT (CTAS)
3. 제약조건 심화
4. 테이블 상속 (INHERITS)
5. 파티셔닝 (PARTITION BY)
6. 임시 테이블 (TEMPORARY)
7. UNLOGGED 테이블
8. LIKE 절로 테이블 복사

---

1. CREATE TABLE IF NOT EXISTS

테이블이 이미 있으면 에러 없이 넘어간다. 스크립트 여러 번 실행할 때 유용하다.

-- 테이블이 있으면 에러 발생
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);
-- ERROR: relation "users" already exists

-- 테이블이 있으면 무시
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);
-- NOTICE: relation "users" already exists, skipping

마이그레이션 스크립트나 초기화 스크립트에서 자주 쓴다.

2. CREATE TABLE AS SELECT (CTAS)

SELECT 결과로 테이블을 바로 생성한다. 데이터 백업이나 임시 테이블 만들 때 유용하다.

기본 사용법

-- 기존 테이블 데이터로 새 테이블 생성
CREATE TABLE users_backup AS
SELECT * FROM users;

-- 특정 컬럼만 선택
CREATE TABLE user_names AS
SELECT id, name FROM users;

-- 조건 추가
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

데이터 없이 구조만 복사

-- WHERE 1=0 으로 데이터 없이 구조만 복사
CREATE TABLE users_empty AS
SELECT * FROM users WHERE 1=0;

-- 또는 WITH NO DATA 사용 (PostgreSQL 9.0+)
CREATE TABLE users_empty AS
SELECT * FROM users WITH NO DATA;

주의사항

CTAS로 만든 테이블은 제약조건이 복사되지 않는다.

-- 원본 테이블
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- CTAS로 복사
CREATE TABLE users_copy AS SELECT * FROM users;

-- 제약조건 확인
\d users_copy
-- PRIMARY KEY, UNIQUE, NOT NULL 모두 없음

제약조건이 필요하면 LIKE 절을 사용하거나 별도로 추가해야 한다.

3. 제약조건 심화

NOT NULL + DEFAULT 조합

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

CHECK 제약조건

값의 범위나 조건을 지정한다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INT NOT NULL CHECK (price > 0),
    discount_rate DECIMAL(3,2) CHECK (discount_rate >= 0 AND discount_rate <= 1),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'deleted'))
);

복합 UNIQUE 제약조건

여러 컬럼의 조합이 유일해야 할 때

CREATE TABLE user_roles (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    UNIQUE (user_id, role_id)  -- user_id + role_id 조합이 유일
);

-- 같은 유저에게 같은 역할 중복 불가
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1);
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1);  -- ERROR
INSERT INTO user_roles (user_id, role_id) VALUES (1, 2);  -- OK (다른 역할)

외래키 (FOREIGN KEY)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    product_id INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

ON DELETE 옵션

• CASCADE: 부모 삭제 시 자식도 삭제
• SET NULL: 부모 삭제 시 NULL로 변경
• SET DEFAULT: 부모 삭제 시 DEFAULT 값으로 변경
• RESTRICT: 자식이 있으면 부모 삭제 불가 (기본값)

4. 테이블 상속 (INHERITS)

부모 테이블의 구조를 상속받는다. PostgreSQL 고유 기능이다.

-- 부모 테이블
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 자식 테이블 (부모 컬럼 + 추가 컬럼)
CREATE TABLE error_logs (
    error_code VARCHAR(10),
    stack_trace TEXT
) INHERITS (logs);

CREATE TABLE access_logs (
    ip_address INET,
    user_agent TEXT
) INHERITS (logs);

상속 테이블 조회

-- 부모 테이블 조회 시 자식 데이터도 함께 조회됨
SELECT * FROM logs;

-- 부모 테이블만 조회 (ONLY 키워드)
SELECT * FROM ONLY logs;

-- 자식 테이블만 조회
SELECT * FROM error_logs;

주의: 상속은 파티셔닝 이전에 쓰던 방식이다. PostgreSQL 10 이후로는 선언적 파티셔닝을 쓰는 게 좋다.

5. 파티셔닝 (PARTITION BY)

대용량 테이블을 작은 단위로 나눈다. PostgreSQL 10부터 선언적 파티셔닝을 지원한다.

RANGE 파티셔닝 (범위 기준)

날짜별로 나누는 경우가 가장 많다.

-- 부모 테이블 (파티션 정의)
CREATE TABLE logs (
    id SERIAL,
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 파티션 테이블 생성
CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE logs_2024_03 PARTITION OF logs
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

LIST 파티셔닝 (값 기준)

특정 값별로 나눌 때 사용한다.

CREATE TABLE orders (
    id SERIAL,
    region VARCHAR(20) NOT NULL,
    amount INT
) PARTITION BY LIST (region);

CREATE TABLE orders_seoul PARTITION OF orders
    FOR VALUES IN ('seoul', 'incheon', 'gyeonggi');

CREATE TABLE orders_busan PARTITION OF orders
    FOR VALUES IN ('busan', 'ulsan', 'gyeongnam');

CREATE TABLE orders_etc PARTITION OF orders
    DEFAULT;  -- 나머지 값들

HASH 파티셔닝 (해시 기준)

데이터를 균등하게 분산시킬 때 사용한다.

CREATE TABLE user_activities (
    id SERIAL,
    user_id INT NOT NULL,
    activity TEXT
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_activities_1 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_activities_2 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_activities_3 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

파티셔닝 장점

• 오래된 데이터 삭제가 빠름 (DROP PARTITION)
• 특정 파티션만 스캔해서 성능 향상
• 파티션별로 다른 테이블스페이스 사용 가능

6. 임시 테이블 (TEMPORARY)

세션이 끝나면 자동으로 삭제된다. 복잡한 쿼리의 중간 결과 저장할 때 유용하다.

-- 임시 테이블 생성
CREATE TEMPORARY TABLE temp_results (
    id INT,
    value TEXT
);

-- 또는 줄여서
CREATE TEMP TABLE temp_results (
    id INT,
    value TEXT
);

-- 세션 종료 시 자동 삭제
-- 다른 세션에서는 보이지 않음

ON COMMIT 옵션

트랜잭션 단위로 데이터 관리

-- 트랜잭션 끝나도 데이터 유지 (기본값)
CREATE TEMP TABLE temp1 (id INT) ON COMMIT PRESERVE ROWS;

-- 트랜잭션 끝나면 데이터 삭제 (테이블 구조는 유지)
CREATE TEMP TABLE temp2 (id INT) ON COMMIT DELETE ROWS;

-- 트랜잭션 끝나면 테이블 자체 삭제
CREATE TEMP TABLE temp3 (id INT) ON COMMIT DROP;

7. UNLOGGED 테이블

WAL(Write-Ahead Log)을 쓰지 않아서 빠르다. 서버 장애 시 데이터가 사라질 수 있다.

CREATE UNLOGGED TABLE cache_data (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT,
    expires_at TIMESTAMP
);

사용 케이스

• 캐시 테이블
• 임시 작업용 테이블
• 언제든 재생성 가능한 데이터

성능 비교

10만 건 INSERT 기준

테이블 타입 소요시간

일반 테이블	3.2초
UNLOGGED 테이블	0.8초

약 4배 빠르다. 단, 복제(Replication)가 안 되고 장애 시 데이터 손실 가능.

8. LIKE 절로 테이블 복사

기존 테이블 구조를 복사한다. CTAS와 달리 제약조건도 복사할 수 있다.

-- 기본 (컬럼명, 타입, NOT NULL만 복사)
CREATE TABLE users_copy (LIKE users);

-- 모든 것 복사 (제약조건, 인덱스, DEFAULT 등)
CREATE TABLE users_copy (LIKE users INCLUDING ALL);

-- 선택적으로 복사
CREATE TABLE users_copy (
    LIKE users
    INCLUDING DEFAULTS
    INCLUDING CONSTRAINTS
    INCLUDING INDEXES
);

INCLUDING 옵션

• DEFAULTS: DEFAULT 값
• CONSTRAINTS: CHECK 제약조건
• INDEXES: 인덱스
• STORAGE: STORAGE 설정
• COMMENTS: 코멘트
• GENERATED: GENERATED 컬럼
• ALL: 위 모든 것

실무 팁

1. 테이블 생성 전 체크리스트

-- 1. 테이블명 컨벤션 (복수형 권장)
-- users, orders, products (O)
-- user, order, product (X)

-- 2. 컬럼 순서 (자주 조회하는 컬럼을 앞에)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,      -- 자주 조회
    status VARCHAR(20),         -- 자주 조회
    total_amount INT,
    created_at TIMESTAMP,
    -- ... 나머지 컬럼
    description TEXT            -- 큰 컬럼은 뒤에
);

-- 3. 적절한 데이터 타입 선택
-- INT vs BIGINT (20억 넘을 것 같으면 BIGINT)
-- VARCHAR(n) vs TEXT (길이 제한 필요하면 VARCHAR)
-- TIMESTAMP vs TIMESTAMPTZ (타임존 필요하면 TIMESTAMPTZ)

2. 대량 데이터 테이블은 처음부터 파티셔닝

-- 나중에 파티셔닝 전환은 어렵다
-- 처음부터 파티셔닝으로 설계하자

CREATE TABLE logs (
    id BIGSERIAL,
    message TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);

-- 매월 파티션 자동 생성 스크립트 준비

3. 개발/운영 환경 분리

-- 개발 환경: 빠른 테스트용
CREATE UNLOGGED TABLE dev_test_data (...);

-- 운영 환경: 안전한 일반 테이블
CREATE TABLE prod_data (...);

정리

기능 용도

IF NOT EXISTS	스크립트 반복 실행
CTAS	데이터 백업, 임시 테이블
LIKE INCLUDING ALL	구조 + 제약조건 복사
PARTITION BY	대용량 테이블 분할
TEMPORARY	세션 내 임시 데이터
UNLOGGED	빠른 쓰기 (캐시용)

다음 글에서는 PostgreSQL IN 절 성능 최적화에 대해 정리한다.