skip to content

Collations & Encoding

PostgreSQL compares text through libc, ICU or builtin collation providers over a server encoding. Interviewers probe the collation-version mismatch after an OS upgrade and case-insensitive matching.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL 18, how do the libc, ICU and builtin collation providers differ, and which would you pick for a new UTF8 cluster?

level: middleimportance: must knowfreq 50%

answer

  1. who supplies the rules
  2. OS library, ICU library, PostgreSQL itself
  3. code point order never changes
  4. C.UTF-8 in 17, PG_UNICODE_FAST in 18

basics

~20 s

libc borrows the OS C library's locales; ICU uses the ICU library and allows tailored and nondeterministic collations; builtin (17+) sorts by code point with no external library. A sound pick: builtin default, ICU on columns needing linguistic order.

solid answer

~50 s

PostgreSQL asks a **locale provider** how to compare and classify text. `libc` uses the operating system's C library locales such as `en_US.UTF-8`; it is still `initdb`'s default, but one locale name can sort differently across platforms and C library releases. `icu` uses the ICU library: rules are the same on every OS for a given ICU version, locales can be tailored (`de-u-co-phonebk`, or `en-US-u-kn` for numeric ordering), and only ICU can back a nondeterministic, case- or accent-insensitive collation. `builtin`, added in 17, needs no external library: `C`, `C.UTF-8` and, new in 18, `PG_UNICODE_FAST` sort by code point, so their order never changes, but it is not dictionary order. For a new UTF8 cluster I would take a builtin default (`C.UTF-8`, or `PG_UNICODE_FAST` for full Unicode case mapping) and put ICU collations on the columns users read in sorted order.

code

bash · 4 lines
bash
initdb -D /var/lib/postgresql/18/main \
  --encoding=UTF8 \
  --locale-provider=builtin \
  --builtin-locale=C.UTF-8

go deeper

for a junior

Recall the three provider names, that libc follows the operating system and that builtin sorts by code point rather than in dictionary order.

for a middle

Explain where each provider gets its rules, which one can be nondeterministic, and what C.UTF-8 and PG_UNICODE_FAST do differently for case mapping.

for a senior

Justify a provider choice by upgrade risk: which collations can change order under an OS or ICU upgrade, and how a builtin default plus ICU columns shrinks that exposure.

for a principal

Set a fleet-wide locale policy: standard initdb flags, an approved list of ICU collations, and how library versions stay aligned across primaries, standbys and restore hosts.

