페이징과 대량 조회: offset pagination, keyset pagination, cursor

페이지가 깊어질수록 느려지는 이유

대부분의 웹 서비스는 리스트 화면에 LIMIT ... OFFSET ... 패턴을 쓴다. 처음 몇 페이지는 빠르다. 그런데 100만 건짜리 테이블에서 500번째 페이지를 요청하면 쿼리가 눈에 띄게 느려진다. 근본 원인은 간단하다. OFFSET N은 첫 N개 행을 버리는 게 아니라 정렬된 결과를 N번째까지 세어 나간 뒤 비로소 LIMIT 개수만큼 반환하기 때문이다.

이 문제를 해결하는 방법이 keyset pagination이다. 마지막으로 본 행의 값을 커서(cursor)로 삼아 WHERE 조건에 직접 걸어, 데이터베이스가 인덱스를 타고 그 지점부터 바로 스캔하게 만든다.

OFFSET의 두 가지 문제

성능: O(N) 스캔

-- 500번째 페이지 (페이지당 20행)
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 9980;

옵티마이저는 ORDER BY created_at DESC, id DESC를 만족하는 결과를 앞에서부터 9,980개 읽어 버리고, 다음 20개를 반환한다. 인덱스가 있어도 인덱스를 처음부터 따라가면서 9,980개를 세어야 한다. OFFSET 값이 커질수록 실행 시간은 선형 증가(O(N)) 한다.

실제 측정 예시(1억 건 테이블):

정확성: 중복·누락

OFFSET은 "N번째 행부터"라는 위치 기반 참조다. 페이지를 넘기는 사이에 새 행이 삽입되거나 기존 행이 삭제되면 결과가 어긋난다.

-- 10초 전 1페이지 조회: rows 1~20
-- 그 사이 row 5가 삭제됨
-- 2페이지 조회: OFFSET 20 → 실제로는 row 21이 아니라 row 22부터
-- row 21을 건너뜀

실시간 피드나 동시 쓰기가 많은 테이블에서 OFFSET 페이지네이션은 데이터 일관성을 보장하지 못한다.

Keyset Pagination (Seek Method)

핵심 아이디어

마지막 페이지에서 가져온 행의 정렬 기준 컬럼 값을 WHERE 조건에 넣는다. 데이터베이스는 인덱스로 그 지점을 바로 찾아 스캔을 시작하므로 페이지 깊이와 무관하게 O(1) 성능을 유지한다.

-- 첫 페이지
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 다음 페이지: 첫 페이지 마지막 행의 (created_at='2026-06-01 10:00:00', id=4200)을 커서로 사용
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-06-01 10:00:00', 4200)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at, id) < (last_created_at, last_id)는 행 값 비교(row value comparison)다. SQL 표준에서 지원하며 MySQL 8.0+, PostgreSQL, Oracle, SQL Server 모두 처리한다. 내부적으로 다음과 동등하다.

WHERE created_at < '2026-06-01 10:00:00'
   OR (created_at = '2026-06-01 10:00:00' AND id < 4200)

인덱스 설계

keyset가 O(1) 성능을 내려면 WHERE와 ORDER BY 컬럼을 모두 포함하는 복합 인덱스가 필요하다.

-- MySQL
CREATE INDEX idx_articles_created_id ON articles (created_at DESC, id DESC);

-- PostgreSQL
CREATE INDEX idx_articles_created_id ON articles (created_at DESC, id DESC);

인덱스가 없으면 keyset도 풀 스캔으로 떨어진다. 인덱스 컬럼 순서와 정렬 방향이 쿼리와 일치해야 Index Scan이 작동한다.

동점(Tie) 처리

정렬 기준 컬럼에 중복 값이 있으면 페이지 경계가 흔들린다. 해결책은 고유 컬럼(보통 PK)을 정렬 마지막에 추가하는 것이다.

-- 나쁜 예: status가 같은 행이 많으면 페이지 경계가 불안정
SELECT id, status, created_at FROM orders
WHERE status > :last_status
ORDER BY status, created_at LIMIT 20;

-- 좋은 예: id를 tiebreaker로 추가
SELECT id, status, created_at FROM orders
WHERE (status, created_at, id) > (:last_status, :last_created_at, :last_id)
ORDER BY status ASC, created_at ASC, id ASC
LIMIT 20;

NULL 정렬 주의

NULL은 정렬 순서가 DB마다 다르다. PostgreSQL은 기본적으로 NULLS LAST(ASC) / NULLS FIRST(DESC)이고, MySQL은 NULL을 가장 작은 값으로 취급한다. NULL이 포함된 컬럼을 keyset 커서로 쓰면 WHERE 조건이 예상과 달라질 수 있다.

실용적인 대응:

• 가능하면 keyset 기준 컬럼을 NOT NULL로 설계한다.
• NULL이 불가피하면 COALESCE로 대체값을 정하고 일관된 NULL 처리를 쿼리와 커서에 모두 적용한다.

커서(Cursor) 토큰 인코딩

