PostgreSQL 검색 최적화: 표현식 인덱스와 날짜 파티션 프루닝

1장. 이메일 검색은 빨라졌는데 중복 가입은 그대로 생겼다#

고객 검색 API가 있습니다.

사용자는 이메일을 대소문자와 관계없이 찾을 수 있어야 합니다.

예를 들어 DB에 다음 값이 저장되어 있습니다.

Garam@Example.com

사용자는:

garam@example.com

으로 검색합니다.

개발자는 다음 SQL을 사용했습니다.

SELECT customer_id
FROM customer_search
WHERE lower(email) = 'garam@example.com';

검색은 잘 됩니다.

데이터가 늘면서 느려지자 다음 인덱스를 추가했습니다.

CREATE INDEX customer_email_lower_idx
ON customer_search (lower(email));

검색 속도도 좋아졌습니다.

그런데 며칠 뒤 다음 계정이 새로 가입했습니다.

garam@example.com

기존 계정:

Garam@Example.com

과 사실상 같은 이메일로 취급해야 하는 서비스였지만 가입은 그대로 성공했습니다.

왜일까요?

이 인덱스는:

검색 경로

를 제공했을 뿐:

중복 금지

를 보장하지 않았기 때문입니다.

최적화와 데이터 무결성은 다른 문제입니다.

2장. 표현식 인덱스는 컬럼 원값이 아니라 계산 결과를 인덱싱한다#

일반 인덱스:

CREATE INDEX customer_email_idx
ON customer_search (email);

는 다음 값을 정렬합니다.

Garam@Example.com

daon@other.test

narae@example.com

반면:

CREATE INDEX customer_email_lower_idx
ON customer_search (lower(email));

는 개념적으로:

garam@example.com

daon@other.test

narae@example.com

이라는 표현식 결과를 검색 키로 사용합니다.

즉 두 인덱스는 같은 데이터를 보고 있어도 서로 다른 키를 인덱싱합니다.

3장. 실습용 데이터를 준비하자#

CREATE TABLE customer_search (
    customer_id integer PRIMARY KEY,
    email text NOT NULL
);

데이터를 넣습니다.

INSERT INTO customer_search
VALUES
    (1, 'Garam@Example.com'),
    (2, 'narae@example.com'),
    (3, 'daon@other.test');

조회:

SELECT
    customer_id,
    email
FROM customer_search
WHERE lower(email) = 'garam@example.com';

결과:

customer_id email
1 Garam@Example.com

4장. 일반 이메일 인덱스와 lower 조건은 같은 검색 키가 아니다#

다음 인덱스가 있다고 하겠습니다.

CREATE INDEX customer_email_idx
ON customer_search (email);

그리고 조건은:

WHERE lower(email) = 'garam@example.com'

입니다.

인덱스 키:

email

과 조건 표현식:

lower(email)

이 다릅니다.

따라서 단순한 email B-tree 인덱스가 lower(email) 검색 요구를 그대로 만족한다고 가정하면 안 됩니다.

5장. 조건과 동일한 표현식으로 인덱스를 만들 수 있다#

CREATE INDEX customer_email_lower_idx
ON customer_search (lower(email));

통계를 갱신합니다.

ANALYZE customer_search;

실행 계획:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    customer_id
FROM customer_search
WHERE lower(email) = 'garam@example.com';

이제 PostgreSQL은 표현식 인덱스를 사용할 수 있는 후보 경로를 가집니다.

6장. 그런데 세 행짜리 테이블에서는 Seq Scan이 나와도 이상하지 않다#

테이블에 행이 세 개뿐입니다.

DBMS 입장에서:

테이블 세 행 전체 읽기

가:

인덱스 탐색
+
테이블 접근

보다 저렴할 수 있습니다.

따라서 실행 계획이:

Seq Scan

이어도:

인덱스가 잘못됐다

고 단정하면 안 됩니다.

7장. 인덱스는 존재와 사용을 구분해야 한다#

다음 두 문장은 다릅니다.

이 쿼리에 사용할 수 있는 인덱스가 있다.

그리고:

옵티마이저가 이번 실행에서 그 인덱스를 선택했다.

인덱스를 만들었다고 PostgreSQL이 반드시 사용해야 하는 것은 아닙니다.

선택 여부는:

테이블 크기

통계

선택도

예상 I/O

캐시 상태

비용 모델

등의 영향을 받습니다.

8장. 인덱스 효과는 충분한 데이터 규모에서 확인해야 한다#

예를 들어 고객이:

10명

인 시스템과:

1,000만 명

인 시스템에서 같은 SQL의 최적 계획이 같다고 보장할 수 없습니다.

작은 샘플은:

쿼리 의미 검증

에 유용합니다.

실제 성능 판단은:

운영과 비슷한 데이터 규모와 분포

에서 해야 합니다.

9장. 표현식 인덱스에도 쓰기 비용이 있다#

고객 이메일을 INSERT하거나 UPDATE할 때 PostgreSQL은:

email 원값

만 저장하는 것이 아니라:

lower(email)

의 인덱스 키도 갱신해야 합니다.

따라서 표현식 인덱스 역시:

저장 공간

INSERT 비용

UPDATE 비용

VACUUM·유지 비용

이 발생합니다.

읽기만 빨라지는 공짜 기능이 아닙니다.

10장. 모든 함수 조건에 표현식 인덱스를 만드는 것은 좋은 전략이 아니다#

다음 조건들이 있다고 하겠습니다.

lower(email)

upper(name)

date_trunc('month', created_at)

substring(phone, 1, 3)

조건마다 무조건 표현식 인덱스를 만든다면 인덱스가 급격히 늘어날 수 있습니다.

