DBA의 일을 한 문장으로 줄이면 이렇다.

데이터를 잃지 않으면서 빠르게 만드는 것. 두 목표가 충돌하면 항상 앞이 이긴다.

이 글은 그 순서대로 썼다. 저장의 물리적 최소 단위에서 시작해 인덱스 → 실행계획 → 트랜잭션 → 락 → MVCC의 청구서 → 커넥션 → 백업 → 복제 → 무중단 스키마 변경 → 모니터링 → 권한 순으로 간다. 앞쪽을 모르면 뒤쪽에서 하는 일이 대부분 미신이 된다.

모든 동작 설명과 수치는 PostgreSQL 18 / MySQL 8.4 공식 문서로 대조했고, 문서에서 확인하지 못한 것은 그렇다고 표시했다. 인용문은 원문 표현을 옮긴 것이다. Oracle·SQL Server는 다루지 않는다(확인하지 않았다).

0. 30초 요약

# 주제 모르면 벌어지는 일
1 페이지와 행 타입 하나 잘못 골라 I/O가 2배
2 인덱스 만들었는데 안 타고, 쓰기만 느려짐
3 실행계획·통계 파티션 부모 테이블 통계가 영원히 안 갱신됨
4 격리수준 같은 코드가 DB 바꾸면 다르게 동작
5 락·데드락 새벽 배치가 서로 물고 멈춤
6 VACUUM·XID DB가 쓰기를 전면 거부함
7 커넥션 max_connections 올렸다가 더 느려짐
8 백업 있는 줄 알았던 백업으로 복구가 안 됨
9 복제 읽기 복제본을 백업으로 착각
10 스키마 변경 DDL 하나에 전체 서비스 정지
11 지표 터진 뒤에야 원인을 찾기 시작
12 권한 앱 계정으로 DROP TABLE

1. 저장의 최소 단위 — DB는 행이 아니라 페이지를 읽는다

가장 먼저 잡아야 할 감각이다. 디스크에서 행 하나만 읽는 방법은 없다. DB는 고정 크기의 페이지(블록) 단위로 읽고 쓴다.

그래서 다음이 성립한다.

한 페이지에 행이 몇 개 들어가느냐가 같은 쿼리의 I/O 횟수를 결정한다.

컬럼 타입 선택이 “취향”이 아니라 성능 문제인 이유가 여기 있다. 행 하나가 200바이트인 테이블과 400바이트인 테이블은, 같은 100만 건 풀스캔에서 읽어야 할 페이지 수가 2배 차이 난다.

기본 페이지 크기는 환경마다 다를 수 있으니 외우지 말고 직접 확인하는 습관을 권한다.

-- PostgreSQL
SHOW block_size;

-- MySQL (InnoDB)
SELECT @@innodb_page_size;

실무 판단 기준 몇 가지.

  • 필요한 것보다 큰 정수 타입을 쓰지 않는다. BIGINT가 안전해 보이지만, 그 컬럼이 인덱스에도 들어가면 인덱스 페이지에서도 두 배를 먹는다.
  • 가변 길이 타입의 상한은 “저장 공간”이 아니라 “검증 규칙”이다. PostgreSQL에서 varchar(255)와 text의 저장 방식 차이는 없다시피 하다. 길이 제한은 성능 최적화가 아니라 도메인 제약으로 쓸 때만 의미가 있다.
  • NULL은 공짜가 아니고 공짜에 가깝지도 않다. NULL 허용 여부는 저장 방식뿐 아니라 인덱스 동작과 옵티마이저 추정에 영향을 준다. 의미 없는 NULL 허용은 나중에 비용이 된다.
  • 큰 텍스트/바이너리는 별도 저장소로 밀려난다. PostgreSQL은 큰 값을 TOAST 영역으로 분리한다. 그 컬럼을 SELECT 목록에 넣기만 해도 추가 I/O가 붙는다. SELECT *가 나쁜 실질적 이유가 이것이다.

2. 인덱스 — 만드는 것보다 “왜 안 타는가”가 본론

B-tree가 왜 B-tree인가

인덱스가 이진 트리가 아니라 B-tree인 이유는 §1에서 나온다. 노드 하나 = 페이지 하나이고, 페이지 하나 읽는 게 I/O 한 번이다. 노드당 자식을 수백 개 둘 수 있으면 트리 높이가 3~4로 줄고, 그러면 수천만 행에서도 I/O 서너 번에 도달한다. 디스크 시대에 맞춰 설계된 자료구조다.

