💡 Что такое база данных и зачем нужны таблицы?

Представьте себе огромную библиотеку, полную полок с книгами. Чтобы быстро найти нужную книгу, вы используете каталог, который помогает организовать книги по авторам, жанрам или годам издания. Примерно так же устроены и базы данных — системы структурированного хранения информации, позволяющие эффективно искать, обновлять и анализировать данные.
(Позвольте мне далее по тексту использовать сокращённое наименование базы данных — БД.)

На первых этапах знакомства с базами данных у меня сформировалось предвзятое мнение, что все базы данных непременно представляют собой таблицы с рядами записей. Однако реальность гораздо разнообразнее, и далее Мы рассмотрим какие вообще виды баз данных существуют.
(такое представление имеет место быть, так-как самые часто встречающиеся базы данных соответствуют именно такому описанию)

📊 Виды БД (за 4-е место в топе выдаем шоколадку 😄)
(Прошу Вас относится к этому рейтингу как к ориентировочному показателю, иллюстрирующему общую картину популярности различных типов баз данных на сегодняшний день. Приведённые проценты отображают частоту использования каждой категории технологий среди разработчиков и компаний.)

• Реляционные БД (~ 70 %) 🏆

• Документные БД (~ 20%) 🥈

• Ключ-значение БД (~ 10-15%) 🥉

• Облачные БД (~ 10%) 🍫

• Графовые БД (~ 5-7%)

• Колоночные БД (~ 3-5%)

• Файловые БД (~ 2-3%)

• Объектно-ориентированные БД (< 1%)

❗️❗️ Ввиду того, что данная статья посвящена именно реляционным базам данных, основное внимание сосредоточено на этом типе. В последующих статьях мы также рассмотрим и другие виды БД.

Мы можем заметить, что наиболее распространенным видом БД являются реляционные и это не просто так !

🌟 Почему реляционные базы настолько популярны?

• Зрелость модели - реляционная модель, предложенная в 20-м веке ученым Эдгаром Коддом успела пройти проверку временем

• Универсальность и совместимость технологий - существует более 160 реляционных СУБД, поддерживающих общие стандарты и язык запросов SQL. Это в значительной степени облегчает миграцию данных и совместимость решений

• Поддержка транзакций - реляционные БД предлагают мощный функционал поддержки транзакций, обеспечивающий сохранение целостности данных (что является ключевым параметром многих решений)

• Поддержка большинства корпоративных систем (ERP, CRM, BI) - данные системы исторически проектировались под реляционную модель, поэтому в настоящее время большинство крупных и средний предприятий имеют готовые решения, построенные на базе реляционных моделей.

Простота проектирования схем и стандартизация подходов, делают выбор реляционных моделей хранения данных более предпочтительным в отношении других подходов.
Но, реляционные БД это не "серебряная пуля", решающая все проблемы и не имеющая недостатков.

😱 Проблемы реляционных БД

• Масштабирование - реляционные БД обладают определенными трудностями при горизонтальном масштабировании (чаще всего по причине, что каждая отдельная БД размещена на физическом сервере с ограниченными ресурсами, что значительно усложняет задачу разделения нагрузки)

• Поддержка ACID-транзакций - не смотря на то, что поддержка транзакций является преимуществом реляционных БД, одновременно с этим она может быть и ее недостатком. Поддержание таких свойств, как: атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation), надежность (Durability) может потребовать значительные ресурсы, что в свою очередь повлияет на скорость и производительность системы.

• Проблема самой концепции - данные в реляционной БД хранятся разнесенные по таблицам (часто стараются хранить нормализованные данные), со временем увеличивается объемы таблиц, усложняются JOIN- запросы (одна из функций языка SQL), что в свою очередь снижает производительность при работе с данными. (не смотря на наличие индексов)
  
  Перечисленные Выше проблемы (а это далеко не все, но, как мне кажется, одни из основных проблем) влияют на производительность системы и требуют поиска дорогих решений для обхода данных особенностей при использовании реляционных БД

