Rdbms

Проблема

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

Конечно, они также хотели получить данные быстро. Я, как обычно, сказал: Я посмотрю, что я могу сделать и пошел поближе взглянуть на обсуждаемую таблицу. Удача никогда н покинет нас, индекс действительно не существовал, и таблица была огромной. Не проблема, мы можем просканировать таблицу, правильно? Неправильно. Если я чему-то научился за годы работы с базами данных, то этот размер имеет значение. Таблица с сотнями миллионов записей, состоящая из нескольких целочисленных столбцов, была бы достаточно грозной. Затем добавьте различные столбцы varchar и datetime. Теперь это настоящий вызов, не так ли?



Таблица, на которую я смотрел, имела миллиард записей, буквально. Простое сканирование таблицы могло легко занять больше суток, и мне нужно было также обработать извлеченные данные. Кроме того, сканирование таблицы такого размера может оказаться не таким уж благоприятным для общего состояния системы, как кажется на первый взгляд. Все отсканированные страницы должны быть извлечены с дисков в память sql сервера, заполнив её. Это, в свою очередь, выгрузит из кеша страницы данных, которые могут быть использованы для текущих оперативных задач. Запросы ваших текущих пользователей могут ждать дольше, пока сервер перечитывает данные с дисков, вместо того, чтобы быстро повторно использовать страницы данных из памяти. Производительность может снизиться, и в худшем случае сервер может быть полностью поставлен на колени. Таким образом, при сканировании таблицы следует использовать особую технику запускать ее небольшими партиями, сохраняя где-то текущую позицию и промежуточные результаты. Такой подход позволяет серверу перенастраиваться и иметь передышку, не допуская большой просадки производительности.

Поиск

Для всех кто, как и я, закончил обучение довольно давно, переподготовка по теории должна быть в порядке вещей. Бинарный поиск это простой, но чрезвычайно эффективный способ поиска значения в отсортированном массиве (в нашем случае в столбце таблицы). Массив должен быть предварительно отсортирован и конечен. Поиск осуществляется путем сравнения среднего элемента вашего массива с целью. Если они равны, то поиск окончен. Если нет, вы удаляете половину, в которой искомый элемент заведомо отсутствует, и повторяете предыдущий шаг пока не останется один. Найдите середину, сравните цель с ней, если они не равны, снова уберите половину и так далее. Общее количество шагов, необходимых для нахождения цели в худшем случае, когда вы найдете ее к самому последнему шагу, будет log(N), где N число элементов. Сравните это с N / 2, когда вы просто перебираете массив. Вообще говоря, это было бы N, но, если бы мы могли догадаться, ближе ли цель к концу, мы могли бы вернуться назад, таким образом уменьшая общее количество необходимых шагов.

В моем случае, N=1,000,000,000 и вот как я пришел к двум числам выше: 30 и 500,000,000. Идентификатор (ID) играл бы роль перечислителя массива, а datetime создания был бы значением элемента массива. Хотя здесь есть одна оговорка. Перечислитель массива, по самому определению, является непрерывной последовательной последовательностью целых чисел. В идентификаторах таблиц легко могут быть пробелы, из-за удаления записи или повторного заполнения идентификатора. Простое определение середины путем деления диапазона идентификаторов на 2, не следует ожидать, что там будет запись с таким идентификатором. Вместо прямого поиска мне пришлось использовать функцию top (). Что-то вроде этого:

Select top(1) * from Table where id <= @id order by id desc

Я использовал <= и desc потому что я хотел найти значение либо равное или непосредственно перед целью. При условии @l, @r это левая и правая границы соответственно, id это середина, @dt это время создания (creation datetime), tdt это цель и idr реальный идентификатор таблицы (ID), алгоритм может выглядеть следующим образом:

while @l <@rbegin -- найти середину @id = @l +floor((@r-@l)/2) -- найти запись в таблице select top(1) @idr = id, @dt = creation_datetime from Table where id <= @id order by id desc -- переместить границу if(@dt<@tdt) @l = @id +1 else @r = @idend 

Последний найденный idr был бы идентификатор записи, после которой была нужная. Алгоритм имеет что-то вроде левого смещения, то есть тенденция выбирать крайнее левое из всех значений. Так как я хотел, чтобы запись со значением была как можно ближе к цели, я также проверил ближайших левых и правых соседей в таблице чтобы увидеть, был ли один из них лучше. Обратите внимание, что я не использовал реальный идентификатор из таблицы для процесса итерации, как в этом случае, с пробелами в значениях ID и при некоторых обстоятельствах, алгоритм мог уйти в бесконечный цикл.

Написание и тестирование скрипта заняли у меня около часа. Используя его, я нашел первую запись с определенной датой и временем создания. После этого я использовал простой оператор select с предложением where, которое содержало оба условия: id >= @ and creation_datetime >= @dt1 and creation_datetime < @dt2. Мне нужно было только убедиться, что оптимизатор выбрал бы использование первичного ключа вместо индекса или сканирования таблицы. В общем, я сделал это менее чем за 2 часа! Было удивительно вновь обнаружить, что алгоритмы не являются чем-то эзотерическим, похороненным глубоко внутри sql сервера, а скорее тем, что можно легко использовать в повседневной работе.

Ниже весь скрипт, который я написал. Пожалуйста, смотрите комментарии внутри, чтобы понять, как его использовать.

/* Запустите этот скрипт на вашей бд Он найдет значение, равное или непосредственно перед целью. Обратите внимание, что точность datetime ограничена 3 мс*/-- флаг отладки, если установлен, он будет показывать результаты для каждой итерацииdeclare @debug bit = 0-- @Table - имя таблицы, в которой вы хотите искать.declare @Table varchar(128) = 'TableX' -- @Id - имя столбца идентификатора (id) для таблицы declare @ID varchar(128) = 'Id' -- @DateTimeColumn - имя вашего datetime столбца со временем создания в таблицеdeclare @DateTimeColumn varchar(128) = 'created_dt'-- это целевое значение даты и времениdeclare @TargetDateTime datetime = '2020-01-03 18:23:03'declare @Sql varchar(max)set @sql = '-- это ваш отладочный флагdeclare @debug bit = <debug>-- это ваше целевое значениеdeclare @tdt datetime = ''<TargetDateTime>''-- в этой переменной мы сохраняем промежуточное значение (результат деления) declare @id bigint -- это ваши левая и правая границы соответственноdeclare @l bigint, @r bigint-- это переменные, в которых мы храним результаты текущего шага поиска, datetime и идентификатор таблицы соответственноdeclare @dt datetime, @idr bigint-- найти первые левые и правые значенияselect @l = min(<ID>), @r = max(<ID>) from <Table>while @l < @rbegin -- ожидаемый идентификатор set @id = @l +floor((@r-@l)/2) -- найти значение и идентификатор записи select top(1) @dt = <DateTimeColumn>, @idr = <ID> from <Table> where <ID> <= @id order by <ID> desc -- если требуется отладка, покажите текущие значения if( @debug = 1 ) begin select ''@id'' = @id, ''@l'' = @l, ''@r'' = @r, ''@dt'' = @dt, ''@idr'' = @idr end if(@dt < @tdt) set @l = @id +1 else set @r = @idend-- проверьте, есть ли у соседних записей лучшее совпадениеdeclare @t table(id bigint, dt datetime, dlt float)insert @t(id, dt, dlt)select top(1) <ID>, <DateTimeColumn>, abs(cast(<DateTimeColumn> as float) -cast(@tdt as float)) from <Table> where <ID> < @idr order by <ID> descinsert @t(id, dt, dlt) select top(1) <ID>, <DateTimeColumn>, abs(cast(<DateTimeColumn> as float) -cast(@tdt as float)) from <Table> where <ID> > @idr order by <ID>insert @t(id, dt, dlt) select @idr, @dt, abs(cast(@dt as float) -cast(@tdt as float))select top(1) @dt = dt, @idr = id from @t order by dlt, id select ''Found Value'' = @dt, ''Record Id'' = @idr'set @sql = replace( replace( replace( replace( replace(@sql, '<ID>', @ID), '<Table>', @Table), '<TargetDateTime>', convert(varchar(50), @TargetDateTime, 121)), '<debug>', @debug), '<DateTimeColumn>', @DateTimeColumn)exec (@sql)

Озера данных (data lakes) фактически стали стандартом для предприятий и корпораций, которые стараются использовать всю имеющуюся у них информацию. Компоненты с открытым исходным кодом часто являются привлекательным вариантом при разработке озер данных значительного размера. Мы рассмотрим общие архитектурные паттерны необходимые для создания озера данных для облачных или гибридных решений, а также обратим внимание на ряд критически важных деталей которые не стоит упускать при внедрения ключевых компонентов.

Проектирование потока данных

Типичный логический поток озера данных включает следующие функциональные блоки:

• Источники данных;
• Получение данных;
• Узел хранения;
• Обработка и обогащение данных;
• Анализ данных.

В этом контексте источники данных, как правило, представляют собой потоки или коллекции сырых событийных данных (например, логи, клики, телеметрия интернета вещей, транзакции).
Ключевой особенностью таких источников является то, что необработанные данные хранятся в их оригинальном виде. Шум в этих данных обычно состоит из дублированных или неполных записей с избыточными или ошибочными полями.



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


После того, как данные приняты и очищены, они сохраняются в распределенной файловой системе (для повышения отказоустойчивости). Часто данные записываются в табличном формате. Когда новая информация записывается в узел хранения, каталог данных, содержащий схему и метаданные, может обновляться с помощью автономного краулера. Запуск краулера обычно запускается событийно, например при поступлении нового объекта в хранилище. Хранилища обычно интегрированы со своими каталогами. Они выгружают базовую схему для того, чтобы к данным можно было обращаться.

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


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

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

Компоненты платформы

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

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



Платформу можно условно разделить на несколько слоёв. Базовый слой это то место, где мы развертываем Kubernetes или его эквивалент. Базовый слой также можно использовать для обработки вычислительных задач вне компетенций озера данных. При использовании облачных провайдеров, перспективно было бы использовать уже наработанные практики облачных поставщиков (ведение журнала и аудит, проектирование минимального доступа, сканирование уязвимостей и отчетность, сетевая архитектура, архитектура IAM и т.д.) Это позволит достичь необходимого уровня безопасности и соответствия другим требованиям.

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

Уровень озера данных включает в себя все необходимые сервисы для приёма (Kafka, Kafka Connect), фильтрации, обогащения и переработки (Flink и Spark), управления рабочим процессом (Airflow). Помимо этого, он включает хранилища данных и распределённые файловые системы (HDFS), а также базы данных RDBMS и NoSQL.

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

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

Взаимодействие компонентов платформы

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

Flink принимает сообщение из узла c необработанными данных от Kafka, фильтрует данные и делает, при необходимости, предварительное обогащение. Затем данные передаются обратно в Kafka (в отдельный раздел для отфильтрованных и обогащенных данных). В случае сбоя, или при изменении бизнес-логики, эти сообщения можно будет вызвать повторно, т.к. что они сохраняются в Kafka. Это распространенное решение для в потоковых процессов. Между тем, Flink записывает все неправильно сформированные сообщения в другой раздел для дальнейшего анализа.

Используя Kafka Connect получаем возможность сохранять данные в требуемые бекенды хранилищ данных (вроде золотой зоны в HDFS). Kafka Connect легко масштабируется и поможет быстро увеличить количество параллельных процессов, увеличив пропускную способность при большой загрузке:



