이번 게시글에서는 MySQL과 PostgreSQL을 공부하며 흥미롭게 접한 내용을 정리하고자 한다. 흔히 볼 수 있는 내용보다는 흥미로운 사실들만 모아 작성했으므로, 다소 가독성이 떨어질 수 있다.

1. 아키텍처

MySQL

MySQL 서버는 스레드 기반으로 동작하며, 포그라운드 스레드와 백그라운드 스레드로 나뉜다.

• 포그라운드 스레드는 접속된 클라이언트 수만큼 존재하며, 스레드 풀과 유사한 방식으로 동작한다. 클라이언트 요청이 들어오면 기존 스레드를 재사용해 성능을 최적화하며, 요청 처리에 유연하게 대처할 수 있다.
• 백그라운드 스레드는 버퍼에서 데이터를 읽고 디스크에 기록하는 작업을 수행한다. 이때 성능 향상을 위해 데이터는 먼저 버퍼에 저장되고, 이후 디스크에 쓰여진다. 이는 데이터의 입출력 성능을 높이기 위한 중요한 설계이다.

쿼리 파서, 전처리기, 옵티마이저, 쿼리 실행기는 MySQL 엔진 영역에서 수행되고, 데이터의 읽기/쓰기 작업은 스토리지 엔진에서 수행된다.

• 쿼리 파서는 SQL 문을 파싱하여 구문을 분석하고, 전처리기는 쿼리의 정확성을 검증하며, 옵티마이저는 효율적인 실행 계획을 수립한다. 이후 쿼리 실행기는 스토리지 엔진에 명령을 전달하여 데이터를 처리한다.
• 이러한 구조 덕분에 MySQL 엔진은 다양한 스토리지 엔진(InnoDB, MyISAM 등)과 호환되며, 각 스토리지 엔진의 특성에 따라 데이터 처리 방식만 다를 뿐, MySQL 엔진의 기본적인 동작 방식은 동일하게 유지된다.

PostgreSQL

PostgreSQL은 프로세스 기반으로 동작하며, PostMaster라는 데몬이 실행되어 있다가 사용자의 요청이 들어오면 Backend Process라는 프로세스를 생성한다.

• PostMaster는 PostgreSQL 서버의 메인 프로세스로, 클라이언트 연결을 관리하고 새로운 클라이언트 요청이 들어올 때마다 Backend Process를 생성한다. 이 프로세스는 클라이언트와의 세션을 유지하며, SQL 쿼리 처리, 데이터 읽기 및 쓰기를 수행한다.
• PostgreSQL이 MySQL과 다르게 프로세스 기반으로 동작하는 이유는 안전성을 중시하는 설계 때문이라고 생각한다. 각 연결이 독립적인 프로세스로 동작하므로, 특정 프로세스에서 오류가 발생해도 다른 연결에는 영향을 미치지 않는다.

2. Undo와 Redo

MVCC를 이해하기 위해 Undo와 Redo에 대해 간략히 정리해보자.

Undo

트랜잭션과 격리 수준을 보장하기 위해 DML(Select 제외)로 변경되기 이전 데이터를 백업한 것을 Undo 로그라고 한다. 이를 통해 두 가지 작업을 수행한다.

• 트랜잭션이 롤백될 때 Undo 로그를 사용해 데이터를 복구한다.
• 격리 수준에 따라 Undo 로그의 데이터를 읽어 이전 상태를 재현할 수 있다. 이는 MVCC에서 중요한 역할을 한다.

• 참고로 트랜잭션이 오랜 시간 유지되면 Undo 로그 크기가 커질 수 있다. 이 자체는 큰 문제가 아니지만, Undo 로그가 커질수록 데이터 검색 시 Undo 로그를 탐색하는 시간이 늘어나 성능 이슈로 이어질 수 있다.
• 이러한 이유로 PostgreSQL은 Undo 로그와 비슷하지만 다른 방식으로 MVCC를 지원하며, 이 차이점은 뒤에서 다룬다.

Redo

프로그램 비정상 종료 시 데이터 변경 내용을 로그로 저장해 복구하는 것을 Redo 로그라고 한다.

