There will be no singularity

Всем привет! Мы хотим организовать неформальную летнюю школу по формальным методам и всяким родственным штукам (см. WIP сайт школы https://лялямбда.рус).
Планируются мини-курсы/воркшопы/доклады/нетворкинг/развлечения.
Темы курсов и воркшопов ещё пока только определяются (предложите свою!) и простираются от рандомизированного тестирования/фаззинга и модел-чекинга, до кубических (и не только) пруверов, логики некорректности, верифицированных компиляторов и слабых моделей памяти. Мы приветствуем участников с любым уровнем знаний — планируются несколько треков, чтобы каждый мог выбрать себе тему по своему бэкграунду.

Чтобы понять больше о наших потенциальных участниках и о том, какой формат школы выбрать, нам нужна ваша помощь: если бы вы хотели поехать, заполните, пожалуйста, небольшую форму https://docs.google.com/forms/d/e/1FAIpQLScfJ3tguxHNgxwHX_XpsWOiABTwB2R74sKh6OXEmcpYCCQqOQ/viewform.

6) FEATURE ENGINEERING
В процессе подготовки данных для ML, дата-инженерам приходится решать задачу поиска зависимых фич. Иными словами, зависимых столбцов. Например, день недели и число. Или дата рождения и возраст.
Такие связи могут быть гораздо сложнее.

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

7) ANOMALY DETECTION
Дата инженеры часто не утруждают себя описанием связей между таблицами в БД. Обычно для таких вещей используются PK/FK, но в аналитических базах эти ограничения все равно не работают.

Но данные-то все равно связаны, не зависимо от наличия FK.
Выявить эти связи можно, проанализировав DML-запросы за некоторый период.
На основании этих данных можно не только попытаться автоматически задать PK и FK, но и найти те запросы, которые выбиваются из общей массы.

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

8) QUERY CATALOG/DISCOVERY TOOL
Еще было бы неплохо не делать одну и ту же работу несколько раз. Если запрос, похожий на тот, который мы хотим сделать, уже кто-то писал, то его просто можно отыскать.

Здорово бы было искать по таблицам, колонкам и способу их связи между собой.
Это можно сделать. Потребуются AST и IR.

9) ETL CODEGEN
А еще можно попробовать генерить ETL пайплайны из SQL. Прикиньте, не надо будет разработчиков заставлять питон учить :)
AST + IR.

10) DOCOPS
Еще одно применение - автоматизация создания документации и контроля за ее актуальным состоянием.
AST + IR.

11) DATA LINEAGE
Пункты (5)-(10) объединяются под одним общепринятым названием - родословная данных. Если мы можем проследить связи между данными с момента их появления в системе, мы можем много что узнать и главное, предотвратить.

Причем отслеживать только SELECT совершенно недостаточно. Так же важно распознавать связи из TABLE CLONE, TABLE AS SELECT, multitarget INSERT, UPDATE и MERGE.
В Snowflake так же имеются специфичные инструменты вроде SNOWPIPE и TASK+STREAM

12) СОЗДАНИЕ ИНСТРУМЕНТОВ ДЛЯ РЕДАКТИРОВАНИЯ И ПОДСВЕТКИ SQL КОДА
Не самая популярная опция, тем не менее, для тех, кто этим занимается, важно иметь AST, компилятор, deparser и другие необходимые инструменты.

13) EDTECH
Автоматизация проверки домашних заданий для образовательных курсов про базы данных. Правила для движка анализатора можно написать так, чтобы они проверяли любые условия.

Это даст возможность формулировать ранее недоступные задачи. Например "измените схему БД так, чтоб заданный запрос возвращал 1 строку" или "исправьте запрос, чтобы он не могу выбросить следующие исключения".
У кого-нибудь есть знакомые в coursera или udemy? )

Было бы круто, если бы все, что я расписывал, анализировалось движком базы и можно было бы дописывать правила в виде UDF, например.
Что-то могло бы оптимизироваться автоматически, особенно то, что касается DDL.

