DDL과 인덱스 — 테이블 설계와 성능 최적화

CREATE TABLE 상세

CREATE TABLE products (
    -- 기본키
    id          SERIAL PRIMARY KEY,

    -- NOT NULL: 반드시 값 있어야 함
    name        VARCHAR(200) NOT NULL,

    -- UNIQUE: 중복 불가
    sku         VARCHAR(50) UNIQUE NOT NULL,

    -- DEFAULT: 기본값
    price       INTEGER NOT NULL DEFAULT 0,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,

    -- CHECK: 조건 제약
    stock       INTEGER NOT NULL DEFAULT 0
                CHECK (stock >= 0),
    rating      DECIMAL(2,1) CHECK (rating >= 0 AND rating <= 5),

    -- 외래키
    category_id INTEGER REFERENCES categories(id)
                ON DELETE SET NULL,   -- 카테고리 삭제 시 NULL로

    -- 타임스탬프
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

---

데이터 타입

타입	예시	설명
INTEGER / INT	42	정수
BIGINT	9999999999	큰 정수
SERIAL	-	자동 증가 정수
DECIMAL(p,s)	9.99	정확한 소수
FLOAT	3.14	부동소수점
VARCHAR(n)	"철수"	가변 길이 문자열
TEXT	긴 텍스트	길이 제한 없음
BOOLEAN	TRUE/FALSE	참/거짓
DATE	2026-04-21	날짜
TIMESTAMP	2026-04-21 14:00:00	날짜+시간
JSONB	키·값 쌍 (예: 문자열 키와 값)	JSON (PostgreSQL)
UUID	550e8400-...	고유 식별자

---

ALTER TABLE: 테이블 수정

-- 열 추가
ALTER TABLE products ADD COLUMN description TEXT;

-- 열 삭제
ALTER TABLE products DROP COLUMN description;

-- 열 타입 변경
ALTER TABLE products ALTER COLUMN price TYPE BIGINT;

-- 제약 추가
ALTER TABLE products ADD CONSTRAINT check_price CHECK (price >= 0);

-- 제약 삭제
ALTER TABLE products DROP CONSTRAINT check_price;

-- 열 이름 변경
ALTER TABLE products RENAME COLUMN sku TO product_code;

-- 테이블 이름 변경
ALTER TABLE products RENAME TO items;

---

인덱스: 검색 속도 향상

인덱스가 없으면 조건에 맞는 행을 찾기 위해 전체 테이블을 읽는 경우가 많고, 적절한 인덱스가 있으면 인덱스만 따라가 원하는 행을 빠르게 찾을 수 있습니다.

구분	인덱스 없음	인덱스 있음
탐색 방식	전체 테이블 스캔 (Full Table Scan)	인덱스 스캔 (Index Scan 등)
대략적 특성	테이블이 크면 행마다 확인	검색·범위 조건에 유리하면 로그 시간에 가깝게

-- 단일 열 인덱스
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_employees_department ON employees(department);

-- 복합 인덱스 (자주 함께 쓰는 열)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 인덱스 목록 확인
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products';

-- 인덱스 삭제
DROP INDEX idx_products_category;

---

EXPLAIN: 실행 계획 확인

-- 어떻게 쿼리가 실행되는지 확인
EXPLAIN SELECT * FROM employees WHERE department = '개발팀';

-- 실제 실행 통계 포함
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = '개발팀';

-- 결과 예시
-- Seq Scan on employees (cost=0.00..1.06 rows=3 width=68)
-- Filter: ((department)::text = '개발팀'::text)
-- → 인덱스 없으면 Seq Scan (전체 탐색)

-- 인덱스 추가 후
-- Index Scan using idx_employees_department on employees
-- → Index Scan으로 개선됨

---

뷰 (View)

자주 쓰는 복잡한 쿼리를 저장합니다.

-- 뷰 생성
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE is_active = TRUE;

-- 테이블처럼 사용
SELECT * FROM active_employees WHERE department = '개발팀';

-- 뷰 삭제
DROP VIEW active_employees;

---

정리

DDL	역할
CREATE TABLE	테이블 생성
ALTER TABLE	테이블 구조 변경
DROP TABLE	테이블 삭제
CREATE INDEX	인덱스 생성
CREATE VIEW	뷰 생성

인덱스를 만들어야 할 때:

• WHERE 절에 자주 등장하는 열
• JOIN 연결 열 (외래키)
• ORDER BY에 자주 쓰는 열

다음 편에서는 SQL 실전 패턴 — 실제 앱 개발에서 자주 만나는 쿼리 패턴을 배웁니다.