PostgreSQL. Хочешь похудеть? Cпроси меня как.
https://postgres.men/database/postgresql/devops-usage-disks-2/
Недавно попросили поднять копию одного проекта для тестов, ну и соответственно потянуть базу данных как есть из боя (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 на боевом сервере — каюсь, да, недоглядел.