Message-ID: <330ca171-623a-44ab-91a0-7dbf7520fb83@joeconway.com>
Date: Thu, 13 Mar 2025 09:14:46 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Moving from Linux to Linux?
To: Paul Foerster <paul.foerster@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>,
 Pgsql-General List <pgsql-general@lists.postgresql.org>
References: <6E6059F6-E5A1-4A8D-8A5E-A41B921BBE67@gmail.com>
 <E0EC05FB-783D-4A24-81B9-0C345A5EC45A@gmail.com>
 <9a8d92de-228c-43b0-b66c-c3390b41044a@aklaver.com>
 <B6135108-EA9F-4E75-80EE-1077D0909F00@gmail.com>
 <c60b0558-290b-4fe8-b839-6a7725c980b3@joeconway.com>
 <6C78D9E9-2EC3-4FBF-AC22-268DCE8F6A91@gmail.com>
Content-Language: en-US
From: Joe Conway <mail@joeconway.com>
Autocrypt: addr=mail@joeconway.com; keydata=
 xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l
 VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS
 sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14
 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE
 qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw
 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ
 ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo
 DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA
 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm
 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzSRKb3NlcGggRSBD
 b253YXkgPG1haWxAam9lY29ud2F5LmNvbT7CwXoEEwEIACQCGwMCHgECF4AFCwkIBwMFFQoJ
 CAsFFgIDAQAFAlWTVvUCGQEACgkQMyt+aLaZQ0oPCQ/9HyRewMyvAIJRmoXoLAr8AoFLId6R
 qBJnNX0Lll0RLZui65aQ0+exwX7aH7TxWR16B2gWX3OmLfGT8XITOoG+zt9zsEpLvNkHchkF
 T/jyAcbuRj5WX9hamZgMbjXAJeCdlhW+fRA9Upb0w4dgBjqK5OgsqMikASL7t2vogHl9H08j
 vSoQLW+8wTnSBXBeBTBwB7xLIin5WVivzFHUCrnD2UsjeBIW3fmGdpTAjSxRzG+UPYVwXQ8F
 FLt7DpEytvLWapmZWMRdj0WZ/Q3SOO/Ed0yFqbzuwKaWcFrQBNeS2Sig+FefBNS98f9Hx7ku
 H3DW34qX/zSSdDh0jLs7X3PkIgF6BZR2TxaCwHPP9ERDiDaUInC9U7We1iZE1DjW8rLMEVJB
 hY0ClrrF67pnUKTbcU+uajpPn+2Jl74T0Set/XxpHZ4cezcJuqg31R8vHZgd5cf1WKP0D0pc
 qiuS02BBFkNCs1jQ+raTWcDuE6F1mUO2nvjUBN9r4y5DUbCNSqLKeAe/aA6JaSDkBpoXKdNS
 +c4rbzbktWkfUW8EhVlCGzNpy4ezEoVsqV2Ex7fNoxsE2vnSylLT9hycAmYf8ryMvniRZqnD
 T4JgLenIcQlkhB896T7wApOXfD8OJj1/XFxAfPi6vdlsr81uoxuB4euLp8IyduwLORRUogO9
 zmAXG5jOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMva
 v/KENd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFV
 dWnlw7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5Vu
 gWNlPZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv
 5ZhPRpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc
 2OrWo7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQ
 bauDQw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT
 0S5yOgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4e
 NrxU0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA
 81uMj49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEI
 AAkFAkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4
 Fia379JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBG
 HwLEOTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZ
 E0C1xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6Xlnd
 FrDlY22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl
 0ZJP3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcH
 YebVJZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs
 5egE3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1
 c9JbPJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+
 1iJNEnp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q=
In-Reply-To: <6C78D9E9-2EC3-4FBF-AC22-268DCE8F6A91@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/330ca171-623a-44ab-91a0-7dbf7520fb83%40joeconway.com>
Precedence: bulk

On 3/13/25 06:10, Paul Foerster wrote:
>> The other option, which may be equally untenable, is to upgrade in-
>> place to pg17 and convert everything to use the new built-in
>> collation provider. That ought to be portable across different
>> versions of Linux.

> Is C.UTF8 really the same as en_US.UTF8? I ask because though we use
> en_US.UTF8, we are located in Switzerland and using non English
> characters is not exactly the exception. We have characters from all
> over the world in our databases. There must be no sorting
> differences between en_US.UTF8 and C.UTF8. Otherwise we will run
> into trouble with unhappy customers. So, C.UTF8 would only be an
> option if the collation would be identical.

Definitely not exactly the same. It does handle all the same characters 
(UTF8).

How often do you really depend on the ordering to the client being 
exactly what the end user wants to see? Often the final ordering is done 
in the application. Or could be done. You can also use a COLLATE clause 
to get exactly the ordering you want when you need it.

The builtin collation has two big advantages -- 1) it should be stable 
and portable, and 2) it should perform substantially faster (in simple 
tests I have seen 10X speed ups).

>> The problem you might find with libicu is that different versions
>> of ICU can have the same issues as different versions of glibc,
>> and you might not have the same ICU version available on SLES and
>> RHEL.

> Yes, I know. As far as I have been told, libicu is far less prone to
> major collation changes than glibc is.

Less prone, yes, but still it happens. And when it happens you can get 
corruption of your index, which can lead to things like duplicate 
primary/unique keys and data going to the wrong partition to cite two 
examples.

> Also, libicu offers the possibility to pin a version for a certain
> time. Our sysadmins will naturally not be able to pin a glibc
> version without wrecking an inevitable server upgrade.

Yes, in theory libicu can be pinned more easily than glibc, for sure. 
The reality is that you would either need your Linux distro to provide 
that pinned version as you upgrade, which I don't think any of them do 
(or in the case of SUSE to RHEL they would have to match from the get 
go), or you would have to take on maintaining your own pinned version 
going forward. That latter option is essentially the same as the glibc 
compatibility library approach though, so perhaps not horrible.

>> If you want to explore the compatibility library approach contact
>> me off list and I will try to get you started. It has been a
>> couple of years since I touched it, but when I did it took me a
>> couple of days to get from the AL2 (glibc 2.26) branch (which was
>> done first) to the RHEL 7 (glibc 2.17) branch.

> I just took a quick glance. I don't have a Github account (and also
> don't want one 🤣). I can do a git clone, but that's basically all I
> know. Also, right now, I'm just exploring possibilities. As far as I
> understand the readme on Github, this will replace the glibc on Red
> Had with one with adapted collation rules? If this is the case, then
> our admins will definitely say no to this.

No, it does not replace glibc. It extracts just the locale functionality 
from glibc into its own portable library, pretty much like libicu. Then 
you can link against it. So for example you wind up with "glibc 2.26 
locale semantics" with Postgres on your Linux distro which has glibc 
2.34 installed. All of the non-locale functionality comes from the 
system glibc 2.34.

>> [1] https://www.joeconway.com/presentations/2025-PGConf.IN-glibc.pdf
> This is a really good one. Thanks very much for this.

You should probably watch this presentation in its entirety:
   https://www.youtube.com/watch?v=KTA6oau7tl8
Jeremy does a really good job of dispelling misconceptions and if I 
remember correctly Jeff Davis talks about the builtin provider.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com