MySQL vs. PostgreSQL, 선택 기준은 무엇일까
MySQL vs. PostgreSQL, 선택 기준은 무엇일까
개요
대표적인 오픈소스 RDBMS로는 MySQL, PostgreSQL이 있다. 두 데이터베이스에 대한 기술적 차이에 대해 정리한다.
MySQL
MySQL은 데이터를 얼마나 빠르고 간편하게 읽을지에 초점을 맞춘다. 초기 웹 환경은 쓰기보다 읽기 작업의 비중이 높았다. 이에 MySQL은 단순 조회 성능을 극대화하는 전략을 취했다.
MySQL의 가장 큰 특징은 계층적으로 엔진이 분리되어 있다는 점이다. MySQL은 ‘MySQL 서버 엔진 계층’과 ‘스토리지 엔진 계층’으로 분리되어 있다.
MySQL 서버 엔진 계층
MySQL 서버 계층은 주로 접속 처리, 파싱 및 전처리, 최적화, 실행 작업을 수행한다.
클라이언트가 접속을 요청하면 MySQL은 이를 처리하는 쓰레드를 할당한다. 쓰레드 캐시를 사용하여 매번 쓰레드를 생성하지 않고 재사용하여 오버헤드를 줄인다. 또한 사용자 아이디 및 비밀번호를 검증하고, 호스트 접속 권한 확인, SSL 암호화 처리 등을 수행한다.
사용자가 SQL 쿼리문을 보내면 파서가 이를 토큰 단위로 분리하여 트리 형태의 구조를 만든다. 이 단계에서 쿼리 오타를 찾아낸다. 이후 전처리기가 트리 형태의 구조를 바탕으로 테이블, 컬럼 등에 대한 실재성 검사를 수행한다.
이후 옵티마이저가 쿼리문을 가장 효율적으로 처리할 수 있는 실행 계획을 수립한다. CPU 사용량, 디스크 I/O 등을 계산하여 가장 최적인 방법을 선택한다. 옵티마이저는 스토리지 엔진으로부터 데이터 분포도, 레코드 수 등과 같은 통계 정보를 받아 판단하지, 실제로 디스크 I/O를 수행하지는 않는다.
어떤 인덱스를 탈 지, 테이블 조인 순서는 어떻게 할 지, … 등을 결정한다.
실행 엔진은 핸들러 API를 통해 스토리지 엔진에게 결정된 명령을 하달한다. 이후 결과를 클라이언트에게 반환한다.
스토리지 엔진 계층
스토리지 엔진 계층은 실제로 디스크에 존재하는 데이터를 찾아 읽는 역할을 수행한다.
MySQL은 데이터 저장 방식을 하나의 고정된 형태가 아니라, 필요 시 교체할 수 있도록 설계되었다. 서버는 단순히 핸들러 API를 통해 함수를 호출하면 된다. 즉, 이 API 스펙만 맞는다면 새로운 스토리지 엔진을 MySQL에 넣을 수 있는 것이다.
다만 현재는 대부분 기본 엔진인 InnoDB를 사용하므로 엔진 튜닝에 대한 처리를 고려하지 않아도 된다.
InnoDB는 트랜잭션을 지원하여 ACID 속성을 보장하며, 커밋, 롤백 등의 기능을 지원한다. 또한 MVCC로, 누군가 데이터를 수정하고 있어도 다른 사용자는 락 대기 없이 해당 데이터의 과거 버전을 읽을 수 있다.
레코드 단위 락이 가능하며, 클러스터링 인덱스를 지원한다.
클러스터링 인덱스는 테이블의 실제 레코드가 특정 컬럼(보통 PK)에 따라 물리적으로 정렬하여 저장하는 인덱스이다.
MySQL은 단일 프로세스, 멀티 쓰레드 구조이다.
MySQL 서버가 실행 중일 때 mysqld라는 하나의 메인 프로세스만 존재한다. 이 프로세스 안에 글로벌 메모리 영역이 있는데, InnoDB 버퍼 풀과 로그 버퍼가 여기에 존재한다. 모든 쓰레드는 메모리 공간에서 별도의 통신 없이 서로 공유한다.
또한 클라이언트가 접속할 때마다 매번 프로세스를 만드는 것이 아니라, 이미 실행 중인 mysqld 프로세스 내부에서 쓰레드를 하나 배정한다. 사용자의 요청을 직접 처리하는 foreground 쓰레드는 클라이언트가 쿼리를 보내면 쓰레드가 쿼리를 받아 파싱하고 데이터를 가져와 결과를 반환한다.
MySQL은 기본적으로 클라이언트 연결 하나당 쓰레드 하나를 할당한다.
background 쓰레드는 로그를 디스크에 기록하거나 데드락 감지 등의 역할을 수행한다.
이러한 특징 덕분에 OS는 메모리를 절약할 수 있다. OS 입장에서 프로세스를 하나 fork하는 것은 무거운 작업이다. 코드, 데이터, 힙 영역 등을 복사 또는 새로 할당해야 하기 때문이다. 그러나 쓰레드는 프로세스 내의 메모리를 공유하고 자신만의 스택, 레지스터 정보만 가지므로, 연결이 늘어나도 메모리 증가폭이 크지 않다.
또한 문맥 전환 비용이 감소한다. CPU는 한 번에 하나의 작업만 수행할 수 있고, 여러 작업을 번갈아가며 작업을 수행하는데, 이것이 문맥 전환이다. 프로세스 간 전환은 비용이 많이 드는 작업이다. 그러나 같은 프로세스 내에서 쓰레드 간 전환은 비용이 훨씬 저렴하다.
다만 연결이 매우 많아지면 쓰레드 생성 비용 및 스케쥴링 오버헤드가 발생하게 되는데, MySQL 엔터프라이즈 버전은 쓰레드 풀을 지원하여 미리 정해진 개수의 쓰레드만 만들고 들어오는 요청들을 쓰레드가 번갈아가며 처리한다.
PostgreSQL
PostgreSQL은 ANSI-SQL 표준을 가장 엄격하게 따르는 데이터베이스이다. 모호한 문법이나 암묵적인 형 변환을 허용하지 않는다. 이는 시스템의 예측 가능성을 높인다.
속도를 위해 데이터의 정확성을 희생하지 않는다. 강력한 제약 조건과 트랜잭션 격리 수준을 통해 어떤 상황에서도 데이터 정합성이 깨지지 않도록 한다.
또한 사용자가 필요하다면 직접 데이터 타입이나 함수, 연산자, 인덱스 방식까지 만들어 사용할 수 있다.
단순한 RDBMS가 아니라 OOP 개념을 데이터베이스에 도입한 ORDBMS이다.
사용자가 비즈니스 로직을 담은 타입을 정의할 수 있으며, 클래스를 상속받듯 테이블끼리 상속이 가능하다. 또한 관계형 테이블뿐만 아니라 배열, JSON, key-value 저장소 등 NoSQL의 데이터 구조를 테이블 내 컬럼으로 사용할 수 있다.
PostgreSQL은 쓰레드 대신 프로세스 방식으로 동작한다.
PostgreSQL 서버를 띄우면 postmaster라는 데몬 프로세스가 실행된다. postmaster는 직접 쿼리를 처리하지 않고 리스너 역할과 자원 관리를 담당한다.
클라이언트로부터 연결 요청이 들어오면 postmaster는 fork 시스템 콜을 통해 자신을 복제한 새로운 백엔드 프로세스를 생성한다. 즉, 클라이언트와 프로세스가 매핑되는 구조이다.
각 프로세스는 독립된 메모리 공간을 가지므로 프로세스 하나가 죽더라도 다른 프로세스 및 메인 서버에는 영향을 주지 않는다.
다만 프로세스는 쓰레드보다 생성 비용이 비싸도 메모리를 더 많이 차지한다. 따라서 동시 접속자가 증가하면 OOM이 발생할 수 있다. 이를 방지하기 위해 실무에서는 PgBouncer와 같은 커넥션 풀링 미들웨어를 앞단에 설치한다.
주요 기술적 차이점
MySQL은 SQL 표준을 준수하나, 엄격하진 않다. 그러나 PostgreSQL은 엄격하게 ANSI-SQL을 준수한다.
MySQL은 문법 상 오류가 있는 쿼리라도 최대한 결과를 반환하려고 노력한다. 예를 들어 문자열 ‘123’과 숫자 123을 비교할 때 MySQL은 자동으로 타입을 변환하여 계산한다. sql_mode 설정을 통해 표준 준수의 강도를 조절할 수 있다.
반면 PostgreSQL은 데이터 타입에 매우 민감하다. 문자열 ‘123’과 숫자 123을 비교하려고 하면 에러가 발생한다. 특히 GROUP BY 절에 포함되지 않은 컬럼을 SELECT 절에서 집계 함수 없이 호출하면 에러가 발생한다.
MySQL 8.0부터 JSON 데이터 타입을 지원한다. 저장 시 내부적으로 바이너리 포맷으로 변환하여 저장한다. JSON_EXTRACT, -> 연산자 등을 통해 JSON 내부의 특정 필드를 쉽게 조회할 수 있다.
또한 MySQL은 JSON 컬럼 자체에 직접 인덱스를 걸 수 없다. 대신에 JSON 내부의 특정 필드를 추출하는 가상 컬럼을 만들고 그 컬럼에 인덱스를 걸어야 한다.
반면 PostgreSQL은 JSON, JSONB 타입을 지원한다. json 타입은 입력 텍스트를 그대로 저장하므로 조회 시 파싱으로 인해 속도가 다소 느리다.
jsonb 타입에 GIN 인덱스를 설정하면 JSON 문서 내부의 모든 필드와 값에 대해 인덱싱이 가능하다.
또한 컬럼 자체를 배열로 선언할 수 있으며, PostgreSQL의 확장 모듈인 PostGIS로 지리 정보를 저장할 수 있다.
MySQL의 MVCC를 통해 누군가 데이터를 수정 중이어도 다른 사용자가 그 데이터의 이전 버전을 읽을 수 있다. InnoDB 엔진은 데이터를 수정할 때 기존 공간에 데이터를 덮어쓰고, 이전 데이터를 Undo Log에 저장한다.
사용자가 UPDATE 쿼리를 날리면 InnoDB는 실제 데이터 값을 새로운 값으로 덮어쓰고, 이 시점에 다른 사용자가 SELECT 쿼리를 날라면 InnoDB는 Undo Log에 있는 값을 반환한다.
다만 트랜잭션이 매우 긴 경우 이전 데이터는 오랫동안 Undo Log에 남아있게 되므로 성능 저하 문제가 발생할 수 있다.
PostgreSQL은 데이터를 수정할 때 기존 데이터를 건들지 않는다. 새로운 행을 추가하고, 이전 행은 사용하지 않는다는 표시를 남긴다.
UPDATE 쿼리가 실행되면 물리적으로는 INSERT와 유사하게 동작하여 새로운 버전의 튜플을 빈 공간에 저장한다. 기존 튜플에는 xmax(트랜잭션 ID)를 기록한다. 이후 SELECT 쿼리가 실행되면 자신의 트랜잭션 ID와 튜플의 트랜잭션 ID를 비교하여 유효한 튜플을 찾는다.
이전 데이터를 지우지 않으므로 업데이트가 빈번하게 발생하면 테이블의 크기가 커진다. 오래된 튜플들은 디스크 및 메모리 공간을 차지하게 되어 쿼리 성능이 떨어지게 된다.
이러한 문제를 막기 위해 autovacuum 데몬이 백그라운드에서 오래된 튜플들을 정리한다.
성능 비교
복잡한 연산이 없는 단순 조회 쿼리는 MySQL이 더 좋은 성능을 보인다. MySQL의 InnoDB는 기본적으로 클러스터링 인덱스 구조이다. 따라서 PK로 데이터를 찾을 때 인덱스를 타서 데이터를 가져오는 과정이 매우 짧고 효율적이다. 또한 쓰레드 기반 구조 덕분에 쿼리가 매우 많이 발생하는 상황에서도 문맥 전환 비용이 낮기 때문에 응답 속도가 빠르다.
반면 5개 이상의 테이블을 조인하거나 대량의 데이터를 동시에 저장해야 하는 환경에서는 PostgreSQL이 유리하다. PostgreSQL은 Hash Join, Sort Merge Join 등 다양한 조인 알고리즘을 상황에 맞게 사용한다. 또한 MySQL은 기본적으로 하나의 쿼리는 하나의 쓰레드만 사용한다. PostgreSQL은 하나의 쿼리를 처리하기 위해 여러 CPU 코어를 동시에 사용할 수 있다. 대용량 데이터를 집계할 때 효과적이다.
MySQL은 master-slave 구조를 통해 읽기 전용 레플리카를 늘려 트래픽을 분산할 수 있다. 또한 비동기 복제가 기본이라 성능 저하가 적다.
PostgreSQL의 master와 slave는 데이터가 바이트 단위로 일치해서 신뢰성이 매우 높다. 그러나 MySQL에 비해 초기 설정, failover 설정 등이 다소 복잡한 편이다.