Sample Header Ad - 728x90

Postgres nondeterministic collation performance

3 votes
1 answer
1710 views
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.
Asked by neveratdennys (41 rep)
Sep 10, 2022, 12:34 AM
Last activity: Sep 12, 2022, 06:05 PM