먼저 확인해야 합니다.

검색 빈도가 높은가?

선택도가 충분한가?

기존 인덱스로 해결할 수 없는가?

쓰기 비용을 감당할 수 있는가?

실제 쿼리 패턴을 기준으로 결정해야 합니다.

11장. 검색용 표현식 인덱스와 유일성 제약은 다르다#

현재 인덱스:

CREATE INDEX customer_email_lower_idx
ON customer_search (lower(email));

는 같은 키를 여러 개 허용합니다.

따라서 다음 두 값이 함께 존재할 수 있습니다.

Garam@Example.com

garam@example.com

서비스 정책이:

대소문자를 무시하면 동일 이메일 계정이다.

라면 별도의 유일성 제약이 필요합니다.

12장. 대소문자 무시 중복을 먼저 확인하자#

SELECT
    lower(email) AS normalized_email,
    COUNT(*) AS duplicate_count
FROM customer_search
GROUP BY lower(email)
HAVING COUNT(*) > 1;

결과가 0행이면 현재 중복은 없습니다.

이 검사를 거친 뒤 유일 인덱스를 만들 수 있습니다.

13장. 표현식 유일 인덱스로 정책을 강제할 수 있다#

CREATE UNIQUE INDEX customer_email_lower_unique_idx
ON customer_search (lower(email));

이제 기존에:

Garam@Example.com

이 존재하는 상태에서:

INSERT INTO customer_search
VALUES (
    4,
    'garam@example.com'
);

을 시도하면 같은 소문자 키가 충돌합니다.

14장. 하지만 lower 하나가 이메일 정규화 전체를 해결한다고 생각하면 안 된다#

이메일 식별 정책에는 더 많은 문제가 있을 수 있습니다.

예:

앞뒤 공백

국제화 도메인

유니코드

서비스별 로컬 파트 규칙

메일 공급자의 별칭 처리

따라서:

lower(email)

이 서비스 전체의 이메일 동일성 정의라고 자동으로 단정해서는 안 됩니다.

서비스 정책을 먼저 정의해야 합니다.

15장. 검색 최적화와 식별 규칙은 다른 설계 문제다#

정리하면:

검색 요구#

대소문자 구분 없이 이메일 검색

해결 후보:

lower(email) 표현식 인덱스

무결성 요구#

대소문자만 다른 이메일 중복 금지

해결 후보:

UNIQUE 표현식 인덱스

같은 표현식을 사용하더라도 목적은 다릅니다.

16장. lower 인덱스가 다른 검색까지 자동으로 빠르게 하지는 않는다#

다음 검색:

WHERE email LIKE '%@example.com'

은 이메일 도메인 접미사를 찾는 문제입니다.

lower(email)의 등가 비교:

lower(email) = 'garam@example.com'

과 검색 구조가 다릅니다.

따라서:

lower(email) 인덱스가 있다
→ 모든 이메일 검색이 빨라진다

라고 생각하면 안 됩니다.

17장. 검색 연산자와 인덱스 구조를 함께 봐야 한다#

대표적으로 검색은 다음처럼 다릅니다.

정확히 같은 값

앞부분 일치

뒷부분 일치

부분 문자열

정규식

유사 문자열

각 연산은 같은 인덱스 구조에서 동일하게 효율적인 것은 아닙니다.

최적화는:

컬럼

표현식

연산자

정렬 규칙

을 함께 봐야 합니다.

18장. 이제 날짜 파티션 문제를 살펴보자#

주문 데이터가 매우 커졌습니다.

월별로 테이블을 나누기로 했습니다.

예:

2026년 1월

2026년 2월

2026년 3월
...

이때 기대하는 효과 중 하나는:

1월 주문 조회
→ 1월 파티션만 읽기

입니다.

이것이 파티션 프루닝의 핵심 아이디어입니다.

19장. 파티션은 데이터를 물리적 범위로 나눈다#

테이블을 만듭니다.

CREATE TABLE sales_partitioned (
    sale_id integer NOT NULL,
    sale_date date NOT NULL,
    amount integer NOT NULL,

    PRIMARY KEY (
        sale_id,
        sale_date
    )
)
PARTITION BY RANGE (sale_date);

20장. 1월과 2월 파티션을 생성한다#

CREATE TABLE sales_2026_01
PARTITION OF sales_partitioned
FOR VALUES FROM ('2026-01-01')
TO ('2026-02-01');
CREATE TABLE sales_2026_02
PARTITION OF sales_partitioned
FOR VALUES FROM ('2026-02-01')
TO ('2026-03-01');

범위는 개념적으로:

1월
[2026-01-01, 2026-02-01)

2월
[2026-02-01, 2026-03-01)

입니다.

21장. 반개구간으로 파티션 경계를 맞추는 이유#

1월 파티션의 종료:

2026-02-01

은 포함되지 않습니다.

2월 파티션의 시작:

2026-02-01

은 포함됩니다.

따라서 정확히:

2026-02-01

값은 2월 파티션 하나에만 들어갑니다.

기간 이력에서 사용한 [start, end)와 같은 원리입니다.

22장. 경계 데이터를 넣어 보자#

INSERT INTO sales_partitioned
VALUES
    (
        1,
        DATE '2026-01-10',
        100
    ),
    (
        2,
        DATE '2026-01-31',
        200
    ),
    (
        3,
        DATE '2026-02-01',
        300
    );

23장. 1월 매출을 정확한 범위 조건으로 조회한다#

SELECT
    SUM(amount)
FROM sales_partitioned
WHERE sale_date >= DATE '2026-01-01'
  AND sale_date < DATE '2026-02-01';

