Skip to content

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

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


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

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


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

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

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


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


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

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

Новости за 2025-10-11 - 2025-10-17

§ Приглашаю вас подписаться на ТГ-канал Александра Гладченко "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"

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

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


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



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

Продолжить чтение "Подбор параметра FetchSize в драйвере PostgreSQL JDBC"

LWLock:LockManager и подготовленные операторы

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

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

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

Загадки max_locks_per_transaction

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




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


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


Продолжить чтение "Загадки max_locks_per_transaction"

Основы баз данных: объяснение уровней изоляции в PostgreSQL

Пересказ статьи Duc Tran. Database Fundamentals: Isolation Levels Explained in PostgreSQL


Когда одновременно выполняется несколько транзакций, как база данных поддерживает согласованность данных? Вот где вступают в действие уровни изоляции. Они определяют видимость изменений, совершенных одной транзакцией, остальным - и помогают предотвратить проблемы типа грязного чтения, неповторяемого чтения и фантомных чтений.

В этой статье мы исследуем четыре стандартных уровня изоляции, определенных стандартом SQL, и как PostgreSQL реализует их под капотом. Вы узнаете, что позволяет каждый уровень и что он запрещает, и как PostgreSQL обеспечивает целостность данных - особенно посредством своей сериализуемой изоляции (Serializable isolation)

Начнем!
Продолжить чтение "Основы баз данных: объяснение уровней изоляции в PostgreSQL"

Новости за 2025-10-04 - 2025-10-10

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

Топик		Сообщений	Просмотров
139 (Learn) 8 5
146 (Learn) 6 7
138 (Learn) 5 6
147 (Learn) 4 6
34 (DML) 3 5

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

Автор		Сообщений
rock_4 17
pegoopik 11
Nividimka 7
gennadi_s 3
selber 2
Продолжить чтение "Новости за 2025-10-04 - 2025-10-10"

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

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



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



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



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

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

Новые функции SQL Regex в SQL Server 2025 и SSMS

Пересказ статьи Scott Murray. New SQL Regex Functions in SQL Server 2025 and SSMS


Если вы похожи на меня, то Regex - это одна из тех древних технологий/инструментов, которые вы никогда в достаточной мере не изучали. Вас может удивить, что Regex (регулярное выражение) на самом деле появились в 1950-х годах в период разработки многих современных компьютеров (Регулярное выражение — Википедия). Конечно, как и многие основные инструменты, они нарастили за последние 70 лет функциональность и области использования.

Использование регулярных выражений в SQL Server было ограничено выходом за пределы SQL Server для выполнения их функций через внешнюю службу, такую как использование процедур CLR. К счастью в 2024 Microsoft наконец начал добавление прямой поддержки, сначала в базах данных Azure SQL, а сейчас такая функциональность доступна в SQL Server 2025. В итоге в SQL Server стали доступными несколько Regex-функций, которые и будут тут рассмотрены. Основой реализации в SQL Server стала библиотека RE2 (RE2 Regular Expression Syntax).

Продолжить чтение "Новые функции SQL Regex в SQL Server 2025 и SSMS"
Категории: T-SQL

Расширьте свои возможности в SQL с помощью функций JSON в PostgreSQL

Пересказ статьи Paulo Fagundes. ODC: Supercharge Your Advanced SQL with PostgreSQL JSON Functions


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

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

Естественно, каждая СУБД вводит свой собственный набор функций и функциональность, которые мы можем использовать, и PostgreSQL предлагает широкий выбор функций для запросов и манипуляции объектами JSON.

Продолжить чтение "Расширьте свои возможности в SQL с помощью функций JSON в PostgreSQL"

Новости за 2025-09-27 - 2025-10-03

§ Очередное усиление проверки задачи 20 (DML).


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

Топик		Сообщений	Просмотров
137 (Learn) 8 6
138 (Learn) 6 6
139 (Learn) 4 4
77 (SELECT) 2 3
240 (SELECT) 2 2

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

Автор		Сообщений
pegoopik 11
rock_4 9
_artist 7
Nividimka 2
selber 2

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

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

Автор: Laurenz Albe


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

Продолжить чтение "Стоит ли перестраивать индексы в PostgreSQL?"

Использование гибридной транзакционной/аналитической обработки в PostgreSQL

Пересказ статьи Sheikh Wasiu Al Hasib. PostgreSQL Hybrid Transactional/Analytical Processing using




Что такое HTAP?


HTAP - это аббревиатура от гибридной транзакционной/аналитической обработки (Hybrid Transactional/Analytical Processing). Она характеризует системы баз данных, которые могут обрабатывать как транзакционные (OLTP), так и аналитические (OLAP) рабочие нагрузки одновременно на одном и том же наборе данных. Такая возможность позволяет выполнять аналитические запросы к текущим транзакционным данным без необходимости извлекать, преобразовывать и загружать (ETL) данные в отдельное хранилище для последующего анализа.

Продолжить чтение "Использование гибридной транзакционной/аналитической обработки в PostgreSQL"