1
  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

3

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.

2
  • You can make a database with UTF8 encoding but C collation. I only found two ways: one involves recreating the cluster which I don't think is possible on RDS... and the other is to change the server encoding, but RDS doesn't allow modifying the server_encoding. You used x collate "en_US"; above, is there a way to use it like create index on tbl (col collate "C");? (Would still end up having to sprinkle something though)
    – davidtgq
    Oct 12 '17 at 23:52
  • You can create just one database in a cluster with a different collation like this create database test3 encoding "utf8" LC_COLLATE 'C' template template0;, but I suspect RDS doesn't allow that either.
    – jjanes
    Oct 13 '17 at 19:56
0

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.

2
  • Speed isn't my primary concern, I might edit the question to put it at a lower order or remove it... in other words, I want to find out what solution is the proper method, since it seems clunky to specify text_pattern_ops all the time, and the fact that it's not the default operator class.
    – davidtgq
    Oct 12 '17 at 18:25
  • It's not at all clunky text_pattern_ops is only useful for left-anchored text patterns and = (which is also in the default class). Shy of that you're using unanchored search patterns you use trigram or if they're big blobs of text you should look into FTS. Oct 12 '17 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.