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

Transcript

1. pgconf.dev 2026 | Thomas Munro Text encoding débâcles Fixing and improving text handling in PostgreSQL tmunro@postgresql.org thomas.munro@microsoft.com
2. Text encoding débâcles pgconf.dev 2026 | Thomas Munro Fixing and improving text handling in PostgreSQL tmunro@postgresql.org thomas.munro@microsoft.com
3. Poll: who is using an encoding other than UTF8?
4. Poll: who is using databases with different encodings?
5. Shared catalogues vs mixed encodings
6. 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…
7. template0 [UTF8] template1 [UTF8] postgres [UTF8] mydb1 [UTF8] CREATE DATABASE mydb1 New block-level copy
8. 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
9. 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…
10. * Bad user experience * Attack vector for security vulnerabilities
11. * 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…
12. 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…
13. $ 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"
14. $ initdb --encoding-mode=mixed -D pgdata postgres# CREATE USER astérix; ERROR: the string "astérix" contains invalid characters for encoding mode "MIXED"
15. * pg_stat_activity * GUCs * various fi les * early authentication * … Other unde fi ned/confused encodings
16. * 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?
17. * Atomically choose UNIFORM, MIXED, LEGACY based on contents of source cluster pg_upgrade
18. PostgreSQL vs other systems and standards
19. * 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…
20. * 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…
21. * Like VARCHAR but with an unspeci fi ed special encoding * In most implementations it is UTF-16 SQL standard: NVARCHAR (NATIONAL …)
22. 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
23. 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…
24. Improve the abstraction level?
25. * A multi-byte aware iterator API (if done right…) could avoid mistakes in common patterns as seen in previous CVEs Security and maintenance angle
26. * We have loops that dispatch via function pointers once per character! * Generate inlined specializations of string-processing functions for each encoding? Performance angle
27. EOF