SQL *

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



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

Три года назад Виктор Тарнавский и Алексей Миловидов из Яндекса на сцене HighLoad++ рассказывали, какой ClickHouse хороший, и как он не тормозит. А на соседней сцене был Александр Зайцев с докладом о переезде на ClickHouse с другой аналитической СУБД и с выводом, что ClickHouse, конечно, хороший, но не очень удобный. Когда в 2016 году компания LifeStreet, в которой тогда работал Александр, переводила мультипетабайтовую аналитическую систему на ClickHouse, это была увлекательная «дорога из желтого кирпича», полная неведомых опасностей — ClickHouse тогда напоминал минное поле.

Три года спустя ClickHouse стал гораздо лучше — за это время Александр основал компанию Altinity, которая не только помогает переезжать на ClickHouse десяткам проектов, но и совершенствует сам продукт вместе с коллегами из Яндекса. Сейчас ClickHouse все еще не беззаботная прогулка, но уже и не минное поле.

Александр занимается распределенными системами с 2003 года, разрабатывал крупные проекты на MySQL, Oracle и Vertica. На прошедшей HighLoad++ 2019 Александр, один из пионеров использования ClickHouse, рассказал, что сейчас из себя представляет эта СУБД. Мы узнаем про основные особенности ClickHouse: чем он отличается от других систем и в каких случаях его эффективнее использовать. На примерах рассмотрим свежие и проверенные проектами практики по построению систем на ClickHouse.

На прошедшей неделе вышло сразу две статьи (от Hubert 'depesz' Lubaczewski и автора самого патча Alvaro Herrera), посвященные реализованной в грядущей версии PostgreSQL 13 поддержке опции WITH TIES из стандарта SQL:2008:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

Что это, и как оно избавляет от проблем с реализацией пейджинга, о которых я рассказывал в статье «PostgreSQL Antipatterns: навигация по реестру»?

Программисты ведут ожесточенные споры о вреде и пользе хранимых процедур в базах данных. Сегодня мы отвлечемся от них и снова сделаем невероятное в невозможных условиях.

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

В чат подкаста «Цинковый прод» скинули статью о том, как некие ребята перенесли всю бизнес-логику в хранимые процедуры на языке pl/pgsql. И так как у статьи было много плюсов, то значит, есть люди, а может быть, их даже большинство, которые положительно восприняли такой рефакторинг.

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

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

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

Введение

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

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

Для начала, разберем создание базы данных в MS SQL Server для сервиса поиска соискателей на работу.

Этот материал можно перенести и на другую СУБД такую как MySQL или PostgreSQL.

Когда я начинал свою карьеру разработчика, моей первой работой стала DBA (администратор базы данных, АБД). В те годы, ещё до AWS RDS, Azure, Google Cloud и других облачных сервисов, существовало два типа АБД:

• АБД инфраструктуры отвечали за настройку базы данных, конфигурирование хранилища и заботу о резервных копиях и репликации. После настройки БД инфраструктурный администратор время от времени «настраивал экземпляры», например, уточнял размеры кэшей.
• АБД приложения получал от АБД инфраструктуры чистую базу и отвечал за её архитектуру: создание таблиц, индексов, ограничений и настройку SQL. АБД приложения также реализовывал ETL-процессы и миграцию данных. Если команды использовали хранимые процедуры, то АБД приложения поддерживал и их.

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

Продолжаем открывать для публичного доступа новый функционал нашего сервиса анализа планов выполнения запросов в PostgreSQL explain.tensor.ru. Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом…


В этом нам помогут различные варианты визуализации:

Прим. перев.: в этой статье тимлид британской компании Ticketsolve делится решением своей весьма специфичной проблемы, демонстрируя при этом общие подходы к созданию так называемых accumulating (накопительных) функций с помощью современных возможностей MySQL 8.0. Его листинги наглядны и снабжены подробными объяснениями, что помогает вникнуть в суть проблематики даже тем, кто не погружался в неё столь глубоко.

Обычная стратегия для выполнения обновлений с использованием накопительных функций в MySQL — применение пользовательских переменных и паттерна UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)).

Этот паттерн плохо работает с оптимизатором (приводя к недетерминированному поведению), поэтому от него решили отказаться. В результате возникла некая пустота, поскольку (относительно) комплексную логику теперь сложнее реализовать (по крайней мере, с той же простотой).

В статье пойдет речь о двух способах ее реализации: с использованием оконных функций (канонический подход) и с помощью рекурсивных СТЕ (общих табличных выражений).

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

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

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



За прошедшие месяцы мы сделали про него доклад на PGConf.Russia 2020, подготовили обобщающую статью по ускорению SQL-запросов на основе рекомендаций, которые он выдает… но самое главное — собирали ваши отзывы и смотрели за реальными use case.

И теперь готовы рассказать о новых возможностях, которыми вы можете пользоваться.

Так как нас не устраивала скорость и надежность исходной имплементации на Ruby, в последние несколько лет мы постепенно выводили критический функционал из нашего Rails-монолита GitHub.com и переписывали часть кода на Go. Например, на Github Satellite в прошлом году мы анонсировали — и имплементировали — возможность «более контролируемой авторизации» с использованием сервиса authzd.

Работа с authzd оказалась очень интересной и значимой для нас задачей, поскольку это был наш первый сервис на Go для работы с чтением данных из баз MySQL на продакшне в ходе веб-реквеста. У нас имелся опыт развертывания других работающих с базами MySQL-сервисов на Go, но при этом они были либо службами внутреннего контроля (наша кластерная поисковая система manticore), либо асинхронными пакетными заданиями (оркестратор резервного копирования Git gitbackups). Требования к производительности и надежности authzd отличаются от них повышенной строгостью, поскольку обычный реквест к Rails-монолиту вызывает этот сервис неоднократно.

