ALTER, DROP, TRUNCATE

ALTER: 구조 변경(데이터 유지)

하지만 lock/rewrite 발생 가능

DROP: 객체 삭제(데이터/구조 모두 삭제)

가장 위험

TRUNCATE: 데이터만 전체 삭제(구조 유지, 빠름, 시퀀스 옵션 가능)

FK/IDENTITY 옵션 중요

1) ALTER

이미 존재하는 객체의 구조를 바꾸는 명령
ALTER는 단순 문법이 아니라 락 / 테이블 리라이트 / 성능 / 서비스 중단과 직결된다.

대상:

TABLE
COLUMN
CONSTRAINT
INDEX
SEQUENCE
SCHEMA
VIEW

1-1) 컬럼 추가

ALTER TABLE users ADD COLUMN phone varchar(20);

metadata 변경
기존에 있던 row는 NULL 값으로 취급
LOCK: 짧은 ACCESS EXCLUSIVE lock

DEFAULT와 함께 추가할 때

ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

최신 버전: metadata만 변경
과거 버전: 전체 테이블 rewrite 발생
rewrite 발생 시:
- 대형 테이블 → 몇 분~수십 분
- 서비스 중단 가능성

1-2) 컬럼 삭제

ALTER TABLE users DROP COLUMN phone;

metadata 삭제
물리 데이터는 vacuum(가비지 컬렉션과 유사) 시 정리
FK/인덱스/뷰 의존성 있으면 실패

1-3) 타입 변경 (위험)

ALTER TABLE users
ALTER COLUMN email TYPE varchar(255);

타입 변경이 위험한 이유

데이터 변환 필요
전체 테이블 rewrite 발생 가능 (대형 테이블에서 가장 위험한 ALTER 중 하나)
ACCESS EXCLUSIVE lock

USING 절 (고급): 데이터 변환 방식을 직접 지정.

ALTER TABLE users
ALTER COLUMN age TYPE bigint
USING age::bigint;

1-4) DEFAULT 설정(낮은버전 - 위험) / 해제

ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();
ALTER TABLE users ALTER COLUMN created_at DROP DEFAULT;

metadata 변경
기존 데이터 영향 없음
낮은 버전의 경우 전체를 rewirte할 수 있다.

1-5) NOT NULL 설정(위험) / 해제

ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users ALTER COLUMN age DROP NOT NULL;

SET NOT NULL 내부 동작

DB는 전체 테이블 스캔 → NULL 존재 여부 검사 → NULL 하나라도 있으면 실패한다.
그래서 UPDATE → NULL 제거 → SET NOT NULL 순으로 진행한다.

1-6) 컬럼 이름 변경

ALTER TABLE users RENAME COLUMN email TO user_email;

metadata만 변경
데이터 이동 없음

1-7) 테이블 이름 변경

ALTER TABLE users RENAME TO app_users;

1-8) 제약조건 추가/삭제

ALTER TABLE users
ADD CONSTRAINT users_age_check CHECK (age >= 0);

ALTER TABLE users
DROP CONSTRAINT users_age_check;

UNIQUE / FK 추가도 ALTER로 한다

ALTER TABLE users
ADD CONSTRAINT users_email_uk UNIQUE (email);

ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id);

1-9) PRIMARY KEY 추가(위험)/변경

ALTER TABLE users
ADD PRIMARY KEY (id);

유니크 인덱스 생성됨
전체 스캔 발생

1-10) 인덱스 관련 ALTER

인덱스 생성 (위험)

CREATE INDEX idx_users_email ON users(email);

ALTER가 아닌 이유는 인덱스는 독립 객체이기 때문이다.
락 발생할 수 있음.

CONCURRENTLY (핵심)

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

쓰기 중단 없이 인덱스 생성

인덱스 삭제

DROP INDEX idx_users_email;

1-11) SEQUENCE 변경

IDENTITY / SERIAL 관련.

ALTER SEQUENCE users_id_seq RESTART WITH 1000;

1-12) OWNER 변경

ALTER TABLE users OWNER TO app_user;

1-13) SCHEMA 이동

ALTER TABLE users SET SCHEMA app;

1-14) TABLE 옵션 변경

ALTER TABLE users SET (autovacuum_enabled = false);

2) DROP

테이블/인덱스/뷰/스키마 같은 객체 자체를 삭제
데이터는 완전 삭제(metadata 삭제 + 디스크 삭제)
트랜잭션으로 되돌릴 수는 있지만(DDL도 트랜잭션 대상), 운영에선 거의 “되돌릴 수 없는 버튼”처럼 취급함(사람/권한/배포 파이프라인 문제)

자주 쓰는 패턴

DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_users_email;

존재하지 않을 경우에도 에러가 발생하지 않도록 항상 'IF EXISTS'를 붙이는 게 좋다.

CASCADE (위험)

DROP TABLE users CASCADE;

의존 객체(FK, VIEW, INDEX, TRIGGER)를 함께 삭제
영향 범위가 커서 사고 위험이 매우 큼
운영에선 삭제될 대상 목록을 확인하고 확신이 있을 때만 사용

3) TRUNCATE

테이블 구조는 유지
데이터만 전부 제거(테이블 페이지 초기화)
DELETE보다 훨씬 빠른 경우가 많음(대량 데이터에서 특히)
WHERE 조건 없음(전체 삭제만)

