Использование дискового пространства в кластере PostgreSQL

На дисках кластера PostgreSQL хранятся:

временные файлы;
файлы данных;
журналы транзакций (WAL);
слоты логической репликации;
логи СУБД;
системные файлы, необходимые для функционирования PostgreSQL.

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

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

размер временных файлов;
потребителей слотов репликации;
«мертвые» кортежи.

Если диск кластера будет заполнен на 80%, уведомление появится в панели управления и будет отправлено на электронную почту Владельца аккаунта и тех пользователей, которые подписаны на категорию уведомлений «Услуги и сервисы».

Если диск кластера будет заполнен на 95% и более, кластер перейдет в статус DISK_FULL и будет работать только на чтение. Чтобы кластер работал на чтение и запись, очистите диск или масштабируйте кластер и выберите конфигурацию с бóльшим размером диска.

Посмотреть размер временных файлов

Временные файлы могут использоваться для сортировки, хеширования и временного хранения результатов запросов. Чтобы посмотреть общий размер временных файлов в базе данных, используйте SQL-запрос к представлению pg_stat_database:

SELECT datname, temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM   pg_stat_database;

Пример вывода:

datname | temp_files   | temp_bytes
--------+--------------+----------------
mydb    |      2       |    16384
postgres|      1       |    8192

Здесь:

datname — имя базы данных;
temp_files — количество временных файлов в этой базе данных;
temp_bytes — размер временных файлов в байтах.

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

Размер временных таблиц, создаваемых конкретным запросом, можно получить с помощью команды EXPLAIN ANALYZE.

Проверить потребителей слотов логической репликации

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

Подробнее об управлении слотами логической репликации в статье Subscription документации PostgreSQL.

Чтобы проверить, есть ли у слотов логической репликации потребители, отправьте SQL-запрос к представлению pg_replication_slots:

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,active
FROM pg_replication_slots;

Пример вывода:

slot_name        | replicationslotlag | active
-----------------+--------------------+--------
myslot1          | 129 GB             | f
myslot2          | 704 MB             | t
myslot3          | 624 MB             | t

Здесь:

slot_name — имя слота логической репликации;
replicationslotlag — размер WAL-файлов, которые не будут автоматически удаляться при контрольных точках и которые потребители слота логической репликации могут использовать;
active — булево значение показывает, используется ли слот логической репликации:
- f — у слота нет потребителя;
- t — у слота есть потребитель.

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

«Мертвые» кортежи

Когда вы обновляете строки в таблице (UPDATE) или удаляете их (DELETE), кортежи фактически не удаляются с диска, а создаются их новые версии. Старые версии кортежей будут называться «мертвыми» (dead tuples). Такое версионирование необходимо для реализации процесса MVCC (Multi-Version Concurrency Control). Несмотря на то, что строки изменены в одной транзакции, другие активные транзакции могут продолжать видеть старую версию строк. «Мертвые» кортежи могут быть удалены, только когда все активные транзакции будут закрыты.

«Мертвые» кортежи также будут образовываться на реплике, если вы используете логическую репликацию.

Проверить «мертвые» кортежи

Если «мертвых» кортежей много, то они могут занимать значительный объем дискового пространства. Чтобы проверить количество «мертвых» кортежей, можно использовать расширение PostgreSQL pgstattuple или представление pg_stat_all_tables.

Пример SQL-запроса к представлению pg_stat_all_tables:

SELECT * FROM pg_stat_all_tables WHERE relname='test';

Пример вывода:

-[ RECORD 1 ]----------+------------------------------
relid                  | 16395
schemaname             | public
relname                | test
seq_scan               | 3
seq_tup_read           | 5280041
idx_scan               |
idx_tup_fetch          |
n_tup_ins              | 2000000
n_tup_upd              | 0
n_tup_del              | 3639911
n_tup_hot_upd          | 0
n_live_tup             | 1635941
n_dead_tup             | 1999952
n_mod_since_analyze    | 3999952
last_vacuum            |
last_autovacuum        | 2023-02-16 04:49:52.399546+00
last_analyze           | 2023-02-09 09:44:56.208889+00
last_autoanalyze       | 2023-02-16 04:50:22.581935+00
vacuum_count           | 0
autovacuum_count       | 1
analyze_count          | 1
autoanalyze_count      | 1

