PostgreSQL 정렬과 LIKE 인덱스 사용

Last Updated on 6월 9, 2026 by Jade(정현호)

안녕하세요
이번 포스팅은 PostgreSQL에서 정렬과 LIKE 검색시 인덱스 사용에 관한 내용을 확인해보려고 합니다.

오버뷰(Overview)

PostgreSQL은 다양한 로케일과 콜레이션을 지원하여 전 세계의 다양한 언어와 문화권에서 사용할 수 있도록 설계되었습니다. PostgreSQL에서 문자열 정렬과 검색 성능을 제대로 이해하려면, 먼저 세 가지 축을 구분해야 합니다.

• Encoding(캐릭터셋): 어떤 문자 집합(Character Set) 형태로 데이터를 저장할 것인가
• Locale: 문자 비교/정렬/대소문자/날짜 및 숫자 표현 규칙
• Collation: 로케일의 하위 개념으로, 오직 문자열의 정렬(Sort)과 비교(Compare) 규칙에 집중한 객체

로케일(Locale)과 콜레이션(Collation)은 밀접하게 관련되어 있지만, 엄밀히 말하면 로케일(Locale)이 더 넓은 개념입니다. 
특히 정렬과 LIKE 인덱스 사용 여부는 Encoding보다 Collation(LC_COLLATE) 과 밀접한 관계가 있습니다.

인코딩(Encoding)

PostgreSQL에서 "캐릭터셋"은 Server Encoding으로 관리하며, PostgreSQL은 DB 단위로 문자 인코딩을 설정할 수 있습니다.

한글 서비스를 포함한 현대 환경에서는 Encoding은 사실상 UTF8이 표준입니다.

PostgreSQL에서는 다국어 처리를 위한 다양한 문자 인코딩을 지원하며 다양한 클라이언트와 서버 간의 자동 인코딩 변환 기능을 지원합니다. 지원하는 목록은 다음 링크를 참고하시면 됩니다.

UTF8 장점으로 다음과 같습니다.

• 한글/영문/이모지 등 유니코드 전체 표현 가능
• 다국어 확장성 우수
• 애플리케이션과 드라이버 호환성 가장 좋음

로케일(Locale)

로케일은 데이터베이스에서 문자열 정렬, 대소문자 구분, 날짜/시간 포맷 등을 결정하는 설정입니다.

PostgreSQL에서는 데이터베이스 생성 시 로케일을 지정할 수 있으며, 이는 해당 데이터베이스 내의 모든 테이블과 컬럼에 기본설정으로 적용됩니다.

Locale은 "문자열 정렬만"이 아니라 여러 규칙의 묶음이며, Locale은 LC_* 하위 설정들의 집합입니다.

• LC_COLLATE: 문자열 비교/정렬 순서
• LC_CTYPE: 문자 분류, 대소문자 변환 기준
• LC_MESSAGES: 메시지 언어
• LC_TIME: 날짜/시간 표시 형식
• LC_NUMERIC: 숫자 포맷
• LC_MONETARY: 통화 포맷

즉, "로케일을 C로 쓴다"는 말은 보통 LC_COLLATE/LC_CTYPE 을 C로 설정함을 의미합니다.

콜레이션 (Collation) - LC_COLLATE

문자열의 비교 및 정렬 규칙, 로케일(Locale)의 하위 개념 (로케일의 일부 설정을 따름)입니다. 관여되는 데이터는 오직 문자열(텍스트)입니다.

정렬과 비교, 그리고 LIKE '접두어%' 최적화는 LC_COLLATE의 영향을 직접 받습니다.

주의 할점으로 PostgreSQL에서 데이터베이스 레벨의 LC_COLLATE와 LC_CTYPE은 데이터베이스가 생성되는 시점(CREATE DATABASE)에 결정되면 이후 변경할 수 없습니다.
              

정렬

정렬 결과는 데이터의 내용만으로 결정되지 않고, 데이터베이스의 locale과 collation 설정에 영향을 받습니다.

특히 PostgreSQL에서는 LC_COLLATE 값에 따라 문자열 비교 방식이 달라지므로, 같은 데이터라도 정렬 순서가 다르게 보일 수 있습니다. 아래에서는 로케일별 정렬 차이를 간단한 테스트로 확인해보겠습니다.

테스트를 위해 다음과 같이 데이터베이스 4개를 생성해보도록 하겠습니다.

CREATE DATABASE testdb_ko
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'ko_KR.UTF-8'
LC_CTYPE = 'ko_KR.UTF-8';

CREATE DATABASE testdb_c
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C';

CREATE DATABASE testdb_en
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';

CREATE DATABASE testdb_icu_ko
TEMPLATE = template0
LOCALE_PROVIDER icu
ICU_LOCALE "ko-KR"
LOCALE "ko_KR.UTF-8";

생성 후 \list 명령어를 통해 현재 생성된 데이터베이스 정보를 확인해보도록 하겠습니다.

postgres=# \list
                                                       List of databases
     Name      |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
---------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres      | postgres | UTF8     | libc            | C.UTF-8     | C.UTF-8     |        |           |
 template0     | postgres | UTF8     | libc            | C.UTF-8     | C.UTF-8     |        |           | =c/postgres          +
               |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1     | postgres | UTF8     | libc            | C.UTF-8     | C.UTF-8     |        |           | =c/postgres          +
               |          |          |                 |             |             |        |           | postgres=CTc/postgres
 testdb_c      | postgres | UTF8     | libc            | C           | C           |        |           |
 testdb_en     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 testdb_icu_ko | postgres | UTF8     | icu             | ko_KR.UTF-8 | ko_KR.UTF-8 | ko-KR  |           |
 testdb_ko     | postgres | UTF8     | libc            | ko_KR.UTF-8 | ko_KR.UTF-8 |        |           |

