PostgreSQL: вернуть место после delete22.12.2023 15:00

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

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

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

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

delete from schema.table where condition;

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

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

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

Решение

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

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

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

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

sudo -u postgres psql

\c your_database
create extension if not exists pgstattuple;

grant execute on function pgstattuple(text) to ;
grant execute on function pgstattuple(regclass) to ;
grant execute on function pgstattuple_approx to ;

-- Если вы забыли пароль от админской уз 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  \
-n  \
-h localhost \
-p 5432 \
-U  \
-W  \
-t  \
-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.

© Habrahabr.ru