При записи из Kafka Connect в HDFS рекомендуется выполнять разбиение контента для эффективности обращения с данными (чем меньше данных для сканирования тем меньше запросов и ответов). После того, как данные были записаны в HDFS, serverless функциональность (вроде OpenWhisk или Knative) будет периодически обновлять хранилище метаданных и параметров схемы. В результате, к обновленной схеме становится возможно обращаться через SQL-подобный интерфейс (например, Hive или Presto).



Для последующего data-flows и управления ETL-процессом можно использовать Apache Airflow. Он позволяет пользователям запускать многоступенчатые pipline обработки данных с использованием Python и объектов типа Directed Acyclic Graph (DAG). Пользователь может задавать зависимости, программировать сложные процессы и отслеживать выполнение задач через графический интерфейс. Apache Airflow также может служить для обработки всех внешних данных. Например, для получения данных через внешний API и сохранения их в постоянном хранилище.

Spark под управлением Apache Airflow через специальный плагин, может периодически обогащать сырые отфильтрованные данные в соответствии с бизнес-задачами, и подготавливать данные для исследования специалистами по данным, и бизнес-аналитикам. Специалисты по данным могут использовать JupyterHub для управления несколькими Jupyter Notebook. Поэтому стоит воспользоваться Spark для настройки многопользовательских интерфейсов для работы с данными, их сбором и анализом.



Для машинного обучения можно воспользоваться фреймворками, вроде Kubeflow, используя возможность масштабируемости Kubernetes. Получившиеся модели обучения могут быть возвращены в систему.

Если сложить пазл воедино, мы получим что-то вроде этого:

Операционное совершенство

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

Основные принципы будут следующими:

1. Ограничить доступ пользователей;
2. Ведение мониторинга;
3. Шифрование данных;
4. Serverless-решения;
5. Использование процессов CI/CD.

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

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

Шифрование данных ещё один механизм для защиты данных. Шифрование хранимых данных может производиться при помощи системы управления ключами (KMS). Это зашифрует вашу систему хранения и текущее состояние. В свою очередь, шифрование при передаче может производиться при помощи сертификатов для всех интерфейсов и эндпоинтов сервисов вроде Kafka и ElasticSearch.

А в случае систем поиска, которые могут не соответствуют политике безопасности, лучше отдавать предпочтение serverless-решениям. Необходимо также отказаться от ручных деплоев, ситуативных изменений в любом компоненте озера данных; каждое изменение должно приходить из системы контроля версий и проходить серию CI-тестов перед развертыванием на продуктовое озеро данных (smoke-тестирование, регрессия, и т. д.).

Эпилог

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

Ну и год выдался! Подходит к концу. 21-му надо изрядно постараться, чтобы стать хуже. Но он надеемся стараться не будет. А жизнь продолжается. И мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL.

Но сначала поделимся воспоминаниями: как проводил время на хабре отдел образования компании Postgres Professional:

• Начнём с того, что под рукой с Postgresso. Из фонового и иногда побочного занятия Postgresso сместился к центру, стал новостным каналом со стабильной периодичностью примерно месяц. Мы отказались от плоского формата большой простыни с большим списком релизов и статей по 3-5 строчек на каждую. В 21-м продолжим экспериментировать, но от периодичности не откажемся.
• Наш коллективный труд PostgreSQL 13. Чертова дюжина. Первый (задержка в 37 минут после заморозки) и самый полный обзор возможностей 13 версии. Далее последовали обзоры коммитфестов: Июльский, Сентябрьский и Ноябрьский Павла Лузанова. Эта практика 20-го года будет продолжена и в 21-м. Мы часто сами на них ссылаемся а как не сослаться? Они действительно информативны.
• Жизнь в PostgreSQL и в Postgresso 26 подборка других реализаций Жизни памяти Джона Хортона Конвея, умершего от COVID-19.
• Автор статьи Серверное программирование на человеческом языке, очень понравившейся хабр-читателям Иван Панченко. Мы помогали Ивану в подготовке статьи.
• Сотрудник нашего отдела образования Павел Толмачёв написал для хабра статью о модуле aqo. Тема непростая, а тема использование ИИ для оптимизации запросов актуальна, а станет ещё актуальней.
• К тому же бОльшая часть статей была переведена на английский (спасибо Елене Индрупской за титанический труд). Это серии очень глубоких погружений Егора Рогова Locks in PostgreSQL (ru), WAL in PostgreSQL (ru), MVCC in PostgreSQL (ru) и Indexes in PostgreSQL (ru). Кроме того переведён ещё десяток статей, наиболее интересных для англоязычной аудитории. Некоторые из этих статей попадали в англоязычные обзоры самых интересный статей.

Релизы

Вышла Postgres Pro Standard 13

18 декабря 2020 года компания Postgres Professional выпустила новый релиз Postgres Pro Standard 13.1.1. Это первый из тринадцатых релизов Postgres Pro.

Среди новых возможностей:

Новое расширение pgpro_pwr (или PWR, читается как power). Это расширение позволяет получать отчёты по нагрузке, полезные для выявления наиболее ресурсоёмких операций в базе данных. Оно умеет использовать данные расширения pgpro_stats, предназначенного для сбора статистики выполнения SQL-операторов и подсчёта статистики событий ожидания. pgpro_stats обновлено. В январе мы опубликуем на хабре отдельную статью о PWR.

Появилась поддержка операционной системы ОСнова 2.0. Также исправлены ошибки в PostgreSQL 13.1. Среди этих исправлений устранение уязвимостей CVE-2020-25694, CVE-2020-25695 и CVE-2020-25696 (6 патчей сотрудников Postgres Professional).

Postgres Operator v1.6.0

Релиз поддерживает последнюю PostgreSQL 13 и новый образ Spilo 13 (спило слон по-грузински), в котором имеется Patroni 2.0 (но последняя версия Patroni на сегодня 2.0.1). Апгрейд ещё не автоматический, но сильно упростился. Проще стало развертывание pgBouncer на репликах. Подробности в чейнджлоге и в доке.

Pgpool-II 4.2.0

Изменения:

• в этом релизе теперь во всех образцах файла pgpool.conf путь к сокетам /var/run/postgresql;
• Используется единственный сегмент разделяемой памяти для всех разделяемых переменных родительского процесса pgpool;
• при старте убиваются существовавшие до того файлы сокетов watchdog

Загрузить можно отсюда.

pg_timetable: Advanced PostgreSQL Scheduling

Это шедулер, написанный на Go разработчиками Cybertec и работающий как отдельное приложение (для сравнения: pgpro_scheduler выполнен как расширение). Он умеет выполнять задания, состоящие из нескольких разнородных действий, например:

• начать транзакцию;
• записать в лог;
• загрузить файл;
• импортировать файл;
• запустить агрегирование;
• закоммитить транзакцию.

pg_timetable на гитхабе.

Новый начальник Коммитфеста

Масахико Савада (Masahiko Sawada, NTT) стал распорядителем нового Коммитфеста (предыдущий координировала Анастасия Лубенникова)

Статьи

PostgreSQL 14: Часть 3 или ноябрьское затишье (Коммитфест 2020-11)

Это изменения после ноябрьского коммитфеста, последнего в 2020. Павел Лузанов сам предлагает обратить особое внимание на вопросы:

• Не пора ли увеличивать wal_buffers?
• Можно ли перегружать хранимые подпрограммы по OUT-параметрам?
• По умолчанию pg_stat_statements собирает данные о 5000 запросов. Как понять много это или мало?
• Что будет, если в операционной системе обновится библиотека libc?

Текст огромный, так как список изменений полный. Продолжение следует после январского коммитфеста.

Обзор операторов PostgreSQL для Kubernetes: Часть 1: наш выбор и опыт и Часть 2: дополнения и итоговое сравнение"

В первой части Николай Богданов в блоге компании Флант, советовал начать с доклада на Highload++ своего коллеги Дмитрия Столярова, где тот знакомит с общими принципами работы баз данных в Kubernetes (K8s). Николай же формулирует 6 основных требований со стороны K8s и рассматривает операторы:

• Stolon. Этот довольно популярный отказоустойчивый кластер интегрирован в K8s. Но Stolon не подошёл, так как первое же (деплой из Git и с Custom Resources) из тех кубернетовских требований не удовлетворено (нет Custom).
• Crunchy Data PostgreSQL Operator разработка нашего старого postgres-знакомого CrunchyData (автор называет молодым стартапом) богат фичами, но он оттолкнул несоответствием принятым в K8s стандартным возможностям работы с ресурсами.
• Zalando Postgres Operator понравился больше всего. И возможностей много, и развивается быстро, и соответствует look & feel в глазах истых кубернетчиков.

Дальше Николай начинает работать с Crunchy Data PostgreSQL Operator, делится впечатлениям. А они не столько радужны, как хотелось. Список проблем и их решений, а также план миграции прилагаются.
Во второй части обзора, вышедшей 13-го ноября, добавились ещё два K8s-оператора:
KubeDB и
StackGres.
В результате появилась сводная таблица матрица имеющихся возможностей этих 5 операторов. Но сердце автора уже прикипело к Zalando, он объявлен лучшим вариантом для тру кубернетчика.

What are table access methods, and what is their importance to PostgreSQL?

Статья Панкаджа Капура (Pankaj Kapoor, Fujitsu) этакое обозрение уже не такой уж короткой (4 года) истории попыток интегрировать вертикальное хранение в PostgreSQL. Автор наблюдал этот процесс не как посторонний: Fujitsu, где он работает, предлагала сообществу свой Vertical Clustered Index в 2016, одновременно с патчем подобной направленности, предложенным Альваро Эррера (lvaro Herrera, 2ndQuadrant, теперь EDB). Со стороны Fujitsu внедрением Vertical Clustered Index занимался Харибабу Коми (Haribabu Komi). Но сообщество пошло другим путём: сосредоточило усилия на универсальном решении на API методов доступа к таблицам, по образцу методов доступа к индексам.

Сейчас, на конец 2019-го через слой методов доступа идёт интеграция с таблицами альтернативного типа хранения, zheap, например. Но пока только с доступом на базе кортежей, то есть до интеграции вертикального хранилища (реальный претендент Zedstore) ещё далеко.

Автор предлагает заодно ознакомиться со своей презентацией на PGCon2019.

Напомним и о vops интересном расширении Postgres Professional, поддерживающем векторные операции. Данные там группируются по значениям столбцов и хранятся в виде плиток (паркета).

Insert-Only Data Modelling To Smooth Peaks On Slow Disks

Каарел Моппел (Kaarel Moppel, Cybertec) предлагает неожиданный и даже контринтуитивный способ сглаживания пиков: вместо UPDATE данных только INSERT на время пиков нагрузки, чтобы потом, в спокойные часы разобраться с данными, вставленными в экстремальной ситуации. Выигрыш в скорости INSERT vs UPDATE на тестовых данных Каарела (100 млн записей) получился раза в 3. Конечно, этот способ подходит отнюдь не во всех случаях, но Каарел говорит об опыте конкретной проблемы заказчика, у которого не было возможности или желания апгрейдить железо из-за пиков, в то время, как в обычных условиях система справлялась.

10 Things I Hate About PostgreSQL

Под Новый Год лучше бы уж не о ненависти, а о любви. Ну да ладно. Рик Бронсон (Rick Branson), работавший в том числе с петабайтного масштаба проектами, решил подытожить 2020-й десяткой самых ненавистных ему особенностей PostgreSQL (некоторые наши спойлеры курсивом):

