Загадка сортировки PostgreSQL: Сказка об алфавитной анархии

Недавно я столкнулся с озадачивающей проблемой в PostgreSQL, которая на первый взгляд выглядела как “это невозможно”.

То, что должно было быть быстрой задачей на пол сторипойнта, превратилось в полноценную задачу на полновесный сторипойнт.

Неожиданный сюрприз сортировки

После добавления сортировки приложение стало себя вести иначе локально и на сервере.

Это было Django-приложение, и поначалу поведение было действительно загадочным.

Но через некоторое время я обнаружил, что проблема заключалась в том, как работает ORDER BY.

На моей локальной машине, когда я выполнял:

SELECT * FROM users ORDER BY name;

Результат был таким:

a
A
b
B

Но на сервере я увидел следующее:

A
B
a
b

Тот же запрос, разные результаты. Что происходило?

Виновник: различия в сортировке

На этом этапе было легко догадаться о первопричине: настройки collation.

Моя локальная машина использовала сортировку без учета регистра, которая следовала словарному порядку, в то время как сервер использовал collation “C”, которая сортирует по кодам ASCII.

Попытка исправления: указание сортировки

Моя первая мысль была указать collation для столбца:

ALTER TABLE users ALTER COLUMN name TYPE VARCHAR COLLATE "en_US.utf8";

Этот подход должен был обеспечить согласованную сортировку для столбца во всех запросах. Однако я столкнулся с препятствием…

Осложнение на сервере

Оказалось, что наш сервер PostgreSQL не поддерживал сортировку “en_US.utf8”, которую я пытался использовать.

Решение

Хорошо, другой подход. Я использовал LOWER в предложении ORDER BY:

SELECT * FROM users ORDER BY LOWER(name);

Это обеспечивает сортировку без учета регистра. Хотя и недетерминированную. Но для моей прикладной задачи это было не важно.

Дополнительное преимущество заключается в том, что не нужно изменять структуру таблицы.

Чтобы сделать это решение более эффективным, я создал индекс:

CREATE INDEX ON users (LOWER(name));

Этот индекс значительно улучшает производительность запросов при использовании LOWER в предложении ORDER BY.