PostgreSQL *

Добрый день. Нас зовут Илья Баштанов (разработчик, Точка-Точка) и Татьяна Воронова (аналитик данных, Центр 2М). И мы хотим рассказать о технической реализации задачи подбора грузов для перевозок.

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

Решаемые задачи для бизнеса в данном случае:

1. Максимально эффективно загружать транспортные средства и тем самым увеличить доход от перевозок.
2. Решать задачу доставки в приемлемые сроки для пользователя (включая принцип FIFO).

24 апреля мы провели онлайн-митап MySQL@Scale, посвященный проблемам масштабируемости MySQL. Участвовали спикеры из Avito, Badoo и ECOMMPAY: Андрей Аксенов (автор Sphinx, лид инфраструктуры поиска), Евгений Кузовлев (CIO ECOMMPAY), Владимир Федорков (MySQL эксперт/DBA в ECOMMPAY) и Николай Королев (MySQL эксперт/DBA в Badoo).

Митап вышел длинным, поэтому мы решили публиковать его частями, и начать с конца — с очень интересной на наш взгляд дискуссии о популярности MySQL и PostgreSQL, причинах роста популярности PostgreSQL, ORM, impedance mismatch, фрактальных индексах, гневе, отрицании, торге и настройке автовакуума и прочих проблемах выбора СУБД разработчиками гостевых книг на NodeJS. Внимание! Имеется не очень цензурная лексика, ряд некорректных обобщений были заменены, а любые совпадения случайны и ни в коем случае не носят оскорбительного характера.

Я уже рассказывал, как мы «ловим» проблемы PostgreSQL с помощью массового мониторинга логов на сотнях серверов одновременно. Но ведь кроме логов, эта СУБД предоставляет нам еще и множество инструментов для анализа ее состояния — грех ими не воспользоваться.

Правда, если просто смотреть на них с консоли, можно очень быстро окосеть без какой-либо пользы, потому что количество доступных нам данных превышает все разумные пределы.


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

Сегодняшняя статья — о том, какие выводы можно сделать, наблюдая в динамике различные метрики баз PostgreSQL-сервера, и где может скрываться проблема.

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

---

Введение

В этой статье я познакомлю вас с GCP SQL и покажу как создать в этом сервисе сервер PostgreSQL.

Для приготовления получения типов колонок за один запрос нам понадобится postgres. Можно также воспользоваться готовым образом.

Postgres знаменит своей расширяемостью, что относится и к поддержке процедурных языков (PL). Никто не может похвастаться языком списком языков такой длины, а потенциально этот список и вовсе не ограничен: для того, чтобы подключить язык к серверу, не требуется сверхусилий. Можно даже придумать собственный язык и сделать его серверным процедурным языком. Переделок в СУБД это не потребует. Как и многое другое, такая расширяемость была заложена с самого начала в архитектуру Postgres.

Можно и иногда нужно писать PL-языки под задачи. А еще лучше, если кто-то напишет такой фреймворк для написания языков, чтобы можно было писать не на C, а выбрать более комфортный для разработчика языков язык. Как с FDW, которые можно писать на Python.

Эта статья написана на основе ряда докладов и мастер-классов по этой теме, делавшихся автором на конференциях PgConf.Russia 2019, PgConf.Russia 2018 и DevConf 2017.

Речь пойдет не об экзотике, а о самых распространённых процедурных языках PL/Perl, PL/Python и PL/V8 (то есть JavaScript) и сравнении их возможностей с PL/pgSQL.

Публикую на Хабр оригинал статьи, перевод которой размещен в блоге Codingsight.

Что будет в этой статье?

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

По мере публикации буду добавлять ссылки на следующие части:
Часть 1. Сложности парсинга. Истории о доработке ANTLR напильником
Часть 2. Оптимизация работы со строками и открытия файлов
Часть 3. Жизнь расширений для Visual Studio. Работа с IO. Необычное использование SQL
Часть 4. Работа с исключениями, влияние данных на процесс разработки. Использование ML.NET

За время работы произошло много интересного: мы нашли несколько багов в .NET, оптимизировали некоторые функции во много раз, а некоторые лишь на проценты, что-то делали очень круто и с первого раза, а что-то у нас не получалось даже после нескольких попыток. Моя команда занимается разработкой и поддержкой языковых функций IDE, главная из которых автодополнение кода. Отсюда и название цикла статей. В каждой их частей я буду рассказывать несколько историй: некоторые об успехах, некоторые о неудачах.

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

UPD. Ссылка на новый репозиторий проекта с поддержкой развертывания в Kubernetes

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

Первая часть шаблона посвящена HTTP серверу:

• настройка HTTP сервера через командную строку и конфигурационный файл
• настройка параметров TLS HTTP сервера
• настройка роутера и регистрация HTTP и prof-обработчиков
• настройка логирования HTTP трафика, логирования ошибок в HTTP
• HTTP Basic и MS AD аутентификация, JSON Web Token
• запуск сервера с ожиданием возврата в канал ошибок
• использование контекста для корректной остановки сервера и связанных сервисов
• настройка кастомной обработки ошибок и кастомного логирования
• сборка кода с внедрением версии, даты сборки и commit