#1: Wraparound, чреватый катастрофой
[скорее всего когда-то в будущем XID-ы станут 64-разрядными целыми (то есть как уже давно в Postgres Pro Enterprise)];
#2: При переключении кластера (failover) могут потеряться данные;
#3: Неэффективная репликация, распространяющая испорченные данные;
#4: Частая сборка мусора в СУБД типа MVCC проходит болезненно
[Вся надежда Рика на будущий zheap];
#5: Принцип по процессу на соединение мешает масштабируемости
[Рик рассказывает, как использовал 2 слоя pgbouncer-ов и как доходило в общей сложности до миллиона процессов; а также скучает про тред-на-соединение в MySQL];
#6: Индекс по Primary Key очень прожорлив по части ресурсов
[Рик предлагает использовать индекс-таблицы];
#7: Для апгрейда мажорных версий может потребоваться остановка СУБД
[Из-за несовместимости бинарных форматов хранения файлов на диске могут потребоваться часы простоя. Это при потоковой репликации. Переход на логическую может решить проблему в будущем];
#8: Неуклюжая настройка репликации;
#9: Странная догма Никаких-подсказок-планировщику;
#10: Отсутствие компрессии на уровне блоков.

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

Waiting for PostgreSQL 14 Multirange datatypes

Как всегда активен Депеш, он же Хуберт Любашевски (Hubert Lubaczewski). Здесь он пишет о патче Александра Короткова. Как можно догадаться, многодиапазонные типы собираются из непересекающихся диапазонов. Как и диапазоны, они строятся на базе integer, bigintint, numeric, timestamp without time zone, timestamp with time zone, date.

Депеш создает многодиапазонные данные и пробует их на зуб: смотрит, какие индексы с ними уже можно создавать. btree уже точно можно, так что вот такой запрос будет ускоряться:

SELECT * FROM testWHERE ranges = '{[77.7909859996235,177.7909859996235],(1035.84122266822,1135.84122266822],(1000099.99954803,1000199.99954803]}';

How to install and configure PostgreSQL Debian/Ubuntu for developer use part 1

А здесь Депеш решил расписать шаги по установке PostgreSQL-13, настройке пользователей, редактировании pg_hba.conf и запуске PgAdmin под произвольным пользователем. Это азбука, но какие-то нюансы могут и пригодиться. Содержание следующих частей пока не анонсировано. На всякий случай напоминаем о существовании Малютки.

Waiting for PostgreSQL 14 pg_stat_statements: Track time at which all statistics were last reset.

Идёт постоянное усовершенствование pg_stat_statements. В 1-м и 3-м обзорах коммитфестов от Павла Лузанова уже было о некоторых коммитах. Депеш пишет о важном коммите Фуджи Масао (Fujii Masao): времени последнего ресета статистики. Информацию в pg_stat_statements время от времени очищают приложения и отдельные запросы:

SELECT pg_stat_statements_reset();

Теперь можно спросить у pg_stat_statements о времени последней чистки:

SELECT stats_reset FROM pg_stat_statements_info; dealloc |          stats_reset          ---------+-------------------------------       0 | 2020-12-20 12:06:02.099943+01

Postgres, PL/Python and SciPy/NumPy for Processing Images

Это продолжение статьи о сохранении картинок через Django-приложение в тип PostgreSQL bytea. На этот раз картинки ещё и обрабатывают фильтром.

Is Update The Same As Delete + Insert In PostgreSQL

Ответ: почти. И дальше Лоренц Альбе (Laurenz Albe) из Cybertec исследует это почти. Речь о блокировках при стандартном уровне изоляции: READ COMMITTED.

Session 1                     Session 2 BEGIN; UPDATE uptest SET id = 2   WHERE val = 42;                               SELECT id FROM uptest                                  WHERE val = 42                                  FOR UPDATE;  -- hangsCOMMIT;                               -- one row is returned

А в другой раз:

Session 1                     Session 2 BEGIN; DELETE FROM uptest   WHERE id = 1; INSERT INTO uptest VALUES (2, 42);                               SELECT id FROM uptest                                  WHERE val = 42                                  FOR UPDATE;  -- hangsCOMMIT;                               -- no row is returned

в первый раз возвращается 1 запись, во втором 0.
Дальше Лоренц исследует эту ситуацию, используя расширение pageinspect, да ещё и рассказывает о разнице поведения атрибутов infomask и infomask2 в этих двух случаях.

Конференции

Неопределённость сохраняется. Кто-то уже объявил о переформатировании в он-лайн.

PGCon 2021

В 2021-м пройдёт 28-го мая в сокращенном формате. От конференции осталась только Unconference, которая уместится в zoom. Записаться можно здесь.

Nordic PGDay 2021

Запланирована на 18 марта в Хельсинки. Об он-лайне пока ни слова. Год назад эта конференция была отменена из-за эпидемии.

Облака

Want more PostgreSQL? You just might like Babelfish

Этот проект откровенно ориентирован на тех, кто хочет беспроблемно мигрировать с MS SQL Server на PostgreSQL. Утверждается, что Bablefish это PostgreSQL, совместимый с SQL Server настолько, что приложения, под него написанные (в том числе с T-SQL и протоколом TDS), будут сразу работать.

Новости юриспруденции

Trademark Policy изменилась

Изменения касаются и Slonik-а то есть милой сердцам постгресистов картинки, и торговых марок.

Кто ты, бек-эндер?

Может ты бэкендер? Этот в высшей степени непростой вопрос разбирается в пространном исследовании Острые орфографические боли по всей длине слова и как от них избавиться на сайте ГЗОМ. Любители отгадывать зажмурьтесь: дальше ответы-спойлеры.

Сегодня нормативно:
Бэк-энд, бэк-энд-разработчик. В профессиональных текстах back-end-разработчик.

Соответствуют русской орфографии:
Бэкендер, бэк-эндовый.

Лет через семь могут возобладать:
Бэкенд, бэкендовый.

---

Предыдущие выпуски:
#26, #25, #24, #23, #22, #21, #20, #19, #18, #17, #16, #15, #14, #13, #12, #11 (спец), #10, #9, #8, #7, #6, #5, #4, #3, #2, #1

Привет всем уже в 21-м году. Надеемся, он будет добрей к нам, чем прошлый. Жизнь продолжается. И мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL. Для разнообразия начну с конференций: этот жанр больше всего пострадал.

Конференции

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

Nordic PGDay 2021

Отменена. Рассчитывают на Хельсинки в марте 2022. Виртуального варианта не будет, но собираются сфокусироваться на PostgreSQL-треке FOSDEM 2021 в феврале. На сайте написано 2022, но имеется в виду, судя по всему FOSDEM 2021, о котором ниже.

А вот подход Highload++. Бескомпромиссный никакого онлайна:
Highload++ 2020 (2021)

Конференцию HighLoad++ не стали переносить в онлайн решили, что она для этого слишком масштабная. Но даты передвинули с 9-10 ноября 2020 г. на 20-21 мая 2021 года. Должна пройти в Москве в Крокус Экспо 3.

А вот полная противоположность:
FOSDEM 2021

Никакого Брюсселя, в 2021 только онлайн. Не только бесплатно, но и регистрации даже не требуется. Среди участников этой огромной конференции немало докладчиков, известных среди российских постгресистов: Олег Бартунов, Павел Борисов, Алексей Кондратов, Анастасия Лубенникова, Никита Глухов (Postgres Professional), Николай Самохвалов (Postgres.ai), Пётр Зайцев (Percona), Андрей Бородин (Yandex), Олег Иванов (Samsung AI Center, он автор плагина AQO в Postgres Pro Enterprise).
Расписание можно попробовать изучить здесь. Поток PostgreSQL здесь.

PGConf.Online 2021

Последняя в этом списке, компенсирую большим количеством знаков: у меня просто больше информации.
Здесь комбинация оф и он: офлайн-конференция PGConf.Russia 2021 запланирована на на конец мая начало июня 2021 года. А 1-3 марта будет проведена онлайн-конференция с соответствующим названием PGConf.Online 2021.

Темы конференции:

• Postgres на предприятии;
• Масштабируемость;
• Высокие нагрузки и очень большие базы данных;
• devops;
• Переход на Postgres.

Участие в онлайновой конференции бесплатное. Всем желающим участвовать нужно предварительно зарегистрироваться на сайте, трансляция докладов будет вестись из личных кабинетов. Если уже оплатили PGConf.Russia 2021, то регистрироваться повторно не нужно. Регистрация действительна для обоих событий PGConf.Online и ближайшего PGConf.Russia. Также можно отказаться от участия в PGConf.Russia и вернуть свои деньги. Для этого надо написать на info@pgconf.ru.

Доклады принимаются до 10 февраля в двух форматах: кратком (22 мин + вопросы) и полном (45 мин + вопросы) на русском и английском языках. Также приветствуются мастер-классы с практическими упражнениями и обучающие лекции по вопросам расширенной разработки и DBA. Мастер-классы могут длиться 90 или 180 минут.

На PGConf.Online 2021 возможны доклады как онлайн, так и предварительно записанные, по желанию. Планируется, что даже после записанного выступления докладчик будет онлайн отвечать на вопросы, которые будут собираться в чате модераторами.

Соревнования

PostgreSQL is the DBMS of the Year 2020

СУБД года! Это не рейтинг популярности, а рейтинг роста популярности. Из рейтингов на январь 2021 вычитаются рейтинги за январь 2020. А они вычисляются по методологии экспертов db-engines. По абсолютной, а не дифференциальной популярности postgreSQL по-прежнему на 4-м месте.
О соревновании x86 с ARM в облаках см. далее.

Облака

Тема ARM в облаках набирает обороты. Что не удивительно ARM наступает широким фронтом: суперкомпьютер на ARM взобрался на верхушку Top500; новые попытки Apple; процессор Whitechapel у Google; процессоры от Ampere Computing появятся в облаках Oracle; ну и, конечно, процессоры AWS Graviton2 с ядром Arm Neoverse в исполнении Amazon.

Вот две статьи: в одной Hosting Postgres on an AWS EC2 t4g Graviton2 ARM Instance рассказывается, как запустить и настроить инстансы t4g (но ещё и о выборе EC2 vs RDS); в другой PostgreSQL on ARM-based AWS EC2 Instances: Is It Any Good? исследуется производительность. Об этом чуть подробней: Жобин Аугустин (Jobin Augustine) и Сергей Кузьмичев (Sergey Kuzmichev) из Percona тестировали ARM vs. x86. ARM на инстансах m6gd.8xlarge на базе ARM-процессоров AWS Graviton2. Сам Amazon позиционирует их как обеспечивающий на 40 % лучшее соотношение цены и производительности по сравнению с показателями x86-инстансов M5 в тестах m5d.8xlarge. В обоих инстансах по 32 виртуальных процессора.

Для разминки прогнали на pgbench, ARM выиграл и на Read-Write и на Read-Only в районе 20%. При этом тестировщики не забывали отключать и включать проверку контрольных сумм мало ли что, архитектура разная. Затем перешли к основным перконовским тестам sysbench-tpcc. Размер базы подбирали так, чтобы она умещалась в память. Стали смотреть результаты на числе потоков от 16 до 128. Получилось, что на 16 примерно та же картина, как и на pgbench, а когда потоков больше, чем виртуальных процессоров, игра в ничью. Чтобы уж совсем не огорчать поклонников x86, авторы констатировали худшую производительность у ARM на тестах, оценивающих ввод-вывод. Но и то при 128 потоках. Подробности в статье и на гитхабе.