• 데이터가 커밋되었지만 디스크에 기록되지 않은 상태에서 시스템이 종료될 경우, Redo 로그를 사용해 복구할 수 있다.
• 디스크에 직접 데이터를 저장하면 안전하지만, 데이터베이스는 랜덤 I/O를 꺼리기 때문에, 한번에 데이터를 저장하기 위해 버퍼에 기록하고 백그라운드 스레드가 이를 디스크에 저장한다. 이 과정에서 프로그램이 백그라운드 스레드가 작업하기 전에 종료될 가능성에 대비해 Redo 로그가 필요하다.

프로그램 종료 시 처리해야 할 상황은 두 가지가 있다.

• 커밋되었지만 디스크에 기록되지 않은 경우, Redo 로그를 사용해 데이터를 복구한다.
• 롤백되었지만 디스크에 기록된 경우에는 Redo 로그로 진행 상태를 확인하고, 롤백된 데이터를 Undo 로그를 사용해 복구한다.

참고로 Redo 로그는 변경된 내용만 기록하여 공간 사용을 최소화하고, 로그의 크기를 효율적으로 관리한다.

3. MVCC

MVCC는 다중 버전 동시성 제어의 약자로, 기존 Lock 기반 동시성 제어 방식에서 발생하는 문제를 해결하기 위해 고안된 기법이다. 여러 버전을 관리해 읽기 Lock에서 다른 쓰기가 가능하고, 쓰기 Lock에서 다른 읽기가 가능하게 한다.

MySQL

MySQL에서의 MVCC는 앞에서 설명한 대로 Undo 로그를 사용한다. 이와 관련해 궁금했던 몇 가지를 정리해보자.

• READ COMMITTED와 REPEATABLE READ 둘다 MVCC를 사용하는데, 왜 READ COMMITTED에서만 NON-REPEATABLE READ가 발생할까?

  이는 READ COMMITTED와 REPEATABLE READ가 참조하는 Undo 영역의 범위가 다르기 때문이다. READ COMMITTED는 다른 트랜잭션이 커밋한 최신 데이터를 읽는 반면, REPEATABLE READ는 트랜잭션 시작 시점에 백업된 레코드를 참조해 같은 데이터를 반복적으로 읽는다.

• MVCC를 사용하는데도 REPEATABLE READ에서 PANTOM READ가 발생할 수 있는 이유는?

  일반적으로 MVCC를 사용하는 REPEATABLE READ에서는 PANTOM READ가 발생하지 않는다. 하지만 SELECT ... FOR UPDATE를 사용하는 경우에는 발생할 수 있다. SELECT ... FOR UPDATE는 쓰기 Lock을 설정해 현재 레코드에 Lock을 걸지만, Undo 영역에는 Lock을 설정할 수 없기 때문에 새로운 데이터 삽입이 가능해지는 경우다.

• MySQL의 REPEATABLE READ에서는 PANTOM READ가 발생하지 않는다고 들었는데?

  이는 InnoDB의 Next-Key Lock 덕분이다. Next-Key Lock은 SELECT ... FOR UPDATE로 가져오는 레코드뿐만 아니라 그 인접한 레코드에 대한 잠금을 설정해, 해당 범위에 새로운 데이터가 삽입되지 않도록 한다. 이로 인해 PANTOM READ를 방지할 수 있지만, 이러한 Locking 방식은 데드락 문제를 유발할 수 있어 현업에서는 REPEATABLE READ를 신중하게 사용하는 경우가 많다.

PostgreSQL

PostgreSQL의 MVCC는 Undo 로그를 사용하지 않고, 데이터를 직접 Page에 기록하며, 각 레코드는 xmin, xmax 값을 사용해 자신의 트랜잭션에 맞는 데이터를 읽는다. 이와 관련해 궁금했던 부분을 정리해보자.

