3

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.

4
  • 1
    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
    – user1822
    Sep 10, 2022 at 6:53
  • 1
    Also, I guess that the speed reduction may be similar to SQL Server - after all, case insensitive comparisons have to be more expensive. Sep 11, 2022 at 14:25
  • I'm specifically looking to figure out if this postgres implementation would be slower than the old sql server counter part. Appreciate the input. Sep 12, 2022 at 16:45
  • @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. Sep 12, 2022 at 16:46

1 Answer 1

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/

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.