Жизнь продолжается. А мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL

Релизы

Есть ощущение, что поток релизов в промежутке длиной в месяц между этим и предыдущим выпуском Postgresso заметно истончился.

Здравствуйте, меня зовут Виктор и я разработчик в компании Gems Development. Я хочу рассказать, как мы реализовывали создание и заполнение производственного календаря в Postgresql.

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

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

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

А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум,… — и, как правило, не одной.


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

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

Managing PostgreSQL at Gitlab.com. Jose Cores Finotto.

Большое спасибо! Добро пожаловать на наш разговор о PostgreSQL в Gitlab. Мы поговорим только об основных моментах. И более подробно вы можете узнать на сайте Gitlab.com.

Уже давно известно, что делать бэкапы в SQL-дампы (используя pg_dump или pg_dumpall) – не самая хорошая идея. Для резервного копирования СУБД PostgreSQL лучше использовать команду pg_basebackup, которая делает бинарную копию WAL-журналов. Но когда вы начнёте изучать весь процесс создания копии и восстановления, то поймёте что нужно написать как минимум пару трёхколёсных велосипедов, чтобы всё это работало и не вызывало у вас боль как сверху, так и снизу. Дабы облегчить страдания был разработан WAL-G.

WAL-G – это инструмент, написанный на Go для резервного копирования и восстановления PostgreSQL баз данных (а с недавнего времени и MySQL/MariaDB, MongoDB и FoundationDB). Он поддерживает работу с хранилищами Amazon S3 (и аналогами, например, Yandex Object Storage), а также Google Cloud Storage, Azure Storage, Swift Object Storage и просто с файловой системой. Вся настройка сводится к простым шагам, но из-за того что статьи о нём разрозненны по интернету – нет полного how-to мануала, который бы включал все шаги от и до (на Хабре есть несколько постов, но многие моменты там упущены).

Расшифровка доклада 2020 года Алексея Лесовского "PostgreSQL Scaling Usecases".

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

Однако классические РСУБД за счет накопленных фич нередко остаются выбором №1 при том что они также не стоят на месте и предоставляют богатый набор инструментов в плане масштабирования.

В этом докладе я буду рассматривать преимущественно PostgreSQL, варианты его масштабирования и то когда это стоит делать и как это делать правильно и как делать неправильно. В докладе будут рассмотрены следующие темы:

• Потоковая репликация и разделение read/write рабочей нагрузки
• Логическая репликация и шардирование данных
• Обеспечение высокой доступности и устойчивости к сбоям

Посвящается всем любителям анализировать логи.

В логах работающих систем рано или поздно появляются тексты каких-то ошибок. Чем таких систем больше в обозримом пространстве, тем больше вероятность ошибку увидеть. Серверы PostgreSQL, которые находятся под нашим мониторингом ежедневно генерируют от 300K до, в неудачный день, 12M записей об ошибках.

И такие ошибки — это не какой-то там «о, ужас!», а вполне нормальное поведение сложных алгоритмов с высокой степенью конкурентности вроде тех, о которых я рассказывал в статье про расчет себестоимости в СБИС — все эти deadlock, could not obtain lock on row in relation …, canceling statement due to lock timeout как следствие выставленных разработчиком statement/lock timeout.

Но есть ведь и другие виды ошибок — например, you don't own a lock of type ..., которая возникает при неправильном использовании рекомендательных блокировок и может очень быстро «закопать» ваш сервер, или, мало ли, кто-то периодически пытается «подобрать ключик» к нему, вызывая возникновение password authentication failed for user …

^{[источник КДПВ]}

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

Расшифровка доклада 2015 года Алексея Лесовского "Deep dive into PostgreSQL internal statistics"

Disclaimer от автора доклада: Замечу что доклад этот датирован ноябрем 2015 года — прошло больше 4 лет и прошло много времени. Рассматриваемая в докладе версия 9.4 уже не поддерживается. За прошедшие 4 года вышло 5 новых релизов в которых появилась масса новшеств, улучшений и изменений относительно статистики и часть материала устарела и не актуальна. По мере ревью я постарался отметить эти места чтобы не вводить тебя читатель в заблуждения. Переписывать же эти места я не стал, их очень много и получится в итоге совсем другой доклад.

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

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



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

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

В самом сердце проекта Actual, который предназначен для управления персональными финансами, лежит система синхронизации данных собственной разработки. Недавно я реализовал в проекте полное сквозное шифрование (оно, правда, пока не вышло в продакшн). Эта работа вдохновила меня на исследование производительности внутренних механизмов системы. Сегодня я хочу рассказать об одной возможности PostgreSQL, которая позволила добиться 9-10 кратного увеличения производительности проекта.

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

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

Сегодня мы поговорим про расчет себестоимости в СБИС:

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

Расшифровка доклада 2015 года Ильи Космодемьянского "Linux tuning to improve PostgreSQL performance"

Disclaimer: Замечу что доклад этот датирован ноябрем 2015 года — прошло больше 4 лет и прошло много времени. Рассматриваемая в докладе версия 9.4 уже не поддерживается. За прошедшие 4 года вышло 5 новых релизов PostgreSQL вышло и 15 версий ядра Linux. Если переписывать эти места, то получится в итоге другой доклад. Но здесь рассмотрен фундаментальный тюнинг Linux для PostgreSQL, который актуален и сейчас.

