Nikolay: Hello, hello, this is
Postgres.FM.

As usual, I'm Nik, Postgres AI,
and as usual, my co-host is

Michael, pgMustard.

Hi, Michael.

Michael: Hello, Nikolay.

Nikolay: How are you doing today?

Michael: I'm good, thanks.

How are you?

Nikolay: Great.

Very good, very good.

Thank you for asking.

So today we have a very big topic.

I'm joking.

But Yeah, I hope for this podcast
episode should be the shortest

because we are going to discuss
very narrow topic.

Although this topic hits almost
every project, as I noticed.

So every system has usually some
table like users.

It has some column like email and
in email we usually expect

the search to not to distinguish
registry of characters look

case it should be case-insensitive
search right So if it's all

uppercase email or lowercase or
mixed, all those values must

be considered the same.

However, All people made this mistake,
including myself, many,

many times.

You usually start using varchar
or something or text for email

column.

And then you see that multiple
accounts, multiple users' records

have basically the same record,
just written in different cases.

Right?

So here, we usually, what do we
do?

We just introduce additional unique
index, over expression, lower

email.

So we convert email value to lowercase
or uppercase, doesn't

matter, and build an index, B-tree index with unique option,

create unique index concurrently
on lower or upper function from

that value and this gives us uniqueness,
case-sensitive uniqueness,

right?

But that's not it, we then need
to fix our search queries.

Michael: And make sure that every
future use of email uses the

function as well in order to be
able to use the index or have

2 indexes if you want to be able
to, you know, there's a few

kind of like gotchas around this
approach.

Nikolay: Honestly, like roughly
half of the cases, you know,

I, we do a lot of health check
of our startups, like usually

like they are grown to some like
terabyte or so.

And we see some tables and not
every time we look into the, the,

the, the table structure, but oftentimes
we do.

And I just see some indexes, like
index on raw email value and

on lower email, I see it a lot.

And some queries use 1 index.

So we hunt for unused indexes,
which is part of our health check,

and we discussed it on our podcast
in terms of how to maintain

good health of indexes.

Extra indexes is not what you want
to have.

And in this case, if we have index
on raw value and on lower

of email, often we also see both
of them are used, which means

there is a mess.

So all of this was introduction
of our today's topic, which is

called citext.

Michael: Yeah, so this was, I know
it's a very narrow topic,

but this was my choice because
I was looking through the contrib

modules at what was in there that
was both popular and that we

hadn't discussed yet.

And we've got to enough episodes
now that that was not a long

list of ones that, I mean, there
was, there were definitely some

other extensions we have, some
other contrib modules that we

haven't discussed yet, but the
ones on that list are not ones

I hear discussed often, not ones
I see in use.

Whereas citext, I feel like at
least was quite popular.

It did get quite a lot of use,
or at least a lot of people were

interested in the use case.

So yeah, I was interested kind
of in your, in your perspective

on this.

And I also, after suggesting it
as a topic, started looking into

it more and found the topic more
interesting than I was expecting

to.

So yeah I'm looking forward to
getting your thoughts on this

and what people should be doing.

But just in terms of those drawbacks
of using the function approach,

there's a couple more mentioned
by the documentation I thought

were good.

1 is less important but there is
the just the verbosity of every

query and just having to have functions
in them.

But a more important 1, potentially,
depending on other opinions

you might have, is that you also
then can't use it as a primary

key if it's just text because,
or at least you'd have to have

another unique index on it to guarantee
uniqueness, to make sure

somebody couldn't sign up with
the same email address, but with

some case different because it's
the same email address.

But yeah, email, email's an important
1.

I don't know if you'd actually
want email as a primary key anywhere,

but also username.

Like a lot of services allow you
to set a username that is

case-insensitive, like social media
handles and things.

Those will often be, if not
case-insensitive, definitely unique

depending on case in a lot of cases.

So like Twitter handles, for example.