복합 인덱스: 좌측 접두사 규칙

(a, b, c) 인덱스는 정렬 순서가 a → b → c다. 따라서:

조건 인덱스 사용
WHERE a = ? ✅
WHERE a = ? AND b = ? ✅
WHERE a = ? AND b = ? AND c = ? ✅
WHERE b = ? ❌ (a를 건너뛸 수 없음)
WHERE a = ? AND c = ? ⚠️ a까지만 (c는 필터로 걸러짐)
WHERE a > ? AND b = ? ⚠️ 범위 조건 뒤는 정렬이 깨짐

마지막 줄이 실무에서 제일 많이 틀리는 곳이다. 범위 조건(>, <, BETWEEN, LIKE 'x%')이 나오는 순간 그 뒤 컬럼은 인덱스 정렬의 이점을 잃는다. 그래서 복합 인덱스 컬럼 순서는 등치 조건 → 범위 조건 → 정렬용 순으로 배치하는 게 기본이다.

인덱스를 만들었는데 안 타는 전형적 이유

  1. 컬럼에 함수를 씌웠다. WHERE DATE(created_at) = '2026-08-12' → created_at 인덱스를 못 쓴다. WHERE created_at >= '2026-08-12' AND created_at < '2026-08-13'으로 바꾸거나, 함수 인덱스/생성 컬럼을 만든다.
  2. 타입이 안 맞아 암묵 캐스팅이 일어났다. 문자열 컬럼에 숫자를 넣거나 그 반대.
  3. 앞쪽 와일드카드. LIKE '%검색어'는 B-tree로 시작점을 잡을 수 없다. 전문검색 색인이나 trigram 계열이 필요한 영역이다.
  4. 선택도가 나쁘다. 전체의 30%를 고르는 인덱스라면 옵티마이저는 풀스캔이 더 싸다고 판단한다. 이건 옵티마이저가 틀린 게 아니라 맞은 것이다.
  5. 통계가 낡았다. → §3.

커버링 인덱스와 PostgreSQL의 함정

쿼리가 필요로 하는 컬럼이 전부 인덱스 안에 있으면 테이블(힙)을 안 읽어도 된다. 이게 index-only scan이다. 그런데 PostgreSQL에서는 조건이 하나 더 붙는다.

“Since PostgreSQL indexes don’t contain tuple visibility information, a normal index scan fetches the heap tuple for each matching index entry, to check whether it should be seen by the current transaction. An index-only scan, on the other hand, checks the visibility map first.” — PostgreSQL 18 Documentation, 24.1.4

즉 PostgreSQL 인덱스는 “이 행이 지금 트랜잭션에 보이는지”를 모른다. 그 정보는 visibility map에 있고, visibility map은 VACUUM이 갱신한다. VACUUM이 밀리면 실행계획에 Index Only Scan이라고 찍혀도 실제로는 힙을 읽는다. 실행계획의 Heap Fetches: 값을 보면 알 수 있다.

인덱스 튜닝이 VACUUM 관리(§6)와 연결되는 첫 번째 지점이다.

인덱스의 청구서

인덱스는 조회를 빠르게 하는 대신 모든 INSERT/UPDATE/DELETE에 갱신 비용을 붙인다. 그러니 정기적으로 “쓰지 않는 인덱스”를 찾아 지워야 한다.

-- PostgreSQL: 스캔 횟수 0인 인덱스
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

-- MySQL 8.x: sys 스키마
SELECT * FROM sys.schema_unused_indexes;

두 뷰 모두 통계 수집이 시작된 시점 이후의 값이다. 서버 재시작 직후나 통계 리셋 직후 값을 보고 지우면 안 된다. 배치 잡이 월 1회만 쓰는 인덱스도 있다.

3. 실행계획과 통계 — 추정과 실제의 괴리를 본다

EXPLAIN은 계획만 보여주고, EXPLAIN ANALYZE는 실제로 실행하고 실측치를 함께 보여준다.

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

⚠️ EXPLAIN ANALYZE는 쿼리를 진짜 실행한다. UPDATE/DELETE에 그냥 붙이면 데이터가 바뀐다. 트랜잭션으로 감싸고 롤백하는 습관을 들일 것.

