[DataBase] OFFSET 페이지네이션, 11초에서 2초로 줄인 과정

상품 목록 조회 API가 페이지가 깊어질수록 눈에 띄게 느려지는 문제가 있었다.

이 글은 그 원인을 EXPLAIN ANALYZE로 분석하고 인덱스 설계와 2단계 조회 방식으로 응답 시간을 약 77% 개선한 과정을 정리한 것이다.

들어가기 전에 — 데이터베이스는 SQL을 실행하기 전에 옵티마이저(Optimizer) 를 통해 가능한 실행 방법들을 비교한다.
테이블 전체를 스캔할지, 어떤 인덱스를 사용할지, 정렬을 어떻게 처리할지를 통계 기반으로 판단하고 예상 비용이 가장 낮은 실행 계획을 선택한다.

1. EXPLAIN ANALYZE로 성능 분석하기

EXPLAIN ANALYZE는 DB가 이 SQL을 어떤 방식으로 실행했고 실제로 시간이 얼마나 걸렸는지를 보여주는 분석 명령어다.

초기 상태(PK = sku)에서 이름순 정렬 조회를 OFFSET을 늘려가며 측정해봤다.

100만 건 중 10만 개 건너뛰고 20개 조회

EXPLAIN ANALYZE
SELECT *
FROM bbd2.item
ORDER BY name ASC
LIMIT 20 OFFSET 100000;

-- Limit (cost=87952.15..87954.48 rows=20 width=75)
--   (actual time=4381.155..4457.944 rows=20.00 loops=1)
-- 약 4.4초

100만 건 중 90만 개 건너뛰고 20개 조회

EXPLAIN ANALYZE
SELECT *
FROM bbd2.item
ORDER BY name ASC
LIMIT 20 OFFSET 900000;

-- Limit (cost=181125.34..181127.66 rows=20 width=75)
--   (actual time=6438.676..6527.725 rows=20.00 loops=1)
-- 약 6.5초

1차 결론

OFFSET 기반 페이징은 사용자에게 보여주는 row가 20개뿐이어도 DB는 OFFSET에 해당하는 앞선 row들을 모두 정렬하고 건너뛰어야 한다. 따라서 페이지가 깊어질수록 응답 시간이 증가한다.

2. 현재 인덱스 확인

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'bbd2'
  AND tablename = 'item';

조회 결과 item_pkey만 존재했다.

PostgreSQL은 테이블 생성 시 PRIMARY KEY를 지정하면 해당 컬럼에 유니크 B-Tree 인덱스를 자동 생성한다.

즉 name, category, active 같은 조회·정렬 컬럼에는 별도 인덱스가 없는 상태였다.

인덱스 유무 비교 (100만 건 기준, OFFSET 800000)

정렬 컬럼	인덱스	실행 시간
name	없음	약 5.9초
sku	있음 (PK)	약 4.0초

인덱스 유무만으로도 1.5배 이상 차이가 났다.

3. 인덱스란?

인덱스는 테이블의 특정 컬럼 값을 빠르게 찾기 위해 별도로 관리하는 데이터베이스 객체다.
PostgreSQL의 기본 인덱스 자료구조는 B-Tree이며, 컬럼 값을 정렬된 트리 구조로 관리한다.
B-Tree 인덱스를 쓰면 전체 테이블을 순차 탐색하지 않고 트리를 따라 필요한 데이터 위치를 빠르게 찾을 수 있다. 따라서 WHERE, ORDER BY, JOIN 조건에서 조회 성능 개선에 효과적이다.
다만 인덱스는 별도 저장 공간을 차지하고, INSERT·UPDATE·DELETE 시 함께 갱신되어야 하므로 쓰기 성능에는 부담이 될 수 있다.

4. 부분 인덱스 & 복합 인덱스 설계

검색은 주로 name / sku 기준으로 이루어지고 목록 조회에서는 활성 상품을 카테고리별로 이름순 정렬하는 요구가 많다고 판단해 인덱스를 설계했다.

CREATE INDEX idx_item_name_sku
ON bbd2.item (name, sku);

CREATE INDEX idx_item_active_category_name_sku
ON bbd2.item (category, name, sku)
WHERE active = true;

ANALYZE bbd2.item;

설계 의도는 다음과 같다.

부분 인덱스 (active = true) — 실제로 사용자와 다른 MSA가 조회하는 대상은 거래 가능한 활성 상품이다. 비활성 상품을 인덱스 대상에서 제외해 인덱스 크기와 탐색 비용을 줄였다.
복합 인덱스 컬럼 순서 — 조회 범위를 좁히는 category를 앞에, 그 안에서 탐색하기 쉬운 name을 다음에, 마지막으로 sku를 두어 동일 상품명이 있어도 정렬 순서를 안정적으로 유지하도록 했다.

테스트 쿼리:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM bbd2.item
WHERE active = true
  AND category = 'EXTERIOR'
