SQL-Ex blog

Автор: Amit Langote, Postgres: faster foreign key checks

В одной из предыдущих статей я описывал, как работает обеспечение целостности внешних ключей в Postgres. Краткая версия такова: каждая команда INSERT или UPDATE в таблицу, ссылающуюся на другую, запускает триггер AFTER, который проверяет, существуют ли значения столбца внешнего ключа (FK) в ссылочной (PK) таблице. Эта проверка проходит через SPI (Server Programming Interface): строится запрос, он планируется, выполняется, а затем всё уничтожается — для каждой отдельной строки.

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

Для Postgres 19 я применил два патча (соавтором обоих является Джунванг Жао), которые полностью обходят SPI для стандартного случая и выполняют пакетную (batch) проверку индекса. Вместе они ускоряют массовые вставки с внешними ключами примерно в 2.9 раза в используемом мною тесте (int первичный ключ, int внешний ключ, 1 миллион строк, таблица первичного ключа и её индекс в памяти).Что деаёт быстрый путь (fast path) Continue reading "Postgres: более быстрые проверки внешних ключей"

Автор: Richard Yen, The Postgres Performance Triangle

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

• Распределение памяти (Memory Allocation)


• Дисковый ввод-вывод (Disk I/O)


• Конкурентность (Concurrency)

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

Оговорка: Я понимаю, что с академической точки зрения понятие «пропускной способности» не полностью отражает суть баланса между этими концепциями, но, пожалуйста, отнеситесь к этому снисходительно!

Давайте поговорим о том, как каждый из этих трёх аспектов взаимодействует со всей системой и как выглядят сопутствующие компромиссы.

Continue reading "Треугольник производительности Postgres"

Автор: Craig Ringer, Choosing a PostgreSQL text search method

(Эта статья написана применительно к PostgreSQL 9.3. Если вы используете более новую версию, пожалуйста, проверьте, остались ли описанные ограничения в силе.)

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

Continue reading "Выбор метода текстового поиска в PostgreSQL"

Автор: , Radim Marek: PostgreSQL MVCC, Byte by byte

Вы выполняете SELECT * FROM orders в одном сеансе psql и видите 50 миллионов строк. Ваш коллега в другом сеансе выполняет тот же запрос в тот же момент и видит 49 999 999. Никто из вас не ошибается, и никто не видит устаревших данных. Вы оба читаете одни и те же страницы кучи размером 8 КБ, одни и те же байты на диске.

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

Continue reading "PostgreSQL MVCC, байт за байтом"

Автор: Richard Yen, Understanding PostgreSQL Wait Events

Одним из самых полезных инструментов отладки в современном PostgreSQL является система событий ожиданий (wait events). Когда запрос замедляется или база данных становится ограниченной по процессору, естественно возникает вопрос: «Чего на самом деле ожидают сеансы?» Postgres предоставляет эту информацию через представление pg_stat_activity с помощью двух столбцов:

• wait_event_type — тип события ожидания


• wait_event — само событие ожидания

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

LWLock

Если вы когда-либо видели панели мониторинга, полные ожиданий LWLock, вы не одиноки в своих сомнениях о том, что они означают и являются ли они проблемой.

Continue reading "Погружение в события ожиданий PostgreSQL"

Автор: Jeremy Schneider, Zero autovacuum_vacuum_cost_delay, Write Storms, and You

Несколько дней назад Шон Томас (Shaun Thomas) опубликовал статью в блоге pgEdge под названием «Контрольные точки, лавинная запись и вы». К сожалению, на многих корпоративных блогах больше нет возможности комментировать. Я оставил несколько комментариев в LinkedIn, но в целом позвольте мне сказать, что эта статья — отличное чтение, и я всегда рад, когда кто-то погружается в важную и обойдённую вниманием тему, даёт хорошее техническое описание и включает реальные результаты тестов для иллюстрации деталей.

У меня сегодня нет воспроизводимых реальных результатов тестов. Но у меня есть хорошая история и немного реальных данных.

Continue reading "Нулевая задержка autovacuum_vacuum_cost_delay - вас сметёт лавина записи"

