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

🔬 EXCLUDE ограничения в PostgreSQL (и не только).

В комментариях к заметке об ограничениях PostgreSQL подписчик напомнил еще и про EXCLUDE ограничения. Давайте разберемся что это такое и посмотрим еще на кое-что 😊.

EXCLUDE (исключающие) ограничения могут нам помощь тогда, когда нам нужно отслеживать какие-то пересекающиеся диапазоны данных. Т.е., например, если у нас есть база данных бронирования номеров отелей, то мы не хотим, чтобы один и тот же номер могли забронировать на одну и ту же дату разные люди. Или если мы занимаемся сдачей машин в аренду, то мы не хотим чтобы одну и туже машину взяли в аренду разные люди в одно и тоже время ну т.д. Справится с такими ситуациями нам и помогут EXCLUDE ограничения.

Для установки таких ограничений используется оператор EXCLUDE. Этот оператор часто используется с индексом типа GiST или SP-GiST, чтобы обеспечить эффективность выполнения запросов, хотя может применяться и вместе с обычным B-Tree индексом.

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

CREATE TABLE events (
id serial primary key,
event_time tstzrange,
CONSTRAINT no_time_overlap EXCLUDE USING gist (event_time WITH && )
);

Здесь:

event_time - время начала события (сеанса);

no_time_overlap - название нашего ограничения;

EXCLUDE USING gist - установка исключающего ограничения с использованием индекса Gist;

event_time WITH && - эта конструкция как раз и будет проверять при вставке или обновлении строки в таблицу, не пересекаются ли ее значения с уже имеющимися. Здесь && - оператор, который и проверяет пересечение диапазонов, т.е. возвращает истину если они пересекаются и ложь, если нет.

DEFERRABLE (отложенные) ограничения.

Фактически это не отдельный вид ограничений, а возможность отложить их проверку во время выполнения транзакции. Ограничения будут проверены не сразу, а в момент завершения транзакции (COMMIT). Ограничения CHECK и NOT NULL нельзя отложить.

В PostgreSQL уровень всех ограничений по умолчанию — NOT DEFERRABLE. Мы можем сделать ограничение отложенным сразу при его создании с помощью команды DEFERRABLE INITIALLY IMMEDIATE, или же временно сделать отложенным уже существующее ограничение в таблице с помощью вот такой команды:

SET CONSTRAINTS constraint_name DEFERRED;

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

Как видите, в PostgreSQL есть очень интересные фишки, которые могут пригодиться в той или иной ситуации.

На сегодня все! До связи!

#pgbase

PGConf.Russia 2025 — открыта регистрация и прием докладов на крупнейшую конференцию по PostgreSQL

31 марта — 1 апреля пройдет встреча сообщества PostgreSQL в Москве, организатор — крупнейший СУБД-разработчик Postgres Professional.

Выступите с докладом на PGConf.Russia 2025: поделитесь опытом, расскажите о своих разработках, получите обратную связь от экспертов по открытой СУБД.

PGConf.Russia 2025 — это:

✔️1500+ DBA, архитекторов, разработчиков ПО и ИТ-менеджеров

✔️10+ демонстраций разработок в PostgreSQL и решений на базе Postgres Pro

✔️40+ докладов про работу с PostgreSQL, отказоустойчивость и масштабируемость в СУБД, миграцию, возможности систем. Также пройдут мастер-классы по работе с СУБД

📍Встреча пройдет онлайн и офлайн: Центр Международной Торговли в Москве. Подайте тему и тезисы доклада до 23 февраля.

Регистрация на PGConf.Russia 2025

Реклама. ООО "ППГ" ИНН: 7729445882 Erid:2VtzqwWjqgH

#pgmeme

🔬 Ограничения целостности (Contraints) в PostgreSQL.

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

В этой заметке посмотрим какие ограничения есть в PostgreSQL.

1️⃣ Ограничение NOT NULL.

Это ограничение позволяет нам удостовериться, что в значениях колонки не будет NULL (отсутствие значения). Устанавливается так:

CREATE TABLE table_name (
col1 INTEGER,
col2 INTEGER NOT NULL);

Следует помнить, что бывают ситуации, когда пользователю все же нужно оставлять возможность не вводить данные в какое-то поле. Поэтому это ограничение тоже надо использовать с умом. В нашем случае, если оставить колонку col2 незаполненной, то получим вот такую ошибку:

ERROR: ExecAppend: Fail to add null value in not null attribute col2

2️⃣ Ограничение уникальности (UNIQUE).

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

CREATE TABLE table_name (
col1 INTEGER,
col2 INTEGER UNIQUE);

Следует помнить, что ограничение UNIQUE позволяет вставить NULL значения в колонку.

Мы можем установить это ограничение не для одной колонки, а для их комбинации, например вот так:

CREATE TABLE table_name (
col1 INTEGER,
col2 INTEGER
UNIQUE (col1, col2) );

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

3️⃣ Ограничения первичного ключа (PRIMARY KEY).

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

CREATE TABLE table_name
(col1 INTEGER PRIMARY KEY);

Как и в случае с UNIQUE, мы можем повесить первичный ключ на комбинацию колонок и получим так называемый составной первичный ключ.

4️⃣ Ограничение внешнего ключа (FOREIGN KEY).

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

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

Пример:

CREATE TABLE table_name1
(col1 INTEGER REFERENCES table_name2);

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

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

5️⃣ CHECK (проверка).

CHECK устанавливает ограничение на значение колонки. Такие ограничения как раз и позволяют избежать нам отрицательных или очень низких цен, или, например возраста человека больше 100 лет.

Пример:

CREATE TABLE table_name1 (
age INTEGER CHECK (age <= 100));

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

До связи!

#pgbase

PostgreSQL 17: архитектура и тюнинг SQL-запросов

Погрузись в архитектуру и прокачай оптимизацию запросов одной из самых популярных open source СУБД – PostgreSQL.

🌐 В программе курса:

🤩 Разберем, как работают СУБД вообще и PostgreSQL в частности: что такое MVCC, ACID, WAL, LRU, PPC/TPC и другие фундаментальные понятия архитектуры баз данных
🤩 Получите свой собственный выделенный облачный PostgreSQL-сервер (8 vCPU, 12G RAM, 100G NVMe) – БЕСПЛАТНО на время обучения предоставляется
🤩 Получите теорию и практику EXPLAIN и EXPLAIN ANALYZE на разных типа запросов
🤩 Изучите архитектуру хранения данных в PostgreSQL, типы и особенности индексов, а также получите полезные советы и трюки оптимизации БД

🗓 Старт курса: 6 марта. Продолжительность: 5 недель обучения (четверг, 18:00 МСК).

Изучить программу и записаться можно здесь.

🤩Кто мы: R&D-центр Devhands, основатель школы Алексей Рыбак.
Автор курса — Николай Ихалайнен, эксперт по СУБД (ex-Percona), со-основатель MyDB, энтузиаст открытого ПО.

Реклама. ИП Рыбак А.А. ИНН 771407709607 Erid:2VtzqxUmfoJ

🛠️ LOGIN trigger в PostgreSQL 17.

В PostgreSQL 17 появилась фишка, которую многие бывшие пользователи БД Oracle очень ждали - LOGIN trigger (В Oracle он называется LOGON trigger).

Как понятно из названия этой фишки - это триггер, который позволяет нам отследить событие входа пользователя в базу данных.

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

CREATE TABLE user_login (
id serial,
time timestamptz DEFAULT now(),
name text
);

LOGIN trigger будет работать не на уровне всего кластера PostgreSQL, а на уровне базы данных.

Как и при использовании любого другого триггера в PostgreSQL, нам нужно сначала написать триггерную функцию:

BEGIN;
CREATE OR REPLACE FUNCTION on_login_proc()
RETURNS event_trigger AS
$$
BEGIN
INSERT INTO user_login (name)
VALUES (SESSION_USER);
END;
$$ LANGUAGE plpgsql;

Далее пишем триггер, который будет вызывать нашу функцию по событию login. Это и есть новая фишка PostgreSQL 17:

EVENT TRIGGER on_login_event
ON login
EXECUTE FUNCTION on_login_proc();

Ну и наконец включаем триггер:

ALTER EVENT TRIGGER on_login_event ENABLE ALWAYS;
COMMIT;

Собственно и все! 😊 Теперь в нашу таблицу будут писаться данные по входу пользователей в базу данных. Можете проверить просто подключившись своим пользователем к базе данных через psql.

Теперь о минусах такого решения. Что случиться, если мы где-то ошибемся в триггерной функции и она не сработает?

В таком случае все пользователи БД начнут получать примерно вот такую ошибку:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
"Ошибка_в_функции"

И как же залогиниться в базу в таком случае? Единственным вариантом остается остановить экземпляр PostgreSQL и запустить его в single mode, и уже там поправить или отключить функцию.

Как видите, новая фишка PostgreSQL 17 LOGIN trigger может быть очень полезна в целях обеспечения безопасности вашей БД. Но пользоваться ей надо с осторожностью, досконально тестировать вашу триггерную функцию во избежание ошибок, иначе вы рискуете парализовать работу пользователей 🤕.

#queries #pg/plsql

#pgmeme

🤖 Управляем фоновыми процессами PostgreSQL (расширение pg_worker_pool).

pg_worker_pool - простое расширение, которое позволит нам более гранулированно управлять фоновыми процессами PostgreSQL, создавая так называемый пулы.

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

➡️ https://github.com/serpent7776/pg_worker_pool/

Зачем нам это может понадобиться? Например, если мы хотим выделить какому-то конкретному запросу определённое количество фоновых процессов, чтобы ускорить его выполнение. Или же мы захотим назначит разные задачи на разные фоновые процессы для параллельного их выполнения.

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

Установка расширения достаточно простая. Нужно как обычно склонировать репозиторий и собрать расширение:

# git clone https://github.com/serpent7776/pg_worker_pool.git

# cd pg_worker_pool

# make

# sudo make install

Далее подключаемся к нужной базе и выполняем команду:

CREATE EXTENSION pg_worker_pool;

Также нужно прописать расширение в параметр shared_preload_libraries.

Количество фоновых процессов жестко закодировано в расширении и равно 8. Чтобы это изменить, можно поменять значение переменной MAX_WORKERS в исходниках расширения pg_worker_pool.c и пересобрать его. Тут надо помнить, что PostgreSQL не даст создать большее количество процессов, чем указано в параметре max_worker_processes, по этому нужно не забыть поменять и этот параметр.

После установки расширения создаст схему с именем worker_pool. Также нам станет доступно несколько функций для назначения процессов определенной задаче и выделения их в пул:

📌 worker_pool.submit - назначает запрос на выполнение определенному процессу (ам). Процесс запустится в конце запроса, если тот будет выполнен (COMMIT), если запрос завершиться неудачей, то процесс не будет запущен. Если вы запустите несколько процессов в одной транзакции, то порядок их работы ни где не задается. Выглядеть это может примерно так:

CALL worker_pool.submit('worker1', 'CREATE INDEX myindex_1 on my_table (id)');

CALL worker_pool.submit('worker2', 'CREATE INDEX myindex_2 on my_table (name)');

Здесь мы создали два процесса worker1 и worker2 и запустили на первом создание индекса по колонке id, а на втором по колонке name.

📌 worker_pool.launch - используется для запуска процесса без указания запроса, который будет с помощью него выполняться;

📌 worker_pool.jobs - хранит историю выполнения запросов процессами. Посмотреть можно так:

SELECT * FROM worker_pool.jobs ORDER BY id DESC;

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

До связи!

#pgext

#pgmeme Случайно удалили вчерашний пост 😊

👨‍🎓 Обобщенные табличные выражения (Common Table Expressions, CTE) в PostgreSQL.

Думаю для многих из вас CTE не является чем-то новым. Эта заметка для тех, кто еще не знаком с этой полезной фишкой PostgreSQL. Надо сказать, что CTE это ни какая-то эксклюзивная фишка PostgreSQL, обобщенные табличные выражения используются во многих СУБД.

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

Давайте разбираться как это работает. Синтаксис CTE выглядит следующим образом:

WITH cte_name AS (
SELECT column1, column2, ..., columnN
FROM table_name
WHERE cte_condition
)
SELECT *
FROM cte_name
WHERE query_condition;

Здесь:

