PostgreSQL *

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

#1. Секционирование

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

Речь пойдёт всего лишь о PostgreSQL 13. 8 апреля состоялась «заморозка» — PostgreSQL feature freeze, теперь в эту версию войдут только те фичи, которые приняты до этой даты.

Революционной эту версию, пожалуй, трудно назвать. Кардинальных, концептуальных изменений в ней нет. К тому же не успели войти в неё такие важные патчи, как Table и Functions для стандарта JSON/SQL, которых хотелось видеть еще в PG12 рядом с патчем JSONPath; не появились готовые встраиваемые хранилища — лишь дорабатывается интерфейс. Но список доработок всё же впечатляет. Мы подготовили довольно полную сводку вошедших в Чёртову Дюжину патчей.

---

Изменения в командах SQL

CREATE DATABASE… LOCALE

Утилиты initdb, createdb и команда CREATE COLLATION имеют параметр LOCALE, позволяющий сразу указать значения для LC_CTYPE и LC_COLLATE. Теперь такая же возможность появилась в команде CREATE DATABASE:

CREATE DATABASE db_koi8r TEMPLATE template0 
    ENCODING 'KOI8R' LOCALE 'ru_RU.KOI8R';

Привет! Это наш первый релиз из дома. DataGrip и другие наши IDE с поддержкой баз данных теперь умеют больше.

Предлагаю ознакомиться с расшифровкой доклада 2018 года Андрея Сальникова "Практика обновления версий PostgreSQL"

В большинстве своем, системные администраторы и ДБА бояться как огня делать мажорные обновления версий баз данных (RDBMS), особенно если эта база данных в эксплуатации и имеет достаточно высокую нагрузку. Главной причиной тому некоторый даунтайм базы данных, который всегда подразумевается при планировании таких работ.

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

В Data Egret мы накопили огромный опыт проведения мажорных апгрейдов PostgreSQL в проектах, где нет права на ошибку. Я поделюсь своим опытом и расскажу о следующих шагах процесса: как правильно подготовиться к upgrade-у PostgreSQL? что необходимо сделать на этапе подготовки? как запланировать последовательность действий на сам upgrade? как провести процедуру upgrade-а успешно, без возврата на предыдущую версию бд? как минимизировать или вообще избежать простоя всей системы во время upgrade-а? какие действия необходимо выполнить после успешного upgrade-а PostgreSQL? Я также расскажу про две наиболее популярные процедуры апгрейда PostgreSQL — pg_upgrade и pg_dump/pg_restore, плюсы и минусы каждого из методов и расскажу про все типичные проблемы на всех этапах этой процедуры, и как их избежать.

Доклад будет интересен как новичкам так и тем ДБА которые уже давно работают с PostgreSQL, но хотят побольше узнать о том как правильно планировать и проводить upgrade максимально безболезненно.

Кратко об этом модуле

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

Модуль объединяет в себе три важные функции: валидацию входных данных, постройку запросов в базу данных и вывод данных в JSON.

SQL — это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе, и вот это — совсем не одно и то же:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

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

Поэтому если управлять приоритетом все-таки хочется, надо структурно сделать эти условия неравными с помощью условных выражений и операторов.


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

Зачем переносить данные

Тема переноса (миграции) данных из Oracle в Postgres набирает обороты. В РФ количество запросов по переходу на Postgres на волне импортозамещения, сейчас больше, чем когда либо. Oracle — очень качественный, мощный и отлично документированный инструмент. Но вместе с тем очень дорогой в лицензировании и поддержке. Зачастую охватить все его возможности не получается в силу специфики использования. Ключевыми параметрами эксплуатации в нашем случае являются производительность системы, процент использования (утилизации) железа, простая тех. поддержка, а также дешевое и быстрое масштабирование — легкое развертывание новых баз.

Я решил изучить процесс миграции данных, изучая то, как реализовать пожелания конкретного заказчика, а также из практического любопытства, теперь я пробую перенести небольшую схему около 15 Гбайт с Oracle 11.2.0.4 на Linux Redhat 6.8 в Postgres 9.4 на Windows. Немного погрузившись в тему, я решил написать эту статью и рассмотреть процесс подробно. В отрыве от нашей специфики пост будет полезен интеграторам и DBA при планировании переноса данных/CUT.

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Андрея Бородина "Резервные копии с WAL-G. Что там в 2019?"

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Алексея Лесовского — «Поиск и устранение проблем в Postgres с помощью pgCenter»

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