포함:

1월 10일 100

1월 31일 200

제외:

2월 1일 300

결과:

300

입니다.

24장. 이제 결과뿐 아니라 접근한 파티션을 확인해야 한다#

EXPLAIN
SELECT
    SUM(amount)
FROM sales_partitioned
WHERE sale_date >= DATE '2026-01-01'
  AND sale_date < DATE '2026-02-01';

여기서 확인할 것은 단순히:

결과 300

이 아닙니다.

실행 계획에서:

어떤 파티션이 접근 대상이 되었는가?

를 봅니다.

25장. 정확한 결과와 좋은 접근 계획은 서로 다른 주장이다#

어떤 SQL이:

정확한 1월 데이터

를 반환했다고 해서:

1월 파티션만 읽었다

는 뜻은 아닙니다.

반대로 특정 파티션만 읽었다고 해서 기간 조건이 업무적으로 정확하다는 뜻도 아닙니다.

따라서 최적화 검증은 두 단계입니다.

1. 결과가 정확한가?

2. 필요한 범위만 읽었는가?

26장. EXTRACT MONTH 조건은 연도를 잃는다#

다음 SQL:

WHERE EXTRACT(MONTH FROM sale_date) = 9

의 의미는:

모든 연도의 9월

입니다.

따라서:

2025년 9월

2026년 9월

2027년 9월

이 모두 조건을 만족할 수 있습니다.

27장. 2026년 9월만 필요하면 기간 자체를 표현하는 것이 명확하다#

WHERE sale_date >= DATE '2026-09-01'
  AND sale_date < DATE '2026-10-01'

이 조건은:

2026년 9월

이라는 업무 기간을 직접 표현합니다.

연도와 월의 의미가 동시에 보존됩니다.

28장. BETWEEN은 날짜형과 타임스탬프형에서 다르게 느껴질 수 있다#

DATE 컬럼이라면:

BETWEEN DATE '2026-09-01'
    AND DATE '2026-09-30'

은 9월 30일을 포함합니다.

하지만 TIMESTAMP 컬럼에서:

BETWEEN
    TIMESTAMP '2026-09-01 00:00:00'
    AND
    TIMESTAMP '2026-09-30 00:00:00'

을 사용하면 9월 30일 낮 데이터는 포함되지 않습니다.

29장. 시간까지 있는 컬럼은 다음 기간 시작 미만으로 쓰는 편이 안전하다#

9월 전체:

WHERE ordered_at >= TIMESTAMPTZ '2026-09-01 00:00:00+09'
  AND ordered_at <  TIMESTAMPTZ '2026-10-01 00:00:00+09'

이렇게 하면:

9월 30일 23:59:59

9월 30일 23:59:59.999999

같은 값도 자연스럽게 포함됩니다.

30장. 날짜 끝을 23:59:59로 만드는 방식은 정밀도 문제를 만든다#

다음 조건:

9월 30일 23:59:59까지

를 사용했다고 하겠습니다.

실제 데이터:

23:59:59.500

이면 제외될 수 있습니다.

마이크로초 정밀도에서는:

23:59:59.999999

까지 존재할 수 있습니다.

그래서:

다음 달 시작 미만

이 더 안정적입니다.

31장. 열에 함수를 적용하면 파티션 경계 추론이 달라질 수 있다#

다음 조건:

WHERE to_char(
    sale_date,
    'YYYY-MM'
) = '2026-01';

은 결과 의미상 2026년 1월을 찾을 수 있습니다.

하지만 파티션 키는:

sale_date

자체입니다.

옵티마이저가 이 표현식을 파티션 범위로 얼마나 변환할 수 있는지는 직접 실행 계획에서 확인해야 합니다.

32장. “함수 사용 = 프루닝 실패”라고 외우면 안 된다#

다음 표현 역시 지나치게 단순합니다.

파티션 키에 함수 사용
→ 프루닝 불가능

제품 버전·표현식·상수 단순화·실행 시점 조건에 따라 달라질 수 있습니다.

따라서 실제로 확인해야 합니다.

어떤 파티션이 계획에 남았는가?

실행 중 몇 개가 제거됐는가?

33장. 실행 계획에서 Append 계열을 확인할 수 있다#

여러 파티션이 후보일 경우 PostgreSQL 실행 계획에서 여러 하위 스캔이 나타날 수 있습니다.

예를 들어 개념적으로:

Append
 ├─ sales_2026_01
 ├─ sales_2026_02
 └─ sales_2026_03

처럼 보일 수 있습니다.

조건을 좁혔을 때 불필요한 하위 파티션이 사라지는지 확인합니다.

34장. 파티션 하나만 읽어도 그 안에서 전체 스캔할 수 있다#

9월 파티션에:

1억 행

이 있다고 하겠습니다.

쿼리:

고객 7의 주문 10건

을 찾습니다.

파티션 프루닝으로 9월 하나만 남겨도 그 안의 1억 행을 모두 읽는다면 여전히 느릴 수 있습니다.

35장. 파티션 프루닝과 인덱스는 서로 다른 문제를 해결한다#

파티션 프루닝:

어떤 물리 분할을 읽을 것인가?

인덱스:

선택된 분할 안에서
어떤 행을 효율적으로 찾을 것인가?

입니다.

둘을 함께 사용할 수 있습니다.

36장. 주문 조회를 예로 보면#

조건:

WHERE ordered_at >= ...
  AND ordered_at < ...
  AND customer_id = 7

이라고 하겠습니다.

파티션 프루닝:

9월 관련 파티션만 선택

인덱스:

그 파티션 안에서
customer_id = 7
행 탐색

역할을 할 수 있습니다.