Автор: Shaun Thomas,Checkpoints, Write Storms, and You

Каждая база данных должна примириться с двумя неприятными истинами: память быстра, но энергозависима, а диск медленен, но долговечен. Postgres справляется с этим противоречием с помощью журнала предзаписи (Write-Ahead Log, WAL), который регистрирует каждое изменение до того, как оно произойдёт. Но WAL не может расти бесконечно. В какой-то момент Postgres должен сбросить все накопившиеся грязные страницы на диск и объявить чистую начальную точку. Этот процесс называется контрольной точкой (checkpoint), и когда он идёт не по плану, пропускная способность может упасть до нуля.

Continue reading "Контрольные точки, лавинная запись и вы"

Автор: Lætitia AVROT, pg_column_size(): What you see is not what you get

Благодаря моему коллеге Озаиру (Ozair), который прислал мне запрос в JIRA: «Мне нужно удалить этот огромный столбец, каковы будут последствия?» Мой первый вопрос был: насколько он огромен? И тут кроличья нора открылась.

Это выглядит просто. Это просто. Просто используйте административную функцию pg_column_size(). Пока у вас нет атрибутов, обработанных TOAST. Тогда становится интересно.

Continue reading "pg_column_size(): То, что вы видите, не всегда то, что получаете"

Автор: Shaun Thomas, What is a Collation, and Why is My Data Corrupt?

Библиотека GNU C (glibc) версии 2.28 появилась на свет 1 августа 2018 года, и с тех пор Postgres уже не был прежним. Среди множества её изменений было масштабное обновление данных локалей для сопоставления (collation), приведшее их в соответствие с изданием 4 стандарта ISO 14651 (выпуск 2016 года) и Unicode 9.0.0. Это была не мелкая правка. Это была кульминация примерно 18 лет накопленных изменений в локалях, объединённых в одном выпуске.

Никто не устраивал вечеринку.

За этим последовал один из самых значительных и коварных инцидентов с целостностью данных в истории Postgres. Индексы молча становились повреждёнными, результаты запросов менялись без предупреждения, уникальным ограничениям больше нельзя было доверять. Самая страшная часть? Нужно было знать, что искать. Postgres не жаловался. Операционная система не жаловалась. Всё выглядело нормально, ровно до тех пор, пока это не переставало быть так.

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

Continue reading "Что такое collation (правило сортировки) и почему мои данные повреждены?"

Авторы: Elizabeth Garrett Christensen и Erik Jones, Do You Need to Tune Postgres Vacuum?

Если вы работаете с Postgres какое-то время, вы, вероятно, слышали, как кто-то упоминал «очистку» (vacuuming) базы данных или использовал термин «раздувание» (bloat). Оба эти понятия звучат как рутинная и надоедливая работа, но они — просто часть жизни здоровой базы данных. В современных версиях Postgres autovacuum обычно обрабатывает эти проблемы за кулисами. Но по мере роста вашей базы данных вы можете начать задаваться вопросом: достаточно ли настроек по умолчанию? Нужно ли мне запускать очистку Postgres вручную? Или почему моя база данных внезапно занимает гораздо больше места на диске, чем должна?

Давайте углубимся в то, зачем нужна очистка, как работает autovacuum и когда вам действительно нужно вмешаться и настроить его.

Continue reading "Нужно ли настраивать Vacuum в Postgres?"

Автор: Itzik Gan Baruch, How to learn CI/CD fast

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

Continue reading "Как быстро освоить CI/CD"

Автор: ByteGoblin/, Setting Up PostgreSQL High Availability with Patroni and Ansible

В современном мире, управляемом данными, обеспечение высокой доступности вашей базы данных имеет решающее значение для непрерывности бизнеса. PostgreSQL — мощная открытая реляционная база данных, которую можно настроить для обеспечения высокой доступности (High Availability, HA), чтобы минимизировать простои и сохранить доступ к данным. Одно из популярных решений для достижения высокой доступности PostgreSQL — использование Patroni (шаблона для управления кластерами PostgreSQL) в сочетании с Ansible (инструментом автоматизации, который упрощает развёртывание приложений, управление конфигурацией и оркестрацию).

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

