Text index using text_pattern_ops with utf-8 vs. default operator class with C?

Question

1. What is happening with each operator class, and why is it necessary to use text_pattern_ops when using utf-8?
2. When using the default operator class with C, what happens to the text column indexes if the database is dumped to another Postgres that uses utf-8? Is it simply a matter of creating new indexes with text_pattern_ops?
3. Does the default operator class with C perform any better or worse than text_pattern_ops?

I'm considering changing the RDS client_encoding parameter to C so it wouldn't be necessary to use text_pattern_ops everywhere, but I'd like to find out if any disadvantages (other than not supporting emojis and other languages) or complications may exist.

Related docs: https://www.postgresql.org/docs/current/static/indexes-opclass.html

Answer 1

It isn't about encoding, it is about collation.

Consider this:

select * from (values ('1,z'),('1,2j'),('12q'),('13 apples')) foo(x)
  order by x collate "en_US";

gives:

     x
-----------
 1,2j
 12q
 13 apples
 1,z

If you index using that collation, how would you efficiently support a x like '1,%' query? Not all the things starting with '1,' are adjacent in the index. This is the problem that text_pattern_ops (or C collation) solves.

You can make a database with UTF8 encoding but C collation. This mean you don't have to sprinkle text_pattern_ops all over your indexes, and your index builds over text columns will be much faster (and any sort-merge joins you do). But you can still store non-ASCII characters, although they will sort funny.

Also, setting client_encoding won't make any difference. Is is the server's encoding and collation that matter, not the client's.

Answer 2

dumped to another Postgres that uses utf-8? Is it simply a matter of creating new indexes with text_pattern_ops?

And dropping the default btree index.

Does the text_pattern_ops perform better/worse than the default with C?

Presumably, a tad faster as you won't even have to check if you're in a glyph when using a wildcard '%' in something LIKE 'foo%bar'. But, who knows and I doubt the difference is even measurable.

I'm considering changing the RDS client_encoding parameter to C so it wouldn't be necessary to use text_pattern_ops everywhere, but I'd like to find out if any disadvantages (other than not supporting emojis and other languages) or complications may exist.

That's a bad idea. Do you have any reason to believe that UTF8 is too slow for your use case. The whole idea of downgrading database disk encoding for speed seems very silly to me. I would be curious to see what you're finding too slow to begin with. My assumption is that you're preoptimzing.