ds-forge — interview trainer

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

매물(listings) 테이블 1,000만 행. 같은 목록 쿼리인데 1페이지는 5ms, 5,000페이지는 2초가 걸린다. 증상 → 원인 → 눈으로 확인(EXPLAIN) → 당장 막는 법 → 스케일에서 사는 법(keyset) → 그 대가, 이 순서로 올라간다.

1
증상: 같은 목록 쿼리인데 1페이지는 5ms, 5,000페이지는 2초. 쿼리에서 바뀐 건 OFFSET 숫자 하나뿐이다. 느린 쿼리 로그를 보면 전부 OFFSET이 큰 놈들이다.
🔧 도구:pg_stat_statements (느린 쿼리 상위 찾기)log_min_duration_statement = 500
```
-- listings: 10,000,000 rows, page size 20
SELECT id, price, created_at
FROM listings
ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 20;   -- page ~5,000

-- page 1     (OFFSET 0):      ~5 ms
-- page 5,000 (OFFSET 100000): ~2,000 ms  -- same query, only OFFSET changed
```
🗣 영어로 말해
“Page one returns in five milliseconds; page five thousand takes two seconds.”
checking microphone…
2
원인: OFFSET 100000은 '100,001번째 행으로 점프'가 아니다. DB는 순서대로 100,020행을 전부 읽고, 앞의 100,000행을 버리고, 20행만 돌려준다. 비용이 OFFSET 크기에 비례한다.
⚖️ Trade-off: 페이지가 깊어질수록 선형으로 느려진다. 데이터가 늘면 같은 페이지 번호도 점점 느려진다 — 오늘 괜찮아도 내년엔 죽는다.
```
OFFSET 100000 LIMIT 20
-- != "jump to row 100,001"
-- == read 100,020 rows in order, DISCARD 100,000, return 20
-- cost = O(offset + limit), not O(limit)
```
🗣 영어로 말해
“OFFSET isn't a jump — Postgres reads every skipped row and throws it away.”
checking microphone…
3
눈으로 확인: EXPLAIN (ANALYZE, BUFFERS)를 붙여 본다. rows=100020 — 20행을 주려고 실제로 10만 행을 스캔했다는 증거다. Buffers의 read 숫자는 디스크에서 읽어 온 블록 수. 추측 말고 이 숫자를 보여 주면 된다.
🔧 도구:EXPLAIN (ANALYZE, BUFFERS)pg_stat_statements
```
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, price, created_at FROM listings
ORDER BY created_at DESC, id DESC OFFSET 100000 LIMIT 20;

Limit (actual time=1979.3..1979.4 rows=20 loops=1)
  -> Index Scan using idx_listings_created_id on listings
       (actual time=0.05..1974.8 rows=100020 loops=1)   -- <- smoking gun
  Buffers: shared hit=3402 read=97615
Execution Time: 1980.1 ms
```
🗣 영어로 말해
“EXPLAIN ANALYZE shows it scanned a hundred thousand rows just to return twenty.”
checking microphone…
4
기본 수리(boring fix): ORDER BY와 똑같은 모양의 복합 인덱스를 만든다 — 정렬 단계가 사라져서 얕은 페이지는 확실히 빨라진다. 그리고 API에서 페이지 깊이를 자른다. 구글 검색도 깊은 페이지는 안 준다.
⚖️ Trade-off: 깊은 OFFSET 자체는 여전히 느리다 — 인덱스가 있어도 10만 행은 읽는다. 깊이 cap은 제품 쪽 합의가 필요하다.
✅ Fix: 인덱스로 정렬 비용 제거 + 깊이 cap으로 최악 케이스 차단. 대부분의 사용자는 앞 몇 페이지만 본다.
🔧 도구:복합 인덱스 (ORDER BY와 같은 컬럼·방향)API 깊이 제한 (max page)
```
CREATE INDEX idx_listings_created_id
  ON listings (created_at DESC, id DESC);  -- same shape as ORDER BY

-- API guard: refuse deep pages
-- if (page * size > 10_000) -> 400 "switch to cursor pagination"
```
🗣 영어로 말해
“First the boring fix: I index the sort columns and cap page depth.”
checking microphone…
5
스케일 해법: keyset(cursor) 페이지네이션. '몇 번째 페이지'가 아니라 '마지막으로 본 행 다음 20개'를 묻는다. WHERE (created_at, id) < (마지막 값)이면 인덱스가 그 지점으로 바로 점프 → 어느 깊이든 딱 20행만 읽는다. 5,000페이지도 5ms.
✅ Fix: id를 tie-breaker로 같이 넣는다 — created_at이 같은 행이 있어도 빠지거나 중복되지 않는다. 덤으로 새 행이 끼어들어도 페이지가 밀리지 않는다(OFFSET drift 버그도 해결).
🔧 도구:keyset / seek method(created_at, id) 복합 인덱스row comparison: (a, b) < (:x, :y)
```
-- client sends the last row it saw (the cursor)
SELECT id, price, created_at
FROM listings
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

--  Index Cond: ROW(created_at, id) < ROW($1, $2)
--  rows=20  Execution Time: ~5 ms   -- at ANY depth
```
🗣 영어로 말해
“Keyset seeks straight to the last seen key, so every page costs the same.”
checking microphone…
6
keyset의 대가: '37페이지로 점프'가 안 된다 — 앞에서부터 커서를 따라가야 한다. 전체 건수도 비싸다 — COUNT(*)는 1,000만 행 풀스캔이라 그 자체가 2초짜리 쿼리다. 그래서 keyset은 무한 스크롤 UI + 추정 카운트와 짝이 맞는다.
⚖️ Trade-off: 임의 페이지 점프와 정확한 총 건수를 포기한다. 대신 어느 깊이에서도 일정한 응답 시간을 얻는다 — 스케일에서는 남는 거래다.
🔧 도구:pg_class.reltuples (추정 카운트)무한 스크롤 / 'next' 커서 API
```
-- exact count = its own seq scan over 10M rows
SELECT count(*) FROM listings;            -- ~2 s

-- planner's estimate: instant, close enough for UI
SELECT reltuples::bigint AS approx_rows
FROM pg_class WHERE relname = 'listings';
```
🗣 영어로 말해
“The trade-off: no jumping to page thirty, and exact totals become estimates.”
checking microphone…

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