PostgreSQL 성능 최적화 — 인덱스·쿼리 튜닝 실전편

데이터베이스 성능 문제는 개발자에게 늘 뜨거운 감자입니다. 특히 서비스 규모가 커지고 데이터 양이 방대해질수록, 빠릿하던 애플리케이션이 느려지고 사용자 경험이 저하되는 현상을 마주하게 됩니다. 이때 가장 먼저 점검하고 최적화해야 할 부분이 바로 PostgreSQL 데이터베이스의 인덱스 전략과 쿼리 튜닝입니다.

PostgreSQL은 강력하고 유연하며 안정적인 오픈소스 관계형 데이터베이스로, 많은 스타트업과 기업에서 핵심 데이터 저장소로 활용되고 있습니다. 하지만 그 강력함만큼이나 제대로 관리하지 않으면 성능 병목의 주범이 될 수 있습니다. 이 글에서는 PostgreSQL의 성능을 극대화하기 위한 실전적인 인덱스 생성 및 관리 방법, 그리고 효율적인 쿼리 작성 기법을 코드 예제와 함께 깊이 있게 다룹니다.

1. 문제 진단: EXPLAIN ANALYZE 마스터하기

성능 최적화의 첫걸음은 정확한 문제 진단입니다. 어디서 병목이 발생하는지 모른다면, 무작정 인덱스를 추가하거나 쿼리를 수정하는 것은 오히려 독이 될 수 있습니다. PostgreSQL은 이를 위해 EXPLAIN ANALYZE라는 강력한 도구를 제공합니다.

1.1. EXPLAIN ANALYZE란?

EXPLAIN ANALYZE는 특정 SQL 쿼리의 실행 계획(Execution Plan)을 보여주고, 실제로 쿼리를 실행하면서 각 단계에 소요된 시간과 반환된 행의 수를 측정하여 출력합니다. 이를 통해 쿼리가 어떤 방식으로 데이터를 찾고 처리하는지 시각적으로 파악하고, 비효율적인 부분을 찾아낼 수 있습니다.

기본 사용법:

EXPLAIN ANALYZE
SELECT id, username, email
FROM users
WHERE created_at >= 2023-01-01
ORDER BY created_at DESC
LIMIT 10;

1.2. EXPLAIN ANALYZE 결과 해석하기

결과는 트리 구조로 나타나며, 각 노드는 쿼리 실행의 한 단계를 의미합니다. 주요 지표는 다음과 같습니다:

• cost: PostgreSQL 옵티마이저가 추정한 쿼리 실행 비용입니다. (시작 비용..총 비용) 형태로 표시되며, 이 수치가 낮을수록 일반적으로 효율적입니다.
• rows: 해당 노드에서 처리될 것으로 예상되는 행의 수입니다. (예상치)와 (실제치)가 크게 다르면 통계 정보가 오래되었거나 쿼리 예측에 문제가 있을 수 있습니다.
• actual time: 해당 노드를 실행하는 데 실제로 소요된 시간입니다. (시작 시간..총 시간) 형태로 표시됩니다. 이 값이 높은 노드가 성능 병목 지점일 가능성이 큽니다.
• buffers: 이 노드에서 사용된 공유 버퍼(shared buffers) 및 로컬 버퍼(local buffers)의 양을 보여줍니다. shared hit는 캐시에서 찾은 블록, shared read는 디스크에서 읽어온 블록을 의미합니다. shared read가 많다면 디스크 I/O가 많이 발생하고 있음을 나타냅니다.
• Seq Scan (Sequential Scan): 테이블 전체를 처음부터 끝까지 스캔하는 방식입니다. 대규모 테이블에서 Seq Scan이 보인다면, 대부분 인덱스가 필요하다는 신호입니다.
• Index Scan: 인덱스를 사용하여 특정 조건에 맞는 행을 효율적으로 찾아내는 방식입니다. Seq Scan보다 훨씬 빠릅니다.
• Bitmap Heap Scan: 비트맵 인덱스 스캔 후, 해당 비트맵을 사용하여 실제 테이블의 힙(heap)에서 데이터를 가져오는 방식입니다. 여러 인덱스를 조합할 때 효율적입니다.
• Sort: 데이터를 정렬하는 작업입니다. work_mem이 부족하면 디스크에 임시 파일을 생성하여 정렬(Disk Sort)하므로, memory가 아닌 disk로 표시되면 성능 저하의 원인이 됩니다.
• Hash Join, Merge Join, Nested Loop Join: 테이블을 조인하는 방식입니다. 각 조인 방식은 데이터 크기와 조건에 따라 효율성이 다릅니다.

