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

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()
);

-- 열 추가
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;

-- 단일 열 인덱스
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 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으로 개선됨

-- 뷰 생성
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;