테스트 1

ko_KR.UTF-8 locale 로 설정된 testdb_ko 로 접속해서 테이블을 생성하고 테스트를 진행합니다.

-- 데이터베이스 접속
\c testdb_ko

-- 테이블 생성
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 데이터 입력
INSERT INTO test_table (name) VALUES
-- 영문 대소문자 정렬 테스트용
('apple'),
('Apple'),
('banana'),
('Banana'),
-- 공백 및 특수문자 정렬 테스트용
('가방'),
('가-방'),
('가 방'),
('가방구'),
-- 한글 자모 및 완성형 정렬 테스트용
('ㄱ'),
('ㄴ'),
('가'),
('나');

name 컬럼을 기준으로 정렬을 해서 조회해보도록 하겠습니다.

SELECT * FROM test_table ORDER BY name ASC;
 id |  name
----+--------
  2 | Apple
  4 | Banana
  1 | apple
  3 | banana
  9 | ㄱ
 10 | ㄴ
 11 | 가
  7 | 가 방
  6 | 가-방
  5 | 가방
  8 | 가방구
 12 | 나

테스트 2

C locale 로 설정된 testdb_c 로 접속해서 테이블을 생성하고 테스트를 진행합니다.
테이블 생성 및 데이터 입력을 이전과 동일하게 진행합니다.(내용 생략)

데이터까지 입력 후 name 컬럼을 기준으로 정렬을 해서 조회해보도록 하겠습니다.

SELECT * FROM test_table ORDER BY name ASC;
 id |  name
----+--------
  2 | Apple
  4 | Banana
  1 | apple
  3 | banana
  9 | ㄱ
 10 | ㄴ
 11 | 가
  7 | 가 방
  6 | 가-방
  5 | 가방
  8 | 가방구
 12 | 나

결과를 보면 ko_KR.UTF-8 와 C의 정렬된 내용이 동일하다는 것을 확인할 수 있습니다.

테스트3

en_US.UTF-8 locale 로 설정된 testdb_en 로 접속해서 테이블을 생성하고 테스트를 진행합니다.
테이블 생성 및 데이터 입력을 이전과 동일하게 진행합니다.(내용 생략)

데이터까지 입력 후 name 컬럼을 기준으로 정렬을 해서 조회해보도록 하겠습니다.

SELECT * FROM test_table ORDER BY name ASC;
 id |  name
----+--------
 11 | 가
 12 | 나
  5 | 가방
  8 | 가방구
  7 | 가 방
  6 | 가-방
  1 | apple
  2 | Apple
  3 | banana
  4 | Banana
  9 | ㄱ
 10 | ㄴ

en_US.UTF-8 로케일에서 한글 정렬 결과를 확인할 내용이 있습니다. 확인을 한글을 조금더 입력해서 조회결과를 확인해보도록 하겠습니다.