Теперь информация, связанных с апгрейдом в облаках Amazon:
Ensuring Consistent Performance After Version Upgrades with Amazon Aurora PostgreSQL Query Plan Management

Query Plan Management это расширение apg_plan_mgmt. В статье показано, как после апгрейда кластера Aurora PostgreSQL с 9.6.11 на 10.12 при помощи этого инструмента можно легко проверить, использует ли планировщик одобренный в предыдущей версии план запроса (планы могут получать статус Approved, Rejected, Unapproved, или Preferred).

Кстати, о версиях:
Amazon RDS for PostgreSQL Supports 12.5

RDS теперь поддерживает минорные версии: 12.5, 11.10, 10.15, 9.6.20 и 9.5.24.

Релизы

pgAdmin 4 v4.30

В этой версии появился (пока в статусе бета) новый инструмент: ERD диаграммы сущность-связь, то есть графическая репрезентация таблиц, столбцов и их взаимосвязей. Можно визуализировать уже существующие схемы БД, можно создавать мышью новые, а ERD сгенерит соответствующие SQL. Также появилась поддержка Kerberos.

PostgreSQL-плагин для Zabbix 5.2.4rc1

В новой версии появилась поддержка custom query для плагина PostgreSQL. Теперь можно создать файл .sql и положить его на свою машину. Далее в web-интерфейсе своего Zabbix-сервера в шаблоне для Zabbix-Agent2 находим элемент под названием pgsql.query.custom и в нем указываем макрос, который должен иметь значение имени sql файла с запросом (при этом в конфигурационном файле Zabbix-Agent2 нужно указать путь на машине к папке с sql файлом. И тогда агент сам выполняет запрос в sql файле и пришлет результат на Zabbix-сервер с остальными, дефолтными метриками. Автор плагина Дарья Вилкова, Postgres Professional.

Целая серия новых версий FDW:

sqlite_fdw 1.3.1
InfluxDB fdw 0.3
griddb_fdw 1.3

PostgresNIO 1.0

Это неблокирующий, event-driven клиент для Swift от Vapor, построенный на эппловской SwiftNIO. Этот клиент устанавливает соединение, авторизует и отправляет запрос на сервер, а результат обратно. Использует протокол PostgreSQL. Умеет создавать пул соединений. И ещё есть пакеты более высокого уровня поверх PostgresNIO postgres-kit.

PGMoon 12.0-1

pgmoon это клиентская библиотека, написанная на чистом Lua (MoonScript). pgmoon с самого начала была разработана для использования в OpenResty web-платформе на базе докрученного Nginx), чтобы можно было пользоваться API 100% неблокирующих cosockets для асинхронных запросов.

Ещё статьи

Расширение кластера PostgreSQL размером 5,7 ТБ и переход с версии 9.6 на 12.4

Статья в блоге Альфа-Банка, автор оригинала Томми Ли (Tommy Li, Coffee Meets Bagel приложение для романтических знакомств с системой курирования).

Базы работали на 6 серверах Postgres на инстансах i3.8xlarge в амазоновском облаке: одна главная нода, три реплики для раздачи веб-трафика только для чтения, балансируемые с помощью HAProxy, один сервер для асинхронных воркеров и один сервер для ETL (Extract, Transform, Load) и Business Intelligence. Для поддержания реплик в актуальном состоянии использовалась потоковая репликация.

Надо было одновременно проапгрейдить Postgres и перейти с i3.8xlarge на i3.16xlarge при минимальной суммарной остановке 4 ч. (а вышло полчаса). Для миграции использовали pglogical. Также в статье из этого опыта извлекли уроки. Эта статья вызвала справедливые и несправедливые замечания в комментариях. Так что примечателен не только сам случай, но и реакция на него, да и тот факт, что перевод статьи появился не где-нибудь, а на хабр-блоге Альфа-Банка (до этого там о базах данных ничего, кажется, не было).

PostgreSQL Scaling Advice For 2021

Каарел Моппел (Kaarel Moppel, Cybertec), чьи статьи регулярно попадают в наши обзоры, дерзнул дать советы тем, кто озабочен будущим масштабированием своих систем. Каарел признаётся, что воодушевился роликом Distributed MySQL Architectures Past, Present, Future Петра Зайцева, основателя и гендира Percona, и приложил (так как, по его, Каарела, словам, MySQL и Postgres суть сводные братья) некоторые выводы Петра к родной PostgreSQL и добавил собственные.

Итого: что даёт обычный Postgres?

• один инстанс PostgreSQL легко выполняет сотни тысяч транзакций в секунду;
• одна нода обычно выполняет десятки тысяч пишущих транзакций в секунду;
• один инстанс Postgres легко справляется с десятками ТБ данных;
• один инстанс на одной ноде даёт буквально пуленепробиваемую надёжность при должной заботе о согласованности данных;
• в причинах сбоев легко разобраться, поэтому данные можно восстановить.

О чём не стоит забывать, когда озаботился масштабированием?

• не бойтесь заводить свои собственные (а не поддерживаемые в облаке) базы данных;
• старайтесь избегать архитектуры данных, у которой в основе одна большая таблица;
• убедитесь, что вы выбрали подходящий ключ разбиения при шардинге вашей таблицы/базы.

Агрегаты в БД

Кирилл Боровиков aka Kilor (компания Тензор) на этот раз обратился к агрегатам. Это мини-серия из двух статей: Агрегаты в БД зачем, как, а стоит ли? и продолжение Агрегаты в БД эффективная обработка потока фактов. В первой движение мысли от count(*) к подсчетам с парсингом EXPLAIN, к сбору агрегатов в отдельную таблицу, к хранению временных агрегатов в памяти процесса и даже к хранению их вообще в другой СУБД.

Во второй статье агрегация обретает бизнес-очертания: пересчет остатков и ведение сводных продаж по товарам при их постоянных отгрузках, или агрегация сальдо и оборотов по бухгалтерским счетам, при массовых изменениях проводок. Цель: агрегаты по продажам в разрезе товар/день/кол-во. Разных товаров очень много и отгрузок много. Решение: конкурентная обработка очередей, чтение из курсора читайте подробности.

Образование

Чёрная Малютка

Вышла новая версия знаменитой книжки-малышки
Postgres: первое знакомство.



Книжечка проапгрейдилась до версии PostgreSQL 13. В бумажном виде она, как и раньше, будет раздаваться на конференциях, которые проходят с участием Postgres Professional. PDF можно скачать.

DEV2: Разработка серверной части приложений PostgreSQL 12. Расширенный курс.

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

Митапы и подкасты

Постгрес-вторник с Петром Зайцевым

Петра Зайцева, основателя Percona, Николай Самохвалов и Илья Космодемьянский зазывали на свои Вторники целый год. Свершилось. Был разговор о компании (из которого выяснилось, что сейчас в компании около 300 сотрудников, из них человек 50 постгресистов); о причинах дрейфа компании от MySQL и MongoDB в сторону PostgreSQL (не по любви, и не из-за технологических причин, а просто в это сторону двигались клиенты и потенциальные клиенты); о разной атмосфере в комьюнити MySQL, MongoBD и PostgreSQL (второе самое монополистическое, а третье самое открытое). Но гвоздь программы перконовская утилита мониторинга pg_stat_monitor.

Монитор опирается на расширении pg_stat_statements, но добавляет некоторую функциональность. Можно, например, посмотреть тексты запросов, отбирающих много ресурсов, сравнить прожорливость одного и того же запроса с разными планами; монитор знает название приложения, отправившего запрос. В этом контексте возник и разговор о новом расширении PWR (pgpro_pwr), вошедшем в Postgres Pro Standard и Enterprise 13. Это, кажется, обсудят на следующем Вторнике (мы же обещали статью о нём и обещание скоро сдержим).

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

Конференция PGConf.Online 2021

Она начинается уже 1-го марта и закончится 3-го. О ней подробно написано в статье Ивана Панченко, зам. гендира Postgres Professional.

На этой конференции (которая не вместо, а кроме офлайновой, теплой-ламповой, она ожидается в конце весны) будет рекордное число иностранных гостей чему явно поспособствовал онлайн-формат. В том числе на этот раз поучаствует и Саймон Риггс (Simon Riggs). Доклады в 3 потока с 10 утра до 6 вечера. А также мастер-классы.

Статьи

PostgreSQL 14: Часть 4 или январское наступление (Коммитфест 2021-01)

Очередной must read Павла Лузанова. Крупные изменения после первых трех относительно скромных коммитфестов (июльский, сентябрьский, ноябрьский).

Вопросы для затравки, предложенные Павлом:

• Могут ли диапазоны содержать пропуски значений?
• Зачем нужна индексная нотация типу json?
• Может ли индекс при частых обновлениях разрастаться меньше, чем таблица? А вообще не разрастаться?
• Сколько времени простаивали сеансы в idle_in_transaction?
• Как построить ER-диаграмму для таблиц системного каталога?

Deep PostgreSQL Thoughts: The Linux Assassin

Слово deep уже пугает: не про ИИ ли это. Но нет. Джо Конвей (Joe Conway, Crunchy Data) действительно копает вглубь. Даже не Постгреса, не своего же расширения plr. На этот раз тема Жуткий Убийца, являющийся из недр Linux OOM Killer.

Джо начинает с истории: первые дискуссии в Postgres-сообществе и первые патчи в 2003-м году как заставить киллера работать по понятиям Postgres. Далее Джо поясняет отношения киллера и Postgres на уровне хоста (oom_score и oom_score_adj) и на уровне CGroup, поясняет, почему так важно не допустить прихода киллера.

И дальше Джо Конвей переходит к специфическим проблемам OOM Killer в Kubernetes это, видимо, и был главный повод для написания этой статьи. Оказывается, что там, в K8s, разрушительная деятельность киллера может начаться даже при относительно благополучном состоянии памяти. В K8s своп по умолчанию выключен. До этого момента автор не трогал тему свопа, которая, вообще-то, очень даже обсуждается среди постгресистов-практиков.

Джо ссылается на обстоятельную статью Криса Дауна (Chris Down) In defence of swap: common misconceptions, причём есть и русский перевод (не автопереводчиком): В защиту свопа: распространенные заблуждения. О Postgres там нет речи, но может заинтересовать и постгресистов.

Также ссылается он на статью The weird interactions of cgroups and linux page cache in hypervisor environments в блоге компании StorPool, где в команде в основном болгарские фамилии.

Далее Джо Конвей плавно переходит к разработкам и усилиям Crunchy Data в треугольнике PostgreSQL Kubernetes ядро Linux.

??
Акула жуёт гугловый кабель (The Guardian??)

Things I Wished More Developers Knew About Databases

Статья не (только) о Postgres. Иногда полезно ещё разок глянуть на разные СУБД с птичьего полёта. Вот внушительный список тем, о которых стоит помнить разработчикам приложений. В статье Джоанна Доган (Jaana Dogan) не поленилась их разворачивать и развивать. Иногда в неожиданную сторону: в пункте #1 мы, например, узнаём, что гугловские кабели давеча покусали акулы. Немало SQL-примеров, схем и есть матрица PostgreSQL vs. MySQL.