Continue reading "Обеспечение высокой доступности PostgreSQL с помощью Patroni и Ansible"

Автор: Joe Celko, Combinations, permutations, and derangements

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

Факториалы

Функция факториала обычно записывается как (n)!, и она определяется как произведение первых (n) натуральных чисел. Таким образом, 5! равно (5 · 4 · 3 · 2 · 1) = 120. Как обычно, ноль — это особый случай: 0! = 1, что можно доказать с помощью несколько иного определения факториала. Вместо определения через произведение определим его рекурсивно следующим образом: n! = CASE WHEN n = 0 THEN 1 ELSE n · (n-1)! END.

Показывая процесс шаг за шагом, рекурсия разворачивается так:

5! = 5 · 4!

4! = 4 · 3!

3! = 3 · 2!

2! = 2 · 1!

1! = 1 · 0!

Посмотрите на последний шаг рекурсии. Теперь разделите обе части на единицу, чтобы получить (1! / 1) = 0! или 1 = 0! Обратите внимание, что всё, что было сделано до сих пор, является процедурным, а не ориентированным на множества. Специалисты по реляционным СУБД предпочитают уходить от процедурного кода. Для остальной части этой статьи вы можете думать о n! как о количестве способов упорядочить (n) элементов множества в последовательность. Очевидно, если у вас есть один элемент, то у вас есть только один способ упорядочивания. Но точно так же, если у вас ноль элементов, вы также на этом закончили. Как существует только одно пустое множество, так существует и только одна пустая последовательность.

Да, можно определить факториалы для отрицательных чисел, мнимых чисел, гамма-функцию для действительных чисел и другие вещи. Если вы не студент-математик, вам совершенно не понадобятся эти изощрённые трюки. Поскольку SQL — это язык баз данных, а не вычислительный язык, возможно, вы захотите использовать поиск по таблице, а не это рекурсивное определение. Вы можете найти таблицу чисел от одного до ста для заполнения таблицы. Факториальная функция растёт очень быстро!

0! = 1

1! = 1

2! = 2

3! = 6

4! = 24

5! = 120

6! = 720

7! = 5 040

8! = 40 320

9! = 362 880

10! = 3 628 800

11! = 39 916 800

12! = 479 001 600

Continue reading "Сочетания, перестановки и беспорядочность"

Автор: Radim Marek, Good CTE, bad CTE

Обобщённое табличное выражение (Common Table Expression, CTE) — это первая возможность, к которой часто обращаются разработчики, выходя за рамки базового SQL, а зачастую и единственная. Вы пишете подзапрос после WITH, даёте ему имя и используете в остальной части запроса. Он существует только на время выполнения этого запроса.

Но популярность CTE обычно связана не столько с модернизацией кода, сколько с обещанием императивной логики. Для многих CTE выступает в роли простого для понимания средства от «страшных запросов» и способа навязать базе данных порядок выполнения. Многие пишут запросы так, как будто они говорят оптимизатору: «сначала сделай это, затем сделай то».

Это создаёт проблему. CTE обеспечивают декомпозицию запросов, рекурсию и многосоставные DDL. Планировщик обрабатывает их по-разному в зависимости от того, как вы их пишете и используете. Долгое время (до PostgreSQL 12) CTE служили барьером для оптимизации. Планировщик не мог проталкивать условия предикатов внутрь них, не мог использовать индексы на нижележащих таблицах. Он не мог сделать ничего, кроме как материализовать их и просканировать полученный результат.

PostgreSQL 12 изменил это. Теперь CTE могут быть встроены, материализованы или находиться в промежуточном состоянии, в зависимости от того, как вы их пишете.

Continue reading "CTE - Хороший, плохой, злой"

Автор: Richard Yen, EXPLAIN's Other Superpowers

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

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

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

В этой статье мы рассмотрим некоторые из этих менее популярных опций.

Continue reading "Cуперспособности EXPLAIN в PostgreSQL"