SQL *

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

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


Требуемый уровень подготовки: базовое понимание SQL и Python (код статьи проверялся под Python 3.6). Желательно ознакомится с первой частью, так как к ней будут неоднократные отсылки и сравнения. В конце статьи есть весь код примеров под спойлером в едином файле и список ссылок для более углубленного изучения материала.

Первые 70% курса по SQL кажутся довольно простыми. Сложности начинаются на остальных 30%.

С 2015 по 2019 годы я прошёл четыре цикла собеседований на должность аналитика данных и специалиста по анализу данных в более чем десятке компаний. После очередного неудачного интервью в 2017 году — когда я запутался в сложных вопросах по SQL — я начал составлять задачник с вопросами по SQL средней и высокой сложности, чтобы лучше готовиться к собеседованиям. Этот справочник очень пригодился в последнем цикле собеседований 2019 года. За последний год я поделился этим руководством с парой друзей, а благодаря дополнительному свободному времени из-за пандемии отшлифовал его — и составил этот документ.

Есть множество отличных руководств по SQL для начинающих. Мои любимые — это интерактивные курсы Codecademy по SQL и Select Star SQL от Цзы Чон Као. Но в реальности первые 70% из курса SQL довольно просты, а настоящие сложности начинаются в остальных 30%, которые не освещаются в руководствах для начинающих. Так вот, на собеседованиях для аналитиков данных и специалистов по анализу данных в технологических компаниях часто задают вопросы именно по этим 30%.

Удивительно, но я не нашёл исчерпывающего источника по таким вопросам среднего уровня сложности, поэтому составил данное руководство.

Предлагаю ознакомиться с расшифровкой доклада начала 2016 года Владимира Ситникова "PostgreSQL и JDBC выжимаем все соки"

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Андрея Сальникова "Управление нагрузкой на PostgreSQL, когда одного сервера уже мало"

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

Сегодня не будет никаких сложных кейсов и мудреных алгоритмов на SQL. Все будет очень просто, на уровне Капитана Очевидность — делаем просмотр реестра событий с сортировкой по времени.

То есть вот лежит в базе табличка events, а у нее поле ts — ровно то самое время, по которому мы хотим эти записи упорядоченно показывать:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Понятно, что записей у нас там будет не десяток, поэтому нам потребуется в каком-то виде постраничная навигация.

#0. «Я у мамы погроммист»

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Даже почти не шутка — редко, но встречается в дикой природе. Иногда после работы с ORM бывает тяжело перестроиться на «прямую» работу с SQL.

Но давайте перейдем к более распространенным и менее очевидным проблемам.

Предлагаю ознакомиться с расшифровкой доклада начала 2020 года Георгия Рылова "WAL-G: новые возможности и расширение сообщества"

У меинтейнеров open-source возникает множество проблем по мере их роста. Как писать все больше требуемых фич, чинить все больше issues'ов и успевать смотреть все больше pull request'ов? На примере WAL-G(backup-tool for PostgreSQL) расскажу про то, как мы решали эти проблемы, запустив курс по Open-source разработке в университете, чего мы добились и куда будем двигаться дальше.

Работая в одном из крупнейших банков страны мне пришлось столкнутся с задачей оценки эффективности использования ресурсов примерно 16 тысяч серверов. Задача была сформулирована предельно просто — нужно было разработать методологию оценки метрик загрузки сервера за период. В идеале оценка загрузки сервера за период должна производится по одному или нескольким (не более 8) числам.

Несколько слов об особенностях использования виртуальных серверов

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

По мере развития платформ виртуализации наступает момент, когда никто в компании не может понять насколько эффективно используются ресурсы. Даже самые развитые средства мониторинга не дают ответ на этот вопрос из-за различных сценариев использования серверов. Например, в департаменте может быть сервер отчетов, который будет полностью загружен только ограниченный период времени. Скажем, часа 3-4 в конце месяца. В реальных сценариях динамически ресурсы для таких серверов никто не выделяет — это сложно технически и организационно. Ресурсы выделяются именно под максимальную периодическую загрузку сервера, хоть она и бывает нечасто.

