The only thing I feel is wrong with this (really: the /only thing/, which is fricken awesome... I love PostgreSQL from the bottom of my heart, and have been using it for almost all of my database needs since the late 90s) is "per-column collation": collation is not a problem "per-column", it is a problem "per-index", which means you really want it "per-operator class".

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);

The next piece of the puzzle is that an ORDER BY clause can take a USING parameter to pass it a custom operator, and you can always (obviously) use a custom operator for purposes of comparison. So, you now are in the position where you should be able to do this:

    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;

So, really, the only thing that needs to be specified, is we need the ability to have "parameterized operator classes": as in, we really need a "meta operator class" that takes itself an argument, the string name of the collation, and then returns an operator class. With this one general technique defined, we not only drastically increase PostgreSQL's user-defined type abilities, but we better solve this whole class of collation problem.

(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.)