SQL-Ex blog

Автор: Amit Langote, Postgres: caches and invalidation

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

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

Автор: Amit Langote, Postgres: where is the data

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

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

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

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

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

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

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

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

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


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


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


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

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

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

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

Автор: 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: полноценное руководство для разработчиков"

Пересказ статьи 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” (должен быть владельцем)?"

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

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

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

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


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


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

Continue reading "Краткие и тяжёлые блокировки 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»; названия режимов сложились исторически.

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

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

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

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

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

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

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

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

Continue reading "Аварийное восстановление в 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

Continue reading "Новости за 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.

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 и подготовленные операторы"