Локаль против производительности: сравнение сортировок PostgreSQL с помощью pg_expecto.

C или UTF-8?

Предисловие

В мире PostgreSQL настройки локали LC_COLLATE и LC_CTYPE часто остаются в тени, пока не сталкиваешься с проблемами производительности при работе с текстовыми данными. В статье приведены результаты нагрузочного тестирования, чтобы количественно оценить эту разницу и помочь администраторам баз данных сделать осознанный выбор между эффективностью и лингвистической точностью.

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

Тестовая СУБД

CPU = 8

RAM = 8GB

PostgreSQL 17

1. Создание таблиц

CREATE TABLE categories (

id SERIAL PRIMARY KEY,

category_name VARCHAR(100) NOT NULL,

description TEXT,

created_date DATE

);

CREATE TABLE products (

id SERIAL PRIMARY KEY,

category_id INTEGER REFERENCES categories(id),

product_name VARCHAR(150) NOT NULL,

manufacturer VARCHAR(100),

price DECIMAL(10,2),

in_stock BOOLEAN

);

2. Заполнение таблицы categories (1000 записей) с русскими буквами

INSERT INTO categories (category_name, description, created_date)

SELECT

CASE (seq % 20)

WHEN 0 THEN 'Электроника и бытовая техника'

WHEN 1 THEN 'Одежда и обувь'

WHEN 2 THEN 'Дом и сад'

WHEN 3 THEN 'Красота и здоровье'

WHEN 4 THEN 'Спорт и отдых'

WHEN 5 THEN 'Автотовары'

WHEN 6 THEN 'Книги и канцтовары'

WHEN 7 THEN 'Детские товары'

WHEN 8 THEN 'Продукты питания'

WHEN 9 THEN 'Ювелирные изделия'

WHEN 10 THEN 'Строительные материалы'

WHEN 11 THEN 'Мебель и интерьер'

WHEN 12 THEN 'Игрушки и игры'

WHEN 13 THEN 'Товары для животных'

WHEN 14 THEN 'Музыкальные инструменты'

WHEN 15 THEN 'Антиквариат и коллекции'

WHEN 16 THEN 'Цифровые товары'

WHEN 17 THEN 'Хобби и творчество'

WHEN 18 THEN 'Туризм и путешествия'

WHEN 19 THEN 'Бытовая химия'

END || ' ' || LEFT(md5(random()::text), 4) as category_name,

CASE (seq % 10)

WHEN 0 THEN 'Высококачественные товары премиум класса для требовательных покупателей'

WHEN 1 THEN 'Современные решения для дома и офиса с гарантией качества'

WHEN 2 THEN 'Экологически чистые продукты с сертификацией соответствия'

WHEN 3 THEN 'Инновационные технологии и передовые разработки'

WHEN 4 THEN 'Классические модели проверенные временем'

WHEN 5 THEN 'Сезонные товары со скидкой до 50%'

WHEN 6 THEN 'Эксклюзивные предложения от ведущих производителей'

WHEN 7 THEN 'Бюджетные варианты для экономных покупателей'

WHEN 8 THEN 'Профессиональное оборудование для бизнеса'

WHEN 9 THEN 'Товары для всей семьи с доставкой по России'

END as description,

DATE '2020-01-01' + (random() * 1500)::integer as created_date

FROM generate_series(1,1000) as seq;

3. Заполнение таблицы products (1 000 000 записей) с русскими буквами

INSERT INTO products (category_id, product_name, manufacturer, price, in_stock)

SELECT

(random() * 999 + 1)::integer as category_id,

-- product_name с русскими названиями

CASE (seq % 50)

WHEN 0 THEN 'Смартфон ' || (seq % 10 + 1)::text || ' поколения'

WHEN 1 THEN 'Ноутбук игровой ' || chr(65 + (seq % 10)) || ' серии'

WHEN 2 THEN 'Платье вечернее ' || CASE (seq % 5) WHEN 0 THEN 'красное' WHEN 1 THEN 'черное' WHEN 2 THEN 'синее' WHEN 3 THEN 'зеленое' WHEN 4 THEN 'белое' END

WHEN 3 THEN 'Диван угловой ' || (seq % 8 + 1)::text || '-местный'

WHEN 4 THEN 'Кроссовки беговые ' || CASE (seq % 4) WHEN 0 THEN 'мужские' WHEN 1 THEN 'женские' WHEN 2 THEN 'детские' WHEN 3 THEN 'унисекс' END