• Если сеть доступна 99.999% времени, вам сильно повезло;
• ACID понимают по-разному;
• у каждой СУБД свои возможности поддержки согласованности и изоляции;
• оптимистические блокировки могут помочь, когда удерживать эксклюзивные блокировки нет возможности;
• есть аномалии кроме грязного чтения и потери данных;
• моя СУБД, в каком порядке хочу исполнять транзакции, в таком и исполняю;
• шардинг на уровне приложения не означает шардинг вне СУБД;
• AUTOINCREMENT может преподнести неприятные сюрпризы;
• устаревшие данные могут быть полезны и помогают обойтись без блокировок;
• рассогласования из-за часов;
• под задержками (latency) могут подразумевать разное;
• надо оценивать производительность не по усредненным показателям, а по критическим операциям/транзакциям;
• вложенные транзакции небезопасны;
• транзакции не должны поддерживать состояния приложений;
• планировщик поможет узнать многое о базе данных;
• миграции без останова сложны, но возможны;
• существенный рост базы данных увеличивает непредсказуемость.

Troubleshooting Performance Issues Due to Disk and RAM

Хамид Ахтар (Hamid Akhtar, HighGo, Китай) написал простенькую, но небесполезную памятку для тех, кто хочет быстро сузить круг подозреваемых при поиске проблем с железом. Начав с совсем очевидных top, free и df, он обращается к утилитам анализа производительности дисков, процессора и памяти, и предлагает полезные наборы их опций:
iostat (информация и о диске, и о процессоре), напр. iostat -dmx sda 1
sar (System Activity Report, часть пакета sysstat), напр. sar -f /var/log/sa/sa03 -b -s 02:00:00 -e 02:30:00 -r -S
dstat, напр. dstat -cdngy

А вот скриптик для анализа памяти:

#!/bin/bashgrep -A3 "MemTotal" /proc/meminfo  grep "Swap" /proc/meminfogrep -A1 "Dirty\|Active" /proc/meminfo

.

Starting with Pg where is the config?

Депеш (Хуберт Любашевски) в короткой заметке напоминает, как можно найти конфигурационные файлы, если они лежат в нестандартном месте. Способы, которыми он предлагает воспользоваться не сенсационны, но может быть полезен, скажем, удобный набор опций.
Например, так:
ps -fxao pid,command | grep -E 'post(gres|master)'
на выходе будет path. И отсюда:
sudo grep -E '(hba|ident)\.conf' <путь к postgresql.conf>
Или теперь танцуем от pid:
sudo cat /proc/<подставляем pid>/environ | tr '\0' '\n' | grep ^PG | sort
Или:
sudo lsof -p <подставляем pid> -a -d cwd
получаем каталог данных и сведения о нём.
Если такие советы не понадобились, можно порефлексировать на тему я бы сделал по-другому. Скажем, просто-напросто используя find, например.

Агрегаты в БД

Кирилл Боровиков aka kilor завершил мини-серию статей про агрегаты:

Зачем, как, а стоит ли?

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

Эффективная обработка потока фактов

как обработать БОЛЬШОЕ (капслок kilor-а) количество записей и при этом особо никого не заблокировать, включая набегающий поток данных. Например, это может быть пересчет остатков и ведение сводных продаж по товарам при их постоянных отгрузках, или агрегация сальдо и оборотов по бухгалтерским счетам, при массовых изменениях проводок.

Многомерные суперагрегаты

иерархичные агрегаты в нескольких одновременных разрезах;

Прокси-таблицы

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

Облака

Babelfish: the Elephant in the Room?

Русский перевод названия этой статьи, появившейся на сайте фонда испаноговорящего сообщества FUNDACIN POSTGRESQL звучал бы так: "Вавилонская рыбка или слона-то я и не приметил?" Мы уже упоминали, что идея проекта сверхамбициозная: Bablefish это PostgreSQL, совместимый с SQL Server настолько, что приложения, под него написанные (в том числе с T-SQL и протоколом TDS), будут сразу работать, не зная, что работают с PostgreSQL.

Автор статьи Альваро Эрнандес (lvaro Hernndez Tortosa, OnGres) начинает с рыночной конъюнктуры, чтобы дальше предъявить гамлетовский вопрос, которым авторы Вавилонской Рыбки должны были задаться: форкать или не форкать?

Babelfish пока не может работать как расширение без доработки ядра PostgreSQL. Альваро напоминает, что 25-го января заслуженный и авторитетный в сообществе человек Ян Вик (Jan Wieck) предложил обсудить расширяемость протокола PostgreSQL: сделать такие хуки, которые позволят реализовать протокол SQL Server в виде расширения без изменений в ядре. Но это процесс небыстрый. Заодно решили обсудить и совместимость с MySQL. Но что делать AWS с Bablefish, если сообщество проигнорирует этот путь или интеграция пойдёт ни шатко, ни валко? Вероятней всего, считает Альваро, AWS будет развивать Bablefish как форк (так уже случилось с Aurora), как бы им не хотелось бы обойтись без форка. А если всё же придётся, то AWS это по силам.

Далее Альваро привлекает Дилемму инноватора. И задаёт ещё один интересный вопрос: хотим ли мы (то есть сообщество), чтобы Babelfish стала MariaDB у PostgreSQL?

Персона

Очередной PG-персоной недели стал Александр Сосна, живущий в небольшом городке на Нижнем Рейне и в свободное от работы в credativ время преподающий ИТ-безопасность в Нижнерейнском Университете. Он работает над довольно необычным расширением: pg_snakeoil. Это антивирус специально для PostgreSQL: он ищет вирусы в данных так, чтобы не мешать работе базы, что отнюдь не характерно для обычных антивирусов. Как замечает Александр, за вирусами охотятся не всегда из-за их вредоносности, иногда только потому, что этого требуют нормативные документы.

Релизы

PostgreSQL 13.2

Вышли PostgreSQL 13.2, 12.6, 11.11, 10.16, 9.6.21, 9.5.25 (последний выпуск ветки 9.5). В этих релизах одолели две проблемы безопасности:
в PostgreSQL 13 можно было, имея права на SELCT одного столбца, получить при помощи изощрённого запроса все столбцы таблицы;
вторая проблема касалась версий 11, 12 и 13. Если у пользователя есть права на UPDATE партицированной таблицы, но нет прав на SELECT некоторого столбца, он мог получить данные столбца из сообщений об ошибке.
Кроме того исправлено более 80 багов.

pg_probackup 2.4.9

Появился флаг --force для инкрементального режима. Теперь можно переписывать содержимое в каталоге, указанном в PGDATA, если system-identifier в целевом экземпляре и копии НЕ совпадают (раньше приходило сообщение об ошибке).


pgAdmin 4 v. 5.0

В версии 5.0 среди прочего появилась поддержка логической репликации; поддержка публикаций и подписок в Schema Diff.

Apache AGE 0.3.0

Apache AGE это расширение, добавляющее в PostgreSQL функциональность графовой базы данных. Цель проекта единое хранилище для реляционной и графовой моделей данных, чтобы пользователи могли использовать и стандартный SQL, и языки запросов к графовым базам openCypher и GQL.

Мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL. Этот выпуск получился с некоторым уклоном в средства диагностики. Нет, не только. Например:

Хардверные ускорители: FPGA

В небольшом сообщении Энди Эликотта (Andy Ellicott) в блоге Swarm64 3 hardware acceleration options Postgres users should know in 2020 рассказывается о трёх аппаратных ускорителях, не GPU, а FRGA, и все они в облаках. У автора свой интерес: у Swarm64 есть собственное решение на FPGA-ускорителе. Значимым сигналом он считает объявление Amazon об FPGA-ускорителе кэша (FPGA-powered caching layer) в Redshift AQUA (Advanced Query Accelerator) в Amazon, который убыстряет запросы на порядок. А вообще уже почти все облака (во всяком случае Amazon, Alibaba, и Azure) используют сейчас FPGA-ускорители, просвещает нас Энди.

Итак:

Swarm64 Data Accelerator (DA)
это расширение, которое умеет переписывать обычные SQL-запросы, чтобы распараллеливать вычисления на всех этапах их исполнения, а сотни читающих или пишущих процессов будут работать параллельно на FPGA. Кроме того, там реализованы индексы columnstore, как в MS SQL Server. Есть техническое описание в PDF, но именно про FPGA в нём ничего нет. Зато есть демонстрационное видео, показывающее, как можно легко и быстро развернуть Postgres на инстансе Amazon EC2 F1 с FPGA. Ещё есть результаты тестов TPC-H (а позиционируется эта комбинация с FPGA прежде всего как ускоритель для гибридных транзакционно-аналитических нагрузок HTAP), и там показывает выигрыш в 50 раз по скорости.

Другой вариант, который предлагает Энди: Intel Arria 10 GX FPGA в связке с NVM-памятью Intel Optane DC, SSD и PostgreSQL 11 с тем же расширением Swarm64 DA. Всё это собрано в демо, которое вбрасывает в PostgreSQL потоки биржевых котировок со скоростью 200 тыс инсертов в секунду, и дальше работает с ними с обычным SQL.

Третий вариант с Samsung SmartSSD, в которой внутри FPGA-чип от Xilinx. Испытания (с тем же свормовским расширением, как можно догадаться) дали выигрыш в 40 раз на TPC-H и в 10-15 раз на JOIN-ах.

С маркетинговой точки зрения эти усилия нацелены прежде всего против хардверных решений для WH вроде Netezza или Teradata.

Обещано, что будет и сравнение эффективности FPGA vs. GPU (в т. ч. и в контексте проекта PGStrom).

(спасибо Александру Смолину за наводку в FB-группе PostgreSQL в России)

Конференции

были:

PGConf.online

Теперь выложены все видео и презентации доступ через расписание.

FOSDEM 21

Поток PostgreSQL devroom тёк два дня 6-7 февраля с 10 утра до 6 вечера. Материалов конференции очень много. Вот имеется однобокая, зато систематизированная выборка доклады от Postgres Professional (глаголы будущего времени там надо поменять в уме на глаголы прошедшего).

будет:

Highload++

Объявлено, что состоится офлайн 17 -18 мая 2021 в Крокус-Экспо, Москва. Есть Расписание. Я бы обратил особое внимание на потоки
СУБД и системы хранения, тестирование в Зале 3, например:
Микросервисы с нуля, Семен Катаев (Авито);
Прокрустово ложе или испанский сапог мифы и реальность СУБД в Облаках, Александр Зайцев (Altinity)
и на
Архитектуры, масштабируемость, безопасность в Зал 4 (главном), например:
Архитектура процессора Эльбрус 2000, Дмитрий Завалишин (Digital Zone);
SQL/JSON в PostgreSQL: настоящее и будущее, Олег Бартунов (Postgres Professional);
Распространённые ошибки изменения схемы базы данных PostgreSQL, Николай Самохвалов (Postgres.ai).

Вебинары и митапы

RuPostgres-вторник s02e13 Андрей Зубков (PostgresPro) pg_profile, pgpro_pwr

Вторник, посвященный pg_profile / PWR, так заинтересовал устроителей, что с большой вероятностью в ближайшее время можно ожидать продолжения: разобрались не во всех тонкостях работы этого весьма практичного инструмента, ну а расширения pgpro_stats, которое используется в PWR, коснулись по касательной.

После это был ещё вторник с Александром Кукушкиным (Zalando). Тема риски апгрейда мажорных версий с фокусом на PG12 и PG13, а пособник апгрейда Spilo: как выяснилось, бесшовный апгрейд в контексте Patroni задача слишком амбициозная, а вот Spilo, то есть Docker-образ с PostgreSQL и Patroni, с задачей справляется. Но опасностей и нюансов при апгрейде остаётся немало. Говорилось о сюрпризах от VACUUM, ANALYZE, о параллелизме по умолчанию, о CTE и материализации, о JIT.

