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

---

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

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

О себе

Здравствуй, Хабр! Меня зовут Павел, я работаю техническим директором в компании, занимающейся производством IoT устройств. Производим много чего — начиная от контроллеров для умных домов, заканчивая умными приборами учёта на своём запатентованном протоколе сенсорных сетей.

Также исполняют обязанности генерального директора ит-компании. В прошлом полуфиналист ЧМ по программированию ACM ICPC.

Мотивация

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

Эмбеддинг – это отображение из дискретного вектора категориальных признаков в непрерывный вектор с заранее заданной размерностью.

Geometry of Flowers by Mookiezoolook

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

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

Привет, Хабр! Меня зовут Максим Васильев, я работаю аналитиком и менеджером проектов в FINCH. Сегодня я хотел бы рассказать, как с помощью ElasticSearch, мы смогли обработать 15 млн запросов за 6 минут и оптимизировать ежедневные нагрузки на сайте одного из наших клиентов. К сожалению, придётся обойтись без имён, так как у нас NDA, надеемся, что содержание статьи от этого не пострадает. Let`s go.

Добрый день. Нас зовут Илья Баштанов (разработчик, Точка-Точка) и Татьяна Воронова (аналитик данных, Центр 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, борьбе с этими проблемами и ошибками допущенными в этой борьбе.

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

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

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

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

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

• в режиме прямого чтения из PostgreSQL — до 16 000 [get/sec], сoncurrency 1024, медиана 60 [ms]. Кажды Get запрашивает данные из двух таблиц общим размером 360 000 000 строк. Размер JSON 1800 байт.
• в режиме кэширования эту цифру можно поднять до 100 000 — 120 000 [get/sec], сoncurrency 1024, медиана 2 [ms]. Transfer rate в пределах 200-250 [MB/sec]
• на вставку в PostgreSQL — около 10 000 [insert/sec].

В сложных 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 проектами всё и началось.