WHEN 5 THEN 'Кофеварка автоматическая ' || chr(65 + (seq % 5)) || ' класса'

WHEN 6 THEN 'Велосипед горный ' || (seq % 21 + 24)::text || ' дюймов'

WHEN 7 THEN 'Шампунь для волос ' || CASE (seq % 3) WHEN 0 THEN 'сухих' WHEN 1 THEN 'жирных' WHEN 2 THEN 'окрашенных' END

WHEN 8 THEN 'Книга развивающая для детей ' || (seq % 10 + 3)::text || ' лет'

WHEN 9 THEN 'Автомобильные шины ' || (seq % 18 + 13)::text || ' радиуса'

WHEN 10 THEN 'Ювелирное кольцо ' || CASE (seq % 4) WHEN 0 THEN 'золотое' WHEN 1 THEN 'серебряное' WHEN 2 THEN 'платиновое' WHEN 3 THEN 'палладиевое' END

WHEN 11 THEN 'Краска акриловая ' || CASE (seq % 6) WHEN 0 THEN 'белая' WHEN 1 THEN 'черная' WHEN 2 THEN 'синяя' WHEN 3 THEN 'красная' WHEN 4 THEN 'желтая' WHEN 5 THEN 'зеленая' END

WHEN 12 THEN 'Стол письменный ' || (seq % 5 + 1)::text || '-местный'

WHEN 13 THEN 'Конструктор детский ' || (seq % 500 + 100)::text || ' деталей'

WHEN 14 THEN 'Корм для кошек ' || CASE (seq % 3) WHEN 0 THEN 'сухой' WHEN 1 THEN 'влажный' WHEN 2 THEN 'полувлажный' END

WHEN 15 THEN 'Гитара акустическая ' || (seq % 4 + 1)::text || '/4 размера'

WHEN 16 THEN 'Монета коллекционная ' || (seq % 50 + 1970)::text || ' года'

WHEN 17 THEN 'Курс онлайн обучения ' || CASE (seq % 5) WHEN 0 THEN 'программированию' WHEN 1 THEN 'дизайну' WHEN 2 THEN 'маркетингу' WHEN 3 THEN 'финансам' WHEN 4 THEN 'иностранным языкам' END

WHEN 18 THEN 'Набор для вышивания ' || CASE (seq % 4) WHEN 0 THEN 'крестиком' WHEN 1 THEN 'бисером' WHEN 2 THEN 'гобеленом' WHEN 3 THEN 'алмазной мозаикой' END

WHEN 19 THEN 'Рюкзак туристический ' || (seq % 70 + 20)::text || ' литров'

WHEN 20 THEN 'Стиральный порошок ' || CASE (seq % 3) WHEN 0 THEN 'автомат' WHEN 1 THEN 'ручная стирка' WHEN 2 THEN 'цветные ткани' END

ELSE 'Товар ' || seq::text || ' категории ' || (seq % 20 + 1)::text

END as product_name,

-- manufacturer с русскими названиями

CASE (seq % 25)

WHEN 0 THEN 'ООО "Русские технологии"'

WHEN 1 THEN 'ЗАО "Восток-Сервис"'

WHEN 2 THEN 'АО "Северный ветер"'

WHEN 3 THEN 'ПАО "Южные просторы"'

WHEN 4 THEN 'ИП Иванов А.С.'

WHEN 5 THEN 'Компания "Запад-Импорт"'

WHEN 6 THEN 'Фабрика "Красный октябрь"'

WHEN 7 THEN 'Завод "Сибирские узоры"'

WHEN 8 THEN 'Фирма "Уральские самоцветы"'

WHEN 9 THEN 'Объединение "Дальневосточный край"'

WHEN 10 THEN 'Корпорация "Центральный регион"'

WHEN 11 THEN 'ТД "Волга-Дон"'

WHEN 12 THEN 'Холдинг "Байкал-Амур"'

WHEN 13 THEN 'Концерн "Кавказские минералы"'

WHEN 14 THEN 'Группа "Белые ночи"'

WHEN 15 THEN 'Альянс "Золотое кольцо"'

WHEN 16 THEN 'Синдикат "Черное море"'

WHEN 17 THEN 'Ассоциация "Янтарный берег"'

