After reading documentation I thought I should ask about the performance issues with the case insensitive behavior I am looking for. For context, I am working on a migration project from SQL Server to PostgreSQL, and I need to make sure that case insensitivity is achieved for all data entries in the database. Will this become a issue for the database migration? From my understandings, there will be performance issues when all tables are using nondeterministic collations.
While nondeterministic collations give a more “correct” behavior, especially when considering the full power of Unicode and its many special cases, they also have some drawbacks. Foremost, their use leads to a performance penalty. Note, in particular, that B-tree cannot use deduplication with indexes that use a nondeterministic collation. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.