Выше Мы уже упомянули, что основой реляционный БД являются таблицы.
Давайте посмотрим поближе на то, как выглядят таблицы в реляционной БД и что это такое

Рассмотрим пример таблицы на иллюстрации. Мы видим, что таблицы в реляционной БД имеют свою структуру, описание которой приведем ниже.

Структура таблицы

• Таблица (отношение) - основной элемент реляционной БД, представляет собой массив данных, состоящий из строк (записи) и столбцов (атрибуты)

• Атрибуты - свойства, определяющие характеристики каждой сущности. Каждому атрибуту соответствует определенный тип данных (числовой, формат даты, строковый).
  (Идентификатор, чаще всего, не приходит вместе с данными, а создается искусственно в момент создания записи в таблице)

• Записи (кортеж) - строка таблицы, которая содержит совокупность значений атрибутов.

• Мощность - количество записей в таблице (в нашей таблице мощность = 4)

• Размерность - количество атрибутов, описывающих характеристики сущности (в нашей таблице размерность = 4)

Сейчас мы рассмотрели лишь одну таблицу как наглядный пример. Однако на практике в реляционных базах данных встречается огромное множество таблиц, каждая из которых должна учитывать такие ключевые элементы, как мощность, размерность и кортежность. Понимание этих аспектов существенно упрощает процесс проектирования и последующего управления таблицами

Давайте теперь рассмотрим то, как может выглядеть самая простая схема реляционной БД на примере нашей таблицы (Продукты) и двух новых таблиц. (Покупатели и Покупки).

Ранее мы отмечали, что в реляционной базе данных обычно имеется множество таблиц, каждая из которых описывает отдельную сущность. Все эти таблицы связаны между собой с помощью ключевых механизмов — первичных (PK) и внешних (FK) ключей.

Посмотрите на нашу схему сверху: таблица «Покупки» связана с таблицей «Покупатели» с помощью внешнего ключа «Идентификатор покупателя», который фактически ссылается на «Идентификатор» в таблице покупателей. Этот идентификатор гарантирует уникальную привязку каждого покупателя к конкретной покупке.

Аналогично построена связь между таблицей «Покупки» и таблицей «Продукты». Таким образом, любая покупка может быть легко ассоциирована с соответствующим товаром.

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

Теперь перейдем непосредственно к основным типам связей между таблицами.

🔗 Типы связей

• Один-к-одному - одна запись первой таблицы, связана с одной записью второй таблицы. Данный тип связи встречается не столь часто, как два других.

• Один-ко-многим - одна запись первой таблицы, связана с множеством записей второй таблицы. Самый распространенный тип связи на практике.

• Многие-ко-многим - множество записей первой таблицы, связаны с множеством записей второй таблицы. Так же достаточно распространенный тип связи, который на практике требует создание таблицы-посредника (связующая таблица).
  
  (Ниже приведены иллюстрации к видам связи. Прошу Вас учитывать, что примеры гипотетические)

Завершая рассказ об основах реляционных баз данных, особое внимание уделим важной теме — первичным (PK) и внешним (FK) ключам.

Хотя формально использование ключей необязательно, практически их отсутствие нарушает фундаментальные принципы проектирования реляционных БД. Ключи позволяют точно идентифицировать записи, устанавливать связи между таблицами и поддерживать целостность данных.

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

🔑 Рассмотрим виды ключей

Первичный ключ (Primary Key)
Первичный ключ — это атрибут или набор атрибутов, однозначно идентифицирующих каждую запись в таблице. Основная характеристика первичного ключа — его уникальность: каждая запись обладает уникальным значением.

При создании первичного ключа накладываются следующие ограничения:

• Уникальность (каждая запись имеет уникальное значение ключа)

• Обязательность заполнения (атрибут не может быть пустым)

• Постоянство (значение ключа должно быть постоянным)

Первичные ключи удобно классифицировать по двум критериям:

Способ формирования

• Естественный PK - атрибут, присутствующий в самих данных и обладающий уникальностью (например ИНН гражданина, серийный номер товара)