WHEN 18 THEN 'Кооператив "Ладожское озеро"'

WHEN 19 THEN 'Общество "Онежские дали"'

WHEN 20 THEN 'Промышленность "Алтайские горы"'

WHEN 21 THEN 'Мануфактура "Камские просторы"'

WHEN 22 THEN 'Производство "Донские степи"'

WHEN 23 THEN 'Индустрия "Кубанские нивы"'

WHEN 24 THEN 'Предприятие "Кольские тундры"'

END as manufacturer,

(random() * 10000 + 10)::decimal(10,2) as price,

(random() > 0.3) as in_stock

FROM generate_series(1,1000000) as seq;

4. Создание индексов для улучшения производительности

CREATE INDEX idx_categories_name ON categories(category_name);

CREATE INDEX idx_products_name ON products(product_name);

CREATE INDEX idx_products_manufacturer ON products(manufacturer);

CREATE INDEX idx_products_category_id ON products(category_id);

5. Запрос с JOIN, GROUP BY и SORT BY по текстовым полям

SELECT

c.category_name,

p.manufacturer,

COUNT(p.id) as total_products,

AVG(p.price) as avg_price,

SUM(CASE WHEN p.in_stock THEN 1 ELSE 0 END) as in_stock_count

FROM categories c

JOIN products p ON c.id = p.category_id

WHERE p.manufacturer LIKE '%Русск%'

OR p.manufacturer LIKE '%Сибир%'

OR p.manufacturer LIKE '%Ураль%'

GROUP BY c.category_name, p.manufacturer

HAVING COUNT(p.id) > 5

ORDER BY c.category_name ASC, p.manufacturer ASC

LIMIT 50;

6. Запрос с сортировкой по текстовым полям и поиском

SELECT

c.category_name,

p.product_name,

p.manufacturer,

p.price

FROM products p

JOIN categories c ON p.category_id = c.id

WHERE p.product_name LIKE '%Смартфон%'

OR p.product_name LIKE '%Ноутбук%'

OR p.product_name LIKE '%Диван%'

ORDER BY c.category_name DESC, p.product_name ASC

LIMIT 30;

Тестовые базы данных

locale_c : Collate = С

locale_ru : Collate = ru_RU.UTF-8

Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges

-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-------------------

locale_c | postgres | UTF8 | icu | C | C | ru-RU | |

locale_ru | postgres | UTF8 | icu | ru_RU.UTF-8 | ru_RU.UTF-8 | ru-RU | |

(2 rows)

План выполнения : Запрос с JOIN, GROUP BY и SORT BY по текстовым полям

Limit (cost=52.65..383.64 rows=50 width=131) (actual time=3.878..47.432 rows=50 loops=1)

-> GroupAggregate (cost=52.65..55215.07 rows=8333 width=131) (actual time=3.876..47.419 rows=50 loops=1)

Group Key: c.category_name, p.manufacturer

Filter: (count(p.id) > 5)

-> Incremental Sort (cost=52.65..53405.66 rows=114753 width=94) (actual time=3.841..46.655 rows=1934 loops=1)

Sort Key: c.category_name, p.manufacturer

Presorted Key: c.category_name

Full-sort Groups: 17 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB

Pre-sorted Groups: 17 Sort Method: quicksort Average Memory: 37kB Peak Memory: 37kB

-> Nested Loop (cost=0.70..46062.36 rows=114753 width=94) (actual time=0.157..44.242 rows=1966 loops=1)

-> Index Scan using idx_categories_name on categories c (cost=0.28..50.47 rows=1000 width=42) (actual time=0.057..0.098 rows=18 loops=1)

-> Index Scan using idx_products_category_id on products p (cost=0.42..44.86 rows=115 width=60) (actual time=0.051..2.427 rows=109 loops=18)

Index Cond: (category_id = c.id)

Filter: (((manufacturer)::text ~~ '%Русск%'::text) OR ((manufacturer)::text ~~ '%Сибир%'::text) OR ((manufacturer)::text ~~ '%Ураль%'::text))

Rows Removed by Filter: 822

План выполнения : Запрос с сортировкой по текстовым полям и поиском

Limit (cost=48.81..73.58 rows=30 width=133) (actual time=3.304..3.310 rows=30 loops=1)

-> Incremental Sort (cost=48.81..48872.85 rows=59147 width=133) (actual time=3.303..3.306 rows=30 loops=1)

