Postgres nondeterministic collation performance

Question

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.

• user1822: Nobody will give you some figure like "it's going to be x% slower". You will have to test it yourself, with your tables, your data, your queries and your workload. Race your horses
• Laurenz Albe: Also, I guess that the speed reduction may be similar to SQL Server - after all, case insensitive comparisons have to be more expensive.
• neveratdennys: I'm specifically looking to figure out if this postgres implementation would be slower than the old sql server counter part. Appreciate the input.
• neveratdennys: @a_horse_with_no_name It's just that this part is really hard to do. The migration will likely take me at least month or two with the scale I am working with. Hopefully there's a better way of getting around this.

Answer 1

I ended up finding this article with clear performance and reasoning behind the case insensitive pattern matching configuration in PostgreSQL.

enter image description here

https://www.cybertec-postgresql.com/en/case-insensitive-pattern-matching-in-postgresql/

• J Brun: Excellent article. Also, his article cybertec-postgresql.com/en/… talks about collation as well.