Кроме того, проблемы с большими задержками при открытии TCP соединений на наших Kubernetes кластерах особенно влияли на пул соединений Go MySQL драйвера. Это добавляло работы, ведь именно на Kubernetes мы и развернули authzd. Одним из самых опасных самообманов программиста в этом отношении является вера в надежность сети, поскольку да, в большинстве случаев сеть действительно надежна… но как только она начинает тормозить или барахлить, нас настигают базовые проблемы таких же базовых библиотек, и все начинает рушиться.

Так чего нам в итоге стоила подготовка authzd к обработке всего нашего рабочего трафика через SQL, да еще и в соответствии с нашими целями доступности?

Geometry of Flowers by Mookiezoolook

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

Но как оценить, какая схема лучше? И что вообще значит «лучше», когда мы говорим об архитектуре БД? Команда Mail.ru Cloud Solutions предлагает познакомиться с рекомендациями Майка Алча, консультанта по разработке программного обеспечения. Нам кажется, что он довольно лаконично резюмировал некоторые принципы грамотной архитектуры.

Расшифровка доклада 2018 года Алексея Лесовского "Давайте отключим vacuum?!"

Примечание редактора: Любые рекомендации по изменению параметров всегда стоит сравнивать в других докладах

Такой призыв часто возникает, когда в PostgreSQL возникают проблемы, и главным подозреваемым оказывается vacuum (далее по тексту просто "вакуум"). По опыту, многие наступают на эти грабли, и мне с коллегам по Data Egret нередко приходится разгребать последствия, так как потом всё становится ещё хуже. Но если обратить внимание на сам вакуум, то, пожалуй, нет такого человека, который бы использовал Postgres, и при этом ничего не знал про него. Ведь история вакуума начинается относительно давно, и в интернете можно найти массу как старых, так и новых постов про вакуум, объемные дискуссии в списках рассылки. Несмотря на то, что тема вакуума подробно описана в официальной документации к PostgreSQL, новые посты и новые дискуссии будут появляться и дальше. Возможно, поэтому с вакуумом связано очень много мифов, баек, страшилок и заблуждений. Между тем, вакуум является одним из важнейших компонентов PostgreSQL, и его работа напрямую сказывается на производительности. В одном докладе невозможно рассказать про вакуум абсолютно всё, но я бы хотел раскрыть ключевые моменты, связанные с вакуумом, такие как его внутреннее устройство, основные подходы к его настройке, наблюдение за производительностью, мониторинг, и что делать в случае, когда вакуум — главный подозреваемый во всех бедах. Ну и, конечно же, хочется развеять распространенные мифы и заблуждения, связанные с вакуумом.

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

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

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

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

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

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

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

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

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

Особенности работы внутренних механизмов PostgreSQL позволяют ему быть очень быстрым в одних ситуация и «не очень» в других. Сегодня остановимся на классическом примере конфликта между тем, как работает СУБД и тем, что делает с ней разработчик — UPDATE vs принципы MVCC.

Кратко сюжет из отличной статьи:

Когда строка изменяется командой UPDATE, фактически выполняются две операции: DELETE и INSERT. В текущей версии строки устанавливается xmax, равный номеру транзакции, выполнившей UPDATE. Затем создается новая версия той же строки; значение xmin у нее совпадает с значением xmax предыдущей версии.

Через какое-то время после завершения этой транзакции старая или новая версии, в зависимости от COMMIT/ROOLBACK, будут признаны «мертвыми» (dead tuples) при проходе VACUUM по таблице и зачищены.



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

Недавно у наших добрых друзей из крупной розничной сети возникла задача ускорения поиска в 1С.

Во-первых, искать нужно было по клиентам (три справочника, 9 текстовых полей, поиск типа %like%) и всего-то по 2,5 млн записей. Сразу скажем, что полнотекстовый поиск и морфология — это пока не про Tarantool. В результате ряда экспериментов мы остановились на ElasticSearch, но т.к. он не в тему статьи, то можем написать отдельную, если будет интерес. Скажем только, что скорость выросла на порядок по сравнению с тем, что мы могли выжать из полнотекстового поиска MS SQL.

Во-вторых, нужен был поиск и подбор по товарам с выводом остатков по всем складам без дополнительных запросов. Скорость поиска должна была быть сопоставима с обычным откликом интерфейса, то есть около 0,2 сек вместо текущих 5-12 секунд в 1С (в зависимости от уровня нагрузки). 90 тысяч строк, список номенклатур меняется не часто, примерно по 10-50 позиций в день.

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

Повышение производительности PostgreSQL после настройки параметров

Многие думают, что изменение параметров в postgresql.conf — это реальный путь к успеху. Однако это не всегда так. Конечно, чаще всего хорошие параметры конфигурации базы данных очень полезны. Тем не менее, во многих случаях реальные проблемы будут возникать из-за странного запроса, скрытого глубоко в некоторой логике приложения. Даже вполне вероятно, что запросы, вызывающие реальные проблемы, не являются теми, на которые вы обратили внимание. Возникает естественный вопрос: как мы можем отследить эти запросы и выяснить, что на самом деле происходит? Мой любимый инструмент для этого — pg_stat_statements, который всегда должен быть включен по моему мнению, если вы используете PostgreSQL 9.2 или выше (пожалуйста, не используйте его в более старых версиях).