Был интересный проект автономной базы https://github.com/cmu-db/peloton, но его прикопали :(
Крайне рекомендую всем интересующимся вот этот плейлист от автора проекта https://www.youtube.com/playlist?list=PLSE8ODhjZXjasmrEd2_Yi1deeE360zv5O
и другие видео с его канала.

Что-то в области self-driving баз делает Oracle, но проверять я это, конечно же, не буду :)

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

Поэтому пока нам придется решать эти проблемы снаружи.

Интересно еще и то, что сферы применения AST - парсера, компилятора и анализатора не ограничиваются генерацией типов и поиском проблем в запросах.
Сегодня накидаю еще вариантов. Предлагайте свои :)

1) ПРОВЕРКА ЦИКЛИЧЕСКИХ ЗАВИСИМОСТЕЙ
Совершенно реальна ситуация, когда VIEW или FK были созданы таким образом, что базе образуются циклические зависимости между объектами.

Как правило, это указывает на проблемы в архитектуре, но и само по себе может привести к проблемам. Для этого будет достаточно AST.

2) ТОПОЛОГИЧЕСКАЯ СОРТИРОВКА
В Snowflake обнаружилась такая проблема - при экспорте DDL с помощью стандартных средств, объекты сортируются по имени. Если у вас есть VIEW с именем "A", которое зависит от таблицы "B", то потом по этому DDL восстановить схему не получится.

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

3) GRAFANA QUERIES
Был такой фичреквест: сделать тулзу, которая будет подготавливать обычные запросы под дашборды графаны. Если вы делали дашборды, вам это проблема знакома ;) Сделаем отдельным пакетом и интерфейсом на parsers.dev

Будет достаточно только AST, но обязательно нужен deparser, чтобы преобразовать измененный запрос обратно в SQL. Недавно для PostgreSQL такой появился:
https://pganalyze.com/blog/pg-query-2-0-postgres-query-parser
Если с ним все ок, то все просто :)

4) ОЦЕНКА СЛОЖНОСТИ ЗАПРОСА
В Snowflake для запуска запросов нужно указать WAREHOUSE. Это виртуалка. При запуске нужно указать ее размер, который отображает количество выделяемых ресурсов.
Если запрос затрагивает много таблиц, возможно, имеет смысл взять WAREHOUSE побольше.

Можно определить количество таблиц c помощью AST traverse, но проще взять из IR-объекта.

5) GOVERNANCE AND REGULATORY COMPLIANCE.
В некоторых базах пермишены можно накладывать только на объекты целиком, в некоторых можно установить ограничения на уровне строки, в некоторых на уровне колонок.

В Snowflake есть MASKING POLICY. Это очень удобный инструмент для преобразования контента указанного столбца.
Можно, например, экранировать строку в зависимости от роли пользователя
https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy.html#examples

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

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

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

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

SQL-pапросы можно анализировать как в перед выполнением, так и после - в целях расследований или подготовки ИБ-отчетов.
Нужен будет и AST и IR.

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

Но чтобы перейти на следующий уровень, нам нужен инструмент для компиляции SQL в объекты промежуточных представлений (IR), с которыми можно бы было работать в коде.
К сожалению, нет универсального инструмента, который можно было бы взять и начать использовать.

В идеале хорошо бы было сделать подборку инструментов компиляции и сравнить их, но с этим есть одна существенная проблема:
мне такие инструменты не известны.
Есть несколько продуктов, которые пытаются сделать универсальный AST парсер разных SQL-диалектов, но они не работают :)

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

У выбранного пути есть несколько существенных минусов:
- невозможно сделать универсальный компилятор для разных БД
- поведение компилятора не будет совпадать с поведением реальных БД

Но в любом случае это лучше, чем ничего :)
На данный момент у нас есть 2 комилятора: для PostgreSQL и Snowflake.
Компиляторы покрывают все основные юзкейсы и мы постоянно их улучшаем.