• Искусственный PK (суррогатный ) - специально созданный атрибут, формируемый системой автоматически. (самый простой способ - автоинкрементируемое целое число)

Количество элементов

• Простой PK - включает единственный атрибут.

• Составной PK - формируется из нескольких атрибутов, с целью достижения уникальности.

Важно отметить следующее, что естественный или искусственные ключи могут быть как простыми, так и составными.

Внешний ключ (Foreign Key)
Внешний ключ — это атрибут или набор атрибутов, ссылающийся на значение первичного ключа другой таблицы. Благодаря внешним ключам обеспечивается согласованность и целостностью данных.

Особенности внешнего ключа

• Допускает пустые значения, если связь между таблицами необязательна

• Проверяет существование соответствующей записи в родительской таблице

• Гарантирует соблюдение целостности данных (любая запись ссылается на реально существующую запись в другой таблице)

• Определяет иерархические или ассоциативные связи между таблицами. (тут мы имеем ввиду ранее рассмотренные связи один-к-одному, один-ко-многим, многие-ко-многим)

Итак, мы завершили знакомство с основами реляционных баз данных. Рассмотрели базовые понятия, структуру таблиц, типы связей и способы организации ключей.

❤️ Спасибо за Ваше внимание! Надеюсь, эта статья помогла разобраться с ключевыми принципами реляционных баз данных.

📱 Оставайтесь с нами, чтобы получать свежие публикации и полезную информацию по системному анализу в нашей группе системного анализа

Хорошего дня, друзья!

Предисловие

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

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Эксперименты с Демобазой 2.0

"Демобаза 2.0" нагрузочное тестирование : СУБД оказалась устойчива к выбору между Join и коррелированным подзапросом.

Начало экспериментов "IN vs EXISTS"

Очередной раунд тестов: EXISTS против IN в условиях параллелизма.

Тестовый запрос-1 : IN

SELECT DISTINCT a.country

FROM airports_data a

JOIN routes r ON (r.arrival_airport = a.airport_code)

WHERE duration IN

(

'09:45:00' , '11:50:00' , '02:40:00' , '05:50:00' , '15:25:00' , '04:30:00' , '11:00:00' , '07:15:00' , '12:40:00' , '03:40:00' , '05:15:00' , '08:35:00' , '10:35:00' , '07:30:00' , '09:35:00' , '04:15:00' , '11:45:00' , '04:05:00' , '01:10:00' , '19:50:00' , '07:55:00' , '01:35:00' , '16:05:00' , '08:15:00' , '04:00:00' , '08:45:00' , '12:25:00' , '16:40:00' , '07:25:00' , '01:50:00' , '14:35:00' , '12:45:00' , '01:20:00' , '02:55:00' , '20:20:00' , '10:45:00' , '02:45:00' , '12:55:00' , '08:25:00' , '00:45:00' , '02:00:00' , '01:15:00' , '08:00:00' , '04:10:00' , '11:35:00' , '16:45:00' , '17:15:00' , '14:40:00' , '15:35:00' , '15:50:00' , '13:30:00' , '04:25:00' , '01:25:00' , '14:10:00' , '15:15:00' , '08:55:00' , '07:00:00' , '05:05:00' , '06:45:00' , '14:20:00' , '09:50:00' , '08:10:00' , '11:30:00' , '13:45:00' , '04:35:00' , '01:30:00' , '15:10:00' , '05:25:00' , '05:20:00' , '16:30:00' , '14:45:00' , '00:40:00' , '13:15:00' , '12:50:00' , '09:05:00' , '17:30:00' , '13:05:00' , '13:10:00' , '10:50:00' , '07:10:00' , '05:00:00' , '10:40:00' , '03:25:00' , '09:00:00' , '13:00:00' , '10:20:00' , '16:20:00' , '08:05:00' , '07:40:00' , '14:30:00' , '16:10:00' , '03:50:00' , '08:30:00' , '05:40:00' , '06:20:00' , '05:30:00' , '11:05:00' , '11:55:00' , '04:20:00' , '06:40:00' );

