통계 집계를 PostgreSQL로 옮겨 전송량을 최대 99% 줄이고 집계 누락 해결하기

글을 시작하기에 앞서 DF Sheet를 간단히 소개해 보려고 한다.

DF Sheet는 우리 회사에서 사용하는 이슈 관리 및 협업 도구다.
Jira처럼 업무를 등록하고 진행 상황을 공유하는 역할을 한다.

개발을 시작한 계기는 2025년 말부터 2026년 초까지 진행했던 기아 프로젝트 QA였다.
당시에는 구글 스프레드시트로 QA 이슈를 관리했는데, 이슈가 쌓일수록 불편함이 커졌다.

변경 이력과 댓글, 이슈의 진행 상태, 첨부 이미지를 한눈에 확인하기 어려웠다.
특히 누가, 언제, 어떤 방식으로 문제를 해결했는지 추적하기가 쉽지 않았다.

이슈를 기록하는 것에서 나아가 해결 과정까지 함께 관리할 수 있는 도구가 필요했다.

이런 불편함을 해결하기 위해 DF Sheet를 만들었다.
처음에는 웹으로만 제공했지만, 현재는 Electron 기반의 데스크톱 앱으로도 배포해 데스크톱 알림을 받을 수 있도록 했다.

도입 이후 업무와 브랜치 관리가 한결 수월해졌다는 피드백을 받고 있다.
실제 업무에서 겪었던 불편을 해결하려고 만든 도구가 동료들에게도 도움이 되고 있다는 점에서 보람을 느낀다.

이제 본론으로 들어가 보자.

---

DF Sheet의 관리자 대시보드에는 일별 활동 추이, 활동 유형별 비율, 시간대별 활동량, 사용자별 활동 순위가 표시된다.

기존에는 API 서버가 데이터베이스에서 원본 로그를 가져온 뒤 JavaScript로 통계를 계산했다. 구현을 이해하기 쉽고, 응답 형식을 바꾸기도 편한 구조였다.

하지만 이 경로를 점검하면서 두 가지 문제를 발견했다. 차트를 그리는 데 필요한 정보보다 훨씬 많은 데이터를 가져오고 있었고, 조회 결과의 행 제한 때문에 일부 통계는 전체 데이터를 반영하지 못하고 있었다.

이 글에서는 집계 위치를 애플리케이션에서 PostgreSQL로 옮긴 과정과, 그 결과 무엇이 개선됐는지 정리한다.

1. 문제 발견: 작은 차트를 그리기 위해 원본 로그를 가져오고 있었다

관리자 활동 차트가 최종적으로 필요로 하는 데이터는 다음과 같다.

• 날짜별 활동 유형과 발생 횟수
• 조회 기간의 활동 유형별 합계
• 0시부터 23시까지의 활동량
• 활동이 많은 사용자 상위 10명

반면 기존 API는 이러한 결과를 만들기 위해 원본 활동 로그를 두 번 조회했다.

아래는 개선 전 코드의 일부다.

const [allLogsRes, topUsersRes] = await Promise.all([
  supabase
    .from("user_activity_logs")
    .select("activity_type, created_at, user_id, users(id, user_id, name)")
    .gte("created_at", periodFrom)
    .lt("created_at", periodTo)
    .order("created_at", { ascending: true })
    .limit(10_000),

  supabase
    .from("user_activity_logs")
    .select("user_id, users(id, user_id, name)")
    .gte("created_at", periodFrom)
    .lt("created_at", periodTo)
    .limit(50_000),
]);

첫 번째 조회 결과로 날짜별, 유형별, 시간대별 통계를 계산하고, 두 번째 조회 결과로 사용자별 활동 횟수를 계산했다.

두 요청은 Promise.all로 병렬 실행하고 있었지만, 각 요청에서 원본 로그를 가져온다는 점은 같았다. 사용자 정보도 개별 로그 행에 함께 포함됐다.

당시 측정한 두 조회의 JSON 본문 크기는 합계 380,661바이트였다. 화면에 필요한 것은 집계된 숫자였지만, API 서버까지 이동하는 데이터는 개별 활동 기록이었다.

조회한 행 수를 확인하는 과정에서는 정확성 문제도 발견했다. 당시 운영 환경의 PostgREST 응답 상한은 요청당 1,000행이었다. 코드에 작성한 limit(10_000)과 limit(50_000)만큼 데이터가 반환되지 않았다.

전체 로그가 5,449건인 상황에서도 두 조회는 각각 1,000행까지만 반환됐다. 정상 응답을 받았다는 이유만으로 전체 데이터를 집계했다고 볼 수 없는 상태였다.

여기서 두 응답의 합계인 2,000행은 서로 다른 로그 2,000건을 의미하지 않는다. 조회 결과가 서로 겹칠 수 있으며, 핵심은 어느 조회도 전체 로그를 대상으로 한 집계를 보장하지 못했다는 점이다.