ORDER BY name ASC, sku ASC
LIMIT 30 OFFSET 90000;

인덱스를 추가해 정렬 비용은 줄였지만, OFFSET 자체가 앞선 row를 건너뛰어야 한다는 구조적 한계는 그대로 남았다. 그래서 조회 방식 자체를 바꾸기로 했다.

5. 조회 방식 개선 — 2단계 조회

핵심 아이디어는 간단하다. 깊은 OFFSET에서 SELECT *로 전체 row를 정렬 및 조회하면 비용이 크니

① 먼저 정렬·페이징에 필요한 키(sku, name)만 조회하고 → ② 그 키로 실제 상품 데이터를 다시 가져온다.

V2 — Native Query + WITH 기반 2단계 조회

WITH page_keys AS (
    SELECT i.sku, i.name
    FROM bbd2.item i
    ORDER BY i.name ASC, i.sku ASC
    OFFSET ? LIMIT ?
)
SELECT i.*
FROM page_keys pk
JOIN bbd2.item i ON i.sku = pk.sku
ORDER BY pk.name ASC, pk.sku ASC;

먼저 가벼운 키 컬럼만으로 페이지 범위를 확정한 뒤 해당 sku에 대해서만 전체 컬럼을 조회한다.

V3 — Querydsl 기반 2단계 조회

-- 1단계: 페이지에 해당하는 sku 목록만 조회
SELECT ije1_0.sku
FROM item ije1_0
ORDER BY ije1_0.name, ije1_0.sku
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY;

-- 2단계: 해당 sku 목록으로 실제 데이터 조회
SELECT ije1_0.*
FROM item ije1_0
WHERE ije1_0.sku IN (?, ?, ... );

Native Query 대신 Querydsl로 동일한 2단계 조회를 구현했다. 동적 조건 확장과 유지보수성에서 이점이 있다.

6. k6 성능 측정 결과

개선 효과를 확인하기 위해 k6로 API 응답 시간을 측정했다.

단일 가상 사용자(VU) 기준으로 각 API를 10회 반복 호출했고, 30초 이상 응답이 없으면 timeout으로 처리했다.

측정 대상

구분	방식	조회 조건
V1	기존 SELECT * 조회	page=45000, size=20, sortBy=name, ASC
V2	Native Query + WITH 2단계 조회	동일
V3	Querydsl 2단계 조회	동일

세 방식 모두 OFFSET 900000 조건에서 측정했다.

결과

구분	평균 응답 시간	p95	실패율	비고
V1 기존 조회	약 11.28초	약 21.41초	실행마다 10% timeout	SELECT * 기반 깊은 OFFSET
V2 Native WITH	약 2.53초	약 2.67초	0%	2단계 조회
V3 Querydsl	약 2.56초	약 3.12초	0%	2단계 조회

V1 (기존 방식) — 평균 약 11.28초, 10회 중 1회가 30초 timeout으로 실패했다. 반복 측정에서도 동일하게 timeout이 발생해 깊은 OFFSET 상황에서 응답 시간이 불안정함을 확인했다. DB가 정렬 기준에 따라 앞선 90만 건을 건너뛴 뒤 20건을 가져와야 하고, 그 과정에서 전체 row를 다루는 SELECT *의 비용이 크기 때문이다.

V2 (Native + WITH) — 평균 약 2.53초, 실패율 0%. V1 대비 약 77.6% 개선, 속도로는 약 4.46배 빨라졌다.

V3 (Querydsl) — 평균 약 2.56초, 실패율 0%. V1 대비 약 77.3% 개선, 약 4.41배 빨라졌다. Native Query인 V2와 차이는 크지 않았다. 단순 속도만 보면 Native가 가장 빠르지만, 동적 조건 확장성과 유지보수성을 고려하면 Querydsl도 충분히 실용적이다.

7. 최종 결론

기존 V1은 깊은 OFFSET에서 평균 응답 시간이 10초를 넘고 timeout까지 발생했다.

반면 V2·V3의 2단계 조회는 평균 응답 시간을 약 2.5초까지 줄였고 두 방식 모두 약 77% 이상의 개선 효과를 보이며 timeout 없이 안정적으로 응답했다.

이번 개선으로 얻은 교훈을 정리하면:

단순히 인덱스를 추가하는 것만으로는 깊은 OFFSET 문제를 해결할 수 없다.
먼저 정렬·페이징에 필요한 최소 컬럼(키)만 조회하고 그 키로 실제 row를 다시 조회하는 2단계 조회가 효과적이다.
다만 OFFSET 기반 페이지네이션은 여전히 앞선 row를 건너뛰어야 하는 구조적 한계가 있다.

따라서 관리자 화면처럼 특정 페이지로 점프해야 하는 경우엔 2단계 조회 방식이 적절하고 사용자 화면처럼 "다음 페이지" 중심의 탐색이라면 커서 페이지네이션으로 확장하는 것이 더 낫다.

