이 글에서 다룰 주제
- 표현: 같은 글자 모양이 다른 코드 포인트일 수 있는가?
- 비교: Collation은 저장된 값을 바꾸는가?
- 설계: 원본 파일명·비교 키·저장 키를 어떻게 나눌 것인가?
주요 단어 · Encoding · Locale · Collation · ICU · Deterministic · NFC/NFD · normalize
화면에는 같은 한글이 보이는데 중복 검사나 파일명 비교에서 다른 값으로 처리될 수 있다. 반대로 DB가 두 문자열을 같다고 비교해도 외부 저장소의 키나 해시는 다를 수 있다. 문제를 풀려면 문자열의 표현을 바꾸는 정규화와 정렬·동등성을 정하는 비교 규칙을 분리해야 한다.
Collation은 문자열의 정렬·동등성 규칙이다. Unicode 정규화는 같은 의미의 문자 표현을 정해진 형태로 변환하는 과정으로, 비교 규칙과 역할이 다르다.
자료와 예제 기준 — PostgreSQL 18을 중심으로 개인 학습 노트를 재구성했다. SQL·실행 계획·설정값은 설명 및 재현용 예제이며 이 글을 위해 운영 DB에서 새로 측정한 결과는 아니다. DDL/DML 예제는 독립적인 테스트 환경에서 사용한다.
1. Collation으로 정렬과 동등성 정하기
한 줄 정의
Collation은 문자열을 어떤 순서로 정렬하고 어떤 차이를 동등하게 취급할지 결정하는 규칙이다. 서버 개발의 문자열 Comparator와 연결해 이해할 수 있다. 저장된 문자열 자체를 변환하는 함수는 아니다.
| 질문 | 예 |
|---|---|
| 정렬 순서 | A와 a 중 무엇이 먼저인가? |
| 대소문자 | Alice와 alice가 같은 값인가? |
| 악센트 | cafe와 café가 같은 값인가? |
| 문자열 속 숫자 | file2와 file10 중 무엇이 먼저인가? |
| Unicode 표현 | NFC·NFD의 가를 같은 값으로 볼 것인가? |
정렬 외에 문자열 비교, 중복 제거, 그룹화, JOIN, UNIQUE 등에도 영향을 준다. 대소문자 변환·패턴 연산에도 Locale 규칙이 관여하지만 지원 범위를 연산별로 확인해야 한다.
Encoding·Locale·Collation
| 개념 | 역할 | 예 |
|---|---|---|
| Encoding | 문자를 바이트로 표현 | UTF-8 |
| Unicode 정규화 | 코드 포인트 표현 통일 | NFC·NFD |
| Collation | 정렬·동등성 정책 | C·ICU 한국어 정렬 |
| LC_COLLATE | libc Locale의 정렬 범주 | 문자열 순서 |
| LC_CTYPE | 문자 분류·대소문자 관련 범주 | 문자 여부와 변환 |
| LC_TIME | 날짜·시간 지역화 | 지역별 표현 |
UTF-8은 한국어 사전순이나 대소문자 무시를 자동으로 정하지 않는다. C Collation으로도 UTF-8 한글을 저장할 수 있다. 인코딩과 정렬을 구분한다.
Provider
PostgreSQL 18 기준이다.
| Provider | 구현 주체 | 특징 |
|---|---|---|
| libc | OS C 라이브러리 | OS Locale 설치 상태·버전에 영향받음 |
| icu | ICU 라이브러리 | 다국어 정렬과 세밀한 비교 옵션 |
| builtin | PostgreSQL 내장 | C·C.UTF-8·PG_UNICODE_FAST 등 제한된 Locale 지원 |
C는 바이트 값 순서의 대표적인 규칙이다. ICU는 언어별 정렬 규칙을 제공하지만 ICU 버전 변경의 영향을 받을 수 있다. pgAdmin의 public → Collations는 public에 만든 객체를 보여주며 기본 객체는 주로 pg_catalog에 있다.
SELECT n.nspname AS schema_name,
c.collname,
c.collprovider,
c.collisdeterministic,
c.collversion
FROM pg_collation AS c
JOIN pg_namespace AS n ON n.oid = c.collnamespace
ORDER BY n.nspname, c.collname;collprovider는 c=libc, i=ICU, b=builtin, d=DB 기본값이다. 카탈로그에는 현재 DB 인코딩에 적용되지 않는 Collation도 포함될 수 있다.
숫자가 들어간 문자열 정렬
SELECT name
FROM (VALUES ('file1'), ('file2'), ('file10')) AS t(name)
ORDER BY name COLLATE "C";예상 순서: file1 → file10 → file2. 숫자 자릿값이 아니라 문자 순서로 비교하기 때문이다.
CREATE COLLATION public.numeric_order (
provider = icu,
locale = 'und-u-kn-true'
);
SELECT name
FROM (VALUES ('file1'), ('file2'), ('file10')) AS t(name)
ORDER BY name COLLATE public.numeric_order;예상 순서: file1 → file2 → file10. kn-true는 숫자 부분을 하나의 수로 비교하도록 한다. 문자열을 숫자 타입으로 바꾸지 않는다. SemVer의 사전 릴리스 규칙 등을 모두 해석하는 기능도 아니다.
ICU 비교 수준과 deterministic
ICU는 언어별 규칙으로 비교 가중치를 만들고 여러 수준의 차이를 구분한다.
| 수준 | 일반적인 의미 |
|---|---|
| Primary | 기본 문자 차이 |
| Secondary | 악센트 등의 차이 |
| Tertiary | 대소문자 등의 차이 |
언어별 tailoring이 있으므로 모든 문자를 이 표 하나로 판단해서는 안 된다.
CREATE COLLATION public.case_insensitive (
provider = icu,
locale = 'und-u-ks-level2',
deterministic = false
);| 옵션 | 의미 |
|---|---|
| und | 특정 언어를 지정하지 않는 기본 Locale |
| ks-level2 | 두 번째 비교 수준까지 사용 |
| deterministic=false | 규칙상 같다면 바이트 차이로 다시 구분하지 않음 |
SELECT
'Alice' COLLATE public.case_insensitive = 'alice' AS same_case,
'cafe' COLLATE public.case_insensitive = 'café' AS same_accent;예상 결과는 true, false다. 대소문자 차이는 무시하고 악센트 차이는 구분한다.
| 구분 | deterministic=true | deterministic=false |
|---|---|---|
| 기본 | 기본 설정 | 명시적 지정 |
| 언어 규칙상 동등할 때 | 바이트 비교로 추가 구분 | 규칙의 동등성 인정 |
| 다른 바이트를 같은 text로 판단 | 하지 않음 | 규칙에 따라 가능 |
| 지원 | 기본·일반 Collation | ICU 필요 |
비결정적이라는 이름은 결과가 매번 랜덤이라는 뜻이 아니다. false만으로 대소문자 무시가 정해지는 것도 아니다. Locale 옵션과 함께 동작한다. 이 설명은 text/varchar 중심이며 char 타입의 공백 처리 같은 타입 자체의 규칙은 별도다.
DB·컬럼·표현식의 적용 범위
CREATE COLLATION public.korean_order (
provider = icu,
locale = 'ko'
);
CREATE TABLE public.person (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text COLLATE public.korean_order
);
-- 컬럼의 한국어 규칙
SELECT id, name FROM public.person ORDER BY name;
-- 해당 표현식에서 C 규칙
SELECT id, name FROM public.person ORDER BY name COLLATE "C";DB 기본값은 별도 지정이 없는 경우의 기준이다. 컬럼의 규칙이 있고, 표현식의 명시적 COLLATE가 이를 우선할 수 있다. 서로 다른 비기본 Collation을 갖는 입력을 결합하면 비교에 사용할 규칙이 모호해져 오류가 발생할 수 있다.
두 입력에 서로 다른 명시적 COLLATE를 쓰면 한쪽이 임의로 이기는 것이 아니라 충돌한다. 서로 다른 Collation 객체는 동작 설정이 같아도 PostgreSQL에서 다른 객체로 취급하므로 혼용을 피한다.
UNIQUE·GROUP BY·JOIN의 의미
CREATE TABLE public.app_user_demo (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username text COLLATE public.case_insensitive NOT NULL UNIQUE
);
INSERT INTO public.app_user_demo(username) VALUES ('Alice');
-- 다음 문장은 UNIQUE 위반을 예상하는 별도 실험이다.
INSERT INTO public.app_user_demo(username) VALUES ('alice');Alice를 저장했을 때 소문자로 자동 변환되는 것은 아니다. 저장값은 Alice이며 비교 기준이 대소문자를 무시한다. 해당 Collation을 따르는 DISTINCT·GROUP BY·동등 JOIN도 같은 동등성 정책을 사용한다.
동등한 여러 표기가 있어도 GROUP BY가 항상 원하는 대표 표기를 선택해준다고 가정하지 않는다. 표시용 대표값은 별도로 정한다. 동등 정렬 키가 여러 개면 ORDER BY name, id처럼 보조 키를 넣어 페이지네이션 순서를 명확히 한다.
Collation 변경은 중복 정책 변경이 될 수 있다. 기존 데이터가 새 규칙에서 충돌하는지 먼저 확인해야 한다. 이름의 표시 순서와 계정 식별자의 동등성은 서로 다른 요구사항이다.
B-tree 인덱스
문자열 B-tree 키는 지정된 Collation의 순서를 전제로 배치된다.
CREATE INDEX person_name_ko_idx ON public.person(name);
CREATE INDEX person_name_c_idx ON public.person(name COLLATE "C");| 인덱스 | 정렬 기준 |
|---|---|
| person_name_ko_idx | name 컬럼의 korean_order |
| person_name_c_idx | C |
한국어 순서 인덱스만으로 C 순서의 정렬 요구를 그대로 만족할 수 없다. 조건·비용·조회량에 따라 옵티마이저가 실제 접근 방식을 결정한다.
비결정적 Collation에도 인덱스를 만들 수 있지만 B-tree deduplication 최적화가 지원되지 않는다. 비교 비용과 인덱스 크기를 실제 데이터로 측정한다.
패턴 검색의 버전 차이
PostgreSQL 18은 비결정적 Collation을 이용한 LIKE를 지원한다. ILIKE·SIMILAR TO·POSIX 정규식은 같은 방식으로 지원하지 않는다. 예전 버전 문서의 제한을 18에 그대로 적용하거나, LIKE 지원을 모든 패턴 연산 지원으로 확대해서는 안 된다.
또한 LIKE가 실행된다는 사실과 원하는 인덱스로 빨라진다는 사실은 별개다. 접두 검색·부분 문자열·전문 검색에 맞는 인덱스 전략을 별도로 검토한다.
OS·ICU 버전 변경 운영
OS 또는 ICU 업데이트로 비교 규칙이 달라지면, 이전 규칙으로 정렬된 인덱스와 새 비교 함수 사이에 불일치가 생길 수 있다.
- 사용 중인 Provider·Collation과 영향받는 객체를 식별한다.
- 새 규칙에서 유일성 충돌과 쿼리 의미 변화를 확인한다.
- 영향받는 인덱스 등 객체를 새 규칙에 맞게 재구축한다.
- 마지막으로 기록된 Collation 버전을 갱신한다.
-- 객체 재구축을 완료한 뒤 실행하는 예시
ALTER COLLATION public.korean_order REFRESH VERSION;REFRESH VERSION은 메타데이터 갱신이며 인덱스 복구 명령이 아니다. DB 기본 Collation에 대한 관리와 개별 Collation 객체 관리는 대상을 구분한다. 실제 운영에서는 작업 시간·잠금·복제 환경·재구축 실패 시 대응을 계획한다.
적용 판단
| 요구사항 | 설계 방향 |
|---|---|
| 사람에게 자연스러운 이름 순서 | 언어별 ICU 규칙을 실제 데이터로 검증 |
| 정확한 토큰·코드 식별 | 대소문자·표현 차이를 허용할지 계약을 먼저 정의 |
| 대소문자 무시 유일성 | ICU 비결정적 규칙 또는 정규화 키 방식 비교 |
| DB 밖에서도 동일한 표현 필요 | Collation과 별개로 NFC 등의 입력 정규화 검토 |
| 한글 초성·형태소 검색 | 별도 검색 표현·분석기 필요 |
2. 한글 Unicode 표현과 정규화
핵심 구분
NFC·NFD는 Unicode 정규화 형태다. normalize는 표현을 변환하는 함수다. Collation은 저장 표현을 그대로 두고 정렬·동등성을 결정한다. UTF-8은 이들과 별개로 문자를 바이트로 인코딩하는 방식이다.
| 항목 | 무엇을 결정하는가? |
|---|---|
| UTF-8 | 코드 포인트를 바이트로 표현하는 방법 |
| NFC | 정준 분해 후 가능한 합성을 수행한 형태 |
| NFD | 정준 분해한 형태 |
| normalize(text, NFC) | NFC 형태의 새 문자열 반환 |
| Collation | 문자열의 비교 순서와 동등성 |
같은 '가'의 두 표현
| 구분 | NFC | NFD |
|---|---|---|
| 화면 | 가 | 가로 조합되어 보일 수 있음 |
| 내부 구성 | 가 | 조합용 초성 ᄀ + 중성 ᅡ |
| 코드 포인트 | U+AC00 | U+1100 + U+1161 |
| 코드 포인트 수 | 1 | 2 |
| UTF-8 바이트 수 | 3 | 6 |
| UTF-8 16진수 | eab080 | e18480e185a1 |
폰트·렌더러가 조합 자모를 하나의 음절처럼 표시할 수 있지만, 실제 문자 배열은 다르다. 현대 한글 음절은 NFC에서 완성형 음절로 합성할 수 있다. 모든 언어의 모든 결합 문자가 NFC에서 한 코드 포인트가 되는 것은 아니다.
'한글'의 표현
| 부분 | NFC | NFD |
|---|---|---|
| 한 | U+D55C | U+1112 + U+1161 + U+11AB |
| 글 | U+AE00 | U+1100 + U+1173 + U+11AF |
| 전체 코드 포인트 수 | 2 | 6 |
| 전체 UTF-8 바이트 수 | 6 | 18 |
표현·길이·바이트 확인 실습
서버 인코딩이 UTF8인 환경에서 실행한다. U& 문자열 문법을 사용해 복사 과정의 정규화 여부와 무관하게 원하는 코드 포인트를 지정한다.
SHOW server_encoding;
WITH samples(label, value) AS (
VALUES
('NFC', U&'\AC00'),
('NFD', U&'\1100\1161')
)
SELECT label,
value,
char_length(value) AS character_count,
octet_length(value) AS byte_count,
encode(convert_to(value, 'UTF8'), 'hex') AS utf8_hex,
value IS NFC NORMALIZED AS is_nfc
FROM samples;| label | character_count | byte_count | utf8_hex | is_nfc |
|---|---|---|---|---|
| NFC | 1 | 3 | eab080 | true |
| NFD | 2 | 6 | e18480e185a1 | false |
char_length는 사용자 눈에 보이는 글자 덩어리인 grapheme cluster를 세는 함수가 아니다. 이 예에서는 코드 포인트 수를 반영한다. 부분 문자열 자르기나 길이 제한도 표시 길이와 달라질 수 있다.
normalize: 변환된 값을 반환
SELECT
normalize(U&'\1100\1161', NFC) AS normalized_text,
char_length(normalize(U&'\1100\1161', NFC)) AS chars,
octet_length(normalize(U&'\1100\1161', NFC)) AS bytes;예상 결과: 가, 1, 3.
SELECT normalize(U&'\AC00', NFD);반대 방향으로 분해할 수도 있다. normalize(text)는 형태를 생략하면 NFC를 사용한다. NFC·NFD 등의 인자는 문자열 리터럴이 아니라 문법상의 키워드다.
SELECT로 normalize를 호출해도 기존 컬럼이 자동 수정되지 않는다. 결과를 INSERT·UPDATE하거나 생성 컬럼으로 보관해야 저장값이 달라진다.
Collation: 원래 표현을 유지한 채 비교
SELECT U&'\AC00' COLLATE "C" = U&'\1100\1161' AS equal;결과는 false다. 이번에는 ICU 지원 환경에서 정준 동등성을 인정하는 Collation을 만든다.
CREATE COLLATION public.canonical_equal (
provider = icu,
locale = 'und',
deterministic = false
);
SELECT U&'\AC00' COLLATE public.canonical_equal
= U&'\1100\1161' AS equal;
SELECT char_length(
U&'\1100\1161' COLLATE public.canonical_equal
) AS chars;첫 결과는 true, 두 번째는 2다. 비교는 같다고 했지만 NFD의 바이트를 NFC로 변환하지 않았다. 이 ICU 규칙은 대소문자 무시 설정과 같지 않으며, 정규화 형태 차이만을 엄격히 제거하는 전용 키가 필요하면 NFC 변환과 C 비교가 더 명확한 계약이 된다. ICU 동등성에는 다른 Unicode 규칙도 관여할 수 있다.
세 가지 비교를 한 번에 실행
WITH sample AS (
SELECT U&'\AC00' AS nfc,
U&'\1100\1161' AS nfd
)
SELECT
nfc COLLATE "C" = nfd AS raw_equal,
normalize(nfc, NFC) COLLATE "C"
= normalize(nfd, NFC) AS normalized_equal,
nfc COLLATE public.canonical_equal = nfd AS collation_equal
FROM sample;| raw_equal | normalized_equal | collation_equal |
|---|---|---|
| false | true | true |
| 비교 기준 | 정규화 | Collation |
|---|---|---|
| 문자열 바이트 | 변환 결과는 달라질 수 있음 | 원래 값 유지 |
| 길이 | 달라질 수 있음 | 그대로 |
| 원본 컬럼 자동 변경 | 아님 | 아님 |
| DB 밖으로 전달 | 변환 결과를 보내면 통일 | 원래 표현 전달 |
| 핵심 목적 | 표현 일관성 | 정렬·동등성 정책 |
한글 호환 자모와 NFKC 주의
| 종류 | 문자 | 코드 포인트 |
|---|---|---|
| 조합용 초성 | ᄀ | U+1100 |
| 조합용 중성 | ᅡ | U+1161 |
| 호환 자모 | ㄱ | U+3131 |
| 호환 자모 | ㅏ | U+314F |
가의 NFD는 U+1100·U+1161이다. 호환 자모 ㄱㅏ는 다른 문자열이며 NFC가 입력기처럼 항상 음절로 조합하지 않는다.
| 형태 | 정준/호환 분해 | 합성 |
|---|---|---|
| NFC | 정준 | 수행 |
| NFD | 정준 | 안 함 |
| NFKC | 호환 | 수행 |
| NFKD | 호환 | 안 함 |
NFKC는 전각 문자·동그라미 숫자 등 호환성 차이를 없앨 수 있다. 원문 의미와 표기 보존이 중요한 번역 데이터에 무조건 적용하지 않는다. NFC도 대소문자·공백·오타·초성 검색을 해결하지 않는다.
실무 설계: 원본과 비교 키 분리
파일 업로드에서는 눈에 같은 한글.pdf라도 표현이 다를 수 있다. DB의 ICU 비교가 같다고 판단해도 캐시 키·Object Storage 키·바이트 기반 해시는 여전히 다를 수 있다.
| 필드/영역 | 설계 예 |
|---|---|
| 원본 파일명 | 필요 시 입력 그대로 보존 |
| 비교용 파일명 | NFC로 정규화 |
| 실제 저장 키 | 서버가 발급한 ID 사용 |
| 표시용 정렬 | 요구 언어의 Collation 적용 |
| 중복 범위 | 사용자·폴더·프로젝트 등 업무 범위 명시 |
CREATE TABLE public.upload_demo (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
filename_raw text NOT NULL,
filename_nfc text COLLATE "C"
GENERATED ALWAYS AS (
normalize(filename_raw, NFC)
) STORED
);
INSERT INTO public.upload_demo(filename_raw)
VALUES (U&'\AC00.pdf'), (U&'\1100\1161.pdf');두 원본은 유지되지만 생성 컬럼은 같은 가.pdf가 된다. 이 테이블에는 파일명 UNIQUE를 만들지 않았다. 같은 이름 업로드를 허용할지 먼저 정해야 하기 때문이다.
정규화 조회용 인덱스
CREATE INDEX upload_filename_nfc_idx
ON public.upload_demo(filename_nfc);
-- $1은 애플리케이션에서 바인딩하는 파일명 파라미터
SELECT id, filename_raw
FROM public.upload_demo
WHERE filename_nfc = normalize($1::text, NFC) COLLATE "C";저장값과 입력값 모두 같은 규칙으로 통일해야 한다. 위 $1 SQL은 파라미터 바인딩 문맥용이므로 그대로 단독 실행할 때는 $1을 문자열 리터럴로 바꾼다.
대안 비교
| 방식 | 장점 | 비용·주의점 |
|---|---|---|
| 입력 시 NFC 저장 | 전체 시스템의 표현 계약을 통일하기 쉬움 | 원본이 필요하면 별도 보관; 모든 입력 경로 적용 |
| 원본+생성 컬럼 | 원본 보존, 조회용 키 명시 | 추가 저장 공간과 계산 비용 |
| normalize 표현식 인덱스 | 별도 사용자 컬럼 없이 검색 가속 | 쿼리 표현식·Collation이 인덱스와 맞아야 함 |
| ICU 비결정적 Collation | DB 동등성 정책을 선언적으로 적용 | DB 밖 표현 통일 안 됨; 비교 비용·B-tree dedup 제한 |
기존 데이터 점검과 변경 순서
읽기 전용 점검 예제다.
-- NFC가 아닌 원본 찾기
SELECT id, filename_raw
FROM public.upload_demo
WHERE filename_raw IS NOT NFC NORMALIZED;
-- NFC 기준으로 같은 값이 되는 그룹 찾기
SELECT normalize(filename_raw, NFC) COLLATE "C" AS normalized_name,
count(*) AS row_count,
array_agg(id ORDER BY id) AS ids
FROM public.upload_demo
GROUP BY normalize(filename_raw, NFC) COLLATE "C"
HAVING count(*) > 1;실제 서비스에서는 tenant_id·folder_id 등 중복 판단 범위를 GROUP BY에 포함한다. 대규모 테이블은 array_agg 결과와 스캔 비용을 고려해 집계 범위를 제한한다.
안전한 반영 순서:
- 원문 보존 여부, NFC/NFKC, 중복 범위, 대소문자 정책을 결정한다.
- 비정규화 행과 새 규칙에서 충돌하는 그룹을 조사한다.
- 중복 병합·이름 변경·별도 보존 정책을 확정한다.
- 입력 경로와 조회 경로에 동일한 규칙을 반영한다.
- 기존 데이터는 배치 처리하고 필요한 인덱스·제약을 적용한다.
- 샘플과 실제 조회를 검증하고 이후 비정규화 유입을 감시한다.
일괄 UPDATE는 데이터 양에 따라 잠금·WAL·복제 지연·dead tuple 증가를 유발할 수 있다. 정규화 이후 UNIQUE 충돌 여부를 확인하기 전에 기존 데이터를 무작정 덮어쓰지 않는다.
복습
- NFC와 NFD의 가는 모두 UTF-8로 저장 가능한가? 가능하다.
- ICU 비교가 true면 바이트도 같은가? 아니다.
- SELECT normalize가 원본을 수정하는가? 아니다.
- NFC 정규화가 한글 초성 검색을 구현하는가? 아니다.
- DB Collation이 Object Storage 키의 의미를 바꾸는가? 아니다.
- NFC 키에 UNIQUE를 추가하기 전에 무엇을 확인하는가? 업무 범위 내 정규화 충돌과 중복 처리 정책.
자료 기준과 참고 문서
개인 PostgreSQL 학습 노트를 바탕으로 정리했다. 버전이나 설정에 따른 조건은 본문에 덧붙였다.
