Недавно попросили поднять копию одного проекта для тестов, ну и соответственно потянуть базу данных как есть из боя (pg_basebackup). Выяснилось, что физический размер базы составил 505 GB, что само по себе слегка удивило, не то, чтобы данных там было мало. Много, но не настолько.
Первичный осмотр не выявил мусора или других проблем, поэтому решили сделать VACUUM FULL целиком на базу данных в технологическом окне и забыть о проблеме как таковой.
for dd in \
psql -U postgres -h [ host ] -p [ port ] -t -c "SELECT datname FROM pg_catalog.pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');";
do echo $dd; \
for tt in psql -U postgres -h [ host ] -p [ port ] $dd -c "SELECT '\"' || schemaname || '\".\"' || tablename || '\"' FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');" -t; \
do echo $tt; \
psql -U postgres -h [ host ] -p [ port ] $dd -c "VACUUM FULL $tt;";
done;
doneНа этом можно было бы закончить рассказ, и сказать, что после VACUUM FULL физический размер базы данных уменьшился на 104 Gb и все стали счастливы, расходимся.
Но это менее увлекательно, чем потратить немного времени и разобраться что к чему, и да, не у всех есть возможность использовать технологическое окно в несколько часов.
Итак, начнем сначала:
- есть база данных, размер 555 Gb, что по мнению является завышенной цифрой;
- в данный момент проблем с ней в общем нет: база работает нормально, бекапы делаются, места везде пока достаточно, и хочется чтобы так дальше и было;
- есть технологическое окно, и был проведен VACUUM FULL на всех таблицах, всех баз данных. В результате размер базы уменьшился до 451 Gb.
То что VACUUM FULL уменьшил объем базы не более чем 20% на самом деле хорошо, это значит, что не так много места использовалось неэффективно, и база данных настроена более-менее правильно. Если бы используемое место уменьшилось бы в разы, это был бы действительно повод задуматься над настройками базы.
Мы будем рассматривать только то что относится к непосредственной работе базы данных и её папкам. Какие еще артефакты есть на сервере — нас не интересует.
Первичный осмотр
Первым делом смотрим на сопутствующие данные, то есть:
- логи;
- WAL файлы;
- временные файлы;
- прочий мусор.
Логи
У нас фиксируется в логах в принципе все, чтобы можно было проводить более качественный анализ работы базы данных:
#> cat postgresql.conf
...
log_min_messages = info
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
...При этом, очевидно, что размер лог-файлов может быть достаточно большим:
ls -la /backup/log/postgresql/server/base/source/*
...
-rw-r--r-- 1 eo eo 52026109946 Sep 17 00:25 pg.main-2019.09.16.csv
-rw-r--r-- 1 eo eo 53264143235 Sep 18 00:26 pg.main-2019.09.17.csv
-rw-r--r-- 1 eo eo 52352131583 Sep 19 00:26 pg.main-2019.09.18.csv
-rw-r--r-- 1 eo eo 52086239824 Sep 20 00:25 pg.main-2019.09.19.csv
-rw-r--r-- 1 eo eo 52176368587 Sep 21 00:25 pg.main-2019.09.20.csv
-rw-r--r-- 1 eo eo 50602211593 Sep 22 00:25 pg.main-2019.09.21.csv
-rw-r--r-- 1 eo eo 49587651458 Sep 23 00:25 pg.main-2019.09.22.csv
...Да, размеры дневного лог-файла на высоко нагруженных базах иногда у нас превышают размер самой базы.
Поэтому не забываем про ротацию логов. Для этого устанавливаем ограничение на размер лог-файла или по времени, так параметр:
log_filename = 'pg.base.%Y-%m-%d.log'Ограничивает лог-файл одним днем, а параметры:
log_rotation_size = ...
log_rotation_age = ...Отвечают за максимальный размер и возраст log-файла
Понятно, что лог-файлы должны потом доставляться на какой-нибудь бэкапный сервер там анализироваться, архивироваться и прочая, но на сервере баз данных они не должны копиться.
И да, логи желательно сохранять в понятное для вас место, например, я рекомендую хранить логи по такому пути:
log_directory = '/spool/log/postgresql' WAL файлы
Или лог транзакций, кому как привычней их называть. Какие проблемы могут возникнуть с ними?
Журналы транзакций очевидно хранят транзакции, это значит, что можно открыть транзакцию и “забыть” её закрыть. Журнал начнет “копиться” с момента старта этой транзакции. Поэтому не забываем устанавливать timeout:
statement_timeout = 100000
lock_timeout = 100000
idle_in_transaction_session_timeout = 100000И только в особых случаях эти параметры увеличиваются.
Однажды один из разработчиков соединился с базой клиентом, который во время соединения открывает транзакцию и закрывает её во время закрытия. Так вот, он сделал пару UPDATE и пошел домой отдыхать на выходные. А рабочие станции у нас как бы не принято выключать вообще. Хотя переполнение WAL — было меньшей проблемой
Но основные проблемы с чисткой WAL файлов возникают на этапе их архивации, при включенном archive_mode, когда команда archive_command не может быть выполнена. В этом случае WAL файлы начинают копиться до момента когда архивация может сработать.
Ранее, до версии 9.6, мы использовали в archive_command = ‘rsync …’ и сразу отправляли файлы на ведомый сервер. Потом, что-то пошло не так. Сейчас же мы используем копирование WAL в соседний файловый раздел, а забором архивных WAL файлов озадачен ведомый сервер тем же rsync. Но для потоковой репликации WAL файлы важны не очень, поэтому их передачу мониторят обычно никак
Соответственно, требуется проверить, что у нас в pg_wal (pg_xlog, для версии 9.6 и ниже) и соответствует ли количество WAL файлов параметру wal_keep_segments.
Также стоит проверить раздел где хранятся архивные файлы WAL, если они доставляются ведомому серверу отдельно.
Временные файлы
На самом деле временные файлы, создаваемые при выполнении запросов, нас интересуют мало, так как они автоматически удаляются после выполнения. Но это не точно, при падении сервера баз данных во время выполнения тяжелого запроса, который создал много временных файлов, они таки могут остаться. Правда, чтобы такое произошло, требуется, как минимум “ретроградный Меркурий”.
Основной причиной переполнения временных файлов может служить наличие связки pgbouncer + PostgreSQL и временные таблицы.
Так, у нас бизнес-аналитики любят развлекаться с аналитическими запросами с использованием временных таблиц. Как известно, временные таблицы удаляются по истечении сессии, но при использовании pgbouncer сессия в общем-то становится как бы “вечной” в пуле соединений. И если временные таблицы не удалять вручную, то их удалять никто автоматически не будет.
Как-то однажды внезапно перезагрузился тестовый сервер с PostgreSQL который до этого достаточно долго “кошмарили” разработчики и аналитики. И база данных никак не хотела стартовать, то есть, она не падала при старте, а как-то “залипала” на этапе запуска. Как оказалось, накопилось несколько десятков гигабайт временных файлов и PostgreSQL их очень долго прожевывал на старте.
Для проверки требуется пройтись по всем базам данных и выполнить запрос:
SELECT
n.nspname as SchemaName
,c.relname as RelationName
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as RelationType
,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner
,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;Который вернет список всех временных таблиц текущей базы данных и их размер. Но можно просто быстро проверить, что у нас в папке pgsql_tmp:
[ PostgreSQL datadir ]/base/pgsql_tmp/Заодно проверить, не прошел ли “ретроградный Меркурий”.
Простой способ чистки — это остановка базы данных и полная очистка содержимого pgsql_tmp. Если возможности остановки нет, то тогда точечно удалять временные таблицы до просветления:
DROP TABLE pg_temp_[XX].[temp_table];Прочий мусор
В своей работе я сталкивался с разными артефактами криворуких админов, но массово заметил хранение каких-либо дампов базы в самой папке базы данных. Ну и система может сохранить core dump файл (postgres.core) процесса PostgreSQL при его падении. Остальной же состав файлов и папок примерно такой:
PG_VERSION
base
global
pg_*
postgresql.*
postmaster.*
recovery.*Все остальное — скорее всего неизвестные артефакты.
С первичным осмотром закончили и если результата нет или его мало, можно приступить к следующему этапу.
Как я писал выше, первичный осмотр не выявил проблем, все-таки боевая база. По его результатам места больше не стало. Но бывало, что размер накопившихся WAL файлов был несколько Gb, а размер всех временных таблиц как-то достиг ~ 100 Gb.
Некоторые спросят, а как же autovacuum, он же может быть выключен, упал и прочая. Отвечаю, включение или выключение autovacuum в моменте место нам не освободит и не займет. Им займемся позже.
Лишние объекты базы данных
Точнее неиспользуемые объекты базы данных. К ним могут относиться:
- неизвестные таблицы;
- неиспользуемые индексы;
Для того, чтобы отделить используемые объекты от неиспользуемых для начала потребуется включить сбор статистики в принципе, если его кто-то догадался выключить (https://www.postgresql.org/docs/current/runtime-config-statistics.html). Кстати, я где-то читал, что сборщик статистики можно выключить для экономии ресурсов. Не знаю, не проверял, ума хватило не заниматься ерундой.
Также, мне пришлось сбросить статистику, так как VACUUM FULL на всех таблицах привел к тому, что все таблицы использовались и из них читались данные. Сброс статистики выполняется командой:
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_*Смотрим, итого:
SELECT pg_size_pretty(SUM( pg_relation_size(schemaname ||'.'|| relname) ) )
FROM pg_stat_all_tables
WHERE
schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
AND seq_scan + idx_scan = 0
AND (
relname LIKE 'tmp_%'
);
pg_size_pretty
----------------
18 GB
(1 row)И это без учета размера индексов на этих таблицах и только одна база (пусть и самая большая).
Далее, можно переименовать все таблицы по шаблону, например ‘deplecated_’ + relname, либо отключить права на чтение-запись для проектного пользователя и ждать обратной связи от пользователей и приложений. В случае чего вернуть всегда можно. А можно их просто удалить, все одно есть резервирование и судя по статистике таблицы не используются, значит в бекапе будут актуальные данные.
Итого высвобождено места: 19 Gb (на 1Gb наскребли индексы и прочие таблицы из других баз).
При этом, стоит проверить сомнительные таблицы на использование. В случае чего, сделать соответствующие организационные выводы.
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
AND (
relname LIKE 'tmp_%'
);Неиспользуемые индексы
С индексами все гораздо проще, так как они не хранят в себе данные, но в процессе развития проекта некоторые индексы перестают быть актуальными. Некоторые создавались в качестве неудачных экспериментов оптимизации, но не удалялись. С одной стороны казалось бы ничего плохого, но с другой — индексы занимают дисковое пространство и требуют перестроений при изменении данных, то есть таким образом замедляют работу на этих операциях.
Но не стоит забывать, что некоторые индексы все-таки могут не использоваться, но тем не менее требуются для обеспечения логики, к таким индексам относятся:
- PRIMARY KEY
- UNIQ CONSTRAINT
Первичный ключ удалять точно нельзя даже если статистика показывает, что он ни разу не использовался. Уникальные индексы тоже скорее всего удалять нельзя, по крайней мере без согласования с разработкой. Тем не менее некоторые разработчики считают, что уникальные индексы работают быстрее и могут наплодить их вагон и тележку, при этом они будут проходить проверку уникальности, просто потому что в них поле id, и не создавать конфликтов при работе. Так что проверить все же стоит. Впрочем, это больше относится к производительности, потому вернемся к занимаемому месту.
Итак, поиск неиспользуемых индексов практически не отличается от поиска неиспользуемых таблиц.
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;При этом, удаление “лишних” индексов не так страшно с точки зрения данных. Но после удаления обязательно провести мониторинг и анализ медленных запросов к базе.
Общий размер составил:
SELECT
pg_size_pretty( SUM( 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%';
relationsize
--------------
14 GB
(1 row)Итого высвобождено места: 14 Gb.
В итоге, всего “мусора” насобирали на 33 Gb, с одной стороны немного, но с другой — в боевой базе много мусора быть и не должно.
В общем, с черновой уборкой закончили пора перейти к самой “мякоте”.
Фрагментация
Для начала слегка пройдемся по теории:
- записи хранятся в страницах таблицы по 8 kb;
- при изменении записи, новая версия может быть сохранена в той же странице, если у нее достаточно для этого свободного места (см. fillfactor);
- если в старой странице осталась хотя бы одна запись, то она не может быть использована для сохранения в ней новых записей.
В итоге основными “факторами” фрагментации являются:
- удаление старых записей, в этом случае в странице может освободится больше места для обновлений оставшихся в ней записей, но не для новых записей;
- если производятся постоянные массовые или частые изменения записей, autovacuum может не успевать очищать страницы и соответственно высвобождать резерв.
Последний пункт можно нивелировать увеличением приоритета процесса autovacuum в системе, так как по-умолчанию его приоритет понижен чтобы не мешать клиентам.
Для определения степени фрагментации можно воспользоваться запросом со страницы (не надо ничего придумывать, все уже придумано за нас): https://wiki.postgresql.org/wiki/Show_database_bloat
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
(maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+COUNT(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;Фрагментация индексов
Для индексов нет VACUUM, но есть REINDEX, при этом до версии PostgreSQL 12, REINDEX является блокирующей операцией на изменение. Начиная с 12 версии, REINDEX можно делать конкурентно, что давно можно было сделать, так как REINDEX по сути — это DROP + CREATE INDEX. И если операция DROP хоть и блокирующая, но атомарная, то CREATE INDEX CONCURRENTLY появился еще в версии 8.2 уж не помню в каком году. Но, при любых раскладах во время переиндексации индекс недоступен, и запросы его использовавшие будут искать альтернативы на это время, в основном более медленные. Самым простым способом решения является конкурентное создание еще одного такого же индекса с последующим удалением старого. Это позволит кроме всего прочего избавиться от блокировок на более старых версиях PostgreSQL.
Следует иметь ввиду что конкурентное создание индекса замедляется относительно того как много изменений вносится в таблицу при его создании.
Фрагментация таблиц
Если с индексами еще достаточно просто, то с таблицами куда сложней, так как есть VACUUM и он имеет несколько вариаций:
- просто VACUUM — суть дело — очистка старых пустых страниц для их переиспользования, а также очистка свободного места в заполненных таблицах. Autovacuum, фактически, это и так делает постоянно. Поэтому просто VACUUM в обычных случаях не даст ничего. И да, эта команда не освободит место в моменте, а только даст возможность записать новые данные в старые пустые страницы, но потом;
- VACUUM ANALYZE — в принципе ничего не очистит и только обновит статистику;
- VACUUM FULL перезапишет всю таблицу, но как бы требует полной блокировки.
То есть, если мы не хотим блокировок, а autovacuum у нас включен и работает, то VACUUM нам поможет ровно никак. Вообще в базах данных любое изменение данных, будь то простой перенос — блокирующая операция.
Как было сказано выше, основной проблемой раздувания таблиц является полупустые страницы, то есть, если в странице, допустим было 10 строк и 6 из них мы удалили, то оставшиеся 4 строки штатно не получится “выкурить” практически никак, так как оставшееся свободное пространство в странице позволит производить запись новой версии строк прямо на месте в той же странице не отходя от кассы.
Например создадим таблицу и отключим у нее autovacuum для чистоты эксперимента:
CREATE TABLE "public"."test" (
id BIGSERIAL,
sr BIGINT,
fl BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT test_pkey PRIMARY KEY(id)
)
WITH (
autovacuum_enabled = false,
);Заполним её какими-нибудь данными:
INSERT INTO
"public"."test"
(
sr
) SELECT * FROM generate_series(1,100000);Размер таблицы у нас получился:
SELECT SUM( pg_relation_size('"public"."test"' ));
sum
---------------
5218304
(1 row)Теперь немного её разрыхлим:
ALTER TABLE "public"."test" SET (fillfactor = 40);
VACUUM FULL "public"."test";
SELECT SUM( pg_relation_size('"public"."test"' ));
sum
----------------
13213696
(1 row)Все честно:
5218304 / 13213696 * 100 = 39,49 %Возвращаем степень заполнения в 100%
ALTER TABLE "public"."test" SET (fillfactor = 100);Теперь погоняем массовые UPDATE:
UPDATE "public"."test" SET fl = true;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = false;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = true;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = false;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = true;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = false;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = true;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = false;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = true;
VACUUM "public"."test";
UPDATE "public"."test" SET fl = false;
VACUUM "public"."test";Проверим, что у нас с размером:
SELECT SUM( pg_relation_size('"public"."test"' ));
sum
----------------
13213696
(1 row)Что и требовалось доказать — размер таблицы изменился ровно никак.
Важно! командой VACUUM мы осуществляли очистку свободного места в страницах для того, чтобы можно было не выносить обновление записи за пределы страницы. Если мы не будем этого делать, то мы получим весьма печальную картину:
Сделаем несколько массовых UPDATE без VACUUM и посмотрим как изменялся размер таблицы:
UPDATE "public"."test" SET fl = true;
SELECT SUM( pg_relation_size('"public"."test"' ));
...| SIZE | DELTA | % | NOTE |
| 13213696 | +0 | 0% | Первый раз — никаких изменений. У нас 60% свободного места на страницах |
| 13221888 | +8192 | 0% | Добавилась одна новая страница — ни о чем |
| 14508032 | +1286144 | 24.64% | Уже четверть записей обновилось в новые страницы |
| 15802368 | +1294336 | 24.80% | Еще на четверть увеличился объем |
| 18751488 | +2949120 | 56.51% | И далее — больше половины записей обновились в новые страницы |
| 21700608 | +2949120 | 56.51% | |
| 24649728 | +2949120 | 56.51% |
% увеличения — от стартового размера таблицы при плотном заполнении (5218304 байт).
Почему именно такой % и при таких условиях — отдельная тема и не будем на нее перескакивать, важно другое: не хотите “толстеть” — не отключайте autovacuum и проверяйте его включенность. Также до и после массовых UPDATE делайте VACUUM вручную.
В итоге: “выкурить” записи с полупустых страниц при работающем autovacuum — никак не получится, конечно можно “покостылить”, но легче от этого не станет. И да, массовый UPDATE — это тоже блокирующая операция. И таки да, “выкуриваются” далеко не все записи.
Тут еще можно было бы придумать, что-то типа такого:
- уплотнить записи в конце таблицы, чтобы страницы в начале таблицы стали пустыми;
- очистить таблицу;
- массово обновить, так чтобы уплотнение пошло в начало таблицы и освободило страницы в конце;
- очистить таблицу, отрезав пустые страницы в конце.
Но так не работает, так как, например, в последней странице в таблице практически всегда есть свободное место под обновление строки в ней и она всегда останется заполненной.
Но хватит примеров, вернемся к сути.
Вариант только один: полное клонирование таблицы. Увы клонирование больших таблиц занимает время во время которого вероятность изменения данных большая. Но если есть возможность приостановить операции изменения на таблице, то можно сделать так:
Убираем права на изменение данных для проектного пользователя:
REVOKE INSERT, UPDATE, DELETE
ON "public"."test"
FROM projects;Понятно, что пользователь при этом не должен быть SUPERUSER и не быть владельцем этой таблицы, иначе права забрать не получится.
Копируем таблицу:
CREATE TABLE "public"."test_new" AS TABLE "public"."test";Создаем для новой таблицы все индексы и первичный ключ. Восстанавливаем последовательности.
Поля SERIAL становятся целочисленными без последовательностей, поэтому после создания требуется перепривязать существующие последовательности от старой таблице к новой.
ALTER TABLE "public"."test_new"
ALTER COLUMN id SET NOT NULL;
ALTER TABLE "public"."test"_new
ADD CONSTRAINT test_new_pkey
PRIMARY KEY (id) NOT DEFERRABLE;
ALTER TABLE "public"."test"_new
ALTER COLUMN id SET DEFAULT nextval('"public"."test"_id_seq'::text::regclass);Важно именно перепривязать последовательности, так как иначе потребуется в дальнейшем и синхронизировать следующее значение для новых последовательностей.
Даем права на новую таблицу для проектного пользователя:
GRANT SELECT, INSERT, UPDATE, DELETE
ON "public"."test"_new
TO projects;И переименовываем таблицы:
BEGIN;
ALTER TABLE "public"."test" RENAME TO "public"."test"_old;
ALTER TABLE "public"."test"_new RENAME TO "public"."test";
COMMIT;Если же нет возможности блокировать изменения данных, то тогда потребуется делать синхронизацию данных порционно, для этого:
Клонируем таблицу, но без данных:
CREATE TABLE "public"."test_new" AS TABLE "public"."test" WITH NO DATA;Создаем триггер для таблицы test и хранимую процедуру для него:
CREATE OR REPLACE FUNCTION public.trigger_sync_test (
)
RETURNS trigger AS
$body$
BEGIN
IF TG_OP = 'INSERT' THEN
-- для INSERT просто вставляем строку в новую таблицу как есть
INSERT INTO "public"."test_new" SELECT NEW.*;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
-- для UPDATE обновляем все поля, в том числе и id,
-- при этом условие первичного ключа берется из не измененной строки
UPDATE "public"."test_new"
SET
id = NEW.id,
sr = NEW.sr,
fl = NEW.fl
WHERE id = OLD.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
-- при DELETE просто удаляем запись по первичному ключу
DELETE FROM "public"."test_new" WHERE id = OLD.id;
RETURN OLD;
ELSE
RAISE EXCEPTION 'WTF??? TG_OP = %', TG_OP;
RETURN NULL;
END IF;
END;
$body$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_sync
AFTER INSERT OR UPDATE OR DELETE
ON "public"."test"
FOR EACH ROW
EXECUTE PROCEDURE public.trigger_sync_test();То есть после каждой операции изменения данных, данный триггер будет осуществлять такую же операцию на новой таблице.
Сначала я думал создать универсальную процедуру, которая сама бы автоматически выбирала первичный ключ из таблицы, а также список полей таблицы для UPDATE, но во-первых мне было лениво, во вторых — конструкция была бы не так легка для чтения и её бы просто копипастили не вдаваясь в детали. Задача же у меня дать не инструмент, но знания.
После этого, так же создаем индексы и первичный ключ, только для индексов обязательно конкурентно, дабы не блокировать вызовы триггера, а также перепривязываем последовательности.
ALTER TABLE "public"."test_new"
ALTER COLUMN id SET NOT NULL;
ALTER TABLE "public"."test_new"
ADD CONSTRAINT test_new_pkey
PRIMARY KEY (id) NOT DEFERRABLE;
ALTER TABLE "public"."test_new"
ALTER COLUMN id SET DEFAULT nextval('"public"."test"_id_seq'::text::regclass);Ага, щаз… Первичный ключ конкурентно не создается, но так как данных там собралось еще не много, то операция выполнится быстро
В PostgreSQL 11 появились хранимые процедуры, поэтому воспользуемся ими для написания скрипта выгрузки данных (в противном случае потребуется использовать какой либо внешний инструмент для написания скрипта, но ни в коем случае не хранимую функцию, так как они работают в рамках одной транзакции):
CREATE OR REPLACE PROCEDURE public.procedure_sync_test (
)
AS
$body$
DECLARE
v_id BIGINT DEFAULT 0;
v_continue BOOLEAN DEFAULT TRUE;
BEGIN
WHILE v_continue LOOP
INSERT INTO "public"."test_new"
SELECT *
FROM "public"."test" AS t
WHERE t.id > v_id
ORDER BY t.id
LIMIT 1
FOR UPDATE
ON CONFLICT DO NOTHING
RETURNING id INTO v_id;
IF v_id IS NULL THEN v_continue := FALSE; END IF;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql';И выполним её:
CALL public.procedure_sync_test();Да, процедура будет выполняться достаточно долго, так как копирование строк происходит по одной, но зато и блокировка производится только на одну запись.
Так же выдаем права для проектного пользователя:
GRANT SELECT, INSERT, UPDATE, DELETE
ON "public"."test"_new
TO projects;После чего производим переименование. Да, в этот момент мы также столкнемся с блокировкой, но достаточно короткой, так как переименование таблицы атомарная операция, хоть их и две сразу:
BEGIN;
ALTER TABLE "public"."test" RENAME TO "public"."test"_old;
ALTER TABLE "public"."test"_new RENAME TO "public"."test";
COMMIT;И так для каждой таблицы.
Немного долго, немного неудобно, зато без эксклюзивных блокировок.
Резюме
В итоге что мы получили:
- на уплотнении таблиц: 104 Gb
- уборка мусора: 33 Gb
Мы сэкономили 24,6 % дискового пространства относительно начального размера базы данных.
Кому-то может показаться, что этого мало, кому-то — много, но:
- если у вас после всех манипуляций база данных “уплотнилась” в несколько раз — у меня для вас плохие новости: либо база данных работает криво, либо её обслуживанием не занимаются в принципе;
- то что мусора у меня на 33 Gb на боевом сервере — каюсь, да, недоглядел.