We're investigating changing an application from using MSSQL to Postgres.
We have a problem with collations - Postgres defaults to case sensitive whereas MSSQL defaults to case insensitive. If we change the Postgres collation to case insensitive, some feature (like LIKE) are no longer supported.
My colleague's solution to this is to add a case sensitive and case insensitive collation so that we can use the case insensitive one unless we need to do a LIKE, in which case we use the case sensitive collation with ILIKE.
My question is, how does having two collations affect the size of the database?
Is it going to be storing all text fields twice? Or all text in indexes?