CREATE TABLE hangul_test (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO hangul_test (name) VALUES
('ㄱ'),('ㄴ'),('가'),('각'),('간'),('바사'),
('가1'),('가 1'),('가-1'),('가_1'),('가나'),('가나다'),
('나'),('파'),('나다라마바'),('나다'),('다'),('하');

SELECT * FROM hangul_test ORDER BY name ASC;
 id  |    name
-----+------------
  86 | 가
  87 | 각
  88 | 간
  96 | 나
 100 | 다
  97 | 파
 101 | 하
  94 | 가나
  99 | 나다
  89 | 바사
  95 | 가나다
  98 | 나다라마바
  91 | 가 1
  92 | 가-1
  93 | 가_1
  90 | 가1
  84 | ㄱ
  85 | ㄴ

위의 조회결과를 보면 조금더 한글의 정렬이 예상했던것과 많이 다른 정렬 조회 결과를 보이는 것을 확인할 수 있습니다.

en_US.UTF-8 환경(또는 데이터베이스)에서 나오는 현상으로 다른 로케일보다 en_US.UTF-8에서 한글 정렬이 잘 안되는 현상입니다.

en_US.UTF-8에서 ORDER BY name ASC 결과가 한글 사용자 관점에서 "뒤죽박죽"처럼 보이는 이유는, PostgreSQL이 문자열을 한글 사전식 규칙이 아니라 en_US 로케일의 정렬 가중치로 비교하기 때문입니다.

즉, 가, 각, 간, 나, 다처럼 단일 음절은 앞쪽에 모이지만, 가나, 가나다 같은 다음 문자열은 같은 접두어라도 로케일 가중치와 길이/문자 조합 규칙에 의해 별도 위치로 배치될 수 있습니다. 또한 가 1, 가-1, 가_1, 가1처럼 공백·하이픈·언더스코어·숫자가 섞인 값은 기호별 정렬 우선순위가 적용되어 기대와 다른 순서가 나타납니다.

그래서 한글에 대해서 정렬을 고려한다면 특히 더 en_US.UTF-8 로케일 대신 다른 로케일 선택을 해야합니다.

            
테스트 4

ko_KR (ICU) locale 로 설정된 testdb_icu_ko 로 접속해서 테이블을 생성하고 테스트를 진행합니다.

ICU 콜레이션은 PostgreSQL이 ICU(International Components for Unicode) 라이브러리를 이용해 문자열 정렬 규칙을 적용하는 방식입니다.

로케일과 정렬 규칙이 ICU 기준으로 관리되므로, 동일한 ICU 버전을 사용한다면 OS가 달라도 더 일관된 정렬 결과를 기대할 수 있습니다. libc 기반 로케일보다 이식성과 재현성이 좋고, 다국어 문자열 정렬에 더 적합할 수도 있습니다.

테이블 생성 및 데이터 입력을 이전과 동일하게 진행합니다.(내용 생략)
데이터까지 입력 후 name 컬럼을 기준으로 정렬을 해서 조회해보도록 하겠습니다.

SELECT * FROM test_table ORDER BY name ASC;
 id |  name
----+--------
  9 | ㄱ
 11 | 가
  7 | 가 방
  6 | 가-방
  5 | 가방
  8 | 가방구
 10 | ㄴ
 12 | 나
  1 | apple
  2 | Apple
  3 | banana
  4 | Banana

로케일 마다 출력되는 정렬 결과가 다른 부분이 있다는 것을 아실 수 있으실 것입니다.

4개의 로케일에 대해서 정렬을 확인해보았고 정렬된 결과를 비교해보겠습니다.

| id   | ko_KR.UTF-8  | C          | en_US.UTF-8 | ko-KR-x-icu |
|------|-------------|-------------|-------------|-------------|
| 1    | Apple       | Apple       | 가          | ㄱ           |
| 2    | Banana      | Banana      | 나          | 가           |
| 3    | apple       | apple       | 가방         | 가 방        |
| 4    | banana      | banana      | 가방구       | 가-방         |
| 5    | ㄱ           | ㄱ          | 가 방        | 가방         |
| 6    | ㄴ          | ㄴ           | 가-방        | 가방구        |
| 7    | 가          | 가           | apple       | ㄴ           |
| 8    | 가 방        | 가 방        | Apple       | 나           |
| 9    | 가-방        | 가-방        | banana      | apple       |
| 10   | 가방         | 가방         | Banana      | Apple       |
| 11   | 가방구       | 가방구        | ㄱ           | banana     |
| 12   | 나          | 나           | ㄴ           | Banana     |

ko_KR.UTF-8 와 C 는 같고 en_US.UTF-8 와 ICU KO 는 다른 결과를 보여주고 있습니다.

locale별 다음과 같은 정렬 처리 방식을 사용합니다.

| locale        | 정렬 기준                    | 영문/한글 순서   | 특수문자 처리       | 이식성                |
|---------------|----------------------------|---------------|-----------------|----------------------|
| ko_KR.UTF-8   | OS libc 기반 언어 규칙         | 로케일 의존     | 로케일 의존        | OS/libc 의존          |
| C             | 바이트 순서(bytewise)          | 영문 → 한글     | 바이트 순서대로    | OS 무관               |
| en_US.UTF-8   | OS libc 기반 영어 로케일 규칙    | 로케일 의존     | 로케일 의존        | OS/libc 의존          |
| ko-KR-x-icu   | ICU 기반 한국어 정렬 규칙        |   로케일 의존    | ICU 규칙에 따름   | OS 무관, ICU 버전 의존  |

위의 테스트 결과를 정리하면 다음과 같습니다.

• 테스트 결과는 현재 포스팅의 테스트 시스템 환경에서 ko_KR.UTF-8의 정렬 결과가 C와 동일하게 나타났습니다.
• 이는 ASCII, 한글 자모, 현대 한글 음절만으로는 libc 한국어 로케일의 차이가 잘 드러나지 않기 때문일 수 있습니다.
• 다만 PostgreSQL의 libc provider는 OS의 C library와 locale data에 의존하므로, ko_KR.UTF-8이 항상 C와 같다고 단정할 수는 없습니다.
• en_US.UTF-8은 libc 기반 로케일이므로 정렬 결과가 OS와 locale data에 의존하며, 한글, 영문, 특수문자, 공백의 상대적 순서는 환경에 따라 달라질 수 있습니다.
• ko-KR-x-icu는 ICU 기반 한국어 콜레이션으로, OS와 무관한 정렬 규칙을 제공하지만 ICU 버전에는 의존할 수 있습니다.

간단하게 속도를 측정해보면 다음과 같은 결과가 나왔습니다.(수행 환경에 따라 다를 수 있습니다)

Time: 0.389 ms - ko_KR.UTF-8
Time: 0.333 ms - c
Time: 0.447 ms - en_US.UTF-8
Time: 0.372 ms - icu ko

C/POSIX는 bytewise 정렬이라 비교 로직이 가장 단순하며, 그러므로 보통의 경우 가장 빠른 속도를 보입니다.
하지만, ko_KR.UTF-8과 en_US.UTF-8은 각각의 언어에 맞는 정렬 규칙을 적용하기 때문에 비교 로직이 더 복잡해지고, 그 결과로 속도가 느려질 수 있습니다.
반면 ICU는 OS와 무관하게 일관된 정렬을 제공하지만, PostgreSQL 문서상 성능 페널티가 있다고 알려져 있어 일반적으로는 libc보다 더 무거울 수 있습니다.

아래 내용에서도 정리를 하겠지만 보통의 상황에서는 C 로케일이 상대적으로 조금이라도 빠른 속도를 보이는 편이며 그래서 PostgreSQL에서는 통상 C 로케일을 사용하는 것이 보통입니다.
      

LIKE 검색

이번에는 문자열 비교와 함께, prefix LIKE 검색에서 인덱스가 실제로 사용되는지 확인해보겠습니다.

PostgreSQL에서는 collation에 따라 문자열 비교 규칙이 달라지므로, 같은 `LIKE '가%'` 같은 접두어 검색이라도 로케일에 따라 일반 btree 인덱스를 사용할 수 있거나, 사용할 수 없을 수 있습니다.

이번 테스트에서도 `C`, `ko_KR.UTF-8`, `en_US.UTF-8`, `ko-KR-x-icu` 네 가지 로케일에서 prefix LIKE 검색의 인덱스 사용 여부를 비교해보겠습니다.

테스트 1

ko_KR.UTF-8 locale 로 설정된 testdb_ko 로 접속해서 기존 테이블을 활용해 테스트를 진행해보도록 하겠습니다.

\c testdb_ko;

기존 테이블에서 데이터만 다시 입력하도록 하겠으며 그 다음 index 를 생성하였습니다.

-- 데이터를 약 10,000건 이상으로 증가
INSERT INTO test_table (name)
SELECT 
    chr(floor(random() * (55203 - 44032 + 1) + 44032)::int) || 
    chr(floor(random() * (55203 - 44032 + 1) + 44032)::int)
FROM generate_series(1, 10000);

-- 인덱스 생성
CREATE INDEX idx_name ON test_table (name);

-- 통계 정보 갱신
ANALYZE test_table;

쿼리는 문자열 범위 검색, 동등 검색, 한글에 대한 LIKE Prefix 검색 3가지 조회조건으로 수행해보도록 하겠습니다.

문자열 범위 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name >= '가' AND name < '나';
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=22.42..92.26 rows=989 width=10) (actual time=0.570..0.888 rows=1083 loops=1)
   Recheck Cond: ((name >= '가'::text) AND (name < '나'::text))
   Heap Blocks: exact=54
   ->  Bitmap Index Scan on idx_name  (cost=0.00..22.18 rows=989 width=0) (actual time=0.552..0.552 rows=1083 loops=1)
         Index Cond: ((name >= '가'::text) AND (name < '나'::text))
 Planning Time: 0.415 ms
 Execution Time: 0.965 ms