2. 원인: 원본 데이터 조회와 통계 계산을 같은 방식으로 다루고 있었다

기존 처리 흐름은 다음과 같았다.

PostgreSQL에서 원본 로그 조회
→ PostgREST를 통해 API 서버로 전송
→ JavaScript에서 날짜와 유형별 분류
→ 횟수 계산과 사용자별 정렬
→ 차트용 응답 반환

이 구조에서는 로그가 늘어날수록 API 서버가 받아야 하는 데이터와 순회해야 하는 행이 함께 증가한다.

실제 집계 코드도 조회한 로그를 하나씩 순회하는 방식이었다.

const type = log.activity_type as string;
typeBreakdown[type] = (typeBreakdown[type] ?? 0) + 1;

const row = dailyMap.get(kstDate);

if (row) {
  row[type] = ((row[type] as number) ?? 0) + 1;
}

이 계산 자체가 복잡한 것은 아니다. 문제는 계산에 필요한 원본 데이터를 모두 API 서버로 가져와야 한다는 점이었다.

정확성 문제도 같은 구조에서 발생했다. 조회 결과가 일부 잘리더라도 JavaScript 집계는 정상적으로 실행된다. 다만 계산 대상이 전체 로그가 아닌 조회된 일부 로그가 된다.

예외가 발생하지 않고 차트도 그려지기 때문에, 응답 성공 여부만 확인해서는 누락을 알아차리기 어려웠다.

페이지네이션으로 전체 로그를 가져오면 누락 문제에 대응할 수는 있다. 하지만 원본 로그를 계속 전송하고 API 서버에서 집계하는 비용은 남는다.

이 화면에 필요한 것은 개별 로그 목록이었는지, 집계 결과였는지부터 다시 구분할 필요가 있었다.

3. 해결: PostgreSQL에서 집계하고 결과만 반환하도록 변경했다

집계 책임을 PostgreSQL로 옮기고, API 서버는 집계 결과를 화면의 응답 형식으로 변환하도록 변경했다.

PostgreSQL에서 기간 필터링과 집계
→ 집계 결과를 JSON으로 반환
→ API 서버에서 응답 형식 정리
→ 차트용 응답 반환

날짜별, 유형별 계산을 SQL로 이동

활동 차트용 함수인 get_admin_activity_charts에서 날짜별 집계, 활동 유형별 합계, 시간대별 분포, 사용자 순위를 계산하도록 구현했다.

아래는 실제 함수의 CTE를 바탕으로 일별 집계 부분만 반환하도록 축약한 코드다. p_from과 p_to_exclusive는 함수의 조회 기간 매개변수다.

* CTE(Common Table Expression)는 WITH 구문으로 이름을 붙여 하나의 SQL 문 안에서 참조하는 임시 결과 집합이다.
복잡한 쿼리를 단계별로 나누어 읽기 쉽게 만든다. 아래 예제에서는 filtered가 조회 기간의 로그를 준비하고, daily_counts가 날짜와 활동 유형별 건수를 집계한다.
참고: PostgreSQL 공식 문서

WITH filtered AS MATERIALIZED (
  SELECT
    logs.user_id,
    logs.activity_type::text AS activity_type,
    (logs.created_at AT TIME ZONE 'Asia/Seoul')::date AS date_kst,
    EXTRACT(
      hour FROM logs.created_at AT TIME ZONE 'Asia/Seoul'
    )::integer AS hour_kst
  FROM public.user_activity_logs AS logs
  WHERE logs.created_at >= p_from
    AND logs.created_at < p_to_exclusive
),
daily_counts AS (
  SELECT
    filtered.date_kst,
    filtered.activity_type,
    COUNT(*)::bigint AS event_count
  FROM filtered
  GROUP BY filtered.date_kst, filtered.activity_type
)
SELECT *
FROM daily_counts;

원본 로그를 API 응답으로 내보내기 전에 데이터베이스 내부에서 집계한다. 따라서 원본 조회 응답이 1,000행에서 잘리는 문제를 집계 경로에서 제거할 수 있었다.

조회 범위에 포함되는 로그를 계산 대상으로 삼되, 외부로 반환하는 것은 그룹별 발생 횟수다.

날짜와 시간대의 기준도 기존 화면과 동일하게 Asia/Seoul로 명시했다. 집계 위치를 바꾸면서 날짜 경계까지 달라지는 일이 없도록 하기 위해서다.

실제 함수에서는 generate_series로 날짜 목록과 0~23시 구간도 생성한다. 활동이 없는 날짜나 시간대도 차트에서 빠지지 않도록 처리했다.

두 번의 원본 조회를 한 번의 RPC 호출로 변경

API에서는 원본 로그 조회 대신 집계 함수를 호출한다.