자주 쓰는 패턴

TRUNCATE logs;

시퀀스(IDENTITY/BIGSERIAL)까지 초기화

TRUNCATE logs RESTART IDENTITY;

DELETE나 TRUNCATE는 시퀀스가 유지돼서 새로운 id가 1로 시작하지 않는다. 이때 사용한다.

FK 연관 테이블까지 함께 비우기

TRUNCATE parent_table CASCADE;

연관된 테이블까지 전부 비움 (위험)

ALTER

1. 컬럼 추가 + 기본값 채우기

users에 is_active BOOLEAN 컬럼을 추가한다.
기존 데이터는 모두 true로 채우고, 이후 신규 row의 기본값도 true가 되게 한다.
추가로 is_active는 NOT NULL이어야 한다.

ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

기존 row → 자동으로 true 채워짐
신규 row → default true
NOT NULL 조건 충족

훨씬 안전한 버전:

ALTER TABLE users ADD COLUMN is_active BOOLEAN;
UPDATE users SET is_active = true;
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;
ALTER TABLE users ALTER COLUMN is_active SET NOT NULL;

컬럼 추가 시 전체 rewrite 방지
락 최소화
마이그레이션 안정성

2. 컬럼 이름 변경 + 타입 변경

users.username 컬럼명을 display_name으로 변경한 뒤, 타입을 VARCHAR(50)으로 바꿔라.

ALTER TABLE users RENAME COLUMN username TO display_name;
ALTER TABLE users 
ALTER COLUMN display_name TYPE VARCHAR(50);

VARCHAR(30) → VARCHAR(50)는 문제가 없지만, 문자형에서 숫자형 같이 타입 변환이 실패할 수 있다.

3. UNIQUE 제약 “이름을 지정해서” 추가

users에 phone 컬럼이 이미 존재한다고 가정한다.
phone에 대해 중복을 금지하는 UNIQUE 제약을 users_phone_uk 이름으로 추가하라.
(단, phone은 NULL 허용)

ALTER TABLE users
ADD CONSTRAINT users_phone_uk UNIQUE (phone);

UNIQUE는 NULL을 각각 다르게 받아들이기 때문에 NULL 허용을 따로 할 필요가 없다.

4. CHECK 제약 추가 전에 데이터 정리

orders.total_price에 음수 값이 존재할 수 있다.
음수는 전부 0으로 바꾼 뒤, total_price >= 0 CHECK 제약을 orders_total_price_ck 이름으로 추가하라.

UPDATE orders
SET total_price = 0
WHERE total_price < 0;

ALTER TABLE orders
ADD CONSTRAINT orders_total_price_ck
CHECK (total_price >= 0);

음수가 있으면 에러가 발생하므로 항상 UPDATE → CONSTRAINT 추가가 운영에 안전한 패턴이다.

대용량 테이블이면,

ALTER TABLE orders
ADD CONSTRAINT orders_total_price_ck
CHECK (total_price >= 0) NOT VALID;

ALTER TABLE orders
VALIDATE CONSTRAINT orders_total_price_ck;

NOT VALID 키워드로 일단 추가만 하고
VALIDATE CONSTRAINT로 제약조건을 확인한다. 검사 중에는 읽기는 가능하다.

5. FK 추가 (기존 데이터가 이미 있음)

orders.user_id는 이미 존재하지만 FK가 없다.
orders.user_id에 FK를 추가해서 users(id)를 참조하게 만들고,
삭제 동작은 ON DELETE RESTRICT로 한다.
제약 이름은 orders_user_id_fk.

추가 질문: FK 추가 후 성능을 위해 보통 뭘 하나 더 하냐?

ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fk
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE RESTRICT;

성능을 위해 인덱스를 추가해준다.

CREATE INDEX idx_orders_user_id ON orders(user_id);

TRUNCATE

1. TRUNCATE + 시퀀스 초기화

logs 테이블을 비우고 id를 1부터 다시 시작시켜라. (1줄)

TRUNCATE logs RESTART IDENTITY;

2. FK 때문에 TRUNCATE가 막히는 상황

orders가 order_items에 의해 FK로 참조되고 있다.
orders를 비우면서 관련 테이블도 함께 비우되, 시퀀스도 초기화해라. (1줄)

TRUNCATE orders RESTART IDENTITY CASCADE;

순서도 지켜야한다. CASCADE 먼저쓰면 안된다.
TRUNCATE 테이블 → RESTART IDENTITY → CASCADE

DROP

1. 인덱스 안전 삭제

idx_orders_user_id 인덱스를 “있으면 삭제, 없으면 무시”하라. (1줄)

DROP INDEX IF EXISTS idx_orders_user_id;

2. 테이블 삭제 + 의존성은 남기고 싶다?

users를 지우고 싶은데, reviews 테이블은 지우면 안 된다.
이 상황에서 DROP TABLE users CASCADE;가 위험한 이유를 한 문장으로 설명하고, 대신 어떤 접근(고수준 전략)을 택해야 하는지 한 문장으로 적어라.

FK, VIEW, FUNCTION 등 의존 객체가 연쇄적으로 삭제되면서 서비스 데이터와 무결성이 함께 사라질 수 있기 때문이다.
CASCADE는 쓰는 게 아니라 의존성을 먼저 정리하고 DROP하는 게 원칙이다