Postgres Guru | Базы данных

🛠️ Параметр commit_delay в PostgreSQL и на что он влияет.

Как мы все прекрасно помним (знаем), в PostgreSQL измененные в оперативной памяти данные не попадают сразу в базу данных, а пишутся сначала в журнал WAL. Сделано это для повышения отказоустойчивости базы к сбоям и снижения нагрузки на диск.

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

commit_delay (в микросекундах) - определяет, как долго PostgreSQL будет ждать перед фиксацией транзакции и ее записью в WAL, если выполняется определённое количество других транзакций (commit_siblings).

commit_siblings — минимальное количество параллельных транзакций, при котором задержка (commit_delay) активируется.

Т.е. эти два параметра идут рука об руку, и если первый определяет время задержки записи транзакций в WAL, то второй определяет кол-во незаписанных транзакций, при котором активируется задержка.

commit_delay будет особенно полезен на медленных дисках (например, HDD) группировка WAL-записей может снизить нагрузку, но commit_siblings должен быть правильно настроен (обычно ≥5).

Обратной стороной уменьшения кол-ва операций записи на диск является уменьшение отзывчивости всей системы в целом, так как PostgreSQL не будет фиксировать транзакции до тех пор, пока они не запишутся в WAL. Это происходит потому что по умолчанию у нас включен параметр synchronous_commit.

⚠️ Помним, что отключение этого параметра (off) может привести к потере последних транзакций в случае сбоя. Но если для вас такая потеря не критична, то отключение этого параметра может значительно повысить производительность, здесь действуем исключительно на свой страх и риск.

По факту же, в большинстве современных систем с SSD/NVMe дисками commit_delay не нужен. Лучше оставить его значение по умолчанию (0) и сосредоточиться на других оптимизациях, таких как:
shared_buffers и wal_buffers, checkpoint_timeout и max_wal_size.

Если же у вас высокая конкуренция за запись WAL (например, десятки одновременных транзакций), можно поэкспериментировать с commit_delay = 10 -100 мс и commit_siblings = 5 -10. Но всегда проверяйте влияние на реальной нагрузке!

#pgsettings

Ваши SQL-запросы работают медленно, а базы данных грузятся дольше, чем хотелось бы? Исправим это на нашем бесплатном уроке 31 марта в 20:00 мск: https://otus.pw/38hM/

Индексы — один из ключевых инструментов ускорения работы с БД. Но как выбрать нужный тип, правильно его создать и избежать ошибок?

После занятия вы сможете уверенно работать с индексами в PostgreSQL и MS SQL Server, оптимизировать запросы и делать базы данных быстрее. 

Регистрируйтесь прямо сейчас и получите скидку на большое обучение «SQL для разработчиков и аналитиков»: https://otus.pw/38hM/

erid: 2W5zFHQJ1ua

⚖️ Параметр target_session_attrs в PostgreSQL.

Раз уж мы с вами в прошлом посте заговорили про шардирование и отказоустойчивость в PostgreSQL, вспомнился параметр target_session_attrs, который может нам обеспечить балансировку нагрузки и отказоустойчивость баз данных прямо из коробки.

Версия PostgreSQL 14 представила новую настройку подключения — target_session_attrs, которая позволяет клиентам указывать требования к серверу при подключении в режиме multi-host.

Это нам может пригодится для:
✅ Балансировки нагрузки между несколькими серверами.
✅ Обеспечения отказоустойчивости (High Availability, HA).
✅ Разделения чтения и записи (read/write splitting).

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

Параметр target_session_attrs как раз и определяет, какие типы серверов допустимы для подключения.

Доступные значения:

📌 any (по умолчанию) – подключиться к любому доступному серверу.
📌 read-write – разрешает подключение только к серверу, принимающему запись (primary в репликации).
📌 read-only – подключается только к read-only серверам (standby реплики).
📌 primary – подключаться только к к главным серверам (не репликам).
📌 standby – подключаться только к standby репликам.
📌 prefer-standby – пытается подключиться к standby, но если таких нет — выбирает primary.

