Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

caSe-inSENsiTive

Aug 8, 202526 minAudio

Nikolay and Michael discuss case-insensitive data - when we want to treat columns as case-insensitive, and the pros and cons of using citext, functions like lower(), or a custom collation. Here are some links to things they mentioned: citext https://www.postgresql.org/docs/current/citext.html Our episode on over-indexing https://postgres.fm/episodes/over-indexing Nondeterministic collations https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC How to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation (blog post by Adam Johnson) https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation The collation versioning problem with ICU 73 (blog post by Daniel Vérité) https://postgresql.verite.pro/blog/2023/10/20/icu-73-versioning.html amcheck https://www.postgresql.org/docs/current/amcheck.html ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

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.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode