Collations en PostgreSQL: libc, ICU y el proveedor builtin, y por qué una actualización de biblioteca puede corromper un índice

Qué es

La documentación define una collation como un objeto SQL que asocia un nombre con datos de configuración regional (locale) de un proveedor: libc (los locales del sistema operativo, ligados a la codificación de la base de datos), icu (la biblioteca ICU, con nombres en forma de etiquetas BCP 47 como de-x-icu, independiente de la codificación) o builtin (solo C, C.UTF-8 y PG_UNICODE_FAST, que ordenan por punto de código). Los valores LC_COLLATE y LC_CTYPE de la base de datos se fijan al crearla y solo se pueden cambiar creando una base de datos nueva. Una columna, un índice o una expresión pueden llevar su propia collation (COLLATE "de-x-icu"), y CREATE COLLATION permite definir collations propias, incluidas las no deterministas (deterministic = false) para comparaciones que ignoran mayúsculas o acentos.

Por qué importa

En primer lugar, el orden de un idioma no coincide con el orden por punto de código: ORDER BY name y las condiciones de rango sobre texto dependen de la collation, y un índice solo sirve para una ordenación si su collation coincide con la de esta. En segundo lugar, un B-tree almacena las claves en el orden de la collation; cuando la biblioteca C del sistema operativo o ICU modifica sus reglas, los índices existentes dejan de ser coherentes con la nueva comparación, y la documentación de ALTER COLLATION advierte de que esto puede dar lugar a índices corruptos. PostgreSQL registra la versión de la collation y emite una advertencia si no coincide; la solución es ejecutar REINDEX sobre los objetos afectados y después ALTER COLLATION ... REFRESH VERSION.

Cómo aplicarlo

• Elige la collation predeterminada al crear el clúster o la base de datos. C o la C.UTF-8 builtin son adecuadas para identificadores, claves y datos de máquina: la documentación describe C como estable en todas las versiones para una codificación dada, y C.UTF-8 como estable dentro de una misma versión mayor. Usa una collation de idioma solo donde los usuarios lean texto ordenado.
• Prefiere ICU a libc para las collations de idioma cuando ambas estén disponibles: el capítulo sobre locales describe el comportamiento de ICU como independiente del sistema operativo y de la codificación de la base de datos.
• Define el orden visible para el usuario en la columna o en la consulta (ORDER BY title COLLATE "de-x-icu") y crea un índice con la misma collation si esa ordenación debe ser rápida.
• Usa una collation no determinista para garantizar unicidad sin distinguir mayúsculas y minúsculas, siempre que resulten aceptables sus límites documentados (comparación más lenta, sin deduplicación en B-tree, algunas búsquedas por patrón no disponibles).
• Tras actualizar el sistema operativo, actualizar ICU o ejecutar pg_upgrade hacia bibliotecas más recientes, lista las discrepancias con SELECT collname FROM pg_collation WHERE collversion <> pg_collation_actual_version(oid), reindexa y después actualiza las versiones; la collation predeterminada de la base de datos se actualiza por separado con ALTER DATABASE ... REFRESH COLLATION VERSION.

Trampas

Una réplica construida a partir de otra imagen del sistema operativo puede tener una biblioteca C distinta y, por tanto, un orden distinto para el mismo índice. Con la collation C, solo las letras ASCII de la A a la Z cuentan como letras, de modo que upper() y lower() no modifican las demás letras. REFRESH VERSION registra la nueva versión sin comprobar que los objetos se hayan reconstruido.