POSTGRESQL, OMS, 주문 목록 조회, SQL 튜닝, 쿼리 최적화, 2단계 페이징, OFFSET 페이징, EXPLAIN ANALYZE, 데이터베이스 성능

OMS 주문 조회 성능 개선 — 뒤 페이지가 느려지는 PostgreSQL OFFSET 페이징 최적화

들어가며 — 주문 목록은 페이지를 넘길수록 느려졌다

내가 개발·운영하고 있는 OMS(Order Management System)에는 여러 판매 채널의 주문을 한곳에서 확인하는 주문 목록 조회 기능이 있다. 배송, 취소, 반품 같은 업무가 이 화면에서 시작되기 때문에 OMS의 핵심 기능 중 하나다.

그런데 데이터가 많은 환경에서 페이지를 뒤로 넘길수록 조회가 느려지는 이슈가 있었다. 검색 조건과 페이지 크기는 같은데 응답 시간은 첫 페이지의 약 20초에서 뒤 페이지의 1분 40초까지 늘어났다. 반환 건수는 같고 페이지 번호만 달라졌기 때문에 단순히 데이터가 많아서 생긴 문제는 아니었다.

주문 조회에는 2단계 페이징을 포함해 여러 개선을 함께 적용했다. 전체 개선 결과 첫 페이지는 약 20초에서 5초로, 뒤 페이지는 약 1분 40초에서 6초로 줄었다.

주문 조회 전체 개선 결과: 측정한 페이지에서 최소 75% 단축

이 글에서는 전체 개선 작업 중에서도 페이지를 뒤로 넘길수록 느려지던 현상을 해결한 2단계 페이징에 집중해 정리한다.

목차

  1. 증상 — 뒤 페이지로 갈수록 느려졌다
  2. 원인 — 주문을 꾸민 뒤 대부분 버리고 있었다
  3. 해결 — 페이지 키를 먼저 고르는 2단계 페이징
  4. 똑같이 버리는데, 왜 응답 시간은 일정해졌을까?
  5. 결과가 달라지지 않도록 확인한 것
  6. 성능 측정 결과

증상 — 뒤 페이지로 갈수록 느려졌다

처음 발견한 패턴은 명확했다.

같은 검색 조건
같은 페이지 크기
같은 정렬 방식

1페이지  → 약 20초
중간 페이지 → 약 50초
10페이지 → 약 1분 40초

검색 범위나 반환 건수는 같은데 페이지 번호만 커질수록 응답 시간이 늘어났다. 단순히 전체 데이터가 많아서 느린 것이라면 페이지마다 비슷해야 하는데, 뒤 페이지가 유독 더 느렸다.

페이지를 뒤로 갈수록 느려진다는 현상을 보고 가장 먼저 떠올린 것은 OFFSET이었다. 페이징 쿼리에서 페이지 번호가 커질수록 함께 커지는 값이기 때문이다.

ORDER BY ordered_at DESC
OFFSET :offset
LIMIT :page_size;

PostgreSQL 공식 문서를 찾아보니, OFFSET으로 건너뛸 행도 서버 내부에서는 계속 계산하기 때문에 큰 OFFSET은 비효율적일 수 있다고 설명되어 있었다.

OFFSET이 영향을 주고 있는 것은 맞았지만, 이것만으로 첫 페이지와 뒤 페이지 사이의 큰 응답 시간 차이를 모두 설명하기는 어려웠다. 몇천 개의 키를 정렬하고 건너뛰는 것보다, 건너뛰기 전에 각 주문에 어떤 작업을 하고 있는지가 더 중요했다.

원인 — 주문을 꾸민 뒤 대부분 버리고 있었다

주문 목록은 주문 번호와 일시만 반환하지 않는다. 화면에는 상품 정보, 재고, 금액, 처리 상태, 이미지처럼 여러 테이블에서 가져온 값이 함께 필요했다.

기존 쿼리의 구조를 단순화하면 다음과 같은 모양이었다.

SELECT
    o.order_id,
    o.ordered_at,
    (SELECT ... FROM product_info WHERE ...),
    (SELECT ... FROM stock_info WHERE ...),
    (SELECT ... FROM order_amount WHERE ...),
    (SELECT ... FROM fulfillment_status WHERE ...)
