[PostgreSQL] SLA 대시보드: percentile_cont(p50/p95/p99) + FILTER + TABLESAMPLE

설치·접속: PostgreSQL 설치와 접속

부제: API 응답시간 로그에서 엔드포인트별 p50/p95/p99를 뽑고, 2xx/5xx를 나눠 보고, 로그가 수억 행일 때 표본으로 근사할 때

문제 상황

응답시간 대시보드에 avg(latency_ms)만 그려 놓고 "평균 35ms, 괜찮네"라고 안심하던 팀이 있다. 그런데 사용자 항의는 계속 들어온다. 평균은 롱테일에 속는다. SLA는 평균이 아니라 p95/p99로 말해야 한다. API 로그 60만 건(엔드포인트 3종, 롱테일 지연 분포)을 스크래치 테이블 api_log(endpoint, status_code, latency_ms)로 만들어 세 기능을 얹는다.

1단계 — percentile_cont로 분위수를 한 번에

percentile_cont(f) WITHIN GROUP (ORDER BY latency_ms)가 ordered-set 집계다. 배열 인자를 주면 p50/p95/p99를 한 번의 정렬로 함께 뽑는다.

SELECT endpoint,
       count(*) AS n,
       round(avg(latency_ms), 1) AS avg_ms,
       percentile_cont(ARRAY[0.5,0.95,0.99]) WITHIN GROUP (ORDER BY latency_ms) AS p50_95_99
FROM api_log
GROUP BY endpoint;

2단계 — FILTER로 2xx/5xx를 분리

같은 쿼리 안에서 성공/실패의 지연을 갈라 보려면 ordered-set 집계에도 FILTER를 붙인다. 에러 응답이 유독 느린지 한 줄에서 비교된다.

SELECT endpoint,
       count(*)                                                          AS n,
       round(avg(latency_ms), 1)                                         AS avg_ms,
       (percentile_cont(ARRAY[0.5,0.95,0.99]) WITHIN GROUP (ORDER BY latency_ms)) AS p50_95_99,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) FILTER (WHERE status_code=200) AS p95_2xx,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) FILTER (WHERE status_code=500) AS p95_5xx
FROM api_log
GROUP BY endpoint
ORDER BY endpoint;

 endpoint  |   n    | avg_ms |     p50_95_99     | p95_2xx |      p95_5xx      
-----------+--------+--------+-------------------+---------+-------------------
 /checkout | 200000 |   35.1 | {17.9,68.1,617.5} |    68.1 | 67.65999999999994
 /login    | 200000 |   34.9 | {17.8,67.6,616.7} |    67.7 |              65.5
 /search   | 200000 |   34.6 | {17.8,68,616.9}   |      68 | 68.56499999999996

평균은 35ms 근처인데 p99는 617ms다. 평균만 보던 대시보드가 왜 위험한지 이 한 줄이 보여준다. avg의 함정을 숫자로 다시 확인하면:

SELECT round(avg(latency_ms),1) AS avg_ms,
       percentile_cont(0.5)  WITHIN GROUP (ORDER BY latency_ms) AS p50,
       percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99,
       max(latency_ms) AS worst
FROM api_log;

 avg_ms | p50  | p99 | worst 
--------+------+-----+-------
   34.9 | 17.8 | 617 | 767.6
(1 row)

중앙값(p50)은 17.8ms인데 평균은 34.9ms — 소수의 느린 요청이 평균을 두 배로 끌어올린 것이다. p50과 avg가 벌어지면 분포가 오른쪽으로 길다는 신호다.

3단계 — TABLESAMPLE로 수억 행을 즉시 근사

로그가 수억 행이면 전체 정렬 기반 percentile은 비싸다. 스팟체크/실시간 위젯 용도라면 TABLESAMPLE SYSTEM (1)로 블록 표본만 읽어 근사한다. REPEATABLE(seed)를 주면 테이블이 안 바뀌는 한 같은 표본을 재현한다.

-- 전체 스캔
SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_full
FROM api_log;

-- 1% 블록 표본
SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_approx,
       count(*) AS sampled_rows
FROM api_log TABLESAMPLE SYSTEM (1) REPEATABLE (42);

 p95_full 
----------
     67.9
Time: 113.710 ms

    p95_approx     | sampled_rows 
-------------------+--------------
 69.31499999999996 |         5338
Time: 1.488 ms

전체 60만 행 p95는 67.9ms(113ms 소요), 1% 표본(5,338행) 근사는 69.3ms(1.5ms 소요). 정확도는 2ms 차이인데 시간은 약 76배 빠르다. 대시보드 위젯이 몇 초마다 갱신되어야 한다면 이 트레이드가 정확히 맞는다.

결론 — 언제 이 조합을 쓰나

SLA를 숫자로 관리하는 순간 percentile_cont는 필수다. 성공/실패나 엔드포인트별로 쪼개려면 FILTER, 로그가 커서 매번 풀스캔이 부담이면 TABLESAMPLE SYSTEM으로 근사한다. 단 표본 근사는 스팟체크용이다. SLA 위반 판정처럼 정확값이 필요한 계산은 전체 스캔(또는 사전 집계된 롤업 테이블)으로 가야 한다.

이렇게도 쓴다

이산 분위수가 필요하면(실제 존재하는 값만) percentile_disc. 보간값이 아니라 데이터에 있는 latency 하나를 돌려준다.

SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY latency_ms) FROM api_log;

 

가장 흔한 응답시간 버킷(최빈값)은 mode(). (조합: ordered-set 집계)

SELECT mode() WITHIN GROUP (ORDER BY round(latency_ms)) FROM api_log;

 

블록 단위가 아니라 행 단위 균일 표본이 필요하면 BERNOULLI(느리지만 고름). (조합: TABLESAMPLE BERNOULLI)

SELECT count(*) FROM api_log TABLESAMPLE BERNOULLI (1) REPEATABLE (7);

 

엔드포인트별 성공률을 같은 리포트에 붙인다. (조합: FILTER 카운트)

SELECT endpoint,
       round(100.0 * count(*) FILTER (WHERE status_code=200) / count(*), 2) AS success_pct
FROM api_log GROUP BY endpoint;

표본 오차가 SLA 판정에 영향을 줄 만큼 커지면(작은 파티션, 극단 분위수 p99.9) 표본을 버리고 전체 스캔 + 인덱스(ORDER BY latency)나 사전 집계로 갈아탄다. TABLESAMPLE은 "빠른 근사"지 "정확한 SLA 리포트"가 아니다.