읽을 때 제일 먼저 볼 것은 노드 이름이 아니라 추정 행수와 실제 행수의 비율이다.

Seq Scan on orders  (cost=... rows=100 width=...) (actual ... rows=980000 ...)
                                    ^^^^^^^^                    ^^^^^^^^^^^^

추정 100건, 실제 98만 건. 이건 쿼리 문제가 아니라 통계 문제다. 옵티마이저는 100건인 줄 알고 중첩 루프를 선택했을 텐데, 98만 번 돌게 된다.

통계는 누가 만드는가

PostgreSQL에서 통계는 ANALYZE가 만들고, autovacuum 데몬이 자동으로 발행한다. 그런데 공식 문서가 명시하는 예외가 있다.

“The autovacuum daemon does not issue ANALYZE commands for partitioned tables. Inheritance parents will only be analyzed if the parent itself is changed - changes to child tables do not trigger autoanalyze on the parent table.” — PostgreSQL 18 Documentation, 24.1.3

파티션 부모 테이블은 자동 통계 수집 대상이 아니다. 자식 파티션에 아무리 데이터가 쌓여도 부모 통계는 갱신되지 않는다. 외부 테이블(foreign table)도 마찬가지다.

파티셔닝을 도입한 팀이 몇 달 뒤 “왜 갑자기 느려졌지”를 겪는 흔한 경로가 이것이다. 부모 테이블에 대한 주기적 수동 ANALYZE를 크론에 넣어야 한다.

그리고 통계 정밀도가 부족하면 컬럼 단위로 올릴 수 있다.

ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000;  -- 기본값보다 세밀한 히스토그램
ANALYZE t;

4. 트랜잭션 격리수준 — 표준과 구현이 다르다

여기가 “안다고 생각하지만 대체로 틀리게 아는” 영역이다.

표준이 정의하는 것

SQL 표준은 격리수준을 “일어나면 안 되는 현상” 으로 정의한다. PostgreSQL 문서의 정의를 그대로 옮기면:

  • dirty read — 커밋되지 않은 다른 트랜잭션의 데이터를 읽음
  • nonrepeatable read — 이미 읽은 데이터를 다시 읽었더니 값이 바뀜
  • phantom read — 같은 조건으로 다시 조회했더니 행 집합이 바뀜
  • serialization anomaly — 커밋 결과가 어떤 직렬 실행 순서로도 설명되지 않음

그런데 실제 구현은 표준과 다르다

PostgreSQL 공식 문서의 표를 그대로 인용한다.

Isolation Level Dirty Read Nonrepeatable Read Phantom Read Serialization Anomaly
Read uncommitted Allowed, but not in PG Possible Possible Possible
Read committed Not possible Possible Possible Possible
Repeatable read Not possible Not possible Allowed, but not in PG Possible
Serializable Not possible Not possible Not possible Not possible

출처: PostgreSQL 18 Documentation, Table 13.1

문서가 직접 설명한다.

“In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL’s Read Uncommitted mode behaves like Read Committed.”

“The table also shows that PostgreSQL’s Repeatable Read implementation does not allow phantom reads. This is acceptable under the SQL standard because the standard specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable.”

즉 PostgreSQL은 4개를 받아주지만 실제로는 3개만 구현한다. 그리고 REPEATABLE READ는 표준이 허용하는 팬텀조차 막는다. 표준보다 강하다.

기본값이 다르다는 것이 실무에서 제일 중요하다

  기본 격리수준
PostgreSQL READ COMMITTED
MySQL (InnoDB) REPEATABLE READ

출처: PostgreSQL 18 Documentation 13.2.1 / MySQL 8.4 Reference Manual 17.7.2.1

같은 애플리케이션 코드가 DB를 옮기면 다르게 동작한다. 트랜잭션 안에서 같은 SELECT를 두 번 하는 코드는, MySQL에서는 같은 값을 보고 PostgreSQL에서는 다른 값을 볼 수 있다.

PostgreSQL 문서가 READ COMMITTED에 대해 명시한다.

“two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.”

InnoDB REPEATABLE READ의 구조와 공식 경고

MySQL 문서 설명은 이렇다.

“Consistent reads within the same transaction read the snapshot established by the first read.”

“For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.” — MySQL 8.4 Reference Manual, 17.7.2.1

