한글과 LC_COLLATE
아주 지루한 이야기를 시작합니다.1. 문자세트
% locale
LANG="ko_KR.CP949"
LC_COLLATE="ko_KR.CP949"
LC_CTYPE="ko_KR.CP949"
LC_MESSAGES="ko_KR.CP949"
LC_MONETARY="ko_KR.CP949"
LC_NUMERIC="ko_KR.CP949"
LC_TIME="ko_KR.CP949"
LC_ALL=
% /pgsql/16/bin/initdb -D cp949-cluster
데이터베이스 클러스터는 "ko_KR.CP949" 로케일으로 초기화될 것입니다.
initdb: 오류: "ko_KR.CP949" 로케일은 지원하지 않는 "UHC" 인코딩을 필요로 함
initdb: 상세정보: "UHC" 인코딩을 서버측 인코딩으로 사용할 수 없습니다.
initdb: 힌트: 다른 로케일을 지정해서 initdb 작업을 다시 하세요.
OS 언어환경이 확장 완성형 (cp949, uhc) 인 경우는 로케일 관련 옵션 없이는 PostgreSQL 데이터베이스를 초기화 할 수 없습니다.
% export LANG=ko_KR.eucKR
% /pgsql/16/bin/initdb -D euckr-cluster
데이터베이스 클러스터는 "ko_KR.eucKR" 로케일으로 초기화될 것입니다.
기본 데이터베이스 인코딩은 "EUC_KR" 인코딩으로 설정되었습니다.
데이터베이스 클러스터가 잘 만들어졌습니다.
이제 데이터베이스 인스턴스를 만들겠습니다.
% /pgsql/16/bin/pg_ctl -D euckr-cluster start
서버 시작됨
잘 시작되었습니다.
이제 접속해서 한글 관련 테스트를 하겠습니다.
postgres=# select '툝롲';
오류: "EUC_KR" 인코딩에서 사용할 수 없는 문자가 있음: 0xb8 0x94
아무런 사전 조치를 하지 않은 euc-kr 환경에서는 그냥 데이터베이스를 만들면,
euc-kr 문자세트에 포함되지 않은 문자를 처리할 수 없습니다.
PostgreSQL은 철저하게 예외를 두지 않습니다.
그래서, 해당 문자세트에 포함되지 않은 문자를 처리하려고 할 때는 원천 차단됩니다.
자료를 저장하고자 한다면 저장 자체가 안됩니다.
지금까지 결론
모든 한글을 담을 수 있는 locale은 ko_KR.eucKR, ko_KR.CP949 로 모국어 환경으로는 안된다.
결국 ko_KR.UTF-8 설정을 합니다.
% locale -a | grep ko_KR
ko_KR
ko_KR.eucKR
ko_KR.UTF-8
ko_KR.CP949
% export LANG=ko_KR.UTF-8
% date
2023년 11월 5일 일요일 01시 51분 50초 KST
% /pgsql/16/bin/initdb -D utf8-cluster
% /pgsql/16/bin/pg_ctl -D utf8-cluster start
서버를 시작하기 위해 기다리는 중.... 완료
서버 시작됨
postgres=# select '툝롲';;
?column?
----------
툝롲
(1개 행)
이제 잘 됩니다.
postgres=# show server_encoding ;
server_encoding
-----------------
UTF8
(1개 행)
postgres=# show client_encoding ;
client_encoding
-----------------
UTF8
(1개 행)
결국 utf8 문자세트 환경이어야 한글 모든 글자를 처리할 수 있습니다.
2. LC_COLLATE
이렇게 만들어진 데이터베이스의 문자 관련 설정은 다음과 같습니다.
postgres=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | 로케일 제공자 | Collate | Ctype
-----------+--------+--------+---------------+------------
postgres | ioseph | UTF8 | libc | ko_KR.UTF-8 | ko_KR.UTF-8
collate 관련 설정을 특별히 지정하지 않으면, LANG 환경설정을 따르게됩니다.
이것 기반으로 데이터베이스를 만들때도 그대로 사용되어 데이터베이스의 Collate값은 ko_KR.UTF-8 로 지정됩니다.
OS LC_COLLATE 환경 설정 변수는 문자열 정렬과 관계됩니다.
'가' 가 '나'보다 앞에 있다는 것에 대한 정의입니다.
간단하게 생각하면 그냥 각 문자의 코드를 순서대로 하고, 그냥 그 코드 값을 따르면 될 것 처럼 보이지만, 실세계에서는 그리 간단한 문제가 아닙니다.
'abcde' 인경우에, 'à', 'ã' 문자를 사용하는 나라에서는 각 고유의 순서를 임의로 정합니다.
이런식으로 libc 로케일 제공자가 정의한 utf8 문자셋을 사용하는 ko_KR (Korean_Korea) 문자 정렬은 그 libc 제작 주체에 따라 달라집니다. Microsoft, Apple, GNU, .....
한글, 간체, 히라가나로만 확인해보면,
GNU: 간체, 히라가나, 한글
Microsoft : 히라가나, 간체, 한글
Apple: 간체, 한글, 히라가나
이런식으로 제각각입니다.
전세계 다양한 문자들을 다 고려하고, 각 문자들을 사용하는 나라들의 환경을 고려하면, 더 복잡해집니다.
거기에, 각종 다양한 프로그래밍 언어까지 더해지면, 완전 엉망이 되어버리죠. 그래서,
모든 문자에 대한 정렬에 관련한 표준이 필요해집니다. 그래서 등장한 것이
ICU - International Components for Unicode
이 ICU 이야기는 그 자체만으로도 꽤 긴 이야기가 되기 때문에, 이 글에서는 다루지 않습니다.
3. 한글을 사용하는 칼럼의 인덱스
mydb=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | 로케일 제공자 | Collate | Ctype
-----------+----------+--------+---------------+-----------
mydb | postgres | UTF8 | libc | ko_KR.utf8 | ko_KR.utf8
mydb=# create table koword (a text);
CREATE TABLE
mydb=# \copy koword from 'kodic.txt'
COPY 5966
mydb=# create index koword_a_i on koword (a);
CREATE INDEX
mydb=# explain select a from koword where a like '사랑%';
QUERY PLAN
--------------------------------------------------------
Seq Scan on koword (cost=0.00..105.58 rows=1 width=9)
Filter: (a ~~ '사랑%'::text)
(2개 행)
인덱스를 사용하지 않습니다.
여느 다른 관계형 데이터베이스에서 이렇지 않는데 말이죠.
PostgreSQL에서는 독특한 규칙 하나가 있습니다.
그 데이터베이스의 문자 정렬 규칙이 C 가 아닌 경우, like 연산에서 인덱스를 사용하려면,
그 인덱스를 만들 때 like 연산이 가능하도록 연산자 클래스를 지정해 주어야 합니다.
mydb=# create index koword_a_i2 on koword (a text_pattern_ops);
CREATE INDEX
mydb=# explain select a from koword where a like '사랑%';
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using koword_a_i2 on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: ((a ~>=~ '사랑'::text) AND (a ~<~ '사랒'::text))
Filter: (a ~~ '사랑%'::text)
(3개 행)
이렇게 연산자 클래스를 지정하지 않은 인덱스를 사용해서 자료를 탐색하려면,
범위 연산만 허용합니다.
mydb=# explain select a from koword where a >= '사랑' and a < '사랒';
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using koword_a_i on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: ((a >= '사랑'::text) AND (a < '사랒'::text))
(2개 행)
mydb=# explain select a from koword where a between '사랑' and '사랑힣';
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using koword_a_i on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: ((a >= '사랑'::text) AND (a <= '사랑힣'::text))
(2개 행)
이 두가지 검색방법 - 범위연산, like 연산 모두 같은 인덱스를 사용해서 탐색할 수 있는 방법은
1. 데이터베이스 collate 가 C 가 아닌 경우에는
인덱스를 만들 때 해당 칼럼의 정렬 규칙을 C로 임의로 지정하고,
탐색 쿼리에서 찾을 문자열 뒤에 그 정렬 규칙이 C라로 일일히 지정하는 방법입니다.
mydb=# drop index koword_a_i;
DROP INDEX
mydb=# drop index koword_a_i2;
DROP INDEX
mydb=# create index koword_a_i on koword (a collate "C");
CREATE INDEX
mydb=# explain select a from koword where a >= '사랑' collate "C" and a < '사랒' collate "C";
QUERY PLAN
------------------------------------------------------------------------------------
Index Only Scan using koword_a_i on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: ((a >= '사랑'::text COLLATE "C") AND (a < '사랒'::text COLLATE "C"))
(2개 행)
mydb=# explain select a from koword where a like '사랑' collate "C";
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using koword_a_i on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: (a = '사랑'::text)
Filter: (a ~~ '사랑'::text COLLATE "C")
(3개 행)
2. 다른 방법은 데이터베이스의 collate 값을 처음부터 C로 지정하는 방법입니다.
mydb=# \c postgres
접속정보: 데이터베이스="postgres", 사용자="postgres".
postgres=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | 로케일 제공자 | Collate | Ctype
-----------+----------+--------+---------------+-----------
postgres | postgres | UTF8 | libc | C | ko_KR.utf8
postgres=# create table koword (a text);
CREATE TABLE
postgres=# \copy koword from 'kodic.txt'
COPY 5966
postgres=# create index koword_a_i on koword (a);
CREATE INDEX
postgres=# explain select a from koword where a >= '사랑' and a < '사랒';
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using koword_a_i on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: ((a >= '사랑'::text) AND (a < '사랒'::text))
(2개 행)
postgres=# explain select a from koword where a like '사랑%';
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using koword_a_i on koword (cost=0.28..4.30 rows=1 width=9)
Index Cond: ((a >= '사랑'::text) AND (a < '사랒'::text))
Filter: (a ~~ '사랑%'::text)
(3개 행)
3. SQL_ASCII 문자세트
가끔, 나는 이런 것 신경 안쓰고 그냥 SQL_ASCII 문자세트로 지정해서 잘 쓰고 있는데,
SQL_ASCII를 사용하면, 한글 뿐만 아니라, 모든 문자들들 다 잘 처리할 수 있고, 인덱스도 아주 잘 사용할 수 있고, 게다가 문자열의 저장공간도 utf-8 문자세트를 쓰지 않아, 한글인 경우는 1바이트도 줄일 수 있어 아주 좋은데 왜 SQL_ASCII 문자세트 사용에 대해서는 말하지 않는가?
라는 질문을 받습니다.
문자세트의 시작은 문자를 저장할 때 '무엇을 문자라 할 것인가?' 에서 출발합니다.
'안녕하세요' 는 한글로 다섯글자입니다.
이게 가장 기본입니다.
SQL_ASCII 문자세트는 8비트 정보를 주고받기 위한 128개의 컴퓨터 기준의 문자세트일 뿐입니다. 그 안에는 한글도, 간체도, 히라가나도 고려하지 않았습니다.
뭐 알아서 잘 쓰겠다면, 뭐라 할 말은 없지만, 요즘처럼 전세계의 모든 문자를 처리해야하는 상황에서 이것을 고집하는 것은 위험한 것임에는 분명합니다.
4. 결론
여느 다른 관계형 데이터베이스에서처럼 between 연산이나, like 연산을 사용하는데 있어 문자열 칼럼을 사용하는 인덱스를 편하게 사용하려면,
PostgreSQL 데이터베이스의 lc_collate 값은 C 여야합니다.
문제는 initdb 작업부터 이것을 염두해 두지 않았다면,
사용할 데이터베이스를 만들 때 template 데이터베이스로 template0 를 사용해야합니다.
create database mydb template template0 lc_collate "C";
이런 귀찮음도 피하고자 한다면,
initdb 작업 전 그 명령을 실행할 OS 환경 설정이 문자세트는 utf8 로 문자정렬은 C로 하는 것이 정신건강에 좋습니다.
export LANG=ko_KR.UTF-8
export LC_COLLATE=C
이렇게 있으면 되겠죠.