Since you mentioned PostgreSQL, I can say with some authority that the main killer reason why non-UTF8 server-side encodings are supported in such detail is that the Japanese need it. Apparently, identical round-trip conversion between Unicode and the various Japanese "legacy" encodings is not always possible, and in some cases conversion tables are even different between vendors. It's baffling really, but it's apparently so. (The extensive character set support is also one of the reasons why PostgreSQL is so popular in Japan.)
Since we are talking about a database system, one of the main jobs is to be able to store and retrieve data reliably, as defined by the user, so lossy character set conversion sometimes won't fly. If you were dealing with the a web browser, say, where all that really matters is whether the result looks OK, then you could probably get away with supporting less encodings, but in a database system you have extra requirements.
Some of the other reasons mentioned in other answers also apply as supporting arguments. But as long as the Japanese veto it, character setup support cannot be reduced.
(1) What does byte sequence mean, an arrary of char in C? Is UTF-16 a byte sequence, or what is it then? (2) Why does a byte sequence have nothing to do with variable length?
You seem to be misunderstanding what endian issues are. Here's a brief summary.
A 32-bit integer takes up 4 bytes. Now, we know the logical ordering of these bytes. If you have a 32-bit integer, you can get the high byte of this with the following code:
uint32_t value = 0x8100FF32;
uint8_t highByte = (uint8_t)((value >> 24) & 0xFF); //Now contains 0x81
That's all well and good. Where the problem begins is how various hardware stores and retrieves integers from memory.
In Big Endian order, a 4 byte piece of memory that you read as a 32-bit integer will be read with the first byte being the high byte:
[0][1][2][3]
In Little Endian order, a 4 byte piece of memory that you read as a 32-bit integer will be read with the first byte being the low byte:
[3][2][1][0]
If you have a pointer to a pointer to a 32-bit value, you can do this:
uint32_t value = 0x8100FF32;
uint32_t *pValue = &value;
uint8_t *pHighByte = (uint8_t*)pValue;
uint8_t highByte = pHighByte[0]; //Now contains... ?
According to C/C++, the result of this is undefined. It could be 0x81. Or it could be 0x32. Technically, it could return anything, but for real systems, it will return one or the other.
If you have a pointer to a memory address, you can read that address as a 32-bit value, a 16-bit value, or an 8-bit value. On a big endian machine, the pointer points to the high byte; on a little endian machine, the pointer points to the low byte.
Note that this is all about reading and writing to/from memory. It has nothing to do with the internal C/C++ code. The first version of the code, the one that C/C++ doesn't declare as undefined, will always work to get the high byte.
The issue is when you start reading byte streams. Such as from a file.
16-bit values have the same issues as 32-bit ones; they just have 2 bytes instead of 4. Therefore, a file could contain 16-bit values stored in big endian or little endian order.
UTF-16 is defined as a sequence of 16-bit values. Effectively, it is a uint16_t[]. Each individual code unit is a 16-bit value. Therefore, in order to properly load UTF-16, you must know what the endian-ness of the data is.
UTF-8 is defined as a sequence of 8-bit values. It is a uint8_t[]. Each individual code unit is 8-bits in size: a single byte.
Now, both UTF-16 and UTF-8 allow for multiple code units (16-bit or 8-bit values) to combine together to form a Unicode codepoint (a "character", but that's not the correct term; it is a simplification). The order of these code units that form a codepoint is dictated by the UTF-16 and UTF-8 encodings.
When processing UTF-16, you read a 16-bit value, doing whatever endian conversion is needed. Then, you detect if it is a surrogate pair; if it is, then you read another 16-bit value, combine the two, and from that, you get the Unicode codepoint value.
When processing UTF-8, you read an 8-bit value. No endian conversion is possible, since there is only one byte. If the first byte denotes a multi-byte sequence, then you read some number of bytes, as dictated by the multi-byte sequence. Each individual byte is a byte and therefore has no endian conversion. The order of these bytes in the sequence, just as the order of surrogate pairs in UTF-16, is defined by UTF-8.
So there can be no endian issues with UTF-8.
Best Answer
Guessing the encoding of a message based on its byte values is always imperfect, although it can be made to give relatively accurate results. There are libraries available to do this, e.g. IBM's ICU.
But the preferable option is always either to standardize client and server on one encoding and always use that, or to force the client to declare the encoding of messages explicitly. It requires only a little programming effort and solves the entire question once and for all.