[SQL] PostgreSQL vs MySQL

PostgreSQL 도입에 대한 고민

PostgreSQL과 MySQL은 가장 인기 있는 두 가지 오픈 소스 데이터베이스입니다.

아시겠지만 MariaDB는 Oracle이 인수한 후 MySQL 포크 버전으로 보시면 됩니다.

다음은 세 가지의 데이터베이스 인기도를 나타내는 그래프 입니다.

출처 :  https://db-engines.com

우선 제가 관리하고 있는 QR발행 시스템에서의 데이터가 누적되고 있고 데이터 컨트롤 및 통계 데이터의 수행 속도를 개선하고자 대량 데이터 처리에 특화되어있는 PostgreSQL로 관심이 가기 시작했습니다. 고민에 앞서 PostgreSQL과 MySQL 특장점을 살펴보겠습니다.

출처 :  https://www.guru99.com/postgresql-vs-mysql-difference.html

 

MySQL (RDBMS)

• Master-Slave Replication, Scale-Out과 같은 기능 지원
• 대부분 읽기 애플리케이션에 사용될 때 MyISAM 스토리지 엔진으로 매우 낮은 Overhead
• 자주 사용하는 테이블에 대한 메모리 스토리지 엔진 지원

PostgreSQL (ORDBMS)

• 고급 기능을 제공하는 오픈 소스 데이터베이스
• 데이터베이스의 내장 기능, 고급 데이터 분석, 고급 질의 기능 등이 강점
• AES, 3DES 및 기타 데이터 암호화 알고리즘을 지원

기대하고 있는 부분은 PostgreSQL에는 partial index가 있는데. 간단히 요약하자면, 조건을 만족하는 것만 Indexing하기 위해서 쓰입니다. MySQL의 B-tree인덱스와의 차이와 PostgreSQL 만에 Join 알고리즘으로 쿼리에 있어서 성능적인 이점을 기대해 볼 수 있을 듯 합니다.

우선 몇 가지 간단한 비교부터 진행 하겠습니다.

성능 비교

테스트 환경

#**MySQL**
Engine Version - 8.0.32
Instance Class - db.t3.micro

#**PostgreSQL**
Engine Version - 13.10
Instance Class - db.t3.micro

#**공통**
EC2
Instance Class - t3.micro
Ubuntu - 16.04

테이블 및 데이터 세팅

