PostgreSQL 인덱스와 쿼리 최적화 가이드 — 느린 쿼리를 빠르게 만드는 방법

PostgreSQL에서 쿼리가 느려지는 이유와 EXPLAIN ANALYZE 사용법

PostgreSQL이 쿼리를 실행할 때 데이터를 어떻게 가져올지 결정하는 실행 계획을 세운다. 인덱스가 없는 컬럼에서 데이터를 찾을 때는 Sequential Scan을 한다. 테이블의 모든 행을 처음부터 끝까지 읽어가면서 조건에 맞는 행을 찾는 방식이다. 테이블에 행이 100건이면 빠르지만 100만 건이면 그 수백만 번의 비교가 필요하다. 데이터가 늘수록 쿼리 시간이 선형으로 늘어난다. 인덱스가 있으면 PostgreSQL은 Index Scan을 한다. 인덱스는 특정 컬럼의 값을 정렬된 형태로 저장한 별도 자료 구조다. 찾고자 하는 값을 인덱스에서 이진 탐색으로 빠르게 찾고, 해당 행의 위치로 바로 이동한다. EXPLAIN ANALYZE 명령은 쿼리 앞에 붙여서 실행하면 실행 계획과 실제 소요 시간을 보여준다. EXPLAIN만 쓰면 예상 계획만 보여주고 실제로 쿼리를 실행하지 않는다. ANALYZE를 붙이면 실제로 쿼리를 실행해서 각 단계별 실제 시간을 측정한다. 결과에서 Seq Scan이 보이고 rows 수가 크면 인덱스 추가를 검토해야 한다는 신호다. actual time 수치가 전체 실행 시간의 어느 단계에서 가장 많은 시간이 소요되는지 파악하는 데 쓰인다.

PostgreSQL 인덱스의 종류와 B-tree 인덱스가 쓰이는 경우

PostgreSQL은 여러 종류의 인덱스를 지원한다. 가장 기본적이고 많이 쓰이는 것은 B-tree 인덱스다. CREATE INDEX 명령을 별도 설정 없이 쓰면 B-tree가 기본으로 생성된다. B-tree 인덱스는 등호(=), 범위(>, <, BETWEEN), ORDER BY, LIKE 'prefix%'처럼 접두사 매칭 조건에 효과적이다. 중간이나 끝 매칭(LIKE '%suffix')에는 B-tree가 인덱스를 쓰지 못한다. GIN 인덱스는 배열, JSONB, 전문 검색(full-text search)에 적합하다. 여러 값을 담는 컬럼에서 특정 값의 포함 여부를 검색할 때 효율적이다. GiST 인덱스는 지리 데이터, 범위 타입, 기하학 데이터에 쓰인다. Hash 인덱스는 등호 조건에만 쓸 수 있고 범위 검색에는 쓸 수 없다. BRIN 인덱스는 물리적으로 정렬된 대용량 테이블에서 범위 조건에 유용하다. 대부분의 일반적인 쿼리에서는 B-tree 인덱스만으로 충분하다. JSONB 컬럼을 자주 조회하거나 전문 검색이 필요한 경우에 GIN을 추가로 고려한다.

PostgreSQL에서 인덱스를 추가하고 효과를 확인하는 방법

인덱스는 CREATE INDEX 명령으로 추가한다. CREATE INDEX idx_users_email ON users(email) 형태로 테이블 이름, 인덱스 이름, 컬럼 이름을 지정한다. 인덱스 이름은 관례상 idx_테이블명_컬럼명 형태로 짓는다. 인덱스를 추가한 후 동일한 쿼리에 EXPLAIN ANALYZE를 다시 실행하면 실행 계획이 Seq Scan에서 Index Scan으로 바뀐 것을 확인할 수 있다. 실행 시간도 함께 줄어든다. UNIQUE 제약과 함께 인덱스를 만들려면 CREATE UNIQUE INDEX를 쓴다. 이미 PRIMARY KEY나 UNIQUE 제약이 있는 컬럼은 자동으로 인덱스가 생성되어 있다. 프로덕션 테이블에 인덱스를 추가할 때는 CREATE INDEX CONCURRENTLY를 써야 한다. 일반 CREATE INDEX는 테이블에 잠금을 걸어서 인덱스 생성 중에 쓰기 작업이 차단된다. CONCURRENTLY를 사용하면 잠금 없이 백그라운드에서 인덱스를 생성한다. 시간이 더 걸리지만 서비스 중단 없이 인덱스를 추가할 수 있다. 데이터가 많은 테이블에서 CONCURRENTLY 없이 인덱스를 추가했다가 쓰기가 막히는 상황을 만든 경험이 있어서, 프로덕션에서는 반드시 CONCURRENTLY를 쓰는 것을 원칙으로 삼게 됐다.

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 복합 인덱스 (자주 함께 쓰이는 컬럼)
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);