План выполнения тестового запроса-1 : IN

HashAggregate (cost=282.56..284.86 rows=230 width=54) (actual time=5.604..5.609 rows=17 loops=1)

Group Key: a.country

Batches: 1 Memory Usage: 40kB

-> Nested Loop (cost=0.54..276.22 rows=2534 width=54) (actual time=0.104..3.559 rows=2534 loops=1)

-> Seq Scan on routes r (cost=0.25..185.13 rows=2534 width=4) (actual time=0.049..1.650 rows=2534 loops=1)

Filter: (duration = ANY ('{09:45:00,11:50:00,02:40:00,05:50:00,15:25:00,04:30:00,11:00:00,07:15:00,12:40:00,03:40:00,05:15:00,08:35:00,10:35:00,07:30:00,09:35:00,04:15:00,11:45:00,04:05:00,01:10:00,19:50:00,07:55:00,01:35:

00,16:05:00,08:15:00,04:00:00,08:45:00,12:25:00,16:40:00,07:25:00,01:50:00,14:35:00,12:45:00,01:20:00,02:55:00,20:20:00,10:45:00,02:45:00,12:55:00,08:25:00,00:45:00,02:00:00,01:15:00,08:00:00,04:10:00,11:35:00,16:45:00,17:15:00,14:40:00,

15:35:00,15:50:00,13:30:00,04:25:00,01:25:00,14:10:00,15:15:00,08:55:00,07:00:00,05:05:00,06:45:00,14:20:00,09:50:00,08:10:00,11:30:00,13:45:00,04:35:00,01:30:00,15:10:00,05:25:00,05:20:00,16:30:00,14:45:00,00:40:00,13:15:00,12:50:00,09:

05:00,17:30:00,13:05:00,13:10:00,10:50:00,07:10:00,05:00:00,10:40:00,03:25:00,09:00:00,13:00:00,10:20:00,16:20:00,08:05:00,07:40:00,14:30:00,16:10:00,03:50:00,08:30:00,05:40:00,06:20:00,05:30:00,11:05:00,11:55:00,04:20:00,06:40:00}'::int

erval[]))

Rows Removed by Filter: 3258

-> Memoize (cost=0.29..0.39 rows=1 width=58) (actual time=0.000..0.000 rows=1 loops=2534)

Cache Key: r.arrival_airport

Cache Mode: logical

Hits: 2461 Misses: 73 Evictions: 0 Overflows: 0 Memory Usage: 11kB

-> Index Scan using airports_data_pkey on airports_data a (cost=0.28..0.38 rows=1 width=58) (actual time=0.007..0.007 rows=1 loops=73)

Index Cond: (airport_code = r.arrival_airport)

Тестовый запрос-2 : EXISTS

SELECT DISTINCT a.country

FROM airports_data a

WHERE EXISTS (

SELECT 1

FROM routes r

WHERE r.arrival_airport = a.airport_code

AND r.duration IN (

'09:45:00', '11:50:00', '02:40:00', '05:50:00', '15:25:00', '04:30:00', '11:00:00', '07:15:00',

'12:40:00', '03:40:00', '05:15:00', '08:35:00', '10:35:00', '07:30:00', '09:35:00', '04:15:00',

'11:45:00', '04:05:00', '01:10:00', '19:50:00', '07:55:00', '01:35:00', '16:05:00', '08:15:00',

'04:00:00', '08:45:00', '12:25:00', '16:40:00', '07:25:00', '01:50:00', '14:35:00', '12:45:00',

'01:20:00', '02:55:00', '20:20:00', '10:45:00', '02:45:00', '12:55:00', '08:25:00', '00:45:00',

'02:00:00', '01:15:00', '08:00:00', '04:10:00', '11:35:00', '16:45:00', '17:15:00', '14:40:00',

'15:35:00', '15:50:00', '13:30:00', '04:25:00', '01:25:00', '14:10:00', '15:15:00', '08:55:00',

'07:00:00', '05:05:00', '06:45:00', '14:20:00', '09:50:00', '08:10:00', '11:30:00', '13:45:00',

'04:35:00', '01:30:00', '15:10:00', '05:25:00', '05:20:00', '16:30:00', '14:45:00', '00:40:00',

'13:15:00', '12:50:00', '09:05:00', '17:30:00', '13:05:00', '13:10:00', '10:50:00', '07:10:00',

'05:00:00', '10:40:00', '03:25:00', '09:00:00', '13:00:00', '10:20:00', '16:20:00', '08:05:00',

'07:40:00', '14:30:00', '16:10:00', '03:50:00', '08:30:00', '05:40:00', '06:20:00', '05:30:00',

'11:05:00', '11:55:00', '04:20:00', '06:40:00'

));

