DB프로그래밍

[PostgreSQL] 오라클의 ROW_NUMBER() OVER와 같은 기능), 페이징 처리 관련 SQL 작성법

관리자

PostgreSQL에서 Oracle의 ROW_NUMBER() OVER (윈도우 함수) 기능과 페이징 처리(Paging)를 구현하는 방법은 거의 동일하거나 훨씬 간결합니다.

상황별 SQL 작성법을 나누어 안내해 드립니다.

1. ROW_NUMBER() OVER

(그룹별 순위 매기기)

PostgreSQL은 Oracle과 동일한 ANSI 표준 윈도우 함수 구문을 완벽하게 지원합니다. 따라서 Oracle에서 쓰시던 문법 그대로 사용하시면 됩니다.

💡 특정 그룹별로 1등만 뽑아오기 (예: 부서별 최고 급여 시용자)

SELECT 부서ID, 사원명, 급여
FROM (
    SELECT 부서ID, 사원명, 급여,
           ROW_NUMBER() OVER(PARTITION BY 부서ID ORDER BY 급여 DESC) as rn
    FROM 사원테이블
) t
WHERE t.rn = 1; -- Oracle과 완벽히 동일한 방식

2. 페이징 처리 (Paging) 구현하기

PostgreSQL에서는 페이징 처리를 할 때 Oracle처럼 ROWNUM을 서브쿼리로 두 번 감싸는 복잡한 방법을 쓸 필요가 없습니다. LIMIT와 OFFSET을 사용하면 단 한 줄로 끝납니다.

페이징 처리 공식

  • LIMIT: 한 페이지에 보여줄 행의 개수 (pageSize)
  • OFFSET: 건너뛸 행의 개수 ((pageNumber - 1) * pageSize)

예제 1 > 1페이지 (1번~10번 데이터)

SELECT * FROM 게시판
ORDER BY 등록일 DESC
LIMIT 10 OFFSET 0; -- 앞에 0개 건너뛰고 10개 가져옴


예제 2 > 2페이지 (11번~20번 데이터)

SELECT * FROM 게시판
ORDER BY 등록일 DESC
LIMIT 10 OFFSET 10; -- 앞에 10개 건너뛰고 그다음 10개 가져옴

3. 표준 SQL 방식의 페이징 처리 (FETCH + OFFSET)

앞서 배운 표준 SQL 구문을 사용해 페이징을 처리할 수도 있습니다. LIMIT 방식과 성능은 같으며 문법적 취향에 따라 선택하시면 됩니다.

-- 2페이지 (11번~20번 데이터) 예시
SELECT * FROM 게시판
ORDER BY 등록일 DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;



오라클 vs PostgreSQL 페이징 가독성 비교

데이터베이스페이징 쿼리 스타일
Oracle (Old)SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (...) a WHERE ROWNUM <= 20) WHERE rnum >= 11; (서브쿼리 필수)
PostgreSQLSELECT * FROM 테이블 ORDER BY 기준 LIMIT 10 OFFSET 10; (직관적이고 단순함)


대용량 테이블에서 OFFSET을 사용할 때 발생하는 성능 저하 문제를 해결하는 최적화 방법(No-Offset 페이징 등)

대용량 테이블에서 OFFSET 1000000 LIMIT 10을 수행하면, 데이터베이스는 앞의 100만 건을 실제로 전부 읽고 정렬한 뒤 버리는 작업을 수행합니다. 이 때문에 페이지가 뒤로 갈수록 쿼리가 급격히 느려집니다.

이를 해결하기 위한 대표적인 최적화 방법 3가지를 안내해 드립니다.

1. No-Offset 페이징 (커서 기반 페이징)

가장 효과적인 방법으로, “내가 마지막으로 본 데이터의 식별자(ID, 날짜 등)를 조건문(WHERE)으로 걸어 그 다음부터 읽는 방식”입니다. 이전 위치를 기억하는 ‘커서’를 쓰기 때문에 아무리 뒷페이지로 가도 늘 첫 페이지만큼 빠릅니다.

  • 1페이지 조회sqlSELECT id, title, created_at FROM post ORDER BY id DESC LIMIT 10; -- 결과로 나온 마지막 데이터의 id가 '54321'이라고 가정
  • 2페이지 조회 (No-Offset)sqlSELECT id, title, created_at FROM post WHERE id < 54321 -- OFFSET 10 대신 식별자 조건문 사용! ORDER BY id DESC LIMIT 10;


📊 일반 페이징 vs No-Offset 성능 및 특징 비교

항목일반 페이징 (OFFSET)No-Offset (커서 기반)
속도 (뒷페이지)읽은 양이 많아져 매우 느려짐언제나 첫 페이지처럼 일정하게 빠름
인덱스 활용OFFSET 구간은 인덱스를 타도 스캔 낭비 발생WHERE 조건으로 인덱스를 타고 곧바로 점프
적합한 서비스일반적인 웹 커뮤니티 (1..2..3.. 페이지 번호 클릭)무한 스크롤, 더보기 버튼 (인스타그램, 유튜브 등)
데이터 정합성조회 중 새 글이 써지면 데이터 중복 노출됨새 글이 써져도 중복 없이 완벽히 정합성 유지

2. 커버링 인덱스 (Covering Index) 활용

만약 서비스 기획상 반드시 ‘페이지 번호(1, 2, 3…)’를 제공해야 해서 OFFSET을 버릴 수 없다면, 커버링 인덱스 기법을 적용해야 합니다.