Bitmap Index Scan 으로 수행됨을 실행계획을 통해 확인할 수 있습니다.

동등 검색

EXPLAIN ANALYZE
SELECT * FROM test_table
WHERE name ='가';
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_name on test_table  (cost=0.29..8.30 rows=1 width=10) (actual time=0.019..0.020 rows=1 loops=1)
   Index Cond: (name = '가'::text)
 Planning Time: 0.097 ms
 Execution Time: 0.033 ms

Index Scan 으로 수행됨을 실행계획을 통해 확인할 수 있습니다.

LIKE Prefix 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE '가%';
-------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..180.15 rows=1 width=10) (actual time=0.010..1.218 rows=7 loops=1)
   Filter: (name ~~ '가%'::text)
   Rows Removed by Filter: 10005
 Planning Time: 0.126 ms
 Execution Time: 1.230 ms

일반적인 상황 또는 예상과 다르게 Seq Scan 으로 수행 되었으며 Full Table Scan을 의미하며 Index 를 사용하지 못하였습니다.
한글이 아닌 다른 문자일 경우도 다른 상황이 나올수 있을 것이다 라는 의문을 확인해보기 위해 영문으로 검색해보겠습니다.
            

-- 영문에 대해서 LIKE Prefix 검색
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE 'A%';
-------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..180.15 rows=1 width=10) (actual time=0.013..1.282 rows=1 loops=1)
   Filter: (name ~~ 'A%'::text)
   Rows Removed by Filter: 10011
 Planning Time: 0.075 ms
 Execution Time: 1.295 ms

동일하게 인덱스를 사용하지 못하고 Seq Scan 으로 수행되는것을 확인할 수 있습니다.

테스트 2

C locale 로 설정된 testdb_c 로 접속해서 테이블을 생성하고 테스트를 진행합니다.

\c testdb_c

테이블에 데이터 입력 및 인덱스 생성, 통계정보 갱신은 이전과 동일하게 수행합니다.
이제 다시 다음과 같이 실행계획을 확인해보도록 하겠습니다.

문자열 범위 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name >= '가' AND name < '나';
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=22.40..92.21 rows=987 width=10) (actual time=0.121..0.421 rows=1075 loops=1)
   Recheck Cond: ((name >= '가'::text) AND (name < '나'::text))
   Heap Blocks: exact=55
   ->  Bitmap Index Scan on idx_name  (cost=0.00..22.16 rows=987 width=0) (actual time=0.106..0.106 rows=1075 loops=1)
         Index Cond: ((name >= '가'::text) AND (name < '나'::text))
 Planning Time: 0.254 ms
 Execution Time: 0.482 ms

동등 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name ='가';
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_name on test_table  (cost=0.29..8.30 rows=1 width=10) (actual time=0.011..0.012 rows=1 loops=1)
   Index Cond: (name = '가'::text)
 Planning Time: 0.114 ms
 Execution Time: 0.026 ms

LIKE Prefix 검색

-- 한글
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE '가%';
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_name on test_table  (cost=0.29..8.31 rows=1 width=10) (actual time=0.008..0.011 rows=5 loops=1)
   Index Cond: ((name >= '가'::text) AND (name < '각'::text))
   Filter: (name ~~ '가%'::text)
 Planning Time: 0.223 ms
 Execution Time: 0.025 ms

-- 영문
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE 'A%';
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_name on test_table  (cost=0.29..8.31 rows=1 width=10) (actual time=0.010..0.012 rows=1 loops=1)
   Index Cond: ((name >= 'A'::text) AND (name < 'B'::text))
   Filter: (name ~~ 'A%'::text)
 Planning Time: 0.160 ms
 Execution Time: 0.025 ms

이전 ko_KR.UTF-8 테스트와 다르게 C 로케일에서는 인덱스를 사용한 실행계획을(Index Scan using) 확인할 수 있습니다.

테스트 3

en_US.UTF-8 locale 로 설정된 testdb_en 로 접속해서 테스트를 진행합니다.

\c testdb_en

테이블에 데이터 입력 및 인덱스 생성, 통계정보 갱신은 이전과 동일하게 수행합니다.
이제 다시 다음과 같이 실행계획을 확인해보도록 하겠습니다.