FROM order_detail o
JOIN ...
WHERE ...
ORDER BY o.ordered_at DESC
OFFSET :offset
LIMIT :page_size;

실행 계획에서 가장 먼저 눈에 들어온 것은 반복 실행 횟수를 뜻하는 loops였다. 화면에는 한 페이지 분량의 주문만 보여주는데, 상품·재고·금액처럼 화면에 필요한 값을 만드는 노드는 페이지 크기보다 훨씬 많이 실행되고 있었다.

즉, PostgreSQL은 현재 페이지에 남길 주문을 먼저 고른 것이 아니었다. 검색 조건을 통과한 많은 주문에 표시용 값을 계산한 뒤, 앞 페이지에 해당하는 결과를 OFFSET으로 버리고 있었다.

검색 조건을 통과한 주문 후보
        ↓
상품·재고·금액·상태 계산
        ↓
정렬
        ↓
OFFSET만큼 버림
        ↓
현재 페이지 반환

페이지가 뒤로 갈수록 느려진 이유도 여기에 있었다.

1페이지  : 앞 페이지에 해당하는 결과를 버릴 필요가 없음
10페이지 : 앞 페이지 후보에도 화면용 정보를 계산한 뒤 폐기

물론 LIMIT이 있는 모든 PostgreSQL 쿼리가 이런 방식으로 실행되는 것은 아니다. 반대로 SQL 끝에 LIMIT이 있다고 해서 비싼 작업도 페이지 크기만큼만 실행된다고 볼 수도 없다. 결국 실제 실행 계획을 확인해야 한다.

이번 쿼리에서는 EXPLAIN (ANALYZE, BUFFERS)를 통해 비싼 작업이 LIMIT보다 넓은 범위에서 반복되고 있다는 사실을 확인할 수 있었다.

해결 — 페이지 키를 먼저 고르는 2단계 페이징

해결 방향은 단순했다. 주문을 전부 꾸민 다음 페이지를 자르지 않고, 페이지에 들어갈 주문을 먼저 고른 뒤 그 주문만 꾸미는 것이다.

Before
  전체 후보에 화면용 정보 계산
    → 정렬
    → OFFSET/LIMIT

After
  정렬에 필요한 최소 정보로 페이지 PK 선별
    → 선택된 PK의 주문에만 화면용 정보 계산

1단계 — 현재 페이지의 주문 키만 조회

안쪽 쿼리에서는 검색 조건과 정렬에 필요한 컬럼, 그리고 주문을 식별할 PK만 선택한다.

SELECT
    base.tenant_id,
    base.order_group_id,
    base.order_id
FROM order_detail base
JOIN ...
WHERE ...
ORDER BY
    base.ordered_at DESC,
    base.tenant_id,
    base.order_group_id,
    base.order_id
OFFSET :offset
LIMIT :page_size;

이 단계에서는 상품 정보나 재고, 금액, 상태를 계산하지 않는다. 뒤 페이지라서 많은 행을 건너뛰더라도 화면용 정보 조회 비용은 발생하지 않고, 검색·정렬과 PK 선별에 필요한 작업만 수행한다.

2단계 — 선택된 주문만 상세 조회

1단계 쿼리를 page라는 파생 테이블로 만들고, 기존 주문 목록 쿼리와 PK로 다시 연결한다. 괄호 안의 page가 주문 키를 고르는 1단계이고, 바깥쪽 쿼리가 선택된 주문의 상세 정보를 만드는 2단계다.

SELECT
    -- 2단계: page가 고른 주문에만 화면용 정보를 계산한다.
    o.order_id,
    o.ordered_at,
    (SELECT ... FROM product_info WHERE ...),
    (SELECT ... FROM stock_info WHERE ...),
    (SELECT ... FROM order_amount WHERE ...),
    (SELECT ... FROM fulfillment_status WHERE ...)
FROM order_detail o
JOIN (
    -- 1단계: 상세 정보는 조회하지 않고 현재 페이지의 PK만 고른다.
    SELECT
        base.tenant_id,
        base.order_group_id,
        base.order_id
    FROM order_detail base
    JOIN ...  -- 검색과 정렬에 필요한 조인만 사용
    WHERE ...
    ORDER BY
        base.ordered_at DESC, -- 화면의 기본 정렬
        -- 정렬값이 같아도 순서가 바뀌지 않도록 PK를 추가한다.
        base.tenant_id,
        base.order_group_id,
        base.order_id
    OFFSET :offset        -- 화면용 정보를 붙이기 전의 키를 건너뛴다.
    LIMIT :page_size      -- 현재 페이지에 필요한 키만 남긴다.
) page
  ON page.tenant_id = o.tenant_id
 AND page.order_group_id = o.order_group_id
 AND page.order_id = o.order_id
