![](https://habrastorage.org/getpro/habr/upload_files/87b/7c1/a38/87b7c1a38fabec978377a375ad529ddb.png)
Суть проблемы
У вас есть таблицы, либо ряд таблиц, строки которых нужно очистить и единственный способ, которым вы можете это сделать - это операция 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.