## What a locale provider supplies A **collation** decides how two strings compare: their order for `ORDER BY`, `<` and B-tree indexes, and whether they are equal. Alongside it, **character classification** (ctype) decides what `upper()`, `lower()`, `initcap()` and regular-expression classes such as `[[:alpha:]]` do. PostgreSQL does not implement language rules itself; it delegates to a **locale provider**. A provider can be chosen at four levels, each overriding the one above: 1. `initdb --locale-provider=...` sets the default for the template databases. 2. `CREATE DATABASE ... LOCALE_PROVIDER = ...` sets one database's default collation. 3. A column declared `text COLLATE "..."` uses a collation object, which names its own provider. 4. An expression such as `ORDER BY headword COLLATE "sv-x-icu"` overrides everything for one query. `pg_collation.collprovider` records the provider as `c` (libc), `i` (ICU), `b` (builtin) or `d` (the database-default entry). ## The three providers side by side | | `libc` | `icu` | `builtin` | |---|---|---|---| | Rules come from | the OS C library | the ICU library PostgreSQL is linked with | PostgreSQL itself | | Sort order | linguistic, per locale | linguistic, tailorable | code point (`C`: bytes) | | Same on every OS? | no: one locale name can behave differently per platform | yes, for a given ICU version | yes | | Order can change when | the C library's locale data changes | the ICU library is upgraded | never | | Nondeterministic collations? | no | yes | no | | Chosen at initdb by | default | `--locale-provider=icu --icu-locale=...` | `--locale-provider=builtin --builtin-locale=...` | For libc, PostgreSQL can only detect a change where the platform reports a version: glibc, FreeBSD and Windows. ICU always reports one. ## The builtin locales - **`C`**: byte-order comparison and ASCII-only case mapping, identical to libc's `C`; any encoding. - **`C.UTF-8`** (PostgreSQL 17+, predefined collation `pg_c_utf8`): `UTF8` only; code-point order; Unicode *simple* case mapping; POSIX Compatible regex classes. - **`PG_UNICODE_FAST`** (new in 18, predefined collation `pg_unicode_fast`): `UTF8` only; code-point order; Unicode *full* case mapping, so one character can map to several (`casefold()` turns `ß` into `ss`); Standard regex classes. Their recorded collation version is `1` and tracks only sort order, which is plain code-point comparison. Case mapping and character classes come from PostgreSQL's own Unicode tables, which are stable within a major version and can be refreshed by a new one (18 moved to Unicode 16.0.0). ## Choosing for a new UTF8 cluster A pattern that limits upgrade risk: 1. Initialize with the builtin provider, `initdb --encoding=UTF8 --locale-provider=builtin --builtin-locale=C.UTF-8`, or `PG_UNICODE_FAST` on 18 when full case mapping matters. Keys, codes, slugs and other machine strings then sort fast and never change order under an OS upgrade. 2. Give the columns people read in sorted order an explicit ICU collation (`headword text COLLATE "de-x-icu"`), and use a nondeterministic ICU collation where matching must ignore case. 3. Keep libc only where an application depends on exactly the OS locale's behaviour, and then keep the same C library on every host, standbys included. The cost of step 1 is visible: `ORDER BY` under a builtin collation puts `Zebra` before `apple`, and `éclair` after both. That is right for identifiers and wrong for a dictionary page, which is why the human-facing columns get an ICU collation. ## Practical details - With `--locale-provider=icu`, `initdb` still initializes libc `LC_COLLATE` and `LC_CTYPE` for legacy reasons; the ICU locale comes from `--icu-locale`, for example `und` (the root collation) or `de`. - ICU collation objects are named by BCP 47 tag plus `-x-icu` (`de-x-icu`, `und-x-icu`); tailorings use `-u-` keys such as `en-US-u-kn`, which sorts `id-45` before `id-123`. - A libc locale must match the database encoding; ICU works with most server encodings; `C.UTF-8` and `PG_UNICODE_FAST` require `UTF8`. - A database's provider is fixed when it is created; changing it means a new database and a reload. ## Checking an existing cluster Before choosing anything, look at what is already there. `SELECT datname, datlocprovider, datcollate, datlocale, datcollversion FROM pg_database;` shows each database's provider (`b`, `c` or `i`), its libc locale, its builtin or ICU locale and the recorded collation version; psql's `\l` shows the same as a Locale Provider column. A cluster created years ago with plain `initdb` is almost certainly libc, which means every text index in it depends on the C library of whatever host it runs on.

  • What is the practical difference between the builtin C.UTF-8 and PG_UNICODE_FAST locales?
    Both require `UTF8` and sort by code point. `C.UTF-8` (17+) uses Unicode simple case mapping and POSIX Compatible regex classes. `PG_UNICODE_FAST` (18+) uses full case mapping, so one character can map to several (`casefold()` turns `ß` into `ss`), and Standard regex classes. Pick `PG_UNICODE_FAST` when caseless matching must follow Unicode exactly.
  • Why is an ICU collation still exposed to upgrades when it does not use the OS locale files?
    ICU results depend on the ICU library version PostgreSQL is linked with, and ICU updates its collation data as languages change. A new package or build can bring a newer ICU, and then `pg_collation.collversion` disagrees with `pg_collation_actual_version()`. The remedy matches libc: rebuild dependent indexes, then refresh the recorded version.

  • The builtin provider sorts words in dictionary order for every language.
  • ICU collations never change, so they never need a reindex.
  • Modern initdb uses the ICU provider by default.
  • A case-insensitive nondeterministic collation can be built on a libc locale.
  • A libc locale name like en_US.UTF-8 sorts identically on every platform.

Assumes PostgreSQL 18: the builtin provider arrived in 17 with C and C.UTF-8, and PG_UNICODE_FAST is new in 18.

open as a page

After an OS upgrade on a PostgreSQL host, a dictionary app's headwords sort differently and logs warn of a collation version mismatch; what happened, and how do you fix it safely?

level: seniorimportance: must knowfreq 55%

basics

~20 s

The upgrade brought a C library that orders some strings differently, so B-tree indexes built under the old order are now wrong. Rebuild every index depending on the collation, resolve duplicate keys, then run ALTER DATABASE ... REFRESH COLLATION VERSION.

open as a page

In PostgreSQL, how do a database's server encoding and a session's client_encoding differ, and what happens to bytes that do not fit?

level: juniorimportance: should knowfreq 40%

basics

~20 s

The server encoding, normally UTF8, is fixed per database at creation; client_encoding is a per-session setting the server converts to and from. Invalid input bytes fail with SQLSTATE 22021, and characters the other encoding cannot hold fail with 22P05.

open as a page

In PostgreSQL, how does a COLLATE clause decide which collation a comparison or ORDER BY uses, and when can an index no longer serve it?

level: middleimportance: should knowfreq 30%

basics

~20 s

An explicit COLLATE beats a column's implicit collation, which beats the default; two different implicit collations in one comparison fail with SQLSTATE 42P22. Each index column has one collation, so a query asking for another cannot use that index.

open as a page

In PostgreSQL 18, how would you make dictionary headword lookups case-insensitive (a lower() index, citext, a nondeterministic ICU collation or casefold()) and what does each cost?

level: seniorimportance: should knowfreq 45%

basics

~20 s

A nondeterministic ICU collation such as und-u-ks-level2 makes equality, UNIQUE and, from 18, LIKE case-insensitive per column; citext lowercases under the database ctype; lower() or 18's casefold() expression indexes keep plain text but need matching queries.

open as a page