Девять лет назад мы создали базу данных, которая не должна была никого особенно интересовать — только учить SQL. Она была простой, как таблица умножения, и вроде бы не менялась. Но как оказалось, даже самая скромная база может стать мостом между разными культурами, языками и поколениями разработчиков — если её не трогать слишком долго.

Демобаза

Первую версию демобазы мы сделали в 2016 году. Идея была в том, чтобы не создавать каждый раз пресловутую «таблицу T», а вместо этого иметь готовую учебно-тренировочную базу с понятной и известной структурой. Такую базу можно использовать в публикациях, на ней можно учиться языку SQL.

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

Первую версию мы готовили для книги Бориса Асеновича Новикова «Основы технологий баз данных», и с тех пор она разошлась и по другим книгам, статьям и курсам, и, что приятно, не только нашим. По предложению Бориса Асеновича предметной областью нашей базы стали авиаперевозки по России. В базе всего восемь таблиц и некоторое количество представлений. Она содержит около сотни российских городов, между которыми летают самолеты и перевозят пассажиров.

Отлично, сделали мы демобазу, а зачем сейчас в ней что-то менять?

Во-первых и в самых главных, нам стала мешать ограниченность географии. Впервые мы столкнулись с этим при переводе моей книги «PostgreSQL изнутри» на английский язык. Думаю, не каждый из вас сразу найдет на этой карте Нерюнгри или Усть-Кут, а иностранцам эти названия даже выговорить трудно. Хочется, чтобы переводные материалы были доступны и интересны всем.

Во-вторых, база имеет небольшой объем — 2,5 гигабайта в максимальной конфигурации. Это более чем скромно по современным меркам, тем более если надо показать преимущества секционирования или шардирования.

Кроме того, опыт использования диктовал и другие изменения, без которых, в принципе, можно ��ить, но которые давно хотелось реализовать.

Безусловно, у нас были сомнения: не выбрать ли какую-то совсем другую тему? Авиаперевозки сильно пострадали сначала от ковида, а потом и от нынешней геополитической обстановки. Но мы решили, что покажем мир таким, каким хотим его видеть. Ведь Постгрес должен объединять людей, а не разъединять их.

Итак, давайте смотреть, что же у нас вышло.

Мы добавили в базу все более или менее крупные аэропорты, имеющие трехсимвольный код ИАТА (Международная ассоциация воздушного транспорта). Таких — пять с половиной тысяч. Конечно, большинство из них не участвует в авиасообщении, но в базе они все есть. Те, что участвуют, показаны на картинке оранжевыми кружочками; о них чуть позже.

Такое количество аэропортов уже позволяет, например, демонстрировать индексы GiST и SP-GiST для точек.

К аэропортам пришлось добавить страну. Как минимум, в разных странах (а их у нас 230) есть одноименные города, поэтому одного только города уже недостаточно.

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

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

Создавать наполнение для нее тоже оказалось занятной задачей. Я уже не говорю, что в открытом доступе нет полной и актуальной базы ИАТА, это ладно. Но в старой демобазе названия аэропортов и городов хранятся на двух языках, русском и английском, и теперь у нас добавляется 5 500 тысяч названий городов и столько же аэропортов. Как перевести 11 000 наименований и не сойти с ума?

Какое-то время назад я бы задумался о том, чтобы распарсить Википедию, но сейчас, к счастью, нам завезли ИИ. Я использовал DeepSeek, и он довольно бодро все перевел. Однако нужен глаз да глаз: я все перепроверял, в случае сомнений заглядывал в ту же википедию или заставлял и-интеллект подумать получше. Но в целом кажется, что времени сэкономил много. А в конце попросил ИИ оценить согласованность перевода, и он нашел несколько несоответствий, случайно пропущенные названия, перепутанные русскую «эс» и латинскую «це» и тому подобное.

Вообще — в качестве лирического отступления — ИИ в его текущем виде напоминает мне стажера. Очень работоспособного, исполнительного, в принципе, даже понятливого, но проверять надо каждый шаг, потому что соврет и не покраснеет сам не поймет, что соврал.

Таблицы самолетов и мест остались по большому счету без изменений.

