Skip to content

Кэши и их аннулирование в Postgres

Posted by mssqlhelp on
Автор: Amit Langote, Postgres: caches and invalidation

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

Continue reading "Кэши и их аннулирование в Postgres"

Где хранятся данные Postgres

Posted by mssqlhelp on
Автор: Amit Langote, Postgres: where is the data

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

Continue reading "Где хранятся данные Postgres"

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

Posted by mssqlhelp on

Автор: Николай Самохвалов #PostgresMarathon 2-011: Prepared statements and partitioned tables — the paradox, part 3


В первой и второй частях мы разобрали, почему на 6‑м выполнении при построении generic‑плана для секционированной таблицы возникает лавина блокировок: планировщик вынужден блокировать все 52 отношения, потому что без значений параметров он не может отсечь секции.


Сегодня проверим, что происходит при разных значениях настройки plan_cache_mode.

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

Профессиональная проверка таблиц и индексов в PostgreSQL

Posted by Sergey Moiseenko on

Пересказ статьи Sadigrzazada. Inspecting PostgreSQL Tables and Indexes Like a Pro


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

  • Структуру таблицы

  • Размер таблицы

  • Информацию об индексе

  • Размер индекса

Continue reading "Профессиональная проверка таблиц и индексов в PostgreSQL"

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

Posted by mssqlhelp on

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


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


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



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

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

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

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

Posted by mssqlhelp on

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


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


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

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

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

Posted by mssqlhelp on

Автор: 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‑разработчиком.


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

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

Posted by Sergey Moiseenko on

Пересказ статьи 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

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

Новости за 2025-10-18 - 2025-10-24

Posted by Sergey Moiseenko on

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


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

Топик		Сообщений	Просмотров
 35 (DML)		4		6
 152 (Learn)		4		7
 181 (SELECT)		4		5
 145 (Learn)		2		7
Continue reading "Новости за 2025-10-18 - 2025-10-24"

Родословная LWLock:LockManager

Posted by mssqlhelp on

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


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


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

Continue reading "Родословная LWLock:LockManager"

От строк к страницам: скрытый хаос в методах выборки внутри SQL Server

Posted by Sergey Moiseenko on

Пересказ статьи 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 становится весьма непредсказуемым. Давайте смоделируем эту ситуацию на простом примере для демонстрации поведения на практике.
Continue reading "От строк к страницам: скрытый хаос в методах выборки внутри SQL Server"

Краткие и тяжёлые блокировки PostgreSQL

Posted by mssqlhelp on

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


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



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




Continue reading "Краткие и тяжёлые блокировки PostgreSQL"

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

Posted by mssqlhelp on

Автор: Николай Самохвалов #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 "Блокировки на уровне отношений"

Понимание рекурсивных запросов в PostgreSQL: пример иерархии процессов

Posted by Sergey Moiseenko on

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


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

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


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