일반적인 백엔드 RDBMS 성능개선 (PostgreSQL GIN 인덱스, 실행계획)

서버 애플리케이션에서 DB 성능을 개선할 때, 크게 애플리케이션 레벨과 DB 레벨로 나누어 접근합니다.
경험해온 DB 성능 개선 포인트를 정리해보려고 합니다.

 

테스트에 사용한 소스코드는 링크에 있습니다.

 

1. 애플리케이션 레벨 개선

1-1. 불필요 DB I/O 검토

대표적인 예로 N+1 쿼리 문제는 IN 절을 활용한 단일 쿼리로 개선하고, 결과는 애플리케이션에서 재조합하도록 합니다.

다양한 이유로 연관 테이블과의 JOIN이 어려운 경우, 관련된 ID(PK)를 배열 형태로 전달받아 한 번에 조회할 수 있는 인터페이스를 제공해야 합니다. 이렇게 수집한 데이터를 조합하는 작업은 주로 API Gateway, 또는 Mashup API에서 처리하게 됩니다.

또한 ORM 구현체에서 제공하는 메서드나 로직이 실제 쿼리와 일치하는지, 쿼리 로그를 분석해 의도치 않은 I/O가 발생하는 지점을 검토해야 합니다.

 

1-2. native sql 검토

ORM이 지원하지 않거나 성능상 이점이 있는 경우, native sql을 작성해 쿼리를 실행합니다.
대표적으로 PostgreSQL의 json 또는 jsonb 타입을 조건으로 필터링할 때 아래와 같은 쿼리를 사용합니다.

native sql은 DBMS 종속성이 생기기 때문에 애플리케이션 코드에서 인터페이스로 분리하여 의존성을 최소화합니다.

SELECT * FROM tb_json WHERE data->>'user' = 'user_500';

 

또한 특수한 요구사항에서 native sql을 작성하곤 했습니다. 쿼리문은 100줄이 넘는 복잡한 구조가 되기도 하며, 조인, 집계, 서브쿼리, 윈도우 함수 등이 혼합되어 있는 경우가 많습니다. 이로 인해 쿼리 성능 저하나 유지보수 어려움이 발생할 수 있어, 다음과 같은 컨벤션을 도입하여 개선 작업을 진행해왔습니다.

• SELECT 절 내 서브쿼리 사용 금지합니다.
• WHERE 절과 ON 절에서 함수 사용 지양합니다. (인덱스 무력화 방지)
• 재귀, 복잡한 쿼리는 CTE 또는 뷰 테이블로 분할합니다.

이러한 컨벤션은 코드 리뷰 체크리스트로 활용되며, 팀 전체의 쿼리 품질을 일관성 있게 유지하고자합니다.

더미 데이터 세팅 후 이어서 테스트해보겠습니다.

---

2. 데이터 세팅

제품(product) 테이블을 생성하였습니다. 리스트 및 상세 페이지를 제공하고 주문과 같은 다양한 기능과 연계된 테이블로, 실제 서비스에서의 요구사항과 유사한 시나리오로 테스트를 진행했습니다.

• 테스트 데이터는 총 500만건으로 진행했습니다.
• 인덱스와 extension(다양한 기능이 있는 PostgreSQL 플러그인)은 상황에 맞추어 생성하고 사용했습니다.
• 각 필드의 데이터 타입은 인덱스 테스트에 적합하도록 설정했습니다.
• DB 캐시 영향을 최소화하기 위해 매 테스트마다 PostgreSQL을 재시작했습니다. (Buffers: shared hit 에 따른 변수 최소화)

CREATE TABLE product (
  id SERIAL PRIMARY KEY,

  -- 일반 컬럼
  code TEXT NOT NULL,
  category TEXT NOT NULL,
  is_special boolean NOT NULL,
  created_at Date,

  -- JSON / JSONB 컬럼
  attributes_json JSON,
  attributes_jsonb JSONB
);

 

 

각 컬럼의 카디널리티입니다.

code: 5,000,004
category: 5
created_at : 9,242
is_special: 2

---

3. DB 레벨 개선

DB레벨 개선의 주요 포인트는 쿼리플랜을 해석하여 적절한 인덱스 적용입니다. PostgreSQL에는 B-TREE, Hash 인덱스와 더불어 Gist, GIN 등 다양한 인덱스를 지원합니다. PostgreSQL 에서는 오라클의 hint 처럼 직접적으로 인덱스를 강제하는 방법은 지원하지 않습니다. pg_hint_plan 확장을 지원하지만, 이 글에서는 아래와 같이 간접 방식으로 제어합니다.

