SQL-Ex blog

Автор: Николай Самохвалов #PostgresMarathon 2-010: Prepared statements and partitioned table lock explosion, part 2

В первой части мы сосредоточились на поведении Lock Manager при работе с подготовленными выражениями и секционированными таблицами.

В простом синтетическом примере мы увидели взрыв числа блокировок: 8 с custom‑планами в первых пяти вызовах, до 52 с generic‑планом в шестом, и до 13 с использование кэшированного generic‑плана в седьмом и последующих вызовах. Остаются вопросы:

• почему именно в 6‑м вызове происходит этот скачок до 52 блокировок, и можно ли его избежать?


• почему мы блокируем все 12 секций, хотя во время выполнения 11 из них отсекаются?

Продолжить чтение "Подготовленные операторы и лавина блокировок на секционированной таблице, часть 2"

Автор: Николай Самохвалов #PostgresMarathon 2-009: Prepared statements and partitioned table lock explosion, part 1

В статье "LWLock:LockManager и подготовленные операторы" мы выяснили, что prepared statements могут радикально снизить конкуренцию LWLock:LockManager, переключаясь с блокировок планировщика (которые блокируют всё подряд) на блокировки исполнителя (которые блокируют только то, что действительно используется). Начиная с 7‑го выполнения, мы увидели падение числа блокировок с 6 (таблица + 5 индексов) до всего 1 (только таблица).

Там мы тестировали лишь простую, не секционированную таблицу. А что будет, если таблица секционирована?

Продолжить чтение "Подготовленные операторы и лавина блокировок на секционированной таблице, часть 1"

Автор: Vivek Johari Difference Between CTE and Subqueries in SQL: A Complete Guide for Developers

Язык структурированных запросов (SQL) — основа манипулирования и извлечения данных в современных базах. Будь то MySQL, PostgreSQL, SQL Server или Oracle, SQL предоставляет мощные инструменты для эффективной работы с данными. Среди них важнейшую роль в упрощении сложных операций играют Common Table Expressions (CTE) и подзапросы.

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

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

Это руководство подробно разбирает разницу между CTE и подзапросами в SQL, с примерами, вариантами применения и советами по оптимизации, которые сделают вас более эффективным SQL‑разработчиком.

Продолжить чтение "Разница между CTE и подзапросами в SQL: полноценное руководство для разработчиков"

Пересказ статьи Prapti Patel. PostgreSQL: Why “GRANT ALL” Still Gives “Must Be Owner” Error

Проблема

Работая над проектом, связанным с PostgreSQL, я столкнулся со странной проблемой:

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

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO root;

GRANT USAGE ON SCHEMA public TO root;

ALTER TABLE abc ADD COLUMN test_column TEXT;

ERROR:  must be owner of relation abc

Продолжить чтение "PostgreSQL: почему “GRANT ALL” все еще выдает ошибку “Must Be Owner” (должен быть владельцем)?"

§ Уточнение формулировки задачи 174 (обуч. этап) в ответ на замечание maxim_bredikhin

§ Популярные темы недели на форуме

Топик		Сообщений	Просмотров
 35 (DML)		4		6
 152 (Learn)		4		7
 181 (SELECT)		4		5
 145 (Learn)		2		7

Продолжить чтение "Новости за 2025-10-18 - 2025-10-24"

Автор: Николай Самохвалов #PostgresMarathon 2-003: The roots of LWLock:LockManager

Как мы уже обсуждали, Lock Manager управляет тяжёлыми блокировками — их существует множество видов (разные режимы, разные уровни гранулярности). Эти блокировки снимаются только в конце транзакции.

В самом простом случае, когда вы выполняете SELECT по таблице, эта таблица блокируется режимом AccessShareLock. И не только таблица, но и все её индексы — это происходит на этапе планирования (всегда происходит, если только вы не используете prepared statements). Цель — защититься от параллельного DROP. Все эти блокировки снимаются только при завершении транзакции.

Продолжить чтение "Родословная LWLock:LockManager"

Пересказ статьи Chandan Shukla. From Rows to Pages: The Hidden Chaos Behind SQL Server’s Sampling Methods

Введение

Выборка данных является обычным требованием, применимым ко многим реальным рабочим нагрузкам SQL Server, пытаетесь ли вы протестировать подмножество данных, выполняете просмотр записей перед экспортом, или строите небольшую копию таблицы для разработки, выборка становится необходимым инструментом. SQL Server предлагает оператор TABLESAMPLE, который на первый взгляд выглядит простым и многообещающим. Написав запрос типа select top 100 from Orders tablesample 10 percent, вы естественно ожидаете, что SQL Server вернет 10 процентов случайных строк. К сожалению, это не так работает.

Предложение TABLESAMPLE работает не с отдельными строками, а с физическими страницами данных. Это означает, что SQL Server пытается вернуть строки из приблизительно 10 процентов от общего числа страниц, а не строк. Если ваши данные равномерно распределены и каждая страница заполнена, это может дать вам результаты, близкие к 10 процентам строк. Но в реальности, благодаря фрагментации, обновлениям и удалениям, большинство страниц содержат различное число строк. Именно тут TABLESAMPLE становится весьма непредсказуемым. Давайте смоделируем эту ситуацию на простом примере для демонстрации поведения на практике.
Продолжить чтение "От строк к страницам: скрытый хаос в методах выборки внутри SQL Server"

Автор: Николай Самохвалов #PostgresMarathon 2-001: Lightweight and heavyweight locks

Для разминки поговорим о легковесных (кратких) и тяжёлых блокировках (или «обычных блокировках», или просто «блокировках»).

Я опираюсь на следующие материалы:

• Документация PostgreSQL (прежде всего, 13.3. Explicit Locking)


• Исходники LockManager — каталог src/backend/storage/lmgr (и прежде всего README: Locking Overview)


• Книга Егора Рогова «PostgreSQL 17 изнутри», часть III «Блокировки»

Продолжить чтение "Краткие и тяжёлые блокировки PostgreSQL"

Автор: Николай Самохвалов #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»; названия режимов сложились исторически.

Продолжить чтение "Блокировки на уровне отношений"

Пересказ статьи Dmitry Romanoff. Understanding Recursive Queries in PostgreSQL: A Process Hierarchy Example

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

Задача: представление иерархических данных

Рассмотрим сценарий, в котором нам требуется представить иерархию процессов. Каждый процесс может иметь родительский процесс, формируя древообразную структуру. Наша цель - выполнить эффективный запрос к этим иерархическим данным и отобразить их в читабельном формате.
Продолжить чтение "Понимание рекурсивных запросов в PostgreSQL: пример иерархии процессов"

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

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

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

Продолжить чтение "Аварийное восстановление в PostgreSQL"

§ Приглашаю вас подписаться на ТГ-канал Александра Гладченко "MS SQL Server - дело тонкое...": https://t.me/mssqlhelp.
Канал ориентирован на администраторов базы данных SQL Server.

§ Популярные темы недели на форуме

Топик		Сообщений	Просмотров
 35 (DML)		4		6
 152 (Learn)		4		7
 181 (SELECT)		4		5
 145 (Learn)		2		7

§ Авторы недели на форуме

Автор		Сообщений
 rock_4  	14
 pegoopik  	6
 Nividimka	2
 gennadi_s  	2

Продолжить чтение "Новости за 2025-10-11 - 2025-10-17"

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

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

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

Продолжить чтение "Подбор параметра 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 вызовов? Продолжить чтение "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.

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