Компилятор для PostgreSQL и Snowflake (parsers.dev)
1) DDL компилируется в объект, описывающий схему БД
2) DML + DDL IR компилируется в IR, описывающий имена, типы, nullability и класс количества строк запроса, список зависимых объектов

1) Берем все команды из DDL (CREATE, ALTER, DROP) и "выполняем" их по очереди.
В итоге мы получим состояние схемы, которое по идее могли бы вытащить из INFORMATION_SCHEMA.

Зачем так сложно?
Мы сможем не зависеть от наличия БД под рукой. Это будет актуально, когда мы переключимся на работу с облачными БД.

2) Имея состояние схемы, разбираем запрос и ищем в нем связи между известными нам объектами.
Видим поле - ищем в текущем scope.
Видим функцию - ищем в списке.
Видим JOIN - ищем FK и пытаемся вычеслить информацию о nullability.

Как можно использовать компилятор parsers.dev прямо сейчас?
1) По DDL + DML сгенерировать типы, модели и вообще все что угодно для своего языка программирования.
PostgreSQL поддерживается неплохо, и скорее всего подойдет для абсолютного большинства проектов.
Snowflake в preview

Таблицы, вью, системные функции, пользовательские функции, системные типы, некоторые extensions - должно хватить :)

Как получить все что угодно для своего языка программирования?
Сгенерировать самостоятельно из того объекта, который выдаст наш API

2) AST парсеры
Для создания своих инструментов для работы с SQL можно воспользоваться AST-парсерами.
Для PostgreSQL используется оригинальный парсер.
Для Snowflake пришлось написать парсер самостоятельно. Большая часть конструкций протестирована, структура приближена к PG

3) Существует готовый npm модуль, который автоматизирует проверку изменений типов запросов между коммитами.
Текущие запросы сравниваются с последними закомиченными на предмет соответствия типов, nullability, класса количества строк и lineage колонок
https://github.com/parsers-dev/sql-type-tracker


Aнализатор (holistic.dev)
Проверяет Postgresql запросы по существующему списку правил, основываясь на данных из парсера и компилятора.
Анализатору доступны все внутренние состояния (CTE, подзапросы и тд), поэтому получается найти проблемы даже в подзапросах.

В Postgresql есть такая вьюха - pg_stat_statements, в ней лежат очищенные от данных запросы.
Можно запустить крон на своей стороне и по расписанию отправлять содержимое этой вьюхи на анализ.
Идеальный вариант для проектов на ORM.

Вся интеграция займет минут 10.
Есть готовые интеграции с облачными провайдерами.

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

Бывают же такие совпадения...
Только что релизнули вторую версию pg_query с поддержкой 13 версии pg и ДЕПАРСЕРОМ на борту! (это отдельная боль). Есть обертка для руби и го

https://pganalyze.com/blog/pg-query-2-0-postgres-query-parser

Итак, типы мы доставать научились, как из этого сгенерить что-то полезное?
Возьмите чистую кастрюлю...

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

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

В-третьих, теперь изменения запросов в системе контроля версий можно отслеживать отдельно от кода приложения.
Так же улучшается и архитектура самого приложения: там, где раньше была лапша if'ов и склейки строк, теперь пяток отдельных методов.
И для каждого описаны типы!

Ну допустим. А дальше?
А дальше берем любой шаблонизатор...
Нет, ну можно, конечно, заморочиться с каким-нибудь конструктором AST и депарсером, но вы помните, что мы все еще делаем MVP?
Короче - у нас есть типы и теперь мы можем превратить их в ваши модели, POJO или json-schema

Заметьте, что мы уже реализовали 2/3 плюсов ORM, о которых говорили вчера. Теперь нам не надо думать о соответствии типов в приложении и базе и у нас есть кодогенерация!
Но все-таки из списка проблем на границе базы и приложения решена только 1 проблема из 5.

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

А вот дальше нам нужно сделать качественный скачок от как-то полученных типов к анализу запросов.
И тут вариант один - нам нужен AST парсер.
Если у нас будет AST мы сможем сделать как минимум linter-like правила.
Например, второе правило вторника - avoid typecast IS [NOT] NULL

