PostgreSQL — оптимизация размера базы

Кейс оптимизации размера PostgreSQL базы данных объёмом 505 GB. После проведения VACUUM FULL размер уменьшился на 104 GB — разберём в причинах раздувания базы и предложим систематический подход к её сокращению.

Первичный осмотр

Логи

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

log_filename                = 'pg.base.%Y-%m-%d.log'
log_rotation_size           = ...
log_rotation_age            = ...
log_directory               = '/spool/log/postgresql'

Логи должны доставляться на резервный сервер и не накапливаться на продакшене.

WAL файлы

Проблемы с WAL возникают при:

Открытых незакрытых транзакциях (решение — установка таймаутов).
Неудачной архивации файлов.

Рекомендуемые параметры:

statement_timeout                   = 100000
lock_timeout                        = 100000
idle_in_transaction_session_timeout = 100000

Требуется проверить содержимое pg_wal (или pg_xlog для версии 9.6 и ниже).

Временные файлы

Временные таблицы в pgbouncer могут не удаляться автоматически. Поиск:

SELECT
    n.nspname as SchemaName,
    c.relname as RelationName,
    pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s')
AND (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC;

Лишние объекты базы данных

Неиспользуемые таблицы

Сброс статистики:

select pg_stat_reset();

Поиск неиспользуемых таблиц:

SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_relation_size(schemaname ||'.'|| relname)) as RelationSize
FROM
    pg_stat_all_tables
WHERE
    schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') AND
    seq_scan + idx_scan = 0
ORDER BY
    pg_relation_size(schemaname ||'.'|| relname) DESC;

В описанном кейсе таблицы с префиксом tmp_* занимали 18 GB.

Неиспользуемые индексы

SELECT
    schemaname,
    relname,
    indexrelname,
    pg_size_pretty(pg_relation_size(schemaname ||'.'|| indexrelname)) as RelationSize
FROM
    pg_stat_all_indexes
WHERE
    schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') AND
    idx_scan = 0 AND
    indexrelname NOT LIKE '%pkey%'
ORDER BY
    pg_relation_size(schemaname ||'.'|| indexrelname) DESC;

Неиспользуемые индексы занимали 14 GB.

Итого очищено мусора: 33 GB.

Фрагментация

Теория

Записи хранятся в 8 KB страницах.
Удалённые строки освобождают место для обновления существующих строк в той же странице.
Не позволяют создавать новые записи на полупустых страницах.

Фрагментация индексов

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

Фрагментация таблиц

Обычный VACUUM не решает проблему полупустых страниц. Варианты решения:

1. С блокировкой (быстро):

CREATE TABLE "public"."test_new" AS TABLE "public"."test";

BEGIN;
ALTER TABLE "public"."test" RENAME TO "public"."test"_old;
ALTER TABLE "public"."test"_new RENAME TO "public"."test";
COMMIT;

2. Без блокировки (долго): через триггер синхронизации и хранимую процедуру порционного копирования.

Резюме

После всех операций:

Уплотнение таблиц: 104 GB.
Уборка мусора: 33 GB.
Итого экономия: 137 GB (24,6% от исходного размера).

Значительное уменьшение (в несколько раз) указывает на проблемы с конфигурацией или обслуживанием базы данных. Мусор на 33 GB свидетельствует о недостаточном контроле.

PostgreSQL. Хочешь похудеть? Спроси меня как