링크 테이블을 조인해 SUM 하면 파일 하나를 여러 번 센다

요금제 개편 작업을 하다가 기존 배치의 집계 쿼리를 들여다볼 일이 있었다. 기관별로 저장 용량을 얼마나 쓰고 있는지 계산해서, 그 값으로 한도를 넘었는지 판단하고 업로드를 막는 배치였다.

쿼리는 이렇게 생겼다.

SELECT pa.tenant_id, SUM(f.file_size) AS total
FROM post_attachments pa
LEFT JOIN files f ON pa.file_id = f.file_id
GROUP BY pa.tenant_id;

파일은 files 에 있고, 그 파일이 어느 글에 붙었는지는 post_attachments 라는 링크 테이블이 들고 있다. 기관별로 묶어서 크기를 더한다. 읽으면 딱히 이상한 데가 없다. 나도 처음엔 그냥 넘겼다.

링크는 파일보다 많다

문제는 post_attachments 가 파일과 1:1 이 아니라는 것이다.

같은 파일을 여러 글에 첨부하면 링크 행이 여러 개 생긴다. 글을 복사해도 생기고, 답글에 원문 첨부가 따라붙어도 생긴다. 조인하면 결과 행이 링크 수만큼 늘어나고, SUM 은 그 늘어난 행을 전부 더한다. 스토리지에는 한 번 올라간 파일인데 집계에서는 두 번, 세 번 계산된다.

얼마나 되는지 세어봤다.

WITH dup AS (
  SELECT file_id, count(*) AS n
  FROM post_attachments
  GROUP BY file_id
  HAVING count(*) > 1
)
SELECT count(*) AS dup_files,
       pg_size_pretty(SUM(f.file_size * (dup.n - 1))::bigint) AS overcounted
FROM dup
JOIN files f USING (file_id);

중복 링크를 가진 파일은 전체의 2% 정도였다. 그런데 그것들 때문에 과다 계상된 용량은 400GB 가 넘었다.

이게 이 문제의 고약한 부분이다. 행 수 기준으로는 2% 라서 "무시해도 되는 수준"처럼 보이는데, 용량 기준으로는 전혀 그렇지 않다. 여러 곳에 인용되는 파일은 대개 큰 파일이기 때문이다. 회의 녹화본, 설계 문서, 디자인 시안. 작고 사소한 파일은 한 번 올리고 끝나지만 중요한 파일은 여기저기 붙는다. 중복 비율과 과다 계상 비율은 비례하지 않는다.

물리 파일로 되돌린 뒤 더한다

고치는 방향은 단순하다. 합산 대상을 링크가 아니라 물리 파일로 되돌린다.

WITH links AS (
  SELECT pa.tenant_id, pa.file_id
  FROM post_attachments pa
  WHERE /* 가시성 필터 */
),
physical AS (
  SELECT DISTINCT tenant_id, file_id FROM links
)
SELECT p.tenant_id, COALESCE(SUM(f.file_size), 0) AS total
FROM physical p
JOIN files f USING (file_id)
GROUP BY p.tenant_id;

여기서 놓치기 쉬운 게 DISTINCT 에 그룹 키를 같이 넣는 것이다. file_id 만으로 접으면 여러 기관이 공유하는 파일이 한쪽으로만 몰린다. 문제는 그 "한쪽"이 실행 계획에 따라 달라진다는 점이다. 같은 배치를 두 번 돌렸는데 기관별 결과가 다르게 나온다. 청구서에 쓸 값이 실행할 때마다 달라지면 곤란하다.

tenant_id 를 함께 넣으면 기관별로 한 번씩 계상되고, 어떤 순서로 처리하든 결과가 고정된다. 공유 파일을 양쪽에 다 세는 게 맞는지는 별개의 정책 문제지만, 적어도 결정론은 확보된다.

참고로 SUM(DISTINCT f.file_size) 는 답이 아니다. 크기가 우연히 같은 다른 파일들이 하나로 접힌다. 실제로 0바이트 파일이나 같은 템플릿에서 나온 파일들이 꽤 있어서, 이렇게 하면 과다 계상 대신 과소 계상이 된다.

남는 생각

조인 한 단계가 카디널리티를 바꾸면 그 뒤의 집계 함수는 전부 오염된다. 아는 이야기다. 그런데도 매번 놓치는 이유는 SUM 의 결과가 틀린 티가 안 나기 때문인 것 같다.

COUNT 가 부풀려지면 눈에 띈다. "우리 기관 파일이 3만 개라고? 그렇게 많을 리가 없는데." 그런데 SUM 은 그냥 좀 큰 숫자로 나온다. 원래 용량이 얼마인지 아무도 감이 없으니 의심할 근거도 없다. 몇 년째 틀린 값을 보고 있어도 아무 일이 안 생긴다. 그 값으로 요금을 매기기 시작하는 날까지는.

그래서 청구나 쿼터처럼 값 자체가 근거가 되는 집계라면, 쿼리를 읽어서 검증하려 하지 말고 중복분을 따로 계산해 실측하는 편이 빠르다. 위에 쓴 HAVING count(*) > 1 쿼리 하나면 된다. 5분이면 답이 나온다.

참고

• PostgreSQL: Table Expressions
• PostgreSQL: Aggregate Functions