MIME-Version: 1.0
References: <e4ac16908dad3eddd3ed73c4862591375a3f0539.camel@j-davis.com>
 <47e1b4f72fe732c5ae85c6cf2c085b4e99a10120.camel@j-davis.com>
 <CA+TgmobnOn9ipEUcthLmxvv0ZKBWyCcc048qqgqy1X+tszw_Cg@mail.gmail.com>
 <4309879ac305b1cf6b4d7b5fb85bc7b62c6ab768.camel@j-davis.com>
In-Reply-To: <4309879ac305b1cf6b4d7b5fb85bc7b62c6ab768.camel@j-davis.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Wed, 11 Mar 2026 08:47:58 -0400
Message-ID: 
 <CA+TgmoYPfiz6_QjkiKykwziFDZFzPVf_x77rChYBu=OGVOmbYw@mail.gmail.com>
Subject: Re: Change initdb default to the builtin collation provider
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoYPfiz6_QjkiKykwziFDZFzPVf_x77rChYBu%3DOGVOmbYw%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 10, 2026 at 3:04=E2=80=AFPM Jeff Davis <pgsql@j-davis.com> wrot=
e:
> If their environment's LC_CTYPE is UTF8-based, they already get UTF-8.
> If it isn't, we can either:
>
> (a) Fall back to LC_CTYPE=3DC, which is the only UTF8-compatible locale
> available everywhere. C is actually not a terrible fallback: it doesn't
> actually affect many things, because I have moved almost everything to
> use the database default locale.
>
> (b) Warn or error unless they explicitly specify the encoding with -E.
> But the former is likely to be ignored and the latter is not what I'd
> call "gentle".
>
> Which of these do you think is the right approach?

I'm a little confused as to how this relates to what you were asking
before. I thought you were proposing to pick UTF-8 rather than
SQL_ASCII when LC_CTYPE=3DC, but that's not on this list of options. To
be honest, I'd probably be ready to support making the default
encoding UTF8 regardless of the environment, and you have to use -E if
you want anything else. I think there are still people using other
encodings, but I believe it to be a small minority at this point.

> There's narrower question about what we do with LC_CTYPE=3DC. Currently
> we use SQL_ASCII encoding, which doesn't seem like a great default, and
> we could change that to default to UTF8. And another question about
> whether we change the meaning of --no-locale.

I think SQL_ASCII is a terrible default. Nobody actually wants that
unless they're trying to get out of a sticky situation. Making it
opt-in must be right. I do not know what the question about
--no-locale is.

> We sweat over single-digit performance regressions in fairly specific
> cases all the time, but here we're 3X slower for index builds:
>
> https://www.depesz.com/2024/06/11/how-much-speed-youre-leaving-at-the-tab=
le-if-you-use-default-locale/
>
> and 2-5X slower for Sort:
>
> https://www.postgresql.org/message-id/64039a2dbcba6f42ed2f32bb5f0371870a7=
0afda.camel@j-davis.com
>
> and others don't seem very concerned, so I feel like I'm missing
> something.

<insert shrug emoji here>

At the end of the day, we're all just guessing. My experience working
for EDB is that we have a number of customers who care about sort
order quite a lot, and we've had to sweat blood to make them happy.
And, on a personal level, I have a hard time understanding why anyone
would be OK with a sort order that puts =C3=81lvaro after Zebra instead of
between Alvaro and Beatriz, because that seems extremely frustrating.
However, these are just personal biases. I'm much more likely to hear
from the customers who care a lot about the details of how something
works than I am to hear from the customers who are perfectly happy to
take the defaults, because people who are happy don't contact support
at all and people who are unhappy about relatively normal things get
handled by support; I get the weird cases. And everybody is going to
have different experiences. Presumably, your experience is that the
indexing and sorting performance is a big concern for the users you
support, and that's why you favor prioritizing that part of the
experience. That's perfectly legitimate, but it's different from my
experience. My experience is that when I tell people they can use
collate "C" to speed up sorting, they tell me that's a stupid
workaround that doesn't give them the answers that they want, which
obviously colors my viewpoint on this question in the same way that
your experiences color yours.

--=20
Robert Haas
EDB: http://www.enterprisedb.com