Мы исправили старую ошибку, переименовав aircrafts в airplanes. Дело в том, что слова aircrafts не существует, aircraft не изменяется во множественном числе. Такой вот английский язык, в котором исключений больше, чем правил.

Тут у нас был выбор. Можно было оставить просто aircraft, но это не согласуется с остальными названиями, возникали бы вопросы. Можно было перейти на единственное число для всех таблиц. В этом тоже есть своя логика, поскольку при создании таблицы создается одноименный тип записи, а запись — это единственное число.

Но в конце концов мы решили сменить название одной таблицы самолетов. Оно менее универсальное: aircraft — это не только самолеты, но и, например, вертолеты. Но у нас все равно кроме самолетов ничего нет, так что нам годится.

Мы добавили столбец с крейсерской скоростью (speed). Ну и, конечно, обновили авиапарк. Взяли модели посовременее, в основном «Боинги» и «Эйрбасы». А вместо «Цессны» теперь летают «Эмбраэры» и «Бомбардье», поскольку расстояния увеличились.

А вот и расстояния:

Вот так теперь выглядят перелеты. Тут мы можем заметить своеобразные кластеры из городов, в которых угадываются очертания стран. Всего 17 стран: конечно же, Россия, несколько стран в Европе, дальше Индия, Китай, Япония, Штаты и Канада, и, как ни странно, Чили и Новая Зеландия.

Почему такой выбор? С самого начала было понятно, что вообще все страны сделать не получится — слишком большой объем перелетов, — поэтому надо было выбрать несколько по какому-то признаку. Очевидно, страны должны иметь какое-то отношение к Постгресу. Если взять все страны, где живут разработчики, или все страны, где проводились конференции, все равно получается много. В итоге в базу попали страны основных разработчиков (major contributor), плюс пару стран я еще добавил от себя.

Как я говорил, мы не хотели раздувать базу данных, а вот сделать ее разнообразнее — хотели. Путь здесь такой: надо в те же таблицы положить более интересные данные. И мы решили, что будет хорошо время от времени (раз в месяц) менять маршруты.

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

С точки зрения схемы данных нам, по большому счету, пришлось менять немногое: мы сделали представление routes полноценной таблицей и перенесли в нее часть столбцов из flights.

Раньше представление routes постфактум вычисляло маршруты по существующим рейсам, а теперь это та таблица, которая действительно определяет маршруты. Маршрут говорит о том, из какого аэропорта в какой летим, по каким дням, в какое время, и мы знаем период действия этого маршрута. А конкретный рейс со своей датой и со своим статусом просто ссылается на номер маршрута.

Когда была одна таблица flights, номер маршрута назывался flight_no и возникала некая путаница между номером flight_no и идентификатором рейса flight_id. Теперь она устранена: route_no отдельно, flight_id отдельно.

Поскольку маршрут действует определенный период времени, между таблицами routes и flights образовалась темпоральная связь, завязанная на промежутки времени. Декларативные темпоральные ключи только-только появились в 18-й версии PostgreSQL, но никто не мешает использовать такую конструкцию и в более старых версиях (мы поддерживаем 15-ю). На таблице routes висит ограничение EXCLUDE, которое не дает создать два маршрута с одним номером и пересекающимися периодами действия, а при соединении таблиц надо указывать два условия: равенство route_no и вхождение scheduled_departure в интервал действия маршрута.

Новичкам в SQL это может показаться сложным, поэтому есть новое представление timetable (вместо старого flights_v), которое скрывает темпоральное соединение. С ним можно писать запросы как раньше, не задумываясь о темпоральности бытия.

В бронированиях ничего не поменялось — бронирование оно бронирование и есть.

А вот в билетах — поменялось. Теперь при путешествии «туда и обратно» мы выдаем одному пассажиру два разных билета, и различить их можно с помощью нового поля outbound — у нас появился столбец типа boolean в базе.

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

Мы убрали контактную информацию. Это был такой JSON с телефоном и емейлом. Толку он него все равно было немного, потому что на одних телефонах много не покажешь — если уж делать JSON, то развесистый. Если вам такой нужен, соберите его агрегатными функциями из бронирования, всех его билетов и перелетов, и будет красота.

