What citext actually does, and why you might stop using it

Citext is a PostgreSQL data type that stores text in a way that ignores uppercase and lowercase letters when you search or compare. If you store "Smith" as citext, a query looking for "smith" will find it. The question of whether to replace it is not about whether citext works — it does — but whether it solves your problem in the cleanest way, or whether a different approach fits your actual situation better.

Most teams that move away from citext do so because they discover they were using it as a shortcut for a decision they should have made earlier: what should uppercase and lowercase actually mean in your data? Citext hides that decision rather than answering it. Once you answer it clearly, you often find a simpler path.

Key Takeaways

  • Citext makes case-insensitive searches work automatically, but it applies that behavior everywhere in your database, which is often too broad.
  • The same result — case-insensitive search on one field — usually comes from a function or expression in your query, which gives you more control.
  • Citext can slow down indexes and make certain operations harder to optimize, especially if you only need case-insensitive behavior on some columns.
  • If you need case-insensitive search on usernames or emails but case-sensitive storage elsewhere, replacing citext with a targeted approach is usually faster and clearer.

The real cost of making everything case-insensitive

When you define a column as citext, PostgreSQL treats it as case-insensitive everywhere — in indexes, in joins, in sorting, in comparisons. That sounds convenient until you realize you may not want case-insensitive behavior in every single operation your database performs.

A common example: you store usernames as citext because you want "john.smith" and "John.Smith" to be treated as the same user. That works. But now when you sort users alphabetically, PostgreSQL sorts them case-insensitively too, which may not match how your process expects them ordered. You also cannot easily store both "john.smith" and "John.Smith" if you ever need to distinguish them — citext prevents that at the database level, not just in searches.

The index cost matters on large tables. A citext index is larger than a regular text index on the same data, because PostgreSQL has to store additional information to handle case-insensitive comparisons. On a table with millions of rows, that overhead adds up in storage and in query planning time.

Using functions and expressions instead of citext

The most common replacement for citext is to keep your column as regular text and use the LOWER() function in your queries. Instead of searching a citext column directly, you search LOWER(column_name) against a lowercased search term. This gives you case-insensitive search exactly where you need it, without changing how the data is stored or indexed everywhere else.

For example, instead of:

SELECT * FROM users WHERE username = 'john.smith';

You write:

SELECT * FROM users WHERE LOWER(username) = LOWER('john.smith');

The second query is slightly longer, but it does the same work. More importantly, it makes your intent visible: you are choosing case-insensitive comparison in this specific place, not declaring that the column itself is case-insensitive everywhere.

If you run this query often, you can create an index on LOWER(username) so the search stays fast. That index is smaller than a citext index would be, because it only covers the lowercase version of the data, not the original text plus the lowercase version.

When citext is still the right choice

Citext remains useful in specific situations. If nearly every operation on a column needs to be case-insensitive — if your entire process treats a field as case-insensitive by design — then citext can be simpler than writing LOWER() into dozens of queries. Email addresses are sometimes stored as citext for this reason, because case-insensitive email matching is a standard behavior across almost all email systems.

Citext also works well on smaller tables where the index size overhead does not matter. If you have a lookup table with a few hundred rows and you want case-insensitive search on one column, citext is straightforward and the performance difference is invisible.

The decision comes down to scope: if case-insensitive behavior applies to most or all of your queries on that column, citext is reasonable. If it applies to some queries but not others, or if you need case-sensitive storage alongside case-insensitive search, the LOWER() function approach is usually clearer and faster.

Collations as an alternative to citext

PostgreSQL also offers collations, which control how text is sorted and compared. A collation can make comparisons case-insensitive without using the citext type. You can assign a collation to a column or to a specific query, which gives you finer control than citext provides.

Collations are more complex to set up than citext, and they behave differently depending on your PostgreSQL version and your system's locale settings. For that reason, most teams either use LOWER() for simplicity or stick with citext if they have already chosen it. But collations exist as a middle ground if you need case-insensitive behavior that applies to sorting and comparison but you want to keep your column as regular text.

How to migrate away from citext if you decide to

If you have an existing column defined as citext and you want to change it to regular text, the process is straightforward but requires a moment of downtime on small tables or careful planning on large ones.

First, create a new column as regular text. Then copy the data from the citext column into the new column — the data itself does not change, only the type. Next, drop the old citext column and rename the new column to take its place. Finally, recreate any indexes or constraints that depended on the old column.

On a large table, you can do this in smaller steps to avoid locking the table for too long: create the new column without dropping the old one, populate it gradually, test your process against the new column, then switch over when you are confident. The exact steps depend on your table size and your tolerance for downtime.

Frequently Asked Questions

Does citext slow down my database?

Citext adds a small but measurable overhead to indexes and comparisons. On small tables or lightly queried columns, you will not notice it. On large tables with frequent searches, switching to LOWER() in your queries and a targeted index can be noticeably faster. The only way to know for your specific data is to test both approaches.

Can I use LOWER() in a WHERE clause without an index?

Yes, but it will be slow on large tables because PostgreSQL has to explore the LOWER() function to every row. To keep it fast, create an index on LOWER(column_name). Then PostgreSQL can use that index instead of scanning the whole table.

What if I need case-sensitive storage but case-insensitive search?

Use regular text and LOWER() in your search queries. Store "John.Smith" as-is, but search for it with WHERE LOWER(username) = LOWER('john.smith'). This gives you both behaviors: the original case is preserved in the database, and searches ignore case.

Is citext deprecated in PostgreSQL?

No, citext is not deprecated and PostgreSQL still maintains it. The question of whether to use it is a design choice, not a technical requirement. Many active PostgreSQL databases use citext successfully.

Should I use citext for email addresses?

Email addresses are case-insensitive by standard, so citext is a reasonable choice if your process treats them that way everywhere. However, LOWER() in your queries works just as well and gives you more flexibility if you ever need to store the original case for display purposes.