Вторая часть шаблона посвящена прототипированию REST API.
Ссылка на репозиторий проекта осталась прежней.

Третья часть посвящена развертыванию шаблона в Docker, Docker Compose, Kubernetes (kustomize).

Пятая часть посвящена оптимизации Worker pool и особенностям его работы в составе микросервиса, развернутого в Kubernetes.

В ходе тестирования шаблона на стенде были получены следующие результаты.

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



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

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

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

И пока вы всматриваетесь в глубину иерархии, она терпеливо ждет, насколько же [не]эффективными окажутся ваши «наивные» способы работы с такой структурой.


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

Сделал dashboard Postgresql overview для postgres_exporter.

Чем отличается от других дашбородов postgres_exporter?

Я объединил все другие дашборды postgres_exporter в один.

Этот дашборд показывает общую информацию по кластеру.

Скриншоты и краткая инструкция по установке: postgresql, postgres_exporter, prometheus, grafana под катом.

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

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

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

В 2018-м году, работая в Akvelon Inc., я собеседовал одного человека. Перед интервью мне дали на проверку его тестовое задание: небольшое web-приложение по типу записной книжки или todo-списка – React\TypeScript, C# на бэке и MS SQL Server в качестве персистентного хранилища. Приложение было модное: с обилием unit-тестов на mock’ах, упакованное в docker-образ – видно, что человек старался. И у этого решения был всего один недостаток – оно не работало. Совсем. Падало при попытке сохранить новую строку в базу данных.

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

Первая из них – ложная уверенность от модульных тестов. Даже 100% покрытие кода тестами не гарантирует, что в нём нет ошибок.

И вторая – отсутствие функциональных тестов. Если ваше приложение работает с СУБД, то вы обязательно должны покрыть эту часть кода реальными тестами с реальной базой данных. И здесь есть очень важное условие: проверять нужно именно на той версии СУБД, которая работает у вас в production’е. Думаю, очень многие разработчики под Oracle, прогоняющие свои тесты на H2\HSQLDB, сталкивались с ситуацией, когда тесты проходят, а production не работает (boolean, group by и другие чудеса).

Сейчас я работаю в основном с PostgreSQL и мигрирую наши микросервисы с 10-й версии на 11-ую. В процессе миграции (и разработки вообще) я столкнулся с несколькими нюансами, о которых хотелось бы рассказать.

Для приготовления auto-failover и auto-rejoin в docker swarm нам понадобится docker, postgres, repmgr, pgbouncer, runit и gluster. Можно также воспользоваться готовым образом.

В этой статье я расскажу о том, как создал шаблон (cookiecutter) и настроил окружение для написания REST API сервиса на С++ с использованием docker/docker-compose и пакетного менеджера conan.

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

Так вот, перед нами встала задача написать высоконагруженный сервис, основной задачей которого был препроцессинг поступающих к нему данных и запись их в БД. И после очередного перекура товарищ предложил мне, как С++ разработчику, написать этот сервис на плюсах. Аргументируя это тем, что так будет быстрее, производительнее, да и вообще, жюри будут в восторге от того, как мы умеем распоряжаться ресурсами команды. На что я ответил, что никогда не занимался такими вещами на С++ и с легкостью могу оставшиеся 20+ часов посвятить поиску, компиляции и компоновке подходящих библиотек. Проще говоря, я струсил. На том и порешили и спокойно дописали все на Python.

Предлагаю ознакомиться с расшифровкой доклада начала 2016 года Андрея Сальникова "Типовые ошибки в приложениях, которые ведут к bloat в postgresql"

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

Всем, привет!

В статье я хотел бы рассказать об одном из подходов к логированию приложения, который сильно помогал мне и коллегам при отладке, поиске ошибок и анализе проблем производительности. Про необходимость логирования было написано множество хороших статей в том числе и на Хабре, поэтому здесь нет большого смысла повторяться. Я начинал карьеру в IT с Oracle и изучал эту БД по книгам главного эксперта — Тома Кайта. Мне запомнилась его фраза по поводу логирования из книги "Effective Oracle by Design":

Instrumentation is not overhead. Overhead is something you can remove without losing much benefit. Removing (or not having) instrumentation takes away considerable functionality. You wouldn’t need to do this if your systems never break, never need diagnostics, and never suffer from performance issues. If that is true, you don’t need to instrument your system (and send me your email address, because I have a job offer for you).

С работой над Oracle проектами всё и началось.

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

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

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

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

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

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

Эффект раздувания таблиц и индексов (bloat) широко известен и присутствует не только в Postgres. Есть способы борьбы с ним “из коробки” вроде VACUUM FULL или CLUSTER, но они блокируют таблицы во время работы и поэтому не всегда могут быть использованы.

В статье будет немного теории о том, как возникает bloat, как с ним можно бороться, о deferred constraints и о проблемах, которые они привносят в использование расширения pg_repack.