С телефонными номерами есть и другая сложность: они ведь должны быть ненастоящими, а то получаются такие «как бы персданные». В старой демобазе все номера были со специальным префиксом +70, который был в то время зарезервирован и не использовался. При этом небольшая номерная емкость сильно тормозила генерацию уникальных телефонов. А сейчас, насколько я понимаю, +70 вообще отдали Казахстану и использовать его нехорошо. Теперь представьте, сколько у нас стран в новой базе. Разбираться, как устроены телефонные номера в каждой из них, совсем не хотелось.

А вот с чем пришлось разбираться, так это с именами.

Мы и в старой базе уделяли внимание частотностям имен, а в новой со всеми ее странами кажется вполне логичным, чтобы из Москвы летел Иван Кузнецов, из Нью-Йорка — Джон Смит, а из Дели — скажем, Раджеш Кумар.

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

Тут мне снова помог ИИ. Задача — просмотреть списки имен и фамилий и обратить мое внимание на всякое подозрительное — оказалась ему вполне по зубам. После некоторого разбирательства я принимал решение: оставить имя, убрать или, может быть, исправить (например, добавить пропущенный диакритический знак). С помощью ИИ удалось — как мне кажется — добиться определенного качества и не потратить на это безумное количество времени. Носители языков наверняка найдут косяки, но они в любом случае неизбежны, поскольку модель довольно проста: имя и фамилия выбираются независимо друг от друга (с учетом рода, когда это нужно). Поэтому могут появляться какие-то неблагозвучия. Например, в Индии где-нибудь в сельской местности может странно звучать брахманское имя в сочетании с фамилией, характерной для неприкасаемых. Теоретически и это можно смоделировать, но не будем поддерживать исчезающие предрассудки.

Билеты и рейсы состоят в отношении «многие ко многим» и соединяются через промежуточную таблицу. Раньше она называлась незамысловато — ticket_flights, а сейчас мы ее переименовали в более благозвучное segments, поскольку рейсы, входящие в поездку, часто называются сегментами. И еще заменили имя столбца amount на price, поскольку amount — это скорее итоговая сумма, а здесь у нас стоимость одного перелета.

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

К порядковому номеру посадки мы добавили время — boarding_time. Изменение небольшое, а возможностей для написания запросов оно прибавляет.

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

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

Вот и все про саму базу. Оценить объем можно по табличке:

Архив в файле сжимается хорошо, почти точно в десять раз. Бронирование и билеты — таблицы-миллионники; рейсов не так много, поскольку у нас довольно крупные самолеты, которые перевозят по несколько сотен пассажиров.

Но все это не так важно, поскольку мы переходим к самому интересному — к генератору.

Генератор

Понятно, что все эти данные надо было как-то сгенерировать.

Как была устроена генерация старой базы? Она вся создавалась несколькими довольно крупными запросами, которые вставляли и обновляли сразу миллионы строк. Р-р-раз — все бронирования, два — все билеты и так далее. Это работало в целом неплохо и весьма быстро. Но чтобы поменять распределение данных или как-то изменить алгоритм, приходилось переделывать все эти большие запросы, а это было непросто.

Кроме того, мы получали только статический результат. Нельзя, например, было сказать «Хочу то же самое, но спустя еще час работы системы».

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

События у нас, по большому счету, двух видов.

Первый из них — это поток бронирований. Наши модельные человечки в какой-то момент садятся к компьютеру, идут на сайт авиакомпании и покупают билеты. Такого рода активность моделируется пуассоновским потоком. Это такой поток, события в котором происходят с заданной интенсивностью и не зависят друг от друга.

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

Интересно, что это же распределение моделирует запросы, приходящие в СУБД от пользователей OLTP-системы. И именно такая природа — то густо, то пусто — не позволяет нам утилизировать все ресурсы сервера. Потому что если мы не оставим запас и вылезем за условные 70% утилизации, в какой-то момент соберется очередь запросов и система захлебнется — время отклика вырастет запредельно. Хотя, казалось бы, в среднем ресурсов должно было бы хватить. (Кстати, вот старая, но хорошая книга. Жаль про Постгрес такой еще не написано, где вы, авторы?)

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

