PostgreSQL에서 3억개의 원천 데이터를 효율적으로 통계 데이터로 변환하기
데이터 중심 애플리케이션 개발에서, 원천 데이터를 유용한 통계 정보로 변환하는 과정은 핵심적인 작업 중 하나입니다. 특히, 대량의 데이터를 다루는 경우, 이 과정은 성능 최적화와 직결되며, 개발자는 이를 위해 다양한 전략을 고민해야 합니다. 본 글에서는 PostgreSQL을 사용하여 3억 개의 데이터를 효과적으로 통계 데이터로 변환하는 과정을 소개합니다.
오픈월드 MMORPG를 예시로 들겠습니다. 유저들이 어느 장소에서 가장 오래 대기하는지 알아보는 통계를 내려고 합니다.
1. 인덱싱 전략
첫 번째 접근 방법은 데이터에 인덱싱을 적용하는 것입니다. 인덱스는 데이터베이스에서 데이터 검색 속도를 향상시키는 데이터 구조입니다. 특정 컬럼에 대해 생성되어, 빠른 데이터 검색을 가능하게 합니다. 실제로, 인덱싱을 적용한 후 조회 속도가 확연히 개선되었으나, 통계 정보를 추출하는 데에는 여전히 3초 이상이 소요되었습니다.
-- 유저 위치 테이블에 인덱스를 생성하는 코드
CREATE INDEX idx_user_location_id ON user_location (id);
CREATE INDEX idx_user_location_geom ON user_location (geom);
CREATE INDEX idx_user_location_user_count ON user_location (user_count);
2. View 전략
다음으로 고려한 방법은 view를 사용하는 것입니다. View는 데이터를 물리적으로 저장하지 않고, 필요할 때마다 SQL 쿼리를 통해 데이터를 제공합니다. 이 방식은 개발 과정에서 편리하나, 인덱싱 전략과 유사하게, 대량의 데이터를 실시간으로 처리하는 데에는 한계가 있었습니다. View는 결국 데이터를 조회할 때마다 쿼리를 실행하기 때문에, 인덱싱과 큰 차이가 없는 성능을 보였습니다.
-- 유저 위치를 조회하는 쿼리를 담은 View 테이블을 생성하는 코드.
CREATE VIEW vw_user_location AS
SELECT geom, sum(user_count) as sum_user_count
FROM user_location
GROUP BY geom;
3. Materialized view 전략
이에 대한 해결책으로, Materialized view를 선택했습니다. Materialized View는 쿼리 결과를 물리적으로 저장하여 빠르게 데이터를 제공하는 가상 테이블입니다. 주기적으로 갱신되어 데이터의 최신 상태를 유지합니다. 인덱스를 적용할 수 있어 대량의 데이터 조회에 매우 유리합니다. 이 전략을 통해, 원하는 통계 데이터를 Materialized view로 만들면 조회 속도를 3초 이내로 단축시킬 수 있었습니다.
-- 유저 위치를 조회하는 쿼리를 담은 Materialized view 테이블을 생성하는 코드.
CREATE MATERIALIZED VIEW mvw_user_location AS
SELECT geom, sum(user_count) as sum_user_count
FROM user_location
GROUP BY geom;
-- 실제로 일반 테이블과 같이 데이터를 저장하므로, 인덱스를 생성할 수 있습니다.
CREATE INDEX idx_mvw_user_location_id ON mvw_user_location (id);
CREATE INDEX idx_mvw_user_location_geom ON mvw_user_location (geom);
CREATE INDEX idx_mvw_user_location_sum_user_count ON mvw_user_location (sum_user_count);
또한, 원천 데이터가 변경되더라도 간단한 동기화 작업을 통해 최신 상태를 유지할 수 있습니다.
-- Materialized view에 정의된 쿼리에 따라 데이터 동기화.
REFRESH MATERIALIZED VIEW mvw_user_location;
다만, 단점은 아래와 같습니다.
1. materialized view는 쿼리를 변경할 수 없습니다. 변경하려면 view를 drop 후에 다시 쿼리를 짜고 생성해야 합니다. (인덱스가 있다면 인덱스까지)
2. 원천 데이터가 변경되는 경우, 이에 따른 동기화 작업이 필요합니다. (실시간으로 반영되지 않습니다)
3. 실제로 데이터를 저장하기 때문에 추가적으로 디스크를 사용합니다.
결론
하지만, 제 경우에는 원천 데이터가 자주 변경되지 않고, 동기화도 자주 있지 않기에(한 달에 한번 정도) materialized view를 택했습니다.
업무 내용을 공개하기엔 부담이 있어 게임을 비유했는데 설명에 적절한지는 모르겠습니다. 게임사가 어느 좌표에서 얼마나 유저가 머무르는지 관심이 있을지도 모르는 일이구요. 다음에도 제 고민을 게임에 비유해서 글에 녹여보려 합니다.