И это моя особая боль. Официальных AST парсеров для диалектов SQL можно сказать, что нет.
Ныть об этом я могу еще один день, но это вряд ли кому-то будет интересно.
Если коротко - вам придется много программировать. Ну или не придется, если вы не бросите читать меня до завтра :)

Глобально нам повезло. Для #PostgreSQL есть AST парсер, который смогли выковырять из сервера:
https://github.com/lfittl/libpg_query
Есть обертки для разных языков.
Минус один - стабильный парсер есть только диалекта десятой версии.

Если к информации о типах и AST мы добавим данные из INFORMATION_SCHEMA (да, придется опять использовать живую базу), то мы при должном упорстве даже сможем понимать типы в подзапросах.

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

Как можно догадаться, linter-like правила на AST это тот рубеж, после которого начинается настоящее месилово.
Потом нам понадобится описание всех типов всех функций, операторов, правила выполнения implicit typecast и много-много всякого...

Например, только из AST нельзя однозначно понять что обозначает * в запросе

SELECT t.* FROM t

Здесь t.* это не table = 't', columns = '*', а массив ['t', '*']
Вы можете сказать - "ну вон же, FROM t"!
А если есть колонка t.t? А если она композитного типа?
Короче, все сложно...

Дальше либо стоит остановиться (мы и так неплохо поработали), либо придется писать КОМПИЛЯТОР SQL в Intermediate Representation Object, фактически повторяя поведение базы...

Я, пожалуй, остановлюсь, т.к. компилятор это явно перебор :)
Скажу лишь, что я его написал*...

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

Сегодня создаем свой анализатор!

На самом деле за 3 дня я описал 2 разные проблемы (анализ запросов и контроль соответствия типов), которые и решать-то надо бы по-разному.
Но практика показала, что они решаются одновременно :)
Я покажу путь, которым шел я.

Давайте сделаем MVP системы контроля соответствия типов.
Нужно как-то смайнить типы из наших запросов, и потом сопоставить их с типами в приложении.
Давайте сначала погуглим...
Тадам!
https://github.com/adelsz/pgtyped
Все готово, берем и используем.

Только есть небольшой минус (он же плюс). Эта тулза "компилирует" запросы в prepared statements и уже из них извлекает типы.
И сделать это можно, только подключившись к базе.
Почему это минус? У нас же есть docker...

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

А почему это плюс?
Этот отличный план, Уолтер. Надежный, как швейцарские часы (с)
Если получать типы из базы, то есть 100% гарантия, что именно такие типы и будут в рантайме.

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

Но когда я озаботился вопросом создания своего анализатора, такой волшебной тулзины еще не было, а сам я до такого не догадался, и тогда пришли они - РЕКЕКСПЫ!
боже_крис_они_же_еще_дети.gif

Скажу сразу: ТАК ДЕЛАТЬ НЕ НАДО. Даже если очень захочется. Почему - расскажу чуть позже.
Но мы же делаем MVP, поэтому кому не пофиг? :)
Т.к. у нас есть только запросы и все, нам нужно взять откуда-то информацию о типах.
Где их взять? Давайте сами их укажем!

SELECT
  id :: INT AS "id"
FROM users
WHERE
  name = ${name} :: VARCHAR(16)

Кхе-кхе... Ну ок... теперь у нас есть информация о типах. Осталось пройтись по этому регекспом...

:: <тип> AS "<имя>" - это имена и типы результата
${<имя>} :: <тип> - это имена и типы параметров

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

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

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

У меня до сих пор эта шутка жива и висит на втором мониторе. Жаль, что не стала популярной. Хардварь стартапы - боль.

https://t.me/pathetic_low_freq/392

3) Изменится класс количества строк в ответе
Что такое класс количества строк?
Количество строк можно разделить на 5 групп: ни одной, одна, одна или ни одной, много, много или ни одной.
И я крайне редко видел, чтобы кто-то проверял класс количества строк в приложениях.

