When to Stop Using Citext and What to Use Instead

Citext is still useful, but it creates problems you may not see until your database grows

Citext is a PostgreSQL data type that stores text without caring about uppercase or lowercase letters. A search for "Smith" finds "smith", "SMITH", and "Smith" all the same way. It sounds convenient, but it often causes performance problems, makes backups harder to move between systems, and creates unexpected behavior when you need case-sensitive data later.

Whether you should replace it depends on what you are actually trying to do. If you need case-insensitive searching for usernames or email addresses in a small database, citext works fine. If you are building something that will grow large, handle data from multiple countries, or need to move your database to a different system, you should replace it now while the work is still small.

Key Takeaways

• Citext makes searches case-insensitive automatically, but indexes on citext columns are slower and larger than indexes on regular text with a function.
• Replacing citext means storing data as regular text and using a function like LOWER() in your search queries instead.
• Citext can cause problems when you export data or move databases between PostgreSQL versions, because the behavior is not portable.
• For most new projects, a function-based approach is faster, more predictable, and easier to change later if your needs shift.

Why citext causes performance problems as your data grows

Citext columns create indexes that are larger and slower than they need to be. When PostgreSQL indexes a citext column, it has to store a case-insensitive version of every value. That takes more disk space and makes the index slower to search through, especially when you have millions of rows.

A function-based index using LOWER() does the same work but more efficiently. You store the original text as-is and tell PostgreSQL to index only the lowercase version. The index is smaller, faster to build, and faster to search. The difference is small on a table with a thousand rows, but noticeable at a hundred thousand rows and significant at a million.

Citext also does not play well with other PostgreSQL features. Full-text search, for example, works better with regular text columns. Pattern matching and regular expressions can behave unexpectedly with citext. If you ever need to add these features later, you will have to rewrite the column anyway.

How to replace citext with a function-based approach

The replacement is straightforward. You change the column type from citext to text, create an index using LOWER(), and update your queries to use LOWER() when searching.

Here is the actual work: First, create a new index on the lowercase version of the column. If your column is called username and the table is called users, run this:

CREATE INDEX idx_users_username_lower ON users (LOWER(username));

Next, update your search queries. Instead of writing:

SELECT * FROM users WHERE username = 'john';

Write:

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

PostgreSQL will use the index you created, so the search is just as fast. Then you can drop the old citext column and recreate it as text, or if the column is small enough, just alter it directly. The whole process takes minutes on a table with millions of rows.

When citext actually makes sense to keep

Citext is worth keeping if your database is small, your team is comfortable with PostgreSQL-specific features, and you never plan to move the data to another database system. Small internal tools, prototypes, and single-server applications where performance is not a concern are good candidates.

Citext also makes sense if you are building something where case-insensitive matching is the core feature and you want that behavior everywhere without thinking about it. Some applications benefit from that simplicity, even if it costs a little performance.

But if you are building something that might grow, that might move to a different database later, or that needs to integrate with other systems, replace citext now. The work is easier when your table is small, and you will not regret it when you scale.

Portability problems when you move databases

Citext is a PostgreSQL extension. If you ever need to move your data to MySQL, SQL Server, or any other database, citext columns do not exist there. You have to rewrite the schema and update all your queries. A function-based approach using LOWER() works on almost every database system, so moving is simpler.

Even moving between PostgreSQL versions can cause surprises. Citext behavior has changed slightly between major versions, and backups created with one version sometimes behave differently when restored to another. Regular text with a function is stable across versions and does not have these edge cases.

If your data might ever leave PostgreSQL, or if you might need to restore a backup to a different system for testing or disaster recovery, use LOWER() instead of citext from the start.

What to do if you already have citext in production

Do not panic. Citext works fine in production; it just is not optimal. You can replace it gradually without downtime. Create the new index while the old column is still there, test your queries with LOWER(), and then switch your application over. Once everything is working, drop the citext column and convert it to text.

If your table is very large (hundreds of millions of rows), do the index creation during a maintenance window when traffic is low. The index creation locks the table briefly, but PostgreSQL can build indexes without blocking reads on newer versions.

If you have a small table, you can do this work during normal business hours. The whole process usually takes less than an hour, and your application will not notice any difference.

Frequently Asked Questions

Will switching from citext to LOWER() make my searches slower?

No. A function-based index on LOWER() is actually faster than a citext index on the same data. PostgreSQL optimizes queries that use LOWER() with a matching index, so the performance is identical or better. The only difference is that you have to write LOWER() in your queries instead of relying on citext to do it automatically.

Can I convert a citext column to text without rebuilding the whole table?

Yes. You can use ALTER TABLE to change the column type directly on most versions of PostgreSQL. The conversion is fast because text and citext store data the same way internally. You just need to create the new index first and update your queries before you drop the citext column.

What if I need case-insensitive searching but also need to preserve the original case?

Store the text as regular text and use LOWER() only in your search queries. The original case is preserved in the column, and your searches are still case-insensitive. This is actually better than citext, because you can display the original case to users while searching case-insensitively behind the scenes.

Does LOWER() work with non-English characters and accents?

Yes, LOWER() handles Unicode characters correctly, including accented letters. It respects the collation you set on the column, so it works the same way across different languages. Citext also handles this, so you are not losing anything by switching.

Should I replace citext in an old application that is already working?

Only if you are planning to scale the application, move it to another database, or you notice performance problems. If the application is stable and small, citext is fine. But if you are adding new features or planning to grow, replacing it now is easier than doing it later when the table is much larger.