그리고 문서가 직접 경고하는 함정이 있다. 이건 실무에서 정말 자주 밟는다.

“It is not recommended to mix locking statements (UPDATE, INSERT, DELETE, or SELECT ... FOR ...) with non-locking SELECT statements in a single REPEATABLE READ transaction, because typically in such cases you want SERIALIZABLE. This is because a non-locking SELECT statement presents the state of the database from a read view (…) while the locking statements use the most recent state of the database to use locking. In general, these two different table states are inconsistent with each other and difficult to parse.”

번역하면 — 하나의 REPEATABLE READ 트랜잭션 안에서 일반 SELECT와 SELECT ... FOR UPDATE를 섞으면, 둘이 서로 다른 시점의 DB를 본다. “재고를 조회하고 → 판단하고 → 잠그고 → 차감한다” 같은 코드가 여기서 조용히 깨진다.

또 하나. MySQL의 READ COMMITTED는 gap lock을 끄기 때문에 팬텀이 가능해지고, 문서에 따르면 row-based binary logging만 지원된다(binlog_format=MIXED면 자동으로 row 기반으로 전환). 격리수준을 바꾸는 결정이 복제 방식에까지 영향을 준다는 뜻이다.

5. 락과 데드락

MVCC의 기본 계약

읽기는 쓰기를 막지 않고, 쓰기는 읽기를 막지 않는다.

PostgreSQL·InnoDB 모두 다중 버전을 유지해 이걸 달성한다. 그래서 일반 SELECT는 락을 기다리지 않는다. 락이 문제가 되는 건 쓰기끼리, 그리고 명시적 잠금 읽기다.

격리수준이 데드락 확률을 바꾼다

MySQL 문서의 READ COMMITTED 설명.

“For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.”

REPEATABLE READ에서는 읽으며 잡은 x-lock을 트랜잭션 끝까지 들고 있고, READ COMMITTED에서는 조건에 안 맞는 행의 락을 바로 푼다. 인덱스가 없는 컬럼으로 UPDATE를 돌리면 클러스터드 인덱스를 훑으며 전 행에 락을 잡는다(문서의 예제가 정확히 그 상황이다). 데드락 대응의 첫걸음이 “인덱스를 제대로 태우는 것”인 이유다.

데드락 실무 원칙 4가지

  1. 접근 순서를 통일한다. 모든 트랜잭션이 테이블/행을 같은 순서로 잡으면 순환이 안 생긴다. 데드락 방지의 근본 대책은 이것 하나뿐이다.
  2. 트랜잭션을 짧게. 트랜잭션 안에서 외부 API를 호출하지 않는다. 사람의 입력을 기다리지 않는다.
  3. 재시도를 전제로 짠다. 데드락은 예방이 아니라 확률 관리다. DB는 한쪽을 죽여서 푼다. 애플리케이션은 그 에러를 잡아 멱등하게 재시도할 수 있어야 한다.
  4. 락 타임아웃을 건다. 무한 대기는 장애를 전파시킨다.

6. MVCC의 청구서 — VACUUM과 XID wraparound

이 절이 이 글에서 가장 중요하다. 다른 걸 다 몰라도 이건 알아야 한다. 나머지는 느려지는 문제고, 이건 DB가 쓰기를 전면 거부하는 문제다.

왜 청소가 필요한가

“In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. (…) the row version must not be deleted while it is still potentially visible to other transactions.” — PostgreSQL 18 Documentation, 24.1.2

MVCC의 대가다. 옛 버전이 쌓이고, 그걸 치우는 게 VACUUM이다. 문서가 정리한 VACUUM의 목적 4가지:

  1. 갱신·삭제된 행이 차지한 공간 회수
  2. 플래너용 통계 갱신
  3. visibility map 갱신 (→ §2의 index-only scan)
  4. 트랜잭션 ID wraparound로 인한 오래된 데이터 손실 방지

VACUUM FULL은 운영 중에 쓰는 물건이 아니다

“VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table it is working on, and therefore cannot be done in parallel with other use of the table. Generally, therefore, administrators should strive to use standard VACUUM and avoid VACUUM FULL.”

일반 VACUUM은 서비스와 병행 가능하고 공간을 재사용 가능하게 만들 뿐 OS에 반납하지 않는다. VACUUM FULL은 테이블을 통째로 다시 쓰며 테이블 크기만큼의 여유 디스크를 추가로 요구한다. 디스크가 꽉 차서 VACUUM FULL을 돌리려는 순간이 제일 위험하다.

