인덱스는 "빨리 찾기 위한 별도 구조"다
테이블에 행이 많아지면 PostgreSQL은 원하는 행을 찾기 위해 테이블을 처음부터 끝까지 읽을 수 있다. 이를 순차 스캔(Seq Scan)이라고 한다. 작은 테이블에서는 순차 스캔이 오히려 빠를 수 있다. 하지만 수백만 건의 주문 중 특정 고객의 최근 주문 20건을 찾는다면 모든 행을 읽는 방식은 비용이 커진다.
인덱스는 이때 사용하는 별도 자료 구조다. 책 뒤의 찾아보기처럼 특정 값이 어느 위치에 있는지 빠르게 찾게 해 준다. PostgreSQL의 기본 인덱스는 B-tree이며, 정렬 가능한 값에 대한 동등 비교, 범위 비교, 정렬에 널리 사용된다. B-tree는 =, <, <=, >, >=, BETWEEN, IN, IS NULL 같은 조건과 잘 맞고, 인덱스 순서대로 결과를 읽는 데도 사용할 수 있다.1
다만 인덱스는 공짜가 아니다. INSERT, UPDATE, DELETE가 실행될 때 테이블뿐 아니라 관련 인덱스도 함께 갱신해야 한다. 인덱스를 많이 만들수록 쓰기 작업은 느려지고 저장 공간도 늘어난다. 성능 튜닝의 출발점은 "인덱스를 더 만들자"가 아니라 "이 쿼리가 어떤 조건과 정렬로 데이터를 찾는가"를 확인하는 것이다.
AI 생성 이미지
B-tree 인덱스를 실행 계획으로 확인하기
다음 예제는 주문 목록 화면을 생각한 것이다. 고객별 주문 조회, 상태별 조회, 최근 주문 정렬이 자주 발생한다고 가정한다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status, total_amount, ordered_at
FROM orders
WHERE customer_id = 10
ORDER BY ordered_at DESC
LIMIT 20;인덱스가 없다면 실행 계획에 다음과 비슷한 흐름이 보일 수 있다.
Seq Scan on orders
Filter: (customer_id = 10)
Sort
Sort Key: ordered_at DESC의미는 단순하다. PostgreSQL이 orders를 넓게 읽고, customer_id = 10인 행만 남긴 뒤, 남은 결과를 다시 정렬한다는 뜻이다. 행 수가 많으면 읽기와 정렬 비용이 모두 커진다.
이 쿼리에 맞춰 인덱스를 만든다.
CREATE INDEX orders_customer_ordered_idx
ON orders (customer_id, ordered_at DESC);다시 실행 계획을 확인한다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status, total_amount, ordered_at
FROM orders
WHERE customer_id = 10
ORDER BY ordered_at DESC
LIMIT 20;좋은 계획에서는 다음과 같은 단서를 볼 수 있다.
Index Scan using orders_customer_ordered_idx on orders
Index Cond: (customer_id = 10)여기서 핵심은 두 가지다. 첫째, customer_id = 10 조건으로 인덱스의 좁은 구간만 찾는다. 둘째, 인덱스가 이미 ordered_at DESC 순서를 갖고 있으므로 별도 정렬을 피할 수 있다. LIMIT 20까지 있으면 필요한 앞부분만 읽고 멈출 가능성이 높다.
반대로 다음 쿼리는 같은 인덱스를 충분히 활용하지 못할 수 있다.
SELECT id, customer_id, status, total_amount, ordered_at
FROM orders
WHERE ordered_at >= timestamp '2026-05-01'
ORDER BY ordered_at DESC;인덱스의 첫 번째 열은 customer_id인데, 쿼리는 customer_id 조건 없이 ordered_at만 사용한다. PostgreSQL이 상황에 따라 일부 활용할 수는 있지만, 기본 설계 감각은 "복합 B-tree 인덱스는 왼쪽 열부터 잘 맞아야 효율적"이라고 이해하면 된다.2
복합 인덱스는 열 순서가 성능을 바꾼다
복합 인덱스는 여러 열을 묶어 만든 인덱스다.
CREATE INDEX orders_status_ordered_idx
ON orders (status, ordered_at DESC);이 인덱스는 다음 쿼리에 잘 맞는다.
SELECT id, status, ordered_at, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY ordered_at DESC
LIMIT 50;status = 'paid'로 먼저 범위를 좁히고, 그 안에서 ordered_at DESC 순서대로 읽을 수 있기 때문이다. PostgreSQL의 다중 열 B-tree 인덱스는 앞쪽 열의 동등 조건과, 그다음 처음 만나는 범위 조건까지가 검색 범위를 줄이는 데 특히 중요하다.2
다음처럼 범위 조건이 섞이면 열 순서를 더 신중히 봐야 한다.
SELECT id, status, ordered_at, total_amount
FROM orders
WHERE status = 'paid'
AND ordered_at >= timestamptz '2026-05-01 00:00:00+09'
AND ordered_at < timestamptz '2026-06-01 00:00:00+09'
ORDER BY ordered_at DESC;(status, ordered_at DESC) 인덱스는 status 동등 조건으로 결제 주문 구간을 찾고, 그 안에서 날짜 범위를 읽는다. 월별 결제 주문 조회에 적합하다.
하지만 관리자 화면에서 상태와 무관하게 최근 주문을 보는 쿼리가 많다면 별도 인덱스가 필요할 수 있다.
CREATE INDEX orders_ordered_idx
ON orders (ordered_at DESC);두 인덱스가 비슷해 보인다고 하나만 고르면 안 된다. 다음 질문으로 판단한다.
쿼리가 항상
status를 조건으로 사용하는가?결과를 어떤 순서로 보여 주는가?
LIMIT으로 앞부분만 가져오는가?전체 주문 중 조건에 맞는 비율이 얼마나 되는가?
GROUP BY도 마찬가지다. 예를 들어 일자별 매출 집계는 먼저 WHERE 조건으로 집계 대상 행을 줄일 수 있는지 보는 것이 중요하다.
SELECT date_trunc('day', ordered_at) AS order_day,
sum(total_amount) AS revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= timestamptz '2026-05-01 00:00:00+09'
AND ordered_at < timestamptz '2026-06-01 00:00:00+09'
GROUP BY date_trunc('day', ordered_at)
ORDER BY order_day;이 쿼리는 집계 자체를 인덱스만으로 모두 해결한다고 단정할 수 없다. 그래도 (status, ordered_at) 인덱스가 있으면 집계 대상 행을 줄이는 데 도움이 된다. 인덱스 설계는 WHERE로 읽을 범위를 줄이고, 가능하면 ORDER BY 비용을 낮추는 방향에서 먼저 검토한다.
외래 키에는 필요한 인덱스를 직접 만든다
외래 키를 만들면 참조 무결성은 보장된다. 그러나 PostgreSQL이 외래 키 열에 인덱스를 자동으로 만들어 주지는 않는다. 주문 상세 테이블을 생각해 보자.
CREATE TABLE order_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id),
product_id bigint NOT NULL REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0)
);주문 상세는 보통 order_id로 자주 조회한다.
SELECT id, product_id, quantity, unit_price
FROM order_items
WHERE order_id = 1001;이때 다음 인덱스가 필요하다.
CREATE INDEX order_items_order_id_idx
ON order_items (order_id);이 인덱스는 조인에도 도움이 된다.
SELECT o.id, o.ordered_at, i.product_id, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 1001;또 하나의 이유가 있다. 부모 테이블인 orders에서 행을 삭제하거나 기본 키를 변경할 때, PostgreSQL은 자식 테이블에 참조 중인 행이 있는지 확인해야 한다. 자식 테이블의 외래 키 열에 인덱스가 없으면 이 검사가 비싸질 수 있다. 따라서 자주 조인하거나 부모 삭제 검사가 발생하는 외래 키 열에는 인덱스를 직접 설계한다.
부분 인덱스: 필요한 행만 인덱싱하기
부분 인덱스는 테이블 전체가 아니라 조건을 만족하는 일부 행만 인덱싱한다. PostgreSQL 공식 문서에서는 이를 인덱스 조건자(predicate)를 만족하는 행만 담는 인덱스로 설명한다.3
쇼핑몰 주문에서 대부분은 완료 또는 취소 상태이고, 운영자가 자주 보는 것은 아직 처리 중인 주문이라고 하자.
CREATE INDEX orders_pending_idx
ON orders (ordered_at DESC)
WHERE status IN ('paid', 'preparing');이 인덱스는 다음 쿼리에 적합하다.
SELECT id, status, ordered_at, total_amount
FROM orders
WHERE status IN ('paid', 'preparing')
ORDER BY ordered_at DESC
LIMIT 100;장점은 인덱스가 작다는 것이다. 완료된 오래된 주문까지 모두 담지 않으므로 읽기와 관리 비용이 줄어든다.
다만 부분 인덱스는 쿼리 조건이 인덱스의 WHERE 조건을 포함하거나 논리적으로 만족한다는 것을 PostgreSQL이 알아볼 수 있어야 사용된다.3 다음 쿼리는 인덱스를 쓰기 어렵다.
SELECT id, status, ordered_at
FROM orders
WHERE ordered_at >= now() - interval '1 day'
ORDER BY ordered_at DESC;최근 주문을 찾고 있지만 status IN ('paid', 'preparing') 조건이 없다. 처리 중 주문만 찾는다는 보장이 없으므로 부분 인덱스와 맞지 않는다.
부분 인덱스는 소프트 삭제에도 자주 쓴다.
CREATE INDEX products_active_name_idx
ON products (name)
WHERE deleted_at IS NULL;활성 상품만 검색하는 화면이 대부분이라면 전체 상품 인덱스보다 작고 효율적이다.
표현식 인덱스와 UNIQUE 인덱스
표현식 인덱스는 열 값 그대로가 아니라 계산 결과에 인덱스를 만든다. 이메일을 대소문자 구분 없이 로그인 ID로 사용한다고 하자.
CREATE UNIQUE INDEX customers_email_lower_uniq
ON customers (lower(email));이 인덱스는 두 가지 역할을 한다. 첫째, 검색 조건에 맞다.
SELECT id, name, email
FROM customers
WHERE lower(email) = lower('[email protected]');둘째, USER@example.com과 user@example.com이 동시에 저장되는 것을 막는다.
INSERT INTO customers (name, email)
VALUES ('테스트 고객', '[email protected]');이미 user@example.com이 있다면 유니크 위반 오류가 발생한다. 애플리케이션에서 중복 검사를 하더라도 최종 방어선은 데이터베이스의 UNIQUE 인덱스가 되어야 한다. 동시에 가입 요청이 들어오는 상황에서는 애플리케이션의 사전 조회만으로 중복을 완전히 막기 어렵다.
부분 유니크 인덱스도 실무에서 유용하다. 소프트 삭제된 상품 코드는 재사용할 수 있지만, 활성 상품끼리는 코드가 중복되면 안 된다고 하자.
CREATE UNIQUE INDEX products_active_code_uniq
ON products (code)
WHERE deleted_at IS NULL;이렇게 하면 deleted_at IS NULL인 상품 사이에서만 code 중복을 막는다. 업무 규칙이 "전체 기간 중 한 번만 사용 가능"인지, "현재 활성 데이터 안에서만 유일"인지에 따라 유니크 인덱스 설계가 달라진다.
GIN, GiST, BRIN은 언제 쓰는가
모든 문제를 B-tree로 해결하지는 않는다. PostgreSQL은 데이터 특성에 맞는 여러 인덱스 방식을 제공한다.
GIN은 하나의 값 안에 여러 구성 요소가 들어 있는 데이터를 찾는 데 적합하다. 배열, jsonb, 전문 검색에서 자주 사용한다.1
CREATE TABLE product_documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id bigint NOT NULL,
attributes jsonb NOT NULL,
search_text tsvector NOT NULL
);
CREATE INDEX product_documents_attributes_gin_idx
ON product_documents USING gin (attributes);
CREATE INDEX product_documents_search_gin_idx
ON product_documents USING gin (search_text);예를 들어 특정 JSON 속성을 포함하는 상품이나 특정 단어가 포함된 문서를 찾을 때 GIN 인덱스가 도움이 된다.
SELECT product_id
FROM product_documents
WHERE attributes @> '{"color": "black"}'::jsonb;
SELECT product_id
FROM product_documents
WHERE search_text @@ plainto_tsquery('무선 이어폰');GiST는 특정 검색 전략을 담는 틀에 가깝다. 지리 데이터, 범위 타입, 가까운 이웃 찾기 같은 검색에서 사용된다.1 PostGIS를 사용하는 위치 기반 서비스에서는 "현재 위치 근처 매장 찾기" 같은 쿼리에 GiST 계열 인덱스가 등장한다.
BRIN은 매우 큰 테이블에서 물리적 저장 순서와 열 값의 순서가 비슷할 때 효과적이다. 예를 들어 로그 테이블이 시간순으로 계속 쌓인다면 created_at 값은 테이블 저장 위치와 어느 정도 함께 증가한다. BRIN은 블록 범위마다 최소·최대 같은 요약 정보를 저장하므로 인덱스가 작다.1
CREATE TABLE access_logs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL,
user_id bigint,
path text NOT NULL,
status_code integer NOT NULL
);
CREATE INDEX access_logs_created_brin_idx
ON access_logs USING brin (created_at);다음처럼 특정 기간 로그를 조회하는 쿼리에서 큰 테이블 전체를 읽지 않도록 도울 수 있다.
SELECT count(*)
FROM access_logs
WHERE created_at >= timestamptz '2026-05-01 00:00:00+09'
AND created_at < timestamptz '2026-05-02 00:00:00+09';BRIN은 모든 상황에서 B-tree보다 빠른 인덱스가 아니다. 값이 무작위로 흩어져 있으면 블록 요약의 효과가 떨어진다. "대용량이고, 입력 순서와 조회 범위가 잘 맞는가"가 판단 기준이다.
인덱스의 비용을 확인하고 정리하기
인덱스는 읽기 성능을 위해 쓰기 비용과 저장 공간을 지불하는 구조다. 다음 SQL로 테이블과 인덱스 크기를 확인할 수 있다.
SELECT
relname AS object_name,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname LIKE 'orders%'
ORDER BY pg_relation_size(oid) DESC;사용 빈도는 pg_stat_user_indexes에서 확인한다.
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;idx_scan = 0인 인덱스는 의심 대상이다. 하지만 바로 삭제하면 안 된다. 누적 통계는 정상 종료 후 재시작에서는 보존되지만, 비정상 종료, 통계 초기화, 복구 작업 같은 상황에서는 초기화될 수 있다.4 또한 월말 정산처럼 특정 시기에만 쓰는 인덱스도 있다. 삭제 전에는 다음을 확인한다.
해당 인덱스를 사용하는 배치, 관리자 기능, 장애 대응 쿼리가 있는가?
같은 역할을 하는 더 좋은 복합 인덱스가 있는가?
최근 통계 수집 기간이 충분한가?
삭제 후 되돌릴 수 있는 생성 SQL을 보관했는가?
삭제는 명확할 때만 한다.
DROP INDEX orders_status_ordered_idx;운영 중 큰 테이블에 인덱스를 만들 때는 잠금도 고려한다. 일반 CREATE INDEX는 테이블 읽기는 허용하지만, 쓰기 작업에는 부담을 줄 수 있다. 서비스 중에는 보통 다음처럼 동시에 만드는 방식을 검토한다.
CREATE INDEX CONCURRENTLY orders_customer_ordered_idx
ON orders (customer_id, ordered_at DESC);CONCURRENTLY는 운영 중 쓰기 차단을 줄이는 대신 더 오래 걸리고, 트랜잭션 블록 안에서 실행할 수 없는 등 제약이 있다. 인덱스 생성도 배포 작업이다. 실행 시간, 디스크 여유, 실패 시 정리 방법을 함께 준비해야 한다.
인덱스 설계 절차
새 인덱스를 만들기 전에 다음 순서로 판단한다.
느린 쿼리를 하나 고른다.
EXPLAIN (ANALYZE, BUFFERS)로 실제 계획을 본다.WHERE의 동등 조건, 범위 조건, 조인 조건을 분리한다.ORDER BY,LIMIT,GROUP BY가 있는지 확인한다.기존 인덱스와 겹치는지 확인한다.
필요한 최소 인덱스를 만든다.
다시 실행 계획과 실행 시간을 비교한다.
쓰기 비용과 저장 공간 증가를 확인한다.
좋은 인덱스는 쿼리의 모양과 업무 규칙을 함께 반영한다. 고객의 최근 주문을 빠르게 찾기 위한 복합 인덱스, 처리 중 주문만 담는 부분 인덱스, 대소문자 없는 이메일 중복을 막는 표현식 유니크 인덱스는 각각 목적이 다르다.
인덱스 설계의 핵심은 많이 만드는 것이 아니라 정확히 만드는 것이다. 실행 계획으로 사용 여부를 확인하고, 실제 업무 쿼리에서 읽기 이득이 쓰기 비용보다 큰지 판단해야 한다.
참고
1PostgreSQL 18 Documentation: Index Types
2PostgreSQL 18 Documentation: Multicolumn Indexes
3PostgreSQL 18 Documentation: Partial Indexes
4PostgreSQL 18 Documentation: The Cumulative Statistics System
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.