Разница между read-only и standby заключается в том, что у вас может быть сервер, который принимает только операции чтения, но при этом не является репликой. Такое может быть, если вы включили на сервере параметр default_transaction_read_only. Тоже самое касается и параметров read-write и primary.

Примеры использования.

Подключение только к primary-серверу:

# psql "host=node1,node2,node3 target_session_attrs=read-write user=postgres"

Если node1 — принимает операции записи,то клиент подключится к нему. Если node1 упал, но node2 — тоже принимает операции записи, подключение перейдёт к нему.

Предпочтительное использование standby:

# psql "host=node1,node2,node3 target_session_attrs=prefer-standby user=postgres"

Сначала попытается подключиться к standby, но если таких нет — выберет primary.

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

На этом все! До связи!

#pghi

Хотите глубже понять управление процессами в микросервисах и повысить надёжность систем? На ум сразу приходят распределённые транзакции – классический, но, увы, проблематичный метод. Но мы предлагаем кое-что получше: шаблон «Сага»!

На открытом вебинаре “«Саги» vs распределённые транзакции: как моделировать рабочие потоки в распределённой архитектуре” вы узнаете:

- Почему распределённые транзакции могут быть непрактичны в контексте микросервисов
- Как работает Сага и в чём преимущества этого шаблона
- Какие типы «саг» существуют и как их применять
- Как использовать Сагу для моделирования сложных рабочих потоков

И, конечно же, получите важные рекомендации по внедрению саг в реальных проектах.

Будет интересно архитекторам ПО, системным аналитикам, бэкенд и фулстек-разработчикам.

Спикер: Сергей Прощаев Java-разработчик в ПАО «Сургутнефтегаз».

Бонус! Скидка 5% на любой курс OTUS и чек-лист «Подойдёт ли вам шаблон SAGA?

Семь вопросов создателю проекта»

25 марта, 19:00 МСК, Бесплатно

Записаться на событие - https://otus.pw/o0E2J/?erid=2W5zFJVig4G

Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963.

🛠️ Шардирование баз данных PostgreSQL: основные принципы и подходы.

Шардирование (sharding) — это метод горизонтального разделения данных, который позволяет распределять информацию между несколькими серверами или базами данных для повышения производительности, масштабируемости и отказоустойчивости. Шардирование реляционной базы данных является не тривиальной задачей в сило того, что нужно соблюдать при этом транзакционную целостность и правила ACID, но тем не менее варианты есть.

Шардирование позволяет:

✅ Увеличить производительность: Распределение данных между несколькими узлами снижает нагрузку на каждый отдельный сервер.

✅ Масштабироваться горизонтально: Добавление новых узлов позволяет увеличивать общую емкость системы.

✅ Повысить отказоустойчивость: Данные дублируются на нескольких узлах, что снижает риск потери информации.

Основные подходы к шардированию:

📌 Горизонтальное разделение данных: Данные разделяются по строкам таблицы. Например, пользователи могут быть распределены между шардами по географическому признаку или по хэшу идентификатора.

📌 Вертикальное разделение данных:
Таблицы разделяются по столбцам. Например, одна таблица может хранить основные данные пользователя, а другая — дополнительную информацию.

📌 Гибридное шардирование: Комбинация горизонтального и вертикального разделения, которая позволяет гибко управлять данными.

PostgreSQL не имеет встроенной поддержки шардирования, но существует несколько инструментов и подходов для его реализации:

📌 Ручное шардирование:
Разработчик самостоятельно создает несколько баз данных и распределяет данные между ними. Тут, сами понимаете, ни какой автоматизации, нужно что-то придумывать, писать какие-то скрипты и т.д.

📌 Использование расширений:
Но не все так плохо, у нас есть пара вариантов, которые смогут нам помочь автоматизировать шардирование в PostgreSQL:

✅ Citus: Расширение от известной одноименной компании. Оно позволяет работать с распределенными таблицами и выполнять запросы на нескольких узлах. Официальный GitHub расширения:

➡️ https://github.com/citusdata/citus?tab=readme-ov-file

