3

PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query.

If I do it in the query, does PG punish me somehow performance-wise? I mean, it makes sense that it can "prepare itself" if I tell the table/column to have a specific "collate", rather than it having to figure it out "on the fly", but is it the case?

Also, any general tips on whether or not I should be having the collate in the query or table/column definition?

2

While not an expert on PostgreSQL collations, I have used PostgreSQL a little bit (I work mostly with SQL Server) and do not see how this particular info would/could be different than what I am about to describe:

Short answer: It Depends (standard answer).

Actual answer: specifying a collation at the column level (assuming that the collation is completely independent of the encoding / character set, which is the case for PostgreSQL and others, but in SQL Server the encoding is part of the collation) is merely a default that will be used for all sorting and comparison operations that do not explicitly provide a collation via a COLLATE clause. The storage of the data is impacted by the encoding / charset (i.e. Windows-1252 vs UTF-8 vs UTF-16 vs etc), but collation has no impact on data at rest because collation is merely rules for working with the data.

That said, the "depends" part is that if you create an index, it will physically store that data (i.e. the index keys in a separate structure) using the rules of the column's collation (unless you specify the COLLATE clause when creating the index, and not all RDBMSs allow for that). If you then specify a COLLATE clause in the query that is different than the collation used for the index, you will have a performance penalty because you won't be using that index. But, to specify a collation in a query that is different than the column's collation when an index is not available for the column's collation is not a performance issue as the data was not sorted in either collation prior to the query.

| improve this answer | |
  • FYI postgres does not store the data in the order of the Collation, MVCC prevents that. The data store in the tables will become completely random on a heavily updated table. – zsheep Feb 21 at 17:38
  • @zsheep Yes, we agree. I did specifically state that "collation has no impact on data at rest". I suppose one could misconstrue what I said regarding indexes as I did not explicitly state that the sorted data is a separate structure. I can clarify that. – Solomon Rutzky Feb 21 at 18:20
2

It does not matter if you use COLLATE in the column definition or (for example) in the ORDER BY clause, the price you pay is always the same. The reason is that the collation has no influence on how the strings are stored, only on how they are compared, which happens at query time.

Some collations are more expensive than others, and the cheapest is C, so use that whenever you can.

If you want to speed up queries with an index, the CREATE INDEX statement has to use the same COLLATE clause as the query. If you don't specify a COLLATE clause in CREATE INDEX or a query, the collation of the column is used. That is a good reason the specify the collation in the column definition, but it has to do with the ease of use, not with performance as such.

| improve this answer | |
0

Setting the collate on the table means when the order by or group by clause are used on that column that is what it will use by default, so it saves some typing

https://www.postgresql.org/docs/current/collation.html

Performance there is no impact other than the speed difference between the different collations.

The data is not stored in that specific order. One key issue when setting collation on a column is collation derivation

The collation derivation of an expression can be implicit or explicit. This distinction affects how collations are combined when multiple different collations appear in an expression. An explicit collation derivation occurs when a COLLATE clause is used; all other collation derivations are implicit. When multiple collations need to be combined, for example in a function call, the following rules are used:

If any input expression has an explicit collation derivation, then all explicitly derived collations among the input expressions must be the same, otherwise an error is raised. If any explicitly derived collation is present, that is the result of the collation combination.

Otherwise, all input expressions must have the same implicit collation derivation or the default collation. If any non-default collation is present, that is the result of the collation combination. Otherwise, the result is the default collation.

If there are conflicting non-default implicit collations among the input expressions, then the combination is deemed to have indeterminate collation. This is not an error condition unless the particular function being invoked requires knowledge of the collation it should apply. If it does, an error will be raised at run-time.

There is one thing that is not documented or I can not find anything.

I do not know is what

create index 

does when a column has collation defined?

I assume it would use that collation which brings up another interesting issue question
what does it do when its a composite index with different collations between the columns. I image would follow the above rules.

| improve this answer | |
  • 1
    Hi there. Regarding "create index": yes, it certainly should use the column's collation as it can't sort data without having rules to sort by. As for composite indexes, each column sorts independently, just like being able to specify ASC and DESC for each column in a composite index. So you can have widely different collations for columns in a composite index and it won't make a difference: each column sorts according to its defined collation. Does that make sense? – Solomon Rutzky Feb 21 at 17:23

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.