그리고 XID wraparound

트랜잭션 ID는 32비트다. 문서의 설명을 그대로 옮긴다.

“since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (…) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.”

방치했을 때 벌어지는 일도 문서에 단계별로 적혀 있다.

1단계 — 남은 XID가 4천만에 도달하면 경고:

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

2단계 — 300만 미만이 되면 새 XID 발급 거부:

ERROR:  database is not accepting commands that assign new XIDs to avoid wraparound data loss
HINT:  Execute a database-wide VACUUM in that database.

문서의 표현대로, 이 상태에서는 “only read-only transactions can be started. Operations that modify database records or truncate relations will fail.” 서비스의 모든 쓰기가 멈춘다.

기본값은 autovacuum_freeze_max_age = 2억 트랜잭션이고, 이 나이에 도달하면 autovacuum이 꺼져 있어도 강제로 발동한다(“This will happen even if autovacuum is disabled”).

그래서 무조건 걸어둬야 하는 감시 쿼리

문서가 직접 제시하는 쿼리다.

-- 데이터베이스 단위 XID 나이
SELECT datname, age(datfrozenxid) FROM pg_database;

-- 테이블 단위 (TOAST 포함)
SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY age DESC
LIMIT 20;

이 값을 모니터링에 올리지 않은 PostgreSQL 운영은 시한폭탄이다. 임계치는 환경마다 다르지만, autovacuum_freeze_max_age에 접근하는 추세 자체를 알람으로 잡아야 한다.

VACUUM을 막는 범인 — 열려 있는 트랜잭션

여기가 §11과 이어지는 고리다. VACUUM은 “아직 누군가에게 보일 수 있는” 행 버전을 지우지 못한다. 그러므로 몇 시간째 열려 있는 트랜잭션 하나가 클러스터 전체의 청소를 막는다. 애플리케이션이 BEGIN만 하고 커밋도 롤백도 안 한 채 붙잡고 있는 커넥션(idle in transaction)이 전형적인 범인이다.

증상은 “디스크가 계속 찬다”, “테이블이 비어 있는데 크기가 안 준다”, “wraparound 나이가 안 줄어든다”로 나타난다. 원인은 DB가 아니라 애플리케이션 코드에 있다.

-- PostgreSQL: 오래 열려 있는 트랜잭션 찾기
SELECT pid, state, now() - xact_start AS xact_age, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 20;

7. 커넥션 — 왜 max_connections를 올리면 더 느려지나

PostgreSQL 공식 문서, 아키텍처 기초.

“The PostgreSQL server can handle multiple concurrent connections from clients. To achieve this it starts (“forks”) a new process for each connection.” — PostgreSQL 18 Documentation, 1.2

커넥션 하나 = OS 프로세스 하나. 스레드가 아니다. 그래서 커넥션 수가 늘면 메모리와 컨텍스트 스위칭 비용이 선형 이상으로 붙는다.

실무 결론은 명확하다.

  1. 애플리케이션마다 커넥션 풀을 쓴다. (HikariCP 등)
  2. 그것으로 부족하면 DB 앞에 커넥션 풀러를 둔다. PostgreSQL이면 PgBouncer/Pgpool. 수천 개의 클라이언트 연결을 수십 개의 실제 백엔드 프로세스로 다중화한다.
  3. max_connections를 올리는 건 대개 문제를 뒤로 미루는 것이다. 동시에 실제로 일할 수 있는 수는 CPU와 디스크가 정한다. 그 위로는 큐가 길어질 뿐이다.

“풀 크기는 코어 수 기준 몇 배” 같은 경험칙이 널리 통용되지만, 나는 공식 문서에서 확정적인 권장 공식을 확인하지 못했다. 워크로드(CPU 바운드/IO 바운드)에 따라 달라지므로, 숫자를 옮겨 적기보다 부하 테스트로 처리량이 꺾이는 지점을 직접 찾는 편을 권한다.

서버리스·오토스케일 환경은 이 문제가 특히 날카롭다. 인스턴스가 100개로 늘면 커넥션도 100배가 된다. 애플리케이션 확장성과 DB 커넥션 확장성은 같은 축이 아니다.

8. 백업 — 검증하지 않은 백업은 백업이 아니다

