Does PostgreSQL handle case-insensitive database as good as others? [closed]

Question

First things first, please forgive me if my question seems so naive (I am not a DBA). I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of the official docs useful. I have tried the following but there was no luck.

CREATE COLLATION ci_fa (provider = icu, locale = 'fa-IR-x-icu', deterministic = false); -- also locale = 'fa-IR' and 'fa-IR.UTF8'

I found this answer but the locale used there, is not based on what I am looking for. Also, the example in the docs reads as:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

This works fine but I faced with a wired behavior in sorting. If I had a non-English character in my order column the result would be different. When I have a, A, b and the non-English char, the result is a, A, b. And when I have a, A, B and the non-English char, the result is A, a, B.

On the other hand, reading a lot of articles and questions made me wondered about the following questions?

1. Having case-insensitive data impact the performance, so should I use it or not? Considering the following facts:

   • 99% of my data has no casing. Our language is simpler so there is no Lower/Upper case in letters :)
   • I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect Full-Text.
   • I don't like any of the suggested solutions here (How to make “case-insensitive” query).
   • Although I use EF Core 5, I have DB functions to handle complex queries.

2. I am at the early stage of my project and for the first time, I decided to migrate to PostgreSQL from SQL Server. Should I use MySQL instead?

   • I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).
   • I need Geolocation.
   • I need Full-Text.
   • And obviously, performance.

Answer 1

When I have a, A, b and the non-English char, the result is a, A, b. And when I have a, A, B and the non-English char, the result is A, a, B.

It's an effect of the deterministic collation property being false. There is no tie-breaker between equivalent strings, so a may come out before A or the opposite. Also sorts in database are generally not stable, because in relational theory, rows have no inherent order so sticking to the "original order" makes no sense.

If you don't like that, you may add a second order with a deterministic collation, for instance

ORDER BY field COLLATE "non-deterministic-collation", field COLLATE "C"

I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect Full-Text.

It doesn't. The full text search parser is mostly influenced by the lc_ctype property of the database. There is even a specific error associated to forcing a collation when extracting a tsvector from a string:

=> select to_tsvector('abc') collate "C";
ERROR:  collations are not supported by type tsvector

I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).

When a database is created, it does inherit a collation from the template database, or get it from the lc_collate or locale clause if specified explicitly. That's the default collation for all sorts and string comparisons in this database.

If you use Linux in an UTF-8 encoding with fa_IR locale, the lc_collate of your database is probably fa_IR.UTF-8, so it does use a Persian collation implicitly. There is no clear indication in your question that your application really needs to create custom collations beyond that.

The details for creating collation can be fine here and here and to create Persian collation the following can be used.

CREATE COLLATION the_name (provider = icu, locale = 'fa-IR@colStrength=secondary', deterministic = false);