PostgreSQL 9.1 released

PostgreSQL 9.1 released
postgresql.org

Here's the use case: you have a website, and you have users using it in English and French. With per-column collation, you are being advocated to have two fields, one english_name, and one french_name, that have /the same content/, but are defined using a different collation, so that the ordering condition on them becomes language-dependent.

The effect that has is actually terrible: it means that the size of your row (and yes, this may end up in TOAST, but there is still a massive penalty to going that route) ends up becoming ginormous, and the size of your row will just get larger the more languages you want to support as first-class citizens in your app.

Instead, what you /want/ is to just have an index english_ordered and an index french_ordered, and you want to be able to select which index you use for any specific query. If you "do it right", you'd also want to be able to support ordering the data using German collation, but it would "just be irritating slower".

Now, if you don't use PostgreSQL much, this may seem like a pipe dream of extra standards and complex interactions ("how will you specify that?!", etc.). However, it turns out there is already a feature that does 99% of this: "operator classes", which is how PostgreSQL lets you define custom collations for user-defined types.

Only, PostgreSQL operator classes are slightly more general than that, as you can specify an operator class to be used when performing order operations for your index; and, even more importantly: they are already being used to work around a specific case of operator-specific collation.

Here's the example: let's say that your database is set up for UTF-8 collation, and yet you have this one field you want to do a "prefix-match" on: WHERE a LIKE 'B%'. The problem with this is that you cannot use a Unicode collation to index this search: it might be that 'B' and 'b' and even 'Q' all index "exactly the same" for purposes of this collation (and there are some other corner cases with the other mapping direction as well).

So, to get index performance for this field, without changing your entire database to collate using "C" collation (which works out to "binary ordering"), you have a few choices, with one of them being to create a index that uses the special "operator class" called text_pattern_ops ("text" in this case as the field is likely a "text" field: there is also varchar_pattern_ops, etc.).

Once specified in your index, PostgreSQL knows to use it for purposes of the aforementioned LIKE clause. You specify this while making your index by specifying the operator class after the column.

    CREATE INDEX my_index ON my_table (a text_pattern_ops);

    CREATE INDEX my_index ON my_table (a english_collation_ops);
    CREATE INDEX my_index ON my_table (a french_collation_ops);
    
    SELECT * FROM my_table
        WHERE english_collation_less(a, 'Bob')
        ORDER BY a USING english_collation_less;

(Unfortunately, I suck at e-mail, or I'd get on the PostgreSQL mailing list and try to argue for this in a more well-defined way; maybe someone else who cares will eventually see it and become this feature's champion; or, of course, come up with an even better solution than mine ;P.)

As some people may balk at the "english_collation_less" operator usage (after all: using

    CREATE TABLE my_table (a collated_text);
    CREATE INDEX my_index ON my_table (a collation_ops('en_US'));
    SET client_collation = 'en_US';
    SELECT * FROM my_table WHERE a