SQL *

В преддверии старта курса «MS SQL Server Developer» подготовили для вас еще один полезный перевод.

---

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

ClickHouse — open-source DBMS от Яндекса — традиционно используется для аналитики различного рода логов или потоков событий от онлайн-систем. Однако, гибкость ClickHouse позволяет применять его для более широкого класса задач.

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

Пусть у нас есть реестр документов, с которым работают операторы или бухгалтеры в СБИС, вроде такого:



Традиционно, при подобном отображении используется или прямая (новые снизу) или обратная (новые сверху) сортировка по дате и порядковому идентификатору, назначаемому при создании документа — ORDER BY dt, id или ORDER BY dt DESC, id DESC.

Типичные возникающие при этом проблемы я уже рассматривал в статье «PostgreSQL Antipatterns: навигация по реестру». Но что если пользователю зачем-то захотелось «нетипичного» — например, отсортировать одно поле «так», а другое «этак» — ORDER BY dt, id DESC? Но второй индекс мы создавать не хотим — ведь это замедление вставки и лишний объем в базе.

Можно ли решить эту задачу, эффективно используя только индекс (dt, id)?

Вот и меня посетило желание что-нибудь написать для читателей Хабра. Чем же ещё заняться в отпуске?

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

Добрый день! Я — Виктор, разработчик в Gems development. Ежедневно наша команда работает с пространственными данными разной сложности и качества. При выполнении операции пространственного пересечения с помощью Postgis в СУБД Postgresql мы столкнулись со следующей ошибкой:

XX000: GEOSIntersects: TopologyException: side location conflict at 10398.659 3844.9200000000001

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


… в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана:


В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать.

С транскриптом первой части, посвященной типовым проблемам производительности запросов и их решениям, можно ознакомиться в статье «Рецепты для хворающих SQL-запросов».

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

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

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

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

Описание предметной области

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

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

Перевод статьи подготовлен в преддверии старта курса «MS SQL Server Developer».

---

Может ли запрос, выполняющийся параллельно, использовать меньше CPU и выполняться быстрее, чем такой же запрос, выполняющийся последовательно?

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


Примечание — TSQL скрипт в виде текста находится в конце статьи.

Развитие темы Этюд по реализации Row Level Secutity в PostgreSQL и для развернутого ответа на комментарий.

Использованная стратегия подразумевает использование концепции «Бизнес-логика в БД», что было чуть подробнее описано здесь — Этюд по реализация бизнес-логики на уровне хранимых функций PostgreSQL

Теоретическая часть отлично описана в документации Postgres Pro — Политики защиты строк. Ниже рассмотрена практическая реализация конкретной бизнес задачи — ролевая модель доступа к данным.

В статье ничего нового, нет скрытого смысла и тайных знаний. Просто зарисовка о практической реализации теоретической идеи. Если кому интересно — читайте. Кому не интересно — не тратьте свое время зря.

В качестве дополнения к Этюд по реализация бизнес-логики на уровне хранимых функций PostgreSQL и в основном для развернутого ответа на комментарий.

Теоретическая часть отлично описана в документации Postgres Pro — Политики защиты строк. Ниже рассмотрена практическая реализация маленькой конкретной бизнес задачи — скрытия удаленных данных . Этюд посвященный реализации Ролевой модели с использованием RLS представлен отдельно.

В статье ничего нового, нет скрытого смысла и тайных знаний. Просто зарисовка о практической реализации теоретической идеи. Если кому интересно — читайте. Кому не интересно — не тратьте свое время зря.

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

Таблица счетчиков

Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE…

Так делать не надо! Потому что завтра же вам придется решать проблемы:

• постоянных пересекающихся блокировок при UPDATE
  см. PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»
• постепенной деградации скорости доступа к данным таблицы счетчиков
  см. PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой
• … и необходимости ее зачистки при активных транзакциях, которые будут вам мешать
  см. DBA: когда пасует VACUUM — чистим таблицу вручную

Побудительным мотивом к написанию этюда послужила статья «В карантин нагрузка выросла в 5 раз, но мы были готовы». Как Lingualeo переехал на PostgreSQL с 23 млн юзеров. Так же показалось интересной статья опубликованная 4 года назад — Реализация бизнес-логики в MySQL.

Показалось интересным то, что одна и та же мысль-"реализовать бизнес-логику в БД".



пришла в голову не только мне одному.

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

Описанные методы не являются каким то открытием и исключительным know how, все по классике и было реализовано неоднократно (я например подобный подход применил 20 лет назад на Oracle).Просто решил собрал все в одном месте. Вдруг кому пригодится. Как показала практика — довольно часто одна и та же идея приходит независимо разным людям. Да и для себя оставить на память, полезно.

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

Работу с колоночными базами данных я начал с BigQuery. Когда пришлось “переехать” на Clickhouse я был неприятно удивлен фактом отсутствия полноценных оконных функций. Есть, конечно, множество функций по работе с массивами, функций высшего порядка и прочие функции (одна функция runningDifferenceStartingWithFirstValue чего стоит). Сразу на ум приходит победитель 1999 года на звание самого длинного слова Donaudampfschifffahrtsgesellschaftskapitänswitwe. Что в переводе с немецкого означает «вдова капитана пароходного общества на Дунае».

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

Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро — тогда появляется масса возможностей.

Например, можно менеджеру сразу показать из какого города идет звонок, подтянуть актуальный прайс и условия доставки, вывести карточку звонящего клиента, последние сделки с ним, конкретное контактное лицо,… — да много чего полезного, как это умеет наш СБИС CRM!

А как этот функционал реализовать самостоятельно? Оказывается, не так уж сложно. Собрать и опробовать работающую модель можно, буквально, «на коленке» — нужна только связка из Node.js и PostgreSQL.

Введение

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

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

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

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

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

Жизненный цикл нововведений 14 версии состоит из 5 коммитфестов. Первый из которых — июльский — уже завершился, а значит есть что обсудить.

Перевод статьи подготовлен в преддверии старта курса «DevOps практики и инструменты».

---

Метод RED (Rate, Errors, Duration) является одним из популярных подходов к мониторингу производительности. Он часто применяется для мониторинга микросервисов, хотя ничего не мешает использовать его для баз данных, таких как MySQL.

В Percona Monitoring and Management (PMM) v2 вся необходимая информация собирается в базу данных ClickHouse, и дальше уже дело техники с помощью встроенного источника данных ClickHouse создать дашборд для визуализации метрик.

При создании дашборда помимо панелей для RED были добавлены несколько дополнительных панелей, чтобы показать некоторые интересные вещи, которые можно сделать с Grafana + ClickHouse в качестве источника данных и информацией, которую мы храним о производительности запросов MySQL.

Давайте посмотрим на дашборд внимательнее.

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