Расшифровка доклада 2014 года Ильи Космодемьянского "Как PostgreSQL работает с диском".

Часть поста, конечно, устарела, но здесь рассмотрены фундаментальные моменты PostgreSQL при работе с диском, которые актуальны и сейчас.

Диски, память, цена, процессор — в таком порядке смотрят на характеристики сервера админы, покупающие машину под базу данных. Как эти характеристики взаимосвязаны? Почему именно они?

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

"Железо", настройки операционной системы, файловой системы и PostgreSQL: как и для чего выбирать хороший setup, что делать, если конфигурация "железа" не оптимальна, и какие ошибки могут сделать бесполезным самый дорогой RAID-контроллер. Увлекательное путешествие в мир батареек, "грязных" и "чистых" страниц, хороших и плохих SSD-дисков, покрасневших графиков мониторинга и ночных кошмаров системных администраторов.

Расшифровка доклада/tutorial "Управление высокодоступными PostgreSQL кластерами с помощью Patroni". А.Клюкин, А.Кукушкин

Patroni — это Python-приложение для создания высокодоступных PostgreSQL кластеров на основе потоковой репликации. Оно используется такими компаниями как Red Hat, IBM Compose, Zalando и многими другими. С его помощью можно преобразовать систему из ведущего и ведомых узлов (primary — replica) в высокодоступный кластер с поддержкой автоматического контролируемого (switchover) и аварийного (failover) переключения. Patroni позволяет легко добавлять новые реплики в существующий кластер, поддерживает динамическое изменение конфигурации PostgreSQL одновременно на всех узлах кластера и множество других возможностей, таких как синхронная репликация, настраиваемые действия при переключении узлов, REST API, возможность запуска пользовательских команд для создания реплики вместо pg_basebackup, взаимодействие с Kubernetes и т.д.

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

• область применения: какие задачи HA успешно решаются Patroni
• обзор архитектуры
• создание тестового кластера
• утилита patronictl
• изменение конфигурации PostgreSQL для кластера, управляемого Patroni
• мониторинг с помощью API
• подходы к переключению клиентов
• дополнительные возможности: ручное переключение, перезагрузка по расписанию, режим паузы
• настройка синхронной репликации
• расширяемость и универсальность
• частые ошибки и их диагностика

В прошлой статье, где я рассказывал о мониторинге БД PostgreSQL, была такая фраза:

Растут wait — приложение в кого-то «уперлось» на блокировках. Если это уже прошедшая разовая аномалия — повод разобраться в исходной причине.

Такая ситуация — одна из самых неприятных для DBA:

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

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

Но как? Ведь, в отличие от запроса с его планом, который позволяет детально понять, на что пошли ресурсы, и сколько времени это заняло, подобных наглядных следов блокировка не оставляет после себя…

Разве что короткую запись в логе:

process ... still waiting for ...

А давайте попробуем зацепиться именно за нее!

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

Зачем нужен шаблонизатор в базе? Ну, во-первых, если шаблонизатор в базе, то и сами шаблоны тоже должны быть в базе. А зачем нужно хранить шаблоны в базе? Да потому, что шаблоны, как и данные, тоже могут зависеть от времени. Например, пусть в базе есть счета (это данные). Очевидно, что они зависят от времени: в этом месяце сумма одна, в следующем — другая, потом — третья и т.д. Но и шаблон счёта тоже может зависеть от времени: в этом году один, а в следующем уже другой (как это было с введением 20% ). Поэтому удобнее сами шаблоны тоже хранить в базе. Ну а шаблонизатор в базе удобен тем, что можно тут же в базе шаблонизировать, потом (тут же в базе) преобразовать в pdf и (тут же в базе) отправить на email. И всё это можно сделать асинхронно с помощью планировщика.

Жизнь продолжается. Продолжаем знакомить вас с самыми интересными новостями PostgreSQL

Главная новость

PostgreSQL 13beta

Вышла долгожданная Бета. Новшества и изменения подробно расписаны здесь, а информация для бета-тестировщиков тут. Кроме того есть и советы по установке на конкретные ОС: вот, например, пошаговая инструкция для RHEL/CentOS/Fedora.

В статье "Много ли нового в Чёртовой Дюжине?" мы уже подробно описали, чего нового ждать от версии (ответ: много). А если есть повод обсудить тему с англоязычными коллегами, то есть и английская версия.

Появляются и статьи, посвященные отдельным новым фичам. Габриэле Бартолини (Gabriele Bartolini, 2ndQuadrant) рассказывает, например о Backup manifests and pg_verifybackup in PostgreSQL 13. А о (auto)VACUUM вы найдёте 2 ссылке в нашем разделе статьи.

Релизы

PostgreSQL 12.3

Эта плановая версия решает в том числе проблему безопасности, найденную в инсталляторе для Windows. Вместе с ней вышли и 11.8, 10.13, 9.6.18, и 9.5.22. До этого можно было запустить зловредный код в директориях, куда был загружен инсталлятор, или в рабочей директории. Теперь четко прописаны пути исполняемых файлов. Проблема заведомо существовала и до 9.5, но это уже останется заботой обладателей этих старинных версий.

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

---

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

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

О себе

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

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

Мотивация

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

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