PostgreSQL 쿼리 속도 30배 빠르게 만들기(feat. DuckDB)
BEEP은 대형마트, 슈퍼마켓, 편의점 등에서 손쉽게 유통기한을 관리할수 있는 앱이다. 유저가 설정한 날짜 및 시간에 맞춰 유통기한 임박시 앱 푸시로 알려준다.
글로벌 서비스로 하고 있으며 한국보다 해외에서 더 많이 사용하고 있다. 매일 약 5만개의 제품이 신규로 등록되고, 누적으로 등록된 제품수는 약 4천만개다!! 매시간마다 각 팀별 유통기한 임박 상품 개수를 구하고, 1개 이상의 임박 제품이 있다면 팀 멤버 모두에게 푸시 알림을 발송한다.
BEEP의 DB는 PostgreSQL(AWS RDS)를 이용하고 있으며, 처리해야할 데이터가 점점 늘어나다 보니 푸시 보낼 대상을 구하는 쿼리 성능 개선이 필요해졌다.
문제점
초기에는 DB 사이즈가 크지 않아서 Postgres에 직접 쿼리해도 크게 문제 없었다. Master DB에 직접 쿼리하면 서비스 이용에 지장 줄 수 있어, Read Replica DB 인스턴스를 만들어 쿼리를 했다.
최근들어 100ms이내 실행되는 쿼리가 가끔 1분 이상 걸리는 현상이 발생되었다. 순간적으로 많은 IO(Disk Read)로 인해 나오는 이슈로 추측된다. 하지만, Read Replica에서 쿼리가 오래 걸리면 단순 속도만의 이슈가 아니라 아래와 같은 메세지로 실패해버린다.
ERROR: canceling statement due to conflict with recovery.
User query might have needed to see row versions that must be removed.
느린 쿼리를 실행하는 도중 Master DB에서 수정하거나 지운 Row를 반영하다보니, Read Replica에 더이상 데이터가 없어 실행을 못하는것이다.
해결 방법 #1: 쿼리 최적화
첫번째 시도는 당연히 쿼리 최적화다. 구해야할 전체 쿼리를 잘게 쪼개서 쿼리 하거나, 정확한 유통기한 임박 상품 개수를 구하기 보다는 최대 100개까지만 구하는 등의 시도를 했다. 하지만, 이 방법으로는 개선되지 않았다.
Index를 잘 사용하고 있어 효율적이고 대부분 100ms안에 결과가 나오는데, IO Burst로 인한 이슈라 로컬에서 재현도 어렵다.
해결방법 #2: OLAP
Postgres와 같은 DB는 OLTP라고 부르는데 트랜잭션에 최적화되어 count와 같은 집계 쿼리(Aggregation Query)는 느린편이다. 이를 해결하기 위해 ElasticSearch나 Clickhouse같은 OLAP DB를 추가로 구성하는게 정석이다. Postgres를 Source of Truth로 사용하고 ElasticSearch는 read-only DB로 analytical query만 하는 식이다.
다만, OLAP를 도입하려면 Postgres 데이터를 OLAP에 안정적으로 복제(Sync)해야 한다. OLAP로 실시간 복제하는게 그리 쉬운 문제는 아니라서 명확한 니즈가 있지 않은 이상 큰 결단이 필요하다.
추후 데이터가 계속 늘어나면 결국 이 방법으로 가야 한다고 생각한다.
해결 방법 #3: DuckDB
DuckDB는 Analytical Query에 최적화된 Embeddable DB이다. OLAP 버전 SQLite라고 보면 된다. Postgres 에서 Count 쿼리가 느린 반면, DuckDB에서는 어마어마하게 빠르다. Column 기반 Storage, Vectorized 쿼리 처리 때문에 속도가 빠르다고 한다.
2022년 10월 DuckDB의 postgres extension이 공개되었다. DuckDB에서 Postgres를 연결하고 DuckDB에 쿼리하면, Postgres에서 최소한의 데이터만 효율적(binary)으로 가져와 로컬 DuckDB에서 계산 하는식이다.
INSTALL postgres;
LOAD postgres;
CALL postgres_attach('dbname=beep user=postgres host=127.0.0.1', source_schema='public');
-- 이제부터 Postgres DB에 있는 데이터를 쿼리할수 있다.Postgres, DuckDB 연결시 장점
- DuckDB SQL 문법은 Postgres와 동일하여 기존 SQL을 거의 그대로 사용할수 있다.
- 별도 인프라 구축할 필요 없이 기존 서버에 DuckDB를 Embed할수 있다.
Postgres에 직접 쿼리하는 대신 Postgres에 연결된 DuckDB에 동일한 쿼리를 했을뿐인데, 쿼리 속도가 개선되는 마법이다!!
로컬 테스트
맥북 M1 Pro에 Postgres 14를 설치하고 BEEP DB 백업본을 pg_restore로 복구했다. Postgres에서 푸시 보낼 전체 대상을 구하는 쿼리를 실행 했을때 약 300초(5분) 정도가 소요되었다. 제품 테이블은 약 4000만 row이고, join, sub-query등 조금 복잡한 쿼리라 오래 걸린다.
로컬에 DuckDB를 설치하고 로컬 Postgres에 연결하여 동일한 쿼리를 했을때 10초가 소요되었다! 단순계산으로 약 30배가 빨라졌다.
서비스 적용
BEEP서버는 JVM기반인 Clojure로 구현되어 있다. DuckDB는 JDBC 드라이버를 제공하는데 clojure dependency로 duckdb를 추가했다.
duckdb에 Read Replica DB를 연결하여 duckdb를 통해 쿼리를 하도록 변경하였다. 참고로 Postgres에서 가져온 데이터는 Disk에 전혀 쓰지 않기 때문에, 메모리가 많이 필요하다. 기존 512MB 메모리를 1GB로 업그레이드 했다. 추후 데이터가 더 늘어나는 경우 서버 메모리만 늘려주면 된다.
로컬 테스트부터 실제 구현까지 반나절 정도 소요될 정도로 간단한 수정인데, 엄청난 성능 향상이었다. 이제 빠르고 안정적으로 유통기한 알림을 보낼수 있게 되었다. 👍
결론
PostgreSQL를 메인 DB로 사용하고 있는 BEEP 서비스의 쿼리 속도 이슈를 해결하기 위해 DuckDB를 도입하였다. 직접 Postgres에 쿼리하는 대신 duckdb라는 proxy를 도입하여, 쿼리 속도가 약 30배 빨라졌다.
Elaticsearch와 같은 OLAP DB를 도입하려면 데이터 sync 이슈를 해결해야 하는데, duckdb를 이용하면 인프라를 크게 바꾸지 않고 쿼리 속도를 개선할수 있다.
Postgres의 쿼리 속도에 대한 고민이 있다면 duckdb로 속도 개선을 한번 해보길 바란다.
