Quick Reference

CREATE INDEX idx_posts_user_id
ON posts (user_id);

인덱스는 테이블이 아니라 반복되는 WHERE, JOIN, ORDER BY 패턴에 맞춰 추가합니다. 읽기를 빠르게 할 수 있지만 모든 쓰기에 유지 비용이 생기며, 인덱스가 있어도 PostgreSQL이 Seq Scan을 고르는 것은 정상일 수 있습니다. 추가 전후에는 실제 쿼리와 실행 계획을 비교합니다.

문법

인덱스는 읽기 속도와 쓰기 비용을 맞바꾼다

인덱스는 특정 접근 패턴을 빠르게 만들기 위한 별도 자료 구조입니다. 기본 B-tree 인덱스가 가장 흔하지만, 인덱스가 있다고 항상 선택되거나 조회가 자동으로 빨라지는 것은 아닙니다. PostgreSQL은 통계를 바탕으로 Seq Scan, Index Scan, Bitmap Scan 등을 선택합니다. 인덱스는 INSERT·UPDATE·DELETE 때도 갱신하므로, 조회 이득과 쓰기·저장 공간 비용을 함께 맞바꿉니다.

-- 실행 계획에서 현재 접근 방식을 먼저 확인한다.
EXPLAIN SELECT * FROM posts WHERE user_id = 42;

CREATE INDEX idx_posts_user_id ON posts (user_id);

-- 같은 쿼리를 다시 확인한다. 계획은 데이터 분포에 따라 달라질 수 있다.
EXPLAIN SELECT * FROM posts WHERE user_id = 42;

인덱스는 먼저 WHERE, JOIN, ORDER BY에서 반복되는 열을 기준으로 고른다

인덱스는 "자주 조회되는 열"이 아니라 "반복해서 찾고, 조인하고, 정렬하는 방식"에 맞춰 잡아야 합니다. 즉 테이블 정의만 보고 만들기보다, 실제 쿼리 패턴을 먼저 보고 정하는 편이 맞습니다.

-- WHERE
SELECT * FROM posts WHERE user_id = 42;

-- JOIN
SELECT * FROM comments c JOIN posts p ON p.id = c.post_id;

-- ORDER BY
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

WHERE, JOIN, ORDER BY 패턴이 인덱스 후보를 결정한다

인덱스 후보는 쿼리의 WHERE 조건, JOIN의 ON 조건, ORDER BY 절에서 나옵니다. 외래 키는 자동으로 인덱스를 만들지 않습니다. 부모 행의 삭제·키 변경, 자식 테이블 조인·필터가 자주 일어나고 자식 테이블이 충분히 크면 자식 키 인덱스를 검토합니다. 고유 조회처럼 적은 행을 찾는 조건은 이득이 클 수 있고, 반환 비율이 높은 boolean·status 조건은 인덱스가 있어도 Seq Scan이 더 나을 수 있습니다.

-- 자주 쓰이는 인덱스 후보들
CREATE INDEX idx_posts_created_at ON posts (created_at DESC);  -- ORDER BY
CREATE INDEX idx_comments_post_id ON comments (post_id);       -- JOIN / FK
CREATE INDEX idx_users_email ON users (email);                 -- WHERE 고유 조회

단일 인덱스와 복합 인덱스는 맞추는 쿼리 패턴이 다르다

user_id만 자주 찾는다면 단일 인덱스로 충분하지만, WHERE user_id = ? ORDER BY created_at DESC처럼 필터와 정렬이 항상 같이 나오면 복합 인덱스가 더 직접적입니다. 복합 인덱스는 열 순서가 중요하므로, 앞쪽 열부터 실제 조건 순서에 맞아야 효율이 납니다.

-- user_id 조건과 created_at 정렬을 함께 맞춤
CREATE INDEX idx_posts_user_created
  ON posts (user_id, created_at DESC);

소형 테이블에서는 인덱스가 오히려 느릴 수 있다

PostgreSQL 옵티마이저는 테이블 통계를 기반으로 인덱스 사용 여부를 결정합니다. 테이블이 작거나 조건이 너무 많은 행을 반환하면, Index Scan의 추가 접근보다 Seq Scan이 더 빠를 수 있습니다. 이때 인덱스가 있어도 Seq Scan을 선택하는 것은 정상입니다. 특정 비율만으로 인덱스 유효성을 판단하지 말고, 운영과 비슷한 데이터에서 추정 행 수·실제 행 수·버퍼 읽기를 확인합니다.

-- 실행 계획으로 인덱스 사용 여부 확인
EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42;

-- rows 추정값이 실제와 크게 다르면 통계 갱신
ANALYZE posts;

CONCURRENTLY는 쓰기를 막지 않는 대신 더 긴 생성 절차를 쓴다

일반 CREATE INDEX는 생성 동안 대상 테이블의 INSERT·UPDATE·DELETE를 막을 수 있습니다. CREATE INDEX CONCURRENTLY는 일반 쓰기를 막지 않도록 여러 단계로 생성하지만, 더 오래 걸리고 트랜잭션 블록 안에서 실행할 수 없으며 다른 세션·트랜잭션을 기다릴 수 있습니다. 운영 테이블에서 쓰기 차단을 감당할 수 없을 때 선택하고, 실패 뒤 남은 invalid index와 배포 도구의 트랜잭션 사용 여부도 확인합니다.

-- 운영 중 쓰기 차단을 피해야 할 때의 인덱스 생성
CREATE INDEX CONCURRENTLY idx_posts_published_at
ON posts (published_at DESC)
WHERE published = true;

일반 인덱스와 UNIQUE 제약은 목적이 다르다

일반 인덱스는 조회 속도를 높이기 위한 것이고, UNIQUE 제약은 중복을 금지하면서 동시에 인덱스를 만듭니다. "빠르게 찾기만 하면 되는가"면 일반 인덱스, "값 중복 자체를 막아야 하는가"면 UNIQUE가 먼저입니다.

-- 조회 성능 목적
CREATE INDEX idx_posts_user_id ON posts (user_id);

-- 중복 방지 + 인덱스 생성
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

체크포인트

주의할 점

CREATE INDEX idx_posts_published ON posts (published);

BOOLEAN처럼 선택도가 낮은 열 하나에 단독 인덱스를 걸어도, 대부분의 행이 조건을 통과하면 PostgreSQL은 Seq Scan을 고를 수 있습니다. 이런 열은 다른 조건과 조합하거나 partial index로 좁히는 쪽이 더 낫습니다.

SELECT *
FROM users
WHERE lower(email) = 'ada@example.com';

email에 일반 인덱스가 있어도 조건에서 lower(email)처럼 함수를 씌우면 그대로는 인덱스를 못 쓸 수 있습니다. 이런 패턴이 실제로 필요하면 표현식 인덱스(CREATE INDEX ... ON users (lower(email));)를 따로 검토해야 합니다.