Но я отвлекся.

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

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

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

За полчаса до фактического времени отправления начинается посадка пассажиров, в посадочном делается отметка.

Затем рейс вылетает и, спустя какое-то время, садится.

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

Есть и еще кое-какие служебные события, но в основном мы имеем дело с этими двумя процессами: стохастическим потоком бронирований и регулярным автоматом состояний.

Все это работает на очереди событий. Точнее, это очередь с приоритетом, поскольку мы вставляем в нее события, которые должны произойти в назначенное модельное время, а достаем из очереди всегда событие с наименьшим временем.

Вначале в очередь вставляются «затравочные» события, потом они начинают порождать следующие события и так все это колесо крутится.

Написан весь генератор, конечно же, на Постгресе, на PL/pgSQL, а реализация очереди взята из нашего курса DEV2. Там мы показываем (со всеми необходимыми словами про изобретение велосипеда), что очередь можно эффективно реализовать средствами самой СУБД без всяких внешних программ. Мне всегда было интересно проверить, как такая очередь поведет себя на настоящей задаче, а то мало ли чему мы учим людей. И вот представилась такая возможность. По-моему, отличный пример, когда самописная очередь полностью оправдана, и тут она очень хорошо себя показала.

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

Еще dblink используется, чтобы запускать время от времени очистку и анализ. Дело в том, что вся обработка проходит внутри процедуры, то есть внутри одного SQL-оператора CALL, а пока оператор не закончится, Постгрес не обновляет статистику. Приходится собирать ее принудительно. К счастью, это отлично ложится на очередь: просто раз в неделю модельного времени появляется специальное событие, по которому в отдельном процессе выполняется очистка.

Использование генератора

Зачем генератор может пригодиться обычному человеку? Почему не воспользоваться уже готовой демобазой?

Первое, что приходит на ум — это объем. Мы выложили разные варианты вплоть до двух лет — это база около 11 Гбайт. Мало? Запускаете процедуру генерации, в параметрах указываете «от» и «до» и немного ждете. Сколько — это зависит от железа, но, как я говорил, процедура хорошо распараллеливается, то есть можно ускорять генерацию простым добавлением ядер.

У меня полугодовая база на 16 ядрах генерировалась за полчаса. Дальше, правда, скорость падает, но не драматически. Наверняка есть потенциал и для оптимизации: я прогнал процесс с pg_stat_statements, поправил пару проблем, но плотно этим не занимался.

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

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

Ну и наконец, можно влезть в код и поменять вообще все что угодно. Заменить самолеты на звездолеты, добавить клингонский. Там всего 3 тысячи строк кода на PL/pgSQL, вполне можно при желании разобраться. Правда, к ним прилагаются еще 2 мегабайта данных — таблицы аэропортов и имен.

Генератор выложен по лицензии MIT, так что можно все что угодно с ним делать.

Спасибо

Напоследок хочу сказать спасибо всем причастным. Конечно же, коллегам по нашему образовательному кружку за обсуждение всех важных вопросов. Отдельное спасибо — Евгению Моргунову, которого вы наверняка знаете по книгам «PostgreSQL. Основы языка SQL» и «PostgreSQL. Профессиональный SQL» и по курсу по языку SQL. Евгений тщательно анализировал те данные, которые я генерировал, и находил в них неочевидные проблемы, с которыми мы совместными усилиями справлялись. Ошибки в генераторе наверняка остались, но их точно стало меньше.

Кстати, если говорить об ошибках, то самые интересные были связаны с параллельностью и с датами. Всегда приходится иметь в виду, что события обрабатываются параллельными процессами. Чуть зазевался — получай взаимоблокировку. А с датами вообще все интересно, начиная с арифметики: длина интервала 1 month может быть разной в зависимости от того, к какому месяцу прикладывается. А говоря «рейс отправляется во вторник», надо четко понимать, что это вторник в часовом поясе аэропорта вылета. А в текущем часовом поясе это может быть совсем другой день недели.

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

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

В заключение — еще раз ссылки:

• демобаза — https://postgrespro.ru/education/demodb;

• генератор — https://github.com/postgrespro/demodb.