ds-forge — interview trainer

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

10만 건일 땐 30ms였던 쿼리가 2,000만 건이 되니 4초가 걸린다. 추측하지 말고 EXPLAIN으로 직접 보고, 인덱스로 고치고, 그 대가까지 말할 수 있게 한 계단씩 올라간다.

1
증상: 구매자별 최근 오퍼 20건을 보여주는 쿼리. 테이블이 10만 건일 땐 30ms, 2,000만 건이 되니 4초. 쿼리도 코드도 그대로인데 데이터가 커지자 느려졌다.
```
-- p95: 30ms at 100k rows -> 4s at 20M rows
SELECT * FROM offers
WHERE buyer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```
🗣 영어로 말해
“Same query, same code — it just got slow as the table grew to twenty million.”
checking microphone…
2
왜 느린가: buyer_id에 인덱스가 없으면 Postgres는 2,000만 행을 전부 읽고(풀 스캔) 그중 buyer_id=42인 1,843건만 남긴다. 20건을 돌려주려고 약 2.4GB를 읽는 셈이다.
⚖️ Trade-off: 데이터가 10배 늘면 시간도 10배 — O(N)이라 테이블 크기에 정비례로 느려진다.
```
CREATE TABLE offers (
  id         bigint PRIMARY KEY,
  buyer_id   bigint NOT NULL,
  home_id    bigint NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL
);
-- only the PK index exists: no fast path for buyer_id
```
🗣 영어로 말해
“With no index on buyer_id, Postgres scans all twenty million rows to return twenty.”
checking microphone…
3
눈으로 확인: EXPLAIN (ANALYZE, BUFFERS)를 돌린다. 플랜에 'Seq Scan' + 'Rows Removed by Filter: 19,998,157' + 'shared read=312500'(8KB 페이지 31만 개 ≈ 2.4GB)이 다 적혀 있다. 추측이 아니라 측정이다.
🔧 도구:EXPLAIN (ANALYZE, BUFFERS)pg_stat_statements (느린 쿼리 찾기)auto_explain (운영에서 느린 플랜 자동 기록)
```
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM offers WHERE buyer_id = 42
ORDER BY created_at DESC LIMIT 20;

Limit (actual time=4123.97..4123.99 rows=20)
  -> Sort (actual time=4123.95..4123.96 rows=20)
     Sort Key: created_at DESC
     -> Seq Scan on offers (actual time=0.31..4098.43 rows=1843)
          Filter: (buyer_id = 42)
          Rows Removed by Filter: 19998157
          Buffers: shared read=312500
Execution Time: 4124.03 ms
```
🗣 영어로 말해
“I don't guess — I run EXPLAIN ANALYZE and the plan shows a sequential scan.”
checking microphone…
4
기본 처방: buyer_id에 B-tree 인덱스 하나. 풀 스캔 대신 트리를 타고 내려가(O(log N)) 1,843건만 짚어 읽는다. 4초 → 약 12ms. 운영 DB에선 CONCURRENTLY로 만들어야 쓰기를 막지 않는다.
✅ Fix: 읽기 경로가 '전부 읽고 거르기'에서 '필요한 것만 짚어 가기'로 바뀐다.
🔧 도구:CREATE INDEX CONCURRENTLYB-tree (Postgres 기본 인덱스 타입)
```
CREATE INDEX CONCURRENTLY idx_offers_buyer
    ON offers (buyer_id);

-- plan after:
-- Limit (actual time=10.8..10.9 rows=20)
--   -> Sort (actual time=10.8..10.85 rows=20)
--        Sort Key: created_at DESC      -- index gives buyer_id, not order
--        -> Bitmap Heap Scan on offers (rows=1843)
--             Recheck Cond: (buyer_id = 42)
--             -> Bitmap Index Scan on idx_offers_buyer (rows=1843)
```
🗣 영어로 말해
“One B-tree index drops it from four seconds to about twelve milliseconds.”
checking microphone…
5
스케일 처방: 플랜에 아직 Sort가 남아 있다 — 1,843건을 다 가져와 매번 정렬한다. 인덱스를 정렬 순서까지 맞춰 (buyer_id, created_at DESC)로 만들면 Sort가 사라지고 딱 20행만 읽는다. 12ms → 0.3ms. 오퍼가 수만 건인 큰 구매자도 똑같이 빠르다.
✅ Fix: WHERE + ORDER BY + LIMIT을 인덱스 하나가 통째로 받아낸다. 더 자주 치는 쿼리면 INCLUDE로 커버링 인덱스까지 가능.
🔧 도구:복합 인덱스 (buyer_id, created_at DESC)INCLUDE (커버링 인덱스 — heap 접근 생략)
```
CREATE INDEX CONCURRENTLY idx_offers_buyer_created
    ON offers (buyer_id, created_at DESC);

-- Limit (actual time=0.05..0.31 rows=20)
--   -> Index Scan using idx_offers_buyer_created
--        Index Cond: (buyer_id = 42)
-- no Sort node: rows come out already ordered
```
🗣 영어로 말해
“At scale I match the index to the sort, so Postgres reads exactly twenty rows.”
checking microphone…
6
대가: 인덱스는 공짜가 아니다. INSERT/UPDATE마다 모든 인덱스도 같이 갱신된다 — 인덱스가 5개면 쓰기 한 번에 6군데를 쓴다. 디스크도 먹는다(이 복합 인덱스 하나가 약 600MB). 그래서 아무 컬럼에나 걸지 않고, 안 쓰는 인덱스는 통계로 찾아서 지운다.
⚖️ Trade-off: 읽기 속도를 사려고 쓰기 속도와 디스크를 판다. 쓰기가 많은 테이블일수록 인덱스 수를 아껴야 한다.
🔧 도구:pg_stat_user_indexes (idx_scan=0 → 안 쓰는 인덱스)ANALYZE (플래너 통계 갱신)
```
-- find indexes no query has ever used
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
```
🗣 영어로 말해
“Indexes trade write speed and disk for reads, so I drop the unused ones.”
checking microphone…

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