• проверить, все ли в порядке с Postgres'ом;
• быстро найти плохих клиентов и устранить их;
• выявлять тяжелые запросы;
• и другие полезные приемы с pgCenter.

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

За прошедшее время вы уже воспользовались им более 6000 раз, но одна из удобных функций могла остаться незамеченной — это структурные подсказки, которые выглядят примерно так:



Прислушивайтесь к ним, и ваши запросы «станут гладкими и шелковистыми». :)

А если серьезно, то многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

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



Давайте чуть подробнее рассмотрим эти кейсы — как они определяются и к каким рекомендациям приводят.

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

Одной из команд, с которой надо быть осторожным на таблицах с большим количеством записей, является добавление not null constraint на столбец. При добавлении данного constraint PostgreSQL приобретает access exclusive lock на таблицу, в результате чего другие сессии не могут временно даже читать таблицу; затем БД проверяет, что в столбце действительно ни одного null нет, и только после этого вносятся изменения. Под катом я рассмотрю различные варианты, как можно добавить not null constraint, лоча таблицу на минимально возможное время или даже не лоча ее совсем.

TL;DR:

1. В PostgreSQL 12+ можно добавить check constraint на таблицу, а затем "преобразовать" его в not null constraint для конкретного столбца.
2. Чтобы полностью избежать блокировки таблицы, можно напрямую внести изменения в системную таблицу pg_attribute (этот пункт подробно разбирается в статье).

При сложной обработке больших наборов данных (разные ETL-процессы: импорты, конвертации и синхронизации с внешним источником) часто возникает необходимость временно «запомнить», и сразу быстро обработать что-то объемное.

Типовая задача подобного рода звучит обычно примерно так: «Вот тут бухгалтерия выгрузила из клиент-банка последние поступившие оплаты, надо их быстренько вкачать на сайт и привязать к счетам»

Но когда объем этого «чего-то» начинает измеряться сотнями мегабайт, а сервис при этом должен продолжать работать с базой в режиме 24x7, возникает множество side-эффектов, которые будут портить вам жизнь.

Чтобы справиться с ними в PostgreSQL (да и не только в нем), можно использовать некоторые возможности для оптимизаций, которые позволят обработать все быстрее и с меньшим расходом ресурсов.

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

Релизы

Postgres Pro Enterprise 11.17.1 и Postgres Pro Standard 11.17.1

В Postgres Pro Enterprise 11.17.1 исправлены недостатки, на которые указали пользователи. Серьезные доработки сделаны в расширении multimaster:
— теперь рекомендуется использовать его в конфигурации с тремя узлами, один из которых голосующий. Подробнее здесь;
— устранена проблема раздувания WAL путём очистки точек синхронизации удалённого узла и исправления расчёта минимального требующегося LSN. Ранее раздувание WAL иногда случалось при удалении узла из кластера;
— устранена проблема с возвращением узла в кластер после длительного отключения этого узла;
исправлена ошибка в точке синхронизации при инициализации модуля multimaster, возникавшая в случае сбоя до первой синхронизации.
Кроме этого усовершенствован механизм встроенного пула соединений. По сравнению с предыдущей версией, в нём появились следующие новшества:
— параметр dedicated_users, позволяющий задать список пользователей, для которых в режиме пула соединений будут использоваться выделенные обслуживающие процессы;
— отдельные обслуживающие процессы теперь могут принимать подключения разных пользователей, так что все подключения к одной базе данных будут относиться к одному общему пулу.
Есть доработки, общие для Postgres Pro Enterprise 11.17.1 и Postgres Pro Standard 11.17.1. Например, утилита pg_probackup обновлена до версии 2.2.7, а mamonsu — до версии 2.4.4.
Об этих и других новшествах релиза есть в главке Замечания к выпуску из документации по PPE и PPS.

Postgres Pro Standard 12.2.1

Отличия этой версии от PostgreSQL 12 и от Postgres Pro Standard 11.17.1 можно проследить по соответствующим Замечаниям к выпуску.

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