WITH ... AS (- и есть сам CTE, заключенный в круглые скобки;

cte_name - имя табличного выражения, к которому мы в дальнейшем можем обращаться в основном запросе как к обычной таблице;

cte_condition - условие запроса CTE.

Далее у нас идет текст основного запроса, который уже обращается к виртуальной таблице CTE.

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

Можно использовать несколько CTE за один раз и объединять их результаты. Например, синтаксис модет быть таким:

WITH
cte_name_1 (optional_column_names) AS (cte_select_query_1),
cte_name_2 (optional_column_names) AS (cte_select_query_2)
SELECT * FROM cte_name_1
UNION
SELECT * FROM cte_name_2;

Мы также можем использовать CTE рекурсивно, с помощью ключевого слова RECURSIVE, синтаксис будет таким:

WITH RECURSIVE cte_name [(cte_column_list)] AS (
cte_definition)
текст_основного_запроса;

Преимущества обобщенных табличных выражений:

📌 Ускоряют запрос. При грамотном применении CTE, ваш запрос может сильно ускориться, так как данные основного запроса будут получены из кэша.

📌 Упрощение структуры запроса. С помощью CTE можно сделать текст запроса более понятным и простым, разбив сложный запрос на несколько более маленьких запросов.

📌 Облегчают написание запросов. Некоторые сложные задачи по получению данных гораздо проще решить с помощью CTE (а иногда и только с помощью CTE), чем писать один большой запрос.

Но не все так радужно. У CTE есть и недостатки:

📌 Не универсальны. В некоторых задачах CTE могут оказаться слишком ресурсоемкими, так как хранят результаты промежуточного запроса в памяти. Памяти может просто не хватить, например.

📌 Особенности оптимизации. Оптимизатор PostgreSQL не так хорошо оптимизирует внутреннее выражение CTE, заключенное в конструкцию WITH ... AS, как обычный запрос.

Несмотря на некоторые недостатки, CTE является мощным инструментом, который может помочь решить сложные задачи и ускорить ваш запрос. Главное не применять CTE необдуманно 😏.

Это самая база CTE. В будущих постах будем более подробно рассматривать работу с обобщенными табличными выражениями.

До связи!

#queries #pgbase

🤖 Хранение векторных данных в PostgreSQL (часть 2, установка).

Продолжаем разбираться с Pgvector в PostgreSQL! В этой заметке установим расширение и попробуем создать таблицу с векторными данными и что-нибудь вставить в нее. Поехали!

Расширение будем собирать из исходников, по этому нам понадобиться утилита make. Как правило она уже установлена в системе Linux, если у вас нет, то ставим:

# sudo apt install make

Далее нам необходимо создать временную папку для исходников расширения и склонировать в нее ее репозиторий с GitHub. Создание папку:

# sudo mkdir tmp

Переходим в нее и клонируем репозиторий:

# cd /tmp && git clone https://github.com/pgvector/pgvector.git

Далее переходим в склонированную папку и запускаем сборку и установку расширения:

# cd pgvector && make && make install

Под Windows расширение тоже можно собрать. Как это сделать можно почитать в официальной документации расширения:

➡️ https://github.com/pgvector/pgvector

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

CREATE EXTENSION pgvector;

Теперь можем создать таблицу с векторным представлением, например вот так:

CREATE TABLE vectors
(id bigserial PRIMARY KEY,
embedding vector(3));

Здесь мы создали таблицу с векторным представлением с 3-мя измерениями.

Мы также можем добавить колонку с векторными данными в существующую таблицу:

ALTER TABLE my_table
ADD COLUMN
embedding vector(3);

Теперь вставим данные:

INSERT INTO vectors
(embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

А так мы можем получить ближайшие друг от друга вектора с помощью формулы евклидового расстояния (L2 distance):

SELECT * FROM vectors ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Как видите, Pgvector позволяет нам работать с векторными представлениями как с простыми данными, в чем и заключается мощь данного расширения. Это самая база по хранению и работе с векторными данными в PostgreSQL. В будущих постах будем прокачивать наши навыки, если, конечно данная тема вам интересна 😏.

До связи!

#pgext

#pgmeme

🤖 Хранение векторных данных в PostgreSQL (часть 1, теория).

Как часто говорят: "Зачем я это учил в школе, если в жизни не пригодилось?" Но с векторами ситуация обратная. Мы их изучали в 9 классе на уроке геометрии, а сейчас они играют ключевую роль в развитии искусственного интеллекта, языковых моделей, машинном обучении и вот этом всем 😊.

Давайте разбираться как так получилось и причём тут PostgreSQL. Для начала вспомним что это вообще такое.

Вектор — это направленный отрезок, который характеризуется величиной (длиной) и направлением. Но это только верхушка айсберга, есть так называемые Векторные представления (Vector embeddings) и вот они то и играют ключевую роль в современных языковых моделях, таких как ChatGPT.

С помощью этой технологии мы можем преобразовать любую информацию в массив чисел с плавающей точкой. Ключевой особенность этого массива будет то, что он будет иметь одинаковую длину в не зависимости от того какую вы информацию в него поместили. Длина зависит от того, какой моделью вы пользуетесь. Соответственно, массив может иметь длину 300, 1000 или 1536 чисел.

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

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

Преимуществами pgvector являются:

📌 Хранение векторных представлений вместе в одной базе с обычными данными в PostgreSQL.

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

📌 Поддерживает различные типы векторов, такие как: single-precision, half-precision, binary, и sparse, а также различные методы поиска схожести, такие как: L2 distance, inner product, cosine distance, L1 distance, Hamming distance, и Jaccard distance.

📌 Поддерживает все фишки PostgreSQL как реляционной базы данных, такие как: консистентность данных и транзакционность.

📌 Поддержка SQL запросов. Вы можете извлекать данные векторных представлений с помощью обычных SQL запросов.

📌 Лёгкая интеграция с PostgreSQL. Pgvector ставится как обычное расширение.

Плюс, Pgvector умеет индексировать векторные представления. Для этого у него есть два типа индексов:

✅ HNSW - строит многослойный граф. Этот индекс более быстрый для поиска по векторным представлениям, но более медленный при его установке, так как использует много памяти.

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

На этом теоретическую часть по векторам в PostgreSQL заканчиваем. В будущих постах установим Pgvector и научимся им пользоваться.

До связи!

#pgext

🔬 Primary keys VS unique constraints в PostgreSQL.

Всем привет! В заметке разберемся чем первое отличается от второго и почему второе лучше не использовать в качестве первого 😁.

Начнем с Primary key (Первичный ключ). Это очень важное понятие в PostgreSQL, да и в мире реляционных баз данных в целом. Как правило, если вы создаете таблицу, то ваш SQL запрос начинается с чего-то такого:

CREATE TABLE my_table (
id INTEGER PRIMARY KEY,
...
);

И это очень правильно 😊. Преимуществами Primary key являются:

1️⃣ Самое, пожалуй важное - это обеспечение уникальности строк в таблице. Это даст нам гарантию того, что строки в таблице не будут иметь одинаковый Primary key. Плюс Primary key позаботиться о том, что значения первичного ключа не будут содержать NULL. Эта комбинация обеспечит нам консистентность наших данных и гарантию их уникальности.

2️⃣ Автоматическое индексирование. PostgreSQL автоматически создаст индекс на колонке, содержащей первичный ключ, что в свою очередь обеспечит более быстрое выполнение запросов к таблице.

3️⃣ Логическая репликация. Primary key играет ключевую роль в логической репликации, так как именно по нему PostgreSQL отслеживает и синхронизирует строки между базами данных.

4️⃣ Ссылочная целостность. Первичные ключи используются для ограничений в связанных таблицах по внешним ключам. Это нам обеспечит целостность данных в связанных таблицах и их согласованность.

Теперь переходим к Unique constraint (Ограничение уникальности). Собственно, оно тоже обеспечит нам уникальность данных в таблице, но с одним нюансом - Unique constraint пропустит NULL значение. Т.е. если вы вместо первичного ключа использовали вот такую конструкцию при создании таблицы:

CREATE TABLE my_table (
id INTEGER,
...,
UNIQUE (id)
);

Готовьтесь с сложностям и проблемам 😅. Давайте разбираться. К чему может привести отсутствие ограничений на NULL значения в Unique constraint в долгосрочной перспективе?

Например, из-за какой-то технической ошибки или человеческого фактора в колонку id вставиться пустое значение. PostgreSQL в этом случае не выдаст ни какую ошибку. Это в свою очередь приведет в дальнейшем к дублированию записей, так как PostgreSQL будет считать значения NULL уникальными. Что в дальнейшем приведет к замедлению запросов и неоднозначным их результатам, если в условии будет использоваться колонка с Unique constraint.

Мы, конечно можем обойти это поведение Unique constraint, добавив еще ограничение NULL значений условием NOT NULL, или использовать для генерации id последовательность. Но зачем? Проще и надежней использовать Primary key.

Плюс , если вы используете логическую репликацию, то Unique constraint не будет использоваться автоматически PostgreSQL в качестве идентификаторов для реплики, вам придется их указывать вручную.

Вывод напрашивается сам собой: в любой непонятной ситуации используйте Primary key, если, конечно у вас нет каких-то специальных требований приложения на использование Unique constraint. Но, мне даже трудно придумать, что это могут быть за требования 😊.

#pgbase

#pgmeme

📚 Инкрементальные резервное копирование в PostgreSQL 17 (практика).

Надеемся вы уже успели отойти от долгих праздников и входите потихоньку в рабочий ритм 😏. В этой заметке посмотрим на практике как работает новая фишка PostgreSQL 17 - инкреметное резервное копирование. Про эту фишку мы уже писали в обзоре новинок PostgreSQL 17.

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

Для начала нам нужно включить WAL Summarization - это специальный процесс, который в дальнейшем нам будет суммировать инкрементальные бэкапы. По умолчанию этот процесс отключен. Включаем:

ALTER SYSTEM SET summarize_wal = 'on';

Для того, чтобы изменения применились, нам достаточно перечитать конфигурацию:

SELECT pg_reload_conf();

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

# pg_basebackup -U имя_пользователя -D путь_к_папке_для_копий

Соответсвенно, реквизиты подключения к кластеру PostgreSQL у вас будут свои.

После этого можем делать уже инкрементные бэкапы:

pg_basebackup -U имя_пользователя
--incremental=
путь_к_папке_с_полным_бэкапом/backup_manifest -D путь_к_папке_с_копиями

Здесь backup_manifest - это файл манифеста первой нашей полной копии. Путь к папке с бэкапами для полной копии и инкрементных копий может быть разным. Главное, при создании папок для резервных копий не забыть выставить на них нужные права для пользователя от имени которого вы делаете резервное копирование.

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

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

# pg_combinebackup -o путь_к_папке_для_восстановления
путь_к_папке_с_полным_бэкапом
путь_к_папке_с_бэкапом_среды
путь_к_папке_с_бэкапом_вторника

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

Переходим в папку с восстановленными файлами:

# cd путь_к_папке

Меняем порт в файле postgresql.conf:

# echo "port=7777" >> postgresql.conf

Запускаем новый кластер:

# pg_ctl путь_к_папке start

Собственно осталось проверить базы и заменить файлы в рабочем кластере на восстановленые, если все хорошо.

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

До связи!

#pgbackup

#pgmeme

Channel name was changed to «Postgres Guru | Базы данных🎄»

⚖️ Конвертируем физическую репликацию в логическую в PostgreSQL 17.

В прошлой заметке мы упомянули утилиту pg_createsubscriber в PostgreSQL 17. Эта утилита призвана упростить процесс конвертации физической репликации в логическую. Давайте посмотрим действительно ли упростила, или лучше по старинке 😊.

Чтобы все получилось, главный сервер и сервер реплики должны отвечать следующим требованиям:

📌 Мажорная версия PostgreSQL на обоих серверах должна быть точно такой же как и у утилиты pg_createsubscriber;

📌 Директория data на обоих серверах должна иметь одинаковый системный идентификатор;

📌 Пользователь базы данных на сервере реплике, указанный для каталога data должен иметь права на запуск репликации и использование функции pg_replication_origin_advance();

📌 На главном сервере параметр wal_level должен быть в значении logical. А также значение параметра max_replication_slots должно быть больше или равно сумме баз данных, участвующих в репликации и существующих слотов репликации, а значение параметра max_wal_senders должно быть больше или равно сумме указанных баз данных и существующих процессов передачи WAL;

📌 Главный сервер не должен находиться в режиме восстановления;

📌 На сервере реплике значение параметров конфигурации max_replication_slots и max_logical_replication_workers должно быть больше или равно числу баз данных, участвующих в репликации, а значение параметра max_worker_processes должно быть больше этого числа;

📌 Ну и конечно оба сервера должны быть доступны друг другу по сети.

Поехали!

Проверяем версии PostgreSQL и идентификатор каталога data на обоих серверах (на всякий случай) с помощью следующих запросов:

SELECT version();

и

SELECT system_identifier FROM pg_control_system();

Утилиту pg_createsubscriber нужно запускать на целевом сервере, т.е. на сервере реплике. Останавливаем службу PostgreSQL на сервере реплике:

# pg_ctl -D путь_к_папке_data stop

Запускаем утилиту:

# pg_createsubscriber
--database=имя_базы
--pgdata=путь_к_папке_data
--subscriber-port=5432
--publisher-server='user=имя_пользователя password=пароль host=IP_главного_сервера'
--subscriber-username=имя_пользователя
--publication=имя_публикации
--subscription=имя_подписки
--dry-run

Думаю, по параметрам здесь все понятно. Последний параметр --dry-run запустить утилиту в тестовом режиме, при котором утилита проверит возможность конвертации, но фактически ничего не сделает. Рекомендуем при первом запуске использовать этот параметр. Так как если что-то пойдет не так, то вам скорее всего придется заново делать физическую реплику.

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

SELECT * FROM pg_publication;

и

SELECT slot_name,slot_type FROM pg_replication_slots;

А также проверить на реплике, что подписка активна:

SELECT * FROM pg_subscription;

Как видите, в PostgreSQL 17 теперь можно провести конвертацию между физической и логической репликацией с помощью одной команды. Не нужно отслеживать LSN, повышать реплику до автономного сервера и т.д., утилита pg_createsubscriber все сделает за нас.

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

#pgsettings #pghi

⚖️ Конвертируем физическую репликацию в логическую в PostgreSQL.

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

Прежде чем начать конвертацию, нужно убедиться, что на вашем главном сервере выполнены следующие требования:

1️⃣ Параметр wal_level установлен в значение logical;

2️⃣ Установлено нужное количество max_replication_slots и max_wal_senders;

3️⃣ Нет лага в физической репликации.

Чтобы проверить, что ваша реплика не отстает от главного сервера, можно воспользоваться следующим запросом:

SELECT usename,client_addr,state,replay_lag FROM
pg_stat_replication;

Переходим к конвертации. Создаём слот для логической репликации на главном сервере:

SELECT pg_create_logical_replication_slot('logical_replica_slot', 'pgoutput');

Проверяем, что слот создался:

SELECT * FROM pg_replication_slots;

Создаем публикацию на главном сервере для всех таблиц:

CREATE PUBLICATION my_pub FOR ALL TABLES;

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

SELECT pg_promote();

Проверяем, что все получилось:

SELECT pg_is_in_recovery();

В выводе должно быть так:

pg_is_in_recovery
-------------------
f

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

redo done at номер_LSN

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

SELECT pg_replication_slot_advance('logical_replica_slot', 'полученный_с_реплики_номер_LSN);

Теперь создаем на реплике подписку для нашей, созданной на главном сервере публикации:

CREATE SUBSCRIPTION my_sub CONNECTION 'user=имя_пользователя password=пароль host=<host_ip> port=5432' PUBLICATION my_pub WITH (copy_data=false, slot_name='logical_replica_slot', create_slot=false);

Здесь важно не забыть установить параметр copy_data=false, так как нам не нужно копировать все таблицы на реплику, а нужно начать репликацию с конкретного LSN. После этого у нас начнется логическая репликация, в чем можно убедиться, введя следующий запрос:

SELECT * FROM pg_stat_replication;

Осталось удалить физический слот репликации с основного сервера. Это нужно сделать обязательно, чтобы избежать переполнения диска на главном сервере накопленными сегментами WAL:

SELECT pg_drop_replication_slot('physical_replica_slot');

Вот и все! В PostgreSQL 17 процесс конвертации между физической и логической репликацией упростили с помощью утилиты pg_createsubscriber. Но об этом мы поговорим в будущих постах.

С наступающими праздниками! 🎄

#pgsettings #pghi

#pgmeme

🤖 Аудит таблицы в PostgreSQL с помощью триггера.

Представим, что у вас появилась задача организовать аудит какой-то конкретной таблицы (в нашем примере это таблица workers) в PostgreSQL. Т.е. вам нужно узнавать кто и какие операции делал в таблице. Одним из способов решения данной задачи будет повесить на таблицу триггер, который при срабатывании определенных условий будет вызывать функцию, которая будет писать информацию об операциях в другую таблицу.

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

Для начала создадим таблицу, в которую будем писать, собираемую триггером информацию. Допустим, нас интересует имя пользователя, вид операции, которую он сделал (UPDATE, DELETE или INSERT) и время операции.

CREATE TABLE audit_user (
user_name TEXT,
action TEXT,
time TIMESTAMP
);

Теперь создаём триггерную функцию:

CREATE FUNCTION audit_func() RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_user(user_name, action, time)
    VALUES (current_user, TG_OP, now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Думаю по тексту функции все понятно. Мы просто вставляем в нашу табличку нужные значения. TG_OP - это переменная PostgreSQL, которая содержит тип выполняемой операции.

Теперь создаем сам тригер для вызова нашей функции:

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON workers
FOR EACH ROW EXECUTE FUNCTION audit_func();

Здесь, я думаю, тоже все понятно. Мы создаём триггер и говорим вызывать нашу функцию после операций INSERT, UPDATE и DELETE в таблице workers.

Мы можем более гранулированно собирать информацию. Если нас интересуют операции конкретного пользователя, то функцию можно переписать вот так, например:

CREATE OR REPLACE FUNCTION audit_func() RETURNS trigger AS $$
BEGIN
    IF current_user = 'buh' THEN
        INSERT INTO audit_user(user_name, action, timestamp)
        VALUES (current_user, TG_OP, now());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Т. е. здесь мы добавили условие по имени конкретного пользователя (buh).

К плюсам такого решения можно отнести его простоту и быстроту. А к минусам бОльшую нагрузку на базу. Нам нужно создавать отдельную таблицу, которая будет занимать место на диске, плюс повысится нагрузка на дисковую подсистему. Такой метод может не подойти, если аудируемая таблица очень большая и в нее очень интенсивно пишутся или обновляются данные.

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

#pl/pgsql

🤬 Классические ошибки сети в PostgreSQL.

В этой заметке разберем простые ошибки, которые может выдавать PostgreSQL, либо при ошибках в конфигурировании сети, либо при ошибках подключения к БД, или при наличии реальных проблем с сетевым соединением. Ошибки простые, но часто именно на таких ошибках "ломают зубы" даже опытные DBA. Так что у вас теперь будет шпаргалка по таким ошибкам 😊.

1️⃣ Sorry, too many clients already

Эта ошибка прям классическая классика 😁. Еще может сопровождаться сообщением “remaining connection slots are reserved for non-replication superuser connections”.

Причина появления ошибки - исчерпание лимита соединений, установленного параметром max_connections. Но, благодаря параметру superuser_reserved_connections суперпользователь все равно сможет подключиться. Значение по умолчанию superuser_reserved_connections равно 3.

Выходов из данной ситуации несколько:

📌 перезапустить службу PostgreSQL. Это самый быстрый способ, если ситуация критическая. Перезапуск сбросить все зависшие соединения, но и текущие прервет, что грозит потерей данных и пользовательским гневом;

📌 увеличить значение параметра max_connections. Это тоже не самый лучший вариант, так как следует помнить, что в PostgreSQL каждое соединение представляет собой отдельный процесс, которые потребляет ресурсы сервера;

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

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

2️⃣ No pg_hba.conf entry for host

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

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

3️⃣ Connection to Server on Socket Failed: No Such File or Directory

Такая ошибка возникает обычно, когда пытаешься подключиться к PostgreSQL через psql без указания имени хоста через параметр -h. В этом случае psql попытается подключиться через UNIX socket, а не по протоколу TCP/IP. Соответственно, ошибка говорит о том, что psql не смог найти socket.

Причин может быть несколько:

📌 Не запущенная служба PostgreSQL;

📌 Подключения через UNIX socket запрещены в pg_hba.conf;

📌 Вы используете psql версии отличной от версии PostgreSQL, к которой вы пытаетесь подключиться;

📌 Стандартный порт и параметр unix_socket_directories были изменены.

Если ваша служба PostgreSQL работает и в остальном тоже все в порядке, то проверяйте настройки порта и UNIX сокетов в файле postgresql.conf. Если они изменены, то вы можете попробовать подключиться к вашей PostgreSQL вот так:

psql -p <PORT> -h <UNIX_SOCKET_DIRECTORIES>

4️⃣ Connection to Server Failed: Connection Refused

Причины этой ошибки могут быть и в неисправности сети, но как правило такая ошибка появляется когда у вас в postgresql.conf в параметре listen_addresses указан какой-то другой IP адрес, а не тот, с которого вы подключаетесь.

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

5️⃣ Database "имя_базы" Does Not Exist

Ну здесь, я думаю все очевидно. Либо база данных действительно не существует, либо же у клиента ошибка в строке подключения к БД. Все что нужно - это проверить есть ли такая база в кластере PostgreSQL и правильность строки подключения.

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

#pgerrors

#pgmeme

🔩 Массив внешних ключей в PostgreSQL.

В комментариях к посту про массивы в PostgreSQL возник вопрос, можно ли сделать элементы массива внешними ключами, ссылающимися на другую таблицу? Да, можно! 😁

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

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

1️⃣ Создать таблицу с колонкой, которая будет использоваться в качестве первичного ключа;

2️⃣ Создать вторую таблицу с колонкой, которая будет использоваться в качестве внешнего ключа и ссылаться на колонку с первичным ключом первой таблицы;

3️⃣ Превратить колонку с внешним ключом второй таблицы в массив;

4️⃣ Наложить различные ограничения, типа NOT NULL на массив внешних ключей второй таблицы;

5️⃣ Ну и наконец, вставить данные.

Теперь давайте на практике:

1️⃣ Создаем первую таблицу:

CREATE TABLE table1 (
id integer PRIMARY KEY);

2️⃣ Создаем вторую таблицу:

CREATE TABLE table2 (
id integer PRIMARY KEY,
foreign_key_array integer[] NOT NULL);

3️⃣ Делаем массив внешних ключей во второй таблице со ссылкой на первую таблицу:

ALTER TABLE table2 ADD FOREIGN KEY (foreign_key_array) REFERENCES table1(id) ON DELETE CASCADE;

4️⃣ Ну и данные вставляем, например так:

INSERT INTO table2 (id, foreign_key_array) VALUES
(1, ARRAY[1, 2, 3]),
(2, ARRAY[4, 5, 6]);

Думаю по коду все понятно.

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

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

#pgqueries

🔬 Секция по умолчанию (Default Partition) в PostgreSQL.

Данный пост относится к теме секционирования таблиц в PostgreSQL, о котором мы уже писали. Давайте разбираться!

Предположим, что у нас есть секционированнная по диапазону дат таблица. И в какой-то "прекрасный" день приложение, работающее с БД сошло с ума и попыталось записать в нашу таблицу строку с датой на сто лет вперед, например. Что в этом случае произойдет? Ведь такая дата явно выходит за границы диапазона секционирования.

До версии PostgreSQL 11 СУБД выдаст в таком случае вот такую ошибку:

ERROR: no partition of relation "имя_таблицы" found for row
DETAIL: Partition key of the failing row contains (имя_колонки) = (значение);

И ваши данные просто не запишутся ни в какую секцию таблицы и потеряются. Чтобы избежать потери данных в таких ситуациях, в PostgreSQL 11 появились секции по умолчанию (Default Partition). В такую секцию будут попадать все данные, не подпадающие под условие секционирования.

Плюсы секции по умолчанию:

📌 Вы могли при создании секций таблицы по диапазону дат не предусмотреть будущие периоды, и новые данные могут просто потеряться;

📌 Секция по умолчанию может показать вам ошибки приложения, по типу той, что описана в начале поста.

Естественно, что секция по умолчанию не будет работать с секционированной по хэшу таблицей.

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

Создать секцию по умолчанию очень просто:

CREATE TABLE имя_секции(таблицы)_по_умолчанию PARTITION OF имя_основной_таблицы DEFAULT;

Теперь, предположим, что мы не предусмотрели будущую секцию и данные попали в секцию по умолчанию. Мы решили быстренько исправить эту оплошность и создать недостающую секцию таблицы.... и обломались 😮‍💨. PostgreSQL не даст нам создать секцию, если данные, подходящие под условие секционирования лежат в секции по умолчанию. PostgreSQL выдаст нам вот такую ошибку при создании новой секции в таком случае:

ERROR: updated partition constraint for default partition "имя_секционированной_таблицы" would be violated by some row

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

Для автоматизации всей этой работы можно использовать расширение типо pg_partman, но об этом мы поговорим в будущих постах. До связи!

#pgbase

🤖 Использование массивов в PostgreSQL.

Наша любимая СУБД на все руки мастер. Собственно, за это ее и любим 😻. Давайте разберёмся какие преимущества нам дает возможность использовать массивы в PostgreSQL, и как это все работает.

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

При использовании массивов в PostgreSQL есть несколько правил:

📌 Первый элемент массива в PostgreSQL считается с 1, а не с 0, как во многих языках программирования, например;

📌 Массивы объявляются в колонке при создании таблицы с помощью квадратных скобок [ ] или ключевого слова ARRAY для одномерных массивов;

📌 Массив в PostgreSQL можно создать с разными типами данных: число, текст, дата и даже другой массив (такой массив будет называться многомерным);

📌 PostgreSQL поддерживает ряд функций и операторов для доступа к данным массива, а также позволяет их индексировать, нарезать один массив на более мелкие и делать поиск по массивам.

Посмотрим как это выглядит на практике. Продолжая наш пример, создадим таблицу с массивом:

CREATE TABLE workers
(worker_id serial PRIMARY KEY,
worker_name text,
worker_phones integer[ ] );

Для объявления одномерных массивов можно использовать альтернативную запись ARRAY. Например вот так:

CREATE TABLE workers
(worker_id serial PRIMARY KEY,
worker_name text,
worker_phones integer ARRAY);

Мы также можем указывать размерность массива, вот так

worker_phones integer[3];

или так:

worker_phones integer ARRAY[3];

Однако, при текущей реализации работы с массивами в PostgreSQL указание размерности массива не имеет ни какого смысла 🤷 Размер массива все равно будет считаться неопределенным.

Чтобы вставить значения в массив, нужно использовать вот такую конструкцию:

INSERT INTO workers (worker_phones) VALUES ('{"84951234567", "84956145238", "84998345361"}');

Это мы вставили отдномерный массив. Если нужен многомерный (например еще мобильные телефоны), то делаем так:

INSERT INTO workers (worker_phones) VALUES ('{"84951234567", "84956145238", "84998345361"}'), ('{"89038532564", "9265374589);

Обратиться к элементу массива можно по его номеру (помним, что в PostgreSQL нумерация элементов массива начинается с 1):

SELECT worker_phones [3] FROM workers;

С телефонами может не очень удачный пример, но идея думаю понятна. Вы также можете обращаться к массивами по диапазонам элементов, вот так [1:3]. Получим три элемента массива с первого по третий. Если не указать верхнюю или нижнюю границу диапазона массива, например вот так [:3], или наоборот вот так [3:], то PostgreSQL возьмет нижний, или верхний предел границы массива.

Соответсвенно, таким же образом вы можете удалять (DELETE) элементы массива и обновлять (UPDATE) их.

На этом пока что про массивы все. Это вводный пост, там много еще интересного есть 😉 До связи!

#pgbase

#pgmeme

🤬 Служба PostgreSQL не запускается, консоль и логи молчат.

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

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

Ниже описанная ситуация касается операционной системы Linux. Первое, что стоит попробовать, это запустить PostgreSQL в режиме single mode. В таком режиме запускается один процес PostgreSQL без каких-либо фоновых процессов типо Autovacuum, и не дает ни кому подключиться. Таким образом мы можем понять, является ли какой-то фоновый процесс PostgreSQL причиной невозможности запуска ее основной службы.

Запустить PostgreSQL в режиме single mode можно так:

# bin/postgres --single -D путь_к_папке_data

Если PostgreSQL в таком режиме запустилась, то надо разбираться с настройками памяти фоновых процессов, может вы где-то накосячили и ОС не может выделить необходимый объем памяти какому-то фоновому процессу. Если же служба не запустилась даже в таком режиме и выдала в консоль что-то типо такого:

Bus Error(Core Dumped)

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

Для этого создадим пустой файл:

# touch emptyconf

И передадим его PostgreSQL при запуске в single mode:

# bin/postgres --single -D data --config-file=emptyconf

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

Если и это не помогло, то пробуем запустить PostgreSQL в single mode с аргументом от 1 до 5. Это аргументы, отвечающие за вывод отладочной информации. Соответсвенно, чем выше аргумент, тем больше информации. Запускается так:

# bin/postgres --single -D путь_к_папке_data -d 5

Если опять увидели сообщение типо такого:

DEBUG: IpcMemoryCreate(size=1519356162)
Bus Error(Core Dumped)

то тут уже точно, что-то с памятью. Первое, что нужно попробовать сделать в таком случае, это проверить работу huge pages. Про них мы уже писали. По умолчанию параметр huge_pages=try, т.е. PostgreSQL пытается использовать huge pages, а если не получается, то не использует. Но если у вас этот параметр стоит в значении on, то PostgreSQL будет принудительно использовать огромные страницы, а если не получиться, падать с ошибкой.

Мы можем принудительно отключить PostgreSQL работу с huge pages, поставив этот параметр в значение off. И попробовать запустить службу еще раз.

В версии ядра Linux 4.18 был глюк, связанный с работой huge pages. Так что это может быть ваш случай, если вы давно не обновляли свою ОС. В этом случае поможет обновление версии ядра ваше ОС.

В любом случае, при столкновении с нестандартной ситуацией в работе PostgreSQL, не стоит паниковать и отчаиваться! 😉

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

#pgerror

#pgmeme

🔬 Операторы классов в PostgreSQL.

Раз уж у нас сегодня реклама про индексы, то и продолжим про них разговаривать 😊. Поговорим о такой штуке, как операторы классов. Эти операторы могут быть полезным инструментом для целей оптимизации производительности работы индексов, но не все про них знают. Будем это исправлять!

Фактически, операторы классов представляют собой набор правил, по которым индекс сравнивает и сортирует определенный тип данных. Также они могут содержать какой-то минимальный набор функций для работы с типом данных. Класс всегда входит в какое-либо семейство операторов. При этом в одно общее семейство могут входить несколько классов, если они имеют одинаковый вид. Например, семейство integer_ops включает классы int8_ops, int4_ops и int2_ops для типов данных bigint, integer и smallint соответственно.

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

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

CREATE INDEX idx_username ON users (username text_pattern_ops);

Здесь text_pattern_ops и является названием оператора класса с регистронезависимым поиском. Что нам это даст? При помощи такой оптимизации мы избежим накладных расходов на поиск с учетом регистра, так как очевидно, что такой поиск более затратный по производительности, чем поиск независимый от регистра. Особенно это будет заметно на больших таблицах с миллионами записей.

Посмотреть все существующие операторы классов можно с помощью вот такого запроса:

SELECT am.amname AS index_method,
opc.opcname AS opclass_name,
opc.opcintype::regtype AS indexed_type,
opc.opcdefault AS is_default
FROM pg_am am, pg_opclass opc
WHERE opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;

Запрос взят из документации:

➡️ https://postgrespro.ru/docs/postgresql/17/indexes-opclass

Но на этом еще не все! Мы можем создавать свои собственные операторы классов! 🤩 Синтаксис будет таким:

CREATE OPERATOR CLASS название_оператора_класса
FOR TYPE тип_данных USING тип_индекса
AS
OPERATOR 1 тип_операций_сравнения ,
FUNCTION 1 имя_функции;

Здесь OPERATOR 1 указывает какие операции сравнения поддерживает наш оператор классов, т.е. например так = . Операции сравнения зависят от типа индекса, для которого вы создаете класс. Например, хэш-индекс поддерживает только равенство, а B-tree поддерживает гораздо больше операций сравнения. Таких операторов мы можем добавить в наш класс сколько угодно, следующий будет OPERATOR 2 и т.д.;

FUNCTION - здесь мы можем указать какую-то функцию для работы класса индекса с типом данных.

Операторы классов являются достаточно эффективным средством для оптимизации производительности вашего индекса. Особенно эффект от правильно подобранного для индекса оператора класса будет заметен на больших таблицах.

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

#pgbase

🤖 Использование переменных в psql.

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

Существует два типа переменных, которые могут использоваться в psql:

📌 Клиентские - это переменные самого psql, которые устанавливаются мета-командой \SET. Такие переменные могут быть использованы в ваших скриптах для хранения каких-то значений.

Синтаксис.

Устанавливаем переменную:

\SET my_var 10

Теперь обратиться к такой переменной можно вот так:

SELECT :my_var AS result;

Устанавливаем текстовую переменную:

\SET my_var 'Hello, world'

Обращаемся к текстовой переменной:

SELECT :'my_var' AS hi;

Заметили кавычки в :'my_var' ? Вот такой интересный синтаксис.

Еще один способ установить переменную, использование команды \gset:

SELECT now() AS tm, current_user AS usr \get

📌 Серверные - эти переменные устанавливаются пользователем с помощью SQL команды SET (не запутайтесь, тут нет обратного слэша, это SQL команда, а не мета-команда psql). Такие переменные могут быть использованы для хранения промежуточных результатов запроса, например.

Устанавливаем переменную:

SET my_var.v1 to 10;

Устанавливаем текстовую переменную:

SET my_var.v1 to 'Hello, World!';

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

Получить значения таких переменных мы можем с помощью команды SHOW, вот так:

SHOW my_var.v1;

Переменные, установленные с помощью SQL команды SET непостоянны, они действую только до конца сессии. А если вы установили переменную с помощью команды SET LOCAL, то вообще до конца транзакции. Если вам нужна постоянная переменная, то установить ее можно следующими способами:

На уровне всего кластера:

ALTER SYSTEM SET my_var.v1 to 10;

На уровне базы данных:

ALTER DATABASE base_name SET my_var.v1 to 10;

На уровне пользователя:

ALTER USER user_name SET my_var.v1 to 10;

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

#psql

#pgmeme

🛠️ Шаблон для обеспечения отказоустойчивости кластера PostgreSQL - Patroni (ч. 1).

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

Начнем с того, почему сами разработчики Patroni называют его шаблоном. Все дело в том, что сам по себе Patroni не может работать из коробки, ему нужно дополнительное программное обеспечение. Именно в связке с ним Patroni сможет обеспечить высокодоступный кластер PostgreSQL.

Что же такое Patroni? Patroni - open-source шаблон для разворачивания отказоустойчивого, высокодоступного кластера PostgreSQL на базе потоковой репликации, а также для управления и мониторинга им. Patroni написан на Python. Под капотом Patroni для отказоустойчивости использует потоковую репликацию PostgreSQL, но позволяет автоматизировать переключение между серверами и избежать так называемого split-brain.

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

📌 Автоматическое переключение узлов в случае аварии на главном;

📌 Ручное переключение узлов или переключение по расписанию;

📌 Поддержка синхронной и асинхронной репликации PostgreSQL;

📌 Поддержка каскадной репликации;

📌 Поддержка автоматического запуска pg_rewind для возврата главного сервере в строй;

📌 Пользовательские скрипты;

📌 REST API для управления и мониторинга кластера Patroni;

📌 Интерфейс командной строки patronictl для управления кластером Patroni.

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

Для работы Patroni необходима распределенная база данных типа ключ-значение. В такой базе Patroni будет хранить состояния узлов и их статус, также нужен прокси-сервер для обеспечения единой точки входа клиента в кластер и переключения клиента на новый мастер сервер. Таким образом, краткое описание схемы работы Patroni получается следующим: агент Patroni устанавливается на всех узлах PostgreSQL, между которыми настроена потоковая репликация. С помощью РБД типа ключ-значение отслеживается состояние узлов, если случилась авария, то Patroni производит автоматическое переключение на сервер реплику и поднимает его роль до главного сервера, а прокси-сервер обеспечивает переключение соединений клиентов на нового мастера.

Если говорить про каждый компонент шаблона Patroni отдельно, то в качестве РБД ключ-значение он поддерживает следующие базы: etcd, Consul, ZooKeeper, и Kubernetes API. Наиболее популярной РБД в связке с Patroni является etcd, а Kubernetes API необходим в случае разворачивания Patroni в Kubernetes. В качестве прокси-сервера вы можете использовать вообще все что угодно, но наиболее популярным тут будет HAProxy.

Собственно РБД обеспечивает Patroni четкие инструкции о том какой сервер является в данный момент главным, а какой репликой. Patroni хранит в РБД ключ типа /leader, который имеет ограниченный TTL (time to live), или по русски срок действия и хранит в себе информацию о текущем мастер сервере. Если ключ не получил своевременного обновления, то Patroni посчитает, что мастер сервер вышел из строя и создаст новый /leader ключ, а также сделает новый мастер сервер из реплики. Все операции с этим ключём защищены работой РБД, которая производит его сравнение на всех узлах кластера, что исключает ситуации когда сразу несколько реплик могут стать мастером и случится split-brain базы данных.

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

#pghi

Вебинар «Анализируем транзакции в реальном времени» 

Приходите на бесплатный вебинар и узнайте, как обрабатывать высокие транзакционные и аналитические нагрузки в гибридной in-memory СУБД. 

Дата и время: 28 ноября, 16:00.

Программа

🔹 Ускорение аналитических расчетов и аналитика на самых свежих данных с помощью HTAP-систем.

🔹 Основные архитектурные характеристики гибридных транзакционно-аналитических СУБД (HTAP). 

🔹 Повышение отказоустойчивости транзакционно-аналитических решений, настройка под разные профили нагрузки и интеграция с другими компонентами ИТ-ландшафта. 

Проведем демонстрацию возможностей продукта Tarantool Column Store в работе с объектами и данными.

Расскажем, как формировать отчетность в реальном времени и рассчитывать агрегаты в антифрод-системах с помощью продукта Tarantool Column Store.  

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

🔬 Сценарии при которых срабатывает AUTOVACUUM в PostgreSQL.

Опять про великий и ужасный AUTOVACUUM 👹 На этот раз поговорим о том при каких обстоятельствах он срабатывает в PostgreSQL.

📌 Количество "мертвых" строк превышает пороговое значение с момента последнего VACUUM. В PostgreSQL есть парочка параметров, которые влияют на это поведение AUTOVACUUM.

autovacuum_vacuum_threshold (значение по умолчанию 50) и autovacuum_vacuum_scale_factor (значение по умолчанию 0,2).

Формула будет такой:

autovacuum_threshold = autovacuum_vacuum_threshold + (Кол-во строк в таблице * autovacuum_vacuum_scale_factor)

Т.е, если у вас 100 строк в таблице, то автоочистка при значениях по умолчанию сработает, когда в таблице будет примерно 70+ мертвых строк.

📌 relfrozenxid больше, чем autovacuum_freeze_max_age транзакций. relfrozenxid - это маркер, который показывает самый старый ID транзакции в таблице, который не был еще заморожен. Напомним, заморозка - это процесс в PostgreSQL, который позволяет безопастно переиспользовать ID старых транзакций, чтобы предотвратить их зацикливание.

За лимит заморозки в PostgreSQL отвечает параметр autovacuum_freeze_max_age. Соответственно, как только этот лимит привысится, на таблице сработает AUTOVACUUM и начнет замораживать старые ID.

📌 Кол-во новых строк (INSERT) превысит лимит вставки с момента последнего запуска VACUUM. Здесь ситуация похожая с кол-вом "мертвых" строк, только для строк новых.

Формула будет такой:

insert_threshold = autovacuum_vacuum_threshold + (Кол-во строк в таблице* autovacuum_vacuum_scale_factor)

📌 Кол-во транзакций превысило разницу между vacuum_freeze_table_age и vacuum_freeze_min_age с последнего агрессивного VACUUM. PostgreSQL может агрессивно включать VACUUM, чтобы предотвратить зацикливание ID транзакций. Под агрессивным VACUUM понимается то, что PostgreSQL будет проводить его несмотря ни на какие ваши настройки как можно быстрее, чтобы предотвратить катастрофу. Так вот, такой VACUUM может включиться, если кол-во транзакций превысило разницу между параметрами
vacuum_freeze_table_age и vacuum_freeze_min_age с последнего такого VACUUM.

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

📌 Если срок жизни самого старейшего multixact ID превысит порог autovacuum_multixact_freeze_max_age. multixact ID используется в PostgreSQL для сценариев, когда нескольким транзакциям нужно получить доступ к одной и той же строке. Это параметр отслеживается с помощью значения relminmxid. Если возраст самого старого multixact ID превысит значение параметра autovacuum_multixact_freeze_max_age, то сработает AUTOVACUUM.

Кстати, для хранения информации о multixact ID PostgreSQL использует специальную папку на диске. Если размер этой папки превысит 2Gb, то тоже сработает AUTOVACUUM.

📌 Если общее кол-во вставленных (INSERT), обновленных (UPDATE) или удаленных (DELETE) строк превысит пороговое значение с момента последней операции ANALYZE. VACUUM отвечает не только за очистку старых строк, но и за сбор статистики, поэтому в этом случает он тоже сработает.

Формула такая:

analyze_threshold = autovacuum_analyze_threshold + (Кол-во строк в таблице * autovacuum_analyze_scale_factor)

Как видите случаев срабатывания VACUUM немало. Мы перечислили только самые основные. VACUUM играет ключевую роль в работоспособности и обеспечения производительности вашей базы данных, так что не забывайте приглядывать за ним 😉.

#pgbase

#pgmeme

🌐 Обновления PostgreSQL 17.1, 16.5, 15.9, 14.14, 13.17, и 12.21.

Всем привет! Вчера (14.11.2024) вышло обновление для основных версий PostgreSQL.

⚠️ Это последнее обновление, закрывающее проблемы с безопасностью для PostgreSQL версии 12. Больше эта версия обновляться не будет. Так что, стоит подумать о переходе на новую версию 😏.

Обновление закрыло целых 4е дырки в безопасности и исправило множество мелких багов и недочетов.

Патчи безопасности:

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

📌 Использование ошибок libpq для перехвата данных. Использование клиентом сообщения об ошибке сервера в PostgreSQL позволяло серверу, не являющемуся доверенным в текущих настройках SSL или GSS, предоставлять произвольные ненулевые байты приложению libpq. Это исправление предотвращает так называемые атаки "man-in-the-middle", когда злоумышленник мог отправить длинное сообщение об ошибке, которое пользователь мог принять за допустимые результаты запроса.

📌 Ошибка в командах PostgreSQL SET ROLE, SET SESSION AUTHORIZATION.
Эта ошибка позволяла пользователю с меньшими привилегиями просматривать или изменять строки, которые ему собственно изменять или просматривать не положено, из-за того, что эти команды сбрасывали идентификатор пользователя на неверный.

📌 Изменения переменных среды PostgreSQL PL/Perl приводят к выполнению произвольного кода. Эта ошибка касается использования PostgreSQL совместно с PL/Perl. Неправильный контроль переменных среды позволял непривилегированному пользователю базы данных их изменять, что могло привести к выполнению произвольного кода.

Плюс исправили более 35 различных ошибок. Среди них есть такие:

✅ Исправление ошибки при присоединении или отсоединении разделов таблиц с ограничениями внешнего ключа;

✅ Несколько исправлений внесено в работу планировщика запросов;

✅ Исправлена ​​ошибка команды COMMIT PREPARED из-за которой после сбоя и восстановления могло потребоваться ручное удаление файлов;

✅ Уменьшено потребление памяти при логическом декодировании.

Более подробно со всеми обновлениями и исправлениями можно познакомиться на официальном сайте:

➡️ https://www.postgresql.org/about/news/postgresql-171-165-159-1414-1317-and-1221-released-2955/

Как видите, обновлений и исправлений приличное количество. Так что не запускаем свою PostgreSQL и поддерживаем ее в актуальном состоянии 😉.

#pgnews

🔬Волатильность (изменчивость) функций в PostgreSQL.

Давайте разбираться что это такое и зачем нам вообще об этом нужно знать 🤔.

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

В PostgreSQL существует несколько типов волатильности функций:

📌 VOLATILE. Те самые "нехорошие" функции, которые возвращают разные значения с одинаковыми аргументами даже в рамках сканирования одной таблицы. Такая функция может модифицировать данные в базе данных, а оптимизатор PostgreSQL не будет даже пытаться делать каких-то предположений о ее работе. Соответсвенно такая функция будет заново рассчитываться для каждой строки запроса. Данный тип волатильности для функции подразумевается PostgreSQL по умолчанию, но мы можем его переопределить (об этом ниже). К таким функциям относятся различные функции, которые работают со случайными значениями, или возвращают текущее время. Например: random(), currval() и timeofday() и т.д.;

📌 STABLE. Такие функции возвращают одинаковый результат в рамках одного запроса, но могут возвращать разные результаты в разных запросах. Такие функции не могут модифицировать базу данных. Благодаря такому поведению, планировщик может заменить множество вызовов функции одним. Такие функции можно безопасно использовать в условии поиска по индексу;

📌 IMMUTABLE. Такие функции будут возвращать одинаковые значения всегда. Они тоже не могут модифицировать базу данных и позволяют планировщику заранее вычислить их значение.

Указать тип волатильности функции мы можем в момент ее создания с помощью команды CREATE FUNCTION. Если этого не сделать, то по умолчанию будет подразумеваться VOLATILE.

Следует также помнить, что волатильность функции влияет на видимость ею данных, внесенных или изменных другими параллельно выполняющимися транзакциями. Функции с типом VOLATILE будут видеть такие данные, а вот STABLE и IMMUTABLE - нет. STABLE и IMMUTABLE функции используют снимок, полученный в начале вызывающего запроса, тогда как функции VOLATILE получают свежий снимок в начале каждого запроса внутри транзакции. Функции STABLE и IMMUTABLE не замечают изменений в базе данных, произведённых ими, поскольку они не попадают в моментальный снимок данных. Поэтому PostgreSQL требует, чтобы функции STABLE и IMMUTABLE не содержали никаких SQL-команд, кроме SELECT, чтобы избежать некорректной модификации данных.

Несмотря на такое требование мы можем использовать VOLATILE функции внутри STABLE и даже IMMUTABLE функций и модифицировать данные. Но если вы так делаете, то следует помнить, что STABLE и IMMUTABLE все равно не будут видеть изменения, внесённые VOLATILE функциями, что может привести к некорректной модификации данных в базе.

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

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

#pl/pgsql #pgbase

📊 Представление pg_shmem_allocations в PostgreSQL.

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

Про расширение pg_buffercache мы уже писали, но оно показывает только данные об использовании общего кэша под таблицы и индексы. А вот чтобы посмотреть служебные структуры в общей памяти, нам и пригодится представление pg_shmem_allocations. Давайте разбираться!

Каждая строка представления pg_shmem_allocations представляет собой информацию о конкретной служебной структуре в общей памяти PostgreSQL. Запрос к представлению простой, так как само представление содержит совсем немного полей:

SELECT *
FROM pg_shmem_allocations
ORDER BY size DESC LIMIT 10;

Если посмотреть в вывод представления, то самой большой структурой по объему занимаемой памяти будет Buffer Blocks. Это и есть буферный кэш, который содержит пользовательские данные. Более подробно его можно исследовать с помощью того же расширения pg_buffercache.

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

Само представление pg_shmem_allocations содержит следующие поля:

📌 name - имя структуры, по которому можно примерно понять ее назначение. Если в списке присутствует имя NULL, то это сегмент неиспользуемой памяти, если есть имя <anonymous> - то это анонимный сегмент памяти, который может использоваться для хранения информации о блокировках;

📌 off - смещение в области общей памяти, с которого начинается сегмент структуры. Для анонимных сегментов значение смещения будет NULL;

📌 size - размер сегмента в байтах;

📌 allocated_size - размер сегмента с учетом выравнивания в памяти (padding). О выравнивании мы уже писали. Для свободной памяти и анонимных сегментов значения size и allocated_size будут всегда равны.

В итоге: представление pg_shmem_allocations будет не очень полезно в повседневном мониторинге производительности и работоспособности вашей СУБД. Но оно может очень сильно помочь в целях контроля расхода оперативной памяти, или отладки какого-либо кода.

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

#pg_monitor

#pgmeme

#pgmeme

🔬 Логика работы и стратегии TOAST в PostgreSQL.

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

Логика работы алгоритма TOAST такая: если размер строки (кортежа) превышает 2Kb, то алгоритм проходит по всем атрибутам строки и сжимает ее до тех пор, пока она не будет удовлетворять этому условию. От куда взялся размер строки в 2Kb, если размер страницы 8Kb?

Расчет тут такой: должно влезть 4е строки в одну страницу (2Kb * 4). Соответсвенно, если строка больше 2Kb, то 4е строки не поместятся на одну страницу и придется разбивать строку на две страницы, что в свою очередь негативно скажется на производительности базы данных. Поэтому PostgreSQL будет пытаться впихнуть все 4е строки на одну страницу, применяя к ним механизм сжатия.

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

Для сжатия строк TOAST использует специальный алгоритм компрессии, называемый pglz (PostgreSQL Lempel-Ziv). Этот метод сжатия используется по умолчанию, но можно использовать еще и lz4, если PostgreSQL была изначально скомпилирована с параметром with-lz4. Устанавливать алгоритм компрессии для TOAST можно с помощью параметра default_toast_compression. Алгоритм сжатия можно установить для конкретной колонки таблицы, введя параметр COMPRESSION в команду CREATE TABLE или ALTER TABLE.

У TOAST существует четыре вида стратегии:

📌 PLAIN. Эта стратегия применяется только в том случае, если тип данных в столбце не совместим с механизмом TOAST. Т.е. в данном случае, строка не будет ни сжата, ни перемещена в отдельную тост-таблицу;

📌 EXTENDED. Эта стратегия используется по умолчанию. TOAST сначала пытается сжать значение, если не получилось, то перемещает его в тост-таблицу;

📌 EXTERNAL. При такой стратегии допускается только отдельное хранение больших данных в тост-таблицах, но не сжатие. Эта стратегия ускорит операции над большими столбцами с типами данных text и bytea, но и ценой такого ускорения будет больший расход места на дисках;

📌 MAIN. Здесь наоборот, допускается сжатие, но не отдельное хранение. Но если сжатие не поможет ни как, то такие данные все равно будут храниться в отдельных тост-таблицах.

Есть несколько конфигурационных параметров, с помощью которых мы можем повлиять на работу механизма TOAST:

📌 TOAST_TUPLE_THRESHOLD - размер строки, при котором срабатывает TOAST. По умолчанию обычно 2Kb;

📌 TOAST_TUPLE_TARGET - минимальный размер строки, до которого TOAST будет ее сжимать или выносить в отдельную тост-таблицу. Значение по умолчанию так же 2Kb.

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

#pgbase #pgsettings

❓Как научиться извлекать максимальную пользу из данных?

Приходите на курс "Основы Data Governance" с Александром Бараковым от BI Consult и получите актуальные навыки в трендовой теме управления данными! Data Governance позволит вам навести порядок в данных, ускорить разработку в ИТ-системах, повысить надежность и качество данных, аналитики и отчетности.

📆Даты: 4-13 декабря 2024 года.

Формат курса: 5 встреч по 3 часа интенсива в онлайн формате для CDO, CIO, Data stewards, BI-менеджеров, Операционных директоров и всех кому тема близка.

Автор курса: Александр Бараков - эксперт с 10-летним опытом ведения Data Governance проектов.

Курс позволит сформировать глубокое понимание темы Data Governance и 'примерить' все это на бизнес реалии вашей организации. По итогам курса у каждого слушателя будет сформирована готовая стратегия по внедрению Data Governance в свою компанию и понимание того, как настроить управление данными в любом бизнесе.

🗂На курсе мы погрузимся в темы:

• Information Management (IM)
• Data Quality (DQ)
• Data Governance (DG) (основные элементы, технологии и практики)
• Master Data Management (MDM)
• Управление и оценка эффективности DG

Каждый участник будет заполнять excel-гайд своего проекта Data Governance, применяя разделы курса на контекст своей компании.

📌Почему стоит выбрать этот курс?

• Сильная база: Вы поймете почему данные - это важно, в чем их реальная ценность и как 'продавать' проект менеджменту.
• Практическая направленность: Курс включает реальные кейсы и задачи из бизнеса.
• Нетворкинг: Участвуйте в активном обмене опытом с другими участниками курса и расширьте свою профессиональную сеть. На нашем курсе обучаются CDO и CIO таких компаний, как Максидом, Сбербанк, ВК, Sunlight и KFC.

🔝Записаться на курс

Реклама: ИП Громов С.Л. ИНН: 781133862981 Erid: 2VtzqvpDKKs

🔬 Недостатки TOAST в PostgreSQL и как с ними жить.

Про технологию TOAST в PostgreSQL, я думаю, вы все прекрасно знаете. Кратко напомним, что это такое.

TOAST (The Oversized Attribute Storage Technique) - это механизм хранения больших колонок в PostgreSQL. Все дело в том, что в PostgreSQL размер одной записи в таблице не может превышать размер страницы (по умолчанию 8Kb). Как только запись превысит размер страницы, в дело вступает TOAST. TOAST разбивает значение на кусочки, или сжимает его (зависит от выбранной стратегии) и хранит в специальной таблице.

При этом оригинальное значение в основной таблице подменяется на ссылку (TOAST Pointer), которая хранит служебную информацию, такую как: ID тост-таблицы, ID записи в тост-таблице, длина исходного и фактически хранимого значения.

К плюсам TOAST можно отнести:

📌 Технология разработана уже давно и работает стабильно и без нареканий;

📌 TOAST работает абсолютно незаметно для пользователей и приложений, работающих с базой данных.

Но, к сожалению и недостатков у TOAST хватает:

📌 Ограничение на 2^32 уникальных ID значений в тост-таблице. То есть, больше чем 2^32 значений просто не может попасть в тост-таблицу. При достижении этого значения больше невозможно сделать операции INSERT или UPDATE в основную таблицу, если хотя бы одна колонка при этом попадет в TOAST;

📌 В тост-таблицах нет операций UPDATE. Каждая старая строка помечается как "мертвая", а на ее место вставляется новая. Место под новые строки освободиться только тогда когда в тост-таблицу придет VACUUM, как следствие, это может вести к распуханию тост-таблиц;

📌 Тост-таблица имеет точно такое же ограничение на объем данных, как и простая таблица в PostgreSQL на 32Tb. На сильно нагруженных базах данных такой объем уже не кажется очень большим. А учитывая, что в тост-таблицы попадают все значения больше 8Кб, такая таблица может достигнуть ограничения быстрее чем основная, что опять же приведет к невозможности операций INSERT и UPDATE основной таблицы;

📌 TOAST является частью ядра PostgreSQL, что сильно затрудняет его модификацию.

В основном, недостатки механизма TOAST связаны с несколькими устареванием данного механизма под современные реалии увеличившегося в разы объема обрабатываемых данных, по сравнению с тем объемом когда TOAST только появился.

Учитывая ограничения TOAST, нужно следить за разрастаниями тост-таблиц и месте на дисках, проверять как часто в такие таблицы приходит AUTOVACUUM и не распухают ли они.

В будущих постах еще более подробно поговорим о механизме TOAST. До связи!

#pgbase

#pgmeme

🛠️ Пишем в лог таблицы, используемые в запросах (расширение pg_relusage).

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

➡️ https://github.com/adept/pg_relusage

Расширение поддерживает версии PostgreSQL с 9.5 до 17.

Зачем нам может это понадобиться? Например, если у нас есть какая-то рукотворная база данных и мы хотим ее оптимизировать и удалить неиспользуемые таблицы. Конечно, такой трюк не пройдет с базами данных, которые используются каким-то проприетарным приложением, типо 1с. Удаление таблиц из таких баз чревато отказом в работе приложения и полной катастрофой 🤬🤕. Или, если мы хотим переместить неиспользуемые таблицы на более медленный диск, чтобы освободить место на быстром диске.

pg_relusage запишет в лог PostgreSQL таблицы, которые используются в запросах. Таким образом мы можем отсеять такие таблицы от неиспользуемых. Конечно, чтобы получить реальную картину, нужно чтобы расширение поработало какое-то время (желательно не в выходные 😁).

Расширение использует так называемые хуки (hooks), которые внедряются в работу планировщика запросов PostgreSQL и позволяют узнать информацию о таблицах, используемых в запросах. В связи с этим расширение должно быть собрано под конкретную версию PostgreSQL. Делается это с помощью команды

make install

После сборки и установки расширения его нужно прописать в shared_preload_libraries и перезапустить службу PostgreSQL.

После этого расширение начнет писать в логи информацию вида:

relations used: имя таблиц, участвующих в запросах

У расширения есть парочка конфигурационных параметров:

📌 pg_relusage.log_level (по умолчанию LOG) - уровень сообщения. Значения этого параметра аналогичны параметру PostgreSQL - client_min_messages. Этим параметром вы можете повысить уровень сообщения до предупреждения или ошибки;

📌 pg_relusage.rel_kinds (по умолчанию 'riSvmfp') - вид отношений, о которых будет собираться информация об использовании. Значение этого параметра представляет собой код. Такие же коды используются в колонке relkind в системном каталоге pg_class. Со списком все кодов можно ознакомиться в документации:

➡️ https://www.postgresql.org/docs/current/catalog-pg-class.html

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

#pgext

🤬 Ошибка "Relation ‘table_name’ Does Not Exist" в PostgreSQL.

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

1️⃣ Ошибки в запросах. Это на самом деле самая банальная и распространённая причина данной ошибки. Так что, прежде чем бросаться восстанавливать базу данных из резервной копии, стоит повнимательнее посмотреть на ваш запрос.

Самое частое о чем забывают - это то, что имена таблиц в PostgreSQL регистрозависимые. Т.е. если ваша таблица называется, например MyTable, то при запросе к ней, ее имя нужно заключать в кавычки, вот так:

SELECT * FROM "MyTable";

Ну и конечно, нужно удостовериться, что вы вообще пишете правильное имя таблицы в запросе. Посмотреть какие таблицы у вас существуют в базе данных можно с помощью команды psql - \dt.

2️⃣ Путь поиска. Путь поиска в PostgreSQL определяет в том числе в какой схеме базы данных по умолчанию искать таблицы. Если у вас есть схема, созданная вами и в ней хранятся какие-то таблицы, то вполне возможно, что эта схема просто не включена в путь поиска PostgreSQL.

Варианта решения тут два. Обращаться к таблице в запросе с указанием имени схемы, вот так;

SELECT * FROM shema_name.table_name;

Или включить вашу схему в путь поиска PostgreSQL. Посмотреть что включено в путь поиска можно с помощью вот такой команды:

SHOW search_path;

Добавить вашу схему в путь поиска можно вот так:

SET search_path TO myschema, public;

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

3️⃣ Материализованные представления. Про них мы уже писали. Здесь может случиться, так что у вас появилась новая таблица, которую вы включили в материлизованное представление, а само представление обновить забыли.

Чтобы обновить материлизованное представление, можно воспользоваться следующей командой:

REFRESH MATERIALIZED VIEW view_name;

4️⃣ Отсутствие прав на таблицу. Тоже частая причина данной ошибки, о которой забывают.

Чтобы проверить права на таблицу можно воспользоваться следующей командой:

\dp table_name

А чтобы выдать права на чтение, можно выполнить вот такую команду:

GRANT SELECT ON table_name TO user_name;

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

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

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

#pgerrors

📊 Обнаружение блокировок с помощью представления pg_locks.

Тут в комментариях к одному из постов про pg_stat_statements появился запрос на заметку про представление pg_locks. Так что сегодняшний пост будет именно про него 😊.

С помощью представления pg_locks можно получить информацию об удерживаемых блокировках. Запросами только к этому одному представлению можно получить детальную информацию по блокировкам, но на практике данное представление хорошо соединять с полями представления pg_stat_activity, чтобы получить еще более развернутую информацию.

Каждая строка представления pg_locks содержит информацию об объекте базы данных на который установленная блокировка, или для которого требуется установка блокировки, а также информация о том кто затребовал данную блокировку.

Когда несколько процессов требуют блокировку одного и того же объекта базы данных, то этот объект в выводе представления будет показан несколько раз.Объектами блокировки могут быть как таблицы, так и строки, или даже отдельные страницы таблицы. Соответсвенно, если блокировка снята, то информация о ней из представления убирается.

Представление pg_locks покажет нам информацию о рекомендательных блокировках (advisory lock), эти блокировки устанавливает само приложение, но не покажет информацию о легких блокировках (lightweight lock, LWLock), эти блокировки работают на более низком уровне и контролируют доступ к структурам памяти PostgreSQL.

Следует помнить о том, что представление pg_lock собирает информацию только на данный момент времени (это касается и pg_stat_activity) и не имеет кумулятивной статистики. Чтобы увидеть всю структуру и поля pg_lock можно воспользоваться командой:

\d+ pg_locks

Остановимся на наиболее важных (наш взгляд) полях представления более подробно:

📌 relation, page, tuple - отношение, номер страницы и номер строки внутри страницы;

📌 virtualxid, transactionid - виртуальный и фактический номер транзакции;

📌 locktype - тип блокировки;

📌 database - OID базы, в которой возникла блокировка;

📌 pid - идентификатор процесса. Это поле чаще все используется для объединения с представлением pg_stat_activity, чтобы можно было получить дополнительную информацию, такую как: имя пользователя, текст запроса и т.д.;

📌 granted - этот флаг указывает удерживается блокировка (true), или ожидает удержания (false);

📌 waitstart - время перехода в ожидание блокировки.

Примеры запросов к представлению pg_locks можно посмотреть здесь:

➡️ https://wiki.postgresql.org/wiki/Lock_Monitoring

И здесь:

➡️ https://wiki.postgresql.org/wiki/Lock_dependency_information

Запросы достаточно объемные и в пост не влезут. Да и вообще, сайт сам по себе полезен, рекомендуем ознакомиться 😊.

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

#pgmonitor

#pgmeme

#pgmeme

📊 Глубокое погружение в pg_stat_statements, часть 6.

Сегодня у нас 6-я, она же заключительная часть постов про pg_stat_statements. Мы рассмотрели с вами наиболее важные нюансы работы расширения. В этой части пробежимся по основным настройкам pg_stat_statements и приведем примеры наиболее полезных и важных на наш взгляд запросов.

Основные настройки pg_stat_statements, которые влияют на его работу и получаемые данные:

📌 pg_stat_statements.max - максимально кол-во отслеживаемых типов запросов, т.е. кол-во строк, выводимых в представлении. Значение по умолчанию 5000;

📌 pg_stat_statements.track — уровень отслеживаемых запросов. Возможные значения:
top- отслеживать вызовы верхнего уровня,то есть запрошенные клиентом (по умолчанию);

all - отслеживать все вызовы, включая функции и процедуры;

none - не отслеживать ничего.

📌 pg_stat_statements.track_utility - отслеживать статистику для служебных команд, отличных от SELECT, INSERT, UPDATE и DELETE;

📌 pg_stat_statements.track_planning - сбор статистики о планировании запросов;

📌 pg_stat_statements.save - сохранение статистики при отключении службы PostgreSQL.

Переходим к запросам!

Запросы с наибольшим количеством вызовов:

SELECT
sum(calls) AS total_calls,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(calls) DESC
LIMIT 10;

Запросы с наибольшим количеством возвращаемых строк:

SELECT
sum(rows) AS total_rows,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(rows) DESC
LIMIT 10;

Запросы с наибольшим временем выполнения:

SELECT
to_char(
interval '1 millisecond' * sum(total_exec_time), 'HH24:MI:SS'
) AS exec_time,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query ORDER BY sum(total_exec_time) DESC LIMIT 10;

Запросы с наибольшим временем ввода-вывода:

SELECT
to_char(
interval '1 millisecond' * sum(
blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time
), 'HH24:MI:SS.MS'
) AS io_time,
left(query, 64) AS query_trunc
FROM pg_stat_statements
WHERE blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time > 0
GROUP BY query
ORDER BY sum(blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time) DESC
LIMIT 5;

Запросы, с наибольшем временем работы CPU:

SELECT
to_char(
interval '1 millisecond' * sum(total_exec_time - (
blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time)),
'HH24:MI:SS') AS cpu_time,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(total_exec_time - (
blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time)
) DESC LIMIT 10;

Здесь мы из общего времени выполнения запроса вычитаем, время затраченное запросом на операции ввода-вывода. Оставшее время - это время затраченное CPU на выполнение запроса.

Агрегированная таблица по времени выполнения запроса и процентам операций ввода-вывода и работы CPU:

SELECT
to_char(
interval '1 millisecond' * sum(total_exec_time),
'HH24:MI:SS'
) AS exec_time,
(100 * sum(
blk_read_time + blk_write_time +
temp_blk_read_time + temp_blk_write_time
) / sum(total_exec_time))::numeric(5,2)::text || ' / ' ||
(100 * sum(total_exec_time - (
blk_read_time + blk_write_time +
temp_blk_read_time + temp_blk_write_time)
) / sum(total_exec_time))::numeric(5,2) AS "io / cpu, %",
left(query, 48) AS query_trunc
FROM pg_stat_statements
GROUP BY query ORDER BY sum(total_exec_time) DESC LIMIT 10;

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

Запросы, которые больше всех пишут данные в журнал WAL:

SELECT
pg_size_pretty(sum(wal_bytes)) AS wal_volume,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(wal_bytes) DESC
LIMIT 10;

На этом все! Если у вас есть какие-то еще полезные запросы для pg_stat_statements, оставляйте их в комментариях 👇 . До связи!

#pgmonitor #pgext

Хотите узнать, как использовать Patroni для управления высокодоступными кластерами PostgreSQL?

Ждем вас на открытом вебинаре 24 октября в 20:00 мск, где мы разберем:
- что такое Patroni и как он помогает обеспечить высокую доступность для PostgreSQL;
- основные компоненты и архитектуру Patroni;
- настройку и конфигурацию Patroni для работы с PostgreSQL;
- примеры использования Patroni для управления кластером PostgreSQL;
- практические советы по оптимизации и мониторингу работы кластера.

👨‍💻🛠👨🏻‍💻 Урок для DevOps-инженеров, системных администраторов, backend-разработчиков и тимлидов, стремящихся обеспечить высокую доступность PostgreSQL.

Встречаемся в преддверии старта курса «Highload Architect». Все участники вебинара получат специальную цену на обучение!

🔴 Регистрируйтесь прямо сейчас, чтобы не пропустить мероприятие: https://otus.pw/w216/?erid=LjN8KRbUA

#реклама
О рекламодателе

📊 Глубокое погружение в pg_stat_statements, часть 5.

В прошлой заметке мы с вами смотрели как считать кумулятивные метрики pg_stat_statements на основе дифференциации по временным интервалам. В этой заметке посмотрим еще два варианта расчета кумулятивных метрик.

Начнем с дифференциации по количеству вызовов (calls). Расчет таких метрик достаточно прост и понятен, берем два значение метрики, собранные в разные промежутки, находим разницу между ними и делим на разницу между вызовами за эти же промежутки. Формула будет такой:

dM/dc

где M дифференцированное значение метрики, а c - дифференцированное значение кол-ва вызовов.

Вместо M мы будем подставлять все теже метрики из предыдущей заметки. Кроме, собственно, calls, так как в данном случае всегда будем получать единицу 😊. Что нам дадут посчитанные таким образом метрики? Они нам покажут усредненные характеристики группы запросов и позволят выявить аномалии в них.

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

M– total_plan_time + total_exec_time. Получим среднее время выполнения запроса в конкретной группе. Очень важная метрика для мониторинга производительности запросов. Если ее применить вообще для всех агрегированных запросов на сервере, то получим среднюю задержку выполнения запросов на всем сервере PostgreSQL. Эта информация ценна тем, что система комсулятивной статистики самой PostgreSQL не предоставляет пока что таких данных.

Если переходить к трактовке полученных данных, то к примеру, если у вас среднее время выполнения запросов равно 2мс, а в какой-то группе запросов оно подскочило до 10, можно переходить к поиску виновника и оптимизации запроса. Также данную метрику можно использовать в качестве тестирования нагрузки на ваш сервер PostgreSQL.

3й тип расчета метрик, полученных через pg_stat_statements - процентное отношение. Рассчитывается так: сначала рассчитываем временную дифференциацию для выбранной группы запросов, а потом делим полученное значение на сумму значений всех остальных групп запросов 🤯 Если предыдущие способы расчета метрик нам дадут абсолютные значения типо вызовы в секунду или гигабайты, то данный тип покажет нам относительные значения. Т.е. с помощью процентных отношений мы можем выявить основных игроков в нашей рабочей нагрузке в различных аспектах, типо частоты, времени, кол-ве вызовов и т.д.

Процентные отношения хороши тем, что помогут нам понять насколько метрики, полученные предыдущими способами приемлемые на фоне общей нагрузки или нет. Т.е. если мы увидим, например, кол-во запросов в секунду равное 1000 в какой-то группе запросов и подумаем, что это много и что-то тут не так, но увидим процентное отношение во всей нагрузке, например в 3%, то поймем, что оптимизация этой группы запросов нам не даст большого прироста производительности. Другое дело, если процентное отношение покажет, например 50%, тогда стоит уже задуматься над оптимизацией.

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

Финальным постом этой серии собираем полезные запросы к pg_stat_statements. До связи!

#pgmonitor #pgext

📊 Глубокое погружение в pg_stat_statements, часть 4.

Раз никому еще не надоел pg_stat_statements, то продолжаем 😊. В первой части мы с вами говорили о том, что для кумулятивных метрик pg_stat_statements нужно высчитывать различные дифференциации. Конечно, если у вас есть хорошая система мониторинга, которая умеет работать с pg_stat_statements, то вам это все и не нужно. Но понимать глубинные процессы всегда полезно.

Начнем с дифференциации по времени. Считается она вот по такой формуле:

dM/dt

где:

dM - разница данных метрики (M) между двумя снимками (снятием метрики);

dt - разница между отметками времени при снятии метрики.

Теперь, вместо M мы можем подставлять различные метрики и смотреть что получится.

📌 M = calls (кол-во вызовов запроса). Получим QPS (кол-во запросов в секунду). Здесь не стоит забывать о том, что pg_stat_statements группирует семантически одинаковые запросы в одну строку, поэтому QPS мы получим для группы запросов. Здесь высокие значения необязательно будут показателем высокой производительности вашей системы. Может случиться так, что какая-то группа запросов вызывается слишком часто и вам нужно исправить ваше приложение, работающее с базой данных. Все зависит от ситуации;

📌 M = total_plan_time + total_exec_time. Это очень полезная метрика , которая покажет сколько времени проводит сервер прежде чем выполнить запрос. Интересно, что единицей измерения для этой метрики является секунд в секунду (sec/sec)😊. Т.е. если мы получили показатель 3 sec/sec, то это значит, что наш сервер проводит 3 секунды каждую секунду времени на планирование и выполнение запроса. Соответсвенно, если время слишком большое, то нужно думать как его уменьшить, чтобы запросы выполнять быстрее.

📌 M = rows (кол-во строк). Покажет нам кол-во строк в секунду, возвращаемых группой запросов. Если строк слишком много, то может стоит как-то оптимизировать ваши запросы, чтобы это кол-во уменьшить.

📌 M = shared_blks_hit + shared_blks_read. Получим кол-во операций чтения общего буфера в секунду. Здесь, для более четкого понимания метрики имеет смысл перевести ее в байты, так показатель будет в блоках памяти. Если предполагать, что размер одного блока по умолчанию 8 KiB (проверить можно командой SHOW block_size;), то формула конвертирования в байты будет такой:

(Значение метрики) bytes/sec * 8 / 1024 / 1024

Если полученное значение слишком большое (гигабайты), то тут тоже стоит поискать кандидата для оптимизации.

📌 M = wal_bytes. Количество байт, записываемых запросом в журнал WAL в секунду. Соответсвенно, чем больше байт в WAL пишется, тем больше у нас нагрузка на дисковую подсистему. Не забываем, что pg_stat_statements не покажет нам неудачно завершившиеся запросы (ROLLBACK).

На этом пока все! Я думаю, что у нас будет еще парочка постов на тему pg_stat_statements, финализируем эту тему постом с полезным запросами. Так что, можете накидывать ваши запросы (если есть) в комментарии, собирем все в кучку 😊. До связи!

#pgmonitor #pgext

#pgmeme

#pgmeme

📊 Глубокое погружение в pg_stat_statements, часть 3.

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

Стоит иметь в виду, что представление показывает планируемые запросы (SELECT, INSERT, UPDATE, DELETE, MERGE) и различные служебные команды в агрегированном виде. Т.е. команды и запросы объединяются pg_stat_statements в одну запись, когда имеют идентичные структуры согласно внутреннему вычисленному хешу. Запросы будут считаться идентичными по структуре, когда они семантически равнозначны, не считая констант, которые могут присутствовать в запросе.

Т. е. pg_stat_statements прежде чем показать нам статистику, нормализует запросы и сравнивает их. Если значение какой-то константы, которая содержалась в запросе было проигнорировано pg_stat_statements в целях сравнения запроса, то такая константа будет отображаться в конечном выводе как $1. Так что, не удивляйтесь, если увидите такие значки в ваших запросах.

Важным моментом является то, что pg_stat_statements использует для сравнения в том числе id запроса (queryid), чтобы id правильно вычислялся нужно проверить, что у вас включен параметр compute_query_id. Этот параметр включает вычисление идентификатора запроса в ядре PostgreSQL. Идентификаторы запросов использует не только pg_stat_statements, но и также команда EXPLAIN и другие представления PostgreSQL, такие как pg_stat_activity. По умолчанию значение параметра compute_query_id равно auto. Т.е. позволяет включать вычисление ID запроса по необходимости.

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

Иногда pg_stat_statements может показать фактические константы в запросе, а не значок $1 вместо них. Это может случиться если внутри запроса много операторов и pg_stat_statements часть из них отбросил и не показал. На количество показываемых строк (операторов) запроса влияет параметр pg_stat_statements.max. Этот параметр отвечает за количество выводимых строк в представлении pg_stat_statements. Значение по умолчанию 5000. Если вы столкнулись с такой ситуацией, то рекомендуется увеличить значение данного параметра.

Отброшенные строки запросов можно посмотреть в представлении pg_stat_statements_info (с версии PostgreSQL 14).

Как видите, нюансов использования в любом инструменте хватает. Чтобы правильно интерпретировать данные и делать правильные выводы, эти нюансы необходимо знать.

Если вы устали от постов на тему pg_stat_statements, то дайте знать. Разбавим их еще какой-нибудь полезной информацией о PostgreSQL 😊 До связи!

#pgmonitor #pgext

📊 Глубокое погружение в pg_stat_statements, часть 2.

Продолжаем погружаться в работу с замечательным (но не без недостатков) расширением pg_stat_statements. В предыдущей части мы рассмотрели недостатки этого расширения и общую структуру. Но мы забыли о достоинствах этого замечательного расширения 😅:
📌 возможность собирать детальную статистику выполнения запросов;
📌 работа расширения почти не влияет на общую производительность системы (ну может быть совсем чуть-чуть).

Теперь давайте разберём несколько наиболее полезных метрик:

calls - количество вызовов запроса. Эта метрика поможет понять нам на сколько часто вызывался тот или иной запрос;

total_plan_time - агрегированное значение времени планирования запроса (вспоминаем, что неудавшиеся запросы pg_stat_statements не видит);

total_exec_time - агрегированное время выполнения запроса;

total_time - общее время выполнения запроса;

rows - количество строк, которое вернул запрос;

shared_blks_hit - количество попаданий запроса в буферный кэш;

shared_blks_read - количество операций чтения запроса из буферного кэша;

wal_bytes - сколько байт записано запросом в журнал WAL.

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

➡️ https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-PG-STAT-STATEMENTS

Теперь давайте разбираться, что со всем этим делать. Если некумулятивные метрики собрать и интерпретировать достаточно легко, то с кумулятимными ситуация немного посложнее. Чтобы правильно интерпретировать такие метрики нужно сделать несколько шагов:

1️⃣ Собрать метрики два раза в нужных промежутках, разнесенных по времени;

2️⃣ Посчитать разницу в данных между собранными метриками;

3️⃣ На основе данных из предыдущего шага посчитать 3 вида метрик:
📌 дифференциация метрики по времени;
📌 дифференциация по вызовам;
📌 процентная дифференциация.
Более подробно об этих дифференциациях поговорим в будущих постах.

Но можно и пойти другим путем. pg_stat_statements предоставляет нам функцию по сбросу своей статистики pg_stat_statements_reset (). Мы можем сбросить статистику перед началом сбора метрик, а потом, через какое-то время собрать метрики и получить данные, накопившиеся с момента сброса. В PostgreSQL 14 и выше у pg_stat_statements появилось еще одно представление - pg_stat_statements_info, которое содержит информацию о последнем времени сброса статистики. В версиях ниже 14й такого представления к сожалению не завезли.

На этом пока все! На связи! 😊

#pgmonitor #pgext

📊 Глубокое погружение в pg_stat_statements, часть 1.

Решили запилить серию постов про одного из главных помощников любого DBA PostgreSQL - расширение pg_stat_statements. Если кто не знает, то это расширение позволяет нам посмотреть детальную статистику и собрать различные метрики по выполнению запросов.

Но, к сожалению, не все идеально в этом мире, так что начнем с недостатков данного расширения:

📌 pg_stat_statements не показывает информацию об уже идущих в данный момент запросах. Такие запросы можно найти в представлении pg_stat_activity;

📌 самый большой недостаток pg_stat_statements заключается в том, что расширение не показывает невыполнившиеся из-за какой-то ошибки запросы, т.е. откатившиеся назад. Этот недостаток может привести вас к неправильным выводам при расследовании нагрузки, если о нем не знать. Например, вы увидели большую нагрузку на CPU, но эта нагрузка вызвана запросами, которые не смогли удачно завершиться. В таком случае pg_stat_statements окажется бесполезным, так как не покажет нам эти запросы;

📌 ну и третий недостаток, это скорее баг, а может и фича. Если в запросе есть SQL комментарии, то они не удаляются в выводе расширения, а показывается только первая строка комментария.

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

📌 queryid - идентификатор запроса. Этот идентификатор можно объединять (JOIN) с таким же идентификатором из представления pg_stat_activity или логов PostgreSQL, для получения более детальной информации. queryid может иметь отрицательное значение, так что не пугайтесь 😉;

📌 столбцы с различной описательной информацией, такие как: идентификатор базы данных ( dbid), пользователь ( userid) и текст запроса ( query);

📌 далее у нас идут метрики. Метрики разделяются на кумулятивные, т.е. по ним собирается накопительная информация и, соответственно, некумулятивные. К кумулятивным метрикам относятся: calls, total_time, rows, и т. д., некумулятивным: stddev_plan_time, stddev_exec_time, min_exec_time, и т. д.

Дальше мы с вами более глубоко погрузимся во все эти метрики и значения и станем настоящими специалистами в pg_stat_statements 😊. До встречи в следующих постах!

#queries #pgmonitor

#pgmeme

#pgmeme

🔬 Влияние порядка колонок таблицы на эффективность хранения данных в PostgreSQL.

Как вы думаете, влияет ли порядок колонок в таблице на ее размер на диске. Коротко ответим - да! Дальше можно не читать 😁

А если серьезно, то давайте разбираться как такое может быть. Начнем с того, что в PostgreSQL строка в таблице имеет минимальный размер в 24 байта. Это связано с тем, что в строке содержится заголовок с различной служебной информацией, типа номера транзакции, ID самой строки и т.д.

Теперь давайте проведем эксперимент. Для этого воспользуемся выражением ROW (). Выражение ROW() представляет собой конструктор табличной строки, т.е. - это выражение, создающее строку или кортеж (или составное значение) из значений его аргументов-полей. Для начала убедимся, что минимальный размер строки действительно 24 байта:

SELECT pg_column_size(ROW());

Получим вот такой ответ:

pg_column_size
----------------
24

Теперь добавим тип данных integer в строку , вот так:

SELECT pg_column_size(ROW(1::int));

Получим:

pg_column_size
----------------
28

Тут все закономерно, минимальный размер строки 24 байта + размер integer 4 байта = 28 байт.

Теперь добавим еще и маленький integer (smallint):

SELECT pg_column_size(ROW(1::int, 1::smallint));

Получим:

pg_column_size
----------------
30

Тоже все понятно: 24 + 4 + 2 (smallint) = 30 байт.

А что если попробовать поменять типы данных местами? Пробуем:

SELECT pg_column_size(ROW(1::smallint, 1::int));

И получим такое 😳:

pg_column_size
----------------
32

Откуда взялось 2 лишних байта? Причём, такое происходит и с другими типами данных. Вот два примера:

SELECT pg_column_size(ROW(1::bigint, true::boolean));
pg_column_size
----------------
33

SELECT pg_column_size(ROW(true::boolean, 1::bigint));
pg_column_size
----------------
40

Ответ на эту загадку кроется в выравнивании данных. Штука эта непростая для понимания, но мы попробуем объяснить ее просто и кратко. Суть в том, что для компьютерных систем проще находить данные на диске когда они выровнены по определенной границе. Это влечет за собой то, что, между данными нужно вставлять разделители, чтобы они были выровнены относительно друг друга на диске. Соответсвенно, в нашем случае между smallint и int был добавлен разделитель в два байта. Не все СУБД используют выравнивание данных, но наша любимая PostgreSQL его использует.

Мы можем посмотреть какое выравнивание для каких типов данных использует PostgreSQL, обратившись к системному каталогу pg_type, который содержит информацию об используемых типах данных. Запрос будет таким:

SELECT typname, typalign, typlen
FROM pg_type
WHERE typname IN ('int4', 'int2', 'int8', 'bool', 'varchar', 'text', 'float4', 'float8', 'uuid', 'date', 'timestamp');

В PostgreSQL существует 4е типа выравнивания данных:

📌 c - выравнивание не нужно;
📌 s- короткое выравнивание (2 байта);
📌 i - int выравнивание (4 байта);
📌 d - двойное выравнивание (8 байтов).

Теперь что со всем этим делать и как использовать? Если мы используем какое-то проприетарное приложение с PostgreSQL, типа 1с, то нам все эти знания особо и не нужны, нам остается только надеется, что разработчики приложения все предусмотрели и расположили колонки таблиц в правильном порядке. А вот если мы занимаемся проектированием баз данных, то это знание очень может пригодятся для оптимизации хранения данных на диске.

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

#pgbase

🗜️ Проверяем индексы PostgreSQL на ошибки (расширение amcheck).

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

При каких обстоятельствах у нас вообще могут повредиться индексы?

📌 Глюки PostgreSQL. Да, и такое бывает. Например, в версии PostgreSQL 14-14.3 был баг в операции REINDEX CONCURRENTLY, который потенциально мог приводить к повреждению B-tree индекса;

📌 Обновление на новую главную версию PostgreSQL;

📌 Перенос каталога PGDATA на другую операционную систему. В этом случае PostgreSQL может по-тихому переключиться на другую версию библиотеки glibc, в которой могут быть изменения в правилах сортировки. PostgreSQL 15 научилась засекать такие изменения и выдавать предупреждение:

WARNING: database XXX has a collation version mismatch

Но более старые версии PostgreSQL не жалуются на такие изменения и по этому риск незамеченного повреждения индекса может быть очень высок;

📌 Ошибки оборудования.

Выявить такие повреждения нам и поможет расширение amcheck.

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

На данный момент amcheck умеет проверять только B-tree индексы на ошибки. Проверка GiST и GIN индексов пока находится в разработке.

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

CREATE EXTENSION amcheck;

После установки нам станут доступны функции для проверки индексов. Все функции amcheck могут быть использованы только суперпользователем.

Пример использования основной функции для проверки B-tree индекса:

SELECT bt_index_check(index_oid);

Где index_oid нужно заменить на OID проверяемого индекса. В ответ получим список ошибок логической структуры индекса, если они есть. В таком виде будет произведена поверхностная проверка индекса, без отслеживания ссылок на строки (кортежи) таблицы. Т.е. будет проверен только индекс. Но у расширения есть опция heapallindexed, которая позволит сделать более глубокую проверку. Такая проверка занимает гораздо больше времени, но крайне рекомендуется, если вы действительно хотите быть уверены в том, что ваш индекс не содержит ошибок.

Учтите, что при проверке индекса расширением amcheck накладываются блокировки вида AccessShareLock, при которых не блокируются DML операции, но такая проверка занимает приличное кол-во ресурсов сервера, так что делать ее лучше в нерабочее время.

Также у расширения есть обертка командной строки под названием pg_amcheck, которая позволяет использовать все те же самые функции расширения amcheck, но прямо из командной строки. Плюс эта утилита позволяет проверить сразу несколько индексов в разных базах данных.

Более подробно обо всех функциях расширения можно почитать в официальной документации:

➡️ https://www.postgresql.org/docs/current/amcheck.html

А вот здесь про утилиту командной строки pg_amcheck:

➡️ https://www.postgresql.org/docs/current/app-pgamcheck.html

#pgutils #pgext

🛠️ Настраиваем PostgreSQL, для того чтобы уменьшить нагрузку на диск.

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

1️⃣ Первое, что приходит на ум, это запихнуть всю базу данных в общий буфер и держать ее там во время работы как можно дольше. Но это не сработает, если у вас очень большая база данных и не очень много оперативной памяти 😏. Хотя, конечно настройка параметра shared_buffers первична для повышения производительности PostgreSQL. Но тут и перестараться нельзя, если сделать значение shared_buffers слишком большим и пытаться запихнуть туда всю базу, то можно потерять данные, при неожиданном сбое сервера.

2️⃣ Следующий параметр, который может влиять на кол-во операций записи на диск - synchronous_commit. Напомним, что при включенном параметре synchronous_commit (по умолчанию) каждая транзакция ожидает своей записи на диск в журнал WAL, и только при успешной записи фиксируется (COMMIT). Ставить значение off для этого параметра опасно, так как можно потерять несколько последних транзакций при сбое сервера. С другой стороны, если вам потеря пары транзакций не очень важна, то отключение этого параметра может существенно повысить производительность всей системы. Что же касается снижения кол-ва операций записи на диск, то тут эффект будет не очень существенным.

3️⃣ Компрессия сегментов журнала WAL. Об этом мы уже писали. Тут, я думаю все понятно, чем больше мы данные сжимаем, тем меньший их объем пишется на диск, снижая тем самым операции записи. Эффект от компрессии WAL может быть разным, все зависит от вашей нагрузки и оборудования.

4️⃣ Увеличение интервала между контрольными точками. Напомним, контрольные точки - это отдельный процесс, который периодически сбрасывает грязные страницы из общего буфера на диск для того, чтобы содержать базу в консистентном состоянии. Соответсвенно, каждая контрольная точка - это запись на диск. Мы можем увеличить интервал между точками с помощью параметра checkpoint_timeout (по умолчанию 5 мин) и параметра max_wal_size, увеличив их. Таким образом контрольные точки будут происходить реже и нагрузка на диск уменьшится. Но, какие последствия будут после такого увеличения? В основном увеличение интервала контрольных точек грозит нам тем, что при неожиданном сбое сервера у нас сильно увеличится время восстановления базы данных при запуске, так как PostgreSQL начнет проигрывать недостающие данные из журнала WAL, чтобы довести базу до консистентного состояния.

5️⃣ Отключение фонового процесса записи (bgwtiter). Фоновый процесс записи - это своего рода помощник контрольных точек, который сбрасывает грязные страницы из буфера в базу в момент простоя базы данных. Отключить его мы можем выставив параметр bgwriter_lru_maxpages в ноль. К чему это приведет? Если мы увеличили период выполнения контрольных точек, то и фоновый процесс записи нам становится не нужен, раз уж мы хотим уменьшить кол-во операций записи на диск. Последствия такие же - длительное время восстановления базы данных при сбое. На общую производительность системы такое отключение повлияет не очень сильно, но операции записи на диск уменьшаться, что позволит еще и продлить срок службы SSD дисков.

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

Если вы знаете еще какие-то способы уменьшения количества операций записи на диск PostgreSQL, то пишите в комментариях 👇😊.

#pgsettings

#pgmeme

Официальная документация PostgreSQL 😊 #pgmeme

🗜️ Разбираемся с долгими запросами в PostgreSQL.

Что вообще понимать под долгими запросами? Здесь однозначного ответа нет, все зависит от типа нагрузки на вашу базу данных. Для кого-то запрос, длящийся 10 секунд будет считаться долгим (например, для мобильных и web приложений), а для кого-то запрос, длящийся час будет нормальным (для аналитических отчетов, например).

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

Как мы можем предотвратить такие долгие запросы? На данный момент нет 100% варианта ограничить выполнение долгого запроса только настройками PostgreSQL. В PostgreSQL 16 хотели добавить патч, который привнес бы новый параметр в настройки - transaction_timeout, но пока не добавили.

Какие у нас остаются варианты? Есть пара параметров, которые могут нам помочь в деле борьбы с долгими запросами:

✅ statement_timeout - ограничивает максимальную продолжительность одного запроса. Для веб/мобильных приложений рекомендуется устанавливать низкое значение, например, 30 сек. или 15 сек. В документации PostgreSQL можно найти совет, что установка этого параметра не рекомендуется. Но в реальности он все же может пригодиться. Учитывая, что мы можем его переопределить для конкретной сессии, которой может понадобиться более долгое время выполнения запроса с помощью команды SET;

✅ idle_in_transaction_session_timeout - устанавливает максимально допустимое время простоя между запросами в транзакции. Аналогичные рекомендации такие же: установите низкое значение, 15-30 сек. Значение этого параметра так же можно переопределить для конкретного сеанса.

Но эти два параметра нам не дадут 100% гарантии избавления от долгих запросов. По этому можно поступить хитрее и добавить выполнение запроса по расписанию, который бы отслеживал выполнение долгих запросов по заданному времени и убивал бы их по истечении таймаута. Выполнение по расписанию запроса можно сделать, например, с помощью расширения pg_cron. О нем мы уже писали.

Вот пример такого запроса:

SELECT clock_timestamp(), pid, query, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE clock_timestamp() - xact_start > interval '5 minute';

Но здесь тоже есть подводные камни. Нам нужно отфильтровать из этого запроса процессы, которым действительно нужны долгие запросы. Например pg_dump. Исключить их можно по PID процесса, например.

Чтобы вообще понять какие долгие запросы у нас существуют в базе, можно использовать вот такой запрос:

SELECT clock_timestamp() - xact_start, *
FROM pg_stat_activity
WHERE clock_timestamp() - xact_start > interval '1 minute'
ORDER BY clock_timestamp() - xact_start desc;

Может быть, вместо того чтобы бороться с долгими запросами, будет достаточно их оптимизировать и сделать быстрыми 😏.

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

#pgsettings

🛠️ Маленькие хитрости утилиты pg_restore.

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

1️⃣ Как нам всем хорошо известно, если мы создадим бэкап в формате directory (ключ -Fd), то мы имеем возможность ускорить процесс его восстановления с помощью утилиты pg_restore, используя несколько параллельных процессов с помощью ключа -j (--jobs). Но! Если у вас в базе данных одна большая таблица, то ключ -j будет бесполезен, так как параллельное восстановление в pg_restore работает на уровне таблиц. Это не касается секционированных таблиц, так как под капотом получается не одна таблица, а несколько.

2️⃣ По умолчанию pg_restore игнорирует ошибки при восстановлении, что может привести к тому, что какая-то часть данных может не восстановиться и это окажется незамеченным. Чтобы этого избежать, можно перевести pg_restore в так называемый строгий режим (strict) с помощью ключа -e (--exit-on-error). Так же можно запускать pg_restore в одной транзакции с помощью ключа -1 (--single-transaction).

3️⃣ Чтобы увидеть прогресс процесса восстановления и его детали, можно использовать ключ --verbose.

4️⃣ Мы можем восстановить только схему базы данных или только сами данные с помощью вот таких ключей:
-s (--schema-only) – восстановить только схему;
-a (--data-only) – восстановить только данные (схема уже должна существовать).

5️⃣ Если у нас дамп создан в формате directory, то мы можем отфильтровать данные и восстановить только то, что нам нужно. Делается это с помощью ключа -l, с помощью которого мы можем вывести список объектов в дампе, и ключа -L, с помощью которого мы уже можем данные отфильтровать. Пример:

# pg_restore -l путь_к_файлу_дампа

В ответ получим список всех объектов, которые содержаться в дампе. Далее делаем так:

# pg_restore -l путь_к_файлу_дампа \| grep -v INDEX \
> no_indexes.list

Т.е. здесь мы убрали индексы из дампа и сохранили это в файл no_indexes.list. Теперь мы можем использовать этот файл как фильтр при восстановлении, чтобы восстановить все кроме индексов с помощью ключа -L.

# pg_restore -j8 -L no_indexes.list --dbname=имя_базы путь_к_файлу_дампа

Здесь мы восстанавливаем бэкап в 8 потоков (-j8) и подсовываем наш файл-фильт при восстановлении.

Не забывайте после восстановления собрать статистику (ANALYZE) и построить карту видимости с помощью VACUUM. Ну или дождаться когда все это сделает AUTOVACUUM.

Если вы знаете еще какие-то полезные фишки pg_restore, то пишите в комментариях 👇 😊.

#pgback

🔬 Что такое ::oid и ::regclass в системном каталоге PostgreSQL?

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

Для начала давайте вспомним что такое системный каталог в PostgreSQL и зачем он нужен. В терминологии реляционных баз данных в целом и PostgreSQL в частности, все объекты базы данных, такие как: таблицы, индексы, представления, материализованные представления и т.д. называются отношениями (relations). В любой СУБД помимо таблиц базы данных есть еще специальные таблицы, которые хранят метаданные обо всех отношениях всех баз данных. Под метаданными понимается различного рода служебная информация, такая как идентификатор таблицы, имя и т.д.

В PostgreSQL такие таблицы и называются системным каталогом. Центральным местом этого каталога является pg_class. Здесь хранятся таблицы, содержащие информацию по всем объектам кластера PostgreSQL. Эти таблицы имеют два ключа:

✅ Первичный ключ: OID - идентификатор объекта;

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

С этим разобрались. Т.е. OID - это численный идентификатор отношения, а relname - имя отношения. Фокус состоит в том, что мы можем быстро конвертировать OID отношения в его имя и обратно, используя преобразование типов в oid и regclass типы данных. Здесь нам и пригодятся конструкции из заголовка поста. Делается это так:

SELECT 'имя_таблицы'::regclass::oid;

В ответ получим OID таблицы. Можно и наоборот:

SELECT oid_таблицы::regclass;

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

SELECT oid FROM pg_class WHERE relname = ...;

#pgbase

#pgmeme

#pgmeme

🌐 Новости из мира PostgreSQL на 27.09.2024 г.

Главная новость - это, конечно выход вчера 17-й версии PostgreSQL. С чем вас всех и поздравляем! 😊 В этой заметке посмотрим на самые интересные (на наш взгляд) фичи 17-й версии PostgreSQL.

✅ WAL и MVCC.

📌 Новое хранилище индетификаторов "мёртвых" строк. Теперь идентификаторы таких строк занимают в 20 раз меньше места, а поиск по ним стал в несколько раз быстрее.

📌 Оптимизация записи в WAL при очистке и заморозке. Теперь PostgreSQL при заморозке во время очистки (VACUUM) страницы пишет одну объединенную запись в журнал WAL, а не две. Это позволяет значительно уменьшить кол-во записей в WAL, а следовательно и операций ввода/вывода.

✅ Репликация.

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

📌 Перенос слотов репликации после обновления на новую версию PostgreSQL. Утилита pg_upgrade теперь умеет переносить слоты репликации. Опять же, подписчикам только остается поменять строку подключения если надо.

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

✅ SQL и команды.

📌 Команда MERGE теперь может использовать в качестве целевого отношения обновляемое представление. Появилось новое условие WHEN NOT MATCHED BY SOURCE, которое и позволяет обработать строки целевого отношения, которых нет в источнике. Также появилась функция merge_action, которая позволит понять какие строки вы поменяли или изменили.

📌 Команда COPY...FROM научилась игнорировать ошибки неправильного формата столбцов, а не как раньше вылетать с ошибкой. Теперь мы можем с помощью нового параметра команды log_verbosity выводить сообщения для каждой пропущенной в следствии ошибки строки.

📌 Поддержка стандарта SQL/JSON. Часть этого стандарта была внедрена в 16ю версию PostgreSQL, теперь реализовали оставшуюся часть.

✅ Безопасность.

📌 Появилась новая привилегия MAINTAIN и роль pg_maintain для таблиц и материлизованных представлений. Пользователи с такой ролью могут выполнять такие команды как: ANALYZE, VACUUM, CLUSTER, REFRESH MATERIALIZED VIEW и LOCK TABLE.

✅ Команда EXPLAIN.

Наша любимая команда обзавелась парой полезных функций. Теперь она может показывать затраты времени на распаковку значений с помощью параметра SERIALIZE и объем памяти для планирования запроса с помощью параметра MEMORY.

✅ Резервное копирование.

Главная фича! Инкрементальное резервное копирование из коробки! О нем мы уже писали.

✅ Мониторинг.

Появилось новое представление pg_stat_checkpointer, которое позволяет просмотреть информацию о статистике контрольной точки .

Более подробно обо все остальных новинках релиза 17-й версии PostgreSQL можете почитать на официальном сайте:

➡️ https://www.postgresql.org/about/news/postgresql-17-released-2936/

Релиз получился крутой! 💪 Так что, идем тестировать и обновляться! 😁

#pgnews

🛠️ Основные настройки потребления памяти PostgreSQL. (ч. 2).

В прошлой заметке мы остановились на параметре work_mem. Для его первичной настройки есть такая, часто встречающаяся, рекомендация:

Свободная RAM * 0.25 / max_connections

Это, опять же, первое приближение. Все может быть индивидуально для каждого типа нагрузки. Для программ семейства 1с, вообще рекомендуется выставлять work_mem в 128 или 256MB. Но, опять же, следует помнить про особенность данного параметра, о которой мы говорили в предыдущей заметке.

Чтобы понять достаточный размер work_mem вы выставили или нет, можно воспользоваться следующим способом. Мы можем выполнить вот такой запрос к представлению pg_stat_database:

SELECT datname, pg_size_pretty(temp_bytes / temp_files) AS overflow
FROM pg_stat_database
WHERE temp_files > 0;

Он нам покажет объем временных файлов, записанных PostgreSQL на диск. Соответсвенно, если этот объем большой, то значения параметра work_mem может не хватать и его следует увеличить.

Следующим параметром у нас идет maintenance_work_mem. Это объем памяти, который используется для таких операций как VACUUM, CREATE INDEX, и ALTER TABLE ADD FOREIGN KEY, т.е. для служебных операций. Значение по умолчанию 64MB. Значение этого параметра влияет на производительность операции VACUUM, по этому пренебрегать его настройкой не стоит. Рекомендуется устанавливать значение этого параметра выше, чем значение work_mem. Формула для расчета значения такая:

Свободная RAM * 0.05

Высокие значения для этого параметра можно устанавливать относительно безопасно, так как есть ограничение на одну операцию за сеанс и очень маловероятно, что у вас будет выполнено сразу несколько одновременных операций обслуживания. Поэтому считается безопасным ставить значения maintenance_work_mem и 1GB и 2GB. Нам всё-таки нужно, чтобы операции VACUUM делались как можно быстрее. Но следует помнить про то, что этот объем памяти может употребить КАЖДЫЙ фоновый процесс AUTOVACUUM. Для большинства серверов с достаточным запасом оперативной памяти размер maintenance_work_mem в 1GB будет безопасным. Если у вас памяти на сервере в обрез, то тут уже надо быть более аккуратным.

Последний параметр, который мы рассмотрим называется effective_cache_size. Рекомендованным значением этого параметра является 50% от свободной памяти. Но, не пугайтесь! Эта память не резервируется. Это подсказка планировщику запросов о том сколько памяти доступно для кэширования диска. Большие значения этого параметра вызывают больше операций сканирования по индексу, меньшие - операций последовательного сканирования.

В PostgreSQL есть куча рычажков, которые мы можем покрутить для увеличения производительности работы СУБД. Будем освещать их и говорить о лучших практиках их настройки в будущих постах.

#pgsettings

🛠️ Основные настройки потребления памяти PostgreSQL. (ч. 1).

Как и почти любая СУБД, PostgreSQL прожорлива до оперативной памяти, так как для ускорения обработки данных использует именно ее. Эти настройки имеют критическое влияние на производительность PostgreSQL, поэтому решили сделать небольшую шпаргалку по ним. В один пост все уместить не получится, так что будет маленькая серия заметок по этой теме.

Я думаю ни кто не будет спорить, что главным параметром, напрямую влияющим на производительность PostgreSQL является shared_buffers. Это параметр, отвечающий за размер буферного кэша, в котором PostgreSQL производит чтение и изменение наиболее часто используемых страниц данных. Его значение по умолчанию равно 128MB.

Во всех источниках в Интернет и в документации написано, что при первой установке PostgreSQL рекомендуется установить этот параметр равным 25% от свободной оперативной памяти сервера. Ключевое слово тут СВОБОДНОЙ, а не всей доступной оперативной памяти сервера. Это самое первое приближение, которое в дальнейшем позволит уже более четко подстроить размер буфера под вашу нагрузку.

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

Вот таким запросом мы можем посмотреть насколько занят буферный кэш:

WITH state AS (
SELECT count(*) FILTER (WHERE relfilenode IS NOT NULL) AS used,
count(*) FILTER (WHERE relfilenode IS NULL) AS empty,
count(*) AS total
FROM pg_buffercache)
SELECT *, round(used * 1.0 / total * 100, 1) AS percent
FROM state;

Запрос этот следует запускать после того, как в базе достаточное время поработали пользователи, чтобы в буфере накопился необходимый объём данных для анализа. Если вы в результате запроса увидите 100%, то объема shared_buffers может не хватать под вашу нагрузку и следует его увеличить. Если же наоборот, увидите маленькие проценты, то следует подумать об уменьшении объёма буфера, чтобы оптимизировать использование оперативной памяти на сервере.

Следующим не менее важным параметром является work_mem. Этот объем памяти используется PostgreSQL для различных служебных операций при выполнении запроса, типо сортировки результата. Значение по умолчанию 4MB.

Самое главное, что надо помнить про настройку этого параметра, это то что этот объем не выделяется единоразово под всех пользователей как shared_buffers, а используется каждой сессией отдельно и даже внутри запроса он может использоваться разными операциями. Т.е. если у вас, к примеру 10 пользователей, то КАЖДЫЙ пользователь может употребить по 4MB, если говорить про значение по умолчанию.

Если PostgreSQL будет не хватать этого объема для каких-то операций при выполнении запроса, то она начнет писать временные файлы на диск, что негативно скажется на производительности запросов. Этот параметр, кстати является одной из причин рекомендаций в установки 25% свобной памяти для shared_buffers. Ведь, если мы установим слишком много памяти для общего буфера, то оставшейся памяти на сервере может не хватить для операций сортировки и мы получим ошибку out of memory.

Продолжим разбираться с параметрами PostgreSQL, отвечающими за потребление памяти в следующей заметке!

#pgsettings