Но это крайне важная метрика, которая очень сильно влияет на качество и безопасность приложения!
Например, сложный запрос с несколькими CTE создает заявки на исходящие выплаты клиентам.
Подразумеваться, что создается 1 документ за раз и возвращается его ID.

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

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

Причина почему класс количества строк может измениться, аналогичны пункту (2).
Но если в случае с NULL приложение может упасть и оставить незавершенные задачи, то в данном случае последствия могут быть куда хуже.

И ладно бы, если это проблема возникала только когда что-то будет меняться. С вероятностью, близкой к 100% это проблема уже существует в вашем приложении на данный момент, т.е. довольно сложно "на глаз" определить корректный класс количества строк в сложном запросе.

Сложно сказать, насколько ORM может помочь в контроле класса строк, но, полагаю, что не сильно.

4) Изменится логическая связанность полей
Тоже довольно опасная категория изменений.
Что, если вместо t1.id будет возвращаться t1.id + 1?
Или MAX(t1.id)?
Или COALESCE(t1.id, 0)?
Или вообще t2.id?

Особенно если эта колонка напрямую не влияет на дальнейшие действия внутри приложения, а отдается клиенту "как есть".
Произойти такое изменение может на любом уровне вложенности или во внешних объектах, например во VIEW.

5) Изменится порядок строк
Про эту проблему я рассказывал вчера. В PostgreSQL нет гарантированного порядка строк, если не указан ORDER BY.
Если внутри приложения от порядка строк что-то зависит, то такое изменение может сломать этот функционал.

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

А теперь давайте окинем взглядом все что я тут понаписал за 3 дня и трезво оценим - что из этого учтено в ваших проектах?
А это лишь малая доля возможных проблем.

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

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

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

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

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

Что может пойти не так?
Для результатов запросов можно выделить несколько проблем:
1) Изменятся типы полей
2) Изменится nullabitity
3) Изменится класс количества строк в ответе
4) Изменится логическая связанность полей
5) Изменится порядок строк

1) Изменятся типы полей
Типы полей в результатах могут смениться по нескольким причинам. Например:
- изменился тип исходной колонки в схеме БД
- изменилась функция или выражение для вычисляемого поля (или их параметры)

Возможно, это сразу будет выявлено при тестировании. Например, в js изменение INT на BIGINT или FLOAT приведет к изменению типа с number на string внутри приложения.
Представим, что с данными ничего не происходит внутри приложения, а они всего лишь отдаются во внешние сервисы.

При этом рантайму все равно что там за тип вернула база (js, python и тд).
Можно представить что может произойти при недостаточно строгих тестах...

Или еще вариант: BIGINT преобразуется в строку. Но внезапно вместо числа в строке мы начинаем получать произвольный текст.
И даже если у нас есть json-schema, которая используется в тестах, мы скорее всего ничего не заметим.

Т.е. либо мы должны иметь очень строгие тесты, либо очень внимательных инженеров на круглосуточном дежурстве :)

2) Изменится nullabitity
Почему такое может произойти?
- изменилось ограничение на NULL у исходной колонки в схеме БД
- добавился или исчез внешний ключ между таблицами при JOIN
- изменился тип JOIN
- изменились условия фильтрации в оригинальном запросе или вложенных запросах

Например, если удалили внешний ключ, по которому производится LEFT JOIN, то уже нельзя будет гарантировать то, что вместо присоединяемого значения мы не получим NULL.
Аналогичная ситуация возникнет, если внешний ключ есть, но JOIN изменили на LEFT JOIN.

Изменившиеся условия фильтрации так же могут влиять на nullabitity:
SELECT (SELECT 1 WHERE TRUE)
vs
SELECT (SELECT 1 WHERE FALSE)

Это будет проблемой во всех языках, кроме, наверное, Java, где ожидаемо, что любое поле объекта nullable :)
Результат - много 500ых ошибок в логах...