문자열 범위 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name >= '가' AND name < '나';
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=4.80..61.79 rows=50 width=10) (actual time=0.079..0.080 rows=1 loops=1)
   Recheck Cond: ((name >= '가'::text) AND (name < '나'::text))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_name  (cost=0.00..4.79 rows=50 width=0) (actual time=0.075..0.075 rows=1 loops=1)
         Index Cond: ((name >= '가'::text) AND (name < '나'::text))
 Planning Time: 0.353 ms
 Execution Time: 0.095 ms

동등 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name ='가';
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_name on test_table  (cost=0.29..8.30 rows=1 width=10) (actual time=0.112..0.113 rows=1 loops=1)
   Index Cond: (name = '가'::text)
 Planning Time: 0.161 ms
 Execution Time: 0.129 ms

LIKE Prefix 검색

-- 한글
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE '가%';
-------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..180.15 rows=1 width=10) (actual time=0.011..1.225 rows=5 loops=1)
   Filter: (name ~~ '가%'::text)
   Rows Removed by Filter: 10007
 Planning Time: 0.131 ms
 Execution Time: 1.237 ms

-- 영문
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE 'A%';
-------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..180.15 rows=1 width=10) (actual time=0.016..1.670 rows=1 loops=1)
   Filter: (name ~~ 'A%'::text)
   Rows Removed by Filter: 10011
 Planning Time: 0.085 ms
 Execution Time: 1.712 ms

en_US.UTF-8 와 ko_KR.UTF-8 는 LIKE 검색에서 인덱스를 사용하지 못하는 동일한 실행계획을 보여주고 있습니다.

테스트 4

ICU 방식의 ko_KR.UTF-8 locale 로 설정된 testdb_icu_ko 로 접속해서 테스트를 진행합니다.

\c testdb_icu_ko

테이블에 데이터 입력 및 인덱스 생성, 통계정보 갱신은 이전과 동일하게 수행합니다.
이제 다시 다음과 같이 실행계획을 확인해보도록 하겠습니다.

문자열 범위 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name >= '가' AND name < '나';
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=26.60..96.69 rows=1006 width=10) (actual time=0.149..0.419 rows=1087 loops=1)
   Recheck Cond: ((name >= '가'::text) AND (name < '나'::text))
   Heap Blocks: exact=55
   ->  Bitmap Index Scan on idx_name  (cost=0.00..26.35 rows=1006 width=0) (actual time=0.134..0.134 rows=1087 loops=1)
         Index Cond: ((name >= '가'::text) AND (name < '나'::text))
 Planning Time: 0.328 ms
 Execution Time: 0.481 ms

동등 검색

EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name ='가';
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_name on test_table  (cost=0.29..8.30 rows=1 width=10) (actual time=0.021..0.022 rows=1 loops=1)
   Index Cond: (name = '가'::text)
 Planning Time: 0.148 ms
 Execution Time: 0.035 ms

LIKE Prefix 검색

-- 한글
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE '가%';
-------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..180.15 rows=1 width=10) (actual time=0.011..1.396 rows=7 loops=1)
   Filter: (name ~~ '가%'::text)
   Rows Removed by Filter: 10005
 Planning Time: 0.131 ms
 Execution Time: 1.409 ms

-- 영문
EXPLAIN ANALYZE 
SELECT * FROM test_table 
WHERE name LIKE 'A%';
-------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..180.15 rows=1 width=10) (actual time=0.017..1.274 rows=1 loops=1)
   Filter: (name ~~ 'A%'::text)
   Rows Removed by Filter: 10011
 Planning Time: 0.095 ms
 Execution Time: 1.289 ms

ICU 방식의 ko_KR.UTF-8 도 en_US.UTF-8 와 ko_KR.UTF-8 마찬가지로 LIKE 검색에서 인덱스를 사용하지 못하는 동일한 실행계획을 보여주고 있습니다.
           

로케일을 C 로 설정

정렬 순서와 LIKE 검색을 통해서 로케일(Locale)에 따른 다른 검색결과 및 LIKE 시 인덱스 사용 가능 여부 등을 확인해보았으며, 위의 내용에 대해서 정리 및 추가로 확인을 해보도록 하겠습니다.
             

C 로케일이 아닐 경우 LIKE 검색시 인덱스를 사용 되지 못하는 이유

PostgreSQL에서 LIKE 'prefix%' 검색이 일반 B-Tree 인덱스를 항상 사용할 수 있는 것은 아닙니다.

핵심 이유는 LIKE가 단순한 문자열 포함 검색이 아니라, 내부적으로는 "접두어(prefix)를 기준으로 한 범위 검색(range scan)"으로 바꿀 수 있어야만 인덱스를 탈 수 있기 때문입니다.

문제는 이 범위 변환이 모든 collation에서 안전하게 성립하지 않는다는 점입니다.
B-Tree 인덱스는 인덱스 키를 collation 순서대로 정렬하지만, locale 기반 collation은 바이트 순서(byte order)와 문자열의 접두어 관계가 항상 같은 순서를 보장하지 않습니다.

즉, LIKE '가%' 나 LIKE 'A%' 처럼 접두어가 같은 문자열들이 인덱스 상에서 연속된 구간으로 정확히 묶인다고 확신할 수 없으면, PostgreSQL 플래너(Planner)는 일반 B-Tree 인덱스를 사용한 범위 스캔으로 변환하지 않게 됩니다.

반대로 C collation은 bytewise 정렬이기 때문에 접두어 순서와 인덱스 순서가 일치합니다. 또는 text_pattern_ops 같은 pattern opclass를 사용하면, locale 규칙이 아니라 문자 단위 비교를 사용 하므로 prefix 검색을 위한 인덱스 최적화가 가능해집니다.