Database Delivery: The Big Problem

Это была презентация от Ростелеком-ИТ, которую провёл Роман Гордеев (в видео глюки, надо прокрутить первые 11 минут). Его пригласили на один из стримов Tver.io сообщества тверских айтишников (но мне удобней было смотреть этот же ролик на на youtube). Речь шла об инкрементальной стратегии миграции. Роман рассказывал о вещах, применимых к разным СУБД и средам разработки, но для примера был выбран переход с базы PostgreSQL на H2 в графическом DataGrip. Соответственно в реальном времени наблюдались и решались проблемы с постгресовым типом text и с последовательностями.

В качестве механизма, который контролирует миграцию, был взят плагин liquibase для среды gradle. О настройках для такой работы можно почитать на страничке liquibase gradle на гитхабе Гордеева. Кстати, Ростелеком Информационные Технологии компания с населением под 2 тыс. человек. На официальной странице есть информация об опенсорсной СУБД in-memory Reindexer собственной разработки. Больше о базах там ничего пока найти не удалось.

Обучение

Выложены в общий доступ видео:

• Разработка серверной части приложений PostgreSQL 12. Базовый курс.
• Разработка серверной части приложений PostgreSQL 12. Расширенный курс

Оба эти курса появились в конце прошлого года, но теперь комплект полный: презентации и видео.

Тем, кто интересуется более пристально, советую прослушать доклад о курсах Егора Рогова на PGConf.online 2021.

Мониторинг

Monitoring PostgreSQL with Nagios and Checkmk

Пишет опять Хамид Ахтар (Hamid Akhtar, китайская компания High Go), на этот раз пишет о средствах мониторинга Nagios (рекурсивный акроним Nagios Ain't Gonna Insist On Sainthood Nagios не собирается настаивать на святости, в отличие от его предшественника NetSaint) и Checkmk. Публикация без претензий: как установить и настроить, не претендуя даже в этом на полноту.

Explaining Your Postgres Query Performance

Идём от простого к сложному. Пока URL подсказывает возможный подзаголовок статьи: Get Started with EXPLAIN ANALYZE. Кэт Бэтьюйгас (Kat Batuigas, Crunchy Data) действительно знакомит с самыми азами EXPLAIN, даже без опций. Жанр For dummies, и наглядно: показывает, как с помощью EXPLAIN ANALYZE можно наблюдать решения планировщика об (не)использовании индексов, и вообще что там происходит. Иллюстрируется это всё на базе Geonames.

Предыдущая её статья была о Query Optimization in Postgres with pg_stat_statements.

Вот ещё одна её статья: Three Easy Things To Remember About Postgres Indexes. В ней не только напоминания о том, что индекс занимает место на диске, но и, например, такие соображения:
Важен и тип запроса. Например, если в запросе есть знаки подстановки (wildcards)
wildcards, e.g. WHERE name LIKE 'Ma%',
то планировщик задействует по умолчанию индекс B-tree, но вам, возможно, стоит указать класс оператора, чтобы был выбран эффективный индекс.

Can auto_explain (with timing) Have Low Overhead?

Михаэль Христофидес (Michael Christofides) показывает работу расширения auto_explain с включённым и отключённым таймингом. Выводы:

Если задать ощутимый промежуток времени min_duration, издержки от auto_explain на небольшой транзакционной нагрузке )была меньше 1% с отключённым таймингом и ~2% с включённым. Семплинга не было, поэтому детали прослеживались для каждого запроса, но попадали в лог для медленных. А когда min_duration=0ms, и логировалось всё, издержки оказались больше 25%, даже без тайминга и ANALYZE. Видимо, издержки auto_explain связаны в основном с логированием.

Интерес у Михаэля не невинный он разработчик утилиты pgMustard, которая визуализирует планы. Она также расписывает, сколько тратится времени и сколько строк возвращает каждая операция (в т.ч. циклы; дочерние узлы планов subplans; CTE). Мало того, pgMustard умеет подсказывать. Например:

• (не)эффективность индексов;
• плохая оценка числа строк;
• неэффективность кэша;
• угроза распухания индекса (bloat);
• CTE-скан использовался только 1 раз.

How to create a system information function in PostgreSQL

Давид Ян (David Zhang, старший системный архитектор в той же High Go) делится опытом написания собственных информационных функций. Ему мало тех, что можно найти на вот этой странице. Например, его не устраивает, что txid_current() возвращает ему тот же идентификатор транзакции, что и было до SAVEPOINT.

Ссылаясь на страничку Исходные данные системных каталогов, Давид показывает, как выбрать OID для новой функции, чтобы он не конфликтовал с существующими. Потом приводит код своей функции, определяющей xtid после SAVEPOINT. Называется она txid_current_snapshot и написана на C. И тестирует её. Теперь идентификатор транзакции показывается корректно.

How The PostgreSQL Optimizer Works

Ханс-Юрген Шёниг (Hans-Jrgen Schnig, Crunchy Data) написал не то, чтобы концептуальную, но большую по объёму статью, в которой есть примеры, демонстрирующие:

обработку констант: почему

WHERE x = 7 + 1

для оптимизатора не то же, что

WHERE x - 1 = 7

встраивание функций (function inlining): умение оптимизатора встраивать функции зависит от языка, в SQL он как дома, но не в PL-ях.

как обрабатываются функции, если они VOLATILE/STABLE/IMMUTABLE. Например:

WHERE x = clock_timestamp()

против

WHERE x = now()

что способен понять PostgreSQL, задумавшись о том, что чему равно:
понять, что если x = y AND y = 4, то x = 4, а значит можно использовать индекс по x это он может.

что такое view inlining и subselect flattening:
как представление превращается во вложенные SELECT-ы, а они в обычный, плоский SELECT.

Ну и, конечно, центральный вопрос как оптимизатор расправляется с JOIN. Тут Ханс-Юрген рассказывает об очерёдности джойнов, о явных и неявных; об OUTER JOIN; автоматическом исключении (pruning) ненужных; об EXIST и анти-джойнах.

Случайности:

Они не случайны

Кирилл Боровиков ака kilor выступил в роли волшебника: он угадывает случайные числа! Он придумал волшебную функцию и даже назвал её magic(). В качестве аргумента она берёт только что сгенерённое функцией random() число и предсказывает следующее:

SELECT r random, magic(r) random_next FROM random() r;       random       |    random_next--------------------+-------------------- 0.3921143477755571 | 0.6377947747296489tst=# SELECT r random, magic(r) random_next FROM random() r;       random       |    random_next--------------------+-------------------- 0.6377947747296489 | 0.5727554063674667

Чтобы исследовать содержание внутренностей волшебной функции, автор предлагает разобраться в линейном конгруэнтном алгоритме, который используется в random(), залезает в код функции setseed() в файле float.c и там находит источник вдохновения для создания своей волшебной функции.

Итого, случайные числа random() не слишком случайны, о криптографии и речи не может быть. Но кое-какие альтернативы имеются: более безопасны функции в расширении pgcrypto.

Восстановление

Speeding up recovery & VACUUM in Postgres 14

Статья на сайте Citus, но речь не о Citus, а о патче в основную ветку PostgreSQL. Написана статья (и патч) Дэвидом Роули (David Rowley), работавшим над этим уже внутри Microsoft. Он переписал внутреннюю функцию compactify_tuples, которая используется, когда PostgreSQL стартует после внештатного (нечистого) шатдауна (crash recovery), и когда идёт восстановление standby-сервера проигрыванием WAL по их прибытии с primary-сервера; VACUUM.

Эти случаи Дэвид и расписывает, поясняя схемами. Новая версия функции избавляет от ненужной внутренней сортировки кортежей в heap, поэтому и работает быстрее. На pgbench выигрыш в 2.4 раза на восстановлении и на 25% при вакууме.

Соревнования

Performance differences between Postgres and MySQL

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

• надо работать со сложно устроенными или объёмистыми данными;
• аналитические нагрузки;
• нужна транзакционная база общего назначения;
• требуется работа с геоданными.

А на этот раз решили померить, причём с уклоном в JSON, поскольку эта тема интересует в сообществе очень многих и очень сильно. Вот что было сделано:
создан проект, в котором использовались PostgreSQL и MySQL;
создали объект JSON для тестирования чтения и записи, размер объекта около14 МБ, около 200210 записей в базе данных.

И опять приятный вывод:
JSON-запросы быстрей в Postgres!

Кроме этого автор по касательной упоминает индексы по выражениям и прочие, особенности репликации, принципиальные отличия MVCC в InnoDB MySQL и в PostgreSQL.

PostGIS

Traveling Salesman Problem With PostGIS And pgRouting

У Флориана Надлера (Florian Nadler, Cybertec) проблемный коммивояжер странствует по окрестностям Гамбурга. Это продолжение статьи 'Catchment Areas' With PostgreSQL And PostGIS. Там собрали множества городов, ближайших к крупным аэропортам, разбросав их по диаграммам Вороного.

Теперь, надо решить, как лучше эти города обойти, для чего кроме PostGIS Флориан использует функции расширения pgRouting. Чтобы превратить множество точек в граф, он выбирает утилиту osm2po.

Дальше pgr_createverticestable функция из pgRouting превратит граф в таблицу. Эта таблица-граф накладывается как слой поверх слоёв OpenStreetMap. После этого Флориан, используя функцию pgr_dijkstraCostMatrix из pgRouting, решает эту знаменитую задачу оптимизации с помощью замысловатого запроса с CTE, учитывая стоимости/веса, присвоенные ещё osm2po.

Performance Improvements in GEOS

GEOS важнейшая для геовычислений библиотека (алгоритмы портированы на C из Java Topology Suite или JTS). Crunchy Data вкладывают в её развитие не меньше сил, чем в саму PostGIS.

Пол Рамси ( Paul Ramsey) рассказывает не просто о тестах производительности GEOS (довольно специфических), а взглядом историка GEOS иллюстрирует ими хронологию улучшений в этой библиотеке от релиза 3.6 к свежайшему 3.9. Вообще-то, о GEOS 3.9 Пол говорил и раньше в начале декабря в блоге Crunchy Data Waiting for PostGIS 3.1: GEOS 3.9 и в собственном. Там тоже есть роскошные иллюстрации, но нет графиков производительности.

А вот заметку Пола Рамси Dumping a ByteA with psql можно увидеть только в его блоге. Она короткая, но может оказаться полезной тем, кто:

• хранит двоичные файлы в столбцах базы, например изображения-ярлычки (thumbnails);
• хочет сформировать на выходе двоичный файл изображения, песни, protobuf или LIDAR-файл;
• использует двоичный формат для транзита двух разных типов.

Хранить в двоичном виде картинку можно, а вот посмотреть нельзя нужен файл. Вот скриптик, который берёт из базы ярлычок в типе bytea, через psql двоичное значение обертывается функцией encode и выгружается как обычное текстовое. Вывод psql перенаправляется в утилиту xxd, которая декодирует входной поток (ключ -r) обратно в двоичный вид и записывает в файл .png:

echo "SELECT encode(thumbnail, 'hex') FROM persons WHERE id = 12345" \  | psql --quiet --tuples-only -d dbname \  | xxd -r -p \  > thumbnail.png

Такой способ будет работать для любого поля bytea.

Активная жизнь в коммьюнити