На всякий случай напомню, что это контент я готовлю для https://twitter.com/sqaunderhood, которым я рулю на этой неделе.
Богомерзкий твиттер приносит много боли, заставляя пилить все на 280 символов. Мне, привыкшему к длинным телегам, это больно :)
Поэтому вечером я скидываю все что понаписал сюда.

Но я дублирую (как могу) на английском в своем акке, поэтому, если захотите пошарить с англоговорящими коллегами, велкам https://twitter.com/antonrevyako

Перейдем к архитектурным проблемам

6) LIMIT/OFFSET без ORDER BY
К сожалению не все знают, что в PostgreSQL нет сортировки по умолчанию. Это значит что в общем случае вы каждый раз можете получать случайный порядок строк. Не просто один и тот же случайный, а каждый раз новый

Если от порядка строк зависит бизнес логика внутри приложения, обязательно используйте ORDER BY

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

7) LIMIT применяется к запросу, возвращающему 1 строку
Это яркий пример наложения чрезмерных ограничений. Вроде ничего страшного нет. На данные LIMIT не повлияет. Да, но может повлиять на разработчика, который будет менять такой запрос.

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

8) OFFSET применяется к запросу, возвращающему 1 строку
А вот это уже действительно плохо. Запрос с OFFSET > 0 будет возвращать пустой результат, там где он на самом деле есть.
SELECT 1 OFFSET 1
А т.к. OFFSET, как правило, передается из приложения, получить пустоту особенно легко

9) псевдоним выражения совпадает с именем поля

 SELECT id + 1 AS id FROM t1 

Казалось бы, что тут такого? Но давайте снова подумаем о разработчике, который, быть может, и запроса самого не видит, а лишь использует результат в приложении.

 #define TRUE FALSE //Happy debugging suckers 

10) отсутствует ORDER BY для агрегационных функций
В PosgreSQL существует ряд функций, которые могут вернуть результат в виде массива или объекта. Например, array_agg. Мы помним, что порядок результатов у нас не гарантирован?
Ну так вот,

 array_agg(f) ... ORDER BY f 

не поможет...

Если мы хотим иметь гарантированно упорядоченный массив, нам нужно указать порядок внутри самой функции - array_agg(f ORDER BY f)

Вчера я рассказал о 10 проблемах, которые можно встретить в схемах БД (DDL), а сегодня поговорим о том, что можно найти в самих запросах (DML).
Так же, как и вчера, я покажу 10 случайных правил из моей обширной полутора тысячной коллекции :)

Несколько заметок о производительности

1) count(DISTINCT unique)
Когда разработчик хочет посчитать количество уникальных значений, он скорее всего использует конструкцию count(DISTINCT).

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

2) Проверка на NULL значений после приведения типов

A IS NOT NULL

в 2 раза быстрее чем

A :: type IS NOT NULL

Причем тайпкаст может быть прийти из подзапроса и обнаружить это будет не так просто.

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

3) В условии JOIN ON не используется присоединяемая таблица
Довольно популярная ошибка, которую так же можно отнести и в разряд архитектурных. Встречается повсеместно при копипасте:

 ... t1 JOIN t2 ON t1.id = t.id 

Это приведет к лавинообразному росту: для каждой строки, где t1.id = t.id будут присоединяться ВСЕ данные из таблицы t2.
При аналитических запросах, где используются агрегационные функции довольно просто такое не заметить

4) JOIN таблиц без использования внешних ключей

 JOIN t2 ON t1.id = t2.id 

Если не существует внешнего ключа, описывающего связь t1.id и t2.id, то вполне возможно, что в запрос закралась ошибка, полученная при копипасте.

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

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

5) порядок колонок в GROUP BY _МОЖЕТ_ иметь значение
Порядок колонок в группировке никак не влияет на содержимое результата. Но на производительность может влиять существенно. Результат _МОЖЕТ_ зависеть от кучи вещей:

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

Но в bigquery, говорят, все пошло еще дальше, и там рекомендуют самостоятельно заниматься расстановкой условий в WHERE:
2.3 WHERE clause: Expression order matters
https://wklytech.medium.com/bigquery-sql-optimization-c7a7db170c56
Я все еще очень надеюсь, что это fake news :)