Здесь n_dead_tup — количество «мертвых» кортежей.

Сравнение способов удаления «мертвых» кортежей

Чтобы удалить «мертвые» кортежи, вы можете использовать стандартные команды VACUUM, VACUUM FULL или перепаковать таблицы и индексы с помощью расширения pg_repack.

	VACUUM	VACUUM FULL	pg_repack
Очищает таблицу от «мертвых» кортежей	✓	✓	✓
Уменьшает размер файла	✗	✓	✓
Полностью блокирует таблицу	✗	✓	Только в течение двух коротких периодов в начале и конце работы расширения

Подробнее о командах VACUUM и VACUUM FULL в статье Routine Vacuuming документации PostgreSQL.

Подробнее о расширении pg_repack и его функциях в документации pg_repack.

Перепаковать таблицы и индексы с помощью pg_repack

к сведению

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

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

Добавьте расширение pg_repack в базу данных.
Определите версию расширения pg_repack:

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

2.2. Определите версию расширения:
```
SELECT
  extname AS extension,
  extversion AS version,
  extnamespace::regnamespace AS schema
FROM pg_extension
WHERE extname = 'pg_repack';
```
Скачайте и установите клиент pg_repack той же версии, что и расширение.
Убедитесь, что на диске достаточно свободного места. Для полной перепаковки таблиц требуется свободное дисковое пространство, примерно вдвое превышающее размер обрабатываемых таблиц и индексов. Например, если общий размер обрабатываемых таблиц и индексов составляет 1 ГБ, потребуется дополнительно 2 ГБ дискового пространства.
Убедитесь, что в обрабатываемой таблице есть первичный ключ (PRIMARY KEY) или уникальный индекс (UNIQUE INDEX).
Перепакуйте таблицы и индексы в базе данных:
```
pg_repack -k -h <host> -p <port> \
  -U <user> \
  -d <database_name> \
  -t <table_name> \
  -i <index_name>
```
Укажите:
- <host> — DNS-адрес ноды;
- <port> — порт для подключения;
- <user> — имя пользователя базы данных;
- <database_name> — имя базы данных;
- опционально: -t <table_name>, где <table_name> — имя таблицы. Используйте этот параметр, если нужно перепаковать отдельную таблицу. Чтобы перепаковать несколько таблиц, укажите нужное количество параметров -t — по одному на каждую таблицу;
- опционально: -i <index_name>, где <index_name> — имя индекса. Используйте этот параметр, если нужно перепаковать отдельный индекс. Чтобы перепаковать несколько индексов, укажите нужное количество параметров -i — по одному на каждый индекс. Если индекс принадлежит таблице, которую вы уже указали через параметр -t, отдельно указывать его через параметр -i не нужно — индекс автоматически перепакуется вместе с таблицей.
Если не указывать параметры -t и -i, pg_repack перепакует все таблицы и индексы в указанной базе данных.

Очистить диск

к сведению

Мы не рекомендуем использовать запрос DELETE FROM table WHERE ... для очистки диска. Этот запрос может создавать выборки больших размеров на больших таблицах и располагать их на диске. Оставшееся свободное место на диске может полностью закончиться, это приведет к проблемам с PostgreSQL и необходимости восстанавливать его работу вручную.

Откройте транзакцию transaction_read_only = no и удалите ненужные данные с помощью одного из запросов:

DROP TABLE — удаляет структуру (данные, привилегии, индексы, constraints, триггеры). Используйте при полном удалении таблицы с данными и структурой:
```
BEGIN;
SET transaction_read_only = no;
DROP TABLE table_name;
COMMIT;
```
TRUNCATE TABLE — удаляет содержимое таблицы, при этом структура сохраняется. Работает быстрее DROP TABLE. Используйте при удалении всех строк таблицы с сохранением структуры таблицы:
```
BEGIN;
SET transaction_read_only = no;
TRUNCATE TABLE table_name;
COMMIT;
```
DELETE — используйте для удаления определенных строк.