정리하면, prefix LIKE가 일반 인덱스를 못 타는 이유는 "한글이라서"가 아니라, "C"가 아닌 locale collation에서는 접두어를 안전한 인덱스 범위로 바꿀 수 없기 때문입니다.

그럼 혹시 연속된 구간으로 정확히 묶여있다고 확신할수 없다고 하니 SQL Hint 로 강제화 한다면 가능할까? 라고 생각해볼수도 있습니다. pg_hint_plan Extension 을 설치하면 오라클이나 MySQL처럼 SQL Hint 를 사용할 수 있으며 pg_hint_plan를 통해 SQL Hint 를 주고 실행계획을 확인해보면 다음과 같은 결과가 나타납니다.

-- testdb_ko 데이터베이스로 접속
\c testdb_ko

-- 힌트를 포함하여 실행계획을 확인
EXPLAIN ANALYZE 
SELECT /*+ IndexScan(test_table idx_name) */ 
* FROM test_table 
WHERE name LIKE '가%';
---------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=10000000000.00..10000000180.15 rows=1 width=10) (actual time=110.031..110.996 rows=7 loops=1)
   Filter: (name ~~ '가%'::text)
   Rows Removed by Filter: 10005
 Planning Time: 0.533 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.266 ms (Deform 0.073 ms), Inlining 94.066 ms, Optimization 8.958 ms, Emission 6.985 ms, Total 110.275 ms
 Execution Time: 128.868 ms

Seq Scan으로 실행되었습니다. pg_hint_plan은 힌트를 적용할 때, 플래너가 그 힌트에 맞는 실행 경로를 만들 수 있으면 해당 경로를 선택하려고 합니다.

그런데 이번처럼 IndexScan(test_table idx_name) 힌트를 주었음에도 Seq Scan이 선택되었다면, 현재 collation과 인덱스 정의 조합에서는 `LIKE '가%'`를 IndexScan 경로로 안전하게 변환할 수 없었다고 보는 것이 맞습니다.
즉, 이 경우는 단순히 힌트가 무시된 것이 아니라, 현재 조건에서 힌트가 요구하는 IndexScan 경로가 성립하지 않았음을 의미합니다.
                  

로케일(Locale)을 C 로 설정이 필요한 이유

정렬 테스트와 LIKE 검색 인덱스 사용 유무 테스트를 통해 로케일(Locale)을 'C'로 설정이 필요한 이유를 다음과 같이 확인할 수 있습니다.

정렬 일관성

• ko_KR.UTF-8은 테스트 환경에서 C와 동일한 결과를 보였으나, 이는 OS와 libc 버전에 따라 달라질 수 있어 이식성이 보장되지 않음
• en_US.UTF-8은 한글을 영문보다 앞에 배치하는 등 한글 서비스에 적합하지 않은 정렬 결과를 보임
• C 로케일은 바이트(bytewise) 순서 기반으로 OS에 관계없이 항상 동일하고 예측 가능한 정렬 결과를 보장

LIKE Prefix 검색 시 인덱스 사용 가능 여부

• C 로케일에서만 LIKE '가%' 와 같은 Prefix 검색에서 일반 B-Tree 인덱스를 사용 가능
• ko_KR.UTF-8, en_US.UTF-8, ko-KR-x-icu 모두 LIKE Prefix 검색 시 인덱스를 사용하지 못하고 Seq Scan(Full Table Scan)으로 수행됨
• 이는 한글만의 문제가 아니라 영문 LIKE 'A%' 검색에서도 동일하게 발생
• C 이외의 로케일에서는 언어적 정렬 가중치(Linguistic Weights)로 인해 접두어를 B-Tree 범위 스캔으로 안전하게 변환할 수 없기 때문

C 로 설정하여 문제 해결하기

정렬의 일관성이나 LIKE 검색시 인덱스를 사용이 가능하게 하려면 로케일을 C로 선택하면 됩니다.

다만, C 로케일로 설정(선택)하는데 있어서 단계적으로 확인 및 설정을 해야합니다.

initdb 수행시

PostgreSQL 인스턴스를 생성시 initdb를 수행하게 되며, initdb를 수행하여 인스턴스(클러스터 데이터베이스)를 생성시에도 locale 을 설정할 수 있습니다.

initdb 수행 시 locale을 지정하지 않으면, OS의 현재 locale 환경변수($LANG, $LC_ALL 등)를 그대로 따라가며, 기본 생성되는 template1 데이터베이스도 해당 OS locale로 생성됩니다.

이후 CREATE DATABASE 시 template1을 기반으로 생성 되므로, 별도의 지정없이 CREATE DATABASE 명령어 만으로 생성하게 된다면 C 로케일(Locale)이 아닌 ko_KR.UTF-8 등과 같은 로케일로 의도치 않게 생성될 수 있습니다.

마찬가지로 테이블 생성 시에도 별도로 설정하지 않으면 데이터베이스의 로케일을 그대로 상속받으므로, 컬럼 역시 C 로케일이 아닌 ko_KR.UTF-8 등으로 생성됩니다.

상속의 흐름으로 요약하면 다음과 같습니다.

initdb (locale 미지정)
    → OS locale (예: ko_KR.UTF-8) → template1 생성
        → CREATE DATABASE (locale 미지정)
            → template1 locale 상속 → ko_KR.UTF-8
                → CREATE TABLE (collation 미지정)
                    → 데이터베이스 locale 상속 → ko_KR.UTF-8

그래서 설치형 PostgreSQL에서는 initdb를 통해 인스턴스 생성시부터 C 로케일로 설정하는 것이 필요합니다.