#**MySQL**
테이블 생성
CREATE TABLE `tb_dummy_data` (
	`idx` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'idx',
	`idx_rand` INT(11) NOT NULL DEFAULT '0' COMMENT 'idx',
	`code` VARCHAR(1) NOT NULL DEFAULT '0' COMMENT '1,2' COLLATE 'utf8_general_ci',
	`reg_dt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시',
	PRIMARY KEY (`idx`) USING BTREE
);

#**PostgreSQL**
#SEQUENCE 생성
CREATE SEQUENCE seq;

#테이블 생성
CREATE TABLE public.tb_dummy_data (
	idx int4 NOT NULL DEFAULT nextval('seq2'::regclass),
  idx_rand int4 NULL,
	code varchar NOT NULL,
	reg_dt date NOT NULL DEFAULT now()
);

#**공통**
dummy data - 1,000만건 (Python으로 insert 진행)

컬럼
idx : AUTO_INCREMENT
idx_rand : join을 위한 랜덤 데이터
code : 1,2로 구성된 랜덤 데이터
reg_dt : 등록일

count

MySQL

PostgreSQL

MySQL은 2초 정도 소요되고 PostgreSQL에서는 5초 가 나오네요. (여러 번 해봤지만 비슷한 결과)

Join

MySQL

PostgreSQL

MySQL은 34초, PostgreSQL에서는 18초 가 나오네요. 거의 2배 정도 빠른 차이가 보이네요.

group by

MySQL

PostgreSQL

MySQL은 6초, PostgreSQL에서는 5초 가 나오네요. 이 부분은 거의 비슷한 속도를 보이네요.

Index 작업

PostgreSQL에는 partial index가 있는데. 간단히 요약하자면, 조건을 만족하는 것만 Indexing하기 위해서 쓰입니다. MySQL의 B-tree인덱스와의 차이와 PostgreSQL 만에 Join 알고리즘으로 조회 속도와 인덱스 크기에서 차이가 오지 않을까 기대해 봅니다.

Index 생성

**MySQL
ALTER TABLE `tb_dummy_data`	ADD INDEX `code` (`code`);
ALTER TABLE `tb_dummy_data`	ADD INDEX `idx_rand` (`idx_rand`);

PostgreSQL**
CREATE INDEX tb_dummy_data_code_idx ON public.tb_dummy_data (code);
CREATE INDEX tb_dummy_data_idx_rand_idx ON public.tb_dummy_data (idx_rand);

MySQL

count : 2초 > 1초대 거의 비슷

join : 34초에서 22초로 (약 35% 향상)

group by : 6초에서 2초대로 (약 63% 향상)

PostgreSQL

count : 5초 > 0.8초대 (약 84% 향상)

join : 18초에서 19초로 오히려 index 작업 이후가 1초가 더 상승했네요.

group by : 5초에서 1초대로 (약 80% 향상)

진행하면서 결과가 기대 이상으로 나와서 조금 놀라긴 했는데, PostgreSQL경우 index 작업 이후에 나온 결과를 비춰 봤을때 앞서 설명 드린데로 partial index으로 인해 join 시에는 비슷한 결과가 나오지 않았나 하는 추측이 듭니다. 이번 계기로 Index의 중요성도 또 한번 느끼게 되었습니다.

끝으로

아쉽게도 더 복잡한 쿼리에 대한 테스트는 못해봤지만 기회가 된다면 더 진행해보려 합니다.

테스트 환경 및 데이터 세팅하면서 시행착오도 많이 겪고 애도 먹었지만 그래도 재미있었고 의미 있는 시간 이였습니다.

추가로 진행하면서 느낀점은 MySQL은 쿼리를 실행 할때마다 평균 편차가 큰 편 인거에 비해 PostgreSQL는 거의 일정한 성능 및 속도를 유지해줬다는 점도 확인 할 수 있었습니다. (MySQL의 cache 문제가 아닐까 하는 생각이 듭니다.)

1,000만건 정도는 개인적으로 대용량(?)은 데이터는 아니라고 생각 드는데, 앞으로 대용량을 처리하기 위해 테이블 관리 및 쿼리 실행 속도에 대해 궁굼했던 부분들이 이번 과정을 통해서 어느 정도 해결되었다고 생각되며, 앞서 언급한 QR발행 시스템의 이관 및 앞으로 진행하게 될 대형 프로젝트의 서비스도 진행하게 된다면 PostgreSQL로 사용하는게 좋을 꺼 같다 라는 생각이 듭니다.

추후 이관도 진행하게 된다면 관련 내용도 전달 드리도록 하겠습니다~!

PostgreSQL 도입에 대한 고민

PostgreSQL과 MySQL은 가장 인기 있는 두 가지 오픈 소스 데이터베이스입니다.

아시겠지만 MariaDB는 Oracle이 인수한 후 MySQL 포크 버전으로 보시면 됩니다.

다음은 세 가지의 데이터베이스 인기도를 나타내는 그래프 입니다.

출처 : https://db-engines.com

우선 제가 관리하고 있는 QR발행 시스템에서의 데이터가 누적되고 있고 데이터 컨트롤 및 통계 데이터의 수행 속도를 개선하고자 대량 데이터 처리에 특화되어있는 PostgreSQL로 관심이 가기 시작했습니다. 고민에 앞서 PostgreSQL과 MySQL 특장점을 살펴보겠습니다.

출처 : https://www.guru99.com/postgresql-vs-mysql-difference.html

MySQL (RDBMS)

• Master-Slave Replication, Scale-Out과 같은 기능 지원
• 대부분 읽기 애플리케이션에 사용될 때 MyISAM 스토리지 엔진으로 매우 낮은 Overhead
• 자주 사용하는 테이블에 대한 메모리 스토리지 엔진 지원

PostgreSQL (ORDBMS)

• 고급 기능을 제공하는 오픈 소스 데이터베이스
• 데이터베이스의 내장 기능, 고급 데이터 분석, 고급 질의 기능 등이 강점
• AES, 3DES 및 기타 데이터 암호화 알고리즘을 지원

기대하고 있는 부분은 PostgreSQL에는 partial index가 있는데. 간단히 요약하자면, 조건을 만족하는 것만 Indexing하기 위해서 쓰입니다. MySQL의 B-tree인덱스와의 차이와 PostgreSQL 만에 Join 알고리즘으로 쿼리에 있어서 성능적인 이점을 기대해 볼 수 있을 듯 합니다.

우선 몇 가지 간단한 비교부터 진행 하겠습니다.

성능 비교

테스트 환경

#**MySQL**
Engine Version - 8.0.32
Instance Class - db.t3.micro

#**PostgreSQL**
Engine Version - 13.10
Instance Class - db.t3.micro

#**공통**
EC2
Instance Class - t3.micro
Ubuntu - 16.04

테이블 및 데이터 세팅

#**MySQL**
테이블 생성
CREATE TABLE `tb_dummy_data` (
	`idx` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'idx',
	`idx_rand` INT(11) NOT NULL DEFAULT '0' COMMENT 'idx',
	`code` VARCHAR(1) NOT NULL DEFAULT '0' COMMENT '1,2' COLLATE 'utf8_general_ci',
	`reg_dt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시',
	PRIMARY KEY (`idx`) USING BTREE
);

