DDL / DML / DCL / TCL 차이 이해
1. DDL (Data Definition Language)
“구조를 만드는 방식”의 차이
DDL이란?
- 테이블 생성
- 컬럼 추가/삭제
- 인덱스 생성
- 스키마 구조 변경
즉, 데이터를 담을 그릇을 만드는 작업
1-1. ID(Primary Key) 생성 방식 차이
MySQL 방식
id BIGINT AUTO_INCREMENT PRIMARY KEY- 테이블 안에 “자동 증가” 속성이 포함됨
- 편리하지만 내부 동작은 감춰져 있음
PostgreSQL 방식
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY- 내부적으로 SEQUENCE 객체를 사용
- ID 생성 로직이 명확하게 분리
PostgreSQL은 “ID 생성도 하나의 객체”로 취급
왜 이게 중요한가?
- MySQL: “알아서 해줘서 편함”
- PostgreSQL: “어디서 값이 생기는지 명확함”
운영/문제 분석 시 PostgreSQL이 원인 파악이 쉬움
1-2. ENUM 처리 철학 차이
MySQL
status ENUM('ACTIVE','INACTIVE')- 컬럼에 값 목록이 박힘
- 값 변경 시 테이블 구조 변경 필요
PostgreSQL
status TEXT CHECK (status IN ('ACTIVE','INACTIVE'))- 타입은 TEXT
- 제약조건으로 값 범위를 관리
“데이터 타입”과 “업무 규칙”을 분리
왜 PostgreSQL이 이렇게 했을까?
- 상태 값은 비즈니스 규칙
- 비즈니스 규칙은 변경 가능
- 그래서 DDL로 명확하게 관리
1-3. DDL 트랜잭션 (PostgreSQL의 결정적 차이)
PostgreSQL
BEGIN;
CREATE TABLE test (...);
ROLLBACK;➡️ 테이블 생성이 완전히 취소됨
MySQL
- DDL은 자동 커밋
- ROLLBACK 불가하거나 제한적
운영 관점에서 의미
- PostgreSQL:
- 배포 중 실수 → 즉시 원복
- MySQL:
- 배포 실수 → 백업 복구 필요
운영 안정성에서 큰 차이
2. DML (Data Manipulation Language)
“데이터를 다루는 방식”의 차이
2-1. INSERT 후 생성된 값 받기
MySQL
INSERT INTO users(name) VALUES ('kim');
SELECT LAST_INSERT_ID();- 두 단계
- 세션 의존
PostgreSQL
INSERT INTO users(name)
VALUES ('kim')
RETURNING id;- 한 문장
- 명확하고 안전
2-2. UPDATE + JOIN 차이
MySQL
UPDATE orders o
JOIN customers c ON ...
SET o.status = 'X';PostgreSQL
UPDATE orders o
SET status = 'X'
FROM customers c
WHERE o.customer_id = c.customer_id;PostgreSQL은 표준 SQL에 더 가까운 구조
왜 이게 중요한가?
- 운영 중 데이터 정합성 보정
- 대량 데이터 수정
- 사고 복구 스크립트
PostgreSQL이 운영 SQL 작성에 유리
3. DCL (Data Control Language)
“권한과 보안 철학”의 차이
3-1. 권한 단위 차이
MySQL
GRANT ALL ON shop.* TO 'app'@'%';- DB 단위
- 과거 + 미래 테이블 자동 포함
- 편하지만 범위가 큼
PostgreSQL
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO app;
# 1) `GRANT CONNECT ON DATABASE shop TO app;`
* `app` 롤이 shop 데이터베이스에 접속할 수 있는 권한을 부여함.
* PostgreSQL은 세션당 DB 1개이므로, CONNECT가 없으면 아예 접속이 막힘.
## 없으면 어떤 일이 생기나
psql -h localhost -U app -d shop
결과:
* `FATAL: permission denied for database "shop"`
# 2) `GRANT USAGE ON SCHEMA public TO app;`
* `app`이 `public` 스키마 안의 객체를 “볼 수/참조할 수” 있게 해줌.
* 스키마 권한의 `USAGE`는 디렉터리(폴더) 접근 권한에 비유하면 됨.
* 폴더에 들어갈 수 있어야 그 안의 파일(테이블)을 읽을 수 있음
## 없으면 어떤 일이 생기나
* 테이블에 SELECT 권한을 줬더라도, 스키마 USAGE가 없으면 접근이 막힐 수 있음.
* 전형적인 에러:
* `ERROR: permission denied for schema public`
## 주의
* `USAGE`는 테이블을 읽게 해주는 권한이 아님.
* 단지 스키마 경로 접근(네임스페이스 접근)임. 실제 테이블 권한은 별도로 필요함.
# 3) `GRANT ALL ON ALL TABLES IN SCHEMA public TO app;`
* 현재 시점에서 `public` 스키마에 이미 존재하는 모든 테이블에 대해 `app`에게 권한을 부여함.
* `ALL`은 테이블에 대해 보통 아래 권한 세트를 의미함:
`SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER`
> 즉, “지금 존재하는 테이블은 app이 다 쓸 수 있게 하겠다.”
## 없으면 어떤 일이 생기나
* app이 shop DB에 접속은 되는데 테이블 조회가 안 됨
* `ERROR: permission denied for relation customers`
# 4) `ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO app;`
* 앞으로 `public` 스키마에서 새로 생성될 테이블에 대해,
* 기본 권한(default ACL)을 바꿔서 `app`에게 자동으로 권한을 주도록 설정함.
> 즉, “미래에 만들어질 테이블도 app이 자동으로 쓸 수 있게 하겠음.”
## 없으면 어떤 일이 생기나 (가장 흔한 운영 장애)
* 배포/마이그레이션 과정에서 새 테이블이 생김
* 기존 테이블은 잘 되는데 새 테이블만 permission denied
* `ERROR: permission denied for relation new_table`
## 가장 중요한 함정: “누가 테이블을 만들었는가(소유자) 기준”
`ALTER DEFAULT PRIVILEGES`는 ‘명령을 실행한 롤(소유자 역할)’이 앞으로 만들 객체에만 적용됨.
예를 들어,
* `postgres`로 위 명령을 실행했다면
→ postgres가 생성하는 테이블에만 app 권한이 자동 적용
* 그런데 실제 배포에서 `migrator`라는 계정이 테이블을 만들면
→ postgres의 default privileges가 적용되지 않습니다
# 실습
## 1) app으로 접속 가능한지
psql -U app -d shop -h localhost
## 2) 테이블 목록 보이는지 (스키마 USAGE 체크)
\\dt public.*
## 3) 기존 테이블 SELECT 되는지
SELECT COUNT(*) FROM customers;
## 4) “새 테이블”이 자동으로 되는지 (default privileges 체크)
관리자(테이블 생성자)로:
CREATE TABLE public.priv_check (id int);
app으로:
SELECT * FROM public.priv_check;- DB → Schema → Table
- 모두 명시적으로 설정
PostgreSQL이 이렇게 귀찮은 이유
“권한은 명확해야 사고가 안 난다”
- 실수로 모든 테이블 접근 ❌
- 마이그레이션 계정 / 앱 계정 분리 ⭕
4. TCL (Transaction Control Language)
“실수를 되돌릴 수 있는 범위”의 차이
PostgreSQL
- DML + DDL 모두 트랜잭션
- 실패 시 전체 롤백
MySQL
- DML은 트랜잭션
- DDL은 자동 커밋
운영에서의 차이
- PostgreSQL: 실수해도 되돌릴 수 있음
- MySQL: 실수하면 복구 작업 필요
5. 인프라 엔지니어 관점 결론
MySQL이 어울리는 경우
- 빠른 개발
- 단순 서비스
- 운영 복잡도 낮음
PostgreSQL이 어울리는 경우
- 장기 운영
- 권한/보안 중요
- 멀티테넌트
- 운영 사고 최소화