37장. 파티션 개수만 줄었다고 최적화가 완료된 것은 아니다#

개선 전:

12개 파티션 접근

개선 후:

1개 파티션 접근

으로 줄었습니다.

하지만 1개 파티션 안에서:

5천만 행

을 읽었다면 여전히 느릴 수 있습니다.

추가로 확인해야 합니다.

실제 블록 수

필터 제거 행

인덱스 사용

반환 행

38장. 한국 기준 월과 UTC 월은 경계가 다르다#

업무 기준:

Asia/Seoul

DB 저장:

UTC 시점

이라고 하겠습니다.

한국 기준:

2026-09-01 00:00 KST

는 UTC로:

2026-08-31 15:00 UTC

입니다.

한국 기준 9월의 끝:

2026-10-01 00:00 KST

는 UTC로:

2026-09-30 15:00 UTC

입니다.

39장. 따라서 한국 9월은 UTC 두 달에 걸친다#

한국 기준 9월 기간:

[2026-09-01 00:00 KST,
 2026-10-01 00:00 KST)

UTC 기준:

[2026-08-31 15:00 UTC,
 2026-09-30 15:00 UTC)

입니다.

UTC 월 단위로 파티션을 나눴다면 이 조회는:

UTC 8월 파티션

UTC 9월 파티션

두 개를 읽는 것이 정상일 수 있습니다.

40장. 두 파티션을 읽었다고 프루닝 실패라고 말하면 안 된다#

업무 기간 자체가:

UTC 8월 말

+

UTC 9월

에 걸쳐 있습니다.

따라서:

8월·9월 파티션 접근

이 올바른 프루닝 결과입니다.

프루닝 성공 여부를 판단하기 전에 업무 기간을 물리 파티션 경계로 변환해야 합니다.

41장. 한국 월 경계를 실제 SQL로 검산해 보자#

WITH month_boundary(
    order_id,
    ordered_at
) AS (
    VALUES
        (
            1,
            TIMESTAMPTZ
            '2026-08-31 14:59:59+00'
        ),
        (
            2,
            TIMESTAMPTZ
            '2026-08-31 15:00:00+00'
        ),
        (
            3,
            TIMESTAMPTZ
            '2026-09-30 14:59:59+00'
        ),
        (
            4,
            TIMESTAMPTZ
            '2026-09-30 15:00:00+00'
        )
)
SELECT
    order_id,
    ordered_at
        AT TIME ZONE 'Asia/Seoul'
        AS korean_time
FROM month_boundary
WHERE ordered_at
    >= TIMESTAMPTZ
       '2026-09-01 00:00:00+09'
  AND ordered_at
    < TIMESTAMPTZ
      '2026-10-01 00:00:00+09'
ORDER BY order_id;

42장. 경계 결과를 확인하면#

order_id 한국 시간 포함
1 8월 31일 23:59:59 제외
2 9월 1일 00:00:00 포함
3 9월 30일 23:59:59 포함
4 10월 1일 00:00:00 제외

이것이 우리가 원하는 9월 경계입니다.

43장. TIMESTAMPTZ는 원래 입력한 시간대 이름을 그대로 저장하는 타입이 아니다#

예를 들어:

2026-09-01 00:00+09

과:

2026-08-31 15:00+00

은 같은 순간입니다.

timestamptz는 이 시점을 다룹니다.

표시할 때는 세션 시간대에 따라 다른 문자열로 보일 수 있습니다.

44장. 지역별 업무 달력이 필요하면 지역 정보도 별도로 관리할 수 있다#

다국가 서비스에서:

한국 매출 월

미국 매출 월

유럽 매출 월

을 계산한다면 단순 UTC 시점만으로 업무 월을 정의하기 어려울 수 있습니다.

다음도 필요할 수 있습니다.

업무 지역

시간대

회계 달력

마감 규칙

시간대는 단순 표시 문제가 아닙니다.

45장. 파티션 키 선택에는 대표 조회 패턴이 중요하다#

주문 테이블을 날짜로 파티션했습니다.

하지만 주요 쿼리가:

order_id 하나로 조회

뿐이라면 날짜 파티션의 이점이 제한적일 수 있습니다.

반대로 대부분의 쿼리가:

최근 한 달

최근 90일

월별 집계

라면 날짜 파티션이 잘 맞을 수 있습니다.

46장. 파티션 키는 관리 목적에도 영향을 준다#

날짜 파티션은 다음 작업을 쉽게 만들 수 있습니다.

오래된 월 제거

월별 보관

월별 아카이빙

기간별 적재

즉 파티셔닝은 성능 기능만이 아닙니다.

관리 단위도 바꿉니다.

47장. 새 파티션을 미리 만들지 않으면 INSERT가 실패할 수 있다#

1월·2월 파티션만 있습니다.

3월 1일 데이터:

2026-03-01

이 들어왔습니다.

이를 받을 파티션이나 DEFAULT 파티션이 없다면 삽입이 실패할 수 있습니다.

따라서 월별 파티션 시스템에서는:

다음 달 파티션 생성

을 운영 자동화해야 할 수 있습니다.

48장. DEFAULT 파티션은 안전망이면서 품질 문제를 숨길 수도 있다#

DEFAULT 파티션을 두면 예상하지 못한 날짜가 들어와도 삽입될 수 있습니다.

장점:

서비스 INSERT 실패 방지

단점:

원래 월 파티션 생성 누락

잘못된 날짜

미래 데이터

가 조용히 DEFAULT에 쌓일 수 있습니다.

따라서 별도 모니터링이 필요합니다.

49장. 파티션을 삭제하는 것은 대량 DELETE와 다른 운영 특성이 있다#

