←

Hot rows + partitioning — when one row/table melts

DB perf

Understand it step by step (한국어로 이해 → 영어로 말하기)

쿼리 자체는 멀쩡한데 DB가 느려질 때가 있다. 범인은 보통 둘 중 하나다: 모두가 같은 행 하나를 두드리거나(hot row), 테이블 하나가 너무 커졌거나. 초당 3,000번 UPDATE 맞는 조회수 행에서 시작해서 5억 행 이벤트 테이블까지 — 증상 → 원인 → 눈으로 확인 → 지루한 해결 → 스케일 해결 → 비용 순서로 올라간다.
  1. 1

    증상: 인기 매물 하나에 조회가 몰린다. 그 매물의 view_count 행 하나가 초당 3,000번 UPDATE를 맞는다. 평소 2ms이던 쿼리가 p99 800ms가 되고, 커넥션 100개가 다 차서 다른 기능까지 같이 느려진다. 이상한 점 하나: DB CPU는 40%밖에 안 된다.

    -- 3,000 times per second, ALL on the same row
UPDATE listings SET view_count = view_count + 1 WHERE id = 42;
-- p99: 2ms -> 800ms, but DB CPU is only 40%
    🗣 영어로 말해

    “One popular listing gets all the traffic, and every update hits the same row.”

    checking microphone…

  2. 2

    원인: 같은 행을 고치는 UPDATE는 행 락(row lock)을 잡는다. 행이 하나니까 줄도 하나 — 3,000개 트랜잭션이 한 줄로 선다. CPU가 낮은 이유가 바로 이것: 일하는 게 아니라 기다리는 중이다. 덤으로 Postgres는 UPDATE마다 행의 새 버전을 만들어서(MVCC), 죽은 버전(dead tuple)이 초당 3,000개씩 쌓이고 테이블이 부푼다.

    ⚖️ Trade-off: CPU 그래프만 보면 'DB 여유 있네'로 오진하기 딱 좋다. 락 대기는 CPU에 안 보인다.

    BEGIN;
UPDATE listings SET view_count = view_count + 1 WHERE id = 42;
-- every other transaction touching row 42 now BLOCKS until this COMMIT
COMMIT;
    🗣 영어로 말해

    “Row locks serialize the writers — three thousand transactions wait in one line.”

    checking microphone…

  3. 3

    확인: 추측하지 말고 본다. pg_stat_activity의 wait_event를 보면 활성 쿼리 대부분이 'Lock: transactionid'에서 기다리는 게 보인다 — 이게 행 락 대기다. 주의: EXPLAIN ANALYZE를 혼자 돌리면 멀쩡하게 나온다. 락 대기는 실행 계획 문제가 아니라서, 계획이 아니라 대기 이벤트를 봐야 한다.

    🔧 도구:pg_stat_activity (wait_event)pg_lockspg_stat_user_tables.n_dead_tup

    SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity WHERE state = 'active' GROUP BY 1, 2;

 wait_event_type |  wait_event   | count
-----------------+---------------+-------
 Lock            | transactionid |    97   -- 97 of 100 connections wait on a row lock
    🗣 영어로 말해

    “I check pg_stat_activity first — the wait event says lock waiting, not slow SQL.”

    checking microphone…

  4. 4

    지루한 해결책: 그 행을 그만 두드린다. 조회수는 몇 초 늦어도 되는 값이라, 앱 메모리에 모았다가 5초마다 한 번에 더한다 — 초당 3,000번이 5초당 1번이 된다. 더 필요하면 행을 16개 조각(slot)으로 쪼개 무작위로 하나씩 올리고, 읽을 때 SUM한다(counter sharding).

    ⚖️ Trade-off: 배치는 서버가 죽으면 몇 초치 카운트를 잃는다. 샤딩은 읽기가 16행 SUM이 된다. 조회수엔 둘 다 싼 비용이다.

    ✅ Fix: 핵심 질문은 '이 값이 정확히 지금이어야 하나?'다. 조회수는 아니다 → 모았다 쓰기. 잔액처럼 정확해야 하는 값이면 이 방법 금지 — 설계 자체를 다시 본다.

    🔧 도구:앱 메모리 버퍼 + 주기 flushcounter sharding (행 N개 분산)Redis INCR → 주기적으로 DB 반영

    -- counter sharding: 16 rows instead of 1 hot row
UPDATE listing_views SET cnt = cnt + 1
WHERE listing_id = 42 AND slot = floor(random() * 16);

-- read side: sum the slots
SELECT sum(cnt) FROM listing_views WHERE listing_id = 42;
    🗣 영어로 말해

    “The boring fix: batch the increments — one write every five seconds, not three thousand.”

    checking microphone…

  5. 5

    다음 단계: 행이 아니라 테이블이 녹는 경우. offer_events가 5억 행이다. 인덱스가 메모리에 안 들어가서 최근 30일 조회도 느리고, 1년 지난 데이터 DELETE는 몇 시간 돌면서 테이블을 더 부풀린다. 해결: created_at 기준 RANGE 파티셔닝 — 한 달에 파티션 하나(약 4천만 행).

    🔧 도구:PARTITION BY RANGEpg_partman (파티션 자동 생성)

    CREATE TABLE offer_events (
  id         bigint,
  offer_id   bigint,
  created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE offer_events_2026_06
  PARTITION OF offer_events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
    🗣 영어로 말해

    “At five hundred million rows, I partition the events table by month.”

    checking microphone…

  6. 6

    효과 확인: EXPLAIN에 파티션 프루닝이 보인다. WHERE created_at >= '2026-06-01'이면 플래너가 6월 파티션 하나만 스캔한다 — 5억 행이 아니라 4천만 행만 본다. 보존 정책도 공짜가 된다: 몇 시간짜리 DELETE 대신 DROP TABLE 한 방, 1초.

    ✅ Fix: 오래된 달을 지우는 게 행 단위 삭제가 아니라 파일 떼어내기가 된다. dead tuple도 VACUUM 부담도 없다.

    🔧 도구:EXPLAIN (partition pruning 확인)DROP TABLE로 보존 정책

    EXPLAIN SELECT * FROM offer_events
WHERE created_at >= '2026-06-01';

 Append
   ->  Index Scan using offer_events_2026_06_idx on offer_events_2026_06
-- other 59 partitions pruned, never touched

-- retention: no giant DELETE
DROP TABLE offer_events_2025_01;
    🗣 영어로 말해

    “EXPLAIN shows the pruning — one partition scanned, and old months drop in one second.”

    checking microphone…

  7. 7

    비용도 말할 수 있어야 한다. ① 쿼리에 created_at이 없으면 60개 파티션을 전부 뒤진다 — 오히려 느려질 수 있다. ② PK/UNIQUE 제약에 파티션 키가 반드시 들어가야 한다 — 'id 하나만으로 전체 유일' 보장이 약해진다. ③ 매달 새 파티션을 만들어줘야 한다(자동화 필요). 그래서 수천만 행 아래에서는 보통 안 꺼낸다.

    ⚖️ Trade-off: 파티셔닝은 '큰 테이블 + 시간/키로 자르는 쿼리 패턴'일 때만 이득이다. 패턴이 안 맞으면 복잡도만 산다.

    -- the catch: unique constraints must include the partition key
ALTER TABLE offer_events ADD PRIMARY KEY (id, created_at);
-- PRIMARY KEY (id) alone is rejected on a partitioned table
    🗣 영어로 말해

    “Partitioning isn't free — every query needs the partition key, or it scans everything.”

    checking microphone…

7단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.