논리 백업과 물리 백업은 다른 물건이다

PostgreSQL 문서가 명시적으로 경고한다.

“pg_dump and pg_dumpall do not produce file-system-level backups and cannot be used as part of a continuous-archiving solution. Such dumps are logical and do not contain enough information to be used by WAL replay.” — PostgreSQL 18 Documentation, 25.3

pg_dump만 돌리고 있다면 PITR(특정 시점 복구)은 불가능하다. 어제 새벽 3시 덤프가 있으면 어제 새벽 3시로만 돌아갈 수 있다. 오늘 오후 2시에 누군가 DELETE를 잘못 돌렸다면, 오전 내내의 정상 트랜잭션도 함께 버려야 한다.

PITR의 구조

PITR은 베이스 백업 + 그 이후의 연속된 WAL 아카이브로 구성된다.

“It is not necessary to replay the WAL entries all the way to the end. We could stop the replay at any point and have a consistent snapshot of the database as it was at that time.”

그리고 순서에 대한 공식 경고가 있다.

“you need a continuous sequence of archived WAL files that extends back at least as far as the start time of your backup. So you should set up and test your procedure for archiving WAL files before you take your first base backup.”

WAL 아카이빙을 먼저 세팅하고 테스트한 다음에 베이스 백업을 떠야 한다. 반대로 하면 연속성이 끊긴 구간이 생겨 그 백업으로는 PITR이 안 된다.

RPO와 RTO를 숫자로 합의하라

용어 뜻 결정하는 것
RPO (Recovery Point Objective) 얼마나 잃어도 되는가 백업/아카이브 주기, 동기 복제 여부
RTO (Recovery Time Objective) 얼마나 빨리 살려야 하는가 백업 방식, 스탠바이 유무, 리허설 여부

이 두 숫자를 경영진과 합의하지 않은 백업 전략은 전략이 아니라 취미다. “가능한 한 자주, 가능한 한 빨리”는 답이 아니다. RPO 0을 원하면 동기 복제 비용을 지불해야 하고, 그건 쓰기 지연으로 청구된다.

그리고 진짜 핵심

복구를 해본 적 없는 백업은 백업이 아니라 백업 파일이다.

정기적으로 실제로 다른 서버에 복구해보고, 걸린 시간을 기록해야 한다. 이 리허설에서만 발견되는 것들:

  • 백업은 되는데 복구 스크립트에 아무도 모르는 수동 단계가 있다
  • 백업 대상에서 어느 순간 빠진 테이블스페이스/스키마가 있다
  • 복구 시간이 합의한 RTO의 5배다
  • 백업 파일 암호화 키를 복구하려는 그 DB에 저장해뒀다

9. 복제와 HA

동기 vs 비동기 — RPO를 정하는 스위치

  커밋 응답 시점 RPO 대가
비동기 프라이머리에만 기록되면 즉시 0이 아님 (지연 구간 유실) 빠름
동기 스탠바이 기록 확인 후 0에 가까움 쓰기 지연 = 네트워크 왕복

선택이지 정답이 아니다. 다만 “복제하고 있으니 데이터는 안전하다”는 말은 비동기에서는 거짓이다. 프라이머리가 죽는 순간 아직 넘어가지 못한 트랜잭션은 사라진다.

복제 지연이 만드는 버그

읽기를 복제본으로 분산하면 필연적으로 만나는 문제.

방금 쓴 걸 바로 못 읽는다 (read-your-writes 위반).

사용자가 프로필을 수정하고 새로고침했더니 예전 값이 보인다. 결제하고 내역을 봤더니 없다. 대응은 셋 중 하나다: (a) 쓰기 직후 일정 시간은 프라이머리로 읽기, (b) 세션 단위로 복제 위치를 추적해 따라잡을 때까지 대기, (c) 애초에 그 화면은 복제본을 안 쓴다.

복제 지연은 반드시 모니터링해야 한다. 지연이 커진 복제본으로 읽기를 보내는 건 조용한 데이터 사고다.