오래된 한 달을 제거한다고 하겠습니다.

DELETE 수억 행

보다:

오래된 파티션 분리·삭제

가 운영상 유리할 수 있습니다.

하지만:

외래키

백업

복제

감사

보존 규칙

영향을 먼저 확인해야 합니다.

50장. PostgreSQL 분할 테이블의 UNIQUE 제약에는 주의가 필요하다#

예제 기본키:

PRIMARY KEY (
    sale_id,
    sale_date
)

에는 파티션 키인:

sale_date

가 포함되어 있습니다.

분할 테이블 전체에 대한 고유 제약은 파티션 구조와 관련된 제한이 있습니다.

따라서:

sale_id 하나만 전역적으로 고유

라는 요구를 동일한 방식으로 바로 보장한다고 생각하면 안 됩니다.

51장. sale_id만의 전역 유일성이 필요하면 별도 설계를 검토해야 한다#

예를 들어:

sale_id = 100

이 1월과 2월에 각각 들어오면 안 된다는 업무 규칙이 있다고 하겠습니다.

파티션별 로컬 제약만으로는 전역 고유성 설계가 복잡해질 수 있습니다.

가능한 방법은 시스템 구조에 따라 다릅니다.

ID 생성 규칙

별도 식별자 테이블

파티션 키 포함 키 설계

애플리케이션 생성 ID

등을 검토할 수 있습니다.

52장. 표현식 인덱스와 파티션 프루닝을 혼동하지 말자#

표현식 인덱스:

lower(email)

은:

행을 찾는 검색 키

와 관련 있습니다.

파티션 프루닝:

sale_date

은:

읽지 않아도 되는 물리 분할 제외

와 관련 있습니다.

둘은 완전히 다른 단계의 최적화입니다.

53장. 하나의 SQL에 두 최적화가 동시에 적용될 수도 있다#

예를 들어 고객 이벤트 테이블이 월별 파티션이고:

event_month

으로 프루닝됩니다.

각 파티션에는:

lower(email)

인덱스가 있다고 하겠습니다.

쿼리:

9월

+

garam@example.com

이라면:

1. 9월 파티션 선택

2. 해당 파티션 안에서 이메일 인덱스 탐색

이 가능할 수 있습니다.

54장. 최적화 전후에는 같은 결과인지 먼저 확인하자#

최적화 전:

결과
customer_id 1

최적화 후:

결과
customer_id 1

이어야 합니다.

기간 조회도:

1월 합계
300

이라는 결과를 유지해야 합니다.

성능 개선 과정에서 결과가 바뀌었다면 먼저 정확성 문제입니다.

55장. 그다음 접근 범위를 비교한다#

비교할 수 있는 항목:

접근 파티션 수

읽은 블록 수

인덱스 사용

필터 제거 행

실제 실행 시간

반환 행 수

입니다.

단순히:

Index Scan이라는 글자가 보였다.

만으로 성공을 판단하지 않습니다.

56장. 인덱스를 사용했는데 더 느릴 수도 있다#

조건이 전체 테이블의:

80%

를 반환한다고 하겠습니다.

인덱스를 따라 많은 위치를 방문하는 것보다 순차적으로 테이블을 읽는 편이 더 저렴할 수 있습니다.

그래서 옵티마이저가 Seq Scan을 선택할 수 있습니다.

57장. 선택도가 낮은 조건은 인덱스 효과가 작을 수 있다#

예:

status = 'ACTIVE'

이고 전체 고객의 95%가 ACTIVE라고 하겠습니다.

이 조건 하나만으로는 인덱스를 사용해도 대부분의 행을 읽어야 합니다.

인덱스의 존재보다 조건이 얼마나 후보를 줄이는가가 중요합니다.

58장. 이메일 정확 일치는 일반적으로 선택도가 높은 편이다#

고객 수:

1,000만 명

이메일 하나:

1명

을 찾는다면 후보가 매우 적습니다.

이런 검색은 적절한 인덱스의 이점을 얻기 쉬운 유형입니다.

하지만 실제 데이터 분포와 유일성 정책을 확인해야 합니다.

59장. 복합 검색에서는 컬럼 순서도 중요하다#

예:

가입일

이메일 도메인

상태

를 함께 자주 검색한다고 하겠습니다.

복합 인덱스를 만들 때는:

조건 종류

선택도

정렬

범위 조건

을 함께 봐야 합니다.

모든 조건을 무조건 한 인덱스에 넣는 것이 정답은 아닙니다.

60장. 실행 계획을 볼 때 estimated와 actual을 함께 보자#

EXPLAIN ANALYZE 결과에서:

estimated rows
10

actual rows
50,000

처럼 크게 다르면 옵티마이저의 비용 판단도 빗나갈 수 있습니다.

최적화에서 인덱스만 보는 것이 아니라 통계 정확성도 중요합니다.

61장. BUFFERS를 보면 실제 접근량을 이해하기 쉽다#

EXPLAIN (
    ANALYZE,
    BUFFERS
)
SELECT ...

를 사용하면:

shared hit

shared read

등의 정보를 통해 버퍼 접근량을 볼 수 있습니다.

성능 비교에서 실행 시간 하나보다 작업량 변화까지 보는 것이 좋습니다.

62장. 실행 시간은 캐시 상태 때문에 크게 달라질 수 있다#

첫 실행:

300ms

두 번째:

20ms

이라고 하겠습니다.

두 번째 실행은 데이터가 이미 캐시에 있기 때문일 수 있습니다.

따라서 인덱스 전후를 한 번씩 실행한 시간만 비교하면 왜곡될 수 있습니다.