JOIN ...
WHERE ...
ORDER BY
    o.ordered_at DESC,
    o.tenant_id,
    o.order_group_id,
    o.order_id;

바깥 쿼리의 화면용 정보 계산은 이제 현재 페이지의 주문에만 실행된다. 기존 쿼리의 반환 컬럼과 업무 로직을 대부분 유지하면서, 비용이 큰 작업의 실행 범위만 줄인 것이다.

그런데 안쪽 쿼리를 보면 여전히 OFFSET이 남아 있다. 10페이지를 조회하려면 앞 페이지에 해당하는 주문을 건너뛰고 버리는 동작도 그대로다.

기존 쿼리도 버리고, 2단계 페이징도 버린다. 그렇다면 똑같이 행을 버리는데 왜 뒤 페이지의 응답 시간은 일정해졌을까? 차이는 버리는 행의 수가 아니라, 어떤 상태의 행을 버리느냐에 있었다.

똑같이 버리는데, 왜 응답 시간은 일정해졌을까?

페이지당 주문을 100건씩 보여주는 화면에서 10페이지를 조회한다고 가정해 보자. 10페이지의 주문 100건을 가져오려면 앞의 900건을 건너뛰어야 한다.

아래는 두 방식의 차이를 이해하기 위해 실행 흐름을 단순화한 예시다. 실제로 읽거나 정렬하는 행의 수는 데이터와 실행 계획에 따라 달라질 수 있다.

기존 쿼리는 앞 페이지에 해당하는 후보에도 상품 정보, 재고 수량, 이미지, 금액, 처리 상태를 조회한 뒤 그 결과를 버리고 있었다.

기존 방식

검색·정렬 대상 주문 후보
  → 앞 페이지 후보마다 화면용 정보 조회
      ├─ 상품 정보
      ├─ 재고 수량
      ├─ 상품 이미지
      ├─ 주문 금액
      └─ 처리 상태
  → 앞 페이지에 해당하는 900건 버림
  → 10페이지의 100건 반환

2단계 페이징에서는 주문 키 후보를 먼저 조회하고 정렬한 뒤, 앞의 900개 키를 건너뛴다. 그다음 남은 100개의 키에 해당하는 주문만 상세 조회한다.

2단계 페이징

주문 키 후보 조회·정렬
  → 앞의 900개 키 버림 (화면용 정보 조회 안 함)
  → 남은 100건에만 화면용 정보 조회
      ├─ 상품 정보
      ├─ 재고 수량
      ├─ 상품 이미지
      ├─ 주문 금액
      └─ 처리 상태
  → 10페이지의 100건 반환

두 방식 모두 OFFSET 때문에 앞의 900건을 확인해야 한다는 점은 같다. 차이는 버릴 주문의 상세 정보까지 조회하느냐다.

처리 대상 기존 방식 2단계 페이징
앞 페이지의 900건 화면용 정보를 조회한 뒤 버림 키만 확인하고 버림
현재 페이지의 100건 화면용 정보 조회 화면용 정보 조회

2단계 페이징을 적용하면 페이지가 뒤로 가더라도 상품·재고·이미지·금액·상태 조회는 현재 페이지로 선택된 주문에만 실행된다. 처리해야 할 키 후보는 늘어날 수 있지만, 비용이 큰 화면용 정보 조회는 함께 늘어나지 않으므로 전체 응답 시간의 변화가 작아졌다.

화면에 보여줄 주문을 먼저 고르고, 선택된 주문에만 비싼 조회 작업을 수행하는 것이 핵심이다.

결과가 달라지지 않도록 확인한 것

2단계 페이징은 같은 조건을 안쪽과 바깥쪽에서 다뤄야 하므로 성능만큼 결과 동등성도 중요하다.

1. 정렬 조건을 동일하게 유지한다