Переходим к поиску проблем в схеме БД.
Первый документ, который стоит изучить: https://wiki.postgresql.org/wiki/Don't_Do_This
Там вы найдете небольшое количество правил, которые легко учесть при разработке, если вы не используете ORM.
Но на что еще нам стоит обратить внимание?
Покажу 10 из 1.5k правил.

Пара моментов, связанных с производительностью:
1) зеркальные индексы (a,b) и (b,a) или индексы с перестановкой
В общем случае невозможно определить оптимальный порядок колонок в индексе. При анализе стоит учесть все множество запросов в проекте, которые могли бы частично использовать многоколоночный индекс.
Но даже если таких запросов нет, они могут появиться.
В любом случае стоит избегать индексов одного типа, созданных по одним и тем же полям. Это замедлит обновление таблицы, увеличит нагрузку на диск, а значит заафектит производительность всей базы.

2) UNIQUE индекс, включающий поля неограниченного размера
Проверка уникальности сама по себе дорогая операция, которая будет тормозить изменение таблицы. Но кроме того, такой индекс таит в себе еще несколько опасных побочных эффектов.
Во-первых, полная уникальность длинной строки, скорее всего не имеет смысла с точки зрения архитектуры. Обычно нужны будут каки-то функции от текста - lowercase, tsvector, или что-то более сложное.
Во-вторых, в один прекрасный момент можно получить ошибку вот с таким хинтом:
Values larger than 1/3 of a buffer page cannot be indexed.

Поэтому рекомендуется строить такие индексы по какому-нибудь хешу

Несколько архитектурных issues:
3) заданное DEFAULT значение без ограничений NOT NULL
Самый распространенный кейс использования значений по умолчанию - отсутствие необходимости указывать значение при вставке данных.
Скорее всего это означает, что отсутствие значения нас не устроит.
Стоит обозначить это явно.

4) Константные DEFAULT значения для колонок, используемых в уникальных индексах
Может привести к возникновению ошибки при вставке строк без явного указания значений для таких колонок.
Скорее всего в тестах такой кейс не придет никому в голову, а значит найти причину будет сложно.

5) DEFAULT значение для колонок с внешним ключем
Кроме очевидного вопроса "зачем?" возникает 2 опасных кейса при вставке данных:
- значение DEFAULT есть во внешней таблице, но не соответствует бизнес логике. Это ломает связанность данных
- значения нет во внешней таблице, ошибка

6) Внешний ключ по SERIAL/IDENTITY колонкам
Мало того, что это не имеет физического смысла, это довольно вероятно со временем приведет к ошибкам при вставке, когда соответствующие значения во внешней таблице закончатся.

7) Уникальные TIMESTAMP колонки
Не имеет физического смысла в большинстве случаев, т.к. TIMESTAMP имеет точность в 1 микросекунду.
Кроме того, могут возникнуть проблемы при массовой вставке с использованием функций времени, фиксируемых на начало транзакции, например NOW()

8) Уникальный индекс включает другой уникальный индекс
Бессмысленно и имеет побочные эффекты, аналогичные (1)

9) Похожие имена
PostgreSQL приводит к нижнему регистру все имена, указанные без кавычек. Snowflake к верхнему регистру. Но имена в кавычках будут регистро-чувствительными.
"Field", "field" и field (превратится в FIELD в Snowflake) будут считаться разными идентификаторами, что может привести к ошибке

10) Неявная генерация имен
Для ограничений, индексов или других объектов, которые не требуют обязательного указания имени при создании, база данных подбирает имена самостоятельно.
В PostgreSQL для этого есть специальный алгоритм. Но если сгенерированные имена объектов похожи, к ним будут добавлены числовые индексы.
А это значит, что важен порядок создания этих объектов.
Поэтому на разных экземплярах базы имена одних и тех же объектов могут быть перепутаны.
Это может привести к очень печальным последствиям, например, при удалении объектов.
Но Snowflake ведет себя иначе. Он генерирует неуказанные явно имена случайным образом. При изменениях схемы такое поведение потребует большого количества внимательности и ручного труда.