63장. 같은 조건에서 여러 번 측정해야 한다#

가능하면:

같은 데이터

같은 SQL

비슷한 캐시 조건

비슷한 동시 부하

에서 비교합니다.

운영 환경에서는 평균뿐 아니라:

p95

p99

도 함께 보는 것이 좋습니다.

64장. 쓰기 부하도 같이 측정해야 한다#

표현식 인덱스를 세 개 추가했습니다.

조회:

100ms
→
10ms

로 빨라졌습니다.

하지만 INSERT:

5ms
→
15ms

로 느려졌다고 하겠습니다.

읽기만 보면 성공입니다.

전체 워크로드에서는 판단이 달라질 수 있습니다.

65장. 파티션 수가 지나치게 많아져도 관리 비용이 생긴다#

일별 파티션:

10년
≈
3,650개

라고 하겠습니다.

더 세분화하면 파티션 수가 훨씬 늘 수 있습니다.

파티션은 무료 추상화가 아닙니다.

DDL 관리

통계

계획 시간

인덱스 관리

백업

등의 부담이 생길 수 있습니다.

66장. 파티션 크기와 조회 범위의 균형을 봐야 한다#

주요 조회가:

최근 30일

이라면 월 파티션이 자연스러울 수 있습니다.

주요 조회가:

최근 5분

이고 데이터 발생량이 매우 크다면 다른 구성이 필요할 수 있습니다.

반대로 데이터가 적은데 시간 단위 파티션을 만들면 관리 복잡도만 증가할 수 있습니다.

67장. 파티션은 샤딩과 다르다#

파티셔닝:

하나의 논리 테이블을
DB 내부에서 여러 파티션으로 분리

샤딩:

데이터를 여러 DB 노드로 분산

하는 구조로 이해할 수 있습니다.

둘 다 데이터를 나누지만 운영 경계와 분산 수준이 다릅니다.

68장. 파티션 프루닝이 곧 네트워크 분산 감소를 의미하지는 않는다#

단일 PostgreSQL 인스턴스의 파티션 프루닝은:

불필요한 테이블 파티션을 읽지 않는 것

입니다.

분산 DB의 샤드 라우팅과는 다른 문제입니다.

개념을 섞으면 실제 성능 병목 위치를 잘못 판단할 수 있습니다.

69장. 조건식은 사람이 보기 좋다고 항상 옵티마이저에도 좋은 것은 아니다#

사람에게는:

to_char(
    ordered_at,
    'YYYY-MM'
) = '2026-09'

가 읽기 쉽습니다.

하지만 파티션 경계와 인덱스 관점에서는:

ordered_at >= ...
AND ordered_at < ...

가 더 직접적일 수 있습니다.

SQL의 가독성과 접근 경로를 함께 고려해야 합니다.

70장. 그렇다고 모든 함수 조건을 피해야 하는 것은 아니다#

함수 조건이 실제 업무 의미를 가장 정확하게 표현할 수도 있습니다.

또 표현식 인덱스와 함께 효율적으로 사용할 수 있습니다.

중요한 것은:

함수가 있다
→ 나쁘다

가 아니라:

현재 조건이 어떤 인덱스와 파티션 구조에 대응하는가?

입니다.

71장. WHERE 표현식과 인덱스 표현식이 맞는지 확인하자#

인덱스:

CREATE INDEX ...
ON customer_search (
    lower(email)
);

쿼리:

WHERE upper(email) = ...

이라면 같은 표현식이 아닙니다.

대소문자 무시라는 목적은 비슷해 보여도 인덱스 키는 다릅니다.

72장. 데이터 타입 변환도 계획에 영향을 줄 수 있다#

파티션 키:

date

인데 조건에서 여러 암시적 변환이 발생한다고 하겠습니다.

결과 의미가 맞더라도 옵티마이저가 경계를 다루는 방식에 영향을 줄 수 있습니다.

그래서 조건 리터럴도:

DATE '2026-01-01'

처럼 타입을 명확하게 쓰는 습관이 도움이 됩니다.

73장. TIMESTAMP와 TIMESTAMPTZ를 혼동하면 경계 자체가 달라질 수 있다#

timestamp without time zone:

벽시계 시간 값

timestamptz:

특정 순간

을 다룹니다.

업무 의미가:

한국 현지 9월

인지:

UTC 9월

인지에 따라 적절한 비교 방식이 달라집니다.

74장. 최적화 전에 업무 기간부터 확정해야 한다#

예:

2026년 9월 주문을 조회한다.

라고 했을 때 반드시 물어야 합니다.

한국 시간 기준인가?

UTC 기준인가?

주문 생성 시간인가?

결제 시간인가?

이 조건이 잘못되면 아무리 빠른 SQL도 잘못된 결과를 냅니다.

75장. 인덱스와 파티션은 정확성을 보장하는 기능이 아니다#

인덱스:

찾는 경로

파티션:

저장·접근 범위

입니다.

다음 업무 규칙은 별도로 필요합니다.

이메일 중복 금지

월 정의

주문 시각 의미

데이터 유일성

성능 기능과 무결성 규칙을 분리해야 합니다.

76장. 작은 테이블에서 강제로 인덱스 계획을 만들 필요는 없다#

교육 예제에서:

Seq Scan

이 나왔다고 해서 설정을 억지로 바꿔:

Index Scan

을 보이게 만드는 것이 목적은 아닙니다.

중요한 것은:

어떤 조건에서 인덱스 후보가 되는가?

왜 옵티마이저가 현재 계획을 선택했는가?

를 이해하는 것입니다.

77장. 강제 계획은 원인 분석을 숨길 수 있다#

힌트나 설정으로 특정 경로를 강제하면:

