Share
Embed
Text encoding débâcles
(Text encoding débâcles)
A talk given at pgconf.dev 2026 in Vancouver.
https://2026.pgconf.dev/session/572
https://www.youtube.com/watch?v=hgPddKXOP40
Thomas Munro
July 15, 2026
Transcript
27 slides
-
pgconf.dev 2026 | Thomas Munro Text encoding débâcles Fixing and
improving text handling in PostgreSQL [email protected] [email protected] -
Text encoding débâcles pgconf.dev 2026 | Thomas Munro Fixing and
improving text handling in PostgreSQL [email protected] [email protected] -
template0 [UTF8] template1 [UTF8] postgres [UTF8] $ initdb -D pgdata
Default locale comes from environment and implies the encoding. In practice this means -E UTF8 on Unixen*, in modern times. Default template Special template containing no non- ASCII text *On Windows there are complications and un fi nished business around UTF8… -
template0 [UTF8] template1 [UTF8] postgres [UTF8] mydb1 [UTF8] CREATE DATABASE
mydb1 New block-level copy -
template0 [UTF8] template1 [UTF8] postgres [UTF8] mydb1 [UTF8] CREATE DATABASE
mydb2 TEMPLATE template0 ENCODING LATIN1 LOCALE 'en_CA.ISO8859-1' mydb2 [LATIN1] Special template containing no non- ASCII text -
mydb1 [UTF8] mydb1=# create user frédéric; CREATE ROLE mydb2=# select
rolname from pg_authid where rolname like 'f%'; rolname ------------- frédéric (1 row) mydb2 [LATIN1] Mojibake! A “shared” pg_catalog table. Encoding is in the eye of the beholder… -
* If databases all use the same encoding, it’s OK
to use non-ASCII role names and databases names [etc] * Otherwise, it’s not OK! * Can we enforce that? 💡 First attempt posted to list a couple of years ago… -
initdb --encoding-mode=uniform initdb --encoding-mode=mixed initdb --encoding-mode=legacy * All databases must
have the same encoding! * Role names, database names etc can use that encoding. * Default option. * Encodings can be mixed. * Role names, database names etc must use ASCII. * Anything goes? * For a limited number of releases? M ore recent prototype… -
$ initdb --encoding-mode=uniform -D pgdata postgres# CREATE DATABASE xxx TEMPLATE
template0 LOCALE 'C' ENCODING LATIN1; ERROR: encoding "LATIN1" does not match existing databases and encoding mode is "UNIFORM" -
$ initdb --encoding-mode=mixed -D pgdata postgres# CREATE USER astérix; ERROR:
the string "astérix" contains invalid characters for encoding mode "MIXED" -
* pg_stat_activity * GUCs * various fi les * early
authentication * … Other unde fi ned/confused encodings -
* Verify all applicable strings to change mode, report obstructions
with suggestions to unblock * Requires WAL logging and coarse locking * Early version had special ALTER SYSTEM command but now considering a function… Changed your mind? -
* Atomically choose UNIFORM, MIXED, LEGACY based on contents of
source cluster pg_upgrade -
* An encoding of Unicode * Encodes Chinese characters in
2 bytes instead of UTF8’s 3 * Unlikely to be supported by PostgreSQL (server-side) GB18030 People ask but we can’t easily do it… -
* Individual VARCHAR columns can use different encodings (cf COLLATE)
* In some implementations implied by COLLATE instead * Probably less relevant in the Unicode age…? SQL standard: CHARACTER SET Not gonna happen… -
* Like VARCHAR but with an unspeci fi ed special
encoding * In most implementations it is UTF-16 SQL standard: NVARCHAR (NATIONAL …) -
language | octets | delta | string ----------+---------+-------+------------------------------------------------- English |
45→90 | +100% | In a hole in the ground there lived a hobbit. Spanish | 44→86 | +95% | En un agujero en el suelo, vivía un hobbit. Russian | 59→66 | +12% | В норе под землей жил-был хоббит. Arabic | 57→64 | +12% | ضرﻷا ﻲﻓ ةﺮﻔﺣ ﻲﻓ ﺖﯿﺑﻮھ ﺶﯿﻌﯾ نﺎﻛ. Hebrew | 43→48 | +12% | טיבוה יח המדאב רוח ךותב. Greek | 74→82 | +11% | Σε µια τρύπα στο έδαφος ζούσε ένα χόµπιτ. Korean | 55→46 | -16% | ٶࣘ যו ҳݨী ೠ ഐࡁ Ҋ . Hindi | 111→86 | -23% | जमीन मे ं बने एक ग ड ् ढ े मे ं एक हॉ बि ट रहता था। Tamil | 146→108 | -26% | அ ந் த நி ல த் தி ல் ஒ ரு து ைள யி ல் ஒ ரு ஹா பி ட் வ சி த் து வ ந் த து . Chinese | 51→34 | -33% | 在地下 一 个洞 里 ,住着 一 个霍 比 特 人 。 Japanese | 66→44 | -33% | 穴 ͷͳ͔ʹ、ͻͱΓͷϗϏοτ͕暮Β͍ͯͨ͠。 Experimental prototype abandoned! Why would anyone want UTF-16? { * There was a time before UTF-8… * It’s “native” for several environments * It’s smaller for some languages -
Unicode compression standards? * SCSU compresses UTF-8 * BOCU-1 also
maintains binary code point order * Hindi: 3→~1 * CJK: 3->~2 * Greek, Russian: 2→~1 Just an idea… -
* A multi-byte aware iterator API (if done right…) could
avoid mistakes in common patterns as seen in previous CVEs Security and maintenance angle -
* We have loops that dispatch via function pointers once
per character! * Generate inlined specializations of string-processing functions for each encoding? Performance angle