SQL-Ex blog

Автор: Chris Travers PostgreSQL and NUMA, part 2 of 4 (NUMA, Linux, and PostgreSQL before libnuma Support)

Эта статья опирается на предыдущий материал «Введение в NUMA» (PostgreSQL и NUMA, часть 1 из 4) и предполагает не только знание определения NUMA, но и общее понимание аппаратной и программной реализации.

В этой части рассматривается взаимодействие PostgreSQL и Linux на системах с NUMA. Тема сложная, поэтому кое‑где используются упрощения. Тем не менее здесь собрана выжимка общих сведений о работе PostgreSQL 17 и ниже (а также PostgreSQL 18 без поддержки libnuma) на NUMA‑системах с Linux в качестве точки отсчёта. К концу чтения вы не только будете в состоянии уверенно запускать Postgres на NUMA‑системе, но и поймёте, почему поддержка libnuma в PostgreSQL 18 настолько важна.

Continue reading "PostgreSQL и NUMA, часть 2 из 4 (NUMA, Linux и PostgreSQL до появления поддержки libnuma)"

Автор: Chris Travers PostgreSQL and NUMA, part 1 of 4

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

Эти статьи сосредоточены на низкоуровневых «как» и «почему» в контексте неоднородного доступа к памяти (Non‑Uniform Memory Access), чтобы затем было проще понимать решения и рекомендации — с упором на концептуальную сторону. К сожалению, во многих местах без технических деталей не обойтись: голые концепции без деталей в лучшем случае сбивают с толку.

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

Continue reading "PostgreSQL и NUMA, часть 1 из 4"

Автор: Josef Machytka PostgreSQL 18 enables data‑checksums by default

Как я объяснял в докладе на PostgreSQL Conference Europe 2025, повреждение данных может незаметно случиться в любой базе PostgreSQL и останется так, пока мы физически не прочитаем повреждённые данные. Причин, по которым отдельные блоки в таблицах и других объектах могут быть испорчены, немало. Даже современное аппаратное обеспечение хранилищ далеко от безошибочности. Бинарные резервные копии, сделанные инструментом pg_basebackup (очень распространённая стратегия в мире PostgreSQL), скрывают эти проблемы, поскольку они не проверяют данные, а копируют файлы «как есть». С релизом PostgreSQL 18 сообщество решило по умолчанию включить контрольные суммы данных — серьёзный шаг к раннему обнаружению таких сбоев. В этой статье рассматривается, как PostgreSQL реализует контрольные суммы, как обрабатывает их несоответствия и как можно включить контрольные суммы в существующих кластерах.

Continue reading "В PostgreSQL 18 контрольные суммы данных включены по умолчанию"

Автор: 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"

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

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

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

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

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

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

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