INSERT, UPDATE, DELETE

1) DML

이미 존재하는 테이블의 데이터(row) 를 변경하는 명령

DDL은 구조를 다루고 DML은 데이터를 다룬다. DML은 구조는 건드리지 않는다.

SELECT → 데이터 조회
INSERT → 데이터 추가
UPDATE → 데이터 수정
DELETE → 데이터 제거

2) INSERT

기본 형태

INSERT INTO users (name, email)
VALUES ('kim', 'kim@test.com');

INSERT는 단순 “쓰기”가 아니라 무결성 검사 + 인덱스 변경 + 로그 기록 작업이다.

INSERT는 생각보다 많은 작업을 한다.

타입 검사
NOT NULL 검사
CHECK 검사
FK 검사
DEFAULT 적용
IDENTITY/SEQUENCE 값 생성
인덱스 업데이트
WAL 기록

여러 row INSERT

INSERT INTO users (name, email)
VALUES
  ('kim', 'a@test.com'),
  ('lee', 'b@test.com');

1 row씩 INSERT → 느림
batch INSERT → 훨씬 빠름

DEFAULT VALUES

INSERT INTO users DEFAULT VALUES;

모든 컬럼이 DEFAULT로 채워짐.

RETURNING (PostgreSQL 핵심)

INSERT INTO users (name)
VALUES ('kim')
RETURNING id;

생성된 PK를 바로 반환
백엔드에서 매우 많이 사용

3) UPDATE

기본 형태

UPDATE users
SET email = 'new@test.com'
WHERE id = 1;

UPDATE는 “값 덮어쓰기”가 아니다.

PostgreSQL은 MVCC 구조라 기존 row를 수정하는 게 아니라 새 row를 생성하고 기존 row를 “죽은 row”로 표시된다.

UPDATE = INSERT + DELETE
그래서 인덱스 재작성, vacuum(GC와 유사) 필요

WHERE 없는 UPDATE = 전체 변경

UPDATE users SET status = 'active';

운영 시 사고 1위 유형.

여러 컬럼 변경

UPDATE users
SET email = 'a@test.com',
    age = 20
WHERE id = 1;

계산 업데이트

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

금융/재고 시스템 핵심 패턴.

FROM 절 UPDATE (Postgres 특화)

UPDATE orders o
SET status = 'paid'
FROM payments p
WHERE o.id = p.order_id;

JOIN 기반 업데이트

4) DELETE

기본 형태

DELETE FROM users
WHERE id = 1;

Postgres에서 DELETE는 물리 삭제가 아니다. row를 “dead”로 표시한다. 그리고 vacuum이 실제 제거를 담당한다.

WHERE 없는 DELETE = 전체 삭제

DELETE FROM users;

row 단위 삭제
TRUNCATE보다 느림

FK 영향

DELETE FROM users WHERE id = 1;

FK 있으면:

RESTRICT → 실패
CASCADE → 연쇄 삭제

5) UPSERT

PostgreSQL 핵심 기능으로 매우 중요하다.

INSERT + UPDATE 합친 것

INSERT INTO users (email, name)
VALUES ('a@test.com', 'kim')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

email 중복이면 UPDATE
아니면 INSERT

DO NOTHING = 중복되면 무시

ON CONFLICT (email) -- email이 이미 존재하면 (갈등)
DO NOTHING; -- 아무것도 하지말고 넘어가라

UNIQUE 인덱스를 기준으로 충돌 검사
충돌 → UPDATE
없음 → INSERT

6) RETURNING

DML 공통 기능으로 INSERT/UPDATE/DELETE 후 값을 바로 반환한다.

UPDATE users
SET status = 'active'
WHERE id = 1
RETURNING *;

7) 트랜잭션과 DML

기본 흐름: BEGIN → INSERT → UPDATE → DELETE → COMMIT

-- 1. 트랜잭션 시작 (이제부터 모든 작업은 한 묶음!)
BEGIN;

-- 2. INSERT: 거래 이력 생성 (이미 같은 ID의 요청이 있으면 무시)
INSERT INTO transaction_log (id, sender, receiver, amount)
VALUES (101, 'User_A', 'User_B', 5000)
ON CONFLICT (id) DO NOTHING;