How many engineers does it take to make subscripting work?

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

Патч добавляет subscripting в синтаксис функций JSONB то есть как у массивов, например:

SET jsonb_column['key'] = '"value"';

вместо

SET jsonb_column = jsonb_set(jsonb_column, '{"key"}', '"value"');

Началась история этого патча в 2015 году с беседы Дмитрия с Олегом Бартуновым и последовавшего простенького патча Долгова. Сообщество отнеслось к патчу сочувственно, но предложило переписать его в более универсальной манере, чтобы подобную функциональность можно было бы использовать и для других типов данных. Соответствующий патч Дмитрия был непрост, и ревюеры не торопились его разобрать и оценить. Ещё в истории этого патча фигурируют Том Лейн (Tom Lane), закоммитивший финальный патч Александр Коротков, Павел Стехуле (Pavel Stehule) и Никита Глухов.

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

В финале статьи 8 советов. Вот некоторые из них в моём вольном переводе, начиная с последнего Last but not least:

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

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

Разбейте патч на несколько частей это всегда облегчает работу ревьюеров.

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

Облака и контейнеры

Running Postgres In Docker Why And How?

Каарел Моппел (Kaarel Moppel, Cybertec) задаёт себе вопрос можно и нужно ли использовать PostgreSQL в Docker в качестве продакшн, будет ли он вообще там работать? и отвечает: да, работать будет, если сильно постараться, и если для фана или для тестирования.

В статье несколько разделов, но начнём с предпоследнего Капли дёгтя в бочку мёда.

Докер-имиджи да и вся концепция контейнеров оптимизированы под моментальное разворачивание в стиле стартапов . По умолчанию там даже данные не разведены как следует по томам (persistent units). Если этого не сделать, затея может закончится катастрофой.

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

У вас будет относительно лёгкая жизнь только в том случае, если вы используете такой всеобъемлющий фреймворк, как Kubernetes плюс выбираете оператор (скорее всего от Zalando или Crunchy).

Поведение

The PostgreSQL Community Code of Conduct Committee Annual Report for 2020

Этот документ сообщества переводили на русский Анастасия Лубенникова, Александр Лахин и Анастасия Распопина (все из Postgres Professional), также участвовали Виктор Егоров и Валерия Каплан. Ещё он переведён с английского на японский и иврит.

Число жалоб увеличилось в 2020: 18 против 12 в прошлом году. Мужчины жалуются чаще: 15/3. Обычно от страны по жалобе. По 2 только от РФ, Аргентины, UK и US.

Надеемся, что вы хорошо отдохнули и попраздновали. А мы предлагаем вам очередную сводку Postgres-новостей.

PostgreSQL 14 Beta 1

Релизная группа в составе Пит Гейган (Pete Geoghegan, Crunchy Data), Мишель Пакье (Michael Paquier, VMWare) и Эндрю Данстан (Andrew Dunstan, EDB) предлагают опубликовать бету 20-го мая, как это и происходило с предыдущими бетами.

Commitfest afterparty

PostgreSQL 14: Часть 5 или весенние заморозки (Коммитфест 2021-03)

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

Вот авторский тизер:

• Может ли один запрос параллельно выполняться на разных серверах?
• Как найти запрос из pg_stat_activity в pg_stat_statements?
• Можно ли добавлять и удалять секции секционированной таблицы не останавливая приложение?
• Как пустить разработчиков на прод чтобы они могли всё видеть, но ничего не могли изменить?
• Почему VACUUM после COPY FREEZE заново переписывает всю таблицу и что с этим делать?
• Можно ли сжимать TOAST чем-то кроме медленного zlib?
• Как понять сколько времени длится блокировка найденная в pg_locks?
• Для чего нужны CYCLE и SEARCH рекурсивному запросу?
• Текст функций на каких языках (кроме C) не интерпретируется при вызове?

Миграция

CHAR(1) to Boolean transformation while migrating to PostgreSQL

В Oracle нет типа boolean, а в PostgreSQL есть. Но почему бы не использовать этот тип, если в исходной оракловой базе есть столбец boolean, который хранится там в виде CHAR(1) с ограничением CHECK? Можно. Но хотелось бы ещё получить гарантию, что значения, отличные от резрешенных для Postgres не остановят работу приложения, а будут должным образом обработаны. Для этого можно создать CAST:

CREATE CAST (char as bool) WITH FUNCTION char_to_bool(char);

Далее автор Дилип Кумар (Dileep Kumar, MigOps) показывает изменение поведения при определении CAST как IMPLICIT, а потом прогоняет запрос (обычный SELECT) на тестах, чтобы увидеть разницу CHAR(1) vs Explicit Casting vs Implicit Casting vs Boolean. Побеждает, как и ожидалось, Boolean.

Choice of Table Column Types and Order When Migrating to PostgreSQL

В статье Стивена Фроста (Stephen Frost) с участием его коллеги по Crunchy Data Дэвида Юатта (David Youatt) тоже говорится о том, какой тип выбрать в PostgreSQL при миграции, но ещё и о том, в каком порядке располагать столбцы, чтобы данные выбранных типов хранились максимально эффективно. Сначала самые широкие поля с фиксированной шириной, затем менее широкие с фиксированной и только потом поля переменной ширины иначе появятся дыры в данных. Стивен рассказывает и про неприятные сюрпризы с выравниванием, которые можно получить, излишне рьяно экспериментируя с типами PostgreSQL. Ещё совет: выбирайте NUMERIC или DECIMAL только тогда, когда необходимо (считая деньги, например), а если нет, то обходитесь NTEGER, BIGINT, REAL, DOUBLE PRECISION это проще и эффективней.

Масштабирование

Lessons Learned From 5 Years of Scaling PostgreSQL

Джо Уилм (Joe Wilm) обобщает опыт использования PostgreSQL в компании OneSignal. Система доросла за 5 лет до 75 ТБ на 40 серверах. Понятно, что не все технические решения были приняты сразу на вырост. Как решают проблемы масштабирования, и как их можно было избежать об этом и рассказывает автор. Для удобства он разбил статью по разделам (сознательно не перевожу, слишком много английских слов пришлось бы писать кириллицей):
Bloat таблиц и индексов. Коротко о (хорошо известных) причинах распухания. pg_repack справлялся так себе (см. причины), написали собственный демон, координирующий его работу. Перешли к pgcompacttable там, где pg_repack обваливает производительность (перешли не везде, pgcompacttable работает надёжней, но медленней). Есть и об уловках по ситуации: в системе были таблицы, в которых большие поля (около 1 КБ) в личных данных, и поле last_seen_time int, которое часто обновлялось. Их разнесли по разным таблицам: одним JOIN больше, зато не копятся килобайты при обновлении строки.
Database upgrade. Мажорные и минорные. С мажорными справлялись при помощи логической репликации pglogical. При минорых просто перестартовывали postgres.
Wraparound. Серьёзная проблема для таких нагрузок. Остановились на оповещениях при приближении к 250 млн оставшихся XID. Напомним, конечно, что в Postgres Pro Enterprise 64-битные XID.
Replica Promotion. Для этого обходятся средствами haproxy. Упоминается только Patroni, но и то в контексте мы не используем, но может и стоило. Для каждой логической базы данных есть два бэкенда: один read-write, другой read-only. Переключение занимает пару секунд.
Partitioning и Sharding. Важнейшая штука для такой базы, конечно. Сначала порезали на 16 секций, потом на 256, а в ближайших планах 4096. Резали на куски выбирая в качестве критерия разбиения id пользователей системы. Сейчас думают над созданием data proxy слое, который будет знать, как разрезаны данные и где лежат, и действовать соответственно. Чтобы приложениям этого не требовалось знать для нормальной работы. Сетуют, что не сделали так с самого начала.

Самокритика

Чего энтерпрайзу в PostgreSQL не хватает

Вот чего ему не хватает в порядке важности (по Кириллу Боровикову, автору статьи)

• легковесного менеджера соединений (он же built-in connection pooler);
• 64-bit XID;
• микротаблиц (речь о том, что у каждой таблицы и индекса в PostgreSQL есть 3 форка файла, но почему бы не обойтись 1 файлом (heap) для мелких справочных табличек?);
• zheap;
• append-only storage (а в идеале, считает Кирилл хотелось иметь возможность назначать часть полей индексов или целых таблиц как no-MVCC чтобы иногда экономить на полях поддержки MVCC);
• отложенная индексация (чтобы сервер мог размазать необходимые операции во времени для балансировки нагрузки эта тема особенно важна для конкуренции с поисковыми системами, где основная задача найти вообще, а не найти прямо сразу сейчас);
• columnar storage (в идеале в ядре или в contrib);
• in-memory storage (очень быстрого нетранзакционного хранилища без сброса на диск);
• не пухнущих TEMPORARY TABLE, в том числе на репликах;
• multimaster из коробки;
• SQL-defined index (уметь описывать новые виды индексов прямо на SQL/PLpgSQL);
• мониторинга производительности запросов (здесь Кирилл предлагает глянуть, как это визуализируется на родном explain.tensor.ru);
• снапшотов статистики таблиц (как в pg_profile [а тем более в pgpro_pwr примечание редакции]).

К ЭТОМУ ДОБАВИЛИСЬ ХОТЕЛКИ ИЗ КОММЕНТАРИЕВ:

• IS NOT DISTINCT FROM при индексации;
• failover из коробки (аналогично Always on у MS SQL) без Patroni и сопутствующих;
• Asynchronous IO и Direct IO;
• бесшовного обновления мажорной версии;
• flashback queries;
• edition-based redefinition;
• нормальной компрессии.

Некоторые из этих хотелок на пути к дальнейшим версиям, некоторые уже есть в Postgres Pro Enterprise (о чём не умалчивает и автор).


Видео-вторник s02e15: Десять проблем PostgreSQL. Мониторинг запросов, pg_profile

(это продолжение вторника ) с Андреем Зубковым)

Статья Рика Брэнсона: (Rick Branson) 10 things I Hate In Postgres внезапно попала в топ обсуждаемых. Вот её не миновали и устроители ruPostgres.Вторников Николай Самохвалов и Илья Космодемьянский.

О ней мы писали в Postgreso 20. На ruPostgres.вторнике s02e15 6-го апреля самые жаркие вопросы возникали, как всегда, вокруг MVCC и VACUUM, переполнения 32-битных счётчиков XID.

На 50-й минуте обсуждения 10 ненавистных вещей Андрей Зубков продолжил рассказал о pg_profile (до pgpro_pwr речь опять не дошла, говорили даже о том, чтобы наверстать в 3-й серии) и о своём патче pg_stat_statements: Track statement entry timestamp (ровно 1:00 записи).

Вторник 20-го апреля назывался Как поменять тип колонки в таблице PostgreSQL с 1 млрд строк без даунтайма?. Два разных варианта решения на уровне колонки и на уровне таблицы.

А совсем недавний 4-го мая о разном, например, о WAL-G vs. pgBackRest, об амазоновских инстансах на ARM, о которых чуть ниже. Список тем лежит в файле.

Облака и контейнеры

Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances

Александр Коротков в своём блоге пишет об опыте работы с новейшими облаками инстансы Graviton2 работают на амазоновских ARM-процессорах. Но следующие за модой расплачиваются некоторыми сложностями у ARM есть специфика (по мнению Александра работа с ними скорее напоминает работу с IBM Power).