✅ Postgres Pro Shardman: Это фактически не расширение, а отдельная СУБД, разработанная компанией Postgres Pro. Это коммерческое решение, созданное для горизонтального масштабирования баз данных, обеспечивающее отказоустойчивость и дающие строгие гарантии изолированности и согласованности данных. Подробнее можно почитать вот здесь:

➡️ https://postgrespro.ru/products/shardman

📌 Федеративные базы данных: Использование внешних таблиц (foreign data wrappers) для доступа к данным на других серверах. Это позволяет объединять данные из разных источников, но не обеспечивает автоматического распределения.

Плюсы и минусы шардирования.

Преимущества:
✅ Улучшение производительности за счет распределения нагрузки;
✅ Возможность масштабирования системы;
✅ Повышение отказоустойчивости.

Недостатки:
❌ Сложность настройки и управления;
❌ Необходимость учитывать распределенные транзакции;
❌ Ограничения на выполнение сложных запросов, затрагивающих несколько шардов.

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

На этом все! До связи!

#pgsettings

🤖 Расширение pg_qualstats. Углубленный анализ предикатов для оптимизации запросов в PostgreSQL.

Расширение pg_qualstats позволяет нам собирать и анализировать статистику по предикатам (условиям в `WHERE`, `JOIN`, `HAVING` и т.д.) в SQL-запросах. Это расширение особенно полезно для выявления узких мест в производительности и определения, какие индексы могут улучшить выполнение запросов.

Официальный GitHub расширения:

➡️ https://github.com/powa-team/pg_qualstats

Расширенее помогает ответить нам на следующие вопросы:
✅ Какие предикаты чаще всего используются?
✅ Какие столбцы участвуют в условиях фильтрации?
✅ Какие столбцы таблицы взаимосвязаны между собой в запросах?
✅ Какие типы операций (например, `=`, `>`, `<`, `LIKE`) применяются к этим столбцам?
✅ Какие индексы могут быть полезны для ускорения выполнения запросов?

Основные возможности:

📌 Сбор статистики по предикатам;
📌 Анализ частоты использования;
📌 Определение потенциальных индексов;
📌 Поддержка различных операций (>, <, =, LIKE и т.д.).

Мы можем использовать pg_qualstats совместно с другими расширениями, такими как pg_stat_statements, для более глубокого анализа производительности.

Установка и настройка.

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

# sudo make install

Далее нужно прописать его в параметр

shared_preload_libraries = 'pg_qualstats'

и перезапустить службу PostgreSQL. После этого подключаемся к нужной базе и вводим команду:

CREATE EXTENSION pg_qualstats;

После установки расширения у нас в файле postgresql.conf станут доступны параметры для его настройки.

Например:

pg_qualstats.enabled: Включение или отключение сбора статистики.
pg_qualstats.track_constants: Отслеживание констант в предикатах.
pg_qualstats.max: Максимальное количество записей, которые будут храниться.

С остальными параметрами можете ознакомиться в документации расширения.

Примеры использования pg_qualstats.

📌 Топ предикатов по частоте использования:

SELECT * FROM pg_qualstats ORDER BY execution_count DESC;

📌 Предикаты для конкретного столбца:

SELECT * FROM pg_qualstats WHERE attname = 'your_column_name';

📌 Определение потенциальных индексов:

SELECT
relname AS table_name,
attname AS column_name,
opno::regoperator AS operator,
execution_count
FROM pg_qualstats
ORDER BY execution_count DESC;

Преимущества

✅ Улучшение производительности:
- Расширение помогает выявить узкие места в запросах и определить, какие индексы могут ускорить выполнение.

✅ Простота использования:
- Установка и настройка занимают минимум времени, а данные предоставляются в удобном для анализа виде.

✅ Гибкость:
- Расширение поддерживает различные типы предикатов и может быть интегрировано с другими инструментами.

✅ Экономия времени:
- Автоматизация сбора статистики позволяет сосредоточиться на анализе, а не на ручном сборе данных.

Ограничения

❌ Накладные расходы:
- Сбор статистики может добавлять небольшую нагрузку на сервер, особенно при высокой частоте запросов.