План выполнения тестового запроса-2 : EXISTS

Unique (cost=299.91..300.27 rows=73 width=54) (actual time=3.071..3.114 rows=17 loops=1)

-> Sort (cost=299.91..300.09 rows=73 width=54) (actual time=3.069..3.076 rows=73 loops=1)

Sort Key: a.country

Sort Method: quicksort Memory: 25kB

-> Nested Loop (cost=191.75..297.65 rows=73 width=54) (actual time=2.457..2.942 rows=73 loops=1)

-> HashAggregate (cost=191.47..192.19 rows=73 width=4) (actual time=2.408..2.421 rows=73 loops=1)

Group Key: r.arrival_airport

Batches: 1 Memory Usage: 24kB

-> Seq Scan on routes r (cost=0.25..185.13 rows=2534 width=4) (actual time=0.048..1.834 rows=2534 loops=1)

Filter: (duration = ANY ('{09:45:00,11:50:00,02:40:00,05:50:00,15:25:00,04:30:00,11:00:00,07:15:00,12:40:00,03:40:00,05:15:00,08:35:00,10:35:00,07:30:00,09:35:00,04:15:00,11:45:00,04:05:00,01:10:00,19:50:00,07:

55:00,01:35:00,16:05:00,08:15:00,04:00:00,08:45:00,12:25:00,16:40:00,07:25:00,01:50:00,14:35:00,12:45:00,01:20:00,02:55:00,20:20:00,10:45:00,02:45:00,12:55:00,08:25:00,00:45:00,02:00:00,01:15:00,08:00:00,04:10:00,11:35:00,16:45:00,17:15:

00,14:40:00,15:35:00,15:50:00,13:30:00,04:25:00,01:25:00,14:10:00,15:15:00,08:55:00,07:00:00,05:05:00,06:45:00,14:20:00,09:50:00,08:10:00,11:30:00,13:45:00,04:35:00,01:30:00,15:10:00,05:25:00,05:20:00,16:30:00,14:45:00,00:40:00,13:15:00,

12:50:00,09:05:00,17:30:00,13:05:00,13:10:00,10:50:00,07:10:00,05:00:00,10:40:00,03:25:00,09:00:00,13:00:00,10:20:00,16:20:00,08:05:00,07:40:00,14:30:00,16:10:00,03:50:00,08:30:00,05:40:00,06:20:00,05:30:00,11:05:00,11:55:00,04:20:00,06:

40:00}'::interval[]))

Rows Removed by Filter: 3258

-> Index Scan using airports_data_pkey on airports_data a (cost=0.28..1.46 rows=1 width=58) (actual time=0.006..0.006 rows=1 loops=73)

Index Cond: (airport_code = r.arrival_airport)

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

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

Среднее превышение операционной скорости при использовании EXISTS составило 23.25%.

Ожидания СУБД

Среднее снижение ожиданий СУБД при использовании EXISTS составило 90.60%.

Итог

Использование паттерна EXIST повышает производительность СУБД в среднем на 20%.

Предисловие

Использование нейросетей для оптимизации баз данных кажется перспективным направлением, но реальная эффективность таких систем требует тщательной проверки. В данном исследовании проанализирована способность нейросетевой модели точно прогнозировать производительность СУБД PostgreSQL в условиях экстремальной параллельной нагрузки. Результаты демонстрируют систематические ошибки AI, связанные с неспособностью учесть динамические аспекты работы СУБД.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Методология эксперимента

Для тестирования прогностической способности нейросети была развернута тестовая среда PostgreSQL 17 с конфигурацией CPU=8 ядер, RAM=8GB. Создана таблица pgbench_test с 1 млн записей, выполнялся запрос с соединением по внешнему ключу. Анализировались два метода доступа: последовательное сканирование (Seq Scan) и индексное сканирование (Index Only Scan) с покрывающим индексом idx_pgbench_test_bid_abalance.

Подробности эксперимента(прогноз и анализ нейросети)

Использование нейросети для прогноза производительности СУБД PostgreSQL

Прогноз нейросети и его несоответствие реальности

Нейросеть, проанализировав планы выполнения запросов, выдала категоричный прогноз:

• Index Only Scan: оптимальная производительность (~2.5 ms) даже при 22 параллельных сессиях

• Seq Scan: катастрофическая деградация производительности (+400%) при превышении 10 сессий

Реальные результаты нагрузочного тестирования показали принципиально иную картину:

• Среднее преимущество Seq Scan составило 9%

• В самой финальной фазе теста Index Only Scan демонстрировал лучшую производительность

• Оба метода показали сопоставимую устойчивость к нагрузке

Критический анализ ошибок нейросетевого прогноза

1. Неспособность моделировать динамическое кэширование

Нейросеть основывалась на статической стоимостной модели, игнорируя эффект прогрева БД. В реальности Seq Scan выигрывал от полного размещения данных в shared_buffers, что нивелировало его основной недостаток - физический I/O.

2. Игнорирование конкуренции за индексные структуры

Модель недооценила contention в B-деревьях при высокой параллельности. Index Only Scan, предсказанный как идеальное решение, столкнулся с блокировками страниц индекса при одновременном доступе 22 сессий.

3. Ошибочная оценка масштабируемости

Нейросеть переоценила линейность индексного доступа и недооценила эффективность параллельного Seq Scan. Распределенная нагрузка workers оказалась стабильнее концентрированной нагрузки на индекс.

Системные ограничения нейросетей в экспертизе СУБД

Проведенный эксперимент выявил фундаментальные проблемы применения AI для анализа производительности БД:

Статичность моделей - нейросети работают с моментальными снимками системы, не учитывая временные аспекты работы СУБД.

Игнорирование конкурентного доступа - модели не способны адекватно предсказать поведение системы при одновременном доступе множества процессов.

Неучет аппаратных ограничений - прогноз не учитывал реальные особенности управления памятью и планирования задач в PostgreSQL.

Рекомендации и выводы

Для эффективного использования нейросетей в экспертизе производительности PostgreSQL необходимо:

1. Обогащение данных обучения динамическими метриками (pg_stat_, pg_statio_)

2. Учет временны́х характеристик - продолжительности теста, эффекта прогрева

3. Моделирование реальной конкуренции - тестирование в условиях, приближенных к продуктивным.

4. Верификация прогнозов обязательным нагрузочным тестированием

Заключение

Нейросетевые модели демонстрируют ограниченную эффективность в прогнозировании поведения СУБД под высокой параллельной нагрузкой. Традиционные методы экспертизы с обязательным эмпирическим тестированием сохраняют критическую важность для принятия архитектурных решений в системах управления базами данных.

Результаты новых исследований указывают на необходимость создания отдельного направления по оптимизации параллельных процессов в СУБД PostgreSQL

Казань, 21.11.2025 – По результатам серии экспериментов, проведенных, был выявлен фундаментальный пробел в современных методологиях оптимизации производительности систем управления базами данных (СУБД). Установлено, что традиционные паттерны и методики оптимизации демонстрируют резкое снижение эффективности или полную неприменимость в средах с высоким уровнем параллельной обработки транзакций.

Эмпирические данные свидетельствуют о том, что при значительной конкурентной нагрузке, когда множество процессов обращаются к данным одновременно, классические подходы, такие как тонкая настройка отдельных запросов или индексация, оказываются недостаточными. Вместо ожидаемого линейного роста производительности наблюдаются нелинейные эффекты, включая интенсивную борьбу за ресурсы (contention), блокировки (locks) и деградацию общей пропускной способности системы.

На основании полученных результатов был сделан вывод о назревшей необходимости системного пересмотра принципов анализа и оптимизации СУБД. Для обеспечения устойчивой работы высоконагруженных информационных систем на базе СУБД PostgreSQL требуется выделение и глубокая проработка нового специализированного подраздела, посвященного исключительно оптимизации параллельных процессов (Parallel Processes Optimization).

Введение данной дисциплины предполагает фокусировку на таких аспектах, как:

• Анализ и минимизация конфликтов блокировок на уровне строк и таблиц.

• Оптимизация работы планировщика задач и управления памятью в условиях высокой конкуренции.

• Разработка специализированных метрик для диагностики узких мест, специфичных для параллельной работы.

• Создание рекомендаций по проектированию схемы данных и логики приложений, ориентированных на параллелизм.

Этот шаг является закономерным ответом на вызовы, связанные с ростом объемов данных и требований к масштабируемости современных приложений. Новая парадигма оптимизации позволит вывести управление производительностью СУБД PostgreSQL на качественно новый уровень, обеспечивая стабильность и эффективность в высокопараллельных средах.

Контактная информация :

• Ринат Сунгатуллин

• kznalp@yandex.ru

• Postgres DBA

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Предисловие:

Принято считать, что выбор между JOIN и коррелированным подзапросом — одна из ключевых задач оптимизации, способная кардинально повлиять на нагрузку базы данных. В качестве эксперимента, было проведено нагрузочное тестирование, используя Демобазу 2.0 в качестве полигона и vmstat для мониторинга изменений со стороны инфраструктуры, готовясь наглядно продемонстрировать превосходство одного подхода над другим.

Однако результаты оказались неожиданными. Исследование показало практическое отсутствие существенного влияния выбранной структуры запроса на общую производительность СУБД и сервера. В данной статье показано, что в контексте современной оптимизации запросов и мощного аппаратного обеспечения, "страшилка" о катастрофических последствиях использования коррелированных подзапросов часто преувеличена. Нагрузочное тестирование выявило, что СУБД успешно справляется с обоими типами запросов, а реальное влияние на метрики vmstat оказалось малым, что позволяет разработчикам в подобных случаях делать выбор, основываясь на читаемости кода, а не на гипотетических рисках для производительности.

Демобаза 2.0

PG_EXPECTO 4 + Демобаза 2.0 : использование тестовой базы данных для нагрузочного тестирования СУБД

Тестовая виртуальная машина

CPU = 8

RAM = 8GB

PostgreSQL 17

Тестовый сценарий-4.1 (JOIN)

-- Запросы с JOIN

CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario4';

WITH seats_available AS

( SELECT airplane_code, fare_conditions, count( * ) AS seats_cnt

FROM bookings.seats

GROUP BY airplane_code, fare_conditions

), seats_booked AS

( SELECT flight_id, fare_conditions, count( * ) AS seats_cnt

FROM bookings.segments

GROUP BY flight_id, fare_conditions

), overbook AS (

SELECT f.flight_id, r.route_no, r.airplane_code, sb.fare_conditions,

sb.seats_cnt AS seats_booked,

sa.seats_cnt AS seats_available

FROM bookings.flights AS f

JOIN bookings.routes AS r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN seats_booked AS sb ON sb.flight_id = f.flight_id

JOIN seats_available AS sa ON sa.airplane_code = r.airplane_code

AND sa.fare_conditions = sb.fare_conditions

WHERE sb.seats_cnt > sa.seats_cnt

)

SELECT count(*) overbookings,

CASE WHEN count(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END verdict

INTO test_rec

FROM overbook;

return 0 ;

END

$$ LANGUAGE plpgsql;

Тестовый сценарий-4.2 (Коррелированный подзапрос)

Создание индексов

demo=# CREATE INDEX CONCURRENTLY idx_seats_airplane_fare ON bookings.seats(airplane_code, fare_conditions);

CREATE INDEX

demo=# CREATE INDEX CONCURRENTLY idx_segments_flight_fare ON bookings.segments(flight_id, fare_conditions);

CREATE INDEX

demo=# CREATE INDEX CONCURRENTLY idx_routes_no_validity ON bookings.routes(route_no, validity);

CREATE INDEX

Изменение SQL запроса

-- коррелированный подзапрос

CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario4';

WITH seats_agg AS MATERIALIZED (

SELECT

airplane_code,

fare_conditions,

COUNT(*) AS seats_total

FROM bookings.seats

GROUP BY airplane_code, fare_conditions

)

SELECT

COUNT(*) AS overbookings,

CASE WHEN COUNT(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END AS verdict

INTO test_rec

FROM (

SELECT 1

FROM bookings.flights f

JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN (

SELECT

flight_id,

fare_conditions,

COUNT(*) AS seats_booked

FROM bookings.segments

GROUP BY flight_id, fare_conditions

) sb ON sb.flight_id = f.flight_id

WHERE sb.seats_booked > (

SELECT sa.seats_total

FROM seats_agg sa

WHERE sa.airplane_code = r.airplane_code

AND sa.fare_conditions = sb.fare_conditions

)

) overbooked;

Анализ результатов нагрузочного тестирования - производительность СУБД

Операционная скорость СУБД

Средняя разница операционной скорости СУБД при использовании JOIN и Коррелированного подзапроса составила 0.58%.

Вывод по результатам анализа метрик производительности СУБД

Использование для тестового запроса JOIN или Коррелированного подзапроса - не оказывает влияния на производительность СУБД в целом и тестового сценария в частности.

Принято считать, что коррелированные подзапросы — это зло, ведущее к проблемам N+1, а JOIN — панацея для производительности. Статья описывает проверку догмы в ходе нагрузочного тестирования, будучи увереным в результатах еще до старта экспериментов.

Результат ошеломил: в некоторых сценариях коррелированный подзапрос показал кардинальное превышение производительности над классическим JOIN. Это наглядный пример того, как теоретическая стоимость запроса, которую мы видим в EXPLAIN, может быть совершенно не релевантна при оценке реальной производительности системы в целом.

Статья — это очередное напоминание всем разработчикам и DBA: в мире СУБД нет абсолютных истин, а любое, даже самое «логичное» правило, нужно проверять экспериментально.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Задача

Провести экспериментальную проверку гипотезы о влиянии коррелированного запроса на производительность СУБД .

В этом тесте PostgreSQL 16 быстрее выполнил вариант с JOIN + GROUP BY: ~0.415 ms против ~0.803 ms для коррелированного подзапроса.

План JOIN: Hash Right Join + HashAggregate с одним проходом по таблицам — меньше итераций и накладных, чем у подзапроса.

План подзапроса: 25 запусков под-плана с Bitmap Scan по orders (классический N+1-эффект), поэтому медленнее.

Вывод: в PostgreSQL коррелированные подзапросы легко деградируют в N+1; предпочитайте set-based JOIN и проверяйте планы через EXPLAIN ANALYZE.

Источник:

JOIN vs. Коррелированный подзапрос: Разрушаем миф о «N+1» на 4 СУБД

Экспериментальная проверка гипотезы

Виртуальная машина

CPU = 8

RAM = 8GB

Postgres Pro (enterprise certified) 17.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

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

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

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

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

Для данной виртуальной машины , данной версии СУБД и данного характера нагрузки среднее снижение операционной скорости в ходе нагрузочного тестирования, для сценария использующего JOIN составило 188%.💥

Продолжение

Опасный мираж оптимизации: почему нейросетевые советы по СУБД PostgreSQL убивают производительность под нагрузкой.