통계 오류

데이터 편향

잘못된 조건

과도한 인덱스

같은 원인을 가릴 수 있습니다.

먼저 옵티마이저가 왜 현재 계획을 선택했는지 조사하는 것이 좋습니다.

78장. 최적화 효과를 숫자로 비교하자#

가상의 운영 데이터에서:

최적화 전#

실행
120ms

읽은 블록
8,000

반환 행
1

표현식 인덱스 적용 후#

실행
4ms

읽은 블록
8

반환 행
1

결과는 같습니다.

작업량은 크게 줄었습니다.

이것이 설명 가능한 성능 개선입니다.

79장. 파티션에서도 같은 방식으로 비교할 수 있다#

조건 개선 전#

접근 파티션
12개

읽은 블록
100,000

기간 범위 조건 적용 후#

접근 파티션
1개

읽은 블록
8,000

반환 결과가 동일하다면 프루닝 효과를 설명할 수 있습니다.

80장. 그러나 접근 파티션이 2개여도 정상일 수 있다#

한국 기준 9월을 UTC 월 파티션에서 조회한다면:

UTC 8월

UTC 9월

두 개가 필요합니다.

따라서 비교 목표는:

무조건 파티션 1개

가 아니라:

업무 기간과 겹치지 않는 파티션을 얼마나 제거했는가?

입니다.

81장. 인덱스를 삭제할 때도 사용 현황을 봐야 한다#

비슷한 인덱스:

email

lower(email)

email, status

lower(email), status

가 계속 추가되면 쓰기 비용과 저장 공간이 커집니다.

정기적으로 실제 쿼리 패턴과 사용량을 확인해 불필요한 인덱스를 정리할 필요가 있습니다.

82장. 유일 인덱스가 있다면 같은 표현의 일반 인덱스가 필요한지도 확인하자#

다음 두 인덱스:

CREATE INDEX ...
ON customer_search (
    lower(email)
);
CREATE UNIQUE INDEX ...
ON customer_search (
    lower(email)
);

가 동시에 있다면 동일한 표현식 검색에 두 구조가 중복 역할을 할 수 있습니다.

유일 인덱스가 검색에도 사용할 수 있으므로 둘 다 유지해야 하는 이유가 있는지 확인합니다.

83장. 기존 중복 데이터가 있으면 유일 인덱스 생성 자체가 실패한다#

예:

Garam@Example.com

garam@example.com

이 이미 존재합니다.

그 상태에서:

CREATE UNIQUE INDEX ...
ON customer_search (
    lower(email)
);

을 실행하면 중복 키 때문에 실패할 수 있습니다.

먼저 중복 처리 정책이 필요합니다.

84장. 중복 데이터를 무조건 삭제하면 안 된다#

두 계정에:

주문

포인트

상담 이력

로그인 기록

이 각각 존재할 수 있습니다.

따라서:

한 행 삭제

가 아니라:

동일 계정인지 확인

병합 정책 결정

관련 데이터 이전

로그인 정책 정리

가 필요할 수 있습니다.

인덱스 생성 전 데이터 거버넌스 문제입니다.

85장. 표현식 인덱스도 비즈니스 규칙과 함께 설계해야 한다#

단순한 목표:

lower(email) 검색 빠르게

만 보면 인덱스 하나면 충분합니다.

하지만 서비스 요구:

대소문자 무시 검색

대소문자 무시 중복 금지

가입 이력 보존

계정 병합

까지 보면 데이터 모델과 업무 정책이 함께 필요합니다.

86장. 파티션 변경도 운영 절차가 필요하다#

1월이 끝났습니다.

1월 파티션을:

읽기 전용

압축

아카이브

삭제

중 어떤 상태로 바꿀지 정할 수 있습니다.

파티션은 단순 성능 기능이 아니라 데이터 수명주기 관리에도 연결됩니다.

87장. 파티션 삭제 전에 백업 보존 정책을 확인하자#

오래된 파티션을 제거했습니다.

그런데 규정상:

7년간 거래 보존

이 필요했다면 문제가 됩니다.

반대로 백업에만 남겨 놓고 조회가 필요할 때 복구할 수 있는지도 확인해야 합니다.

88장. 프루닝 테스트에는 경계 행을 반드시 넣자#

예:

1월 31일

2월 1일

두 행을 넣습니다.

1월 조회에서:

1월 31일
→ 포함

2월 1일
→ 제외

되는지 확인합니다.

파티션 성능만 확인하고 경계 데이터 정확성을 시험하지 않으면 한 달 첫날이나 마지막 날 데이터가 잘못될 수 있습니다.

89장. 시간대 테스트도 네 개의 경계값으로 만들 수 있다#

한국 기준 9월:

8월 31일 23:59:59 KST
→ 제외

9월 1일 00:00 KST
→ 포함

9월 30일 23:59:59 KST
→ 포함

10월 1일 00:00 KST
→ 제외

이 네 값을 자동 테스트로 만들면 기간 경계 오류를 쉽게 발견할 수 있습니다.

90장. 표현식 인덱스 테스트 체크리스트#

  1. 실제 검색 조건은 무엇인가?
  2. 인덱스 표현식과 WHERE 표현식이 일치하는가?
  3. 일반 인덱스와 표현식 인덱스의 키 차이를 이해했는가?
  4. 작은 표에서 Seq Scan이 나와도 정상일 수 있음을 고려했는가?
  5. 실제 규모에서 EXPLAIN ANALYZE를 확인했는가?
  6. 반환 행 수와 읽은 블록 수를 비교했는가?
  7. INSERT·UPDATE 비용을 측정했는가?
  8. 중복 방지까지 필요하다면 UNIQUE가 필요한가?
  9. 이미 중복 데이터가 있는가?
  10. 동일 표현식 인덱스가 중복 생성되어 있지 않은가?
  11. 문자열 정규화 규칙이 서비스 정책과 일치하는가?
  12. 검색 성능과 식별 정책을 구분했는가?