-- 3. UPDATE: 보내는 사람 잔액 차감
UPDATE accounts 
SET balance = balance - 5000 
WHERE user_name = 'User_A' AND balance >= 5000;

-- 4. DELETE: 혹시 만료된 일회용 쿠폰이 있다면 삭제
DELETE FROM coupons 
WHERE user_name = 'User_A' AND coupon_type = 'TRANSFER_FREE';

-- 5. COMMIT: 모든 작업이 완벽하면 한 번에 실제 DB에 반영!
-- (만약 중간에 에러가 났다면 COMMIT 대신 ROLLBACK; 을 실행해야 함)
COMMIT;

ROLLBACK = 삭제 취소

BEGIN
DELETE FROM users;
ROLLBACK;

DML은 항상 트랜잭션 안에서 의미를 가진다.

특히:

UPDATE 충돌
DELETE lock
동시성

이 전부 트랜잭션 문제로 이어짐.

8) DML에서 실제로 걸리는 Lock (INSERT / UPDATE / DELETE)

PostgreSQL은 “테이블 전체를 잠그는 DB”가 아니라 row 단위 lock + 최소한의 table lock 구조다.

INSERT

걸리는 lock:

대상 테이블: ROW EXCLUSIVE LOCK
삽입 row: row-level lock

영향:

다른 INSERT 가능
다른 UPDATE 가능
SELECT 전혀 영향 없음

즉, INSERT는 거의 충돌이 없는 작업이다. 단, UNIQUE 충돌 시 lock 대기 발생

UPDATE

UPDATE는 row를 수정하는 게 아니라 새 row 생성 + 기존 row 잠금

걸리는 lock:

수정 대상 row: row lock
대상 테이블: ROW EXCLUSIVE LOCK

문제 상황:

같은 row 동시에 UPDATE → 대기 발생

A: UPDATE users SET age=20 WHERE id=1;
B: UPDATE users SET age=30 WHERE id=1;

B는 A 끝날 때까지 기다림.

DELETE

삭제 대상 row lock
FK 검사 시 다른 테이블 lock 발생 가능

부모 row 삭제 명령이 들어오면 자식 FK 검사를 하게 된다. FK 검사 중 lock이 걸리므로 → 서비스 지연 발생

작업위험도

INSERT	낮음
UPDATE	중간
DELETE	높음
FK DELETE	매우 높음

9) MVCC (PostgreSQL 구조)

Postgres는 데이터를 덮어쓰지 않는다.
UPDATE 시에는 기존 row → 삭제 표시 후에 새 row → 생성
DELETE 시에는 row → 죽은 상태 표시
그래서 동시에 SELECT 가능하고, lock 충돌 적음
이걸 MVCC (Multi Version Concurrency Control)이라고 한다.

장점:

읽기 vs 쓰기 충돌 없음
SELECT 빠름

단점:

dead row(죽은 상태의 행) 누적
vacuum(dead row 처리) 필요

10) 트랜잭션 격리 수준 (Isolation Level)

READ COMMITTED (기본값)

일반 서비스에 적합하다.
다른 트랜잭션 commit 된 데이터만 읽음
대부분 서비스 기본값이다.
문제는 같은 SELECT 명령을 두 번 했을 때→ 값 다를 수 있음

REPEATABLE READ

금융/정산 등 오차가 없어야하는 서비스에 적합하다.
트랜잭션 시작 시 snapshot 고정 같은 SELECT 반복해도 동일 결과
메모리/버전 부담이 될 수 있다.

SERIALIZABLE: 거의 안 씀, 가장 엄격,성능 급락, 충돌 시 롤백

대부분 금융에서는 SERIALIZABLE 대신 SELECT ... FOR UPDATE(쓰기 잠금)를 사용.
트랜잭션이 종료(COMMIT/ROLLBACK)될 때까지 다른 세션이 해당 데이터를 수정하거나 삭제하지 못하게 차단

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

11) vacuum

MVCC 때문에 반드시 존재해야 하는 시스템이다.
Postgres에서 성능 문제의 절반은 vacuum 문제