AWS RDS for PostgreSQL과 Aurora PostgreSQL은 AWS에서 제공하는 관리형 서비스이므로 OS에 직접 접근하거나 initdb를 수행할 수 없습니다.
따라서 인스턴스(클러스터) 레벨에서 로케일을 C로 설정하는 것이 불가능하며, 기본 로케일은 en_US.UTF-8로 생성됩니다.
이런 환경에서는 다음에서 설명하는 데이터베이스 생성 시 명시적으로 C 로케일을 지정하는 방법을 참조합니다.

데이터베이스 생성시

데이터베이스 생성시에도 별도의 로케일 설정하여 생성할 수 있습니다. 이 내용은 initdb를 통해서 C 가 아닌 다른 로케일로 인스턴스 생성을 하였더라도 가능한 내용입니다.

데이터베이스 생성시 initdb를 수행한 로케일과 다른 로케일로 생성하기 위해서는 template0 을 사용해서 생성해야합니다.

template1 - initdb 시 생성된 locale이 고정되어 있어, 다른 locale로 DB 생성이 불가
template0 - 아무것도 없는 순수한 빈 상태이므로, 원하는 locale을 자유롭게 지정 가능

데이터베이스 생성시 로케일 지정은 다음과 같이 합니다.

CREATE DATABASE testdb_c 
    TEMPLATE = template0 
    ENCODING = 'UTF8' 
    LC_COLLATE = 'C' 
    LC_CTYPE = 'C';

테이블 생성시 또는 컬럼 단위 변경

테이블 생성시에도 컬럼 단위로 로케일을 지정해서 생성할 수 있으며, 테이블을 만들 때 컬럼 뒤에 COLLATE "C"를 명시하면 됩니다.

테스트를 위해 다음과 같은 순서로 테스트 테이블 생성 및 확인해보도록 하겠습니다.

-- ko_KR.UTF-8 로케일인 testdb_ko 로 접속
\c testdb_ko

-- 테이블 생성
CREATE TABLE column_locale_test (
    id SERIAL PRIMARY KEY,
    name TEXT COLLATE "C" NOT NULL,  -- 이 컬럼만 'C' 로케일 적용
    address TEXT                     -- 기본 로케일 (ko_KR.UTF-8) 적용
);

-- 인덱스 생성
CREATE INDEX idx_locale_test_name ON column_locale_test (name);


-- 테이블 컬럼 정보 확인
\d column_locale_test
 Column  |  Type   | Collation | Nullable |                    Default
---------+---------+-----------+----------+------------------------------------------------
 id      | integer |           | not null | nextval('column_locale_test_id_seq'::regclass)
 name    | text    | C         | not null |
 address | text    |           |          |

실행계획을 확인 해보도록 하겠습니다. 인덱스를 사용하는 것을 확인하실수 있습니다.

EXPLAIN ANALYZE 
SELECT * FROM column_locale_test 
WHERE name LIKE '가%';
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on column_locale_test  (cost=4.19..12.65 rows=4 width=68) (actual time=0.006..0.006 rows=0 loops=1)
   Filter: (name ~~ '가%'::text)
   ->  Bitmap Index Scan on idx_locale_test_name  (cost=0.00..4.19 rows=4 width=0) (actual time=0.002..0.002 rows=0 loops=1)
         Index Cond: ((name >= '가'::text) AND (name < '각'::text))

이미 생성된 테이블에 대해서는 컬럼 레벨에서 변경할 수 있습니다.

-- 컬럼 변경
ALTER TABLE column_locale_test 
ALTER COLUMN address TYPE TEXT COLLATE "C";

-- 테이블 컬럼 정보 확인
\d column_locale_test
 Column  |  Type   | Collation | Nullable |                    Default
---------+---------+-----------+----------+------------------------------------------------
 id      | integer |           | not null | nextval('column_locale_test_id_seq'::regclass)
 name    | text    | C         | not null |
 address | text    | C         |          |

주의 할점으로 운영 중인 테이블이라면 ALTER COLUMN TYPE은 테이블 전체를 재작성(rewrite)하므로 락(Lock)이 발생하며, 테이블의 크기가 큰 경우 상대적으로 시간이 오래 소요 될 수 있습니다.

인덱스 생성 시 지정하기

테이블 구조는 그대로 두고, 인덱스 생성시 로케일을 설정할 수 있으며, 기존 테이블의 변경하지 않아도 됨으로 더 많이 사용 되는 방법입니다.

테스트를 위해서 address 컬럼을 다시 변경하겠습니다.

-- 컬럼 변경
ALTER TABLE column_locale_test 
ALTER COLUMN address TYPE TEXT COLLATE "ko_KR.utf8";

-- 테이블 구조 확인
\d column_locale_test
 Column  |  Type   | Collation  | Nullable |                    Default
---------+---------+------------+----------+------------------------------------------------
 id      | integer |            | not null | nextval('column_locale_test_id_seq'::regclass)
 name    | text    | C          | not null |
 address | text    | ko_KR.utf8 |          |

먼저 로케일 지정 없이 생성을 해보도록 하겠습니다.

운영 중인 DB라면 인덱스를 삭제하고 만드는 동안 테이블에 락(Lock)이 걸려 조회가 안 될 수 있습니다. 이때는 CONCURRENTLY 옵션을 사용하여 서비스 중단 없이 새 인덱스를 만들고 기존 것을 삭제하는 것이 좋습니다.

먼저 현재 상태에서 실행계획은 확인해보면 예상대로 Seq Scan 으로 실행됩니다.

