Case-insensitive text in Postgres: citext, lower() or a nondeterministic collation

Someone signs up as Ann@Example.com, forgets, and a year later signs up again as ann@example.com. Now there are two accounts, two password resets that go to the same inbox, and a support ticket. Every app that stores email addresses has to decide what "the same" means, and Postgres gives you three ways to say "ignore case": compare lower(email), store the column as citext, or give the column a case-insensitive collation.

All three stop the duplicate sign-up. They differ in what happens after that: how lookups, LIKE, sorting and index builds perform, which queries quietly return nothing, and how hard it is to switch an existing column. We loaded the same million addresses three ways and measured.

The test tables

PostgreSQL 18.6 on an Apple M4 Mac mini, default settings. The database's default collation is ICU en-US, a linguistic collation like the en_US.UTF-8 most production databases are created with. One million distinct addresses, a third of them with capitals in them (User.c9077732a2@outlook.com), copied into three tables that differ only in the email column:

und-u-ks-level2 is ICU for "compare letters and accents, not case", and deterministic = false lets two strings with different bytes count as equal. The tables came out the same size (65 MB each) and so did the three unique indexes (47 MB each). Timings below are the fastest and slowest of three runs after a warm-up, all in one session.

The duplicate sign-up: all three catch it

Inserting USER.C9077732A2@OUTLOOK.COM when User.c9077732a2@outlook.com exists failed on all three tables with duplicate key value violates unique constraint. A plain text column with an ordinary UNIQUE constraint took both spellings without complaint, which is how the duplicates get there in the first place.

Lookups: all fast, until a query forgets

When each approach is used as intended, a lookup is a single index probe and nobody will notice the difference. The trouble is in the last two lines. With lower(), every query that touches the column has to remember to call it on both sides; one that doesn't gets a sequential scan and finds nobody. That's the reason citext exists, and its manual page says so: "you always have to remember to use lower on both the column and the query value."

citext has its own version of the same trap, and it's harder to see. The comparison is only case-insensitive when both sides are citext. Compare a citext column to a value typed text and Postgres casts the column to text and compares case-sensitively. A literal in the query is fine (it has no type yet, so it becomes citext), but many drivers send parameters as text or varchar, and so do array parameters:

Zero rows and a sequential scan, from a query that looks right and passes any test that uses lowercase data. If you use citext, check what type your driver or ORM binds string parameters as, or cast them in the SQL ($1::citext).

The collation has neither problem. The case rule belongs to the column, so WHERE email = $1 with a text parameter compares under ci and uses the index. One small cost we noticed: in a fresh connection the first lookup took 3.7 ms on the collation table (Postgres opens the ICU collator on first use) against 0.35 ms for citext and 0.012 ms for lower(). With a connection pool you pay that once per connection.

LIKE 'abc%': none of them uses the unique index

Searching for addresses that start with what the user typed (USER.ABC%, 245 matches) scanned the whole table every way:

A btree index can only serve LIKE 'abc%' if its order is plain byte order, and none of the three unique indexes is: two follow en-US and one follows ci. (On a database created with the C collation, the lower() index would serve prefix searches as it is.) The fix is the same in all three cases: a second index on the lowercased value in byte order, and queries written to match it.

LIKE on a nondeterministic collation is new in PostgreSQL 18; on 17 and older it is an error, and so were strpos() and the other text position searches, which now work too (replace('Hello World' COLLATE ci, 'WORLD', 'there') gave Hello there). It matches case-insensitively, as you'd hope, but it can't use an index and was the slowest of the three. ILIKE and regular expressions are still errors on such a column; add COLLATE "C" in the query to get them back (email COLLATE "C" ~* '^user\.abc' returned the same 245 rows). With citext, LIKE and ~ both match case-insensitively, which surprises people who wanted a case-sensitive regex; cast to text for that.

Sorting: citext was seven times slower

Sorting all million addresses without an index (index scans turned off, 256 MB of work_mem, no parallel workers, so each one is a plain in-memory quicksort):

The case-insensitive collation sorted as fast as the ordinary linguistic collation it's built on, because it is the same ICU comparison stopped one level earlier. lower(email) lowercases each value once and then sorts the results. citext lowercases both values inside every comparison, about 20 million comparisons for a million rows, and copies them to do it. The citext manual warns that it "is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons." We didn't expect seven times. In explainViewer, with the two plans compared, the whole difference is in the Sort node:

Index builds follow the sort, since building a btree is mostly a sort. The unique index took 3.5 seconds on citext and 0.7-1.0 seconds every other way:

