안정적인 서비스 구축을 위해서는 여러 가지 요소가 뒷받침되어야 하지만 그중 중요한 요소 중 하나는 DB(데이터베이스)입니다. 사용자에게 최적화된 정보를 제공하기 위해서 회원 정보를 저장하거나 사용자가 원하는 데이터를 제공하기 위해 데이터를 저장하는 등 서비스에서 DB의 역할은 큰 비중을 차지하고 있습니다.
웹사이트 검색, 구인 공고 등 여러 가지 기준을 통하여 DB에 대한 각종 통계를 만들어주는 db-engines 에는 현재 385개의 DB에 대한 통계를 내고 있습니다.
DB-Engines Ranking
Popularity ranking of database management systems.
db-engines.com
2025년 5월을 기준으로 전통의 RDBMS 들이 강세를 보이는 것을 알 수 있습니다. 1위부터 5위까지의 데이터 중 눈에 띄는 것은 PostgreSQL 의 Score 상승입니다.
실제로 최근 랭킹을 보면 MySQL과 Oracle은 유지 또는 약간의 하락세를 보이지만 PostgreSQL 은 꾸준히 우상향함을 알 수 있습니다. 둘 다 오픈소스 DB이고, 둘 다 RDBMS이지만 왜 PostgreSQL 은 꾸준히 점유율이 올라가는 걸까요? 오늘은 오픈소스 RDBMS의 대표주자인 MySQL과 PostgreSQL을 비교하고 PostgreSQL의 점유율 증가 원인에 대해서 포스팅해보려고 합니다.
MySQL
먼저 MySQL 에 대해서 알아보겠습니다.
스웨덴 소프트웨어 회사인 MySQL AB에 의해서 1995년에 개발되었습니다. 최초 MySQL AB 소유였지만 2008년 썬 마이크로시스템즈에 인수된 후, 2010년 오라클에 의해 썬 마이크로시스템즈가 인수되면서 현재는 오라클이 소유하고 있습니다.
주요 특징은 다음과 같습니다.
- 오픈 소스: GNU GPL 라이선스를 따르며 누구나 무료로 다운로드하고 사용할 수 있습니다.
- 뛰어난 성능: 최적화된 쿼리 실행 엔진, 다중 스레드 처리, 인덱스 최적화를 통해 빠른 데이터 읽기와 쓰기 성능을 제공합니다.
- 다양한 운영체제 지원: OS에 종속되지 않고 다양한 환경에서 설치 및 운영이 가능합니다.
- 멀티스레드: 멀티스레드 아키텍처를 기반으로 동작하며 동시성 성능이 뛰어납니다.
- 다양한 스토리지 엔진: 다양한 엔진을 지원합니다. 기본 엔진 InnoDB 입니다.
로고가 돌고래인 이유는 MySQL AB의 창업자 중 하나인 울프 미카엘 "몬티" 비데니우스 가 돌고래의 빠르고 영리하고
친절해서 좋아했기 때문에 돌고래로 정했다고 합니다. 돌고래의 이름은 "돌고래 이름 짓기" 콘테스트에서 선정된 "Sakila"입니다.
PostgreSQL
PostgreSQL 은 1994년 캘리포니아대학교 버클리 졸업생인 앤드류 유와 졸리 첸에 의해 처음 Postgres95 라는 이름으로 개발되었고 이후 1996년 PostgreSQL 으로 이름이 변경되었습니다. 이후 전 세계의 데이터베이스 개발자와 자원자들로 구성된 조직을 형성하고 협력하며 시스템의 개발과 유지보수를 하고 있습니다.
주요 특징은 다음과 같습니다.
- 오픈 소스 및 자유로운 라이선스: BSD 라이선스와 MIT 라이선스와 유사한 PostgreSQL 라이선스를 따르며 자유로운 소스 변경 및 배포가 가능합니다.
- ACID 준수 트랜잭션: 제한적인 조건에서 ACID 원칙을원칙을 준수하는 MySQL과 다르게 PostgreSQL은 모든 구성에서 ACID를 준수합니다.
- 객체-관계형 데이터베이스:관계형 데이터베이스(RDBMS)와 객체지향데이터베이스(OODBMS)의 특징을 결합한 객체관계형 데이터베이스입니다.
- 다양한 데이터 타입 지원: 기본적인 데이터 타입들뿐만 아니라 배열, hstore, JSON, JSONB 등을 포함한 다양한 데이터 유형을 지원합니다.
- PL/pgSQL 지원: SQL만으로는 표현하기 어려운 논리적 흐름, 반복문, 조건문 등을 포함한 복잡한 비즈니스 로직을 작성할 수 있는 PL/pgSQL을 지원합니다.
PostgreSQL 의 로고가 코끼리인 이유는 "코끼리는 기억한다"라는 문구에서 착안하여 안정성과 강력함을 어필하기 위해서 정해졌다고 합니다. 코끼리의 이름은 Slonik 으로, 러시아어로 코끼리를 뜻하는 Slon에서 유래되었다는 설이 있습니다.
그럼 어떤 상황에서 MySQL 또는 PostgreSQL 을 사용하는 것이 좋을까요?
- 복잡한 트랜잭션 처리, 고급 쿼리, 사용자 정의 함수 등 다양한 기능을 활용이 필요하다면 PostgreSQL을 사용하는 것이 좋습니다.
- ex) 인터넷 뱅킹 시스템, 주식 거래 플랫폼
- 빠른 읽기 성능이 필요할 경우 읽기 중심의 작업에 최적화되어 있으며 빠른 인덱싱 시스템을 가지고 있어서 상대적으로 속도가 빠른 MySQL을 사용하는 것이 좋습니다.
- ex) 뉴스 포털, 온라인 쇼핑몰
- 동시에 많은 연결이 필요한 경우 수평적 확장이 뛰어난 MySQL을, 단일 노드의 성능 향상이 필요한 경우 수직적 향상이 뛰어난 PostgreSQL을 사용하는 것이 좋다고 알려져 있습니다.
- ex) 쇼핑몰, 실시간 채팅 서비스
- PostgreSQL 은 다양한 기능을 제공하고 고급 기능들을 제공하지만, MySQL에 비해서 상대적으로 적은 레퍼런스를 가지고 있으며 설정 및 관리가 더 복잡하기 때문에 더 높은 역량을 요구합니다.
성능 비교 테스트
그렇다면 실제로는 어떨까요? 실제로 저런 성능의 차이를 보이는지 궁금해서 직접 테스트 해보기로 했습니다. 테스트 환경은 아래와 같습니다.
운영체제: Windows 11 Pro
프로세서: 13th Gen Intel(R) Core(TM) i7-13700HX 2.10 GHz
RAM: 32.0GB
PostgreSQL: 17.4
MySQL: 8.0.42
프로세서: 13th Gen Intel(R) Core(TM) i7-13700HX 2.10 GHz
RAM: 32.0GB
PostgreSQL: 17.4
MySQL: 8.0.42
데이터는 사내 DB에 매일 수집하고 있는 전국 대기질 측정소에 보내주는 일별 대기질 데이터 20년치를 로컬환경에 각각 구축한 MySQL 서버와 PostgreSQL 서버에 복제하여 사용했습니다. (약 7500만 건) 두 테이블의 인덱스 및 파티션 조건은 동일하게 진행했습니다. (연도별로 파티셔닝)
읽기(Read) 테스트
먼저 조회 테스트를 해보겠습니다. 유의미한 수치를 얻기 위해서 최근 5년 동안의 서초구 데이터의 집계를 내는 쿼리를 사용했습니다.
SELECT station_name, AVG(CAST(so2 AS FLOAT)) AS avg_so2
FROM public.air_quality_info
WHERE date BETWEEN '2020-01-01' AND '2025-12-31'
AND station_name = '서초구'
GROUP BY station_name;
결과 - PostgreSQL
GroupAggregate (cost=0.43..72377.45 rows=1 width=18) (actual time=13.878..13.879 rows=1 loops=1)
-> Append (cost=0.43..72062.29 rows=42020 width=16)
(actual time=0.039..7.366 rows=42386 loops=1)
-> Index Scan using p_2020_station_name_date_idx on air_quality_info_p_2020 air_quality_info_1
(cost=0.43..12827.30 rows=7522 width=16)
(actual time=0.038..1.632 rows=8784 loops=1)
Index Cond: (((station_name)::text = '서초구'::text) AND
(date >= '2020-01-01 00:00:00'::timestamp without time zone) AND
(date <= '2025-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using p_2021_station_name_date_idx on air_quality_info_p_2021 air_quality_info_2
(cost=0.56..14267.24 rows=8358 width=16)
(actual time=0.038..1.249 rows=8760 loops=1)
Index Cond: (((station_name)::text = '서초구'::text) AND
(date >= '2020-01-01 00:00:00'::timestamp without time zone) AND
(date <= '2025-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using p_2022_station_name_date_idx on air_quality_info_p_2022 air_quality_info_3
(cost=0.56..14327.55 rows=8372 width=16)
(actual time=0.024..0.975 rows=8760 loops=1)
Index Cond: (((station_name)::text = '서초구'::text) AND
(date >= '2020-01-01 00:00:00'::timestamp without time zone) AND
(date <= '2025-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using p_2023_station_name_date_idx on air_quality_info_p_2023 air_quality_info_4
(cost=0.56..14505.75 rows=8464 width=16)
(actual time=0.014..1.047 rows=8760 loops=1)
Index Cond: (((station_name)::text = '서초구'::text) AND
(date >= '2020-01-01 00:00:00'::timestamp without time zone) AND
(date <= '2025-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using p_2024_station_name_date_idx on air_quality_info_p_2024 air_quality_info_5
(cost=0.43..12299.91 rows=7171 width=16)
(actual time=0.018..0.828 rows=7321 loops=1)
Index Cond: (((station_name)::text = '서초구'::text) AND
(date >= '2020-01-01 00:00:00'::timestamp without time zone) AND
(date <= '2025-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using p_2025_station_name_date_idx on air_quality_info_p_2025 air_quality_info_6
(cost=0.43..3624.42 rows=2133 width=15)
(actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (((station_name)::text = '서초구'::text) AND
(date >= '2020-01-01 00:00:00'::timestamp without time zone) AND
(date <= '2025-12-31 00:00:00'::timestamp without time zone))
Planning Time: 0.534 ms
Execution Time: 13.919 ms
결과 - MySQL
-> Group aggregate: avg(cast(air_quality_info.SO2 as decimal(10,2)))
(cost=107681 rows=82831)
(actual time=99.1..99.1 rows=1 loops=1)
-> Filter: ((air_quality_info.station_name = '서초구') and
(air_quality_info.`date` between '2020-01-01' and '2025-12-31'))
(cost=99398 rows=82831) (actual time=0.0383..93.4 rows=42386 loops=1)
-> Index range scan on air_quality_info using station_date_index over
(station_name = '서초구' AND '2020-01-01 00:00:00' <= date <= '2025-12-31 00:00:00')
(cost=99398 rows=82831)
(actual time=0.0349..73.5 rows=42386 loops=1)
조회 쿼리는 MySQL 이 더 우수할 것으로 예상했지만 결과는 달랐습니다.
|
항목
|
MySQL 실행 계획
|
PostgreSQL 실행 계획
|
|
실행 계획 주요 노드
|
Group aggregate → Filter → Index range scan on station_date_index
|
GroupAggregate → Append → 파티션별 Index Scan
|
|
파티션 활용 여부
|
미활용 (단일 인덱스 사용)
|
활용 (파티션별 쿼리 실행)
|
|
인덱스 사용
|
단일 복합 인덱스(station_date_index)
|
각 파티션별 인덱스(p_YYYY_station_name_date_idx)
|
|
처리된 행 수
(actual rows) |
42,386 rows
|
42,386 rows (각 파티션별 8,784 + 8,760 + 8,760 + 8,760 + 7,321 + 1)
|
|
총 비용 (cost)
|
99,398
|
0.43 ~ 72,377 (파티션별 합산)
|
|
실제 실행 시간
(actual time) |
106 ms (총)
|
12 ms (총)
|
|
계획 수립 시간
(Planning Time) |
미제공
|
0.517 ms
|
|
인덱스 스캔 시간
|
약 77.4 ms (단일 인덱스)
|
파티션별 0.011 ~ 1.101 ms (병렬적 처리 가능)
|
|
필터링 방식
|
필터 후 집계
|
파티션별 필터 후 집계
|
성능 차이의 원인은 PostgreSQL 은 Append 노드를 통해 파티션 테이블을 각각 분리하여 스캔하는 반면, MySQL 은 Filter 노드에서 전체 테이블을 통째로 스캔하는 것으로 보입니다.
따라서 지금처럼 대용량의 데이터 환경에서는 PostgreSQL 을 사용하는 것도 좋은 선택지가 될 수 있음을 알 수 있습니다.
요약
많은 레퍼런스에서 MySQL은 제공하는 기능이 PostgreSQL보다는 적지만 빠른 읽기 속도를 자랑한다는 내용이 지배적이었으나, 실제로 테스트해 본 결과 MySQL의 인덱스를 사용한 읽기 성능보다 PostgreSQL의 파티션을 활용한 읽기 성능이 훨씬 뛰어났습니다.
쓰기(Write) 테스트
그럼 쓰기는 어떤 성능 차이를 보일까요? 읽기 테스트를 진행했던 동일한 DB와 테이블에 진행했고 10만 건의 데이터를 넣는 쿼리를 사용했습니다.
PostgreSQL
DO $$
DECLARE
i INT := 0;
start_time TIMESTAMP := '2025-01-01 00:00:00';
BEGIN
WHILE i < 100000 LOOP
BEGIN
INSERT INTO air_quality_info (station_name, date, observation_type, SO2, CO, O3, NO2, PM10, PM25)
VALUES ('테스트', start_time - INTERVAL '1 hour' * i, '도로변대기', '0.1', '0.1', '0.1', '0.1', '0.1', '0.1');
COMMIT; -- 각 INSERT마다 COMMIT
END;
i := i + 1;
END LOOP;
END $$;
결과
MySQL
DROP PROCEDURE IF EXISTS insert_air_quality_data;
CREATE PROCEDURE insert_air_quality_data()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE start_time DATETIME DEFAULT NOW();
WHILE i < 100000
DO
INSERT INTO air_quality_info (station_name, date, observation_type, SO2, CO, O3, NO2, PM10, PM25)
VALUES ('테스트', DATE_SUB(start_time, INTERVAL i HOUR), '도로변대기', '0.1', '0.1', '0.1', '0.1', '0.1', '0.1');
SET i = i + 1;
END WHILE;
END;
CALL insert_air_quality_data();
결과
PostgreSQL 은 10만 건의 데이터 입력에 6.789 초가 걸렸지만, MySQL 은 약 9분 30초가 걸렸습니다. 생각보다 시간 차이가 너무 커서 더 시간을 줄일 방법이 있는지 찾아봤습니다. 그리고 공식 문서에서 몇 가지 방법들을 찾을 수 있었습니다.
- autocommit 비활성화
- unique_checks 비활성화
- foreign_key_checks 비활성화
MySQL :: MySQL 8.4 Reference Manual :: 10.5.5 Bulk Data Loading for InnoDB Tables
10.5.5 Bulk Data Loading for InnoDB Tables These performance tips supplement the general guidelines for fast inserts in Section 10.2.5.1, “Optimizing INSERT Statements”. When importing data into InnoDB, turn off autocommit mode, because it performs a
dev.mysql.com
MySQL 의 경우 autocommit 옵션이 활성화되어 있으면 INSERT 쿼리가 완료될 때마다 독립적인 트랜잭션으로 간주해 자동으로 커밋이 되기 때문에 10만 개의 트랜잭션이 만들어집니다. 그래서 이 옵션을 잠시 비활성화하여 하나의 트랜잭션에서 작업을 처리할 수 있습니다.
unique_checks 와 foreign_key_checks 는 데이터의 무결성을 체크하는 옵션인데 옵션이 활성화되어 있으면 데이터의 삽입 시 데이터의 무결성을 검사합니다. 데이터양이 많아지면 검사하는 과정이 오버헤드로 작용할 수 있으므로 비활성화하면 성능을 향상할 수 있습니다. 단, 무결성 체크를 비활성화하는 만큼 데이터를 충분히 신뢰할 수 있을 때 설정해야 합니다.
개선된 프로시져는 다음과 같습니다.
DELIMITER //
DROP PROCEDURE IF EXISTS insert_air_quality_data;
CREATE PROCEDURE insert_air_quality_data()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE start_time DATETIME DEFAULT NOW();
DECLARE original_unique_checks INT;
DECLARE original_foreign_key_checks INT;
DECLARE original_autocommit INT;
SELECT @@unique_checks, @@foreign_key_checks, @@autocommit
INTO original_unique_checks, original_foreign_key_checks, original_autocommit;
SET unique_checks = 0;
SET foreign_key_checks = 0;
SET autocommit = 0;
START TRANSACTION;
WHILE i < 100000 DO
INSERT INTO air_quality_info (station_name, date, observation_type, SO2, CO, O3, NO2, PM10, PM25)
VALUES ('테스트', DATE_SUB(start_time, INTERVAL i HOUR), '도로변대기', '0.1', '0.1', '0.1', '0.1', '0.1', '0.1');
SET i = i + 1;
END WHILE;
COMMIT;
SET unique_checks = original_unique_checks;
SET foreign_key_checks = original_foreign_key_checks;
SET autocommit = original_autocommit;
END //
DELIMITER ;
하나의 트랜잭션에서 10만 건의 데이터를 넣으니, 시간이 훨씬 단축되었습니다.
( 9분 29.295초 ➡️ 8.340초)
PostgreSQL 테스트에서 사용했던 BEGIN-END 구문은 트랜잭션의 사용할 때 사용하는 구문인데 현재 쿼리는 개선 전 MySQL 쿼리처럼 10만 개의 트랜잭션을 생성하고 있습니다. 그렇다면 PostgreSQL 도 하나의 트랜잭션에서 작업을 하면 더 빨라질까요?
DO $$
DECLARE
i INT := 0;
start_time TIMESTAMP := '2025-01-01 00:00:00';
BEGIN
WHILE i < 100000 LOOP
BEGIN
INSERT INTO air_quality_info (station_name, date, observation_type, SO2, CO, O3, NO2, PM10, PM25)
VALUES ('테스트', start_time - INTERVAL '1 hour' * i, '도로변대기', '0.1', '0.1', '0.1', '0.1', '0.1', '0.1');
COMMIT; -- 각 INSERT마다 COMMIT
END;
i := i + 1;
END LOOP;
END $$;
약 3초 정도 빨라진 것을 알 수 있었습니다. 시간 차이가 크지 않아서 더 정확한 결과를 얻기 위해 10번의 테스트를 진행해서 평균값을 내봤습니다.
|
실행 번호
|
한 트랜잭션
|
개별 트랜잭션
|
|
Run 1
|
8 s 7715 ms
|
9 s 8781 ms
|
|
Run 2
|
8 s 8036 ms
|
11 s 10890 ms
|
|
Run 3
|
8 s 8349 ms
|
11 s 10719 ms
|
|
Run 4
|
9 s 8949 ms
|
11 s 10815 ms
|
|
Run 5
|
9 s 9128 ms
|
11 s 11008 ms
|
|
Run 6
|
9 s 9031 ms
|
11 s 11453 ms
|
|
Run 7
|
9 s 8951 ms
|
11 s 10717 ms
|
|
Run 8
|
8 s 8498 ms
|
10 s 10453 ms
|
|
Run 9
|
9 s 9435 ms
|
11 s 10571 ms
|
|
Run 10
|
9 s 9069 ms
|
11 s 10814 ms
|
|
평균 시간
|
8 s 2716 ms
|
10 s 4622 ms
|
미세한 차이지만 하나의 트랜잭션에서 작업을 처리하는 쿼리가 더 빠른 성능을 보여주는 것을 알 수 있었습니다.
요약
BULK INSERT 시 MySQL은 입력데이터 건수마다 트랜잭션을 생성하는 반면 PostgreSQL 은 하나의 트랜잭션에서 모든 데이터를 삽입했기 때문에 더 우수한 성능을 보여줬습니다. 하지만 MySQL도 약간의 쿼리 튜닝을 통해 성능을 향상할 수 있었습니다.
추가 테스트
두 가지 테스트로는 조금 부족한 것 같아서 추가 테스트를 진행했습니다. WITH - RECURSIVE 절로 임시테이블을 만든 후 원본 테이블과 JOIN 하여 테이터를 가져오는 쿼리를 사용했습니다.
현재 날짜로부터 1달씩 뒤로 가면서 그 데이터 중 오존 수치가 오존 주의보 수치에 해당하는 0.12 ppm을 넘는 데이터를 조회하는 쿼리입니다.
MySQL
EXPLAIN ANALYZE
WITH RECURSIVE recent_months AS (
SELECT 1 AS step,
'서초구' AS station_name,
DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS date_time
UNION ALL
SELECT step + 1,
station_name,
DATE_SUB(date_time, INTERVAL 1 MONTH )
FROM recent_months
WHERE step < 1000
)
SELECT *
FROM air_quality_info aqi
JOIN recent_months months on aqi.date = months.date_time
WHERE O3 >= 0.3
PostgreSQL
EXPLAIN ANALYZE
WITH RECURSIVE recent_months AS (
SELECT
1 AS step,
'서초구' AS station_name,
date_trunc('hour', CURRENT_TIMESTAMP) AS date_time
UNION ALL
SELECT
step + 1,
station_name,
date_time - INTERVAL '1 month'
FROM recent_months
WHERE step < 1000
)
SELECT *
FROM air_quality_info aqi
JOIN recent_months months on aqi.date = months.date_time
WHERE
o3 != '-'
AND CAST(o3 as float) >= 0.3
MySQL 결과
-> Nested loop inner join (cost=747 rows=226) (actual time=356..12554 rows=209 loops=1)
-> Filter: (months.date_time is not null) (cost=2.9..2.84 rows=3)
(actual time=0.995..1.72 rows=1000 loops=1)
-> Table scan on months (cost=4.19..5.45 rows=2) (actual time=0.995..1.46 rows=1000 loops=1)
-> Materialize recursive CTE recent_months (cost=2.93..2.93 rows=2)
(actual time=0.994..0.994 rows=1000 loops=1)
-> Rows fetched before execution (cost=0..0 rows=1) (actual time=0..0 rows=1 loops=1)
-> Repeat until convergence
-> Filter: (recent_months.step < 1000) (cost=2.73 rows=1)
(actual time=0.0015..0.0832 rows=500 loops=2)
-> Scan new records on recent_months (cost=2.73 rows=2)
(actual time=850e-6..0.0287 rows=500 loops=2)
-> Filter: ((aqi.O3 >= 0.12) and (aqi.`date` = cast(months.date_time as datetime(6))))
(cost=228 rows=75.2) (actual time=11.4..12.6 rows=0.209 loops=1000)
-> Index lookup on aqi using date_station_index (date=months.date_time)
(cost=228 rows=226) (actual time=0.117..12.5 rows=102 loops=1000)
PostgreSQL 결과
Nested Loop (cost=12.49..977361.29 rows=3848471 width=124) (actual time=37.673..603.789 rows=209 loops=1)
CTE recent_months
-> Recursive Union (cost=0.00..2.73 rows=31 width=44) (actual time=0.005..1.344 rows=1000 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=44) (actual time=0.004..0.004 rows=1 loops=1)
-> WorkTable Scan on recent_months (cost=0.00..0.24 rows=3 width=44)
(actual time=0.000..0.001 rows=1 loops=1000)
Filter: (step < 1000)
Rows Removed by Filter: 0
-> CTE Scan on recent_months months (cost=0.00..0.62 rows=31 width=44)
(actual time=0.006..1.603 rows=1000 loops=1)
-> Append (cost=9.76..31497.69 rows=2999 width=81) (actual time=0.549..0.601 rows=0 loops=1000)
-> Bitmap Heap Scan on air_quality_info_p_2001 aqi_1 (cost=9.76..592.48 rows=51 width=92)
(actual time=0.832..0.832 rows=0 loops=12)
Recheck Cond: (date = months.date_time)
Filter: ((o3 <> '-'::text) AND ((o3)::double precision >= '0.12'::double precision))
Rows Removed by Filter: 172
Heap Blocks: exact=2092
-> Bitmap Index Scan on p_2001_date_station_name_idx (cost=0.00..9.75 rows=176 width=0)
(actual time=0.024..0.024 rows=174 loops=12)
Index Cond: (date = months.date_time)
-> Bitmap Heap Scan on air_quality_info_p_2002 aqi_2 (cost=9.91..660.34 rows=57 width=92)
(actual time=0.880..0.880 rows=0 loops=12)
Recheck Cond: (date = months.date_time)
Filter: ((o3 <> '-'::text) AND ((o3)::double precision >= '0.12'::double precision))
Rows Removed by Filter: 193
Heap Blocks: exact=2335
-> Bitmap Index Scan on p_2002_date_station_name_idx (cost=0.00..9.90 rows=196 width=0)
(actual time=0.024..0.024 rows=195 loops=12)
Index Cond: (date = months.date_time)
//중략...
-> Bitmap Heap Scan on air_quality_info_p_2024 aqi_24 (cost=29.50..2240.01 rows=214 width=75)
(actual time=3.949..4.213 rows=2 loops=12)
Recheck Cond: (date = months.date_time)
Filter: ((o3 <> '-'::text) AND ((o3)::double precision >= '0.12'::double precision))
Rows Removed by Filter: 578
Heap Blocks: exact=7168
-> Bitmap Index Scan on p_2024_date_station_name_idx (cost=0.00..29.45 rows=669 width=0)
(actual time=0.091..0.091 rows=599 loops=12)
Index Cond: (date = months.date_time)
-> Bitmap Heap Scan on air_quality_info_p_2025 aqi_25 (cost=24.63..1397.76 rows=186 width=88)
(actual time=2.930..2.930 rows=0 loops=5)
Recheck Cond: (date = months.date_time)
Filter: ((o3 <> '-'::text) AND ((o3)::double precision >= '0.12'::double precision))
Rows Removed by Filter: 355
Heap Blocks: exact=1777
-> Bitmap Index Scan on p_2025_date_station_name_idx (cost=0.00..24.59 rows=572 width=0)
(actual time=0.094..0.094 rows=355 loops=5)
Index Cond: (date = months.date_time)
Planning Time: 2.707 ms
Execution Time: 604.296 ms
|
항목
|
MySQL 실행 계획
|
PostgreSQL 실행 계획
|
|
조인 방식
|
Nested Loop Inner Join (Index Lookup)
|
Nested Loop
|
|
Recursive CTE 처리
|
Materialize recursive CTE로 1000건 생성 (1회 실행)
|
Recursive Union으로 1000건 생성 (1회 실행)
|
|
CTE 결과 접근
|
Table Scan on months
|
CTE Scan on recent_months months
|
|
원본 테이블 접근 방식
|
Index Lookup using
|
파티션별 Bitmap Index Scan + Bitmap Heap Scan
|
|
데이터 분할 구조
|
단일 테이블에서 인덱스를 활용
|
연도별 파티션(air_quality_info_p_YYYY) 총 20개 이상
|
|
루프 수
|
외부 루프 1, 내부 루프 1000 (month 수만큼)
|
외부 루프 1, 내부 루프 1000
|
|
실제 반환 행 수
|
209 rows
|
209 rows
|
|
실행 시간
|
12 s 554ms
|
0 s 603 ms
|
|
I/O 비용
|
Index Lookup 반복 및 필터링
|
Bitmap Index Scan → Heap Scan 반복으로 파티션 병렬 처리 가능
|
PostgreSQL은 파티셔닝된 테이블에서 병렬 처리하여 스캔하기 때문에 짧은 시간이 걸렸지만, MySQL은 Table scan으로 조건에 맞는 데이터를 찾았기 때문에 오랜 시간이 걸렸습니다.
MySQL은 인덱스를 활용하여 단순하며 구조가 단일했지만, 조건이 일치하지 않을 경우 성능저하를 보였으며 Index lookup 비용이 큰 단점이 있었습니다.
반면 PostgreSQL은 파티셔닝된 테이블에서 병렬 최적화 기능 및 효율인 Bitmap Scan을 통해 우수한 성능을 보였지만 파티션이 많아질수록 Bitmap I/O 비용 증가 및 파티션의 관리가 필요하다는 단점이 있었습니다.
결론
데이터베이스의 버전이나 서버의 성능에 따라 약간의 차이가 있겠지만, 파티셔닝과 인덱스 조건이 동일한 경우 대부분 PostgreSQL 이 더 뛰어난 성능을 보였습니다. 만약 테이블에 적절한 인덱스 생성과 파티셔닝이 되어있다면 PostgreSQL 이 MySQL보다 더 뛰어난 성능을 자랑할 것으로 예상됩니다.
여담
PostgreSQL의 점유율 증가의 또 다른 이유 중 하나는 다양한 Extension 들을 지원하기 때문입니다. 그중 하나는 Microsoft의 자회사인 Citus Data에서 만든 citus라는 Extension입니다. Citus는 CockroachDB나 Google Spanner처럼 SQL을 분산 DB로 사용할 수 있도록 해주는 Extension입니다. Citus의 주요 특징은 다음과 같습니다.
- 테이블을 샤딩해서 여러 개의 데이터 노드로 분산시키고 coordinate 노드 분산 처리하여 더 빠른 시간 안에 실행할 수 있음
- PostgreSQL의
- 수평확장이 약한 PostgreSQL의 단점을 Citus의 병렬 쿼리 처리로 보완할 수 있음
- 다양한 샤딩 전략 및 고가용성 구성이 용이함
Citus Data | Distributed Postgres. At any scale.
Citus gives you all the greatness of Postgres plus the superpowers of distributed tables. By distributing your data and queries, your application gets high performance—at any scale. The Citus database is available as open source and as a managed service
www.citusdata.com
'Tech lab' 카테고리의 다른 글
| AI를 제조업에서 사용하다면? (feat. Cropper) (2) | 2025.05.09 |
|---|---|
| 데이터 파이프라인 모니터링 시스템 구축 (0) | 2025.04.04 |
| n8n에서 파이썬(Python)을 활용하여 데이터파이프라인 구축하기 (1) | 2025.03.28 |
