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.

Improve this question
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.
    – Laurenz Albe
    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.
    – neveratdennys
    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.
    – neveratdennys
    Sep 12, 2022 at 16:46

1 Answer 1

Reset to default
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/

Improve this answer
1

Your Answer

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

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