Как резюме — в крупных организациях ресурсы виртуальных ферм расходуются крайне неэффективно.

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

Предлагаю ознакомиться с расшифровкой доклада начала 2020 года Андрея Бородина "Odyssey: архитектура, настройка, мониторинг"

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

Предлагаю ознакомиться с расшифровкой доклада Николая Самохвалова "Промышленный подход к тюнингу PostgreSQL: эксперименты над базами данных"

Shared_buffers = 25% – это много или мало? Или в самый раз? Как понять, подходит ли эта – довольно устаревшая – рекомендация в вашем конкретном случае?

Пришло время подойти к вопросу подбора параметров postgresql.conf "по-взрослому". Не с помощью слепых "автотюнеров" или устаревших советов из статей и блогов, а на основе:

1. строго выверенных экспериментов на БД, производимых автоматизированно, в больших количествах и в условиях, максимально приближенных к "боевым",
2. глубокого понимания особенностей работы СУБД и ОС.

Используя Nancy CLI (https://gitlab.com/postgres.ai/nancy), мы рассмотрим конкретный пример – пресловутые shared_buffers – в разных ситуациях, в разных проектах и попробуем разобраться, как же подобрать оптимальную настройку для нашей инфраструктуры, БД и нагрузки.

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

Как сделать так, чтобы все были довольны? Как не сойти с ума, проектируя и поддерживая такую систему? Что делать, если в таверну начинают приходить не только привычные пираты и моряки?



Всё под катом. Но пойдем по порядку.

Если вы работаете с SQL, то вам это будет нужно очень скоро. Apache Spark – это один из инструментов, входящих в экосистему Hadoop, который обрабатывает данные в оперативной памяти. Одним из его расширений является Spark SQL, позволяющий выполнять SQL-запросы над данными. Spark SQL удобно использовать для работы посредством SQL-запросов с большими объемами данных и в системах с высокой нагрузкой.

Ниже вы найдёте некоторые нехитрые приёмы по работе со Spark SQL:

• Как с помощью сбора статистики и использования хинтов оптимизировать план выполнения запроса.
• Как, оставаясь в рамках SQL, эффективно обрабатывать соединения по ключам с неравномерным распределением значений (skewed joins).
• Как организовать broadcast join таблицы, если её размер слишком велик.
• Как средствами Spark SQL понять, сколько приложение Spark реально использовало памяти и ядер кластера в развёртке по времени.

При написании приложений на Python, для работы с базами данных часто используются объектно-реляционные мапперы (ORM). Примерами ORM являются SQLALchemy, PonyORM и объектно-реляционный маппер, входящий в состав Django. При выборе ORM довольно важную роль играет её производительность.

На Хабре, да и в интернете в целом, можно найти не один тест производительности. Как пример качественного бенчмарка python ORM можно привести бенчмарк от Tortoise ORM (ссылка на репозиторий). Данный бенчмарк анализирует скорость работы шести ORM для одиннадцати различных видов SQL-запросов.

В целом бенчмарк от tortoise хорошо позволяет оценить скорость выполнения запросов при использовании разных ORM, но у такого подхода к тестированию я вижу одну проблему. ORM зачастую используют в веб приложениях, где одновременно несколько пользователей могут посылать различные запросы, но я не нашел ни одного бенчмарка, оценивающего работу ORM при таких условиях. Вследствие этого я решил написать свой бенчмарк и сравнить с помощью него PonyORM и SQLAlchemy. За основу я взял бенчмарк TPC-C.

Предлагаю ознакомиться с расшифровкой доклада 2018 года Андрея Сальникова "Практика обновления версий PostgreSQL"

В большинстве своем, системные администраторы и ДБА бояться как огня делать мажорные обновления версий баз данных (RDBMS), особенно если эта база данных в эксплуатации и имеет достаточно высокую нагрузку. Главной причиной тому некоторый даунтайм базы данных, который всегда подразумевается при планировании таких работ.

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

В Data Egret мы накопили огромный опыт проведения мажорных апгрейдов PostgreSQL в проектах, где нет права на ошибку. Я поделюсь своим опытом и расскажу о следующих шагах процесса: как правильно подготовиться к upgrade-у PostgreSQL? что необходимо сделать на этапе подготовки? как запланировать последовательность действий на сам upgrade? как провести процедуру upgrade-а успешно, без возврата на предыдущую версию бд? как минимизировать или вообще избежать простоя всей системы во время upgrade-а? какие действия необходимо выполнить после успешного upgrade-а PostgreSQL? Я также расскажу про две наиболее популярные процедуры апгрейда PostgreSQL — pg_upgrade и pg_dump/pg_restore, плюсы и минусы каждого из методов и расскажу про все типичные проблемы на всех этапах этой процедуры, и как их избежать.

Доклад будет интересен как новичкам так и тем ДБА которые уже давно работают с PostgreSQL, но хотят побольше узнать о том как правильно планировать и проводить upgrade максимально безболезненно.

SQL — это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе, и вот это — совсем не одно и то же:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

В процессе оптимизации плана исполнения запроса PostgreSQL может произвольным образом «переставлять» эквивалентные условия, не вычислять какие-то из них для отдельных записей, относить к условию применяемого индекса… Короче, проще всего считать, что вы заранее не можете управлять тем, в каком порядке будут (и будут ли вообще) вычисляться равноправные условия.

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


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

Обычно фреймворк EF Core используют в сочетании с MS SQL — другим продуктом Microsoft. Однако это не догма. Например, мы в CUSTIS пишем бизнес-логику на C#, а для управления базами данных используем Oracle. В EF Core есть замечательный механизм миграций, но в нашем случае они не идемпотентны. Дело в том, что Oracle и ряд других БД, например MySQL, не поддерживают транзакционный DDL. Значит, если миграция упадет где-то посередине, ее не получится ни накатить, ни откатить. Как же реализовать идемпотентные миграции на EF Core без MS SQL?

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Андрея Бородина "Резервные копии с WAL-G. Что там в 2019?"

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Алексея Лесовского — «Поиск и устранение проблем в Postgres с помощью pgCenter»

Время от времени при эксплуатации Postgres'а возникают проблемы, и чем быстрее найдены и устранены источники проблемы, тем благодарнее пользователи. pgCenter это набор CLI утилит которые является мощным средством для выявления и устранения проблем в режиме "здесь и сейчас". В этом докладе я расскажу как эффективно использовать pgCenter для поиска и устранения проблем, в каких направлениях осуществлять поиск и как реагировать на те или иные проблемы, в частности, как:

• проверить, все ли в порядке с Postgres'ом;
• быстро найти плохих клиентов и устранить их;
• выявлять тяжелые запросы;
• и другие полезные приемы с pgCenter.

Несколько месяцев назад мы анонсировали explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.

За прошедшее время вы уже воспользовались им более 6000 раз, но одна из удобных функций могла остаться незамеченной — это структурные подсказки, которые выглядят примерно так:



Прислушивайтесь к ним, и ваши запросы «станут гладкими и шелковистыми». :)

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

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



Давайте чуть подробнее рассмотрим эти кейсы — как они определяются и к каким рекомендациям приводят.

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

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

Одной из команд, с которой надо быть осторожным на таблицах с большим количеством записей, является добавление not null constraint на столбец. При добавлении данного constraint PostgreSQL приобретает access exclusive lock на таблицу, в результате чего другие сессии не могут временно даже читать таблицу; затем БД проверяет, что в столбце действительно ни одного null нет, и только после этого вносятся изменения. Под катом я рассмотрю различные варианты, как можно добавить not null constraint, лоча таблицу на минимально возможное время или даже не лоча ее совсем.

TL;DR:

1. В PostgreSQL 12+ можно добавить check constraint на таблицу, а затем "преобразовать" его в not null constraint для конкретного столбца.
2. Чтобы полностью избежать блокировки таблицы, можно напрямую внести изменения в системную таблицу pg_attribute (этот пункт подробно разбирается в статье).