Road to a Cosmopolitan Database System:
The History of internationalization ofPostgreSQLSRA OSS, Inc. Japan
Tatsuo Ishii

Who am I?
●●●

Working for SRA OSS, Inc. Japan, an OSS
service company
One of the committers of the PostgreSQL
project
Developing PostgreSQL related OSS●

Pgpool-II(http://pgfoundry.org/projects/pgpool/)Copyright(c)2010 Tatsuo Ishii

3

The early days of PostgreSQL●PostgreSQL 6.0, the first version, was bornin 1996●

No consideration for character sets except ASCII●

Fortunately the code accepted 8-bit-set bytes●Most database operations are ok with Japanese Kanjicharacters as long as using EUC-JP●●

Data insertion, table names...

Except the regular expression query

Copyright(c)2010 Tatsuo Ishii

4What is the regular expression queryin database anyway?
●●●“Grep against database contents”●

One of the powerfull features in PostgreSQL●SELECT * FROM foo WHERE bar ~ '^some.*';Standard DBMS have just “LIKE” which ispoor compairing with regular expression●

SELECT * FROM foo WHERE bar LIKE 'some%';●Only '%' and '_' are allowed as wildcard

Afterwards(1999) regular expression wasdefined as a part of the SQL standard
Copyright(c)2010 Tatsuo Ishii

5

The implemetation●●Orginal code used Henry Spenser's regexp code.
Modified it to allow wide characters
Allowed character set is defined at the compile time●

EUC_JP(1-3 bytes): Japanese Extended UNIX code●

EUC_CN(1-3 bytes): Chinese Extended UNIX code●

EUC_KR(1-2 bytes): Korean Extended UNIX code●

EUC_TW(1-4 bytes): Taiwan Extended UNIX code

●UNICODE(UCS-2)●

Mule Internal: Multilingual editor used this code

Copyright(c)2010 Tatsuo Ishii6Social aspect of the patches
●●

●

●

Most European/US commnuity memebers had
no knowledge about I18N
Need to explain what is “Kanji” and why
Japanese need it over and over again
The modification was carefully made not to
be too “invasive” to the original code
Do not make “Japanese only patch”. Key
factor of the acceptance is benefit for allover the world
Copyright(c)2010 Tatsuo Ishii

7

Anyway...
●●

The patch was accepted and released with
PostgreSQL 6.3.1 in 1998
Next year I became a committer ofPostgreSQL

Copyright(c)2010 Tatsuo Ishii

8Toward true 18N●

After the first multibyte patches publishedseveral concerns were remained:
●

●

●

●

Support multiple characters sets, at least per
database“Neutral” to character set. No particularcharacter set should not be “primary” or“dominant”
Transparent character set conversion between
client and DB server
Performance is the top priority as usual
Copyright(c)2010 Tatsuo Ishii

9Support multiple characters sets●●●

Allow to choose
character sets whencreating “database”
Database is chosenwhen a session isstarted
All tables in a
database have samecharacter set
Copyright(c)2010 Tatsuo Ishii

Database cluster
Database
SchemaTableRows, columns

10Character set and Encoding●

Character set != Encoding●A character set could have multiple encoding●●●An encoding could have multiple character
sets
Encoding defines physical representation ofparticular character set thus precisely
specifies character set
PostgreSQL specifies encoding rather thancharacter set
Copyright(c)2010 Tatsuo Ishii

11

Avoid Unicode as an internal
encoding●Many OSS use Unicode as their internal
encoding (intermediate representation) tomake it simple. But this has several
drawbacks:●●Conversion from/to other encoding is slow
because it needs to look up huge conversion
tables
It's not a “loss less” conversion. Some characters
might not be represented in Unicode
Copyright(c)2010 Tatsuo Ishii

12What do we do then?
●●●●Internalencoding

Avoid “internal encoding”
Have functions for all
conversion pairs
Create an abstraction
layer to provide necessary
operations for handling
strings

Abstract interface
Function pointer dispatch table
Modules
For UTF-8

Modules
For EUC_TWModules
For EUC_CN

Copyright(c)2010 Tatsuo Ishii

Modules
For EUC_KR

Modules
For EUC_JP

Conversion module
dynamically loaded using
PostgreSQL's “functionmanager”

String handling in db engine

13

Use case of encoding conversion
between client and DB server●●A DB is shared bymultipleclients(applications)
They are possibly
run on different
platform, thus could
use different
encoding

Shift JIS client
On Windows
Unicode client
On Linux

UTF-8
Datasbe

EUC-JP client
On UNIX

Copyright(c)2010 Tatsuo Ishii

14

Encoding conversion between clientand DB server
DB applicationsDB engine

Language depend
APIsCommunication
moduleTCP/IP

Doing conversion in API•Need to modify existing API•Some OS lacks the capability
to do encoding conversion

Doing conversion in DB server•No need to modify existing API•Any platform can enjoy theEncoding conversion

Copyright(c)2010 Tatsuo Ishii

15

The encoding conversion
architecture
DB client

QueryResult

Communication
moduleEUC-JP/UTF-8
Shift-JIS/UTF-8

Syntactical analysis
Semantics analysis

Query executor

Query optimizer

Copyright(c)2010 Tatsuo Ishii

Dynamic load

Communication
moduleEUC-KR/UTF-8

Encoding conversion
functions16Encoding conversion summary●●●Encoding conversion module could be big because ofhuge conversion tables. To avoid the process size
growing, each module is loaded dynamically only
when conversion needed
If client and DB encoding is identical, no conversion
occursEasy to add new conversion module. 132 encoding
conversion functions are registered so far

Copyright(c)2010 Tatsuo Ishii

17

PostgreSQL 7.3 become
I18N database(2002)●●

After spending 5 years, finally PostgreSQL
became I18N database
Why 5 years?
●Community members need time to understand thenecessity of I18N●

Lot's of technical challenges●I myself was lazy:-)

Copyright(c)2010 Tatsuo Ishii

18

Next big progress – Full text search●

What is full text search in PostgreSQL?
●

Fast and scalable full text search in RDBMS●Up to several TB database possible●

Full ACID complaint●

Can be treated as ordinary data types●●

Can do search while updating database(concurrent index
updating)
Small index size comparing with N-gram full text searchsystem●Can have multiple language dictionaries●

Created by Russian programers in 2006 (PostgreSQL 8.2)
Copyright(c)2010 Tatsuo Ishii

19

Using full text search with Japanese●

Full text search assumes that each word in a
sentence is separated by space. Unfortunately thisis not the case for Japanese.
●●

Using “mecab” (a OSS morphological analysisprogram) we can convert it to:●●

今日は良い天気です(Today is fine)

今日 は 良い 天気 です

There is a Japanese parser which can do thisautomatically(textsearch-ja)
Copyright(c)2010 Tatsuo Ishii

20

Locale, locale...
●Why locale affects database?
●●●●The sort order. Example:●

locale=C: ABCabc...

●

Locale=en_US: aAbBcC...

Some characters are identified to be equal incertain locale while they are not in other locale
Important for European languages

How the the standard defines the locale handling●It defines “COLLATE” clause, which is close toLC_CTYPE and LC_COLLATE
Copyright(c)2010 Tatsuo Ishii

21

What is the current status of locale
handling?
●●●

PostgreSQL 8.4(2009) introduced per
database locale
PostgreSQL 9.0(2010) allows to use caseinsensitive matching in regexp with UTF-8
Per column COLLATE clause patches areproposed for next PostgreSQL version

Copyright(c)2010 Tatsuo Ishii

22

Summary●●●●PostgreSQL started with English onlydatabase
Non English speakers needed multibyte
character sets and added themIt evolved into I18N database management
system without sacrificing performance
which is the highest priority in database
Implementing more flexible locale/collation
handling is a future plan
Copyright(c)2010 Tatsuo Ishii

23

Copyright(c)2010 Tatsuo Ishii

24