Text index using text_pattern_ops with utf-8 vs. default operator class with C? What is happening with each operator class, and why is it necessary to use text_pattern_ops when using utf-8?
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?
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
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.