EXPLAIN ANALYZE 
SELECT * FROM column_locale_test 
WHERE address LIKE '가%';
--------------------------------------------------------------------------------------------------------------
 Seq Scan on column_locale_test  (cost=0.00..20.62 rows=4 width=68) (actual time=0.006..0.007 rows=0 loops=1)
   Filter: (address ~~ '가%'::text)
 Planning Time: 0.245 ms
 Execution Time: 0.020 ms

인덱스를 교체해보도록 하겠습니다.

-- 새로운 인덱스 먼저 생성
CREATE INDEX CONCURRENTLY idx_locale_test_address_c ON column_locale_test (address COLLATE "C");

-- 기존 인덱스 삭제
DROP INDEX CONCURRENTLY idx_locale_test_address_ko_kr;

실행계획을 확인해보면 인덱스를 통해 실행 되는것 확인할 수 있습니다.

EXPLAIN ANALYZE 
SELECT * FROM column_locale_test 
WHERE address LIKE '가%';
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on column_locale_test  (cost=4.19..12.65 rows=4 width=68) (actual time=0.007..0.008 rows=0 loops=1)
   Filter: (address ~~ '가%'::text)
   ->  Bitmap Index Scan on idx_locale_test_address_c  (cost=0.00..4.19 rows=4 width=0) (actual time=0.003..0.003 rows=0 loops=1)
         Index Cond: ((address >= '가'::text) AND (address < '각'::text))
 Planning Time: 0.254 ms
 Execution Time: 0.025 ms

C Collate 로 생성하는 것외에 text_pattern_ops 연산자 클래스도 가능합니다.

-- 새로운 인덱스 생성
CREATE INDEX CONCURRENTLY idx_locale_test_address_pattern_ops ON column_locale_test (address text_pattern_ops);

-- 기존 인덱스 삭제
DROP INDEX CONCURRENTLY idx_locale_test_address_c;

실행계획을 확인해보면 인덱스를 사용해서 실행하는 것을 확인하실수 있습니다.

EXPLAIN ANALYZE 
SELECT * FROM column_locale_test 
WHERE address LIKE '가%';
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on column_locale_test  (cost=4.19..12.65 rows=4 width=68) (actual time=0.009..0.010 rows=0 loops=1)
   Filter: (address ~~ '가%'::text)
   ->  Bitmap Index Scan on idx_locale_test_address_pattern_ops  (cost=0.00..4.19 rows=4 width=0) (actual time=0.005..0.006 rows=0 loops=1)
         Index Cond: ((address ~>=~ '가'::text) AND (address ~<~ '각'::text))

COLLATE "C"와 text_pattern_ops 두 가지 방법 모두 LIKE Prefix 검색 시 인덱스를 사용할 수 있다는 공통점이 있습니다.

다만 차이점이 있습니다. COLLATE "C"는 바이트 순서 기반으로 정렬과 비교를 모두 처리하기 때문에 LIKE 검색뿐만 아니라 ORDER BY 정렬에도 해당 인덱스를 활용할 수 있습니다.

반면 text_pattern_ops는 패턴 매칭 전용 비교 방식으로, LIKE Prefix 검색에서는 인덱스를 사용할 수 있지만 ORDER BY 정렬에는 해당 인덱스를 사용할 수 없습니다.

따라서 LIKE 검색만 필요한 경우라면 두 방법 모두 사용 가능하지만, 정렬까지 인덱스를 활용해야 하는 경우라면 COLLATE "C"를 선택하는 것이 적합합니다.
                

Conclusion

이번 글에서는 PostgreSQL의 Locale(로케일)과 Collation(콜레이션)의 개념과 차이점을 살펴보고, 로케일 설정이 문자열 정렬과 LIKE Prefix 검색 시 인덱스 사용 여부에 어떤 영향을 미치는지 직접 테스트를 통해 확인해 보았습니다.

테스트를 통해 ko_KR.UTF-8, C, en_US.UTF-8, ko-KR-x-icu 네 가지 로케일의 정렬 결과 차이를 확인하였고, C 로케일에서만 LIKE Prefix 검색 시 일반 B-Tree 인덱스를 활용할 수 있음을 확인하였습니다.

또한 정렬 테스트 결과를 통해서 확인한 내용과 같이 한글의 정렬까지 고려한다면 결론적으로 C 로케일을 선택하는게 가장 권장되는 설정이 될 것입니다.

C 로케일이 아닌 다른 로케일로 이미 운영 중인 환경에서도  컬럼 또는 인덱스 레벨에서 COLLATE "C"를 지정하는 방법으로 문제를 해결할 수 있다는 점도 함께 살펴보았습니다.

PostgreSQL을 처음 구성하는 단계에서 로케일 설정은 template1 의 로케일이 변경이 불가능하고 일반 데이터베이스도 생성 이후 로케일이 변경이 불가능한 만큼 초기 구성(initdb) 및 데이터베이스 생성시 신중하게 결정하는 것이 중요합니다.

컬럼 변경 및 인덱스 Collate 변경으로 대처가 가능하지만 사용중인 오브젝트를 변경해야함으로 데이터베이스 및 테이블 생성, 컬럼 추가시 로케일에 대해서 정확히 확인 및 설정을 해야할 필요가 있습니다.

여기서 글을 마무리 하도록 하겠습니다. 긴글 읽어주셔서 감사합니다.
             

Reference

Reference Link
• Locale Support
• Collation Support

연관된 다른글

PostgreSQL 14 설치 - 소스 컴파일 - dnf 패키지 설치 - Source Compile

안녕하세요 이번 글은 PostgreSQL 14 버전 설치에 대해서 확인 해보도록 하겠습니다. PostgreSQLThe PostgreSQL Global Development Group에서 개발하는 오픈 소스 ORDBMS 으로 1996년에 출시 되었습니다. 발음은 ‘포스트그레스큐엘’이라고

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io