dead row 제거
테이블 공간 회수
통계 업데이트

vacuum 없으면? UPDATE/DELETE 많을수록 테이블이 커지고 성능 급락하게 된다.

autovacuum

자동 실행 시스템으로 DB가 스스로 일정 기준 초과 시 vacuum 실행한다.

DML 문제

기본 환경:

users(id, display_name, email, age, created_at)
orders(id, user_id, total_price, status, created_at)
reviews(id, user_id, product_id, rating, content, created_at)

문제 1 신규 사용자 추가.

조건:

display_name: 'kim'
email: 'kim@test.com'
age: NULL 허용
created_at은 자동

INSERT INTO users (display_name, email, age)
VALUES ('kim', 'kim@test.com', NULL);

-- 쓰지 않는다.
INSERT INTO users VALUES
(DEFAULT, 'kim', 'kim@test.com', NULL, DEFAULT);

가능은 하지만, 테이블 구조를 정확히 알아야 해서, 거의 사용하지 않는다.

문제 2

조건:

email UNIQUE 제약 있음
이미 존재하면 에러 발생하지 말고 그냥 넘어가기

INSERT INTO users (display_name, email, age) 
VALUES ('kim', 'kim@test.com', NULL)
ON CONFLICT (email)
DO NOTHING;

가장 많이 쓰는 형태

중복이면 업데이트 하기 (많이 쓰는 확장형)

INSERT INTO users (display_name, email, age)
VALUES ('kim', 'kim@test.com', NULL)
ON CONFLICT (email)
DO UPDATE SET display_name = EXCLUDED.display_name;

문제 3 (INSERT + SELECT)

조건:

users 중 age가 NULL인 사람 전부 찾기
orders 테이블에 자동으로 주문 생성

값:

user_id → users.id
total_price = 0
status = 'WELCOME'
created_at = NOW()

INSERT INTO orders (user_id, total_price, status)
SELECT id, 0, 'WELCOME'
FROM users
WHERE age IS NULL;

문제 4 (안전 UPDATE)

age가 NULL인 사용자만 age = 0으로 변경.

조건:

실수로 전체 업데이트 안 되게 작성

먼저 SELECT으로 COUNT(*) 등 조건(WHERE 절)을 검증하고

SELECT id
FROM users
WHERE age IS NULL;

UPDATE를 진행하는 게 좋다.

UPDATE users
SET age = 0
WHERE age IS NULL;

문제 5 (조건 UPDATE)

orders.status가 'PENDING'이고
created_at이 오늘 이전인 것만 'CANCELLED'로 변경.

UPDATE orders
SET status = 'CANCELLED'
WHERE status = 'PENDING'
AND created_at < CURRENT_DATE;

CURRENT_DATE는 2026-02-10 00:00 이런 식으로 출력됨.

문제 6 (JOIN UPDATE)

users 중 리뷰를 한 번도 작성하지 않은 사람만
is_active = false로 변경.

SELECT u.id
FROM users u
WHERE NOT EXISTS (
	SELECT 1 FROM reviews r WHERE r.user_id = u.id
);

update가 안전한지 확인하고 update 진행

UPDATE users u
SET is_active = FALSE
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.user_id = u.id
);

또는

UPDATE users u
SET is_active = FALSE
FROM (
    SELECT u2.id
    FROM users u2
    LEFT JOIN reviews r ON r.user_id = u2.id
    WHERE r.user_id IS NULL
) t
WHERE u.id = t.id;

문제 7 (안전 DELETE)

age가 NULL이고 created_at이 1년 이상 지난 users 삭제.

DELETE는 UPDATE보다도 위험히기 때문에 SELECT, COUNT 후에 진행하는 게 좋다.

-- 대상 확인
SELECT id
FROM users
WHERE age IS NULL
AND created_at < NOW() - INTERVAL '1 year';
-- 건수 확인
SELECT COUNT(*)
FROM users
WHERE age IS NULL
AND created_at < NOW() - INTERVAL '1 year';

DELETE FROM users
WHERE age IS NULL
AND created_at < NOW() - INTERVAL '1 year';