❌ Требует анализа:
- Данные, собранные расширением, требуют интерпретации и анализа для принятия решений по оптимизации.

❌ Не заменяет другие инструменты:
- pg_qualstats лучше использовать в сочетании с другими расширениями, такими как pg_stat_statements, для полного анализа производительности.

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

На этом все! До связи!

#pgext

💪 Качаем скиллы PostgreSQL!

10 апреля 2025 года пройдет бесплатное комьюнити-мероприятие из серии PG BootCamp Russia — конференция, направленная на приобретение практических навыков при работе с СУБД PostgreSQL.

🔵Программа рассчитана как на начинающих специалистов, так и на более опытных разработчиков, желающих углубить знания в части ядра и экосистемы продукта
🔵 Ведущие эксперты в области СУБД проведут мастер-классы и лекции по наиболее востребованным и интересным темам
🔵Для тех, кто не сможет присутствовать очно, предусмотрена онлайн-трансляция

🧑‍🎓 Все участники получат электронные сертификаты, подтверждающие приобретение новых знаний и навыков.

📌 Дата и время: 10 апреля, в 10:00 (по ЕКБ)
Формат: офлайн/онлайн
Место проведения: конгресс-отель «Екатеринбург»

✅ Зарегистрируйтесь сейчас и приготовьтесь к захватывающему путешествию в мир СУБД!

Реклама. ООО "ТАНТОР ЛАБС" ИНН 9701183207 Erid: 2VtzqxcX5cB

🛠️ Выбираем стратегию выполнения запроса вручную в PostgreSQL.

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

⚠️ Используйте эти параметры с осторожностью, или в крайних случаях, так как они напрямую влияют на планы выполнения запросов и их производительность.

Данные параметры меняются в конфигурационном файле postgresql.conf

📌 enable_nestloop:
Включает или отключает использование вложенных циклов (Nested Loop Join).
Значение по умолчанию: on.

📌 enable_hashjoin:
Включает или отключает использование хэш-соединений (Hash Join).
Значение по умолчанию: on.

📌 enable_mergejoin:
Включает или отключает использование соединений слиянием (Merge Join).
Значение по умолчанию: on.

📌 enable_indexscan:
Включает или отключает использование сканирования по индексу (Index Scan).
Значение по умолчанию: on.

📌 enable_seqscan:
Включает или отключает использование последовательного сканирования (Sequential Scan).
Значение по умолчанию: on.

📌 enable_bitmapscan:
Включает или отключает использование сканирования по битовой карте (Bitmap Scan).
Значение по умолчанию: on.

📌 enable_tidscan:
Включает или отключает использование сканирования по TID (TID Scan).
Значение по умолчанию: on.

📌 enable_sort:
Включает или отключает использование сортировки.
Значение по умолчанию: on.

📌 enable_material:
Включает или отключает использование материализации (Materialization).
Значение по умолчанию: on.

📌 enable_partitionwise_join:
Включает или отключает использование поэтапного соединения для секционированных таблиц.
Значение по умолчанию: off.

📌 enable_partitionwise_aggregate:
Включает или отключает использование поэтапной агрегации для секционированных таблиц.
Значение по умолчанию: off.

Эти параметры можно устанавливать на уровне сессии или на уровне всей системы.

На уровне сессии:

SET enable_nestloop = off;
SET enable_hashjoin = off;


На уровне системы (через конфигурационный файл postgresql.conf):

enable_nestloop = off
enable_hashjoin = off;

Ну и еще пара предупреждений по поводу использования этих параметров:

⚠️ Отключение определенных методов выполнения может привести к ухудшению производительности, если PostgreSQL не сможет выбрать оптимальный план выполнения;
⚠️ Эти параметры обычно используются для тестирования и отладки, а не для повседневной работы;
⚠️ Не рекомендуется изменять эти параметры на уровне системы без тщательного тестирования.

Используйте эти параметры с осторожностью, чтобы не нарушить работу оптимизатора запросов.

На этом все! До связи!

#pgsettings