Nikolay: Yeah, and the question
is why it's an extension not

in core, right?

Michael: Well, there's a couple
of questions, right?

Like, could this be a data type?

Yeah, could it be a core data type?

Nikolay: Well, it is data type,
but

Michael: it's

Nikolay: living outside of the
core.

Michael: But I actually think so.

Well, I don't know how quickly
you want to jump ahead, but the

reason I found this super interesting
is In the docs for citext,

the contract module, it actually
suggests not using either citext

or the lower Function approach,
but instead creating a case-insensitive

collation and using that for it.

And that was so elegant.

But it is something you have to
do at the User level.

And we could, yeah, we could ship,
like, I don't see why Postgres

couldn't come with 1 of
those prebuilt that you could

just use.

And maybe even, maybe it wouldn't
be a case-insensitive field.

I could imagine a case.

Sorry, I'll keep this.

So I need to stop using the word
case to mean like argument.

I can imagine an argument for having
email as a datatype that

could be...

Nikolay: Oh, that's interesting.

I remember for Postgres, some huge
snippet to check that it's

really email.

It's like this RFC, right?

And it's not trivial to ensure
some valid email address or not.

It's not, it's not really like
so trivial as 1 might think.

Michael: Well, so I've seen 2 cases
for this 1 is the really

complicated 1, which actually I
think suits the argument that

it should be pre-built because
you don't want everybody rolling

their own version of that and The
other version is no just ship

it right back.

All you need to do is It doesn't
contain an at It maybe is it

all lowercase or can like cast
it to lowercase?

And then that's it.

Maybe a period after the at, or
a full stop after the at.

But I think anything more complicated
than that runs into potentially

not allowing things that should
be allowed, right?

Nikolay: So I will tell you what
I think.

First of all, I agree that collation
is an elegant solution.

And actually, collation, it's the
job of collations, even in

SQL standard, is to define rules
how we compare values, right?

And case sensitive or case-insensitive,
this is comparison.

So it's exactly where it should
be.

The only thing is, I remember there
should be some, like you

need to distinguish comparison
at the byte level, like logical

level.

It's like deterministic or something,
right?

Yes.

Yeah.

So honestly, I never use it myself.

Never.

I just read about this and I have no idea.

My own experience is, I was like, when I first discovered citext,

probably it happened 19 years ago or so.

Like on my second year of Postgres use, or Maybe in the first

year even.

Michael: I have the history of it here, by the way.

Nikolay: Yeah, it's a very old contrib module.

Michael: First released to the public 17th of February 2003,

but it wasn't contrib module at that point.

That only happened a few years later.

And it was a kind of like a redesign at that point.

But yeah, so you were pretty early.

Nikolay: Well, yeah, I started using Postgres in 2005 or 6.

And of course, it was social media.

So we definitely had the table users.

At the time, I tried to avoid plural.

So it was maybe something, something else like person or something.

Yeah, Then I was convinced by the influence of Ruby on Rails,

and Django and others, like, okay, it should be plural.

And then I quickly became a fan of citext.

And then I quickly became not a fan because like maybe 1 or 2

years later I stopped being a fan because like it's extra effort

to install extension and then you need to describe everyone what

like oh this special data type and I found it easier to keep

using text or varchar and these days I think just text but well

it's not a topic text or varchar and with limit or no for length.

Michael: But did you use collations or no?

Nikolay: No, no, no, no, no, no.

Michael: I just interesting.

Nikolay: It's just functions.

Yeah, function index with unique option if we need uniqueness,

that's it.

And then we need to adjust all the Queries.

And I was in my own projects, I was hunting to if we if we made

decision that it should be case-insensitive search, then probably

it should be global.

So we should get rid of index on raw value.

So that's it.

And fix it and all Queries and avoid sequential scans.

And that's it.

So this is like, you either want a sensitive search, so index

should be on the raw value.

Or you want case and sensitive search.

In this case, you must have index on lower or upper.