Кратко сюжет из отличной статьи:

Когда строка изменяется командой UPDATE, фактически выполняются две операции: DELETE и INSERT. В текущей версии строки устанавливается xmax, равный номеру транзакции, выполнившей UPDATE. Затем создается новая версия той же строки; значение xmin у нее совпадает с значением xmax предыдущей версии.

Через какое-то время после завершения этой транзакции старая или новая версии, в зависимости от COMMIT/ROOLBACK, будут признаны «мертвыми» (dead tuples) при проходе VACUUM по таблице и зачищены.



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

В PostgreSQL нет High Availability из коробки. Чтобы добиться HA, нужно что-то поставить, настроить — приложить усилия. Есть несколько инструментов, которые помогут повысить доступность PostgreSQL, и один из них — Patroni.

На первый взгляд, поставив Patroni в тестовой среде, можно увидеть, какой это прекрасный инструмент и как он легко обрабатывает наши попытки развалить кластер. Но на практике в production-среде не всегда всё происходит так красиво и элегантно. Data Egret начали использовать Patroni еще в конце 2018 года и накопили определенный опыт: как его диагностировать, настраивать, а когда вовсе не полагаться на автофейловер.

На HighLoad++ Алексей Лесовский обстоятельно, на примерах и с разбором логов рассказал о типовых проблемах, возникающих при работе с Patroni, и best practice для их преодоления.


В статье не будет: инструкций по установке Patroni и примеров конфигураций; проблем за пределами Patroni и PostgreSQL; историй, основанных на чужом опыте, а только те проблемы, с которыми в Data Egret разобрались сами.

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

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

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

0: чего же хотел пользователь

[КДПВ отсюда]

Что вообще обычно подразумевает пользователь, когда говорит про «быстрый» поиск по названию? Почти никогда это не оказывается «честный» поиск по подстроке типа ... LIKE '%роза%' — ведь тогда в результат попадают не только 'Розалия' и 'Магазин Роза', но и 'Гроза' и даже 'Дом Деда Мороза'.

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

Привет.

Меня зовут Ваня, и я Java-разработчик. Так получилось, что я много работаю с PostgreSQL – занимаюсь настройкой БД, оптимизацией структуры, производительностью и немного играю в DBA по выходным.

За последнее время я привёл в порядок несколько баз данных в наших микросервисах и написал java-библиотеку pg-index-health, которая облегчает эту работу, экономит моё время и помогает избежать некоторых типовых ошибок, допускаемых разработчиками. Именно об этой библиотеке сегодня и пойдёт речь.

Disclaimer

Основная версия PostgreSQL, с которой я работаю, это 10-ка. Все используемые мною SQL-запросы также проверены на 11-й версии. Минимальная поддерживаемая версия — это 9.6.

Предыстория

Началось всё почти год назад со странной для меня ситуации: конкурентное создание индекса на ровном месте завершилось с ошибкой. Сам индекс, как водится, в невалидном состоянии остался в базе. Анализ логов показал нехватку temp_file_limit. И понеслось… Копнув поглубже, я обнаружил целый ворох проблем в конфигурации БД и, засучив рукава, с блеском в глазах принялся их чинить.

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

Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.

Давайте просто отключим триггеры!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Собственно, тут и все — все уже висит.

Потому что ALTER TABLE накладывает AccessExclusive-блокировку, под которой никто параллельно выполняющийся, даже простой SELECT, ничего из таблицы прочитать не сможет. То есть пока эта транзакция не закончится, все желающие даже «просто почитать» будут ждать. А мы помним, что UPDATE у нас до-о-олгий…

Часто жалуются, что count (*) в PostgreSQL очень медленный.

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

Почему count (*) такой медленный?

Большинство людей без проблем понимают, что следующий запрос будет выполняться медленно:

SELECT count(*)
FROM /* сложный запрос */;

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

Но многие люди потрясены, когда узнают, что следующий запрос медленный:

SELECT count(*) FROM large_table;

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

Поэтому count (*) обычно выполняет последовательное сканирование таблицы, что может быть довольно дорого.

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



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

Мы погрузились в недра профайлера и обнаружили некоторые особенности работы с Buffer в Node.js, знание которых может сильно сэкономить ваше время и серверные ресурсы.