Команды LSE (Large System Extensions), доступные с версии 8.1, действительно ускоряют работу. Вот здесь это разъясняют с некоторыми подробностями, испытывая MySQL на включенных и отключенных LSE. Александр же получил колоссальный выигрыш на pgbench, скомпилировав PostgreSQL 14 с поддержкой LSE. Но это касается только амазоновских ARM AWR Graviton2. Apple M1 не удалось оптимизировать (возможно, в этих процессорах есть какая-то внутренняя оптимизация), а на китайских Kunpeng 920 результаты даже ухудшились.

Что делать

Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL

Кит Фиске (Keith Fiske, Crunchy Data) регулярно пишет в своём собственном блоге Keith's Ramblings о вакууме, распухших индексах и других важнейших для вдумчивого постгресиста вещах.

В этой статье есть конкретные SQL-запросы, использующие autovacuum_freeze_max_age для получения внятной информации о происходящем с конкретными таблицами, так как vacuumdb --all --freeze --jobs=2 --echo --analyze всего кластера баз данных во многих случаях слишком радикальная мера. Если недовакуумированных таблиц очень много, то Кит советует вакуумировать в батчах не больше сотни в каждом. Сам он предпочитает держать max XID < to 50% autovacuum_freeze_max_age, лучше 30-40%.

Он написал статью и о настройке автовакуума: Per-Table Autovacuum Tuning. Но даже аккуратно настроив автовакуум, стоит с не меньшей аккуратностью мониторить ситуацию. Риск не велик, но ставка высока, как говорили наши деды.

Не удержусь от перечисления собственных проектов Кита (или с его существенным участием):
pg_partman расширение с автоматической поддержкой секционирования по времени и serial id;
pg_extractor продвинутый фильтр дампа;
pg_bloat_check скрипт для мониторинга таблиц и индексов;
mimeo расширение PostgreSQL для потабличной логической репликации;
pg_jobmon расширение для логирования и мониторинга автономных функций.

Postgres is Out of Disk and How to Recover: The Dos and Don'ts

Статья Элизабет Кристинсен (Elizabeth Christensen) с участием Дэвида Кристинсена (David Christensen), Джонатана Каца (Jonathan Katz) и Стивена Фроста (Stephen Frost) все из Crunchy Data. Почему забился диск, что НЕ делать, и что делать.
Возможные причины:

• отказала archive_command и WAL начал заполнять диск;
• остались слоты репликации у стендбая, а реплика стала недоступна: опять же WAL заполняет диск;
• изменения в базе настолько большие, что генерящийся WAL съедает всё доступное дисковое пространство;
• просто-напросто данных было слишком много, а средства мониторинга и предупреждения не сработали.

Что НЕЛЬЗЯ делать:
удалять WAL-файлы нельзя категорически;

• не дайте переписать существующие данные, восстанавливаясь из бэкапа;
• Никакого resize.

Что надо делать:

• сделайте сразу бэкап на уровне файловой системы;
• создайте новый инстанс (или хотя бы новый том) с достаточным местом, убедитесь, что Postgres остановлен и сделайте бэкап директории данных PostgreSQL (обязательно директории pg_wal и недефолтные табличные пространства), чтобы вам было куда вернуться, если понадобится;
• когда база данных заработала, просмотрите логи, разберитесь, из-за чего возникли проблемы и почините поломки, если это возможно.

В статье рассказывается, как архивируется WAL, об попорченных архивах, кое-что о pgBackRest, а ещё предлагается почитать How to Recover When PostgreSQL is Missing a WAL File.

Кстати, о WAL. Если нужно порекомендовать хорошую статью англоязычным коллегам, то в блоге Postgre Pofessional опубликован перевод 3-й части серии Егора Рогова о WAL: WAL in PostgreSQL: 3. Checkpoint. Оригинал её здесь, en-начало-серии здесь, а ru-начало здесь.

Из блога БРЮСА МОМДЖАНА

(то есть отсюда)

Jsonb Multi-Column Type Casting

Брюс делится радостью, что есть jsonb_to_record() и можно без всяких двойных двоеточий сразу сказать:

SELECT a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER);

(А ведь добавим от себя есть ещё и jsonb_to_recordset(jsonb)).

Брюс обращает внимание на устройство таких запросов. Если сказать

SELECT x.a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER)WHERE b <= 4;

то это будет работать, ведь b уже integer потому, что запрос уже создал табличку x с областью видимости только внутри запроса, где типы уже преобразованы. Немногословный (как обычно в своём блоге) Брюс предлагает ознакомиться с деталями в тредах json_to_record Example и Abnormal JSON query performance.

Oracle vs. PostgreSQL

Брюс решил оценить функциональную полноту обеих СУБД в %, в ответ на чьё-то сравнение Postgres и Oracle это как резиновая уточка против танкера водоизмещением 300 тыс. тонн. Он считает:
Более реалистичной была бы оценка в 80-90%, в зависимости от того, какая функциональность для вас важней. Но можно бы поговорить и том, что в Postgres есть, а в Oracle нет. С точки зрения админа получится, может быть, и меньше 80%, а вот с точки зрения разработчика в Oracle нет многого, и оценка перевалит за 100%.

Challenging Assumptions

Следующие, некогда справедливые допущения теперь сомнительны:

• платный софт всегда лучше бесплатного;
• открытый код не столь безопасен, так как слабые места видны;
• серьёзные люди софт с открытым кодом не разрабатывают;
• Oracle лучшая СУБД;
• со знанием Oracle без работы я не останусь;

Кто закрывает дыры и латает щели (в оригинале Database Software Bundles)

Проект Postgres дал миру великолепную, полнофункциональную СУБД. Но когда пользователь думает о бэкапе, мониторинге, высокой доступности, ему приходится смотреть на сторону, так как возможности Postgres могут не совпадать с его потребностями. Иногда бреши закрывают проекты с открытым кодом, но в других случаях решают проблемы коммерческие Postgres-компании: Cybertec, edb, HighGo, Ongres, Postgres Pro, sra oss и другие, которые поставляют сервисы последней мили для корпоративных решений.

Также можно заглянуть в

Shared Memory Sizing
или, скажем, в
Replica Scaling by the Numbers

ИИ

Regression Analysis in PostgreSQL with Tensorflow

Дейв Пейдж (Dave Page, вице-президент и главный архитектор EDB) продолжает серию, посвященную ИИ и статистическим методам анализа данных. Из последнего: вышли две статьи посвященные регрессионному анализу, который ускоряют с помощью Tensorflow. В приведенных примерах можно увидеть много ласкающих слух питониста слов: pandas, numpy, matplotlib и seaborn. Подчеркнём, что используется расширение PostgreSQL plpython3u, а не просто внешние по отношению к базе библиотеки.

Во второй части дело доходит до пред-обработки данных. Используется популярный у педагогов машинного обучения набор данных Boston Housing Dataset по ним тренируются угадывать цену дома в Бостоне в зависимости от некоторых факторов. Из набора выкидывают значения, сильно отличающиеся от общей массы, чтобы не запутать нейронную сеть при обучении. Ещё смотрят распределения и строят корреляции. Третья статья ещё не вышла. Обещано, что в ней уже воспользуются достижениями 2-й части, чтобы обучать нейронную сеть регрессионному анализу.

Релизы

Kubegres

Обычно в разговоре о PostgreSQL в Kubernetes на третьей фразе появляются операторы от Crunchy Data и Zalando. Kubegres, возможно, вклинится в разговор. Разработчик Алекс Арика (Alex Arica, Reactive Tech Limited). Создавался Kubegres на базе фреймворка Kubebuilder version 3 (SDK для разработки Kubernetes APIs с использованием CRD. Можно забрать отсюда.

KuiBaDB

KuiBaDB это Postgres для OLAP, переписанный с Rust и многопоточностью. У этой СУБД есть только базовая функциональность. Она, например, поддерживает транзакции, но не вложенные транзакции. KuiBaDB создан для разработчиков, чтобы они могли быстренько проверить на ней свои идеи. В ней есть векторный движок и колоночное хранение, она опирается на каталоги (catalog-driven).

pgBackRest 2.33

Появилась поддержка нескольких репозиториев данные и WAL можно копировать сразу в несколько хранилищ.
pgBackRest поддерживает теперь GCS Google Cloud Storage.
Отныне можно задать путь вручную с ./configure --with-configdir. Стало удобней работать с не-Linux ОС, например с FreeBSD.
Появилось логирование в процессе бэкапа.

pg_probackup 2.4.15

В новой версии pg_probackup при бэкапе в инкрементальном режиме автоматически обнаруживается переключение таймлайнов, за счёт использования команды TIMELINE_HISTORY протокола репликации (предложил Алексей Игнатов).

При операциях merge и retention merge теперь тоже можно использовать флаги --no-validate и --no-sync.

pgmetrics 1.11.0

pgmetrics утилита с открытым кодом для сбора статистики работающего PostgreSQL, распространяемая в виде единого бинарного файла без внешних зависимостей. Разработчик RapidLoop, у которой есть ещё и pgDash, для которой pgmetrics собирает статистику.

Новое в версии:

• собирает и парсит логи из AWS RDS и Aurora, используя CloudWatch;
• поддержка пулера Odyssey v1.1;
• улучшена поддержка Postgres 13;
• улучшена поддержка метрик AWS RDS;
• появились бинарники для ARMv8

Скачать можно отсюда.

HypoPG 1.2

HypoPG одно из произведений Жульена Руо (Julien Rouhaud). Это расширение для работы с гипотетическими индексами. Новое в версии: работая на стендбае, hypopg использует фальшивый (fake) генератор oid, который одалживает их внутри интервала FirstBootstrapObjectId / FirstNormalObjectId, а не генерит реальные. Если потребуется, можно работать по-старому, используя опцию hypopg.use_real_oids. Есть и ещё изменения, hypopg_list_indexes(), подробности в документации.

pgstats.dev

Это динамическая диаграмма Postgres Observability упрощенное представление устройства PostgreSQL и доступные системные представления и функции для получения статистики о работе подсистем Postgres. Этому необычному произведению Алексея Лесовского (Data Egret) всего 5 месяцев, но её знают многие DBA, спорят и интересуются: что новенького? Новое, например, вот:

• стрелки, которые раньше показывали связи между блоками и метками статистики, теперь исчезли, а соответствующие цвета введены, чтобы показать их отношения;
• на страницах описания статистик (см. pg_stat_progress_create_index в качестве примера) улучшена внутренняя навигация за счет добавления ссылок на связанные элементы;
• добавлены ресурсы внешние ссылки с дополнительной информацией;
• теперь есть управление версиями, чтобы вы могли видеть, как Postgres эволюционировал от одной версии к другой.

AGE 0.4.0

Расширение, добавляющее графовую функциональность. Новшества в 0.4.0 здесь.

pg_log_statements 0.0.2

pg_log_statements расширение PostgreSQL, которое позволяет логировать SQL-запросы так, что переменная log_statement может быть установлена для отдельного серверного процесса (по id или фильтру), а не на уровне базы или инстанса.

Можно зайти на PGXN или на гитхабе создателя Пьера Форстмана, специалиста по Oracle.

Конференции

PostgresLondon 2021

Состоится уже 12-го мая, виртуальная. Расписание.

Highload++

Состоится офлайн 17 -18 мая в Крокус-Экспо, Москва. Расписание.

Postgres Vision 2020

Postgres Vision виртуальная конференция EDB, но участие свободное. Состоится 22-23 июня. Регистрация.

Следующий номер Postgresso 32 выйдет в первых числах июня.