-- 프로덕션에서 잠금 없이 추가
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- EXPLAIN ANALYZE로 효과 확인
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

PostgreSQL 복합 인덱스와 인덱스 설계 원칙

여러 컬럼을 동시에 조건으로 쓰는 쿼리에는 복합 인덱스(Multi-column Index)가 단일 인덱스보다 효율적인 경우가 있다. CREATE INDEX idx_posts_user_status ON posts(user_id, status)처럼 두 컬럼을 한 인덱스로 묶으면 user_id와 status를 함께 필터링하는 쿼리에서 단일 인덱스보다 빠르다. 복합 인덱스에서 컬럼 순서가 중요하다. WHERE user_id = 1 AND status = 'published' 쿼리라면 user_id를 앞에 두는 게 낫다. 복합 인덱스는 앞쪽 컬럼만으로도 인덱스를 활용할 수 있지만 뒤쪽 컬럼만으로는 활용하지 못한다. user_id만 조건인 쿼리에서도 idx_posts_user_status가 사용되지만, status만 조건인 쿼리에는 이 인덱스가 쓰이지 않는다. 인덱스는 쓰기 성능과 트레이드오프가 있다. INSERT, UPDATE, DELETE 시마다 인덱스도 함께 갱신되어야 하기 때문에 쓰기 속도가 느려진다. 읽기가 많고 쓰기가 적은 컬럼에 인덱스를 추가하는 게 효과적이다. 인덱스를 무분별하게 추가하는 것보다 실제 슬로우 쿼리를 먼저 파악하고 그에 맞는 인덱스를 추가하는 방식이 실용적이다.

PostgreSQL 슬로우 쿼리를 찾고 분석하는 방법

문제가 있는 쿼리를 찾으려면 먼저 슬로우 쿼리 로그를 활성화해야 한다. postgresql.conf에서 log_min_duration_statement = 1000을 설정하면 1초 이상 걸린 쿼리를 PostgreSQL 로그에 기록한다. pg_stat_statements 익스텐션을 활성화하면 지금까지 실행된 쿼리별 통계를 pg_stat_statements 뷰에서 볼 수 있다. 총 실행 횟수, 평균 실행 시간, 총 소요 시간을 기준으로 정렬하면 개선 효과가 큰 쿼리를 우선순위로 찾을 수 있다. 쿼리를 파악했으면 EXPLAIN ANALYZE로 실행 계획을 분석한다. Seq Scan 노드에서 rows 수가 크면 인덱스 추가가 필요하다는 신호다. 조건 없이 테이블 전체를 읽는 쿼리는 LIMIT을 추가하거나 조건을 좁히는 방식으로 개선한다. JOIN이 많은 쿼리에서 각 테이블에 적절한 인덱스가 있는지 확인한다. JOIN 조건 컬럼에 인덱스가 없으면 Hash Join이나 Nested Loop에서 성능이 나빠진다. 실운영 환경에서 인덱스 추가로 해결되지 않는 슬로우 쿼리는 쿼리 구조 자체를 바꾸거나 캐싱을 도입하는 방향으로 접근해야 한다.

PostgreSQL N+1 쿼리 문제를 해결하는 방법

백엔드 애플리케이션에서 PostgreSQL 성능 문제의 흔한 원인 중 하나가 N+1 쿼리 문제다. 목록을 가져오는 쿼리 하나(1)에 각 항목의 상세 정보를 가져오는 쿼리가 항목 수만큼(N) 추가로 발생하는 패턴이다. 블로그 포스트 목록을 가져오고, 각 포스트마다 작성자 정보를 별도 쿼리로 조회하면 포스트가 100개면 쿼리가 101번 실행된다. JOIN으로 한 번에 가져오면 1번으로 줄일 수 있다. Prisma나 TypeORM 같은 ORM을 쓰는 경우 include/relations 옵션으로 관련 데이터를 한 번에 로드하면 N+1을 방지할 수 있다. 단, 이 경우 JOIN으로 인해 결과 데이터가 커질 수 있으니 필요한 컬럼만 select하는 게 좋다. ORM에서 생성되는 실제 SQL을 로그로 확인하면 N+1 문제를 발견하기 쉽다. 쿼리 로그를 켜두고 하나의 API 요청에서 몇 개의 쿼리가 실행되는지 확인하는 습관이 도움이 된다. 쿼리 수가 예상보다 많다면 N+1 또는 불필요한 중복 쿼리가 있는 것이다.