--옵티마이저에게 특정 스캔 방식을 비활성화하여 실행 계획을 유도하는 방법
set enable_bitmapscan = off; 
set enable_indexscan = off;
set enable_seqscan = off;

 

Case 1. 제품 상세 조회

code 값 하나를 필터 조건에 두고 쿼리합니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM stuff WHERE code = '05QnmQAa0WR';

 

시퀀스 스캔

인덱스 옵션에서 seqscan을 강제하여 실행모드의 실행 계획을 얻었습니다.

Gather  (cost=1000.00..261043.16 rows=1 width=157) (actual time=619.769..624.877 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=1 read=234080
  ->  Parallel Seq Scan on product  (cost=0.00..260043.06 rows=1 width=157) (actual time=566.260..596.760 rows=0 loops=3)
        Filter: (code = '05QnmQAa0WR'::text)
        Rows Removed by Filter: 1666668
        Buffers: shared hit=1 read=234080
Planning Time: 0.121 ms
JIT:
  Functions: 6
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 0.767 ms, Inlining 0.000 ms, Optimization 2.698 ms, Emission 16.558 ms, Total 20.022 ms
Execution Time: 688.598 ms

주요 지표만 살펴보면

Workers Launched: 2, Parallel Seq Scan : 2개 워커에서 병렬 시퀀스 스캔을 수행했습니다.

Buffers: shared hit, read : 대부분의 데이터는 디스크에서 읽어오고 있습니다.

Execution Time : 쿼리 실행에 688.598 ms 가 걸렸습니다.

 

인덱스 스캔

이어서 인덱스 스캔으로 테스트해보겠습니다.
저는 code 필드가 첫번째 필드인 복합인덱스를 미리 만들어두었습니다. 기본적으로 B-tree 인덱스입니다.

CREATE INDEX idx_cardinality_desc ON stuff(code, category, is_special);

 

Index Scan using idx_cardinality_desc on product  (cost=0.43..8.45 rows=1 width=157) (actual time=0.254..0.255 rows=1 loops=1)
  Index Cond: (code = '05QnmQAa0WR'::text)
  Buffers: shared read=4
Planning:
  Buffers: shared hit=137 read=16
Planning Time: 3.198 ms
Execution Time: 0.322 ms

쿼리 실행에는 0.322ms 가 걸렸습니다. 단순 계산으로 500만개 데이터에 2000배 이상 개선된 결과를 나타냅니다.
여러번 반복테스트 해보아도 큰 차이는 없습니다.

 

복합인덱스

한편 해당 인덱스를 탈 수 있었던 이유는 복합인덱스의 첫번째 필드가 code기 때문입니다. 필터 조건에 category만 있었다면 해당 인덱스는 작동하지 않습니다. 이 인덱스는 code, category, is_special 순으로 인덱스가 걸려있으므로 이는 code 단일 필드로도 인덱싱이 되어있다고 볼 수 있습니다.

 

커버링인덱스

한편 B-tree 인덱스 역시 데이터 원본으로 정렬된 테이블 구조로 저장됩니다. 따라서 위에서 사용된 인덱스에 적용된 필드만 조회한다면, 굳이 실제 테이블에 접근할 필요 없이 인덱스만 가지고 조회 결과를 응답할 수 있습니다. 이를 커버링 인덱스라고 합니다. PostgreSQL에서는 INCLUDE 절을 이용해 정렬 대상이 아닌 컬럼도 인덱스에 저장할 수 있습니다.

CREATE INDEX idx_code_cover ON product(code) INCLUDE (created_at, category);

 

--쿼리
EXPLAIN (ANALYZE, BUFFERS)
select code,category FROM product where code = 'Q72uCuoCBE';

--결과
Index Only Scan using idx_code_cover on product
...생략
Execution Time: 0.204 ms

현재는 0.322ms에서 0.204ms로 개선되었으며 절대적인 차이가 크지 않지만, 충분한 크기의 데이터에서 유의미해집니다. 따라서 습관적으로 전체 조회 (select *...)하기보단, 명확한 인덱스 설계가 필요합니다.

 

해시인덱스

현재 적용된 인덱스는 B-Tree 인덱스입니다. B-Tree인덱스는 동등성(=) 뿐만 아니라 비교연산자(<,>) 에서도 인덱스 조회가 가능합니다.  Hash 인덱스가 있을 때 동등성(=) 조회에 한하여 옵티마이저는 Hash 인덱스를 고려합니다.

 

Case 1 결과.

• 데이터가 많을수록 단건 조회에서 인덱스는 사실상 필수입니다.
• 복합인덱스는 적당히 단일 또는 적당한 필드에 적용될 수 있습니다.
• 커버링인덱스 활용이 가능하면 하는게 좋습니다.
• 일치하는 값만 필터할 경우 해시인덱스를 고려할 수 있습니다.

---

Case 2. 제품 검색 최적화

단건 검색이 아닌 키워드에 해당하는 리스트 검색의 경우입니다.

 

like 검색

키워드 기반 검색을 RDBMS로 구현할 경우, 일반적으로 LIKE 문을 사용하게 됩니다.

일반적인 B-tree 인덱스는 정렬된 원본 값을 기반으로 하므로, 전체 값을 기준으로 한 LIKE 'keyword%'는 인덱스 활용이 가능하지만,

LIKE '%keyword'와 같이 접두어가 없는 검색은 인덱스를 사용할 수 없어 시퀀스 스캔이 발생합니다.

아래 예시처럼 시퀀스 스캔을 사용하고 있습니다.

--쿼리
EXPLAIN (ANALYZE, BUFFERS)
select * FROM product where code like '%NV4';

--결과
Gather  (cost=1000.00..261172.69 rows=500 width=157) (actual time=275.735..657.139 rows=20 loops=1)
  ->  Parallel Seq Scan on product  (cost=0.00..260122.69 rows=208 width=157) (actual time=247.712..606.308 rows=7 loops=3)
Execution Time: 657.658 ms

 

code 값의 일부를 별도 인덱스를 구축한다면 like 검색 최적화가 가능합니다. PostgreSQL 에서는 데이터 토큰별 인덱스 엔트리를 구성해주는 GIN 인덱스를 지원합니다. GIN 인덱스는 Elasticsearch의 역색인 방식과 유사하게, 각 데이터를 구성하는 요소(토큰)를 기반으로 색인을 구성합니다. GIN 인덱스는 값을 어떤 키워드나 요소로 색인을 만들 것인지를 Operator 로 정의할 수 있습니다. '배열 포함 여부' 같은 목적에 따라 내장 연산자(built-in operator) 를 지원하지만, Like 문 인덱스 쿼리는 pg_trgm 확장을 사용합니다. pg_trgm 은 트라이그램(gin_trgm_ops)매칭을 통한 텍스트 유사성을 판단하는 확장으로 여러가지 함수를 제공하며 토큰별 인덱스를 생성하므로 Like문에도 인덱스 적용 가능합니다.

별도 설정하지 않는 한 트라이그램은 3글자를 기준으로 인덱스를 구성하여 2글자까지의 like문에서는 seq scan이 발생합니다. 요구사항에 맞게 조절하여 사용가능합니다.

 

날짜 범위 검색

특정일자 이후에 생성된 제품만 필터할 수 있습니다.

B-tree 인덱스는 범위 조건에도 적용되므로, 캐스팅과 같은 함수를 사용하지 않는다면 인덱스 적용이 가능합니다.

Date 타입은 특히 DB에서 묵시적 캐스팅되는 경우가 많아 주의가 필요합니다.

--쿼리 
EXPLAIN (ANALYZE, BUFFERS)
select code, created_at FROM product where created_at >= '2012-01-01';

--결과 
Index Only Scan using idx_created_at on product  (cost=0.43..87087.33 rows=2644280 width=15) (actual time=0.114..421.699 rows=2627813 loops=1)
...생략
Execution Time: 495.523 ms

 

Case 2 결과.

• like 문 인덱싱은 토큰기반 색인(역색인)으로 적용합니다.
• 날짜 조회시 묵시적 캐스팅을 주의합니다.

4. 회고

실제 시나리오에 기반하여 인덱스 작동에 대한 테스트를 해보는 것은 모호할 수 있던 개념 정리에 많은 도움이 되었습니다.

특히 PostgreSQL 에 특화된 내용들에서 조금 더 알아갈 수 있던 시간이 되었습니다. 다음 글에서  Case 3. JSONB 필드의 GIN 인덱스 적용 과 Case4. Join에서 실행계획 검토 와 같은 케이스들도 살펴보도록 하겠습니다.