Part 7. DB 대량 조회 전략: OFFSET/LIMIT 한계와 Keyset, ID Range, Covering Index

출처: Pexels - Computer server in data center room

버전 기준

• Java 21
• Spring Boot 3.3.x
• Spring Batch 5.2.x
• Quartz 2.3.x
• PostgreSQL 15
• OpenSearch 2.x

1) 문제 제기

배치 성능 이슈의 절반 이상은 비즈니스 로직이 아니라 조회 방식에서 발생한다. 특히 "페이지당 1,000건" 같은 고정 규칙으로 OFFSET/LIMIT를 반복하는 패턴은 데이터가 커질수록 선형이 아니라 비선형으로 느려진다.

운영에서 문제는 성능 저하로 끝나지 않는다.

• 페이지 사이에 신규 데이터가 삽입되면 중복/누락이 발생한다.
• 긴 스캔으로 버퍼 캐시가 오염되고 OLTP API가 느려진다.
• 고립 수준을 높이면 락 대기가 급증한다.

2) 핵심 개념 정리

OFFSET/LIMIT vs Keyset 비교표

항목	OFFSET/LIMIT	Keyset Pagination
시간 복잡도	페이지 번호가 커질수록 악화	상대적으로 일정
정합성	중간 삽입/삭제 시 중복·누락 가능	기준 키 기반으로 안정적
인덱스 활용	대량 skip으로 비효율	정렬 키 인덱스 효율적
재시작 용이성	page 번호 저장 필요	마지막 키 저장으로 단순
권장 용도	관리자 UI 작은 데이터	배치/스트리밍 대량 처리

ID Range Batch

ID가 단조 증가하면 범위 기반으로 나눠 병렬 처리하기 쉽다.

• 장점: 파티션 독립성이 높고 재처리 범위 지정이 명확.
• 단점: ID 분포 편향 시 파티션 불균형.

Covering Index

배치가 읽기 전용이면 "필요 컬럼만 포함한 인덱스"로 테이블 접근을 줄일 수 있다. PostgreSQL에서는 INCLUDE 컬럼을 활용해 Heap access를 줄인다.

격리 수준과 스냅샷 읽기

• READ COMMITTED: 일반적인 배치 기본값.
• REPEATABLE READ: 동일 조건 반복 스캔의 정합성 확보.
• Snapshot 기반 읽기(트랜잭션 시작 시점 고정)는 긴 스캔에서 일관성은 좋지만 vacuum 지연과 버전 증가를 유발할 수 있다.

조회 파이프라인 다이어그램

출처: Pexels - Program code on screen

3) 코드 예시

예시 A: OFFSET/LIMIT (비권장 패턴)

SELECT id, user_id, amount, created_at
FROM payment_events
WHERE created_at >= :from
ORDER BY created_at, id
OFFSET :offset
LIMIT :limit;

예시 B: Keyset Pagination (권장)

SELECT id, user_id, amount, created_at
FROM payment_events
WHERE created_at >= :from
  AND (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT :limit;

예시 C: ID Range Batch

SELECT id, user_id, amount
FROM payment_events
WHERE id BETWEEN :start_id AND :end_id
ORDER BY id ASC
LIMIT :limit;

예시 D: Covering Index 설계

CREATE INDEX idx_payment_events_batch
ON payment_events (created_at, id)
INCLUDE (user_id, amount);

예시 E: 락 경쟁 최소화 (SKIP LOCKED)

WITH cte AS (
  SELECT id
  FROM payout_queue
  WHERE status = 'READY'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 500
)
UPDATE payout_queue q
SET status = 'PROCESSING'
FROM cte
WHERE q.id = cte.id
RETURNING q.id, q.user_id, q.amount;

4) 실제 장애/운영 시나리오

상황: 결제 정산 배치가 OFFSET 기반으로 3천만 건을 스캔했다. 2시간 후 페이지 후반으로 갈수록 쿼리 응답이 50ms에서 4s로 증가했고, 새로 유입된 이벤트가 중간에 끼어들어 일부 레코드가 두 번 정산됐다.

원인:

• 대량 skip 비용을 과소평가했다.
• 정렬 키가 단일 created_at라 동시각 데이터에서 순서가 불안정했다.
• 배치 재시작 포인터를 page 번호로 저장해 데이터 변동에 취약했다.

개선:

1. (created_at, id) 복합 keyset으로 전환.
2. 인덱스를 (created_at, id) INCLUDE (user_id, amount)로 변경.
3. 재시작 포인터를 (last_created_at, last_id)로 저장.

5) 설계 체크리스트

• OFFSET 기반 대량 스캔을 제거했는가?
• Keyset 정렬 키가 유일성과 단조성을 보장하는가?
• 배치 조회 컬럼에 맞는 covering index를 설계했는가?
• SKIP LOCKED 등으로 락 경합 완화 전략을 적용했는가?
• 격리 수준 선택이 정합성과 성능 요구를 동시에 만족하는가?
• 재시작 포인터를 page 번호가 아닌 키 값으로 저장하는가?

6) 요약

DB 대량 조회 전략은 배치 성공률을 결정한다. OFFSET/LIMIT는 초기에 편하지만 규모가 커지면 비용과 정합성 리스크가 급증한다. 실무 기본값은 Keyset + 적절한 인덱스 + 재시작 포인터 저장이다.

7) 다음 편 예고

다음 편에서는 OpenSearch/Elasticsearch 배치 전략을 다룬다. Scroll, Search After, PIT, Bulk API, Refresh 정책, 롤오버 인덱스 운영 기준과 비교표를 제공한다.

참고 링크

• Spring Batch Reference
• Quartz Scheduler Documentation
• PostgreSQL Transaction Isolation
• 블로그: Idempotency Key API 설계

시리즈 네비게이션

• 이전 글: Part 6. 수동 배치 전략: REST 트리거, Admin UI, 파라미터 재처리, 롤백
• 다음 글: Part 8. OpenSearch/Elasticsearch 배치 전략: Scroll, Search After, PIT, Bulk, Rollover