페이지 키를 고르는 안쪽 쿼리와 최종 결과를 반환하는 바깥쪽 쿼리의 정렬 조건이 다르면 화면 순서가 달라질 수 있다. 동적 정렬 조건을 하나의 SQL 조각으로 관리해 두 위치에서 동일하게 사용했다.

2. PK를 타이브레이커로 추가한다

ordered_at처럼 같은 값이 나올 수 있는 컬럼만으로 정렬하면 행의 순서가 완전히 결정되지 않는다. 페이지를 이동할 때 주문이 중복되거나 누락되지 않도록 정렬 조건 끝에 주문 PK를 추가했다.

ORDER BY
    ordered_at DESC,
    tenant_id,
    order_group_id,
    order_id;

3. 검색 조건과 조인 조건을 맞춘다

안쪽 쿼리에서 선택한 주문이 바깥쪽 쿼리에서도 정확히 한 행으로 연결되어야 한다. 검색 조건이 서로 다르거나 PK 일부만 연결하면 결과가 줄거나 중복될 수 있다.

4. 변경 전후 결과를 페이지별로 비교한다

다음 항목을 같은 검색 조건으로 비교했다.

  • 페이지별 주문 건수
  • 주문 PK 목록과 순서
  • 첫 페이지, 중간 페이지, 마지막 페이지
  • 정렬값이 같은 주문이 많은 조건
  • 조회 결과가 없는 조건
  • 화면에 반환하는 각 컬럼의 값

5. 실행 계획에서 범위가 실제로 줄었는지 확인한다

SQL을 두 단계로 감싸는 것만으로 최적화가 보장되지는 않는다. 변경 후 실행 계획에서 비용이 큰 노드의 loops가 페이지 크기 수준으로 줄었는지 확인했다.

성능 측정 결과

아래 수치는 2단계 페이징을 포함해 여러 개선을 함께 적용한 주문 조회의 전체 결과다. 특정 기법 하나의 단독 효과는 아니다.

페이지 위치 개선 전 개선 후 변화
첫 페이지 약 20초 5초 안팎 75% 이상 단축
중간 페이지 약 50초 5초 안팎 90% 이상 단축
뒤 페이지 약 1분 40초 6초 안팎 90% 이상 단축

가장 의미 있었던 변화는 최저 기록이 아니라 페이지별 편차가 줄어든 것이었다.

개선 전: 페이지를 넘길수록 계속 느려짐
개선 후: 어느 페이지를 조회해도 비슷한 시간

OMS 사용자는 몇 번째 페이지를 열었는지에 따라 작업 속도가 크게 달라지지 않게 됐다. 운영 측면에서도 응답 시간의 상한을 예측하기 쉬워졌다.

이번 결과는 특정 데이터와 환경에서 측정한 값이다. PostgreSQL 버전, 인덱스, 통계, 캐시 상태, 검색 조건에 따라 효과는 달라질 수 있다. 따라서 같은 패턴을 적용하더라도 반드시 실제 쿼리의 실행 계획과 응답 시간을 함께 측정해야 한다.

정리 — 먼저 고르고, 필요한 주문만 조회한다

OMS에서 주문 조회는 배송, 취소, 반품, 고객 대응이 시작되는 중요한 기능이다. 데이터가 많다는 이유로 뒤 페이지가 1분 넘게 걸리는 상태를 그대로 둘 수는 없었다.

이번 개선의 핵심은 새로운 인프라나 복잡한 기술이 아니었다.

기존: 주문 정보를 모두 계산한 뒤 페이지를 자른다.
개선: 페이지의 주문 키를 먼저 고른 뒤 필요한 정보만 계산한다.

여러 개선을 함께 적용한 결과 측정한 모든 페이지에서 응답 시간이 75% 이상 줄었다. 그중 2단계 페이징은 페이지가 뒤로 갈수록 계속 느려지던 현상을 없애는 데 중요한 역할을 했다.

비슷한 목록 조회 문제를 만난다면 OFFSET 자체만 보지 말고 다음 두 가지를 함께 확인해 볼 만하다.

  1. OFFSET으로 버리기 전에 각 후보 행에 어떤 작업을 수행하고 있는가?
  2. 그 작업을 현재 페이지의 PK를 고른 뒤로 미룰 수 있는가?

피드백은 언제나 환영입니다. 😊

참고