Jak wybrać zestawienie dla międzynarodowej bazy danych?

Projektuję bazę danych, która będzie przechowywać dane w różnych językach (przy użyciu UTF-8), więc myślę, że najlepszym sposobem wyświetlania wyników zapytania jest uporządkowanie go zgodnie z językiem użytkownika podczas samego zapytania ( ponieważ istnieje więcej niż jeden poprawne sposoby ), w następujący sposób:

SELECT a < b COLLATE "de_DE" FROM test1;

Zakładając, że jest to właściwy sposób pracy z danymi międzynarodowymi, który jest najlepszym zestawieniem dla samej bazy danych? Dokumentacja PostgreSQL mówi :

Oba zestawienia C i POSIX określają „tradycyjne zachowanie C”, w którym tylko litery ASCII od „A” do „Z” są traktowane jak litery, a sortowanie odbywa się ściśle według wartości bajtów kodu znaków.

Myślę, że to najlepszy wybór w tym przypadku, czy się mylę?

(Pytanie dodatkowe: czy wybranie sortowania w samym zapytaniu jest zbyt wolne?).

CSortowania jest właściwym wyborem.

Wszystko jest trochę szybsze bez ustawień regionalnych. A ponieważ żadne sortowanie i tak nie jest prawidłowe, utwórz bazę danych bez sortowania, co oznacza za pomocą C.

Konieczne może być zestawienie wielu operacji. Jednak nie powinno być zauważalnej różnicy prędkości między zestawieniem domyślnym a zestawieniem ad-hoc. W końcu to tylko nieposortowane dane, a zasady sortowania są stosowane podczas sortowania.

Należy pamiętać, że Postgres opiera się na ustawieniach regionalnych dostarczonych przez podstawowy system operacyjny, dlatego należy wygenerować ustawienia narodowe dla każdego używanego ustawienia narodowego. Więcej w powiązanej odpowiedzi na temat SO tutaj i tutaj .

Jednak, jak już wspomniano @Craig , indeksy stanowią wąskie gardło w tym scenariuszu. Sortowanie indeksu musi odpowiadać sortowaniu zastosowanego operatora w wielu przypadkach, które dotyczą danych znakowych.

Możesz użyć COLLATEspecyfikatora w indeksach, aby utworzyć pasujące indeksy. Indeksy częściowe mogą być idealnym wyborem, jeśli miksujesz dane w tej samej tabeli.

Na przykład tabela z ciągami międzynarodowymi:

CREATE TABLE string (
   string_id serial
  ,lang_id   int NOT NULL
  ,string    text NOT NULL
);

I najbardziej interesuje Cię jeden język naraz:

SELECT *
FROM   string
WHERE  lang_id = 5  -- 5 being German / Germany here
AND    string > 'foo' COLLATE "de_DE"
ORDER  BY string COLLATE "de_DE";

Następnie utwórz częściowe indeksy, takie jak:

CREATE INDEX string_string_lang_id_idx ON string (string COLLATE "de_DE")
WHERE lang_id = 5;

Jeden dla każdego języka, którego potrzebujesz.

W rzeczywistości dziedziczenie może być lepszym podejściem dla takiej tabeli. Następnie możesz mieć zwykły indeks na każdej odziedziczonej tabeli zawierający tylko ciągi znaków dla jednego ustawienia narodowego. Oczywiście musisz czuć się komfortowo dzięki specjalnym zasadom dotyczącym odziedziczonych tabel.

Sugeruję wybranie sortowania zapewniającego domyślne porządkowanie w Unicode. W ten sposób uzyskasz rozsądne wyniki, nawet jeśli nie zastąpisz sortowania w każdym zapytaniu. Niestety, większość (wszystkich?) Systemów operacyjnych nie udostępnia ustawień narodowych, które są po prostu nazywane „domyślnym Unicode” lub czymś podobnym, więc będziesz musiał zgadywać i / lub szukać dobrego wyboru. Na przykład w systemie Linux / glibc ustawienia regionalne de_DE.utf8 lub en_US.utf8 po prostu przechodzą przez domyślne zachowanie, więc oba są dobrym wyborem.

Nie sądzę, aby używanie ustawień regionalnych C było dobrym pomysłem, ponieważ wtedy domyślne zachowanie aplikacji będzie bezużyteczne. I możesz nie uzyskać właściwego zachowania z operacji konwersji spraw.

(Przesłonięcie sortowania w zapytaniu nie ma dużego obciążenia. Jest to tylko operacja w czasie analizy).

Używamy postgres w kontenerze dokowanym, dlatego zawsze mamy dostęp do OIOM i używamy und-x-icugo domyślnie.

Jest to wspomniane w rozdziale 23.2.2.2.2. Sortowania ICU tych Postres docs jest mowa o:

und-x-icu (dla „niezdefiniowanych”)
zestawień „root” ICU. Użyj tego, aby uzyskać rozsądny porządek sortowania niezależny od języka.