If your lists are sorted by name or email, or you rebuild indexes during maintenance windows, that factor matters. For one-row lookups it doesn't. (The citext page also says it is "slightly more efficient than using lower". For single lookups we measured the opposite, 0.015 ms against 0.006, though both are too small to matter.)

Index size: no deduplication for citext or the collation

Unique indexes on distinct addresses came out the same size. The difference shows on columns with repeated values. Since PostgreSQL 13, a btree index stores a repeated value once with a list of rows, but only when equal values are also byte-for-byte identical, which isn't true for citext or a nondeterministic collation. An index on just the domain part (six different values across a million rows):

Four times bigger. The collation page in the manual says it plainly: "B-tree cannot use deduplication with indexes that use a nondeterministic collation." For a unique email column, it costs nothing. For a case-insensitive status, country or tag column it does, and there lower(), or storing the value lowercased in the first place, is the better fit.

Grouping and Unicode

GROUP BY and DISTINCT on citext or a ci column put Ann@x.io, ann@x.io and ANN@X.IO into one group and show one of the spellings, not one you chose. The others are gone from the output; if you need to know which spellings exist, group by email::text or email COLLATE "C".

The collation compares Unicode text the way ICU does, which lower() and citext don't. An é typed as e followed by a combining accent (how some systems and keyboards send it) is equal to a precomposed é under ci and not equal under lower() or citext. Accents are still significant at level 2: zoe@x.io and Zoë@x.io are different under ci, and equal under a level 1 collation (und-u-ks-level1), if accent-insensitive is what you want. Not everything folds: straße and STRASSE were unequal all three ways.

Migrating a column that is case-sensitive today

The usual starting point is a text column with an ordinary unique index, and some duplicates that differ only in case. Find them first, because all three routes will refuse to build a case-insensitive unique index while they exist:

Deciding which account survives is a product question (merge them, keep the one that logged in last, email the owner), not a SQL one. On our million rows the query took 1.0 second and found the two duplicates we'd planted. Then the three routes, timed on the same 1M-row table:

Neither B nor C rewrote the table (its file stayed the same), but both rebuilt every index on the column while holding an ACCESS EXCLUSIVE lock, so nothing could read or write the table for those seconds. They grow with the table: plan on minutes for a hundred million rows. If another table has a foreign key to the email column, change it too: since PostgreSQL 18, the two sides of a key must both use deterministic collations or the same nondeterministic one. Either one also fails with could not create unique index ... is duplicated if a case duplicate slipped in since you checked. Route A takes the longest to finish in code, since every query has to change to lower(email) = lower($1), but the database side can be done during the day.

A safe way to get to route B on a big table is to do the slow part without the lock: add a new column email_ci text COLLATE ci, fill it in batches, build its unique index CONCURRENTLY, keep it in sync with a trigger, then switch reads over and drop the old column. More steps, and no long lock.

Which one to use

On PostgreSQL 18, a nondeterministic collation. The case rule lives on the column, so a forgotten lower() or a parameter typed text can't bypass it, it sorts as fast as normal text, and Unicode is handled properly. The costs: no ILIKE or regex without a COLLATE "C", no index for LIKE without a second index, no deduplication, and it needs ICU (any packaged Postgres has it). On 12 to 17 it's still a good choice if you never need LIKE on the column.

lower() with an expression index, if you want nothing unusual in the schema, need LIKE prefix searches with one extra index, or the column has many repeated values. The cost is discipline: every query must use lower() on both sides. A check constraint, CHECK (email = lower(email)), with lowercase stored, removes even that, if you don't need to keep the spelling people typed.

citext for an existing schema that already uses it and works. For a new column, its own documentation now says "Consider using nondeterministic collations ... instead of this module", and the numbers agree: seven times slower to sort, four to five times slower to index, and a silent case-sensitive comparison whenever a parameter arrives as text.

Whichever you pick, test it the way it fails: look a user up with the capitals swapped, through your real driver, and run the query with EXPLAIN (ANALYZE, BUFFERS). A Seq Scan with zero rows means the case rule was skipped. Paste the plan into explainViewer and the scan is the node that takes the time.

Sources: PostgreSQL 18 documentation, citext (quoted on lower(), efficiency and the nondeterministic collation tip), Collation Support (nondeterministic collations, deduplication), Operator Classes and Operator Families (text_pattern_ops), B-Tree deduplication; PostgreSQL 18 release notes (LIKE with nondeterministic collations). Our own earlier piece on why sorting text is slow and what COLLATE "C" changes.