1) PostgreSQL Lock 전체 구조
PostgreSQL은 두 가지 lock을 동시에 사용한다.
(1) Table-level lock
(2) Row-level lock
핵심은 가능한 한 “최소 범위만 잠근다”
(1) Table-level Lock 종류
이건 “테이블 전체”에 대한 lock이다. 하지만 대부분 읽기/쓰기 막지는 않는다.
ACCESS SHARE
SELECT가 거는 lock.
특징:
- UPDATE/INSERT/DELETE 허용
- SELECT끼리는 충돌 없음
- SELECT는 거의 시스템에 영향 없음
ROW EXCLUSIVE
INSERT / UPDATE / DELETE 시 걸림.
특징:
- 다른 DML 허용
- DDL은 막힘
- 일반 서비스 트래픽은 이 lock 위에서 돌아감
SHARE
CREATE INDEX 등에서 발생.
- 읽기 허용, 쓰기 제한.
EXCLUSIVE / ACCESS EXCLUSIVE
가장 위험한 lock.
발생 작업:
- ALTER TABLE
- DROP TABLE
- TRUNCATE
효과:
- SELECT 포함 모든 작업 대기
- 서비스 “멈춘 것처럼 보이는” 순간은 거의 이 lock 때문이다.
(2) Row-level Lock
UPDATE/DELETE 시
대상 row만 잠김.
UPDATE users SET age=20 WHERE id=1;
- → id=1 row만 lock
- 다른 row는 영향 없음.
SELECT … FOR UPDATE
SELECT * FROM users WHERE id=1 FOR UPDATE;
- 읽으면서 row lock 확보
- 용도: 재고, 계좌 등 동시 수정 방지
2) Deadlock
Deadlock 정의
서로 lock을 기다리며 영원히 진행 못하는 상태.
트랜잭션 A: row1 lock → row2 lock 대기
트랜잭션 B: row2 lock → row1 lock 대기
- 서로 기다림.
- PostgreSQL은 일정 시간 후 하나를 죽인다.
에러:
deadlock detected
(1) Deadlock 가장 흔한 패턴
패턴 1 — 업데이트 순서 다름
A: UPDATE users WHERE id=1;
A: UPDATE users WHERE id=2;
B: UPDATE users WHERE id=2;
B: UPDATE users WHERE id=1;
100% deadlock 가능.
패턴 2 — FK + DELETE
DELETE parent
→ child FK 검사
다른 트랜잭션이 child 수정 중
패턴 3 — SELECT FOR UPDATE 충돌
동시에 같은 자원 선점.
(2) PostgreSQL Deadlock 대응 방식
자동 탐지 후:
- 한 트랜잭션 kill 다른 트랜잭션 진행
즉, deadlock은 “발생하면 해결”이 아니라 설계로 방지해야 하는 문제다.
(3) 실무에서 deadlock 방지 원칙
원칙 1 — 항상 같은 순서로 접근
나쁜 예:
A: user → order
B: order → user
좋은 예:
항상 user → order 순서
원칙 2 — 트랜잭션 짧게 유지
긴 트랜잭션:
- lock 오래 유지
- deadlock 확률 상승
원칙 3 — WHERE 정확히
불필요한 row lock 방지.
원칙 4 — FK 삭제 조심
CASCADE 설계 시:
- 대량 lock 발생 가능
(4) Deadlock 로그 읽는 법
Process 123 waits for ShareLock on transaction ...
blocked by process 456
- 누가 누구를 기다리는지
- 어떤 테이블/row인지
3) Lock과 성능 관계
Lock은 단순 대기가 아니다.
- latency 증가
- TPS 감소
- 타임아웃
- 장애
위험도:
| ALTER TABLE | 매우 높음 |
| DELETE + FK | 높음 |
| UPDATE batch | 높음 |
| SELECT FOR UPDATE | 높음 |
쿼리 실행
→ 옵티마이저가 계획 생성
→ 인덱스 사용 여부 결정
→ 실행
→ WAL 기록
4) 실행계획 (EXPLAIN)
DB가 “이 쿼리를 어떻게 실행할지” 미리 보여주는 명령
실행은 안 하고 계획만: EXPLAIN
EXPLAIN SELECT * FROM users WHERE id = 1;
또는 실제 실행까지 포함: EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
(1) Scan 방식
| Seq Scan | 테이블 전체 읽음 |
| Index Scan | 인덱스 사용 |
| Bitmap Heap Scan | 대량 조건 인덱스 사용 |
(2) cost
cost=0.29..8.30
- 옵티마이저가 계산한 예상 비용
- 실제 시간 아님
- 비교용 지표
(3) rows
rows=1
- 예상 결과 개수
이게 틀리면 옵티마이저 판단도 틀린다.
Seq Scan vs Index Scan
성능의 핵심 분기점
Seq Scan
Seq Scan on users
- 테이블 전체 읽음
- row 적으면 괜찮음
- 많으면 느림
Index Scan
Index Scan using users_pkey
- 인덱스 통해 row 찾음
- 조건 정확할 때 빠름
EXPLAIN ANALYZE
실제 실행 결과 포함.
actual time=0.02..0.03
이걸 봐야 진짜 성능 분석 가능.
5) 인덱스 구조
PostgreSQL 기본 인덱스: B-tree
B-tree 구조
루트
├─ 중간 노드
│ ├─ leaf
│ └─ leaf
정렬된 트리.
인덱스가 빠른 이유
테이블이 1억 개의 row라면, 검색:
- Seq Scan → 1억 row 읽기
- Index Scan → 트리 3~4단계 탐색
- 시간 복잡도: O(n) → O(log n)
인덱스가 느려지는 경우
(1) 조건이 broad
WHERE age > 1
→ 대부분 row 포함 → Seq Scan 선택
(2) 함수 사용
WHERE LOWER(email) = 'a@test.com'
→ 인덱스 못 탐 (함수 인덱스 필요)
(3) 인덱스 컬럼 순서 문제
복합 인덱스:
INDEX (a, b)
검색:
WHERE b = ?
→ 인덱스 못 씀
인덱스는 많을수록 좋다? 아니다.
- INSERT 느림
- UPDATE 느림
- 디스크 증가
6) 옵티마이저 — DB가 쿼리를 해석하는 방식
옵티마이저는 “이 쿼리를 가장 적은 비용으로 실행하는 방법”을 선택
Cost-based Optimizer
PostgreSQL은:
- 통계 기반
- 비용 계산
판단 기준
- cardinality: 조건 결과 row 수 예측
- selectivity: 조건이 얼마나 좁은지
- statistics: 컬럼 분포 정보
옵티마이저 판단 흐름
조건 분석
→ 통계 조회
→ cost 계산
→ 실행 계획 선택
통계 틀리면? 옵티마이저도 틀림.
7) WAL 구조 — 쓰기 성능의 핵심
PostgreSQL은 “로그 먼저 쓰고 데이터 쓰는 구조”
Write-Ahead Log
- 순서: WAL 기록 → 디스크 데이터 쓰기
- 이유는 crash recovery (비정상 처리를 복구하기 위해)
INSERT 흐름: row 생성 → WAL 기록 → 인덱스 업데이트 → commit
WAL는 성능에 직접적인 영향을 주기 때문에 중요하다
- INSERT 속도
- UPDATE 속도
- replication
- recovery
WAL 부담이 커지는 경우
- 인덱스 많음
- UPDATE 많음
- 대량 INSERT
WAL과 TRUNCATE 차이
- DELETE: row마다 WAL
- TRUNCATE: 최소 WAL → 빠름
실습 해보기
테이블 생성
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
price INT NOT NULL CHECK (price >= 0)
);
데이터 대량 생성
1. users 100,000명
INSERT INTO users(email, created_at)
SELECT
'user_' || g || '@test.com',
NOW() - (g % 365) * INTERVAL '1 day'
FROM generate_series(1, 100000) g;
ANALYZE users;2. orders 2,000,000건 (상태 분포 포함)
INSERT INTO orders(user_id, status, created_at)
SELECT
(random() * 99999 + 1)::bigint,
CASE
WHEN random() < 0.70 THEN 'PAID'
WHEN random() < 0.90 THEN 'PENDING'
ELSE 'CANCELLED'
END,
NOW() - (random() * 365) * INTERVAL '1 day'
FROM generate_series(1, 2000000) g;
ANALYZE orders;3. order_items 6,000,000건
INSERT INTO order_items(order_id, product_id, quantity, price)
SELECT
(random() * 1999999 + 1)::bigint,
(random() * 200000 + 1)::bigint,
(random() * 3 + 1)::int,
(random() * 50000)::int
FROM generate_series(1, 6000000) g;
ANALYZE order_items;느린 쿼리: 기본적인 '기간+상태+조인+집계+정렬+LIMIT'
EXPLAIN (ANALYZE, BUFFERS)
SELECT
o.user_id,
SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'PAID'
AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.user_id
ORDER BY total_amount DESC
LIMIT 20;
- Seq Scan on orders 또는 Seq Scan on order_items가 큰 비중
- Hash Join / Hash Aggregate / Sort가 무겁게 잡힘
- BUFFERS에서 shared read가 크게 뜰 수 있음(디스크 읽음)
Limit (cost=127058.84..127058.89 rows=20 width=16) (actual time=489.459..489.662 rows=20 loops=1)
Buffers: shared hit=10697 read=48230
-> Sort (cost=127058.84..127226.79 rows=67180 width=16) (actual time=489.457..489.659 rows=20 loops=1)
Sort Key: (sum((oi.quantity * oi.price))) DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=10697 read=48230
-> Finalize HashAggregate (cost=124599.41..125271.21 rows=67180 width=16) (actual time=483.339..486.902 rows=66344 loops=1)
Group Key: o.user_id
Batches: 1 Memory Usage: 7441kB
Buffers: shared hit=10697 read=48230
-> Gather (cost=109819.81..123927.61 rows=134360 width=16) (actual time=456.953..464.956 rows=154766 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=10697 read=48230
-> Partial HashAggregate (cost=108819.81..109491.61 rows=67180 width=16) (actual time=449.579..452.390 rows=51589 loops=3)
Group Key: o.user_id
Batches: 1 Memory Usage: 4881kB
Buffers: shared hit=10697 read=48230
Worker 0: Batches: 1 Memory Usage: 4881kB
Worker 1: Batches: 1 Memory Usage: 4881kB
-> Parallel Hash Join (cost=32069.87..107750.26 rows=142607 width=16) (actual time=234.228..430.784 rows=114954 loops=3)
Hash Cond: (oi.order_id = o.id)
Buffers: shared hit=10697 read=48230
-> Parallel Seq Scan on order_items oi (cost=0.00..69117.90 rows=2499990 width=16) (actual time=0.070..67.374 rows=2000000 loops=3)
Buffers: shared hit=841 read=43277
-> Parallel Hash (cost=31475.67..31475.67 rows=47536 width=16) (actual time=233.996..233.996 rows=38332 loops=3)
Buckets: 131072 Batches: 1 Memory Usage: 6464kB
Buffers: shared hit=9856 read=4953
-> Parallel Seq Scan on orders o (cost=0.00..31475.67 rows=47536 width=16) (actual time=0.103..228.041 rows=38332 loops=3)
Filter: ((status = 'PAID'::text) AND (created_at >= (now() - '30 days'::interval)))
Rows Removed by Filter: 628335
Buffers: shared hit=9856 read=4953
Planning:
Buffers: shared hit=8
Planning Time: 1.043 ms
Execution Time: 490.229 ms
order_items를 보면:
- workers 2명 + leader = loops=3
- 각 루프가 rows=2,000,000씩 스캔
- order_items(order_id) 인덱스가 없으면, “필터된 orders를 기준으로 order_items를 인덱스로 찌르는 Nested Loop”가 불가능해져서 옵티마이저는 “그냥 큰 쪽을 다 읽고 해시로 붙이자”를 선택한다.
orders 쪽:
- rows=38339 (각 worker)
- Rows Removed by Filter: 628328 (각 worker)
- 즉 “최근 30일 + PAID”여도 남는 row가 수만 단위라서 (정확한 전체는 parallel 분산이라 체감치가 크지만) 옵티마이저는 “orders도 인덱스 안 타고 그냥 스캔해도 되겠다” 쪽으로 판단했을 가능성이 높다.
Buffer를 보면:
- 처음 읽을 때는 읽기쓰기 작업이 많이 발생해 더 느리고 read 숫자도 더 높게 나온다.
- 현재는 한 번 읽기를 끝낸 후라서 조금 더 적게 읽었지만 여전히 디스크에서 많이 읽었다는 것을 알 수 있다.
orders 필터를 위한 복합 인덱스
CREATE INDEX idx_orders_status_createdat_id
ON orders(status, created_at, id);
ANALYZE orders;- status + created_at로 먼저 줄이고, 조인에 필요한 id를 함께 고려
order_items 조인을 위한 인덱스
CREATE INDEX idx_order_items_order_id
ON order_items(order_id);
ANALYZE order_items;- 조인 키가 order_id이므로 위처럼 인덱스 생성
재측정
Limit (cost=114725.34..114725.39 rows=20 width=16) (actual time=370.419..370.587 rows=20 loops=1)
Buffers: shared hit=15233 read=44257
-> Sort (cost=114725.34..114893.03 rows=67076 width=16) (actual time=370.416..370.583 rows=20 loops=1)
Sort Key: (sum((oi.quantity * oi.price))) DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=15233 read=44257
-> Finalize HashAggregate (cost=112269.72..112940.48 rows=67076 width=16) (actual time=364.247..367.815 rows=66334 loops=1)
Group Key: o.user_id
Batches: 1 Memory Usage: 7441kB
Buffers: shared hit=15233 read=44257
-> Gather (cost=97513.00..111598.96 rows=134152 width=16) (actual time=338.210..346.130 rows=154904 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=15233 read=44257
-> Partial HashAggregate (cost=96513.00..97183.76 rows=67076 width=16) (actual time=327.274..330.172 rows=51635 loops=3)
Group Key: o.user_id
Batches: 1 Memory Usage: 4881kB
Buffers: shared hit=15233 read=44257
Worker 0: Batches: 1 Memory Usage: 4881kB
Worker 1: Batches: 1 Memory Usage: 4881kB
-> Parallel Hash Join (cost=19770.03..95450.42 rows=141677 width=16) (actual time=102.629..308.096 rows=114931 loops=3)
Hash Cond: (oi.order_id = o.id)
Buffers: shared hit=15233 read=44257
-> Parallel Seq Scan on order_items oi (cost=0.00..69117.90 rows=2499990 width=16) (actual time=0.071..69.075 rows=2000000 loops=3)
Buffers: shared hit=414 read=43704
-> Parallel Hash (cost=19179.70..19179.70 rows=47226 width=16) (actual time=102.325..102.325 rows=38324 loops=3)
Buckets: 131072 Batches: 1 Memory Usage: 6464kB
Buffers: shared hit=14819 read=553
-> Parallel Bitmap Heap Scan on orders o (cost=3426.19..19179.70 rows=47226 width=16) (actual time=15.448..93.900 rows=38324 loops=3)
Recheck Cond: ((status = 'PAID'::text) AND (created_at >= (now() - '30 days'::interval)))
Heap Blocks: exact=4897
Buffers: shared hit=14819 read=553
-> Bitmap Index Scan on idx_orders_status_createdat_id (cost=0.00..3397.85 rows=113342 width=0) (actual time=21.316..21.316 rows=114971 loops=1)
Index Cond: ((status = 'PAID'::text) AND (created_at >= (now() - '30 days'::interval)))
Buffers: shared hit=569
Planning:
Buffers: shared hit=102 read=2
Planning Time: 3.284 ms
Execution Time: 372.746 ms약 17% 정도 성능이 개선 됌.
핵심 변화:
- Before: Parallel Seq Scan on orders
- After:
- Bitmap Index Scan on idx_orders_status_createdat_id
- Parallel Bitmap Heap Scan on orders
즉, status='PAID' AND created_at >= now()-30d 필터가 인덱스로 먼저 줄어든 다음 heap을 읽는 구조로 바뀜.
여전히 가장 큰 병목은 이거야:
- Parallel Seq Scan on order_items oi ... rows=2000000 loops=3
- Parallel Hash Join (oi.order_id = o.id)
즉, order_items를 여전히 풀스캔하고 있다.
order_items 인덱스를 안 탈까? order_items를 병렬로 쭉 읽고 해시로 붙이자”가 더 싸다고 본 것
조금 더 최적화를 위해 '커버링/복합' 인덱스를 추가해본다.
CREATE INDEX idx_order_items_order_id_cover
ON order_items(order_id)
INCLUDE (quantity, price);
ANALYZE order_items;- order_id (조인) quantity, price (집계 계산)을 함께 묶는 인덱스 사용
Execution Time: 301.517 ms조인 순서를 강제로 유도하고, CTE로 orders를 먼저 확정하도록 쿼리 변경해보기
EXPLAIN (ANALYZE, BUFFERS)
WITH paid_orders AS (
SELECT id, user_id
FROM orders
WHERE status = 'PAID'
AND created_at >= NOW() - INTERVAL '30 days'
)
SELECT
po.user_id,
SUM(oi.quantity * oi.price) AS total_amount
FROM paid_orders po
JOIN order_items oi ON oi.order_id = po.id
GROUP BY po.user_id
ORDER BY total_amount DESC
LIMIT 20;Execution Time: 172.833 ms가장 처음보다 2.6배 정도 빨라졌다.
실습 후 DROP 하기
\di --인덱스 확인\d -- 테이블/시퀀스 확인DROP INDEX IF EXISTS idx_orders_status_createdat_id;
DROP INDEX IF EXISTS idx_order_items_order_id;
DROP INDEX IF EXISTS idx_order_items_order_id_cover;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS users CASCADE;
'Database > SQL' 카테고리의 다른 글
| TCL (0) | 2026.02.11 |
|---|---|
| INSERT, UPDATE, DELETE (0) | 2026.02.10 |
| ALTER, DROP, TRUNCATE (0) | 2026.02.10 |
| CREATE (0) | 2026.02.10 |
| String, Date (0) | 2026.02.09 |