실제 데이터 레코드(Row) 전체를 읽지 않고, 인덱스에 포함된 컬럼(예: ID와 정렬기준)만 가지고 빠르게 OFFSET을 계산한 뒤, 최종 당첨된 10건만 실제 테이블에서 가져오는 방식입니다.

-- [전제조건] id와 created_at 컬럼으로 복합 인덱스가 생성되어 있어야 합니다.
SELECT p.*
FROM post p
JOIN (
    -- 인덱스 안에서만 놀기 때문에 100만 건을 건너뛰어도 매우 빠름
    SELECT id 
    FROM post 
    ORDER BY created_at DESC, id DESC
    LIMIT 10 OFFSET 1000000 
) filter ON p.id = filter.id;

3. 최대 페이지 제한 (UI/UX 타협)

구글이나 네이버 검색을 보면 검색 결과가 수억 건이 넘어도 최대 100페이지(또는 1,000건)까지만 노출하고 그 뒤는 보여주지 않습니다.

실제 사용자가 5만 번째 페이지를 클릭할 확률은 극히 낮기 때문에, OFFSET이 감당할 수 있는 마지노선(예: 1만 건)까지만 조회를 허용하고 그 이상은 “검색 기간을 좁혀주세요”라는 메시지로 유도하는 것이 데이터베이스 평화를 지키는 가장 현실적인 방법 중 하나입니다.


현재 개발 중이신 화면이 번호를 누르는 페이징인가요, 아니면 무한 스크롤(더보기) 형태인가요?

무한 스크롤과 번호식 페이징은 데이터베이스 관점에서 최적화 접근 방식이 완전히 다릅니다.

실무에서 바로 사용할 수 있도록 가장 대중적인 상황(게시글 테이블 post, 정렬 기준 등록일시(created_at) DESC + ID DESC)을 기준으로 두 가지 방식의 최적화 SQL과 인덱스 설계법을 모두 정리해 드립니다.

⚡ 선결 과제: 필수 인덱스(Index) 생성

두 방식 모두 최적화 성능을 내려면 정렬 기준과 식별자를 묶은 복합 인덱스가 반드시 필요합니다.

sql

CREATE INDEX idx_post_created_id ON post (created_at DESC, id DESC);

1. 무한 스크롤 / 더보기 (No-Offset 방식)

무한 스크롤은 OFFSET을 아예 쓰지 않는 것이 핵심입니다. 이전 페이지의 마지막 데이터(커서)를 기준으로 그보다 과거의 데이터(WHERE)를 조회합니다.

💻 1페이지 (최초 조회)

SELECT id, title, created_at, writer
FROM post
ORDER BY created_at DESC, id DESC
LIMIT 11; -- 💡 팁: 10개만 보여줄 거라면 11개를 조회합니다. (다음 페이지가 있는지 확인용)
  • 결과로 나온 마지막(10번째) 데이터가 created_at = '2026-10-16 12:00:00', id = 54321이라고 가정합니다.


💻 2페이지 (더보기 클릭 시)

직전의 마지막 날짜와 ID를 조건문에 넣어 인덱스를 타고 곧바로 원하는 위치로 점프합니다.

SELECT id, title, created_at, writer
FROM post
WHERE (created_at, id) < ('2026-10-16 12:00:00'::timestamp, 54321) -- 💡 PostgreSQL 행 비교 연산 활용
ORDER BY created_at DESC, id DESC
LIMIT 11;

💡 PostgreSQL 꿀팁 (Row Constructor)
WHERE (created_at, id) < (값, 값) 구문은 PostgreSQL이 지원하는 다중 컬럼 비교 방식입니다. “날짜가 더 과거이거나, 날짜가 같다면 ID가 더 작은 것”을 의미하며, 생성한 복합 인덱스를 완벽하게 탑니다.

2. 번호 누르는 페이징 (지연된 조인 / 커버링 인덱스 방식)

사용자가 500번째 페이지(예: LIMIT 10 OFFSET 5000)를 누르면 OFFSET을 피할 수 없습니다. 이때는 테이블의 모든 컬럼(SELECT *)을 읽지 말고, 인덱스만 읽어서 가볍게 ID 10개만 먼저 걸러내는 방식을 사용합니다.

💻 500페이지 조회 (예시)

SELECT p.id, p.title, p.created_at, p.writer
FROM post p
JOIN (
    -- 1. 인덱스 내부에서만 OFFSET을 수행하여 ID 10개만 빠르게 추출 (커버링 인덱스)
    SELECT id 
    FROM post 
    ORDER BY created_at DESC, id DESC
    LIMIT 10 OFFSET 5000
) filter ON p.id = filter.id; -- 2. 당첨된 10건에 대해서만 실제 상세 데이터 결합

  • 왜 빠를까요? 일반 쿼리는 5,000건의 무거운 전체 레코드를 메모리에 올려서 버리지만, 이 방식은 가벼운 인덱스(ID, 날짜) 안에서만 5,000건을 건너뛰기 때문에 디스크 I/O가 획기적으로 줄어듭니다.

📝 요약 가이드

  • 무한 스크롤을 구현할 때는 백엔드 API에서 다음 페이지 유무(hasNext)를 같이 넘겨주기 위해 무조건 LIMIT + 1 조회와 행 비교 연산자를 활용하세요.
  • 번호식 페이징은 대용량 테이블일 때 무조건 FROM 테이블 LIMIT OFFSET을 쓰면 성능 폭탄을 맞습니다. JOIN 서브쿼리 형태로 인덱스 스캔을 유도하세요.


[연관자료]

Hi, I’m 관리자

Related Posts