세 가지 흔한 착각

  1. “읽기 복제본이 있으니 백업은 필요 없다.” — 아니다. DROP TABLE도 충실히 복제된다. 복제는 하드웨어 장애 대비이고, 백업은 사람의 실수와 논리적 오류 대비다. 서로를 대체하지 못한다.
  2. “페일오버는 자동이니 괜찮다.” — 스플릿브레인(양쪽이 서로 프라이머리라고 믿는 상태)이 자동 페일오버의 최악 시나리오다. 펜싱(옛 프라이머리 확실히 차단)이 없는 자동 페일오버는 데이터가 두 갈래로 갈라지는 사고를 만든다.
  3. “페일오버 테스트는 위험하니 안 한다.” — 테스트하지 않은 페일오버는 작동하지 않는다고 가정해야 한다. §8의 복구 리허설과 같은 논리다.

10. 무중단 스키마 변경

원칙: expand → migrate → contract

한 번에 바꾸지 않는다. 세 번의 배포로 나눈다.

  1. Expand — 새 컬럼/테이블을 추가만 한다. 옛 코드와 새 코드가 동시에 살 수 있는 상태.
  2. Migrate — 애플리케이션을 새 구조로 전환하고, 기존 데이터를 배치로 옮긴다. 양쪽에 쓰는 기간을 둔다.
  3. Contract — 아무도 안 쓰는 게 확인되면 옛 컬럼을 지운다.

이 순서를 지키면 어느 단계에서든 롤백이 가능하다. 한 번에 바꾸면 롤백이 불가능한 구간이 생긴다.

위험한 DDL과 안전한 대안

작업 위험 대안
인덱스 생성 테이블 쓰기 차단 PostgreSQL CREATE INDEX CONCURRENTLY
컬럼 타입 변경 테이블 전체 재작성 새 컬럼 추가 → 백필 → 스위치 → 삭제
대량 백필 UPDATE 롱 트랜잭션 → VACUUM 정지(§6) 배치를 잘게 쪼개고 중간에 커밋
컬럼 삭제 옛 코드가 SELECT * 중이면 즉시 장애 코드에서 참조 제거를 먼저 배포

MySQL 진영에는 gh-ost, pt-online-schema-change 같은 온라인 스키마 변경 도구가 널리 쓰인다. 도구의 구체적 동작·제약은 이 글에서 검증하지 않았다. 쓰기 전에 각 도구의 공식 문서로 제약 조건(외래키, 트리거 등)을 확인할 것.

제일 자주 밟는 지뢰 — 락 대기 큐

DDL이 락을 기다리는 동안, 그 뒤에 도착한 평범한 SELECT까지 함께 줄을 선다.

긴 SELECT 하나가 돌고 있고 → ALTER TABLE이 그 뒤에서 대기하고 → 그 뒤로 모든 쿼리가 쌓인다. 결과적으로 “가벼운 ALTER 하나”가 전체 서비스를 정지시킨다. 실제 장애 원인으로 매우 흔하다.

대응은 간단하다. DDL 세션에 짧은 락 타임아웃을 건다.

-- PostgreSQL
SET lock_timeout = '3s';
ALTER TABLE ...;   -- 3초 안에 락을 못 잡으면 스스로 실패한다

실패하면 재시도하면 된다. 줄 세우는 것보다 훨씬 낫다.

11. 반드시 보고 있어야 할 지표

지표 왜 확인
XID 나이 임계 도달 시 쓰기 전면 중단(§6) SELECT datname, age(datfrozenxid) FROM pg_database;
롱 트랜잭션 / idle in transaction VACUUM을 막고 블로트를 만듦 pg_stat_activity
복제 지연 지연된 복제본 읽기 = 조용한 사고 pg_stat_replication / SHOW REPLICA STATUS
커넥션 수 / 포화도 프로세스 모델의 한계(§7) pg_stat_activity 카운트
데드락 발생 수 증가 추세 = 접근 순서 설계 문제 pg_stat_database.deadlocks
테이블·인덱스 블로트 디스크와 I/O를 조용히 먹음 크기 추이 + dead tuple 수
캐시 히트율 급락 = 워킹셋이 메모리를 넘음 pg_stat_database
슬로우 쿼리 회귀 탐지 pg_stat_statements / slow query log
디스크 여유 가득 차면 VACUUM FULL조차 못 돌린다 OS 레벨

단일 시점 값이 아니라 추세를 봐야 한다. “지금 80%”보다 “3일 전 40%에서 80%가 됐다”가 훨씬 많은 정보를 담는다.

임계치 수치는 워크로드마다 다르므로 일부러 적지 않았다. 남의 임계치를 복사하는 것보다 자기 시스템의 평상시 값을 먼저 기록하는 게 순서다.