You need to choose in advance and make it a rule, like part of

your code style, probably.

And then use it usually on both sides of comparison, so input

value also get lowercase or uppercase,
and the value of Column,

and we know that we rely on Index
on expression here.

Michael: Yeah, well this is an
interesting thing that the original

author, so it was originally, the
citext module was originally

authored by Donald Fraser and in
the initial email about this,

1 of the use cases was multi-Column
functional indexes.

So if you wanted to have, so if
you want an Index on a single,

like just on email, fine, you can
do a Function on that.

But at least at the time, you couldn't
have it on 2, let's say

you wanted it on email, comma,
username, which may have been

more common back then before they
had like the includes option.

If you wanted it to be on the lower
of both of those, you'd have

to have like a kind of a parent
Function that did both of those.

So it got a bit messy in terms
of multi-Column indexes.

Whereas if you have them as their
own data type, you can just

do that at will.

Nikolay: Multi-Column expression
on 2 expressions, so lower email,

lower something else.

I think right now it's definitely
possible.

Michael: Okay, great.

I didn't realize that.

But back then it wasn't, so that
was like...

Nikolay: Yeah, that's interesting.

I don't remember this nuance.

Yeah, okay.

But yeah, so it's difficult to
name, to use the term multi-Column

here because it's already not Column
but expression yeah multi-layered

or something I don't know so multi-dimension
multi-dimensional

maybe Index on expressions.

It should work these days.

Not a problem, but also there is
a like there is in inconvenience.

If you go this route and use like
lower email or something you

it's easier to forget that we need
to when Index is created we

need to run Analyze on the Table
because it lacks statistics

on expression

Michael: yes yeah good point

Nikolay: If you build the regular
Index on raw, a Column value,

statistics usually already there.

But for expressional indexes, it's
not there.

But yeah, since then, I made this
decision in favor of expression

indexes and I never went back.

So why should I use citext in
2025?

Michael: I don't think you should,
but I also don't think you

should be having to do lower Functions.

Nikolay: Both paths are bad, collation
path is good.

Michael: That's what I think is
the solution now.

So it's like super interesting.

I actually came across just by
chance while looking into this

a blog post by Adam Johnson who's
worked on Django for many years.

It seems like Django actually removed
support or mostly removed

support for citext in version 5.1
of Django.

So they actually deprecated this
and he blogged about what you

should do instead and wrote up
about the how to set up a collation

and what we kind of define all
the kind of ICU I don't even know

what you call them like codes or
it's called a locale isn't it

But it's like made up of a bunch
of different, complete gibberish

in my opinion.

But you can look up the spec and
kind of choose which language

do you want it to be and do you
want it to be case sensitive

or not?

Do you want it to be sensitive
to accents or not?

So you

Nikolay: can.

Yeah, that's cool.

And it's much more flexibility.

And it's also SQL standard approach,
I think.

Michael: Yes, it is.

The last time

Nikolay: I looked into SQL standard,
it was more than 10 years

ago.

So my memory might make tricks
with me, but I remember something

about collations.

This came up,

Michael: well, in 2003 when it
was first proposed, I think I

read a post from Tom Lane in that
thread that said basically

the SQL standard way of doing this
is via collations, but I can't

see us doing that anytime soon.

So this makes sense as a hack in
the meantime.

Nikolay: Oh, that's great.

Yeah.

So, so that's great.

So it's very hard.

How are we going to name this episode?

I thought it will be citext now
it's shifted.

Michael: I was going to suggest
maybe case-insensitive

Nikolay: text.

Don't cut this.

Because it shows confusion, you
know, like it's like case-insensitive

comparison.

Yeah.

Michael: Because that's what we
want, isn't it?

Nikolay: But it goes beyond just
cases.

You mentioned excellence.

That's great.

Because some people might use my
like some names, for example,

like Thomas.

Maybe.

Michael: But as you said, I think
the 2 main use cases for this

are email addresses and usernames.

