I'm trying to determine the performance characteristics of citext. In Microsoft SQL Server, the nvarchar(max) has performance hits when there is excessive memory grants required to use it versus something like a varchar(50). There are other performance hits that come in to play also. I'm trying to determine if citext in PostgreSQL has similar issues. We have a PostgreSQL backed web-app that is horribly slow and I'm wondering if the extensive use of the citext data type is contributing. Any thoughts on this? Thank you!
PostgreSQL performance citext
citextperformancepostgresql
Related Question
- PostgreSQL – Why Comparison Between CITEXT and TEXT Fails
- PostgreSQL – How citext Type Affects String Comparisons Using LOWER()
- SQL Server – Best Practices for VARCHAR Sizing
- Postgresql – System configuration to address poor PostgreSQL INSERT performance
- PostgreSQL – How to Change CITEXT Columns to VARCHAR
- PostgreSQL – Troubleshooting Ignored Expression Index on citext Column
- Postgresql – Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application
Best Answer
Case insensitive comparison is always costly than case sensitive one. The documentation also cites that in Limitations section. As I understood, the main problem is that you have a performance issue and you cannot locate the culprit(s). I suggest you to use pgBadger or pg_stat_statements to detect slow queries.