• 용량 문제는 없을까?

  물론 문제다. PostgreSQL은 Page 내에 이전 버전의 데이터가 남아 있게 되고, 이러한 Dead tuple들이 쌓이면서 디스크 공간을 차지한다. 이를 해결하기 위해 Java의 GC와 유사한 AutoVacuum 작업을 수행해 불필요한 데이터를 정리한다. AutoVacuum은 Dead tuple의 임계치에 도달했을 때 실행되지만, 이 작업만으로는 테이블 크기가 줄어들지는 않는다. 테이블 크기를 줄이려면 Exclusive Lock을 사용해 Fully Vacuum을 수행해야 하지만, 이는 해당 테이블에 DML 작업이 불가능하다는 단점이 있다.

• 왜 이렇게 구현했을까?

  PostgreSQL의 MVCC 방식은 Undo 로그를 사용하는 MySQL과 비교해 성능상의 장단점이 있다. 장점은 각 트랜잭션이 필요한 데이터를 Page 내에서 직접 참조할 수 있어, Undo 로그를 IO 작업으로 읽는 것보다 빠르다는 점이다. 또한, 롤백 시 Undo 로그를 사용해 데이터를 복구할 필요가 없어서 롤백 처리 속도가 빠르다. 단점으로는 대량의 Dead tuple이 쌓이면서 디스크 공간을 많이 차지하고, 주기적으로 Vacuum 작업을 수행해야 한다는 점이 있다. 이러한 점으로 롤백이 많이 발생하는 상황이라는 전제에서는 이점이 있지만, 대부분의 시스템에서 이런 경우는 드물다.

4. Index

일반적인 내용은 생략하고, 공부하며 흥미로웠던 부분만 정리해보자.

• 인덱스를 저장하기 위해 B-Tree를 사용하는 이유는?

  Binary Search Tree는 불균형 문제로 인해 성능이 좋지 않다. 예를 들어, 삽입 순서에 따라 한쪽으로 치우칠 수 있어, 검색 시 최악의 경우 선형 시간(O(N))이 걸릴 수 있다. Red-black Tree는 균형을 유지하지만, B-Tree는 노드 하나에 여러 개의 데이터를 저장할 수 있어 디스크 접근 횟수를 줄이고, 대량의 데이터를 저장할 때 더 효율적이다. 이로 인해 대용량 데이터베이스에서는 B-Tree가 선호된다.

• Hash Index를 사용하면 되지 않나?

  Hash Index는 특정 상황에서는 매우 빠르다. 삽입, 삭제, 조회의 시간 복잡도가 O(1)이기 때문에 동등 비교에 특화되어 있다. 그러나 범위 검색이 불가능하고, 데이터가 균일하게 분포하지 않는 경우 성능 저하가 발생할 수 있다. 이러한 한계 때문에 범위 검색이 빈번한 상황에서는 B-Tree가 더 유리하다.

• B+ Tree는 어떤가?

  B+ Tree는 B-Tree와 유사하지만, 리프 노드에만 데이터를 저장하고 리프 노드들이 순차적으로 연결되어 있어 범위 검색 시 매우 빠르다. 이러한 특성 덕분에 전체 조회와 범위 조회에서 B-Tree보다 빠르며, 리프 노드를 제외하고 데이터를 담아두지 않기 때문에 메모리를 더 확보함으로써 더 많은 key들을 수용할 수 있어 트리의 높이가 짧아진다. 이러한 이점 때문에 InnoDB에서는 B+ Tree를 사용한다.

MYSQL

Clustered Index (Primary Key)

클러스터링 인덱스는 리프 노드에 레코드의 모든 컬럼이 저장되어 있다. 이로 인해 데이터 자체가 인덱스의 일부가 되므로, 인덱스를 사용해 데이터를 빠르게 검색할 수 있다. 다만, 클러스터링 인덱스는 테이블당 하나만 가질 수 있으며, 데이터 삽입 시 정렬된 상태를 유지해야 하므로 삽입/삭제 시 추가적인 오버헤드가 발생할 수 있다.

Non-Clustered Index (Secondary Index)