const { data, error } = await supabase.rpc(
  "get_admin_activity_charts",
  {
    p_from: periodFrom,
    p_to_exclusive: periodTo,
    p_top_limit: 10,
  }
);

이제 API 서버는 전체 로그를 순회해 통계를 만드는 대신, 이미 집계된 결과를 받아 기존 차트의 응답 형식으로 변환한다.

여기서 RPC 한 번은 API 서버와 데이터베이스 사이의 호출이 한 번이라는 의미다. 데이터베이스 내부의 연산이나 스캔까지 한 번이라는 의미는 아니다.

조회 범위와 인덱스를 함께 정리

데이터베이스에서 계산하더라도 조회 범위가 무제한이면 부담이 커질 수 있다.

활동 차트 함수에서는 조회 기간을 최대 60일로 제한하고, 사용자 순위도 기본 상위 10명만 반환하도록 했다.

기간 검색에 사용하는 created_at에는 다음 인덱스를 추가했다.

CREATE INDEX IF NOT EXISTS idx_activity_logs_created_at_cover
  ON public.user_activity_logs (created_at)
  INCLUDE (user_id, activity_type);

기간 조건과 집계에 사용하는 컬럼을 고려한 인덱스다. 다만 인덱스를 추가했다는 사실만으로 특정 실행 계획이 항상 선택된다고 단정하지는 않았다.

관리자 통계라는 접근 조건도 유지했다. API에서는 관리자 권한을 먼저 검사하고, 집계 함수는 일반 사용자에게 직접 실행 권한을 주지 않고 서버의 service_role에서만 호출하도록 제한했다.

4. 개선 효과: 전송량 감소와 집계 정확성을 각각 확인했다

같은 방식으로 활동 시간 통계와 페이지별 이용 시간 통계도 데이터베이스 집계로 전환했다.

2026년 8월 25일, 동일 워크스테이션에서 Supabase REST와 RPC를 직접 호출해 측정한 결과는 다음과 같다.

아래 크기는 브라우저가 받는 최종 API 응답이 아니라, 데이터베이스 조회 경로에서 API 서버로 전달되는 JSON 본문 크기다. HTTP 헤더는 포함하지 않았다.

통계 경로	개선 전	개선 후	전송량 감소
활동 시간 통계	15,066B	1,875B	87.6%
사용자별 페이지 이용 시간	154,610B	9,105B	94.1%
활동 차트	380,661B	3,895B	99.0%

활동 차트의 감소율은 다음과 같이 계산했다.

(380,661 - 3,895) / 380,661 × 100
= 약 98.98%

반올림하면 약 99%다. 두 번의 원본 로그 조회도 한 번의 집계 RPC 호출로 줄었다.

정확성 측면에서는 조회 응답의 행 제한에 걸린 일부 로그를 집계하던 구조를 제거했다. 지정한 기간의 데이터를 데이터베이스 안에서 집계하기 때문에, 원본 로그를 반환할 때의 행 제한에 통계 결과가 좌우되지 않도록 바뀌었다.

응답 시간이 모든 구간에서 줄어든 것은 아니다

전송량 감소와 응답 시간 개선은 구분해서 확인했다.

같은 측정 기록의 소요 시간은 다음과 같다.

통계 경로	개선 전	개선 후
활동 시간 통계	약 736ms	warm 기준 179~540ms
사용자별 페이지 이용 시간	약 361ms	483~486ms
활동 차트	약 504ms	503~612ms

일부 경로는 측정 시간이 비슷하거나 더 길었다. 이 결과만으로 모든 통계 API가 빨라졌다고 말할 수는 없다.

특히 활동 차트는 기존에 일부 로그만 계산하던 상태에서 조회 기간 전체를 집계하도록 바뀌었다. 처리 대상의 완전성이 달라졌다는 점도 함께 고려해야 한다.

따라서 이번 작업의 확인된 성과는 다음과 같이 표현하는 것이 정확하다.

관리자 통계를 PostgreSQL 집계로 전환해 데이터베이스에서 API 서버로 전달되는 JSON 전송량을 최대 99% 줄이고, 원본 조회의 행 제한으로 발생하던 집계 누락을 해결했다.

API와 SQL 정의를 확인하는 테스트에서는 정상 경로의 원본 조회 제거, 한국 시간 기준의 기간 전달, 시간대별 응답 형식, 관리자 권한 검사 등을 점검했다. 관련 테스트 2개 파일의 26개 테스트도 통과했다. 이 테스트 결과와 운영 데이터의 전송량 측정은 서로 다른 검증 근거로 구분했다.

이번 작업에서 집계 위치를 바꾼 이유는 화면에 필요한 결과와 서버가 가져오는 데이터 사이의 차이였다. 작은 차트 하나를 그리기 위해 원본 로그를 모두 이동시키는 구조를 걷어내면서, 전송 비용과 통계의 정확성을 함께 개선할 수 있었다.