Sort Key: c.category_name DESC, p.product_name

Presorted Key: c.category_name

Full-sort Groups: 1 Sort Method: quicksort Average Memory: 39kB Peak Memory: 39kB

-> Nested Loop (cost=0.70..45502.36 rows=59147 width=133) (actual time=0.131..3.213 rows=58 loops=1)

-> Index Scan Backward using idx_categories_name on categories c (cost=0.28..50.47 rows=1000 width=42) (actual time=0.061..0.065 rows=2 loops=1)

-> Index Scan using idx_products_category_id on products p (cost=0.42..44.86 rows=59 width=99) (actual time=0.131..1.562 rows=29 loops=2)

Index Cond: (category_id = c.id)

Filter: (((product_name)::text ~~ '%Смартфон%'::text) OR ((product_name)::text ~~ '%Ноутбук%'::text) OR ((product_name)::text ~~ '%Диван%'::text))

Rows Removed by Filter: 517

Результаты нагрузочного тестирования

Нагрузка на СУБД

Операционная скорость

График изменения операционной скорости в ходе нагрузочного тестирования для тестовой базы данных "Collate = С" и "Collate = ru_RU.UTF-8"

Среднее повышение операционной скорости при использовании "Collate = ru_RU.UTF-8" составило ~7%.

Анализ метрик производительности инфраструктуры

ТЕСТ-1 : Collate = С

ТЕСТ-1 : ru_RU.UTF-8

1. Процессы (procs_r)

• ТЕСТ-1: Количество процессов в состоянии выполнения (r) варьируется от 20 до 54, с пиками до 33–54 во второй половине теста.
• ТЕСТ-2: Значения стабильнее — от 21 до 53, с редкими пиками до 40–53 ближе к концу.

Вывод: В ТЕСТ-1 наблюдалась более высокая нагрузка на CPU с точки зрения количества выполняющихся процессов.

2. Память

• swpd: Остаётся постоянным (203) в обоих тестах — своп не используется.
• free:
  ТЕСТ-1: 203–309 (в целом стабильно, с небольшим ростом к концу).
  ТЕСТ-2: 220–407 (больше свободной памяти, особенно в начале).
• buff/cache:
  ТЕСТ-1: cache снижается с ~7000 до ~6743.
  ТЕСТ-2: cache стабилен вокруг 6800.

Вывод: В ТЕСТ-2 система имела больше свободной памяти и стабильный кэш, что может указывать на более эффективное управление памятью.

3. Ввод-вывод (io_bo)

• ТЕСТ-1: bo (блоки вывода) варьируется от 56 до 71.
• ТЕСТ-2: bo находится в диапазоне 48–59.

Вывод: В ТЕСТ-1 была более высокая активность записи на диск.

4. Системные события (system_in, system_cs)

• Прерывания (in):
  ТЕСТ-1: 11479–12837
  ТЕСТ-2: 11677–12837
• Контекстные переключения (cs):
  ТЕСТ-1: 9649–10587
  ТЕСТ-2: 10157–10587

Вывод: Оба теста показывают высокую системную активность, но в ТЕСТ-2 немного выше количество контекстных переключений в начале, что может указывать на более интенсивную работу с потоками.

5. Загрузка CPU

• user (us): В обоих тестах стабильно ~96%.
• system (sy):
  ТЕСТ-1: 3% в начале, к концу растёт до 4%.
  ТЕСТ-2: стабильно 4%.

Вывод: Оба теста показывают практически полную загрузку CPU пользовательскими процессами. Небольшой рост системного времени в ТЕСТ-1 может быть связан с увеличением количества системных вызовов или операций ввода-вывода.

Итоговые выводы:

1. Оба теста показывают высокую нагрузку на CPU, преимущественно в пользовательском режиме.
2. ТЕСТ-1 характеризуется более высокой активностью ввода-вывода и большим использованием кэша.
3. ТЕСТ-2 демонстрирует более стабильное использование памяти и меньшее количество блоков вывода.
4. В ТЕСТ-1 наблюдается рост системной нагрузки к концу теста, что может свидетельствовать о накоплении системных событий или увеличении количества переключений контекста.

Итог

Для данного сценария нагрузочного тестирования тестовая база данных locale_ru : Collate = ru_RU.UTF-8 показывает прирост производительности ~7%, по сравнению с locale_c : Collate = С

Продолжение