SQL-Ex blog

Автор: Николай Самохвалов #PostgresMarathon 2-002: Relation-level locks

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

Ключевая страница в документации Postgres, описывающая блокировки на уровне отношений, находится здесь: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES

Эта страница в документации называется «13.3. Explicit Locking» («Явные блокировки») и может ввести в заблуждение, потому что на ней также говорится об неявных блокировках (например, если вы выполняете DML или DDL, блокировки накладываются неявно; а если вы выполняете LOCK или SELECT .. FOR UPDATE, вы явным образом запрашиваете блокировки). Впрочем, возможно, это просто моя терминологическая придирчивость.

На этой странице есть полезная «Table 13.2. Conflicting Lock Modes», которая помогает понять, как один запрос на получение блокировки может быть заблокирован другой, уже полученной или ожидающей (!) блокировкой:

Возможная путаница здесь связана со словом «row», встречающимся в названиях некоторых режимов блокировок, — не стоит думать, что эти режимы относятся к уровню строк. Это всё равно блокировки уровня отношений. Понятие блокировок уровня строк — особое, к нему мы ещё вернёмся отдельно. В документации эта путаница, впрочем, оговорена:

Помните, что все эти режимы — блокировки уровня таблицы, даже если в названии есть слово «row»; названия режимов сложились исторически.

Continue reading "Блокировки на уровне отношений"

Автор: Warda Bibi: Understanding Disaster Recovery in PostgreSQL

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

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

Continue reading "Аварийное восстановление в PostgreSQL"

Автор: Shane Borden
Understanding and Setting PostgreSQL JDBC Fetch Size

По умолчанию драйвер PostgreSQL JDBC извлекает все строки сразу и пытается загрузить их в память; в отличие, например, от драйвера Oracle, который по умолчанию извлекает по 10 строк за раз. Оба подхода имеют свои плюсы и минусы, однако в контексте тех типов нагрузок, с которыми я сталкиваюсь ежедневно, поведение PostgreSQL по умолчанию обычно неоптимально.

В качестве ориентира: значение fetch size по умолчанию в PostgreSQL подходит, если ваши запросы всегда возвращают небольшие наборы данных. Если возможно получение более крупных результатов, вы увидите заметную разницу в производительности между малыми и большими наборами, и стоит подумать об изменении значения fetch size.

Continue reading "Подбор параметра FetchSize в драйвере PostgreSQL JDBC"

Автор: Николай Самохвалов #PostgresMarathon 2-008: LWLock:LockManager and prepared statements

Для простого SELECT из таблицы на этапе планирования Postgres блокирует таблицу и все её индексы с помощью AccessShareLock. Чтобы смягчить это, можно просто использовать подготовленные операторы (prepared statements). Меня удивило, что единственная блокировка появилась при 7‑й выборке, а не при 6‑й. Я ожидал, что первые 5 раз будет использоваться так называемый «пользовательский план» (custom plan), и все 6 объектов (таблица + 5 индексов) будут заблокированы, а на 6‑м вызове произойдёт переключение на «обобщённый» план (generic plan), и блокироваться будет только сама таблица. Почему 5 вызовов? Continue reading "LWLock:LockManager и подготовленные операторы"

Автор: Николай Самохвалов #PostgresMarathon 2-007: Should we worry about pg_blocking_pids()'s observer effect?

Много лет назад, разрабатывая сложные автоматизированные процедуры для одной крупной компании, я понял, что автоматизации необходимы компоненты мониторинга. В частности, важно понимать конкуренцию тяжёлых блокировок — например, чтобы распознавать ситуации, когда неудачно спроектированное изменение блокируется чем‑то вроде autovacuum, работающим в режиме предотвращения «перехлёста» идентификаторов транзакций (в этом режиме он никому не уступает).

Так я пришёл к pg_blocking_pids() и анализу, описанному в «Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)».

К сожалению, есть нюанс — как сказано в документации по pg_blocking_pids():

Frequent calls to this function could have some impact on database performance, because it needs exclusive access to the lock manager's shared state for a short time.

Continue reading "Стоит ли беспокоиться об «эффекте наблюдателя» для pg_blocking_pids()?"

Автор: Николай Самохвалов #PostgresMarathon 2-006: Mysterious max_locks_per_transaction

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

The shared lock table has space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time. This parameter limits the average number of object locks used by each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited. The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent table with many children. This parameter can only be set at server start.

When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, queries will not be allowed in the standby server.

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

Давайте уделим этому ещё больше времени, разберём каждую деталь и дополним тем, чего нет в приведённом описании.

Continue reading "Загадки max_locks_per_transaction"

Автор: Franck Pachot Improving Your SQL Indexing: How to
Effectively Order Columns

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

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

После фильтрации вы хотите показать лишь несколько строк, отсортированных по
некоторым столбцам, и ограничить результат с помощью LIMIT или FETCH FIRST ROWS
— типичный случай разбивки на страницы. Хотя индексы помогают находить
диапазоны значений и упорядочивать результаты, при всех этих условиях трудно
решить, в каком порядке располагать столбцы в индексе.

Continue reading "Улучшаем индексы: эффективный порядок столбцов ключа"

Автор: Laurenz Albe

Люди часто спрашивают: «Как можно автоматически регулярно перестраивать индексы?» или «Когда нужно перестраивать индексы в PostgreSQL?». Каждый раз у меня возникает ощущение, что они пытаются решить проблему, которой нет. Впрочем, оператор REINDEX существует не зря, и иногда перестроить индекс действительно разумно. В этой статье я объясню, когда уместно перестраивать индекс и как получить данные, чтобы принять такое решение.

Continue reading "Стоит ли перестраивать индексы в PostgreSQL?"

Автор: Андрей Лепихов

8 июля 2024 года - Why PostgreSQL prefers MergeJoin to HashJoin?

Сегодняшняя статья вызвана загадочным наблюдением: пользователи, особенно те, кто использует уровень абстракции наподобие REST или библиотек ORM для взаимодействия с базами данных, часто отключают опцию MergeJoin во всём экземпляре базы данных. Они оправдывают это действие многочисленными случаями снижения производительности.

Учитывая, сколько интересных путей выполнения MergeJoin добавляет в систему, разрабатывая IncrementalSort или порядки сортировки, полученные из лежащего в основе IndexScan, это выглядит странно: ещё одна ошибка искажённого баланса стоимости внутри модели стоимости PostgreSQL?

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

Continue reading "Почему PostgreSQL предпочитает MergeJoin вместо HashJoin?"

Перевод статьи: How To Find High CPU Utilization Query In Postgresql

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

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

Continue reading "Как найти запросы с высокой загрузкой процессора в PostgreSQL"