How does having multiple collations affect the size of the database?

Question

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?

Answer 1

You can have as many collations as you want: a collation is just a line in the table pg_collation and will take barely any space.

Here are two ideas how to deal with pattern matching:

1. Use the citext data type:

   CREATE EXTENSION IF NOT EXISTS citext;
   

   That works with LIKE.

2. Create a view that presents the same column twice:

   CREATE COLLATION english_ci (
      PROVIDER = icu,
      LOCALE = 'en-US@colStrength=secondary',
      DETERMINISTIC = FALSE
   );
   
   CREATE TABLE data (
      id bigint PRIMARY KEY,
      value text COLLATE english_ci
   );
   
   CREATE VIEW v_data AS
   SELECT id, value,
          value COLLATE "C" AS like_value
   FROM data;
   
   INSERT INTO v_data (id, value) VALUES (1, 'string');
   
   SELECT * FROM v_data WHERE like_value ILIKE 'Str%';
   
    id │ value  │ like_value 
   ════╪════════╪════════════
     1 │ string │ string
   (1 row)