PostgreSQL: вернуть место после delete

Суть проблемы

У вас есть таблицы, либо ряд таблиц, строки которых нужно очистить и единственный способ, которым вы можете это сделать — это операция DELETE.

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

Целевое решение проблемы хранения «устаревших» записей — pg_repack.

delete from schema.table where condition;

Помимо очевидной цели — очистки ненужных данных из таблицы, хотелось бы также увеличить свободное место в области диска, доступного для данных postgresql. Но при определенных условиях — операция DELETE не возвращает место, а операция UPDATE дополнительно его забирает.

Частично эту проблему решают команды vacuum и vacuum full, но у первых двух есть свои проблемы, подробности есть в документации.

Если коротко — vacuum освободит место при условии, что удалялись свежие (последние) данные, которые занимают «верхнюю» часть дата-файла.
vacuum full — освободит место, но для этого ему потребуется столько же места на диске, сколько уже занимает эта таблица, так как под капотом — это простая переливка данных из таблицу в таблицу.

Решение

Если у вас много места на диске — просто сделайте vacuum full schema.table.

Если нет — то исправлять ситуацию будем с помощью утилиты pgcompacttable, ссылка github.

Ставим зависимости:

• Debian-based Linux OS apt-get install libdbi-perl libdbd-pg-perl

• RedHat/Centos yum install perl-Time-HiRes perl-DBI perl-DBD-Pg

Переходим в интерактивный режим psql:

sudo -u postgres psql

\c your_database create extension if not exists pgstattuple;  grant execute on function pgstattuple(text) to <db user>; grant execute on function pgstattuple(regclass) to <db user>; grant execute on function pgstattuple_approx to <db user>;  -- Если вы забыли пароль от админской уз postgres alter user postgres with password 'your new admin password'; \q

Далее забираем себе локально perl скрипт.

git clone https://github.com/dataegret/pgcompacttable.git

Запускаем с необходимыми параметрами, полный список можно посмотреть через:

perl pgcompacttable --man

perl pgcompacttable \ --dbname <database_name> \ -n <schema_name> \ -h localhost \ -p 5432 \ -U <user> \ -W <password> \ -t <table_name> \ -f

Какие таблицы проблемные?

Запускаем SQL запрос для проверки данных в таблицах — смотрим на значения в 5 столбце (wastedbytes)

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,   pg_size_pretty(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 --where iname like '%ccnew%' ORDER BY (CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT end) DESC;

Единственная проблема, которую может вызвать «сжатие» таблицы — это падение на этапе работы кода. В результате которой в бд могут остаться «временные» индексы %ccnew%, их нужно будет самостоятельно удалить drop index index_name.

Полезный материал на youtube о хранении и записи данных в PostgreSQL

Большое спасибо всем за внимание! Если вам интересны подобные рассуждения — подписывайтесь на мой канал artydev & Co.