예시 EXPLAIN ANALYZE 결과 (간략화):

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=100.00..100.50 rows=10 width=32) (actual time=0.080..0.082 rows=10 loops=1)
   ->  Sort  (cost=100.00..101.25 rows=25 width=32) (actual time=0.079..0.080 rows=10 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Index Scan using users_created_at_idx on users  (cost=0.42..99.00 rows=25 width=32) (actual time=0.015..0.065 rows=25 loops=1)
               Index Cond: (created_at >= 2023-01-01::date)

위 예시에서는 users_created_at_idx 인덱스를 사용하여 created_at 조건에 맞는 데이터를 효율적으로 찾고(Index Scan), 그 결과를 정렬(Sort)한 후 LIMIT을 적용하는 것을 볼 수 있습니다. actual time이 낮고 Seq Scan이 보이지 않으므로, 이 쿼리는 비교적 잘 최적화되어 있다고 판단할 수 있습니다.

2. 성능 최적화의 핵심: 인덱스 전략

EXPLAIN ANALYZE를 통해 Seq Scan이 자주 발생하거나, 특정 조건의 검색 및 정렬 작업에서 actual time이 높게 나타난다면, 인덱스 생성을 고려해야 합니다. 인덱스는 데이터 검색 속도를 비약적으로 향상시키지만, 무분별한 인덱스 생성은 쓰기 성능을 저하시키고 저장 공간을 낭비할 수 있으므로 신중해야 합니다.

2.1. 인덱스란 무엇이며 왜 필요한가?

인덱스는 데이터베이스 테이블의 특정 컬럼에 대해 검색 성능을 높이기 위해 사용하는 데이터 구조입니다. 마치 책의 목차나 찾아보기처럼, 원하는 데이터를 빠르게 찾을 수 있도록 돕습니다. 인덱스가 없으면 데이터베이스는 테이블의 모든 행을 하나씩 확인해야 하지만(Full Table Scan), 인덱스가 있으면 특정 조건을 만족하는 행을 즉시 찾아낼 수 있습니다.

2.2. 주요 인덱스 유형과 활용

PostgreSQL은 다양한 인덱스 유형을 지원하며, 각 유형은 특정 사용 사례에 최적화되어 있습니다.

2.2.1. B-tree 인덱스 (가장 일반적)

• 특징: 가장 흔하게 사용되는 인덱스 유형입니다. 균형 잡힌 트리 구조로, 대부분의 검색 및 정렬 작업에 효율적입니다.
• 주요 사용처:
  • WHERE 절의 동등(=), 범위(>, <, >=, <=) 검색
  • ORDER BY 절의 정렬
  • JOIN 조건
  • LIKE prefix%와 같은 접두사 일치 검색
• 생성 예시:
  sql ⧉ 복사

  CREATE INDEX idx_users_email ON users (email);
  CREATE INDEX idx_products_price_category ON products (price, category_id); -- 다중 컬럼 인덱스

2.2.2. GIN (Generalized Inverted Index) 인덱스

• 특징: 여러 개의 값이 하나의 항목에 저장되는 경우(예: 배열, JSONB)나 전문 검색(Full-Text Search)에 강력합니다. 역색인(inverted index) 구조를 가집니다.
• 주요 사용처:
  • ARRAY 타입 컬럼 내부 값 검색
  • JSONB 타입 컬럼 내부 키-값 검색
  • tsvector를 이용한 전문 검색 (@@ 연산자)
• 생성 예시:
  sql ⧉ 복사

  -- JSONB 컬럼 내부의 tags 배열 검색
  CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
  
  -- 전문 검색을 위한 인덱스
  CREATE INDEX idx_documents_fulltext ON documents USING GIN (to_tsvector(english, content));

2.2.3. GiST (Generalized Search Tree) 인덱스

• 특징: B-tree나 GIN으로는 처리하기 어려운 복잡한 데이터 타입(예: 지리 공간 데이터, 범위 타입) 및 비표준 연산자에 대한 인덱스를 지원하는 유연한 템플릿입니다.
• 주요 사용처:
  • PostGIS를 이용한 공간 데이터 검색 (예: 특정 반경 내 위치 찾기)
  • range 타입 컬럼 검색 (예: 시간 범위 중복 체크)
  • 전문 검색 (GIN과 비교하여 쓰기 성능이 좋지만, 검색 성능은 GIN이 더 좋을 수 있음)
• 생성 예시:
  sql ⧉ 복사

  -- PostGIS 공간 데이터 검색 (geom은 geometry 타입 컬럼)
  CREATE INDEX idx_locations_geom ON locations USING GiST (geom);
  
  -- 범위 타입 검색 (time_range는 tstzrange 타입 컬럼)
  CREATE INDEX idx_events_time_range ON events USING GiST (time_range);

2.2.4. Hash 인덱스 (제한적 사용)

• 특징: 동등 비교(equality comparison)에만 사용할 수 있습니다. B-tree보다 작고 빠를 수 있지만, 복구 불가능, 유니크 제약 불가, 범위 검색 불가능 등 여러 제한이 있어 권장되지 않습니다.
• 주요 사용처: 극히 제한적인 경우에만 고려됩니다. 대부분 B-tree가 더 좋은 대안입니다.

인덱스 유형별 요약 표:

2.3. 인덱스 생성 시 고려사항 및 고급 전략

2.3.1. 컬럼 선택의 중요성

• 높은 카디널리티(Cardinality): 고유한 값의 종류가 많은 컬럼(예: email, user_id, UUID)에 인덱스를 생성하는 것이 효과적입니다. 성별처럼 카디널리티가 낮은 컬럼은 인덱스 효율이 떨어집니다.
• WHERE, ORDER BY, JOIN 조건: 이 절에 자주 사용되는 컬럼에 인덱스를 생성합니다.
• 쓰기 작업 오버헤드: 인덱스는 데이터를 삽입, 업데이트, 삭제할 때 함께 갱신되어야 하므로, 쓰기 작업이 잦은 테이블에 너무 많은 인덱스는 성능 저하를 초래합니다. 일반적으로 테이블당 3~5개 정도의 인덱스가 적절합니다.

2.3.2. 다중 컬럼 인덱스 (복합 인덱스)

두 개 이상의 컬럼을 조합하여 인덱스를 생성할 수 있습니다. 컬럼의 순서가 매우 중요합니다. PostgreSQL은 **좌측 접두사 규칙(Leftmost Prefix Rule)**을 따릅니다.

• CREATE INDEX idx_users_name_age ON users (name, age);
  • WHERE name = 홍길동 (인덱스 사용)
  • WHERE name = 홍길동 AND age = 30 (인덱스 사용)
  • WHERE age = 30 (인덱스 사용 불가)
  • WHERE name = 홍길동 ORDER BY age (인덱스 부분 사용, 정렬에 도움)

가장 자주 사용되는 컬럼을 맨 앞에 두는 것이 일반적인 전략입니다.

2.3.3. 부분 인덱스 (Partial Indexes)

테이블의 특정 조건에 해당하는 행에만 인덱스를 생성합니다. 데이터의 특정 부분만 자주 쿼리될 때 유용합니다.

예시: status가 active인 사용자만 자주 검색되는 경우

CREATE INDEX idx_users_active_email ON users (email) WHERE status = active;

이렇게 하면 인덱스 크기를 줄이고, 인덱스 유지 보수 비용도 절감할 수 있습니다.

2.3.4. 표현식 인덱스 (Expression Indexes)

컬럼 값 자체 대신, 컬럼에 함수를 적용한 결과에 인덱스를 생성합니다. WHERE 절에서 함수를 사용하는 경우에 유용합니다.

예시: 대소문자 구분 없이 이메일을 검색하는 경우

CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 쿼리 시
SELECT * FROM users WHERE lower(email) = test@example.com;

2.3.5. 인덱스 관리 및 모니터링

• 사용되지 않는 인덱스 찾기: pg_stat_user_indexes 뷰를 사용하여 idx_scan (인덱스 스캔 횟수)이 낮은 인덱스를 찾아 제거를 고려할 수 있습니다.
  sql ⧉ 복사

  SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
  FROM pg_stat_user_indexes
  ORDER BY idx_scan ASC;

• 인덱스 재생성 (REINDEX): 인덱스가 오랫동안 사용되거나 많은 삽입/삭제 작업으로 인해 조각화(fragmentation)될 수 있습니다. REINDEX 명령으로 인덱스를 재구성하여 효율성을 높일 수 있습니다.
  sql ⧉ 복사

  REINDEX INDEX idx_users_email;
  REINDEX TABLE users; -- 테이블의 모든 인덱스 재생성

  REINDEX는 잠금(lock)을 유발할 수 있으므로, 서비스 운영 중에는 CONCURRENTLY 옵션을 사용하는 것을 고려해야 합니다.
  sql ⧉ 복사

  REINDEX INDEX CONCURRENTLY idx_users_email;

3. 쿼리 튜닝 심화: 더 효율적인 SQL 작성법

인덱스만으로는 모든 성능 문제를 해결할 수 없습니다. 비효율적인 쿼리 작성 습관은 아무리 좋은 인덱스도 무용지물로 만들 수 있습니다.

3.1. 피해야 할 쿼리 안티 패턴

3.1.1. SELECT * 사용 자제

필요한 컬럼만 명시적으로 선택하세요. SELECT *는 다음과 같은 문제를 야기합니다:

• 불필요한 데이터 전송: 네트워크 대역폭 낭비.
• 디스크 I/O 증가: 데이터베이스가 더 많은 데이터를 읽어야 함.
• 인덱스 온리 스캔(Index-Only Scan) 불가: 인덱스에 모든 정보가 있더라도, SELECT *는 결국 힙(Heap)에서 데이터를 가져와야 합니다.

3.1.2. LIKE %keyword (앞에 와일드카드)

LIKE %keyword 형태의 검색은 B-tree 인덱스를 사용할 수 없습니다. 인덱스는 접두사 기반으로 정렬되기 때문입니다.

• 해결책:
  • LIKE keyword%는 인덱스를 사용할 수 있습니다.
  • 앞에 와일드카드가 필요하다면 전문 검색(Full-Text Search) 기능을 활용하거나 GIN/GiST 인덱스를 고려해야 합니다.
  • 외부 검색 엔진(Elasticsearch 등)을 사용하는 것도 좋은 방법입니다.

3.1.3. WHERE 절의 컬럼에 함수 적용

인덱스 컬럼에 함수를 적용하면 옵티마이저는 인덱스를 사용하지 않고 테이블 전체를 스캔할 수 있습니다.

-- 비효율적 (인덱스 사용 불가)
SELECT * FROM users WHERE DATE(created_at) = 2023-01-01;

-- 효율적 (created_at 인덱스 사용 가능)
SELECT * FROM users WHERE created_at >= 2023-01-01 AND created_at < 2023-01-02;

함수 적용이 필요하다면, 위에서 설명한 표현식 인덱스를 고려하세요.

3.1.4. OR 조건 남용

WHERE 절에 OR 조건을 많이 사용하면 옵티마이저가 여러 인덱스를 효율적으로 조합하기 어렵습니다.

• 해결책:
  • UNION ALL을 사용하여 여러 SELECT 문으로 분리하고 결과를 합치는 것을 고려할 수 있습니다.
  sql ⧉ 복사

  -- 비효율적일 수 있음
  SELECT * FROM products WHERE category_id = 1 OR brand_id = 5;
  
  -- 더 효율적일 수 있음 (두 컬럼에 인덱스가 있는 경우)
  SELECT * FROM products WHERE category_id = 1
  UNION ALL
  SELECT * FROM products WHERE brand_id = 5 AND category_id != 1; -- 중복 방지

  UNION ALL을 사용할 때는 중복을 제거할 필요가 없다면 UNION 대신 UNION ALL을 사용하여 중복 제거 비용을 피하는 것이 좋습니다.

3.1.5. 서브쿼리 남용

복잡한 서브쿼리는 옵티마이저가 최적화하기 어렵거나, 불필요한 임시 테이블 생성을 유발할 수 있습니다.

• 해결책: 가능한 경우 JOIN으로 대체하는 것을 고려하세요. JOIN은 일반적으로 서브쿼리보다 더 효율적입니다.

3.2. 효율적인 쿼리 작성 기법

3.2.1. JOIN 최적화

• 적절한 조인 순서: PostgreSQL 옵티마이저가 자동으로 최적의 조인 순서를 결정하지만, 때로는 개발자가 쿼리 구조를 통해 힌트를 줄 수 있습니다. 일반적으로 필터링되는 행의 수가 적은 테이블을 먼저 조인하는 것이 좋습니다.
• INNER JOIN vs LEFT JOIN: 필요한 경우에만 LEFT JOIN을 사용하고, 그렇지 않다면 INNER JOIN을 사용하여 불필요한 행을 미리 제거하는 것이 좋습니다.

3.2.2. CTE (Common Table Expressions) 활용

CTE는 복잡한 쿼리를 가독성 좋게 분리하고, 재사용 가능한 서브쿼리를 정의하는 데 유용합니다. 때로는 옵티마이저가 CTE를 더 효율적으로 처리하도록 돕기도 합니다.

WITH recent_users AS (
    SELECT id, username
    FROM users
    WHERE created_at >= 2024-01-01
),
active_posts AS (
    SELECT user_id, COUNT(*) AS post_count
    FROM posts
    WHERE status = published
    GROUP BY user_id
)
SELECT ru.username, ap.post_count
FROM recent_users ru
JOIN active_posts ap ON ru.id = ap.user_id
WHERE ap.post_count > 5;

3.2.3. LIMIT & OFFSET 최적화 (페이지네이션)

OFFSET이 큰 값을 가질수록 성능이 저하됩니다. 데이터베이스는 OFFSET까지의 모든 행을 읽은 후 버리기 때문입니다.

-- OFFSET이 클수록 느려짐
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10000;

• 해결책: 키셋 페이지네이션 (Keyset Pagination / Cursor Pagination)
  마지막으로 조회한 행의 값을 기준으로 다음 페이지를 조회하는 방식입니다.
  sql ⧉ 복사

  -- 첫 페이지
  SELECT * FROM products ORDER BY id LIMIT 10;
  
  -- 다음 페이지 (마지막으로 조회한 id가 100이었다면)
  SELECT * FROM products WHERE id > 100 ORDER BY id LIMIT 10;

  이 방식은 OFFSET을 사용하지 않으므로, 대규모 데이터셋에서 훨씬 효율적입니다.

3.3. VACUUM 및 ANALYZE의 중요성

PostgreSQL은 MVCC(Multi-Version Concurrency Control) 아키텍처를 사용합니다. 데이터가 업데이트되거나 삭제될 때, 실제 데이터는 즉시 물리적으로 제거되지 않고 "데드 튜플(dead tuples)"로 남아있게 됩니다.

• VACUUM: 데드 튜플이 차지하는 공간을 회수하여 재사용 가능하게 만듭니다. 주기적인 VACUUM은 테이블 크기 증가를 방지하고, 인덱스 효율성을 유지하는 데 필수적입니다.
• ANALYZE: 테이블 및 인덱스의 통계 정보를 수집하여 옵티마이저가 최적의 쿼리 실행 계획을 수립할 수 있도록 돕습니다. 통계 정보가 오래되면 옵티마이저가 잘못된 판단을 할 수 있습니다.

PostgreSQL은 기본적으로 autovacuum 데몬을 통해 이 작업을 자동으로 수행합니다. 하지만 특정 테이블의 변경량이 많거나, 통계 정보가 빠르게 변하는 경우에는 수동으로 VACUUM ANALYZE를 실행하거나 autovacuum 설정을 튜닝해야 할 수 있습니다.

VACUUM ANALYZE users; -- users 테이블에 대해 VACUUM 및 ANALYZE 수행

4. 고급 튜닝 팁 및 고려사항

인덱스 및 쿼리 튜닝 외에도 PostgreSQL 성능에 영향을 미치는 요소들이 있습니다.

4.1. PostgreSQL 설정 파라미터 튜닝

postgresql.conf 파일에서 시스템 자원에 맞게 주요 파라미터를 조정하여 성능을 향상시킬 수 있습니다.

• shared_buffers: PostgreSQL이 사용하는 공유 메모리 버퍼 크기. RAM의 25% 정도를 할당하는 것이 일반적입니다. (예: shared_buffers = 2GB)
• work_mem: 정렬(sort) 및 해시(hash) 작업에 사용되는 메모리. 이 값이 너무 작으면 디스크에 임시 파일을 생성하여 작업하므로 성능이 저하됩니다. (예: work_mem = 64MB)
• maintenance_work_mem: VACUUM, CREATE INDEX, ALTER TABLE 등 유지 보수 작업에 사용되는 메모리. shared_buffers보다 크게 설정할 수 있습니다. (예: maintenance_work_mem = 512MB)
• effective_cache_size: PostgreSQL이 사용 가능한 OS 캐시를 포함한 총 캐시 크기를 옵티마이저에게 알려주는 힌트입니다. 실제 메모리 크기에 가깝게 설정합니다.