91장. 파티션 프루닝 테스트 체크리스트#

  1. 파티션 키는 무엇인가?
  2. 주요 조회 기간과 파티션 기준이 맞는가?
  3. 시작 포함·종료 제외 범위를 사용하는가?
  4. DATE와 TIMESTAMP의 경계를 구분하는가?
  5. 업무 시간대가 무엇인가?
  6. 저장 시간대와 업무 시간대가 다른가?
  7. 한국 월이 UTC 여러 파티션에 걸치는지 확인했는가?
  8. 조건에 함수가 있을 때 실제 실행 계획을 확인했는가?
  9. 접근 파티션 수를 확인했는가?
  10. 프루닝 후 파티션 내부 읽기량도 확인했는가?
  11. 다음 기간 파티션을 자동 생성하는가?
  12. DEFAULT 파티션을 모니터링하는가?
  13. 오래된 파티션 삭제가 보존 정책과 맞는가?
  14. 고유 제약과 파티션 키 관계를 확인했는가?
  15. 경계 시각을 자동 테스트했는가?

92장. 가장 흔한 잘못된 판단#

첫 번째:

함수 조건
→ 인덱스 사용 불가

가 항상 맞는 것은 아닙니다.

표현식 인덱스를 사용할 수 있습니다.

두 번째:

인덱스 생성
→ 반드시 Index Scan

도 틀릴 수 있습니다.

옵티마이저는 더 저렴한 경로를 선택합니다.

세 번째:

파티션 적용
→ 항상 빠름

도 아닙니다.

파티션 내부에서 대량 스캔할 수 있습니다.

네 번째:

두 파티션 접근
→ 프루닝 실패

도 아닙니다.

업무 기간 자체가 두 파티션에 걸칠 수 있습니다.

93장. 최적화 순서를 정리하면#

flowchart TD
    A["업무 검색 조건 정의"] --> B["정확한 결과 검산"]
    B --> C["현재 실행 계획 확인"]
    C --> D["표현식·파티션 경계 확인"]
    D --> E["인덱스 또는 조건 개선"]
    E --> F["같은 결과인지 재검증"]
    F --> G["접근 파티션·블록·행 비교"]
    G --> H["쓰기 비용·운영 비용 확인"]

성능부터 고치는 것이 아니라 결과 의미를 먼저 고정합니다.

94장. 핵심 정리#

PostgreSQL 검색 최적화에서 중요한 것은:

인덱스를 만들었다.

또는:

파티션을 나눴다.

는 사실 자체가 아닙니다.

먼저 검색 조건의 의미를 정확하게 정의해야 합니다.

이메일 대소문자 무시 검색에서는:

WHERE lower(email) = ...

이라는 조건을 사용할 수 있습니다.

일반 email 인덱스와 lower(email) 표현식 인덱스는 서로 다른 검색 키를 가집니다.

따라서 조건과 인덱스 표현식을 맞출 수 있습니다.

하지만:

표현식 인덱스
=
중복 금지

는 아닙니다.

같은 소문자 이메일을 하나의 계정으로 취급해야 한다면:

UNIQUE 표현식 인덱스

같은 별도의 무결성 규칙이 필요합니다.

또 인덱스를 만들었다고 항상 PostgreSQL이 사용하는 것도 아닙니다.

작은 테이블에서는:

Seq Scan

이 더 저렴할 수 있습니다.

그래서 계획 이름 하나보다:

반환 행

읽은 블록

실제 시간

쓰기 비용

을 함께 봐야 합니다.

날짜 파티션에서도 같은 원칙이 적용됩니다.

2026년 9월을 찾는데:

EXTRACT(MONTH FROM sale_date) = 9

라고 하면 다른 연도의 9월까지 섞일 수 있습니다.

보다 명확한 표현은:

sale_date >= DATE '2026-09-01'
AND sale_date < DATE '2026-10-01'

입니다.

시간까지 저장한다면:

시작 이상

다음 기간 시작 미만

의 반개구간이 경계를 다루기 쉽습니다.

그리고 업무 시간대와 저장 시간대가 다르면 파티션 접근 수도 달라질 수 있습니다.

한국 기준 2026년 9월은 UTC 기준으로:

2026-08-31 15:00
~
2026-09-30 15:00

입니다.

UTC 월별 파티션에서는 8월과 9월 파티션을 읽는 것이 오히려 정상입니다.

따라서 파티션 프루닝의 목표는:

무조건 하나의 파티션만 읽는 것

이 아닙니다.

업무 기간과 무관한 파티션을 읽지 않는 것

입니다.

마지막으로 최적화는 항상 두 단계로 검증해야 합니다.

1. 결과가 이전과 동일하게 정확한가?

2. 실제 읽는 범위와 작업량이 줄었는가?

이메일 검색 결과가 같아도 불필요한 페이지를 여전히 많이 읽을 수 있고, 파티션이 줄어도 그 안에서 수천만 행을 스캔할 수 있습니다.

좋은 데이터베이스 최적화는 Index Scan이라는 글자를 얻는 작업이 아닙니다.

업무 의미를 그대로 유지하면서 필요한 파티션과 필요한 행만 읽도록 접근 범위를 줄이고, 그 개선을 실행 계획과 실제 작업량으로 설명할 수 있게 만드는 작업입니다.

이 페이지의 목차