That's where I've seen it.

And email addresses are unique,
no matter the case, but I don't

know if they're unique.

Nikolay: No, they are not.

They are not.

But we might decide, for example,
if username in our system is

allowing like variants of Latin
characters, We might say, okay,

we allow them, but we don't want
collisions.

Like 1 user used pure Latin, another
user used like native version,

like from another language.

Many languages have it, right?

But we say we allow them, but first
person who use it should

be like, should be protected from
collisions.

In this case, we might decide to
use collisions and define unique

indexes.

That's a great idea, I think.

Yeah.

Michael: Yeah, good point.

In fact, I know it's not quite
related to that, but reading about

this, I came across somebody giving
an example of lower not always

being ideal, or lower and upper
being subtly different, because

in some languages, you can get
an uppercase character that can

have 2 alternative lowercase versions
of it.

I

Nikolay: didn't know about this.

Michael: Nor me.

But that, I mean, you can imagine
the kind of hassle that's going

to end up causing.

So yeah, it's...

Nikolay: Wow.

Michael: Collations are where this
stuff is defined properly,

right?

Yeah, yeah.

So it gives

Nikolay: you big freedom.

But what are downsides of using
collations?

Michael: Well, I did wonder because
do you remember, you know,

if glibc changes and we can end
up with corrupted indexes.

Nikolay: I don't, I, how can I
like,

Michael: Well, of course you remember?

Nikolay: Yes, of course I remember.

We had multiple cases with multiple
clients.

Michael: Could that be, Could that
be an issue here?

I

Nikolay: don't know.

Well, if you use ICU, it should
not be so.

I don't

Michael: see how.

Right, yeah, right.

Nikolay: Yeah.

And I think in this case, we definitely
want to specify a provider

ICU, right?

Yes.

Good point.

Yeah, and this comparison, I don't
see issue here.

And also there we have issues not
with like comparison but with

order right?

True.

Michael: So isn't that the same
thing?

Nikolay: No Who is bigger who is
who's upper who is lower basically

in the order it doesn't it's not
the same As who equals who I

know

Michael: it Except if you're looking
for like is there a clash

is this Is this unique?

You might traverse it only to the
point where you're expecting

it to be, but it's actually lowered
down.

So you say, oh no, I've already
got past that point in the index,

you can insert this and you can
end up with duplicate values

in the unique index.

Nikolay: Yeah, what I don't know
is if, so here we use deterministic

false, right?

Michael: Yes, yes.

Nikolay: And it means that at byte
level, the characters are

different, but we logically, we
think about them as equal.

But if you think about as equal
in the order by, they should

go together, right?

But do we have deterministic ordering
of them always?

This is, I don't know, like should
be, right?

Or there's some other mystic thing.

If so, that might cause this corruption.

Or yeah, If it's not unique index,
if it's unique index, it's

just 1 value.

I don't see corruption risks.

But if it's non-unique index and
like these values will come

in groups, is there like deterministic
structure?

I mean, not a rule, how they are
ordered.

So we avoid the switch of order
when glibc version changes.

But again, here we talk about ICU,
so glibc is not involved,

right?

So everything should be fine.

The only thing, just not to forget,
ICU should be enabled at

compile time.

I just, yeah, which is default,
I think right now.

Is it default?

I just remember Cursor and I were
fighting a few weeks ago because

my laptop didn't have ICU installed,
so I always reminded that

I created even Cursor a rule without
ICU.

If you run configure without this,
Then I surrendered and installed

ICUs just because somehow Cursor
kept losing this rule somehow.

I don't know why.

So yeah, so it should it should
be present.

It might be not present and not
be available on the system.

Michael: I think lots of people
use managed services these days,

I think it tends to be on those.

But yeah, really good point.

But yeah, this seems to me like
the way to do it now.

And just to complete that, I'm
going to read from Adam's blog

post.

The syntax is create collation,
and then you can call it whatever