논 클러스터링 인덱스는 리프 노드에 물리적 주소 대신 PK를 저장하므로, 클러스터링 인덱스를 통해 실제 레코드에 접근한다. 즉, 인덱스를 이용해 검색한 후, 해당 PK를 사용해 클러스터링 인덱스를 통해 데이터를 가져오는 방식이다. 이로 인해 추가적인 I/O가 발생할 수 있지만, 검색 시 필요한 데이터를 빠르게 찾을 수 있다는 장점이 있다.

• Clustered Index와 Non-Clustered Index를 이렇게 구성한 이유는?

  모든 인덱스에서 레코드의 모든 컬럼을 저장할 경우, 하나의 레코드가 변경되면 모든 인덱스에 변경 사항을 반영해야 한다. 이로 인해 성능에 큰 영향을 미칠 수 있다. 따라서 Non-Clustered Index는 PK를 통해 레코드 위치만 참조하도록 설계되었으며, 이 방식은 변경 작업 시 인덱스 유지 비용을 줄이는 데 유리하다.

Index Range Scan

일반적으로 인덱스를 사용한다면 Index Range Scan을 사용하게 된다. 이는 인덱스를 이용해 특정 범위의 데이터를 탐색하는 방식이다.

• Index Range Scan을 사용하지 않을 때가 있는데?

  Index Range Scan은 레코드를 읽을 때 랜덤 I/O가 발생하기 때문에 데이터베이스는 이를 피하려고 한다. 일반적으로 쿼리에서 20% 이상의 데이터를 읽어야 하는 경우, 인덱스를 사용하는 대신 Full Table Scan을 수행하는 것이 더 효율적일 수 있다. 이는 인덱스를 통해 레코드를 찾는 과정에서 발생하는 랜덤 I/O보다, 테이블 전체를 순차적으로 읽는 방식이 더 빠르기 때문이다.

Index Full Scan

인덱스를 처음부터 끝까지 읽는 방식이다.

• 이걸 언제 쓰지?

  테이블을 읽는 것보다 인덱스를 읽는 게 더 빠르다고 판단될 때 사용한다. 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닐 경우, Index Full Scan을 수행해 인덱스를 이용해 데이터를 검색한다. 또한, 인덱스만으로 쿼리를 해결할 수 있는 경우에도 Index Full Scan이 유리할 수 있다.

커버링 인덱스

간단히 말해 인덱스만으로 쿼리를 해결하므로 테이블 접근을 하지 않는 것이다. 예를 들어, 쿼리에서 필요한 모든 컬럼이 인덱스에 포함되어 있다면, 인덱스를 통해 바로 데이터를 조회하고 테이블에 접근할 필요가 없다.

• 내가 설정한 Non-Clustered Index에 PK가 포함되지 않았는데?

  앞에서 설명한 대로 Non-Clustered Index는 기본적으로 클러스터링 인덱스를 사용해 실제 레코드에 접근한다. Non-Clustered Index에는 레코드의 위치 정보가 포함되어 있지 않기 때문에, 검색한 후 실제 데이터를 가져오려면 클러스터링 인덱스를 통해 접근해야 한다. 이로 인해 Non-Clustered Index를 설정할 때, PK가 포함되지 않은 인덱스라도 PK는 기본적으로 제공된다.

커버링 인덱스는 실제 테이블에 접근하지 않는 다는 점에서 데이터베이스가 굉장히 좋아할만한 인덱스이다.

Off The Record

개인적으로는 PostgreSQL을 더 선호한다. 다른 RDBMS와는 다르게 여러 문제를 고유한 방식으로 해결하려는 접근이 개발자로서 감탄을 자아내기 때문이다.

반면, MySQL은 완전히 무료가 아니라는 점, 다양한 엔진 지원으로 인한 복잡함, 그리고 PostgreSQL보다 ANSI/ISO 표준 지원이 부족하다는 점에서 큰 매력을 느끼지는 못한다.

하지만 단순 CRUD 쿼리에서 MySQL이 뛰어난 성능을 보여주고, 참고 자료가 많다는 점을 고려하면 학생 수준의 프로젝트에서는 MySQL을 사용하는 것이 더 적절하다고 생각한다.

그래도 돌고래 보다는 코끼리지