PostgreSQL 커서와 페이징 성능 최적화 전략

개요

대량의 데이터에서 페이징을 구현할 때 OFFSET/LIMIT 방식은 간단하지만 성능 저하를 일으킨다. 이 글에서는 PostgreSQL 커서의 기본 개념과 키셋 페이징, 서버사이드 커서를 활용한 페이징 성능 개선 방법을 초보자 관점에서 차근차근 설명한다.

왜 OFFSET/LIMIT은 느린가

OFFSET은 원하는 페이지까지 스킵하는 비용이 발생한다. 데이터가 많아질수록 스킵 비용도 증가한다. 특히 큰 OFFSET 값은 전체 스캔 또는 많은 행 건너뛰기를 동반해 쿼리 시간이 급격히 늘어난다.

문제 상황 요약

• 대량 페이지 이동 시 쿼리 비용 증가
• 정렬된 인덱스가 있어도 스킵 비용이 남음
• 사용자 경험 저하 및 DB 부하 증가

대안 1: 키셋 페이징(keyset pagination)

키셋 페이징은 마지막으로 본 항목의 키를 기준으로 다음 데이터를 조회한다. OFFSET을 사용하지 않아 스킵 비용이 거의 없다. 특히 최신 데이터 위주 탐색에 적합하다.

핵심 아이디어

• 정렬 컬럼(예: id, created_at)에 인덱스를 둔다.
• 마지막 키 이후의 행을 WHERE로 제한해 페이징한다.

예시 SQL

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

-- 이후 페이지 (마지막 항목의 키가 last_created, last_id)
SELECT id, title, created_at
FROM posts
WHERE (created_at < last_created)
   OR (created_at = last_created AND id < last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

대안 2: 서버사이드 커서(postgres cursor)

서버사이드 커서는 클라이언트가 서버에 결과 집합을 열어둔 채로 일부만 가져가게 해준다. 네트워크 왕복과 메모리 사용을 줄일 수 있어 일괄 처리(batch processing)나 스크롤 형태의 페이징에 유리하다.

커서의 특징

• 트랜잭션 범위 내에서 사용 시 일관된 결과를 제공
• WITH HOLD 옵션으로 트랜잭션 종료 후에도 유지 가능
• FETCH로 필요한 개수만큼 가져오므로 메모리 절약

SQL로 커서 사용 예

BEGIN;
DECLARE my_cursor CURSOR FOR
SELECT id, title FROM posts ORDER BY created_at DESC;

FETCH 100 FROM my_cursor; -- 100행씩 가져오기
-- 필요 시 반복 FETCH
CLOSE my_cursor;
COMMIT;

-- 트랜잭션 종료 후에도 사용하려면
DECLARE my_hold_cursor CURSOR WITH HOLD FOR
SELECT id, title FROM posts ORDER BY created_at DESC;

언어별 사용 예: Python(psycopg2)

애플리케이션 레벨에서는 클라이언트 커서를 사용해 서버사이드 커서를 제어할 수 있다. psycopg2의 named cursor를 활용하면 자동으로 서버사이드 커서가 생성된다.

간단한 Python 예

import psycopg2
conn = psycopg2.connect(dsn)
cur = conn.cursor(name='my_cursor')
cur.execute("SELECT id, title FROM posts ORDER BY created_at DESC")
for batch in iter(lambda: cur.fetchmany(100), []):
    for row in batch:
        process(row)
cur.close()
conn.close()

성능 튜닝 체크리스트

• 필요한 컬럼만 조회해 네트워크 전송량을 줄임
• 정렬 기준에 적절한 인덱스 생성
• 키셋 페이징에서 비교 조건은 복합 인덱스 순서와 일치
• 커서는 트랜잭션 범위를 고려해 사용. WITH HOLD는 리소스 장기 점유 가능
• fetch size는 네트워크와 메모리 균형을 고려해 조정

언제 어떤 방법을 선택할까

다음 기준으로 선택하면 된다.

• 사용자가 페이지 번호로 임의 접근한다면 키셋 페이징으로는 구현이 어렵다. 이 경우에는 범위를 제한한 쿼리나 적절한 캐싱이 필요
• 연속적인 스크롤(무한스크롤)이나 대량 데이터 일괄 처리에는 서버사이드 커서가 효율적
• 정확한 페이지 번호 접근성과 성능을 모두 원하면 키셋 페이징과 서버사이드 커서를 조합하거나, 페이지네이션 인덱스를 따로 설계하는 방법 고려

주의사항과 함정

• 키셋 페이징은 정렬 기준 변경 시 재설계 필요
• 커서는 서버 리소스를 사용하므로 장기간 열린 커서는 피함
• WITH HOLD는 편리하지만 세션/트랜잭션 관리가 복잡해질 수 있음

요약

OFFSET/LIMIT은 단순하지만 대규모 데이터에서 비효율적이다. 키셋 페이징은 스킵 비용을 줄여 응답성을 개선하며, 서버사이드 커서는 메모리와 네트워크를 절약하면서 대량 처리를 가능하게 한다. 상황에 따라 두 기법을 적절히 조합하고, 인덱스 및 fetch size를 튜닝하면 PostgreSQL에서 페이징 성능을 크게 개선할 수 있다.