you want.

For example, case-insensitive.

And then you set provider and he
goes provider equals ICU.

Then locale and then that's the
string that you can set all the

various settings we mentioned earlier.

And then you choose whether it's,
you have to choose in this

case, deterministic equals false.

So that's the byte-wise thing that
you're talking about.

Nikolay: Right.

Right.

Michael: So yeah, in fact, just,
is it worth going through that

gobbledygook in the locale quickly?

So yeah, he's gone with und, so
UND, I thought it was German

at first, but it actually just
means undetermined language.

So you could set it to like German
or French or something if

you really wanted to.

U specifies Unicode attributes,
and then KS, level 2.

So apparently that's collation
strength and then level 2 doesn't

include case in comparisons only
letters and accents.

So letters and accents can be considered
different, but case

can't.

So yeah, so that's what you'd want
in email address use case,

I think.

Nikolay: Yeah, so yeah, that's
interesting.

Anything else?

We have chances to have this episode,
as I promised, very short.

Or there are some additional aspects
here.

Michael: So no, I think that's
it.

Nikolay: So it looks like both
of us, if we were building a new

system, we probably would try to
use this approach with collations.

Sounds

Michael: like 100%

Nikolay: most flexible, most powerful
these days, right?

Michael: Yeah.

Nikolay: Yeah.

You know what I'm
checking right now and I see

cases about, collation
corruption.

Yeah.

Yeah.

I see, Daniel Vérité or how...

Michael: Oh yeah.

Is he psycopg?

Nikolay: Not sure.

I remember this name.

I read some blog posts in the past, but definitely collation

versioning problem with ICU 73.

So it's interesting to think about possible corruption when something

changes under the hood.

Yeah, and if we go this route, what to expect?

I lack personal experience with it, but I would definitely study

this topic closer because I thought if we use ICU collations,

well, glibc change is not a problem anymore, So we can upgrade

OS, right?

But it looks like an ICU library upgrade can be a problem.

So we need to check this, right?

We should be very careful considering what happened in the past

with glibc changes.

I remember it started from not knowing about it at all, then

thinking only one change was a problem, and then adjusting vision

like, you know what, every glibc version upgrade should be very

carefully tested because things change quite often actually there.

So I would bring this experience to here and then double check

what's happening.

Michael: It's probably a case for re-indexing at those times,

like having maybe a prioritized list of indexes that you want

to make sure, like maybe unique indexes at least.

Nikolay: It's only about unique indexes.

If we talk about corruption, we, again, order what matters.

And we don't want some values to be returned in different order

or search is not working as expected and so on.

Michael: I was thinking about the limitation of only being able

to concurrently create or like re-index one at a time.

You probably want to prioritize lists, so which ones would you

do first?

Yeah, maybe your unique ones first.

Nikolay: Yeah, during OS upgrades we usually look at them and

using amcheck to identify indexes which are going to be corrupted

and then we plan to just rebuild them or we rebuild them on if

it's this upgrade involves logical replication.

We rebuild them before we switch.

This logical replication gives us flexibility here.

But amcheck, back in the days, could only check B-tree indexes.

There was a multi-year work to bring it to GiST and GIN.

Yeah.

I don't remember.

There was some progress there recently.

So Postgres 18 probably will bring something, but I don't remember

from top of my head.

So that's why you probably want to rebuild all GIN and GiST indexes

just for safety, because corruption might happen there as well.

Michael: So nice.

Nikolay: Yeah.

Okay.

Yeah.

I hope this was helpful.

I learned something from you.

Thank you so much.

And next, next time I need this,
I probably will look into collations

based on ICU and deterministic
equals false.

Michael: Yeah, nice.

Nikolay: That's a good idea, yeah.

And probably we'll think about
accents next time.

Yeah, it's a good thing to keep
in mind.

Michael: For sure.

All right.

Nice one, Nikolay.

Take care.

Nikolay: Thank you.

See you next time.