postgre에서는 1년 이상을 "NOW() - INTERVAL '1 year'"이런식으로 사용한다.

문제 8 (JOIN DELETE)

reviews에 한 번도 등장하지 않은 user만 삭제

DELETE FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.user_id = u.id
);

문제 9 (soft delete 설계)

실무에서는 users를 물리 삭제 안 한다.

deleted_at 컬럼이 있다고 가정하고 실제 DELETE 대신 soft delete 수행하는 UPDATE 작성.

UPDATE users u
SET deleted_at = NOW()
WHERE deleted_at IS NULL
AND NOT EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.user_id = u.id
);

deleted_at IS NULL을 같이 넣어야 이미 soft delete된 row를 또 업데이트하지 않아서 불필요한 MVCC/인덱스 부담이 줄어든다.

문제 10

다음 작업을 하나의 트랜잭션으로 처리:

age NULL → 0으로 업데이트
age 0인 users 조회
확인 후 COMMIT

SQL 흐름 작성.

BEGIN;

UPDATE users
SET age = 0
WHERE age IS NULL;

SELECT *
FROM users
WHERE age = 0;

COMMIT;

COMMIT 전에 결과를 눈으로 확인하고, 문제 있으면 ROLLBACK; 한다.

문제 11 (대량 INSERT 성능)

orders 테이블에 users 전체를 대상으로

user_id 그대로
total_price = 0
status = 'INIT'

대량으로 INSERT (VALUES 반복 금지)

INSERT INTO orders (user_id, total_price, status)
SELECT u.id, 0, 'INIT'
FROM users u;

문제 12 (실무 사고 케이스)

다음 UPDATE가 위험한 이유 설명 + 안전한 버전 SQL 작성.

UPDATE orders SET status = 'CANCELLED';

WHERE가 없어서 orders 전체 행이 한 번에 변경된다.

그래서 아래처럼 대상이 없으면 멈추도록 SQL을 작성하는 게 좋다.

BEGIN;

SELECT COUNT(*)
FROM orders
WHERE status = 'PENDING'
AND created_at < CURRENT_DATE;

UPDATE orders
SET status = 'CANCELLED'
WHERE status = 'PENDING'
AND created_at < CURRENT_DATE;

COMMIT;

저작자표시 비영리 동일조건 (새창열림)

'Database > SQL' 카테고리의 다른 글

TCL (0)	2026.02.11
Lock과 성능 개선 (0)	2026.02.11
ALTER, DROP, TRUNCATE (0)	2026.02.10
CREATE (0)	2026.02.10
String, Date (0)	2026.02.09

INSERT, UPDATE, DELETE

1) DML

2) INSERT

기본 형태

여러 row INSERT

DEFAULT VALUES

RETURNING (PostgreSQL 핵심)

3) UPDATE

기본 형태

WHERE 없는 UPDATE = 전체 변경

여러 컬럼 변경

계산 업데이트

FROM 절 UPDATE (Postgres 특화)

4) DELETE

기본 형태

WHERE 없는 DELETE = 전체 삭제

FK 영향

5) UPSERT

INSERT + UPDATE 합친 것

DO NOTHING = 중복되면 무시

6) RETURNING

7) 트랜잭션과 DML

ROLLBACK = 삭제 취소

8) DML에서 실제로 걸리는 Lock (INSERT / UPDATE / DELETE)

INSERT

UPDATE

DELETE

작업위험도

9) MVCC (PostgreSQL 구조)

10) 트랜잭션 격리 수준 (Isolation Level)

READ COMMITTED (기본값)

REPEATABLE READ

11) vacuum

autovacuum

DML 문제

문제 1 신규 사용자 추가.

문제 2

문제 3 (INSERT + SELECT)

문제 4 (안전 UPDATE)

문제 5 (조건 UPDATE)

문제 6 (JOIN UPDATE)

문제 7 (안전 DELETE)

문제 8 (JOIN DELETE)

문제 9 (soft delete 설계)

문제 10

문제 11 (대량 INSERT 성능)

문제 12 (실무 사고 케이스)

'Database > SQL' 카테고리의 다른 글

관련글

티스토리툴바