#**PostgreSQL**
#SEQUENCE 생성
CREATE SEQUENCE seq;

#테이블 생성
CREATE TABLE public.tb_dummy_data (
	idx int4 NOT NULL DEFAULT nextval('seq2'::regclass),
  idx_rand int4 NULL,
	code varchar NOT NULL,
	reg_dt date NOT NULL DEFAULT now()
);

#**공통**
dummy data - 1,000만건 (Python으로 insert 진행)

컬럼
idx : AUTO_INCREMENT
idx_rand : join을 위한 랜덤 데이터
code : 1,2로 구성된 랜덤 데이터
reg_dt : 등록일

count

MySQL

PostgreSQL

MySQL은 2초 정도 소요되고 PostgreSQL에서는 5초 가 나오네요. (여러 번 해봤지만 비슷한 결과)

Join

MySQL

PostgreSQL

MySQL은 34초, PostgreSQL에서는 18초 가 나오네요. 거의 2배 정도 빠른 차이가 보이네요.

group by

MySQL

PostgreSQL

MySQL은 6초, PostgreSQL에서는 5초 가 나오네요. 이 부분은 거의 비슷한 속도를 보이네요.

Index 작업

PostgreSQL에는 partial index가 있는데. 간단히 요약하자면, 조건을 만족하는 것만 Indexing하기 위해서 쓰입니다. MySQL의 B-tree인덱스와의 차이와 PostgreSQL 만에 Join 알고리즘으로 조회 속도와 인덱스 크기에서 차이가 오지 않을까 기대해 봅니다.

Index 생성

**MySQL
ALTER TABLE `tb_dummy_data`	ADD INDEX `code` (`code`);
ALTER TABLE `tb_dummy_data`	ADD INDEX `idx_rand` (`idx_rand`);

PostgreSQL**
CREATE INDEX tb_dummy_data_code_idx ON public.tb_dummy_data (code);
CREATE INDEX tb_dummy_data_idx_rand_idx ON public.tb_dummy_data (idx_rand);

MySQL

count : 2초 > 1초대 거의 비슷

join : 34초에서 22초로 (약 35% 향상)

group by : 6초에서 2초대로 (약 63% 향상)

PostgreSQL

count : 5초 > 0.8초대 (약 84% 향상)

join : 18초에서 19초로 오히려 index 작업 이후가 1초가 더 상승했네요.

group by : 5초에서 1초대로 (약 80% 향상)

진행하면서 결과가 기대 이상으로 나와서 조금 놀라긴 했는데, PostgreSQL경우 index 작업 이후에 나온 결과를 비춰 봤을때 앞서 설명 드린데로 partial index으로 인해 join 시에는 비슷한 결과가 나오지 않았나 하는 추측이 듭니다. 이번 계기로 Index의 중요성도 또 한번 느끼게 되었습니다.

끝으로

아쉽게도 더 복잡한 쿼리에 대한 테스트는 못해봤지만 기회가 된다면 더 진행해보려 합니다.

테스트 환경 및 데이터 세팅하면서 시행착오도 많이 겪고 애도 먹었지만 그래도 재미있었고 의미 있는 시간 이였습니다.

추가로 진행하면서 느낀점은 MySQL은 쿼리를 실행 할때마다 평균 편차가 큰 편 인거에 비해 PostgreSQL는 거의 일정한 성능 및 속도를 유지해줬다는 점도 확인 할 수 있었습니다. (MySQL의 cache 문제가 아닐까 하는 생각이 듭니다.)

1,000만건 정도는 개인적으로 대용량(?)은 데이터는 아니라고 생각 드는데, 앞으로 대용량을 처리하기 위해 테이블 관리 및 쿼리 실행 속도에 대해 궁굼했던 부분들이 이번 과정을 통해서 어느 정도 해결되었다고 생각되며, 앞서 언급한 QR발행 시스템의 이관 및 앞으로 진행하게 될 대형 프로젝트의 서비스도 진행하게 된다면 PostgreSQL로 사용하는게 좋을 꺼 같다 라는 생각이 듭니다.

추후 이관도 진행하게 된다면 관련 내용도 전달 드리도록 하겠습니다~!