숏컷 코드

CREATE INDEX idx_posts_user_id
ON posts (user_id);

문법

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

인덱스는 특정 열 값을 빠르게 찾기 위한 별도의 B-tree 구조입니다. 인덱스가 없으면 PostgreSQL은 Seq Scan으로 테이블 전체를 처음부터 끝까지 읽습니다. 인덱스가 있으면 B-tree를 탐색해 조건에 맞는 행 위치를 찾고, 해당 위치만 읽습니다. 그러나 이 구조는 INSERT·UPDATE·DELETE 시마다 함께 갱신해야 합니다. 인덱스가 많을수록 조회는 빠르지만 쓰기 부하가 늘어나는 트레이드오프가 있습니다.

-- 인덱스 없을 때: 전체 테이블 스캔
-- EXPLAIN → Seq Scan on posts (cost=0.00..450.00 rows=20000)

CREATE INDEX idx_posts_user_id ON posts (user_id);

-- 인덱스 있을 때: 특정 위치만 접근
-- EXPLAIN → Index Scan using idx_posts_user_id (cost=0.29..8.30 rows=3)

인덱스는 먼저 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 절에 해당 열이 등장해야 합니다. 외래 키 컬럼(user_id, post_id 등)은 JOIN과 부모 삭제 검사 양쪽에서 인덱스가 필요합니다. 높은 선택도(cardinality)를 가진 열(예: email, uuid)은 인덱스 효과가 크고, 선택도가 낮은 열(예: 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의 랜덤 I/O보다 Seq Scan의 순차 I/O가 더 빠릅니다. 이때 옵티마이저는 인덱스가 있어도 Seq Scan을 선택합니다. 또한 조건에 맞는 행이 전체의 20~30%를 넘으면 인덱스 효과가 거의 없습니다. 인덱스는 선택도가 높고 데이터가 충분히 많은 테이블에 의미 있습니다.

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

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

CONCURRENTLY 옵션은 운영 중 테이블에 인덱스를 락 없이 추가한다

일반 CREATE INDEX는 테이블에 Share Lock을 걸어 인덱스 생성 중 쓰기를 막습니다. 운영 중인 서비스에서 대형 테이블에 인덱스를 추가하면 서비스 중단이 발생할 수 있습니다. CREATE INDEX CONCURRENTLY는 쓰기 락 없이 인덱스를 생성하지만, 생성 시간이 더 걸리고 트랜잭션 안에서 사용할 수 없습니다. 운영 환경에서 인덱스를 추가할 때는 항상 이 옵션을 사용하는 것이 기본 원칙입니다.

-- 운영 중인 테이블에 락 없이 인덱스 추가
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));)를 따로 검토해야 합니다.