План на неделю в twitter.com/sqaunderhood:
- Категории проблем. Проблемы в схеме БД
- Проблемы в запросах
- Проблемы на границе сопряжения приложения и базы
- Создаем свой анализатор
- Как можно автоматизировать поиск проблем
- Для чего еще пригодится анализатор
- Забавные wtf в PostgreSQL и Snowflake

Дисклеймер: все примеры тестировались в PostgreSQL или SnowflakeDB. В других базах может быть иначе.
Каждый из озвученных тезисов не будет иметь ультимативный характер и приводится в справочных целях.
Используйте на свой страх и риск :)

Независимо от способа создания SQL (руками или при помощи кодогенерации), проблемы с запросами можно разделить на несколько больших групп:
1) naming convention
2) производительность
3) безопасность
4) архитектура


1) Naming convention
В некоторых архитектурах (data vault, anchor modelling) соглашение об именах является главной скрепой :)
Но и в обычной жизни это тоже пригодится. Особенно при работе с аналитическими базами.
В работе дата инженеров случаются смены вендеров БД.
Неожиданно можно узнать, что часть имен считаются ключевыми словами в одних базах и не считаются таковыми в других.
Например, LIMIT в SnowflakeDB (примеры в конце недели)
Но объективно, это не сильно аффектит качество ПО :)

2) Производительность
Кажется, что это самая главная проблема при работе с БД. Большая часть проектов пишется с использованием ORM.
Но как мы знаем, ORM придумывали не для того, чтобы у вас все быстро работало, а чтобы быстро работали вы :)

3) Безопасность
В сознании разработчика слова "безопасность" и "БД" находят пересечения только в SQL инъекциях. Эти проблемы давно решены на уровне фреймворков и специально об этом думать не надо, просто используй best practice. ORM или нет - не важно. Эскейпить строки умеют все.
Но это далеко не все. Есть еще, например, регуляторные требования относительно персональных или банковских данных. Нельзя допустить, чтобы кто-то из сотрудников имел доступ одновременно к имени и фамилии пользователя. Или чтобы система выводила полный номер карты.
А что, если запрос может привести к отказу в обслуживании из-за большого объема обрабатываемых данных? В треде https://twitter.com/SanSYS/status/1299657208934916097 грустные истории о том, как lingualeo не глядя сохраняли весь пользовательский инпут в JSON. И потом его вытягивали.
Да, это больше про производительность. Но часто проблемы будут иметь несколько сторон.

4) Архитектура
Самая богатая на проблемы группа. Количество разных ошибок не поддается счету и их очень сложно искать.
Про них я в основном и буду рассказывать.
Все проблемы в этой группе связанны с нарушением целостности логических связей между элементами базы:
- избыточная обработка невозможных состояний
- недостаточная обработка возможных состояний
- запутанные условия в выражениях
- зависящее от вендора поведение
- worst practices

ой....
https://gitlab.com/gitlab-com/gl-infra/production/-/issues/3962

пишите письма в спортлото DHH

На наступившей неделе мне доверили честь вести коллективный твиттер аккаунт https://twitter.com/sqaunderhood
Я расскажу чем мы занимаемся и как все устроено.
Сюда буду дублировать лонгридами в конце дня.
Постараюсь в своем твиттере и линкеде постить копию на английском (лет ми спик фром май харт ин иглиш).

Поэтому лайк, шер, и что там еще полагается

Если этот пост соберет 1000 лайков, сяду пилить свою облачную бд.
опен-кором, с движками по подписке и всяким таким....

https://baserow.io/

*предложение не является публичной офертой

У нас в чате регулярно происходят академические срачи. Последний, например, про то как внутри питон работает. Присоединяйтесь! https://t.me/joinchat/Tqy4Z0AKtdiFOz16

Вариации на тему "OVH переезжает в облако":

https://twitter.com/anton_sunrise/status/1369968202449571843