12. 권한·암호화·감사

원칙 몇 가지만.

  • 애플리케이션 계정에 DDL 권한을 주지 않는다. 마이그레이션은 별도 계정으로, 별도 경로로. 이것만으로 “앱 버그가 테이블을 드롭”하는 부류의 사고가 사라진다.
  • 읽기 전용 워크로드는 읽기 전용 계정으로. 분석·대시보드·리포트가 쓰기 권한을 가질 이유가 없다.
  • 전송 구간 암호화(TLS)와 저장 암호화는 다른 문제를 푼다. 전자는 도청, 후자는 디스크·백업 파일 유출. 둘 다 필요하다. 백업 파일 암호화를 빠뜨리는 경우가 특히 많다.
  • 운영 DB에 사람이 직접 붙는 경로를 없앤다. 없앨 수 없으면 누가 언제 무엇을 했는지 남는 경로로만 붙게 한다. 사고 후 원인 추적이 가능한지가 갈린다.
  • 개인정보가 든 운영 데이터를 그대로 개발 환경에 복사하지 않는다. 마스킹/합성 데이터가 기본이다.

금융·의료 등 규제 산업의 구체적 법령 요구사항은 이 글에서 확인하지 않았다. 해당 산업이라면 소관 규정을 직접 확인해야 한다.

마무리 — 순서가 곧 내용이다

이 12개를 다시 한 줄씩 줄이면 이렇다.

  1. DB는 페이지를 읽는다 → 타입이 곧 I/O다
  2. 인덱스는 정렬이다 → 좌측 접두사와 범위 조건을 이해하라
  3. 실행계획은 추정 vs 실제를 본다 → 파티션 부모는 자동 통계 대상이 아니다
  4. 격리수준은 표준과 구현이 다르다 → PG는 READ COMMITTED, InnoDB는 REPEATABLE READ가 기본
  5. 데드락은 예방이 아니라 확률 관리다 → 순서 통일 + 재시도
  6. MVCC엔 청구서가 있다 → VACUUM을 안 돌리면 DB가 쓰기를 거부한다
  7. 커넥션은 프로세스다 → 풀러를 쓰고 max_connections로 도망가지 마라
  8. 복구해보지 않은 백업은 백업이 아니다 → pg_dump만으로는 PITR이 안 된다
  9. 복제는 백업이 아니다 → DROP TABLE도 복제된다
  10. 스키마는 세 번에 나눠 바꾼다 → DDL엔 반드시 lock_timeout
  11. 지표는 값이 아니라 추세다 → XID 나이는 무조건 알람
  12. 앱 계정에 DDL 권한을 주지 않는다

가장 자주 무너지는 지점을 하나만 고르라면 6번이다. 나머지는 느려지는 문제지만, 6번은 멈추는 문제다.

이 글의 한계

  1. PostgreSQL 18과 MySQL 8.4 문서 기준이다. 버전이 다르면 기본값과 동작이 다를 수 있다.
  2. Oracle·SQL Server·NoSQL은 다루지 않았다. 확인하지 않은 것에 대해 쓰지 않았다.
  3. 커넥션 풀 크기 공식, 모니터링 임계치 수치는 일부러 적지 않았다. 공식 문서에서 확정적 권장값을 확인하지 못했고, 워크로드 의존적이기 때문이다.
  4. gh-ost / pt-online-schema-change의 구체적 제약은 검증하지 않았다. 이름만 언급했다.
  5. §1의 타입 선택 판단, §5의 데드락 원칙, §8~12의 실무 원칙은 공식 문서 인용이 아니라 내 판단이다. 인용문이 붙지 않은 문장은 그렇게 읽어야 한다.
  6. 직접 벤치마크를 돌린 결과가 아니다. 성능에 관한 서술은 구조적 설명이지 측정치가 아니다.

References

① 1차·공식 (PostgreSQL 18 Documentation)

② 1차·공식 (MySQL 8.4 Reference Manual)

③ 독자가 직접 확인할 것 (이 글이 수치를 적지 않은 항목)

  • 페이지 크기: SHOW block_size; / SELECT @@innodb_page_size;
  • 커넥션 풀 크기: 자기 워크로드의 부하 테스트
  • 모니터링 임계치: 자기 시스템의 평상시 기준선