API에서 keyset를 노출할 때 클라이언트가 내부 컬럼 이름과 값을 직접 다루게 하면 결합도가 생긴다. 실무에서는 마지막 행의 값을 불투명(opaque) 토큰으로 인코딩해 넘긴다.

import json, base64

def encode_cursor(created_at: str, last_id: int) -> str:
    payload = {"ca": created_at, "id": last_id}
    return base64.b64encode(json.dumps(payload).encode()).decode()

def decode_cursor(cursor: str) -> dict:
    return json.loads(base64.b64decode(cursor).decode())

# 예: eyJjYSI6ICIyMDI2LTA2LTAxIDEwOjAwOjAwIiwgImlkIjogNDIwMH0=

커서 토큰의 특성:

• 상태가 없다(stateless): 서버가 세션이나 캐시를 유지할 필요가 없다.
• 변경 내성: API 내부 구조를 바꿀 때 토큰 포맷만 업데이트하면 된다.
• 보안 주의: Base64는 암호화가 아니다. 민감한 값을 커서에 포함하지 않는다. 필요하면 HMAC 서명을 추가한다.

세 가지 방식 비교

대량 추출(Export) 패턴

보고서 생성이나 데이터 마이그레이션처럼 전체 테이블을 순차적으로 읽어야 할 때 keyset는 특히 강력하다.

-- 배치 추출: PK 기반 keyset
-- 처음 시작
SELECT id, data, created_at
FROM events
WHERE id > 0
ORDER BY id ASC
LIMIT 5000;

-- 마지막 id=42000 이후 계속
SELECT id, data, created_at
FROM events
WHERE id > 42000
ORDER BY id ASC
LIMIT 5000;

PK를 keyset로 쓰면 인덱스 설계가 필요 없고, PK는 항상 고유하므로 동점 문제도 없다. 읽기 중 새 행이 삽입돼도 id > last_id 조건 덕분에 중복 없이 진행된다.

MySQL에서 COUNT와 OFFSET 페이지네이션을 함께 쓸 때

관리자 UI처럼 "전체 N페이지 중 현재 M페이지" 표시가 필요한 경우 OFFSET을 완전히 없애기 어렵다. 이때는 두 가지를 분리한다.

-- 전체 건수 (SELECT COUNT): 캐싱 또는 approximation 허용
SELECT COUNT(*) FROM articles WHERE status = 'published';  -- 캐싱 가능

-- 실제 페이지 데이터: keyset 사용
SELECT id, title, created_at FROM articles
WHERE status = 'published'
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

COUNT 쿼리는 자주 바뀌지 않는 집계이므로 Redis 등에 TTL을 두고 캐싱한다. 페이지 데이터는 keyset로 빠르게 가져온다.

실전 시나리오: 피드 API 무한 스크롤

-- 테이블 구조
CREATE TABLE feed_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    published_at DATETIME(3)   NOT NULL,
    content     TEXT,
    INDEX idx_feed_user_time (user_id, published_at DESC, id DESC)
);

-- 첫 로드 (cursor 없음)
SELECT id, content, published_at
FROM feed_items
WHERE user_id = 1234
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- 다음 로드 (last: published_at='2026-06-26 15:00:00.000', id=98765)
SELECT id, content, published_at
FROM feed_items
WHERE user_id = 1234
  AND (published_at, id) < ('2026-06-26 15:00:00.000', 98765)
ORDER BY published_at DESC, id DESC
LIMIT 20;

(user_id, published_at DESC, id DESC) 인덱스는 WHERE의 user_id = 1234 등치 조건 + keyset의 (published_at, id) < (...) 범위 조건을 모두 커버한다. 피드 아이템이 1억 건이 쌓여도 실행 시간이 일정하다.

References

• Markus Winand, "We need tool support for keyset pagination" — https://use-the-index-luke.com/no-offset
• Vlad Mihalcea, "SQL Seek Method or Keyset Pagination" — https://vladmihalcea.com/sql-seek-keyset-pagination/
• Citus Data Blog, "Five ways to paginate in Postgres, from the basic to the exotic" — https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/
• CockroachDB Docs, "Paginate Results" — https://www.cockroachlabs.com/docs/stable/pagination
• PingCAP, "Limit/Offset Pagination vs. Cursor Pagination in MySQL" — https://www.pingcap.com/article/limit-offset-pagination-vs-cursor-pagination-in-mysql/
• AllYouNeedIsBackend, "The SQL I Love — Efficient pagination of a table with 100M records" — http://allyouneedisbackend.com/blog/2017/09/24/the-sql-i-love-part-1-scanning-large-table/
• EagleEye, "Why You Should Avoid LIMIT OFFSET for Pagination in PostgreSQL" — https://eagleeye.com/blog/why-you-should-avoid-limit-offset-for-pagination-in-postgresql
• Gusto Engineering Blog, "A Developer's Guide to API Pagination: Offset vs. Cursor-Based" — https://embedded.gusto.com/blog/api-pagination/
• ReadySet, "Optimizing SQL Pagination in Postgres" — https://readyset.io/blog/optimizing-sql-pagination-in-postgres