8. 트러블슈팅 — Querydsl에서 PostgreSQL COLLATE 정렬 오류

문제 상황

상품명은 대부분 한글이라 단순 문자열 정렬이 아니라 한국어 기준 정렬이 필요했다.

그래서 PostgreSQL 한글 collation인 ko-KR-x-icu를 name 정렬에 적용하려 했고 처음에는 Querydsl에서 stringTemplate으로 정렬 조건에 COLLATE 문법을 직접 넣었다.

StringExpression nameKo = Expressions.stringTemplate(
        "{0} COLLATE \"ko-KR-x-icu\"",
        itemJpaEntity.name
);

return new OrderSpecifier<?>[]{
        nameKo.asc(),
        itemJpaEntity.sku.asc()
};

하지만 API 호출 시 Hibernate 문법 오류가 발생했다.

org.hibernate.query.SyntaxException:
token 'COLLATE', mismatched input 'COLLATE'

Hibernate가 해석하려던 쿼리는 다음 형태였다.

SELECT itemJpaEntity
FROM ItemJpaEntity itemJpaEntity
ORDER BY itemJpaEntity.name COLLATE "ko-KR-x-icu" ASC, itemJpaEntity.sku ASC

원인 분석

Querydsl JPA는 PostgreSQL SQL을 바로 생성하는 게 아니라 먼저 JPQL/HQL을 생성하고 → Hibernate가 이를 해석해 실제 DB SQL로 변환한다.

문제는 COLLATE "ko-KR-x-icu"가 PostgreSQL 전용 SQL 문법이라는 점이었다.

Hibernate HQL 파서는 이를 JPQL 문법으로 인식하지 못했고 stringTemplate으로 끼워 넣은 COLLATE 구문에서 파싱 오류가 났다.

Querydsl JPA       → JPQL/HQL 생성
PostgreSQL COLLATE → DB 전용 SQL 문법
Hibernate HQL 파서 → COLLATE 문법 해석 실패

해결 방법

매번 쿼리에 COLLATE를 직접 넣는 대신, name 컬럼 자체의 collation을 한국어 기준으로 변경했다.

먼저 현재 collation 상태를 확인했다.

collation_name이 비어 있어 DB 기본 정렬 규칙을 따르고 있었다.

SELECT column_name, data_type, character_maximum_length, collation_name
FROM information_schema.columns
WHERE table_schema = 'bbd2'
  AND table_name = 'item'
  AND column_name = 'name';

-- name | character varying | 100 | null

컬럼 자체에 한국어 collation을 적용했다.

ALTER TABLE bbd2.item
ALTER COLUMN name TYPE varchar(100) COLLATE "ko-KR-x-icu";

이제 Querydsl에서는 PostgreSQL 전용 문법 없이 일반적인 정렬 코드만 쓰면 된다.

return new OrderSpecifier<?>[]{
        itemJpaEntity.name.asc(),   // 역정렬은 .desc()
        itemJpaEntity.sku.asc()
};

인덱스 재구성

컬럼 자체에 collation을 적용했으니 인덱스에서도 COLLATE "ko-KR-x-icu"를 따로 명시할 필요가 없어졌다.

기존 collation 명시 인덱스를 제거하고 일반 복합 인덱스로 다시 생성했다.

DROP INDEX IF EXISTS bbd2.idx_item_name_ko_sku;
DROP INDEX IF EXISTS bbd2.idx_item_category_name_ko_sku;

CREATE INDEX idx_item_name_sku
ON bbd2.item (name, sku);

CREATE INDEX idx_item_category_name_sku
ON bbd2.item (category, name, sku);

ANALYZE bbd2.item;

이로써 Querydsl이 생성하는 일반 ORDER BY name ASC, sku ASC 쿼리도 컬럼 collation 설정에 따라 한국어 기준으로 정렬되고, (name, sku) 인덱스를 활용할 수 있게 되었다.

정리

항목	내용
문제	Querydsl 정렬 조건에 PostgreSQL 전용 COLLATE를 직접 넣어 Hibernate HQL 파싱 오류 발생
해결	name 컬럼 자체의 collation을 ko-KR-x-icu로 변경, Querydsl은 일반 name.asc()만 사용
효과	동적 쿼리 유지 · 파싱 오류 제거 · 한국어 정렬 적용 · (name, sku) 인덱스 활용 · sku 동반 정렬로 결과 순서 안정화

이번 문제를 통해 DB 전용 SQL 문법을 Querydsl JPA에 직접 넣으면 Hibernate JPQL/HQL 파서와 충돌할 수 있다는 점을 확인했다. 특정 DB 기능이 정렬·비교·인덱스 기준에 지속적으로 필요하다면, 쿼리마다 우회하기보다 컬럼 정의나 인덱스 설계 단계에서 해결하는 것이 더 안정적이라는 교훈을 얻었다.

inscowoo 님의 블로그